Connecting to a BigQuery database

Last modified by Aurelie Bertrand on 2026/07/02 11:51

Requirements

  • A Google BigQuery account
  • Installation of the BigQuery JDBC driver: see the page BigQuery connector

Connecting to a BigQuery database

To retrieve data from a BigQuery database :

  1. In the Create a new data model box, click the BigQuery button in the Reports section.
    BigQuery_button.png
    ➡ The BigQuery dialog box appears.
  2. Select the desired connection mode.
  • Manual connection mode
    The connection will be visible in the data source only.
    1. Enter the connection information:
      • Host: The BigQuery host address.
      • Port: The database port.
      • Project: The name of your BigQuery project.
      • Authentication: The authentication method to use (see Authentication).
      • Options: Additional login options to configure authentication and/or login settings.
    2. Click the Connection button.
       
  • Named connection mode
    The connection will be visible in the connection manager and can be used by all data sources.
    1. Click Select: the Database Connection Manager is displayed.
    2. You can :
      • Select an existing connection from the list. Click OK.
      • Create a new connection as described on the Add a named connection.
    3. Click on the Connection button.
  1. In the Context Selection box that appears, select the catalogues you wish to import.
    ➡ The data loads.
  2. In the Query tab, create a query using the Wizard :
    • Select the table to be used in the left-hand panel.
      ➡ The corresponding columns are displayed in the right-hand panel.
    • You can remove columns by unchecking the corresponding boxes. The 1734709246622-278.pngbutton selects all.
    • You can define rules on columns: select the column then click the1734709446893-607.png button at the top right of the columns and define the rule. The rule is added to the table below the columns.
      BigQuery_connection_en.png
  3. Click Options if you wish to modify the advanced properties of the database. See the options paragraph below.
  4. In the Results tab, preview the results of the defined query. See the Preview page for more details.
  5. You can also add data transformations. See the Transform data page for more details.
  6. Once you are happy with the result, click Next to configure the data model.

Options

Query timeout

Maximum query duration in seconds (default is 60 seconds).
If set to 0, there is no maximum limit.

Row fetch sizeNumber of rows to be fetched from the database.
If set to 0, the number takes the JDBC driver "fetchsize" value.
Number of retries on error

Number of times the query is repeated in the event of an error.
If other than 0, the time between retries must be specified, in seconds.

Persist error on schedule

Used to generate a cube containing the error. The whole process will fail later, but this avoids having to redo the job (and being in error again) for all the tasks.

Incremental data mode

Incremental data mode is used to feed the cube incrementally: each time the cube is refreshed, only the new data is brought back compared with the previous refresh.
It is based on a reference column and the use of a keyword in the query: ${LASTVAL}.
For example: select ... where ref_col> '${LASTVAL}

The reference column is entered in the Reference column field below.

Editing the query

You can edit the query manually by selecting Advanced.... 
The query generated by the wizard is displayed.

  1. Click Edit.
    1769769893988-492.png
  2. Modify the query as required.
  3. Click Apply.

⚠ If you switch back to the wizard, the query will be overwritten.

You can save the query you have entered for later use. To do this:

  1. Click the Favorites button .
  2. Select Add to favorites then enter the name you want to give to this query. 
    ➡ The query will be available from the same Favorites menu.
    ​​​​​​​Favorites

It is also possible to access the modification history via the History button.
Query history

Authentication

There are several authentication methods for connecting to BigQuery.

Google Service Account (Service Auth)

For this authentication method, you will need to copy the .p12 or .json file to the DigDash server and specify two connection options using the Options button when configuring the connection:

  • OAuthServiceAcctEmail: The email address of your Google Service Account
  • OAuthPvtKeyPath: The path to the .p12 or .json file on the DigDash server

Pre-generated tokens

For this authentication method, you will need to specify several connection options via the options button when configuring the connection, the first option denotes whether you will be using an access or refresh token:

  • OAuthAccessToken or OAuthRefreshToken: The access token or the refresh token respectively.

If you are using the refresh token you will also need :

  • OAuthClientId: The client identifier.
  • OAuthClientSecret: The client secret.

Default application identifiers

For this authentication method, make sure you have configured the "Application Default Credentials" on the "Google Identity Platform". No connection options are required.

Troubleshooting: advanced query

Depending on the data, doing a SELECT * on a BigQuery table in an advanced query will generate an error. This is due to metadata or data types specific to google.
To avoid this problem, you need to do a CAST as STRING on each column.
For example:
cast(ticket_id as STRING) as ticket_id
Only STRING, DATE and INT64 are available for CAST.
If you need a date in DATETIME format, you can CAST it as a STRING and then convert it in DigDash.