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 Start menu, 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 Drivers tab.
3. Locate the ODBC Driver for BigQuery as 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 DSN tab.
   • To create a DSN for all users, click the System DSN tab. 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 Source dialog, select ODBC Driver for BigQuery and then click Finish.
7. The ODBC Driver for BigQuery DSN Setup dialog opens.
8. In the Data Source Name field, 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.