jdbc:bigquery://
No
Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes| Field | Description |
|---|---|
| Data source | Google BigQuery |
| Supported versions | 1.6.5.1002 |
| Connection string | jdbc:bigquery:// |
| Packaged? | No |
| Certified? | Yes |
| Supported features | |
| Estimate job | Yes |
| Analyze data | Yes |
| Schedule | Yes |
| Processing capabilities | |
| Pushdown | Yes |
| Spark agent | Yes |
| Yarn agent | Yes |
| Parallel JDBC | Yes |
| Java Platform version compatibility | |
| JDK 8 | Yes |
| JDK 11 | Yes |
The names of these permissions are subject to change. See the official BigQuery documentation.
The names of these permissions are subject to change. See the official BigQuery documentation.
Yes | Name | Text | The unique name of your connection. Ensure that there are no spaces in your connection name. |
| Yes | Connection URL | String | The connection string path of your BigQuery connection.
When referring to the example below, replace the ${value} sections of the connection URL with your actual value.
:glasses-round: Example: jdbc:bigquery://
Yes | Driver Name | String | The driver class name of your BigQuery connection.
com.simba.googlebigquery.jdbc42.Driver
Yes | Port | Integer | The port number to establish a connection to the datasource.
The default port is 443
No | Limit Schemas | Option | Allows you to manage usage and restrict visibility to only the necessary schemas in the Explorer tree.
See Limiting schemas to learn how to limit schemas from the Connection Management page.
When you include a restricted schema in the query of a DQ Job, the query scope may be overwritten when the job runs. While only the schemas you selected when you set up the connection are shown in the Explorer menu, users are not restricted from running SQL queries on any schema from the data source.
No | Source Name | String | N/A |
| No | Target Agent | Option | The [Agent](/data-quality-self-hosted/installation/agent.md) that submits your Spark job for processing. |
| Yes | Auth Type | Option | The method to authenticate your connection.
The configuration requirements differ depending on the Auth Type you select. See Authentication for more details on available authentication types.
No | Properties | String | The configurable driver properties for your connection. Multiple properties must be semicolon-delimited, and each property=value pair, including the last one, must end with a semicolon (;). `abc=123;test=true;` |
| {% endtab %} | | | |
{% tab title="CDATA" %}
## General information
| Field | Description |
|---|---|
| Data source | Google BigQuery |
| Supported versions | 21.0.8160.0 |
| Connection string | jdbc:googlebigquery: |
| Packaged? | Yes |
| Certified? | Yes |
| Supported features | |
| Estimate job | Yes |
| Analyze data | Yes |
| Schedule | No |
| Processing capabilities | |
| Pushdown | Yes |
| Spark agent | Yes |
| Yarn agent | No |
| Parallel JDBC | Yes |
| Java Platform version compatibility | |
| JDK 8 | Yes |
| JDK 11 | Yes |
The names of these permissions are subject to change. See the official BigQuery documentation.
The names of these permissions are subject to change. See the official BigQuery documentation.
Yes | Name | Text | The unique name of your connection. Ensure that there are no spaces in your connection name. |
| Yes | Connection URL | String | The connection string path of your BigQuery connection.
When referring to the example below, replace the ${value} sections of the connection URL with your actual value.
:glasses-round: Example: jdbc:googlebigquery:AuthScheme="OAuthJWT";OAuthSettingsLocation=memory://;ProjectId=${projectId};InitiateOAuth="GETANDREFRESH";OAuthJWTCert="/tmp/keytab/bigquery.json";OAuthJWTCertType="GOOGLEJSON";OAuthJWTIssuer="iam.service.account";BrowsableSchemas=\
The BrowsableSchemas parameter enables you to limit which datasets/schemas are loaded for a project. Replace \ with the actual values (comma-delimited).
Yes | Driver Name | String | The driver class name of your BigQuery connection.
cdata.jdbc.googlebigquery.GoogleBigQueryDriver
Yes | Port | Integer | The port number to establish a connection to the datasource.
The default port is 443
No | Limit Schemas | Option | Allows you to manage usage and restrict visibility to only the necessary schemas in the Explorer tree.
See Limiting schemas to learn how to limit schemas from the Connection Management page.
When you include a restricted schema in the query of a DQ Job, the query scope may be overwritten when the job runs. While only the schemas you selected when you set up the connection are shown in the Explorer menu, users are not restricted from running SQL queries on any schema from the data source.
No | Source Name | String | N/A |
| No | Target Agent | Option | The [Agent](/data-quality-self-hosted/installation/agent.md) that submits your Spark job for processing. |
| Yes | Auth Type | Option | The method to authenticate your connection.
The configuration requirements differ depending on the Auth Type you select. See Authentication for more details on available authentication types.
No | Properties | String | The configurable driver properties for your connection. Multiple properties must be semicolon-delimited, and each property=value pair, including the last one, must end with a semicolon (;). `abc=123;test=true;` |
| {% endtab %} | | | |
| {% endtabs %} | | | |
## Authentication
Select an authentication type from the dropdown menu. The options available in the dropdown menu are the currently supported authentication types for this data source.
{% tabs %}
{% tab title="Username/Password" %}
| Required | Field | Description | |
| ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Yes | Username | The username of your BigQuery service account.
Set Username to any value.
| | | Yes | Password | The password of your BigQuery service account.
Enter the base64 encoded string of your OAuth key.
base64 encoded commands
To encode your credential JSON file as a base64 encoded string, use one of the following commands:
base64 your\_json.json -w 0 or cat your\_json.json | base64 -w 0
Your JSON file is typically available to download in GCP when you create a service account in your IAM profile.
Yes | Username | The username of your BigQuery service account.
Set Username to any value.
| | Yes | Script | The file path that contains the script file that the password manager uses to interact with and authenticate a user account.
:glasses-round: Example: /tmp/keytab/bigquery\_pwd\_mgr.sh
No | Param $1 | Optional. An additional parameters to authenticate your BigQuery connection. |
| No | Param $2 | Optional. An additional parameter to authenticate your BigQuery connection. |
| No | Param $3 | Optional. An additional parameter to authenticate your BigQuery connection. |
| {% endtab %} | | |
{% tab title="Service Account Credential" %}
| Required | Field | Description |
|---|---|---|
| Yes | JSON Credential File | The JSON file containing service account access credentials from your local file storage. Click anywhere in the Service Account Credential field, then upload the JSON credential file containing your Google IAM service account key. Plain text JSON example The JSON file contains the following service account information: Your JSON file is typically available to download in GCP when you create a service account in your IAM profile. The cdata.jdbc.googlebigquery.GoogleBigQueryDriver driver does not support JSON Credential File authentication. |
Yes | Workload Identity | Refer to Configuring Workload Identity for Google BigQuery for information on how to set it up.
Because the Connection URL contains the Project ID associated with the GCP cluster that was set up with the access grant, you only need to select Workload Identity from the Authentication Type dropdown menu, specify an agent, and fill out the other required fields in the connection template once you configure Workload Identity.
| | {% endtab %} | | | | {% endtabs %} | | | ## Views When creating views from different datasets (collections), you can add the `viewsEnabled=true` parameter to the connection property when defining the connection. For read/write access to BigQuery, you can use the Spark BigQuery connector. To use this connector, ensure that you have the following configurations: * Set `viewsEnabled=true`. * Set `materializationDataset` to a dataset where the GCP user has table creation permission. * Optionally set `materializationProject`. The connector is implemented by running the query on BigQuery and the results are saved into a temporary table. Spark will then read the results, which could result in additional costs on your BigQuery account. {% hint style="info" %} `WITH` clauses with common table expressions (CTEs) improve query readability but do not guarantee better performance. Adding a `WITH` clause does not ensure that BigQuery will create temporary intermediate tables or reuse the results for multiple references. The `WITH` clause may be evaluated multiple times within a query, depending on the query optimizer's decisions. {% endhint %} ## Cross-project connections Use this configuration when the project that owns the data differs from the project where jobs are billed and executed. A common scenario is when a service account lives in a processing or governance project but needs to read data from a separate data project. In the Simba BigQuery JDBC driver, `ProjectId` controls where jobs are created and billed, not which project's data is queried. Because of this, the service account must have `bigquery.jobs.create` permission in the project set as `ProjectId`. To access data from a different project, use `AdditionalProjects` and `QueryProperties`. {% hint style="info" %} The CDATA driver does not support this cross-project configuration. Use the Simba (native) driver for cross-project connections. {% endhint %} ### URL parameters for cross-project connections| Parameter | Role |
|---|---|
ProjectId | The billing/processing project where jobs are created. The service account must have bigquery.jobs.create here. |
AdditionalProjects | The data project(s) whose datasets should be accessible. |
QueryProperties=dataset_project_id | Sets the default dataset context so DQ resolves schemas against the correct project. |
BillingProjectId | Not required when ProjectId is already set to the billing project. |
| Connection property | Description |
|---|---|
AllowLargeResults | When set to true, this option allows results larger than the maximum response size when reading large tables. |
QueryPassthrough | When set to true, this option passes the query to the Google BigQuery server as is, which can help overcome some SQL syntax errors for valid SQL queries. |