Use the JDBC driver for BigQuery

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

Limitations

The JDBC driver for BigQuery is subject to the following limitations:

• The driver is specific to BigQuery and can't be used with other products or services.
• The INTERVAL data type isn't supported with the BigQuery Storage Read API.
• All data manipulation language (DML) limitations apply.

Before you begin

1. Make sure that you're familiar with JDBC drivers, Apache Maven, and the java.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, see Verifying the JRE Environment .

3. Authenticate to BigQuery , and take note of the following information, which is used later when you establish a connection with the JDBC driver for BigQuery. You only need to note the information that corresponds to the authentication method 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 for BigQuery, 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 and runtime can locate the necessary JDBC classes. For information on adding a file to your classpath, see Setting the Classpath .

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

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

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

Establish a connection

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

1. Identify your connection string for the JDBC driver for BigQuery. This string captures all the required information to establish a connection between your Java application and BigQuery. 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 BigQuery project.
   • AUTH_TYPE : a number specifying the type of authentication 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 that you noted when you authenticated to BigQuery , listed in the property_1=value_1; property_2=value_2;... format—for example, OAuthPvtKeyPath=path/to/file/secret.json , if you authenticated with a service account key file.
   • OTHER_PROPS (optional): additional connection properties for the JDBC driver, listed in the property_1=value_1; property_2=value_2;... format. For a full list of connection properties, see Connection properties .

2. Connect your Java application to the JDBC driver for BigQuery with either the DriverManager or DataSource class.

   • Connect with 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 with 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 that you can use to set connection properties , rather than including them in the connection string. The following is an example:

      private 
       
      static 
       
      Connection 
       
      getConnection 
      () 
       
      throws 
       
      SQLException 
       
      { 
       
      DataSource 
       
      ds 
       
      = 
       
      new 
       
      DataSource 
      (); 
       
      ds 
      . 
      setURL 
      ( 
      jdbc 
      : 
      bigquery 
      : 
      //https://www.googleapis.com/bigquery/v2:443;); 
       
      ds 
      . 
      setAuthType 
      ( 
      3 
      ); 
       
      // Application Default Credentials 
       
      ds 
      . 
      setProjectId 
      ( 
      "MyTestProject" 
      ); 
       
      ds 
      . 
      setEnableHighThroughputAPI 
      ( 
      true 
      ); 
       
      ds 
      . 
      setLogLevel 
      ( 
      "6" 
      ); 
       
      ds 
      . 
      setUseQueryCache 
      ( 
      false 
      ); 
       
      return 
       
      ds 
      . 
      getConnection 
      (); 
      } 
     

Connection properties

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

Run queries with the driver

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

Data type mapping

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

Examples

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

Positional parameters

The following example runs a query with a positional parameter :

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

Nested and repeated records

The following example queries 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 driver returns the base record as a struct object or a string representation of a JSON object. The result is similar to the following:

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

The following example queries the 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 example queries a standard array of repeated data, then verifies the result:

 // 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 example queries a Struct array of repeated data, then verifies the result:

 // 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 
 ] 
 ); 
 } 

Bulk-insert

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

 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 
 (); 

Pricing

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

What's next

• Learn more about the Simba ODBC driver for BigQuery .
• Explore other BigQuery developer tools .