Big Query SQL Dialect User Guide

July 27, 2026 · View on GitHub

The Big Query SQL dialect allows you connecting to the Google Big Query, Google's serverless, enterprise data warehouse.

Telemetry

This virtual schema uses telemetry-java to send anonymous feature-usage events.

For details on what is collected and how to disable telemetry, see the documentation.

JDBC Driver

Download the Simba JDBC Driver for Google BigQuery.

Note: Google offers an alternative JDBC Driver for BigQuery. We have not tested this driver and recommend using the Simba JDBC Driver.

Uploading the JDBC Driver to BucketFS

  1. Create a bucket in BucketFS
  2. Upload the driver to BucketFS

When uploading the archive to the bucket then the Exasol database will automatically extract the contents of the archive and your UDF can access the files using the following path pattern <your bucket>/<archive's name without extension>/<name of a file from the archive>.jar.

See the Exasol documentation for accessing BucketFS.

Leave only .jar files in the archive. It will help you to generate a list for adapter script later.

Installing the Adapter Script

Upload the latest available release of Big Query Virtual Schema to BucketFS.

Then create a schema to hold the adapter script.

CREATE SCHEMA SCHEMA_FOR_VS_SCRIPT;

The SQL statement below creates the adapter script, defines the Java class that serves as entry point and tells the UDF framework where to find the libraries (JAR files) for Virtual Schema and database driver.

List all the JAR files from the JDBC driver.

CREATE JAVA ADAPTER SCRIPT SCHEMA_FOR_VS_SCRIPT.ADAPTER_SCRIPT_BIGQUERY AS
    %scriptclass com.exasol.adapter.RequestDispatcher;
    %jar /buckets/<BFS service>/<bucket>/virtual-schema-dist-14.0.4-bigquery-4.0.1.jar;
    %jar /buckets/<BFS service>/<bucket>/GoogleBigQueryJDBC42.jar;
    ...
    ...
    ...
/
;

Hint: to avoid filling the list by hands, use a convenience UDF script bucketfs_ls. Create a script and run it as in the following example:

SELECT '%jar /buckets/<BFS service>/<bucket>/<archive's name without extension if used>/'|| files || ';' FROM (SELECT EXA_toolbox.bucketfs_ls('/buckets/<BFS service>/<bucket>/<archive's name without extension if used>/') files );

Defining a Named Connection

Please follow the Authenticating to a Cloud API Service article to get Google service account credentials.

Upload the key as a JSON file to BucketFS, then create a named connection:

CREATE OR REPLACE CONNECTION BIGQUERY_JDBC_CONNECTION
TO 'jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=<your project id>;OAuthType=0;OAuthServiceAcctEmail=<service account email>;OAuthPvtKeyPath=/<path to the bucket>/<name of the key file>';

You can find additional information about the JDBC connection URL in the Big Query JDBC installation guide.

Creating a Virtual Schema

Below you see how a Big Query Virtual Schema is created. Please note that you have to provide the name of a catalog (=the project name) and the name of a schema (=the dataset name).

CREATE VIRTUAL SCHEMA <virtual schema name>
    USING SCHEMA_FOR_VS_SCRIPT.ADAPTER_SCRIPT_BIGQUERY
    WITH
    CONNECTION_NAME = 'BIGQUERY_JDBC_CONNECTION'
    CATALOG_NAME = '<project name>'
    SCHEMA_NAME = '<dataset name>';

Data Types Conversion

BigQuery Data TypeSupportedConverted Exasol Data TypeKnown limitations
BOOL/ BOOLEAN✓BOOLEAN
DATE✓DATE
DATETIME✓TIMESTAMP
FLOAT / FLOAT64✓DOUBLEExpected range for correct mapping: -99999999.99999999 .. 99999999.99999999.
GEOGRAPHY✓GEOMETRY
INTEGER / INT64✓DECIMAL
BIGNUMERIC✓DOUBLE PRECISIONExpected range for correct mapping: -99999999.99999999 .. 99999999.99999999.
NUMERIC✓DOUBLE PRECISIONExpected range for correct mapping: -99999999.99999999 .. 99999999.99999999.
STRING✓VARCHAR(65535)
TIME✓VARCHAR
TIMESTAMP✓TIMESTAMPExpected range for correct mapping: 1582-10-15 00:00:01 .. 9999-12-31 23:59:59.9999. JDBC driver maps dates before 1582-10-15 00:00:01 incorrectly. Example of incorrect mapping: 1582-10-14 22:00:01 -> 1582-10-04 22:00:01
BYTES×
STRUCT×
ARRAY×
JSON×
INTERVAL×

Known Limitations

Performance

Please be aware that the current implementation of the dialect can only handle result sets with limited size (a few thousand rows).

If you need to process a large amount of data, please contact the Exasol support team. Another implementation of the dialect with a performance improvement (using IMPORT INTO) is available, but not documented for self-service because of:

  1. the complex installation process
  2. security risks (a user has to disable the driver's security manager to use it)

Mapping of Empty Result

If a query returns an empty result set, the Virtual Schema will map all columns to type SMALLINT.

Testing information

In the following matrix you find combinations of JDBC driver and dialect version that Exasol developer have tested successfully:

Virtual Schema VersionBig Query VersionDriver NameDriver Version
1.0.0Google BigQuery 2.0Magnitude Simba JDBC driver for BigQuery1.2.2.1004
2.0.2Google BigQuery 2.0Magnitude Simba JDBC driver for BigQuery1.2.25.1029
2.2.3Google BigQuery 2.0Magnitude Simba JDBC driver for BigQuery1.5.0.1001
3.0.5Google BigQuery 2.0Magnitude Simba JDBC driver for BigQuery1.6.2.1003
4.0.0Google BigQuery 2.0Magnitude Simba JDBC driver for BigQuery1.7.0.1001