Google BigQuery Overview

The ElastiCube Manager provides you with quick and easy access to Google BigQuery tables enabling you to generate and analyze your data.

There are two connection types you can configure when connecting to your BigQuery tables, DSN and Connection String (DSN-Less). For more information about the differences between the connection types, click here.

To connect to your BigQuery tables, follow these steps:

  1. Download the Sisense BigQuery ODBC driver and connect the data source in the ElastiCube Manager.
  2. Add a DSN.
  3. (Optional) Configure advanced DSN configurations.
  4. (Optional) Configure logging settings.
  5. Add the BigQuery tables to your project.

Downloading and Connecting the Sisense Google BigQuery ODBC Driver

  1. Download and install the Sisense BigQuery ODBC Driver.
  2. In ElastiCube Manager, click Add Data and then, Generic ODBC Driver.
  3. Select DSN. If a DSN file has already been created, select it in the dropdown list, and click Test Connection. If your connection has been set up, you can click OK and review the tables generated by the Sisense BigQuery ODBC driver.

    OR
    Select Connection String (DSN-Less) and enter your Connection String. Click Test Connection.  For more information, see Connecting without a DSN.

Adding a DSN

If a DSN has not yet been created, you must add the DSN manually.

Note: To add a DSN you must run the Sisense ElastiCube Manager as an administrator.

To add a DSN:

  1. Click Add DSN.
    CreateNewDataSource
  2. Select the System Data Source option. The created file applies to all users in a specific machine only. Click Next.
  3. Select the Sisense Google BigQuery ODBC Driver, and click Next.
    CreateNewDataSource1
  4. Click Finish. The Simba ODBC Driver for Google BigQuery DSN Setup dialog box opens.
  5. Enter the following information:
    In the Data Source Name field, type a name for your DSN.
    Optionally, in the Description field, type relevant details about the DSN.
    Configure authentication using one of the following methods:
    To authenticate the connection as a service:

    1. From the OAuth Mechanism list, select Service Authentication.
    2. In the Email field, type your service account email ID.
    3. In the Key File Path field, type the path to the .p12 key file that is used to authenticate the service account ID.

    Or, to authenticate the connection as a user:

    1. From the OAuth Mechanism dropdown list, select User Authentication.
    2. Click Sign In.
    3. In the browser that opens, type your credentials for accessing your BigQuery data, and then click Sign In.
    4. When prompted to allow BigQuery Client Tools to access your data in Google BigQuery, click Accept.
    5. Copy the code that Google provides, and then paste the code in the Confirmation Code field in the Simba ODBC Driver for Google BigQuery DSN Setup dialog box.
      When you paste the confirmation code, the Refresh Token populates automatically when you select the field. The refresh token is used whenever the driver needs to access your BigQuery data. You can save the refresh token in the DSN so that you only need to generate it once.

    Note: A confirmation code can only be used once. You must get a new confirmation code from Google whenever you need another refresh token.

  6. To allow the driver to access Google Drive so that it can support federated tables that combine BigQuery data with data from Google Drive, select the Request Google Drive Scope Access check box.
  7. Choose one:
    • To verify the server using the trusted CA certificates from a specific .pem file, specify the full path to the file in the Trusted Certificates field and leave the Use System Trust Store check box cleared.
    • Or, to use the trusted CA certificates .pem file that is installed with the driver, leave the default value in the Trusted Certificates field and the Use System Trust Store check box cleared.
    • Or, to use the Windows Trust Store, select the Use System Trust Store check box and leave the Trusted Certificates field cleared.
  8. In the Catalog (Project) dropdown list, select the name of your BigQuery project. This project is the default project that the Simba ODBC Driver for Google BigQuery queries against, and also the project that is billed for queries that are run using the DSN.
  9. (Optional) To configure advanced driver options, click Advanced Options. For more information, see Advanced Options.
  10. (Optional) To configure logging behavior for the driver, click Logging Options. For more information, see Logging Options.
  11. Click Test. A pop window is displayed that indicates the connection was successful.
  12. Click OK.
  13. In the ODBC Connectivity Properties window, click Test Connection.
  14. Click OK to save and close the ODBC Data Source Administrator.

Advanced Options

You can configure advanced driver by clicking Advanced Options in the Sisense BigQuery ODBC Driver DSN Setup window. This displays the Advanced Options window from which you can configure the following options:

bigqueryadvancedoptions

 

FieldDescription
Max Requests Per Second (0=unlimited)Enter the maximum number of requests that can be made per second.

Note: To allow an unlimited number of requests per second with no throttling, type 0.
Rows Per BlockEnter the maximum number of rows to fetch for each data request.
Default String Column LengthEnter the maximum data length for String columns.
Dataset Name For Large Result SetsEnter the name of the BigQuery dataset to use to store temporary tables.

Note: The dataset created from the default ID is hidden.
Temporary Table Expiration Time(ms)Enter the length of time (in milliseconds) that the temporary table exists for.
Use Native QuerySelect the Use Native Query checkbox to disable the SQL Connector feature and allow the driver to execute BigQuery SQL directly.

When Use Native Query is selected, the driver does not transform the queries emitted by an application, so the native query is used.

When Use Native Query is disabled, the driver transforms the queries emitted by an application and converts them into an equivalent form in BigQuery SQL.

Note: If an application already emits BigQuery SQL, then enable this option to avoid the extra overhead of query transformation. (Optional)
Enable SQLPrepare Metadata with Native Query (slower)To enable the driver to retrieve metadata during the prepare stage of a query when working in Native Query mode, select the Enable SQLPrepare Metadata with Native Query checkbox.

