Google Bigquery

DSS can connect to Google BigQuery through a JDBC driver developed by Simba.

Warning

  • Java 8 is required

Supported

  • Creating external datasets targeting BigQuery projects
  • Reading data across all datasets and tables.
  • Reading all BigQuery data types
  • SQL notebook for interactive querying
  • SQL query datasets
  • SQL query recipes, with inputs and outputs in BigQuery
  • Sync query recipes, with output in BigQuery and input in either Google Cloud Storage or BigQuery.
  • All visual recipes (Group, Join, VStack, Window, Filter executed in BigQuery), with inputs and outputs in BigQuery
  • Charts with DSS and In-Database engine modes.

Note

If your data are not stored in BigQuery, you first need to upload them into a Google Cloud Storage (GCS) bucket. Once they are into GCS, you can use a Sync recipe to transfer them into BigQuery.

Not supported

  • INSERT / CREATE statements in SQL notebook
  • SQL script recipes
  • BigQuery time partitioning experimental feature
  • BigQuery wildcard tables
  • BigQuery BYTES, DATETIME, DATE, TIME, ARRAY, STRUCT data types

Installing the JDBC driver

Warning

The 1.1.8.1010 version of the JDBC driver contains a bug that prevents any error message related to connection from displaying, making investigation of connection issues extremely difficult.

We recommend that you use version 1.2.1.1001 of the driver, which is unaffected.

The JDBC driver is made of many JAR files, all of which are contained in the Zip file.

  • Copy all of the JAR files to the lib/jdbc folder
  • Restart DSS

Connecting to BigQuery

DSS connects to BigQuery using Service Account-based authentication.

  • You first need to create a Google Service Account
  • Create a private key for this account, and download the corresponding JSON file.
  • Upload the JSON file somewhere on the DSS server.

In the connection settings, in the Secret key field, enter the absolute path (on the DSS server) to the credentials JSON file.

Alternatively, you can directly enter the content of the JSON file in the Secret key field to avoid storing the file on the server. Keep in mind that in this latter case, any DSS administrator will be able to see the content of this private file.

Finally we recommend that you add an Advanced JDBC property:

  • Key: Timeout
  • Value: 180

If you need to connect to BigQuery via a Proxy, you must add the following advanced JDBC properties (and also configure the global proxy for DSS in the “Settings” tab of the Administration page):

  • Key: ProxyHost
  • Value: The IP address or host name of your proxy server.
  • Key: ProxyPort
  • Value: The listening port of your proxy server.
  • Key: ProxyUser
  • Value: The user name, if needed, for proxy server settings.
  • Key: ProxyPassword
  • Value: The password, if needed, for proxy server settings.