SQL Dialects

Relevant source files

The following files were used as context for generating this wiki page:

• internal/translate/sparksql/translator.go
• internal/translate/databrickssql/translator.go
• internal/translate/sparksql/resolver.go
• internal/translate/sparksql/translator_test.go (referenced via functionality)
• internal/translate/databrickssql/translator_test.go

The SQL Dialects module in the Daco CLI project is responsible for translating JSON schemas into Data Definition Language (DDL) CREATE TABLE statements for specific big data platforms. It currently supports Spark SQL and Databricks SQL dialects, ensuring that complex hierarchical JSON structures are accurately represented as flat or nested SQL types.

The system utilizes a template-based approach combined with structural inlining to convert $ref definitions into native SQL complex types like STRUCT. This allows users to generate production-ready schemas for data warehouses and lakehouses directly from OpenDPI or standard JSON schema specifications.
Sources: internal/translate/sparksql/translator.go:23-26, internal/translate/databrickssql/translator.go:27-30

Architecture and Translation Logic

The translation process follows a structured pipeline where a JSON schema is first prepared into a generic intermediate representation before being specialized for a specific SQL dialect.

Translation Pipeline

The diagram above illustrates the flow from a raw JSON schema to a final SQL file, highlighting the structural inlining phase.
Sources: internal/translate/sparksql/translator.go:37-52, internal/translate/databrickssql/translator.go:41-56

Structural Inlining

Unlike programming language translators (like Go or Java) that might generate separate classes or structs for referenced types, the SQL translators inline these references. This is achieved through the inlineStruct function, which replaces reference-type fields with inline STRUCT<...> definitions by recursively looking up definitions in a defMap.
Sources: internal/translate/sparksql/translator.go:58-72, internal/translate/databrickssql/translator.go:62-76

Circular Reference Handling

The system includes logic to prevent infinite loops during the inlining of nested structures. It utilizes a visited map to track processed definitions; if a back-edge is detected in a circular reference, the system skips inlining for that specific edge to prevent stack overflow.
Sources: internal/translate/databrickssql/translator_test.go:167-179

Supported Dialects

The project provides specific implementations for two major SQL-on-Hadoop/Cloud dialects.

Spark SQL

The Spark SQL translator generates standard Spark DDL. It maps JSON types to Spark-specific types like BIGINT for integers and TIMESTAMP for date-time formats.

Feature	Implementation
File Extension	.sql
Root Name	Append _schema to port name
Nullability	Adds NOT NULL tag if field is required
Complex Types	Uses STRUCT<...> and ARRAY<...> syntax
Sources: internal/translate/sparksql/translator.go:28-31, internal/translate/sparksql/resolver.go:19-54

Databricks SQL

The Databricks SQL dialect extends Spark SQL capabilities with specific optimizations for the Databricks platform, such as Delta Lake support.

Feature	Implementation
Table Type	Appends USING DELTA to statements
Escaping	Implements sqlEscape to handle single quotes in comments
Metadata	Supports table and field-level COMMENT attributes
Sources: internal/translate/databrickssql/translator.go:21-25, internal/translate/databrickssql/translator_test.go:107-118, internal/translate/databrickssql/translator_test.go:191-205

Type Mapping Reference

The resolver component defines how JSON schema types and formats map to SQL primitive types.

JSON Type	Format	SQL Type
string	N/A	STRING
string	date	DATE
string	date-time	TIMESTAMP
string	uuid	STRING
integer	N/A	BIGINT
number	N/A	DOUBLE
boolean	N/A	BOOLEAN
Sources: internal/translate/sparksql/resolver.go:19-45

Code Implementation Example

The following snippet demonstrates the logic used to render SQL STRUCT types from internal field definitions:

// renderStruct renders fields as STRUCT<field1: TYPE, field2: TYPE>.
func renderStruct(fields []translate.Field) string {
	parts := make([]string, len(fields))
	for i, f := range fields {
		parts[i] = f.Name + ": " + f.Type
		if f.Tag != "" {
			parts[i] += f.Tag
		}
	}
	return "STRUCT<" + strings.Join(parts, ", ") + ">"
}

Sources: internal/translate/sparksql/translator.go:75-84

Summary

The SQL Dialects module provides a robust mechanism for generating schema-synchronized SQL tables. By handling nested JSON structures through recursive structural inlining and providing dialect-specific resolvers for Spark and Databricks, it ensures that data contracts defined in JSON or OpenDPI are strictly enforced at the database layer.