Note: This option is available only if the Use Native Query checkbox is selected.
Allow Large Result SetsSelect to to allow query results larger than 128MB in size.

After you select this checkbox, the Dataset Name For Large Result Sets and Temporary Table Expiration Time(ms) fields are enabled.

 

Logging Options

You can configure logging options by clicking Logging Options in the Sisense BigQuery ODBC Driver DSN Setup window. This displays the Logging Options window.

bigqueryloggingoptions

The ODBC Data Source Administrator provides tracing functionality, which you can activate to help troubleshoot issues.

Important: Only enable logging long enough to capture an issue. Logging decreases performance
and can consume a large quantity of disk space.

The driver allows you to set the amount of detail included in log files. The table below  lists the logging levels provided by the Sisense ODBC Driver with SQL Connector for Google BigQuery, in order from least verbose to most verbose.
In the Logging Options window, you can configure the following information:

FieldDescription
Log LevelSelect the Log Level. There are seven possible options:

LOG_OFF: Disables all logging.
LOG_FATAL: Logs very severe error events that lead the driver to abort.
LOG_ERROR: Logs error events that might still allow the driver to continue running.
LOG_WARNING:Logs potentially harmful situations.
LOG_INFO: Logs general information that describes the progress of the driver.
LOG_DEBUG: Logs detailed information that is useful for debugging the driver.
LOG_TRACE: Logs more detailed information than the DEBUG level.
Log PathEnter the full path to the folder where you want to save log files.
OR
Click Browse and select the folder where you want to save log files.
Log RotationEnter the maximum number of log files to keep in the Max Number Files field.

Note: After the maximum number of log files is reached, each time an additional file is created, the driver deletes the oldest log file.

Enter the maximum size of each log file in megabytes (MB) in the Max File Size field.

Note: After the maximum file size is reached, the driver creates a new file and continues logging.

Connecting without a DSN

When you choose to connect without a DSN, you must define the connection string that sets the configuration options.

The following is an example connection string that sets advanced options:

DSN=Sample Sisense BigQuery DSN; Catalog=public_data; MaxRequestsPerSecond=20; UseNativeQuery=0; AllowLargeResults=0; LargeResultsDataSetId=_bqodbc_temp_tables; LargeResultsTempTableExpirationTime=3600000

The table below provides a list of possible keys you can add to your string and their descriptions:

Key NameMandatoryDescription
AllowLargeResultsNoThe value of this key is 0 or 1.

0: Disabled. The driver returns an error when query results are larger than 128MB in size.
1: Enabled. The driver allows query results that are larger than 128MB in size.
ExecCatalogYesThe name of the project to bill for queries that are run using the DSN.
Project or CatalogYesThe name of your BigQuery project.
LargeResultDataSetIdYes*The ID of the BigQuery dataset to use to store temporary tables.

Note: This option is available only when the Allow Large Result Sets option is enabled (the AllowLargeResults key is set to 1). The dataset created from the default ID is hidden.

*Required if AllowLargeResults is enabled.
DefaultStringColumnLengthNoThe maximum data length for STRING columns.
UseNativeQueryMetadataNoThe value of this key is 0 or 1.

0: Disabled. The driver does not retrieve metadata during the prepare stage.
1: Enabled. The driver retrieves metadata during the prepare stage of a query when working in Native Query mode.

Note: This option is available only when UseNativeQuery key is set to 1.
MaxRequestsPerSecondNoThe maximum number of requests that can be made per second. To allow an unlimited number of requests per second with no throttling, set the value to 0.
ProxyHostNoThe IP address or hostname of the proxy server. If this key is not set for the Linux or Mac OS X driver, then support for connecting to proxy servers is disabled.
ProxyPwdYes*The password corresponding to the user name provided in the Proxy Username field (the ProxyUid key).

*Required if connecting to a proxy server.
ProxyPortYes*The number of the port on which the proxy server is listening.

*Required if connecting to a proxy server.
ProxyUidYes*The user name that you use to access the proxy server.

*Required if connecting to a proxy server.
RefreshTokenYesThe refresh token that you obtain from Google for authorizing access to BigQuery. When you configure a DSN with the Windows driver, the refresh token is generated automatically after you provide the confirmation code. When you configure a DSN with the Linux or Mac OS X versions of the driver, you must use the Refresh Token configuration tool to generate the token.
RowsFetchedPerBlockNoThe maximum number of rows that the driver can fetch for each data request.
LargeResultsTempTable ExpirationTimeYes*The length of time (in milliseconds) for which a temporary table exists.

Note: This option is available only when AllowLargeResults key is set to 1. The default value is 1 hour in milliseconds.

*Required if AllowLargeResults is enabled.
UseNativeQueryNoThe value of this key is 0 or 1.

0:Disabled. The driver transforms the queries emitted by an application and converts them into an equivalent form in BigQuery SQL.
1: Enabled. The driver does not transform the queries emitted by an application, so the native query is used.

Note: If an application already emits BigQuery SQL, then enable this option to avoid the extra overhead of query transformation.

Adding BigQuery Tables to your Project

After setting up the DSN or the DSN-less connection, the Add table from BigQuery Tables window is displayed.

 

From this window, you add your BigQuery tables to your ElastiCube Manager. In addition, you can view the SQL syntax in the Query Preview section and click Edit to customize it.

To add BigQuery Tables to your Project:

  1. Connect to Google BigQuery via the Sisense ODBC Tool.
  2. Add your data source.
  3. In the Add table from BigQuery Tables window, select the table you want to add to the ElastiCube Manager.
  4. Click Add. The selected tables are added to your ElastiCube Manager.