> ## Documentation Index
> Fetch the complete documentation index at: https://docs.firebolt.io/llms.txt
> Use this file to discover all available pages before exploring further.

# SparkSQL and Databricks compatibility

> Run Spark SQL or Databricks SQL against Firebolt by switching the session dialect.

Preview

SparkSQL compatibility mode lets you run [Spark SQL](https://spark.apache.org/docs/latest/sql-ref.html) against Firebolt without rewriting it, and Databricks compatibility mode does the same for [Databricks SQL](https://docs.databricks.com/aws/en/sql/language-manual/). When a mode is on, Firebolt parses each statement with that system's grammar and translates it to native Firebolt SQL, so Spark query syntax, functions, and type names work as written.

Use it to point existing Spark or Databricks queries and tools at Firebolt, and to migrate a workload incrementally: run the SQL you already have, then adopt native Firebolt SQL where you want to.

## What works

* [Query syntax](https://docs.firebolt.io/reference-sql/spark/query-syntax.md): `SELECT`, `FROM`, joins including `LEFT SEMI` and `LEFT ANTI`, set operations including `MINUS` and `BY NAME`, `QUALIFY`, `GROUP BY ALL`, `GROUPING SETS`, `ROLLUP`, `CUBE`, CTEs, `SELECT * EXCEPT`, `SORT BY`, `TABLE`, `VALUES`, and the `|>` pipe operator.
* [Table-valued functions](https://docs.firebolt.io/reference-sql/spark/functions/table-valued.md): `RANGE`, `EXPLODE`, `POSEXPLODE`, `STACK`, and `IDENTIFIER` in a `FROM` clause.
* [Data types](https://docs.firebolt.io/reference-sql/spark/data-types.md): `BOOLEAN`, the integer types, `FLOAT`, `DOUBLE`, `DECIMAL`, `STRING`, `BINARY`, `DATE`, `TIMESTAMP`, `TIMESTAMP_NTZ`, `ARRAY`, and `STRUCT`.
* [Operators](https://docs.firebolt.io/reference-sql/spark/operators.md): arithmetic, bitwise, comparison including `<=>`, and logical operators, the `::` cast, `LIKE ANY` and `LIKE ALL`, `RLIKE`, and zero-based `[]` subscripts.
* Functions:
  * [Aggregate](https://docs.firebolt.io/reference-sql/spark/functions/aggregate.md): `AVG`, `COLLECT_LIST`, `COUNT_IF`, `MAX_BY`, `MIN_BY`, the `REGR_` family, and more.
  * [Array, collection, and struct](https://docs.firebolt.io/reference-sql/spark/functions/array-and-collection.md): `ARRAY`, `ARRAY_CONTAINS`, `ARRAY_DISTINCT`, `ARRAY_MAX`, `ARRAYS_OVERLAP`, `GET`, and more.
  * [Bitwise](https://docs.firebolt.io/reference-sql/spark/functions/bitwise.md): `SHIFTLEFT`, `SHIFTRIGHT`, and more.
  * [Conditional and predicate](https://docs.firebolt.io/reference-sql/spark/functions/conditional-and-predicate.md): `COALESCE`, `IF`, `NULLIF`, `NVL`, `NVL2`, `EQUAL_NULL`, `RLIKE`, and more.
  * [Conversion](https://docs.firebolt.io/reference-sql/spark/functions/conversion.md): `CAST` and more.
  * [Date and timestamp](https://docs.firebolt.io/reference-sql/spark/functions/date-and-time.md): `ADD_MONTHS`, `DATE_ADD`, `MAKE_DATE`, `FROM_UTC_TIMESTAMP`, `TO_DATE`, `TO_TIMESTAMP`, and more.
  * [Hash](https://docs.firebolt.io/reference-sql/spark/functions/hash.md): `MD5` and more.
  * [JSON and semi-structured](https://docs.firebolt.io/reference-sql/spark/functions/semi-structured.md): `JSON_OBJECT_KEYS`, `TO_JSON`, and more.
  * [Math](https://docs.firebolt.io/reference-sql/spark/functions/math.md): `EXP`, `GREATEST`, `LEAST`, `LOG2`, `PMOD`, `POWER`, and more.
  * [Miscellaneous](https://docs.firebolt.io/reference-sql/spark/functions/misc.md): `CURRENT_USER`, `UUID`, and more.
  * [String](https://docs.firebolt.io/reference-sql/spark/functions/string.md): `ELT`, `INSTR`, `REGEXP_COUNT`, `REPEAT`, `SPLIT_PART`, `URL_ENCODE`, and more.
  * [Window](https://docs.firebolt.io/reference-sql/spark/functions/window.md): `RANK`, `LAG`, `LEAD`, `NTH_VALUE`, `NTILE`, and more.
* [Statements](https://docs.firebolt.io/reference-sql/spark/statements.md): `CREATE TABLE`, `CREATE VIEW`, `CREATE SCHEMA`, `DROP`, `ALTER TABLE`, `INSERT`, `UPDATE`, `DELETE`, `MERGE`, `TRUNCATE TABLE`, `SHOW`, `DESCRIBE`, and transactions.
* [Information schema](https://docs.firebolt.io/reference-sql/spark/information-schema.md): the Databricks-shaped `information_schema` views.
* [Firebolt's own functions](#calling-firebolt-functions): call them with the `firebolt.` qualifier when Spark has no equivalent.

Support was checked against open-source Spark 4.1: every example in Spark's function reference ran on both engines and the results were compared. Databricks has no open-source engine to compare against, so behavior specific to Databricks was checked by hand, and only where this reference describes it. Databricks-only functions, such as the `ai_` and `h3_` families and `read_files`, are not available in either mode.

## Enable a mode

Set the `dialect` session setting to `spark` for SparkSQL mode or to `databricks` for Databricks mode:

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SET dialect = 'spark';
SET dialect = 'databricks';
```

Every statement in the session is then read as Spark SQL or Databricks SQL. You can also pass the setting as an HTTP query parameter (`?dialect=spark`) or in a `WITH (dialect='spark')` clause. Return to native Firebolt SQL with:

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SET dialect = 'firebolt';
```

The default is `firebolt`.

## Choosing a mode

Both modes translate the same way: the functions, data types, operators, and results described in this reference are identical. They differ only in grammar, where each follows its own system, so pick the mode that matches the SQL you run.

| Feature | SparkSQL mode | Databricks mode |
| :- | :- | :- |
| `a DIV b` integer division | Supported | Not supported. Use `CAST(a / b AS BIGINT)`. |
| Typed numeric literals: `1Y`, `1S`, `1L`, `1.5F`, `1.5D`, `1.5BD` | Supported | Not supported: `1L` reads as an identifier. Use `CAST`. |
| `LONG` as a type name | Supported | Not supported. Use `BIGINT`. |
| `CREATE TABLE ... USING <format>` | Supported. The format is ignored. | Not supported |
| Backslash escapes in string literals, such as `'a\tb'` and `'it\'s'` | Not processed: a backslash is an ordinary character | Processed, as in Databricks and Spark |
| Queries that start with `FROM`, such as `FROM t SELECT a` | Not supported | Supported |
| `USE DATABASE db` | Not supported. Use `USE db`. | Supported |
| Identifiers that start with a digit, such as `1st_col` | Not supported | Supported |

Backslash handling also changes regular expressions written as string literals. In Databricks mode `'\\d+'` reaches the regular expression engine as `\d+`, as it does in Spark, and `'\d+'` reaches it as `d+`. In SparkSQL mode each string arrives as written: `'\d+'` matches digits, and `'\\d+'` matches a literal backslash followed by `d`.

```text theme={"theme":{"light":"css-variables","dark":"css-variables"}}
Mode differences (dialect=spark vs dialect=databricks). Translation is shared:
same function catalog, function rewrites, types, zero-based subscripts, and table
functions. Only the grammar differs:
- spark only: `a DIV b`; typed literal suffixes 1Y 1S 1L 1.5F 1.5D 1.5BD; LONG type;
  CREATE TABLE ... USING <fmt> (ignored).
- databricks only: backslash escapes processed in '...' strings; FROM-first queries;
  USE DATABASE db; identifiers beginning with a digit (so 1L is a column name).
- databricks parses then rejects with an explanatory error: VERSION AS OF / TIMESTAMP
  AS OF (time travel), OPTIMIZE, USE CATALOG, USE SCHEMA. spark: parse error.
- Regex literals: in spark mode write '\d+' ('\\d+' matches a literal backslash);
  in databricks mode write '\\d+' ('\d+' is read as d+). A pattern such as '\\U'
  becomes \U in databricks mode, which RE2 rejects.
```

## Calling Firebolt functions

In either mode you get Spark's functions, not Firebolt's. A name Spark does not define is rejected, even if Firebolt has a function by that name:

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT city_hash('a');
-- function 'city_hash' not found

SELECT * FROM read_parquet('s3://bucket/data/*.parquet');
-- table function 'read_parquet' not found
```

To call a Firebolt function, qualify it with `firebolt`:

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT firebolt.city_hash('a');
SELECT * FROM firebolt.generate_series(1, 10);
```

This works for scalar, aggregate, window, and table functions. A qualified call runs exactly as it would in Firebolt mode: Spark's translation rules are skipped, so use Firebolt's name for the function and its argument order. `firebolt` is a namespace, not a schema: it qualifies function calls only, not tables or views.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.