Skip to main content
Preview BigQuery compatibility mode lets you run BigQuery GoogleSQL against Firebolt without rewriting it. When the mode is on, Firebolt parses each statement with BigQuery grammar and translates it to native Firebolt SQL, so BigQuery query syntax, functions, and type names work as written. Use it to point existing BigQuery queries and tools at Firebolt, and to migrate a workload incrementally: run the BigQuery SQL you already have, then adopt native Firebolt SQL where you want to.

​
What works

  • Query syntax: SELECT, FROM, joins, UNNEST, set operations with CORRESPONDING, QUALIFY, GROUP BY ALL, GROUPING SETS, ROLLUP, CUBE, CTEs, and SELECT * EXCEPT.
  • Query parameters: named @name parameters. See Parametrized queries for passing parameter values from different client SDKs.
  • Pipe syntax: the |> pipe operator, including WHERE, SELECT, EXTEND, AGGREGATE, ORDER BY, LIMIT, JOIN, and set operations.
  • Data types: INT64, FLOAT64, NUMERIC, BOOL, STRING, BYTES, DATE, DATETIME, TIMESTAMP, GEOGRAPHY, JSON, ARRAY, and STRUCT.
  • Operators: arithmetic, comparison, and logical operators, IS DISTINCT FROM, IN UNNEST, JSON ->, ARRAY [], and AT TIME ZONE.
  • Functions:
    • Date and time: the DATE_, DATETIME_, TIMESTAMP_, FORMAT_, PARSE_, and UNIX_ families, plus type constructors.
    • String: SPLIT, CONTAINS_SUBSTR, INSTR, REGEXP_CONTAINS, REGEXP_EXTRACT, BYTE_LENGTH, and more.
    • JSON: JSON_VALUE, JSON_QUERY, the _ARRAY variants, typed accessors, and the -> operator.
    • Array: GENERATE_ARRAY, GENERATE_DATE_ARRAY, array literals, and lambdas.
    • Aggregate and approximate: standard and statistical aggregates, STRING_AGG, COUNTIF, LOGICAL_AND, LOGICAL_OR, and the HLL_COUNT family.
    • Math: DIV, SAFE_DIVIDE, PARSE_NUMERIC, trigonometric helpers, and native math functions.
    • Geography: ST_GEOGPOINT, ST_GEOGFROMTEXT, ST_CONTAINS, ST_DISTANCE, ST_INTERSECTS, and more.
    • Conditional and conversion: IF, IFNULL, NULLIF, COALESCE, CASE, the single-argument type functions, and CAST.
  • Statements: CREATE TABLE, CREATE VIEW, CREATE SCHEMA, DROP, ALTER TABLE, INSERT, UPDATE, DELETE, MERGE, and TRUNCATE TABLE.
  • Information schema: see below for supported information_schema views.

​
Information schema

A supported view follows BigQuery’s INFORMATION_SCHEMA: its column names, order, and data types. Supported views: Any INFORMATION_SCHEMA view not listed does not exist in BigQuery mode: querying it raises a “relation does not exist” error.

​
Addressing

A supported view is reachable by the BigQuery dataset-qualified forms as well as the bare form:
  • INFORMATION_SCHEMA.TABLES: the current database, all datasets.
  • <dataset>.INFORMATION_SCHEMA.TABLES: one dataset (table_schema = '<dataset>').
  • <project>.<dataset>.INFORMATION_SCHEMA.TABLES: <project> selects the database, <dataset> the schema.
Region-qualified forms (`region-us`.INFORMATION_SCHEMA.*) are rejected for dataset-scoped views. The JOBS family is region-scoped in BigQuery (`region-us`.INFORMATION_SCHEMA.JOBS); Firebolt has no regions, so the region qualifier is accepted and ignored, and the bare form works too.

​
Notes

  • Type-valued columns carry Firebolt spellings, not BigQuery’s: tables.ddl returns Firebolt DDL, columns.data_type returns Firebolt type names (BIGINT, TEXT), views.view_definition returns Firebolt DDL, and tables.table_type may carry Firebolt-specific values (for example EXTERNAL) for objects with no BigQuery equivalent.
  • pg_catalog has no BigQuery equivalent and is not available in BigQuery mode; referencing it raises a “relation does not exist” error.

​
Enable the mode

Set the dialect session setting to bigquery:
Every statement in the session is then read as BigQuery GoogleSQL. You can also pass it as an HTTP query parameter (?dialect=bigquery) or in a WITH (dialect='bigquery') clause. Return to native Firebolt SQL with:
The default is firebolt. Only bigquery selects BigQuery mode.