Use the ODBC driver for BigQuery

The Open Database Connectivity (ODBC) driver for BigQuery connects your applications to BigQuery. This lets you use BigQuery features with your preferred tooling and infrastructure.

Before you begin

1. Make sure that you're familiar with Open Database Connectivity (ODBC) drivers and the driver manager.

2. Take note of the following system requirements:

   Operating System	Supported Architectures	Minimum Version and Dependencies
   Windows	32-bit (x86), 64-bit (x64)	Version: Windows 10, Windows Server 2016 or newer Dependency: Microsoft Visual C++ Redistributable for Visual Studio 2019 or 2022
   macOS	64-bit (x86_64), ARM64 (Apple Silicon)	Version: macOS 12 (Monterey) or newer Dependency: An ODBC Driver Manager (for example, unixODBC ). Ensure you add the installation directory to your DYLD_LIBRARY_PATH .
   Linux	64-bit (x86_64)	Version: Any distribution with glibc 2.27 or later (for example, Ubuntu 20.04 LTS+, Debian 11+) Dependency: An ODBC Driver Manager (for example, unixODBC ). Ensure you add the installation directory to your LD_LIBRARY_PATH .

3. Identify your connection type for the ODBC driver for BigQuery. The driver supports the following authentication methods:

   Authentication method

   Authentication information

   Example

   Connection property (to set later)

   Standard service account

   Service account key (JSON object)

   my-sa-key

   KeyFilePath

   Workload Identity federation or Workforce Identity Federation

   Audience property of the external account configuration file

   //iam.googleapis.com/locations/global/...

   BYOID_AudienceUrl

   Token retrieval and environmental information file

   {"file":"/path/to/file"}

   BYOID_CredentialSource

   User project (only for workforce pool)

   my_project

   BYOID_PoolUserProject

   STS token type

   id_token or other STS types

   BYOID_SubjectTokenType

   STS token exchange endpoint

   Custom sts endpoint URL

   BYOID_TokenUrl

   Application Default Credentials

   N/A

   N/A

   N/A

Install and configure the ODBC driver

This section describes how to install and configure the ODBC driver for Windows and non-Windows operating systems.

Windows

On Windows, ensure you install the driver architecture that matches your application's architecture. For example, use the 64-bit driver for 64-bit applications and the 32-bit driver for 32-bit applications. A 64-bit Windows system supports both 32-bit and 64-bit applications.

• Download ODBCDriverforBigQuery_windows_x86.msi for 32-bit applications
• Download ODBCDriverforBigQuery_windows_x64.msi for 64-bit applications

Create a Data Source Name

To create a Data Source Name in Windows:

1. From the Startmenu, go to ODBC Data Sources, and select the version that has the same bitness as your client application to ensure proper connection to BigQuery.
2. In the ODBC Data Source Administrator, click the Driverstab.
3. Locate the ODBC Driver for BigQueryas it appears in the alphabetical list of installed ODBC drivers.
4. Choose one of the following options:
   • To create a DSN for the current user, click the User DSNtab.
   • To create a DSN for all users, click the System DSNtab. System DSNs are recommended because some applications load data using different user accounts and might not detect User DSNs created under another user account.
5. Click Add.
6. In the Create New Data Sourcedialog, select ODBC Driver for BigQueryand then click Finish.
7. The ODBC Driver for BigQuery DSN Setupdialog opens.
8. In the Data Source Namefield, type a name for your DSN.
9. See the Connection Properties section to understand what values to populate.

Non-Windows

64-bit Linux distributions support both 32-bit and 64-bit applications. Ensure the ODBC driver's architecture matches the application you intend to use. For example, use the 64-bit driver for 64-bit applications and the 32-bit driver for 32-bit applications. You can install both driver architectures simultaneously on a single system.

• Download ODBCDriverforBigQuery_linux_latest.zip for Linux
• Download ODBCDriverforBigQuery_macos_latest.tar.gz for macOS

To install the connector using the tar or zip file package:

1. Create the directory where you want to install the connector, if it does not already exist.
2. Extract the main zip file to a convenient temporary location.
3. Navigate to the folder of the extracted tar or zip file and then (optionally) copy all the files and folders to the installation directory.
4. After extraction, the ODBC Driver for BigQuery shared object path is [INSTALLDIR]/lib/libgoogle_cloud_odbc_bq_driver.so . Update your .ini files to reflect the correct path of the connector.

unzip  
linux_odbc-driver. VERSION 
.zip  
-d  
linux_odbc-driver. VERSION 
/ cd 
  
./linux_odbc-driver. VERSION 
 export 
  
 INSTALL_DIR 
 = 
 $( 
 pwd 
 ) 
 export 
  
 ODBCINI 
 = 
 $INSTALL_DIR 
/odbc.ini export 
  
 ODBCINSTINI 
 = 
 $INSTALL_DIR 
/odbcinst.ini export 
  
 GOOGLEBIGQUERYODBCINI 
 = 
 $INSTALL_DIR 
/googlebigqueryodbc.ini

Establish a connection

To establish a connection using the ODBC driver for BigQuery, you can use a connection string or a DSN.

Connection string format

Driver=ODBC Driver for BigQuery;ProjectId= PROJECT_ID 
;OAuthMechanism= AUTH_TYPE 
; AUTH_PROPS 
; OTHER_PROPS 

