Databricks dialect

Databricks dialect Reference for the databricks SQL dialect (src/dialects/databricks/index.ts). Overview Databricks SQL is built on Apache Spark SQL and the Delta Lake table format (the "lakehouse"). Spark SQL quotes identifiers with backticks, exactly like MySQL, so DatabricksDialect extends MySQLDialect purely to reuse the backtick quoting/escaping scaffolding and the general query-building skeleton, then overrides the places where Spark/Delta grammar genuinely differs from MySQL. Two things are worth stating honestly up front: This is a SQL-generation dialect layer. Its job is to emit Spark SQL / Delta grammar. The synchronous buildSql/buildQuery methods produce strings (and parameter arrays) without opening any connection — that is exactly what the test suite exercises. A real deployment would connect over the Databricks SQL driver (Databricks SQL Warehouse / @databricks/sql) rather than mysql2; the inherited connection code is not used against a real cluster. Default port is 443 (Databricks SQL Warehouses are reached over HTTPS), set in the constructor rather than MySQL's 3306. Identifier quoting is inherited verbatim from MySQLDialect: Connection You can also construct the dialect directly, e.g. for SQL generation in tests or tooling: Delta / Spark features CREATE TABLE (Delta/Spark grammar) buildCreateTableSql(tableName, columns, options?) emits Delta/Spark CREATE TABLE grammar. The Databricks-specific options are: Option | Emits | --- | --- | using (DatabricksTableFormat) | USING <format> — defaults to DELTA. Values: DELTA, PARQUET, CSV, JSON, ORC, AVRO | partitionedBy: string[] | PARTITIONED BY (cols) | clusteredBy: { columns, buckets } | CLUSTERED BY (cols) INTO n BUCKETS | location: string | LOCATION '...' (external/unmanaged table) | comment: string | trailing COMMENT '...' | tblproperties: Record<string,string> | TBLPROPERTIES ('k'=v, ...) | ifNotExists: boolean | CREATE TABLE IF NOT EXISTS | There is no MySQL ENGINE= / ROWFORMAT= / charset concept. using defaults to DELTA when omitted: Bucketing, external location, and table properties: The generated SQL contains: The overall clause order emitted by buildCreateTableSql is: USING → PARTITIONED BY → CLUSTERED BY ... INTO n BUCKETS → LOCATION → COMMENT → TBLPROPERTIES. GENERATED ALWAYS AS IDENTITY Auto-increment columns use Delta identity columns instead of MySQL's AUTOINCREMENT. A primaryKey column is emitted as an inline (informational) PRIMARY KEY — see Caveats. Per-column definition rules (buildColumnDefinitionSql): NOT NULL is emitted when allowNull === false; GENERATED ALWAYS AS IDENTITY when autoIncrement; DEFAULT <value> when defaultValue is set and the column is not an identity column; PRIMARY KEY when primaryKey; and a trailing COMMENT. CTAS helper buildCreateTableAsSelect(tableName, selectQuery, options?) emits CREATE TABLE ... USING <format> AS SELECT .... Only format, partitioning, location, comment, ifNotExists, and tblproperties apply — the column list and types are inferred from the SELECT. A trailing semicolon on the select is stripped. MERGE INTO upsert (buildUpsertQuery) Delta has no MySQL-style ON DUPLICATE KEY UPDATE. Upserts are expressed as a Delta Lake MERGE INTO statement where the row to upsert is supplied as an inline source relation (SELECT ? AS col, ...) and matched on the conflict key columns. values is [1, 'Ada', 'ada@example.com'] (parameterized in column order). conflictFields picks the ON/match key columns; it defaults to all columns when omitted or empty. updateOnDuplicate restricts which columns the WHEN MATCHED branch updates; it defaults to all non-key columns (columns not in conflictFields). If there are no non-key columns to update, the WHEN MATCHED branch is omitted entirely. A Literal value is inlined into the source SELECT (<val> AS col) rather than parameterized. At least one column is required, otherwise buildUpsertQuery throws. buildInsertQuery routes to this MERGE builder whenever options.upsert is set (a plain insert is inherited from MySQL unchanged): Type mapping getDataTypeSql(dataType) maps ORM types to Spark SQL / Delta types. The signature difference from MySQL is that character types collapse to STRING (Spark SQL has no VARCHAR(n)/TEXT), and the complex types ARRAY<>/MAP<>/STRUCT<> are supported. ORM type (key) | Spark SQL type | --- | --- | STRING, CHAR, TEXT, ENUM, SET, UUID, JSON, JSONB | STRING | INTEGER (default) | INT | INTEGER length: 1 | TINYINT | INTEGER length: 2 | SMALLINT | INTEGER length: 8 | BIGINT | BIGINT | BIGINT | FLOAT | FLOAT | DOUBLE | DOUBLE | DECIMAL | DECIMAL(precision,scale) (defaults 10,0) | BOOLEAN | BOOLEAN | DATE | TIMESTAMP (date + time) | DATEONLY | DATE | TIME | STRING (Spark has no standalone TIME) | BLOB | BINARY | ARRAY | ARRAY<elem> | MAP | MAP<keyType, valueType> | STRUCT | STRUCT<field: type, ...> | anything else | STRING | For ARRAY/MAP, an unspecified element/key/value type defaults to STRING. Raw string types are also normalized: a string matching VARCHAR, CHARACTER VARYING, NVARCHAR, CHAR, TEXT/NTEXT, TINYTEXT, MEDIUMTEXT, LONGTEXT, or CLOB returns STRING; any other raw string is returned unchanged. Caveats PRIMARY KEY / FOREIGN KEY are informational, not enforced In Databricks, PRIMARY KEY / FOREIGN KEY constraints are informational only (declared NOT ENFORCED, used by the optimizer for query planning); they do not enforce uniqueness or referential integrity. This dialect emits an inline PRIMARY KEY when a column requests it (see the identity example above) but never relies on it for correctness. RETURNING is unsupported Delta does not support a RETURNING clause. The dialect throws rather than emit SQL a cluster would reject: buildUpsertQuery(..., { returning: true }) throws Databricks (Delta MERGE) does not support the RETURNING clause. ... buildUpdateQuery(..., { returning: true }) throws buildDeleteQuery(..., { returning: true }) throws Re-fetch the row(s) after the write instead. UPDATE / DELETE have no trailing LIMIT Delta UPDATE and DELETE support WHERE but not a trailing LIMIT. Any limit option is stripped before delegating to the inherited builder: LIMIT / OFFSET Unlike MySQL, Spark SQL accepts a standalone OFFSET, so buildLimitOffset drops MySQL's LIMIT 18446744073709551615 OFFSET n sentinel hack: OFFSET is only valid on the outermost query and requires a recent Spark/DBR runtime (Spark 3.4 / Databricks Runtime 11+); on older runtimes it raises a parser error. Prefer a keyset/WHERE predicate for deep pagination. ALTER TABLE uses Spark grammar buildAddColumnSql emits Spark's plural ALTER TABLE t ADD COLUMNS (col type [COMMENT '...']) — not MySQL's singular ADD COLUMN. NOT NULL, DEFAULT, PRIMARY KEY, and identity clauses are intentionally omitted on an added column (Spark rejects them there): buildRenameColumnSql uses RENAME COLUMN old TO new (not MySQL CHANGE). buildDropColumnSql → ALTER TABLE t DROP COLUMN col (requires Delta column-mapping mode enabled on the table). changeColumn() throws — MySQL-style CHANGE col <full-def> is not valid Spark SQL. Use renameColumn(), setColumnComment() (ALTER COLUMN col COMMENT '...'), or setColumnNullability() (ALTER COLUMN col SET|DROP NOT NULL) for the supported in-place edits. buildDropTableSql never emits CASCADE (Spark has no DROP TABLE ... CASCADE); a passed cascade option is ignored. Table properties: buildSetTablePropertiesSql → ALTER TABLE t SET TBLPROPERTIES (...), buildUnsetTablePropertiesSql → ALTER TABLE t UNSET TBLPROPERTIES [IF EXISTS] (...). Secondary indexes are unsupported Spark SQL has no generic CREATE INDEX. addIndex, createIndex, removeIndex, dropIndex, createFulltextIndex, and createSpatialIndex all throw, directing you to liquid clustering / Z-ORDER (OPTIMIZE ... ZORDER BY), partitioning, or a Bloom-filter index instead. Verification status SQL-generation verified (asserted by tests/dialects/databricks.test.ts, which exercises only the synchronous builders — no connection is opened): Dialect name and inherited backtick escapeId. CREATE TABLE with USING DELTA, PARTITIONED BY, CLUSTERED BY ... INTO n BUCKETS, LOCATION, TBLPROPERTIES, default format DELTA, and GENERATED ALWAYS AS IDENTITY + informational PRIMARY KEY. CTAS (buildCreateTableAsSelect). MERGE upsert (buildUpsertQuery) SQL shape and parameter order, plus the RETURNING rejection. Plain vs. upsert INSERT routing, and bulkInsert multi-row VALUES. SELECT clause assembly and standalone OFFSET (no BIGINT sentinel). UPDATE/DELETE without LIMIT, increment, and RETURNING rejection. ALTER TABLE grammar (ADD COLUMNS, DROP COLUMN, RENAME COLUMN, ALTER COLUMN comment/nullability, SET/UNSET TBLPROPERTIES, DROP TABLE without CASCADE, changeColumn rejection). Secondary-index rejections. The full type-mapping table. Not verified against a live warehouse. No end-to-end execution against a real Databricks SQL Warehouse is covered — there is no databricks-crud integration test, and the dialect does not use the real @databricks/sql driver. Whether each generated statement runs successfully depends on the target Databricks Runtime version (e.g. standalone OFFSET on DBR 11+, Delta column-mapping mode for DROP COLUMN, identity-column support). Treat the SQL here as verified-by-shape, and validate against your actual warehouse before relying on it in production. Related reading All dialects — what each engine supports Data types — how each type maps to this engine Querying — how a FindOptions becomes SQL