Use the JDBC driver for BigQuery

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

The Java Database Connectivity (JDBC) driver for BigQuery connects yourJava applications to BigQuery, letting you useBigQuery features with your preferred tooling and infrastructure.To connect non-Java applications to BigQuery, use theSimba Open Database Connectivity (ODBC) driver for BigQuery.

Limitations

The JDBC driver for BigQuery is subject to the followinglimitations:

• The driver is specific to BigQuery and can't be used withother products or services.
• TheINTERVAL data type isn't supported with theBigQuery Storage Read API.
• Alldata manipulation language (DML) limitationsapply.

Before you begin

1. Make sure that you're familiar with JDBC drivers, Apache Maven, andthejava.sql package.
2. Verify that your system is configured with Java Runtime Environment (JRE)8.0 or later. For information on checking your JRE version, seeVerifying the JRE Environment.

3. Authenticate to BigQuery,and take note of the following information, which is used later when youestablish a connection with the JDBC driver for BigQuery. Youonly need to note the information that corresponds to the authenticationmethod that you use.

   Authentication method	Authentication information	Example	Connection property (to set later)
   Standard service account	Service account email	bq-jdbc-sa@mytestproject.iam.gserviceaccount.com	OAuthServiceAcctEmail
   	Service account key (JSON object)	my-sa-key	OAuthPvtKey
   Service account key file	Service account key file (full path)	path/to/file/secret.json	OAuthPvtKeyPath
   Google user account	Client ID	123-abc.apps.googleusercontent.com	OAuthClientId
   	Client secret	_aB-C1D_E2fGh3Ij4kL5m6No7p8QR9sT0uV	OAuthClientSecret
   Pre-generated access token	Access token	ya29.a0AfH6SMCiH1L-x_yZ	OAuthAccessToken
   Pre-generated refresh token	Refresh token	1/fFAGRNJru1FTz70BzhT3Zg	OAuthRefreshToken
   	Client ID	123-abc.apps.googleusercontent.com	OAuthClientId
   	Client secret	_aB-C1D_E2fGh3Ij4kL5m6No7p8QR9sT0uV	OAuthClientSecret
   Application Default Credentials	None	N/A	N/A
   Configuration file	Configuration file (JSON object or full path)	path/to/file/secret.json	OAuthPvtKey
   External account configuration object	Account configuration object	external_account_configuration_object	OAuthPvtKey
   Other	Audience property of the external account configuration file	//iam.googleapis.com/projects/my-project/locations/US-EAST1/workloadIdentityPools/my-pool-/providers/my-provider	BYOID_AudienceUri
   	Token retrieval and environmental information file	{\"file\":\"/path/to/file\"}	BYOID_CredentialSource
   	User project (only if using a workforce pool)	my_project	BYOID_PoolUserProject
   	URI for service account impersonation (only if using a workforce pool)	my-sa	BYOID_SA_Impersonation_Uri
   	Security Token Service token based on the token exchange specification	urn:ietf:params:oauth:tokentype:id_token	BYOID_SubjectTokenType
   	Security Token Service token exchange endpoint	https://sts.googleapis.com/v1/token	BYOID_TokenUri

Configure your development environment

To configure your development environment with the JDBC driver forBigQuery, do the following:

1. Download one of the following JDBC packages:

   • Uber JAR.A JAR file with all dependencies included.
   • Shaded Uber JAR.A shaded JAR file with all dependencies included.
   • Thin JAR with dependencies.A zip file that includes a thin JAR file and all dependencies.

2. Add the downloaded JAR file to your classpath so that the Java compiler andruntime can locate the necessary JDBC classes. For information on adding afile to your classpath, seeSetting the Classpath.

3. Add the following dependency to yourbuild 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>

4. If you're using a Gradle project, add the following to your build file:

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

Establish a connection

To establish a connection between your Java application andBigQuery with the JDBC driver for BigQuery, dothe following:

1. Identify your connection string for the JDBC driver forBigQuery. This string captures all the required informationto establish a connection between your Java application andBigQuery. The connection string has the following format:

   jdbc:bigquery://HOST:PORT;ProjectId=PROJECT_ID;OAuthType=AUTH_TYPE;AUTH_PROPS;OTHER_PROPS

   Replace the following:

   • HOST: the DNS or IP address of the server.
   • PORT: the TCP port number.
   • PROJECT_ID: the ID of your BigQueryproject.
   • AUTH_TYPE: a number specifying the type ofauthentication that you used. One of the following:
     • 0: for service account authentication (standard and key file)
     • 1: for Google user account authentication
     • 2: for pre-generated refresh or access token authentication
     • 3: for Application Default Credential authentication
     • 4: for other authentication methods
   • AUTH_PROPS: the authentication information thatyou noted when youauthenticated to BigQuery, listed intheproperty_1=value_1; property_2=value_2;... format—for example,OAuthPvtKeyPath=path/to/file/secret.json, if you authenticated witha service account key file.
   • OTHER_PROPS (optional): additional connectionproperties for the JDBC driver, listed in theproperty_1=value_1; property_2=value_2;... format. For a full list ofconnection properties, seeConnection properties.

2. Connect your Java application to the JDBC driver forBigQuery with either theDriverManagerorDataSourceclass.

   • Connect with theDriverManager class:

     importjava.sql.Connection;importjava.sql.DriverManager;privatestaticConnectiongetJdbcConnectionDM(){Connectionconnection=DriverManager.getConnection(CONNECTION_STRING);returnconnection;}

     ReplaceCONNECTION_STRING with the connectionstring from the previous step.

   • Connect with theDataSource class:

     importcom.google.cloud.bigquery.jdbc.DataSource;importjava.sql.Connection;importjava.sql.SQLException;privatestaticpublicConnectiongetJdbcConnectionDS()throwsSQLException{Connectionconnection=null;DataSourcedataSource=newcom.google.cloud.bigquery.jdbc.DataSource();dataSource.setURL(CONNECTION_STRING);connection=dataSource.getConnection();returnconnection;}

     ReplaceCONNECTION_STRING with the connectionstring from the previous step.

     TheDataSource class also has setter methods that you can use to setconnection properties, rather than includingthem in the connection string. The following is an example:

     privatestaticConnectiongetConnection()throwsSQLException{DataSourceds=newDataSource();ds.setURL(jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;);ds.setAuthType(3);// Application Default Credentialsds.setProjectId("MyTestProject");ds.setEnableHighThroughputAPI(true);ds.setLogLevel("6");ds.setUseQueryCache(false);returnds.getConnection();}

Connection properties

JDBC driver connection properties are configuration parameters that you includein the connection string or pass through setter methods when youestablish a connection to a database. The followingconnection properties are supported by the JDBC driver forBigQuery.

With your Java application connected to BigQuery through theJDBC driver, you can now run queries in your development environment through thestandard JDBC process.AllBigQuery quotas and limits apply.

Data type mapping

When you run queries through the JDBC driver for BigQuery, thefollowing data type mapping occurs:

Examples

The following sections provide examples that use BigQueryfeatures through the JDBC driver for BigQuery.

Positional parameters

The following example runs a query with apositional parameter:

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

Nested and repeated records

The following example queries the base record ofStruct data:

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

The driver returns the base record as a struct object or a stringrepresentation of a JSON object. The result is similar to the following:

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

The following example queries the subcomponents of aStruct object:

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

The following example queries a standard array of repeated data, then verifiesthe result:

// Execute QueryResultSetresultSet=statement.executeQuery("SELECT [1,2,3]");resultSet.next();Object[]arrayObject=(Object[])resultSet.getArray(1).getArray();// Verify Resultintcount=0;for(;count<arrayObject.length;count++){System.out.println(arrayObject[count]);}

The following example queries aStruct array of repeated data, then verifiesthe result:

// Execute QueryResultSetresultSet=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 Resultfor(intcount=0;count<arrayObject.length;count++){System.out.println(arrayObject[count]);}

Bulk-insert

The following example performs a bulk-insert operation with theexecuteBatch method.

Connectionconn=DriverManager.getConnection(connectionUrl);PreparedStatementstatement=null;Statementst=conn.createStatement();finalStringinsertQuery=String.format("INSERT INTO `%s.%s.%s` "+" (StringField, IntegerField, BooleanField) VALUES(?, ?, ?);",DEFAULT_CATALOG,DATASET,TABLE_NAME);statement=conn.prepareStatement(insertQuery1);for(inti=0;i<2000;++i){statement.setString(1,i+"StringField");statement.setInt(2,i);statement.setBoolean(3,true);statement.addBatch();}statement.executeBatch();

Pricing

You can download the JDBC driver for BigQuery at no cost, and youdon't need any additional licenses to use the drivers. However, when you use thedriver,standard BigQuery pricingapplies.

What's next

• Learn more about theSimba ODBC driver for BigQuery.
• Explore otherBigQuery developer tools.