Use the JDBC driver for BigQuery

The Java Database Connectivity (JDBC) driver for BigQuery connects your Java application to BigQuery, enabling you to use BigQuery features with your preferred tooling and infrastructure. This driver is designed specifically for BigQuery and can't be used with other products or services. You don't need any additional licenses to use this driver.

Before you begin

Make sure that you're familiar with JDBC drivers and the java.sql package.
Verify that your system is configured with Java Runtime Environment (JRE) 8.0 or later.

Configure the driver

To configure the JDBC driver for BigQuery, you must install the driver, authenticate to BigQuery, and establish a connection.

Install the driver

Download the JDBC JAR file.
Add the downloaded JAR file to your classpath so that the Java compiler and runtime can locate the necessary JDBC classes.

Authenticate to BigQuery

The JDBC driver for BigQuery provides several authentication options:

Using a service account
Using a Google user account
Using a pre-generated access and refresh token
Using Application Default Credentials
Using an external method

Authenticate with a service account

When you establish a connection with the JDBC driver for BigQuery, do the following:

Set the OAuthType connection property to 0.
Set the ProjectId connection property to the name of your BigQuery project.
Do one of the following:
- If you're using a service account email and key, set the OAuthServiceAcctEmail connection property to your Google service account email address, and set the OAuthPvtKey connection property to the service account key JSON object. For OAuthPvtKey, you can use either the JSON object of the key or the full path of the key file.
- If you're using a service account key file, set the OAuthPvtKeyPath connection property to the full path to the service account key file.

The following two examples use service account authentication:

jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;
ProjectId=MyTestProject;OAuthType=0;
OAuthServiceAcctEmail=bq-jdbc-sa@mytestproject.iam.gserviceaccount.com;
OAuthPvtKey=my-sa-key

jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;
ProjectId=MyTestProject;OAuthType=0;
OAuthPvtKeyPath=path/to/file/secret.json;

Authenticate with a Google user account

When you establish a connection with the JDBC driver for BigQuery, do the following:

Set the OAuthType connection property to 1.
Set the ProjectId connection property to the name of your BigQuery project.
Set the OAuthClientId connection property to your client ID, and set the OAuthClientSecret connection property to your client secret.

For more information, see Using OAuth 2.0 to Access Google APIs and Manage OAuth Clients.

The following example uses Google user account authentication:

jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;
ProjectId=MyTestProject;OAuthType=1;
OAuthClientId=123456789012-abcdefghijklmnopqrstuvwxyz12345.apps.googleusercontent.com;
OAuthClientSecret=_aB-C1D_E2fGh3Ij4kL5m6No7p8QR9sT0uV;

Authenticate with a pre-generated access or refresh token

When you establish a connection with the JDBC driver for BigQuery, do the following:

Set the OAuthType connection property to 2.
Set the ProjectId connection property to the name of your BigQuery project.
Do one of the following:
- If you're using a pre-generated access token, set the OAuthAccessToken connection property to your access token.
- If you're using a pre-generated refresh token, set the OAuthRefreshToken connection property to your refresh token, set the OAuthClientId connection property to your client ID, and set the OAuthClientSecret connection property to your client secret.

For more information, see Using OAuth 2.0 to Access Google APIs.

The following example uses pre-generated access token authentication:

jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;
ProjectId=MyTestProject;OAuthType=2;
OAuthAccessToken=ya29.a0AfH6SMCiH1L-x_yZABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789-_;

The following example uses pre-generated refresh token authentication:

jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;
ProjectId=MyTestProject;OAuthType=2;
OAuthClientId=123456789012-abcdefghijklmnopqrstuvwxyz12345.apps.googleusercontent.com;
OAuthClientSecret=_aB-C1D_E2fGh3Ij4kL5m6No7p8QR9sT0uV;
OAuthRefreshToken=1/fFAGRNJru1FTz70BzhT3Zg;

Authenticate with Application Default Credentials

Application Default Credentials (ADC) is a strategy that is used by the authentication libraries to automatically find credentials based on the application environment.

When you establish a connection with the JDBC driver for BigQuery, do the following:

Set the OAuthType connection property to 3.
Set the ProjectId connection property to the name of your BigQuery project.

For more information, see Set up Application Default Credentials.

The following example uses ADC authentication:

jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;
ProjectId=MyTestProject;OAuthType=3;

Authenticate with an external method

You can authenticate with external methods such as Workforce Identity Federation or Workload Identity Federation.

When you establish a connection with the JDBC driver for BigQuery, do the following:

Set the OAuthType connection property to 4.
Set the ProjectId connection property to the name of your BigQuery project.
Do one of the following:
- Set the OAuthPvtKey connection property or the OAuthPvtKeyPath connection property to the full path of the configuration file. For OAuthPvtKey, you can use either the JSON object of the key or the full path of the key file.
- Set the OAuthPvtKey connection property to the raw external account configuration object.
- Set the following BYOID connection properties, which are described in the connection properties section:
  - BYOID_AudienceUri
  - BYOID_CredentialSource
  - BYOID_PoolUserProject
  - BYOID_SA_Impersonation_Uri
  - BYOID_SubjectTokenType
  - BYOID_TokenUri

The following examples use external authentication methods:

jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;
ProjectId=MyTestProject;OAuthType=4;
OAuthPvtKeyPath=path/to/file/secret.json;

jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;
ProjectId=MyTestProject;OAuthType=4;
OAuthPvtKey=External_account_configuration_object;

jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;
ProjectId=MyTestProject;OAuthType=4;
BYOID_AudienceUri=//iam.googleapis.com/projects/my-project/locations/US-EAST1/workloadIdentityPools/my-pool-/providers/my-provider;
BYOID_SubjectTokenType=urn:ietf:params:oauth:tokentype:id_token;
BYOID_CredentialSource={\"file\":\"/path/to/file\"};
BYOID_SA_Impersonation_Uri=my-sa;
BYOID_TokenUri=https://sts.googleapis.com/v1/token;"

The following is an example of a configuration file:

{
  "type": "external_account",
  "audience": "//iam.googleapis.com/locations/global/workforcePools/my_pool_name/providers/my_provider_name",
  "subject_token_type": "urn:ietf:params:oauth:token-type:id_token",
  "token_url": "https://sts.googleapis.com/v1/token",
  "credential_source": {
    "file": "/path/to/workforce/tokrn.txt"
  },
  "workforce_pool_user_project": "my_project",
  "token_info_url": "https://sts.googleapis.com/v1/introspect"
}

Establish a connection

To establish a connection with the JDBC driver for BigQuery, do the following:

Add the google-cloud-bigquery-jdbc dependency to your Maven POM/Gradle build file:

<dependency>
    <groupId>com.google.cloud</groupId>
    <artifactId>google-cloud-bigquery-jdbc</artifactId>
    <version>0.0.1</version>
    <scope>system</scope>
    <systemPath>path/to/file/google-jdbc-jar-with-dependencies.jar</systemPath>
</dependency>

If you're using a Gradle project, add the following to the build file as well:

dependencies {
// ... other dependencies
implementation files('path/to/file/google-jdbc-jar-with-dependencies.jar')
}

Identify your connection string for the JDBC driver. This string captures all the essential information that is required to establish a connection between your Java application and a specific database. The JDBC driver for BigQuery connection string has the following format:
```
jdbc:bigquery://HOST:PORT;ProjectId=PROJECT_ID;OAuthType=AUTH_TYPE;PROPERTIES
```
Replace the following:
- HOST: the DNS or IP address of the server.
- PORT: the TCP port number.
- PROJECT_ID: the ID of your BigQuery project.
- AUTH_TYPE: a number specifying the type of authentication that you used. One of the following:
  - 0: Google service account authentication
  - 1: Google user account authentication
  - 2: Pre-generated refresh token or access token authentication
  - 3: Application Default Credential authentication
  - 4: External authentication methods, such as Workforce Identity Federation or Workload Identity Federation
- PROPERTIES: additional connection properties for the JDBC driver. Properties must be listed in the property_1=value_1; property_2=value_2;... format. Property names are case-insensitive. For a full list of connection properties, see Connection properties.

Connect to the driver with either the DriverManager or DataSource class.

Connect to the DriverManager class:

import java.sql.Connection;
import java.sql.DriverManager;

private static Connection getJdbcConnectionDM(){
  Connection connection = DriverManager.getConnection(CONNECTION_STRING);
  return connection;
}

Replace CONNECTION_STRING with the connection string from the previous step.

Connect to the DataSource class:

import com.google.cloud.bigquery.jdbc.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

private static public Connection getJdbcConnectionDS() throws SQLException {
  Connection connection = null;
  DataSource dataSource = new com.google.cloud.bigquery.jdbc.DataSource();
  dataSource.setURL(CONNECTION_STRING);
  connection = dataSource.getConnection();
  return connection;
}

Replace CONNECTION_STRING with the connection string from the previous step.

The DataSource class also has setter methods, which you can use to set connection properties, rather than including every property in the connection string. The following is an example:

private static Connection getConnection() throws SQLException {
  DataSource ds = new DataSource();
  ds.setURL(CONNECTION_STRING);
  ds.setAuthType(3);  // Application Default Credentials
  ds.setProjectId("MyTestProject");
  ds.setEnableHighThroughputAPI(true);
  ds.setLogLevel("6");
  ds.setUseQueryCache(false);
  return ds.getConnection();
}

Explore driver features

Now that you're connected to the JDBC driver for BigQuery, you can explore its features.

SQL connector

The SQL connector lets you run SQL queries with BigQuery. Use the QueryDialect connection property to specify the SQL dialect that you want to use.

Data type mapping

The JDBC driver for BigQuery supports the following data type mappings:

BigQuery type	SQL type	Java type
`ARRAY`	`ARRAY`	`Array`
`BIGNUMERIC`	`NUMERIC`	`BigDecimal`
`BOOL`	`BOOLEAN`	`Boolean`
`BYTES`	`VARBINARY`	`byte[]`
`DATE`	`DATE`	`Date`
`DATETIME`	`OTHER`	`String`
`FLOAT64`	`DOUBLE`	`Double`
`GEOGRAPHY`	`OTHER`	`String`
`INT64`	`BIGINT`	`Long`
`INTERVAL`	`OTHER`	`String`
`JSON`	`OTHER`	`String`
`NUMERIC`	`NUMERIC`	`BigDecimal`
`STRING`	`NVARCHAR`	`String`
`STRUCT`	`STRUCT`	`Struct`
`TIME`	`TIME`	`Time`
`TIMESTAMP`	`TIMESTAMP`	`Timestamp`

Nested and repeated records

The JDBC driver for BigQuery supports nested and repeated records, where the driver returns the base type as a struct object or a string representation of a JSON object.

The following is an example of querying the base record of struct data:

ResultSet resultSet = statement.executeQuery("SELECT STRUCT(\"Adam\" as name, 5 as age)");
    resultSet.next();
    Struct obj = (Struct) resultSet.getObject(1);
    System.out.println(obj.toString());

The result is the following:

{
  "v": {
    "f": [
      {
        "v": "Adam"
      },
      {
        "v": "5"
      }
    ]
  }
}

The following is an example of querying subcomponents of a struct object:

ResultSet resultSet = statement.executeQuery("SELECT STRUCT(\"Adam\" as name, 5 as age)");
    resultSet.next();
    Struct structObject = (Struct) resultSet.getObject(1);
    Object[] structComponents = structObject.getAttributes();
    for (Object component : structComponents){
      System.out.println(component.toString());
    }

The following is an example of querying a standard array of repeated data:

// Execute Query
ResultSet resultSet = statement.executeQuery("SELECT [1,2,3]");
resultSet.next();
Object[] arrayObject = (Object[]) resultSet.getArray(1).getArray();

// Verify Result
int count =0;
for (; count < arrayObject.length; count++) {
  System.out.println(arrayObject[count]);
}

The following is an example of querying a struct array of repeated data:

// Execute Query
ResultSet resultSet = statement.executeQuery("SELECT "
    + "[STRUCT(\"Adam\" as name, 12 as age), "
    + "STRUCT(\"Lily\" as name, 17 as age)]");

Struct[] arrayObject = (Struct[]) resultSet.getArray(1).getArray();

// Verify Result
for (int count =0; count < arrayObject.length; count++) {
  System.out.println(arrayObject[count]);
}

Large result set

To retrieve large result sets when you use the JDBC driver for BigQuery, do the following:

If your QueryDialect connection property is set to SQL, specify a dataset and table to store the results with the LargeResultDataset and LargeResultTable connection properties.
If your QueryDialect connection property is set to BIG_QUERY, set the AllowLargeResults connection property to TRUE, and specify a dataset and table to store the results with the LargeResultDataset and LargeResultTable connection properties.

In both cases, you can use the LargeResultsDatasetExpirationTime connection property to specify the lifetime duration of the destination dataset.

If you don't specify values for the LargeResultDataset and LargeResultTable connection properties, then the JDBC driver for BigQuery creates a hidden dataset named _google_jdbc and a temporary table within it. These resources are automatically deleted after 24 hours.

All BigQuery quotas and limits still apply.

Positional parameters

Positional parameters are placeholders within a SQL statement that represent values to be supplied during query execution. The JDBC driver for BigQuery supports positional parameters with the question mark symbol (?). The following is an example query that uses a positional parameter:

PreparedStatement preparedStatement = connection.prepareStatement(
    "SELECT * FROM MyTestTable where testColumn = ?");
preparedStatement.setString(1, "string2");
ResultSet resultSet = statement.executeQuery(selectQuery);

`SELECT` queries

You can use the JDBC driver for BigQuery to execute SELECT queries through the BigQuery API or the BigQuery Storage Read API.

If you use the BigQuery API, set the JobCreationMode connection property to specify if the query runs with or without creating a job.

If you use the Storage Read API, use the EnableHighThroughputAPI connection property to enable the API and verify that the following conditions are met:

The schema doesn't use the INTERVAL type.
The number of total rows is less than the value of the HighThroughputMinTableSize connection property.
The rows-to-page-size ratio is less than the value of the HighThroughputActivationRatio connection property.

Bulk-insert

To perform bulk-insert operations with the JDBC driver for BigQuery, use the executeBatch method.

The following is a sample write operation:

Connection conn = DriverManager.getConnection(connectionUrl);
PreparedStatement statement = null;
Statement st = conn.createStatement();
final String insertQuery = String.format(
        "INSERT INTO `%s.%s.%s` "
      + " (StringField, IntegerField, BooleanField) VALUES(?, ?, ?);",
        DEFAULT_CATALOG, DATASET, TABLE_NAME);

statement = conn.prepareStatement(insertQuery1);

for (int i=0; i<2000; ++i) {
      statement.setString(1, i+"StringField");
      statement.setInt(2, i);
      statement.setBoolean(3, true);
      statement.addBatch();
}

statement.executeBatch();

Logging

The JDBC driver for BigQuery supports logging actions in the driver through Java Util Logging. Logging can affect performance, so only enable it to capture an issue, and disable it after you are finished.

To configure logging, do the following:

Set the LogLevel property according to the following table:

`LogLevel` value	`LogLevel`	Description
`0`	`OFF`	No messages are logged.
`1`	`SEVERE`	Serious failures or errors that impact application functionality.
`2`	`WARNING`	Potential problems or situations that might require attention.
`3`	`INFO`	Informational messages about normal events and progress.
`4`	`CONFIG`	Messages regarding static configuration information.
`5`	`FINE`	General tracing messages.
`6`	`FINER`	Detailed tracing messages.
`7`	`FINEST`	Highly detailed tracing messages, useful for extensive debugging.
`8`	`ALL`	All messages are logged.

Set the LogPath property to the full path of the folder where you want to save the log file.

Alternatively, you can configure logging with the BIGQUERY_JDBC_LOG_LEVEL and BIGQUERY_JDBC_LOG_PATH environment variables.

Connection properties

In a JDBC driver, connection properties are configuration parameters that you can include in the connection string or pass through the setter methods when you establish a connection to a database. The following connection properties are supported by the JDBC driver for BigQuery.

AdditionalProjects

This connection property lets queries and metadata operations access datasets within specified projects, in addition to the primary project that is defined in the connection string.

Default value: N/A
Data type: String (comma-separated string of project IDs)
Required: No

AllowLargeResults

This connection property specifies if the driver processes query results that are larger than 128 MB when the QueryDialect connection property is set to BIG_QUERY. If the QueryDialect connection property is set to SQL, the AllowLargeResults connection property is set to TRUE by default. For more information, see Large result set.

Default value: TRUE
Data type: Boolean
Required: No

BYOID_AudienceUri

This connection property specifies the audience property in an external account configuration file. The audience property contains the resource name for the workload identity pool or the workforce pool and the provider identifier in that pool.

Default value: N/A
Data type: String
Required: Only when OAuthType=4

BYOID_CredentialSource

This connection property sets token retrieval information and environmental information.

Default value: N/A
Data type: String
Required: Only when OAuthType=4

BYOID_PoolUserProject

This connection property sets the user project when the workforce pool is being used. The project must have the serviceusage.services.use IAM permission.

Default value: N/A
Data type: String
Required: Only when OAuthType=4 and using the workforce pool

BYOID_SA_Impersonation_Uri

This connection property sets the URL for the service account impersonation when workload identity pools are being used and APIs haven't been integrated with UberMint.

Default value: N/A
Data type: String
Required: Only when OAuthType=4 and using workload identity pool with service account impersonation

BYOID_SubjectTokenType

This connection property sets the STS token based on the token exchange specification. The value must be one of the following:

Urn:ietf:params:oauth:token-type:jwt
Urn:ietf:params:oauth:token-type:id_token
Urn:ietf:params:oauth:token-type:saml2
urn:ietf:params:aws:token-type:aws4_request
Default value: urn:ietf:params:oauth:tokentype:id_token
Data type: String
Required: Only when OAuthType=4

BYOID_TokenUri

This connection property sets the STS token exchange endpoint.

Default value: https://sts.googleapis.com/v1/token
Data type: String
Required: No

ConnectionPoolSize

This connection property sets the connection pool size if connection pooling is enabled.

Default value: 10
Data type: Long
Required: No

DefaultDataset

This connection property specifies the dataset that's used when you run a query without explicitly specifying a dataset. You can use either the DATASET_ID or the PROJECT_ID.DATASET_ID format.

Default value: N/A
Data type: String
Required: No

EnableHighThroughputAPI

This connection property determines if the Storage Read API can be used. The HighThroughputActivationRatio and HighThroughputMinTableSize connection properties must also be satisfied to use the Storage Read API.

Default value: FALSE
Data type: Boolean
Required: No

EnableSession

This connection property determines if the connection starts a session. When used, the session ID is passed to all subsequent queries.

Default value: FALSE
Data type: Boolean
Required: No

EnableWriteAPI

This connection property determines if the Storage Write API can be used. It must be set to TRUE to enable bulk inserts.

Default value: FALSE
Data type: Boolean
Required: No

EndpointOverrides

This connection property sets custom endpoints in a comma-separated string.

Default values:
- BIGQUERY=https://bigquery.googleapis.com
- READ_API=https://bigquerystorage.googleapis.com
- OAUTH2=https://oauth2.googleapis.com
- STS=https://sts.googleapis.com
Data type: String
Required: No

FilterTablesOnDefaultDataset

This connection property controls the scope of metadata returned by the DatabaseMetaData.getTables() and DatabaseMetaData.getColumns() methods. When the property is disabled, no filtering occurs. The DefaultDataset connection property must also be set to enable filtering.

Default value: FALSE
Data type: Boolean
Required: No

HighThroughputActivationRatio

This connection property sets a threshold for number of pages in a query response. When this number is exceeded, and the EnableHighThroughputAPI and HighThroughputMinTableSize conditions are met, the driver starts using the Storage Read API.

Default value: 2
Data type: Integer
Required: No

HighThroughputMinTableSize

This connection property sets a threshold for number of rows in a query response. When this number is exceeded, and the EnableHighThroughputAPI and HighThroughputActivationRatio conditions are met, the driver starts using the Storage Read API.

Default value: 100
Data type: Integer
Required: No

JobCreationMode

This connection property determines if queries are run without creating jobs. A 1 value means that jobs are created for every query, and a 2 value means that queries can be executed without jobs.

Default value: 2
Data type: Integer
Required: No

JobTimeout

This connection property sets the job timeout (in seconds) after which the job is cancelled on the server.

Default value: 0
Data type: Long
Required: No

KMSKeyName

This connection property sets the KMS key name for encrypting data.

Default value: N/A
Data type: String
Required: No

Labels

This connection property sets labels that are associated with the query to organize and group query jobs.

Default value: N/A
Data type: Map<String, String>
Required: No

LargeResultDataset

This connection property sets the destination dataset for query results. For more information, see Large result set.

Default value: _google_jdbc, only when QueryDialect=BIG_QUERY or when QueryDialect=SQL and the LargeResultTable connection property is set
Data type: String
Required: No

LargeResultsDatasetExpirationTime

This connection property specifies the lifetime of all tables in a dataset, in milliseconds. This property is ignored if the dataset already has a default expiration time set.

Default value: 3600000
Data type: Long
Required: No

LargeResultTable

This connection property sets the destination table for query results. For more information, see Large result set.

Default value: temp_table..., only when QueryDialect=BIG_QUERY or when QueryDialect=SQL and the LargeResultTable connection property is set
Data type: String
Required: No

ListenerPoolSize

This connection property sets the listener pool size if connection pooling is enabled.

Default value: 10
Data type: Long
Required: No

Location

This connection property specifies the location where datasets are created or queried. BigQuery automatically determines the location, if this value isn't set.

Default value: N/A
Data type: String
Required: No

LogLevel

This connection property controls the level of detail logged during database interactions. For level descriptions, see Logging.

Default value: 0
Data type: Integer
Required: No

LogPath

This connection property sets the directory where log files are written. For more details, see Logging.

Default value: N/A
Data type: String
Required: No

MaximumBytesBilled

This connection property limits the number of bytes billed. Queries with bytes billed greater than this limit fail without incurring a charge.

Default value: 0
Data type: Long
Required: No

MaxResults

This connection property sets the maximum number of results per page.

Default value: 10000
Data type: Long
Required: No

MetaDataFetchThreadCount

This connection property configures the number of threads used for database metadata methods.

Default value: 32
Data type: Integer
Required: No

OAuthAccessToken

This connection property specifies the access token that's used for pre-generated access token authentication.

Default value: N/A
Data type: String
Required: When AUTH_TYPE=2

OAuthClientId

This connection property sets the client ID for pre-generated refresh token authentication and user account authentication.

Default value: N/A
Data type: String
Required: When AUTH_TYPE=1 or AUTH_TYPE=2

OAuthClientSecret

This connection property sets the client secret for pre-generated refresh token authentication and user account authentication.

Default value: N/A
Data type: String
Required: When AUTH_TYPE=1 or AUTH_TYPE=2

OAuthP12Password

This connection property sets the password for the PKCS12 key file.

Default value: notasecret
Data type: String
Required: No

OAuthPvtKey

This connection property sets the service account key when using service account authentication. This value can be a raw JSON keyfile object or a path to the JSON keyfile.

Default value: N/A
Data type: String
Required: When AUTH_TYPE=0 and the OAuthPvtKeyPath value isn't set

OAuthPvtKeyPath

This connection property sets the path to the service account key when using service account authentication.

Default value: N/A
Data type: String
Required: When AUTH_TYPE=0 and the OAuthPvtKey and OAuthServiceAcctEmail values aren't set

OAuthRefreshToken

This connection property sets the refresh token for pre-generated refresh token authentication. An OAuth 2.0 refresh token is a special type of token that allows an application to obtain a new access token when the current one expires, without requiring the user to re-authenticate.

Default value: N/A
Data type: String
Required: When AUTH_TYPE=2

OAuthServiceAcctEmail

This connection property sets the service account email when using service account authentication.

Default value: N/A
Data type: String
Required: When AUTH_TYPE=0 and the OAuthPvtKeyPath value isn't set

OAuthType

This connection property specifies the authentication type. The value must be one of the following:

0: Google service account authentication
1: Google user account authentication
2: Pre-generated refresh token or access token authentication
3: Application Default Credential authentication
4: External authentication methods, such as Workforce Identity Federation or Workload Identity Federation
Default value: -1
Data type: Integer
Required: Yes

PartnerToken

This connection property is used by Google Cloud partners to track usage of the driver.

Default value: N/A
Data type: String
Required: No

PrivateServiceConnectUris

This connection property is identical to the EndpointOverrides property. Use the EndpointOverrides property instead.

ProjectId

This connection property sets the default project ID for the driver. This project is used to execute queries and is billed for resource usage. If not set, the driver infers a project ID.

Default value: N/A
Data type: String
Required: No, but highly recommended

ProxyHost

This connection property sets the hostname or IP address of a proxy server through which the JDBC connection is routed.

Default value: N/A
Data type: String
Required: No

ProxyPort

This connection property sets the port number on which the proxy server is listening for connections.

Default value: N/A
Data type: String
Required: No

ProxyPwd

This connection property sets the password that's needed for authentication when connecting through a proxy server that requires it.

Default value: N/A
Data type: String
Required: No

ProxyUid

This connection property sets the username that's needed for authentication when connecting through a proxy server that requires it.

Default value: N/A
Data type: String
Required: No

QueryDialect

This connection property sets the SQL dialect for query execution. Use SQL for GoogleSQL (highly recommended) and BIG_QUERY for legacy SQL.

Default value: SQL
Data type: String
Required: No

QueryProperties

This connection property configures query behavior modifications.

The following is an example that is set in the connection string:

jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;
QueryProperties=dataset_project_id=TestProjectID,time_zone=America/New_York;

The following is an example that is set in DataSource class:

Map<String, String> queryProperties = new HashMap<>();
queryProperties.put("dataset_project_id", "TestProjectID");
queryProperties.put("time_zone", "America/New_York");
DataSource dataSource = new DataSource();
dataSource.setQueryProperties(queryProperties);

For more details, see ConnectionProperty.

Default value: N/A
Data type: Map<String, String>
Required: No

RequestGoogleDriveScope

This connection property is used to request access to Google Drive. When enabled, read-only Drive scope is added to the connection. To enable this property, set the value to 1.

Default value: 0
Data type: Integer
Required: No

RetryInitialDelay

This connection property sets the delay (in seconds) before the first retry.

Default value: 0
Data type: Long
Required: No

RetryMaxDelay

This connection property sets the maximum limit (in seconds) for the retry delay.

Default value: 0
Data type: Long
Required: No

ServiceAccountImpersonationChain

This connection property specifies a comma-separated list of service account emails in the impersonation chain.

Default value: N/A
Data type: String
Required: No

ServiceAccountImpersonationEmail

This connection property sets the service account email to be impersonated.

Default value: N/A
Data type: String
Required: No

ServiceAccountImpersonationScopes

This connection property specifies a comma-separated list of OAuth2 scopes to use with the impersonated account.

Default value: https://www.googleapis.com/auth/bigquery
Data type: String
Required: No

ServiceAccountImpersonationTokenLifetime

This connection property sets the impersonated account token lifetime (in seconds).

Default value: 3600
Data type: Integer
Required: No

SSLTrustStore

This connection property specifies the full path to the Java TrustStore that contains trusted Certificate Authority (CA) certificates. The driver utilizes this truststore to validate the identity of the server during the SSL/TLS handshake.

Default value: N/A
Data type: String
Required: No

SSLTrustStorePwd

This connection property specifies the password to the Java TrustStore specified in the SSLTrustStore property.

Default value: N/A
Data type: String
Required: Only if using a Java TrustStore that is password-protected

SWA_ActivationRowCount

This connection property sets a threshold number of executeBatch insert rows which, when exceeded, causes the connector to switch to the Storage Write API.

Default value: 3
Data type: Integer
Required: No

SWA_AppendRowCount

This connection property sets the size of the write stream.

Default value: 1000
Data type: Integer
Required: No

Timeout

This connection property sets the length of time, in seconds, that the connector retries a failed API call before timing out.

Default value: 0
Data type: Long
Required: No

UniverseDomain

This connection property sets the universe domain, the top-level domain that is associated with your organization's Google Cloud resources.

Default value: googleapis.com
Data type: String
Required: No

UnsupportedHTAPIFallback

This connection property determines if the connector falls back to the REST API (when set to TRUE) or returns an error (when set to FALSE).

Default value: TRUE
Data type: Boolean
Required: No

UseQueryCache

This connection property enables query caching.

Default value: TRUE
Data type: Boolean
Required: No

Use the JDBC driver for BigQuery

Before you begin

Configure the driver

Install the driver

Authenticate to BigQuery

Authenticate with a service account

Authenticate with a Google user account

Authenticate with a pre-generated access or refresh token

Authenticate with Application Default Credentials

Authenticate with an external method

Establish a connection

Explore driver features

SQL connector

Data type mapping

Nested and repeated records

Large result set

Positional parameters

SELECT queries

Bulk-insert

Logging

Connection properties

`SELECT` queries