Replace the following:

• PROJECT_ID : the ID of your BigQuery project.
• AUTH_TYPE : a number specifying the type of authentication you used. Choose one of the following:
  • 0 : for service account authentication
  • 3 : for Application Default Credential authentication
  • 4 : for Workload or Workforce Identity Federation authentication
• AUTH_PROPS : the authentication information you noted when you authenticated to BigQuery.
• OTHER_PROPS (optional): additional connection properties for the ODBC driver.

Connection properties

ODBC driver connection properties are configuration parameters that you include in the connection string when you establish a connection to a database. The ODBC driver for BigQuery supports the following connection properties.

Run queries with the driver

This section provides information on data type mapping and examples for running queries with the ODBC driver.

Data type mapping

When you run queries through the ODBC driver for BigQuery, the following data type mapping occurs (based on standard ODBC SQL types):

Examples

The following examples demonstrate how to use parameterized queries and multi-statement scripts with the ODBC driver.

Parameterized queries

 // 1. Prepare statement 
 std 
 :: 
 string 
  
 insert_stmt 
  
 = 
  
 "INSERT INTO MyTable VALUES (?, ?, ?)" 
 ; 
 status 
  
 = 
  
 SQLPrepare 
 ( 
 hstmt 
 , 
  
 ( 
 SQLCHAR 
 * 
 ) 
 insert_stmt 
 . 
 c_str 
 (), 
  
 SQL_NTS 
 ); 
 // 2. Bind parameters 
 std 
 :: 
 string 
  
 str_val 
  
 = 
  
 "example_string" 
 ; 
 long 
  
 long 
  
 int_val 
  
 = 
  
 12345 
 ; 
 double 
  
 float_val 
  
 = 
  
 1.2345 
 ; 
 // Bind string field 
 status 
  
 = 
  
 SQLBindParameter 
 ( 
  
 hstmt 
 , 
  
 1 
 , 
  
 SQL_PARAM_INPUT 
 , 
  
 SQL_C_CHAR 
 , 
  
 SQL_VARCHAR 
 , 
  
 50 
 , 
  
 0 
 , 
  
 ( 
 SQLPOINTER 
 ) 
 str_val 
 . 
 c_str 
 (), 
  
 str_val 
 . 
 size 
 (), 
  
 NULL 
 ); 
 // Bind integer field 
 status 
  
 = 
  
 SQLBindParameter 
 ( 
  
 hstmt 
 , 
  
 2 
 , 
  
 SQL_PARAM_INPUT 
 , 
  
 SQL_C_UBIGINT 
 , 
  
 SQL_BIGINT 
 , 
  
 0 
 , 
  
 0 
 , 
  
 & 
 int_val 
 , 
  
 0 
 , 
  
 NULL 
 ); 
 // Bind float field 
 status 
  
 = 
  
 SQLBindParameter 
 ( 
  
 hstmt 
 , 
  
 3 
 , 
  
 SQL_PARAM_INPUT 
 , 
  
 SQL_C_DOUBLE 
 , 
  
 SQL_DOUBLE 
 , 
  
 0 
 , 
  
 0 
 , 
  
 & 
 float_val 
 , 
  
 0 
 , 
  
 NULL 
 ); 
 // 3. Execute statement 
 status 
  
 = 
  
 SQLExecute 
 ( 
 hstmt 
 ); 

Multi-statement scripts

 // 1. Prepare and execute the multi-statement script 
 std 
 :: 
 string 
  
 query 
  
 = 
  
 "CREATE OR REPLACE TABLE MyTable (StringField STRING, IntegerField INTEGER); " 
  
 "INSERT INTO MyTable VALUES ('example', 123); " 
  
 "SELECT * FROM MyTable;" 
 ; 
 status 
  
 = 
  
 SQLExecDirect 
 ( 
 hstmt 
 , 
  
 ( 
 SQLCHAR 
 * 
 ) 
 query 
 . 
 c_str 
 (), 
  
 SQL_NTS 
 ); 
 // 2. Process results for each statement using SQLMoreResults 
 do 
  
 { 
  
 SQLSMALLINT 
  
 num_cols 
 ; 
  
 status 
  
 = 
  
 SQLNumResultCols 
 ( 
 hstmt 
 , 
  
 & 
 num_cols 
 ); 
  
 if 
  
 ( 
 num_cols 
  
 > 
  
 0 
 ) 
  
 { 
  
 // This is a result-returning statement (e.g., SELECT) 
  
 while 
  
 ( 
 SQLFetch 
 ( 
 hstmt 
 ) 
  
 == 
  
 SQL_SUCCESS 
 ) 
  
 { 
  
 // Process rows... 
  
 } 
  
 } 
  
 else 
  
 { 
  
 // This is a non-result statement (e.g., CREATE, INSERT) 
  
 SQLLEN 
  
 row_count 
 ; 
  
 SQLRowCount 
 ( 
 hstmt 
 , 
  
 & 
 row_count 
 ); 
  
 // Process affected rows... 
  
 } 
 } 
  
 while 
  
 ( 
 SQLMoreResults 
 ( 
 hstmt 
 ) 
  
 == 
  
 SQL_SUCCESS 
 ); 

Pricing

Querying through the ODBC driver for BigQuery is subject to standard BigQuery analysis pricing .