SQL to Avro Converter

About SQL to Avro Converter

Convert SQL database dumps (CREATE TABLE and INSERT statements) to Apache Avro schema format. Avro is a data serialization system that provides rich data structures, a compact binary format, and is widely used in big data processing frameworks like Apache Hadoop, Spark, and Kafka.

Key Features

• Automatic Type Detection: Intelligently detects int, long, double, boolean, and string types
• Nullable Fields: Optional union types with null for flexible data handling
• Custom Schema Name: Configure record name and namespace
• Sample Data: Optionally include sample data in JSON format
• Field Name Sanitization: Ensures Avro-compliant field names
• Smart Parsing: Extracts column names and data from SQL dumps
• File Download: Save as .avsc file

How to Use

1. Input SQL Data: Paste your SQL CREATE TABLE and INSERT statements or upload a .sql file
2. Configure Schema: Set schema name and namespace for your Avro record
3. Choose Options: Enable nullable fields and sample data as needed
4. Copy or Download: Use the Copy or Download button to save your Avro schema

Avro Type Mapping

• int: Integer values from -2,147,483,648 to 2,147,483,647
• long: Large integer values beyond int range
• double: Floating-point numbers with decimal places
• boolean: True/false values (including 1/0)
• string: Text and mixed content
• null: Optional union type for nullable fields

Example Conversion

SQL Input:

CREATE TABLE products (
  id INT,
  name VARCHAR(100),
  price DECIMAL(10,2),
  in_stock BOOLEAN
);

INSERT INTO products VALUES (1, 'Laptop', 999.99, true);
INSERT INTO products VALUES (2, 'Mouse', 24.99, false);

Avro Schema Output (with nullable fields):

{
  "type": "record",
  "name": "products",
  "namespace": "com.example",
  "fields": [
    {
      "name": "id",
      "type": ["null", "int"],
      "default": null
    },
    {
      "name": "name",
      "type": ["null", "string"],
      "default": null
    },
    {
      "name": "price",
      "type": ["null", "double"],
      "default": null
    },
    {
      "name": "in_stock",
      "type": ["null", "boolean"],
      "default": null
    }
  ]
}

Common Use Cases

• Apache Kafka: Define message schemas for Kafka topics
• Apache Hadoop: Store data in HDFS with Avro format
• Apache Spark: Process data with Avro serialization
• Data Lakes: Schema evolution and data versioning
• Microservices: Efficient data serialization between services
• ETL Pipelines: Data transformation and migration

Avro Advantages

• Compact Binary Format: Smaller file sizes compared to JSON/XML
• Schema Evolution: Add, remove, or modify fields without breaking compatibility
• Rich Data Structures: Support for complex nested types
• Code Generation: Generate classes in Java, Python, C++, etc.
• Fast Serialization: Efficient encoding and decoding
• Self-Describing: Schema is stored with the data

Nullable Fields

When enabled, fields are defined as union types with null:

• Flexible Data: Handle missing or optional values
• Default Values: Set default to null for optional fields
• Schema Evolution: Add new optional fields without breaking compatibility

Field Name Rules

Avro field names must follow these rules:

• Start with [A-Za-z_]
• Contain only [A-Za-z0-9_]
• Invalid characters are replaced with underscores
• Leading numbers are prefixed with underscore

Supported SQL Syntax

• CREATE TABLE: Extracts column names for schema fields
• INSERT INTO: Parses data values for type detection
• Data Types: Handles all SQL data types (VARCHAR, INT, DECIMAL, BOOLEAN, etc.)
• Quoted Strings: Handles single and double quotes with proper escaping

Integration with Big Data Tools

Apache Kafka:

• Use with Confluent Schema Registry
• Define message schemas for producers and consumers
• Enable schema validation and evolution

Apache Spark:

• Read and write Avro files with spark.read.format("avro")
• Efficient columnar storage with compression
• Schema inference and validation

Apache Hadoop:

• Store data in HDFS with Avro format
• Use with MapReduce and Hive
• Efficient data serialization and compression

Avro Resources

• Apache Avro: Official documentation (avro.apache.org)
• Confluent Schema Registry: Schema management for Kafka
• Avro Tools: Command-line utilities for Avro files

Frequently Asked Questions

• Q: Do I need both CREATE TABLE and INSERT statements? A: No. The converter can infer columns and types from INSERT statements alone, but including CREATE TABLE helps produce better field names.
• Q: What happens if some columns contain mixed data types? A: The tool inspects all non-empty values and chooses the most specific common Avro type. If values are inconsistent, the column falls back to string.
• Q: How are NULL or empty values handled? A: When nullable fields are enabled, NULL or empty values are represented as null in the sample data and allowed in the ["null", "type"] union.
• Q: Can I change the record name later? A: Yes. You can safely rename the Avro record (name/namespace) in the generated schema as long as all producers and consumers agree on the same schema.
• Q: Is the generated schema production ready? A: It is a solid starting point, but you should review field names, types, and defaults to match your organizations Avro and schema registry conventions.

Privacy & Security

All conversions happen locally in your browser. Your SQL data is never uploaded to any server, ensuring complete privacy and security.