Connecting to Google BigQuery

Google BigQuery is a cloud data service. You can use the Google BigQuery data connector to import your organization's BigQuery data.

Before you start

To connect to Google BigQuery, you must gather the following:

• authentication details
• catalog

For help gathering the connection prerequisites, contact the Google BigQuery administrator in your organization. If your administrator cannot help you, you or your administrator should contact Google BigQuery Support.

Create a BigQuery connection

1. From the Analytics main menu, select Import > Database and application.
2. From the New Connections tab, in the ACL Connectors section, select Google BigQuery.

   Tip

   You can filter the list of available connectors by entering a search string in the Filter connections box. Connectors are listed alphabetically.

3. In the Data Connection Settings panel, enter the connection settings and at the bottom of the panel, click Save and Connect.

   You can accept the default Connection Name, or enter a new one.

The connection for Google BigQuery is saved to the Existing Connections tab. In the future, you can reconnect to Google BigQuery from the saved connection.

Once the connection is established, the Data Access window opens to the Staging Area and you can begin importing data. For help importing data from Google BigQuery, see Working with the Data Access window.

Connection settings

Basic settings

Setting	Description	Example
OAuth Mechanism	Specifies the authentication mechanism to authenticate the driver: User Authentication - The driver authenticates as a user, through a Google user account. Service Authentication - The driver authenticates as a service, through a Google service account. When using User Authentication, click Login to access the Google Sign in page.	User Authentication
Confirmation Code	The code that you obtain from Google for generating a refresh token.
Refresh Token	The refresh token that you obtain from Google for authorizing access to BigQuery. The refresh token is generated automatically after you provide the confirmation code.
Email	When using Service Authentication, provide the service account email ID.
Key File Path	When configuring Service Authentication, set this option to the full path to the .p12 or .json key file that is used to authenticate the service account email address.
Catalog (Project)	The name of your Google BigQuery project.

Advanced settings

Setting	Description	Example
Proxy Host	The hostname or IP address of a proxy to route HTTP traffic through. The driver can use the HTTP, Windows (NTLM), or Kerberos authentication types to authenticate to an HTTP proxy.
Proxy Port	The TCP port the proxy server is running on. The default value is 80.	80
Proxy Username	A user name to be used to authenticate to the proxy server.
Proxy Password	The password to authenticate to the proxy server.
Max Requests Per Second (0=unlimited)	Provide the maximum number of requests that can be made per second. To allow an unlimited number of requests per second with no throttling, type 0.	0
Row Per Block	The maximum number of rows that the driver must fetch for each data request.	16384
Default String Column Length	The maximum number of characters to be contained in STRING columns.	65536
Dataset Name for Large Result Sets	ID of the BigQuery dataset to use to store temporary tables for large result sets. Specify a value for this option only if you want to enable support for large result sets. This field is enabled only if you select the Allow Large Result Sets option.	_odbc_temp_tables
Temporary Table Expiration Time (ms)	Time (in seconds) until the temporary table expires. To have the table set to never expire, specify the value 0.	3600000
Language Dialect	Specifies whether the driver executes queries using standard SQL syntax or the legacy BigQuery SQL syntax. Enabled - Standard SQL syntax Disabled - Legacy BigQuery SQL syntax
Enable SQLPrepare Metadata with Legacy SQL (slower)	Specifies whether to use BigQuery's legacy SQL dialect for this query.
Allow Large Result Sets	Specifies whether to query results larger than 128MB when using Legacy SQL.

Data connector updates

When you upgrade Analytics, the Robots Agent, or AX Server, you should test any of your scripts that import data using one of the Analytics data connectors (ACCESSDATA command).

The possibility exists that changes made by third-party data sources or ODBC driver vendors required updates to one or more of the data connectors. Scripted data connections may need to be updated in order to continue working correctly.

• Re-run the import The easiest way to update a connection is to manually perform an import using the Data Access window in the upgraded version of Analytics. Copy the ACCESSDATA command from the log and use it to update your script.

  Note

  Before connecting to a data source and re-running the import, clear the connector cache to flush the existing set of table names.

  In the Existing Connections tab in the Data Access window, beside the connector name, select > Clear cache.

• Update field specifications You may also need to update field specifications in the script body to align with table schema changes in the data source or ODBC driver. Possible changes include field names, field data types, and field and record lengths.
• Check the results of any filtering You should also check the results of any filtering that you apply as part of the data import. Confirm that the import filtering is including and excluding records correctly.