Use the ODBC driver for BigQuery

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

The ODBC driver for BigQuery is available under the Apache 2.0 license.

Before you begin

  1. Make sure that you're familiar with ODBC drivers and driver managers.

  2. Ensure that your operating system meets the following requirements:

    Operating system Supported architectures Minimum version and dependencies
    Windows 32-bit (x86), 64-bit (x64) Version: Windows 10, Windows Server 2016 or later

    Dependency: Microsoft Visual C++ Redistributable for Visual Studio 2019 or 2022
    macOS 64-bit (x86_64), ARM64 (Apple Silicon) Version: macOS 12 (Monterey) or later

    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 that you add the installation directory to your LD_LIBRARY_PATH.

  3. Authenticate to BigQuery, and take note of the following information, which is used later when you establish a connection with the ODBC 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 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 BYOID_SubjectTokenType
    STS token exchange endpoint https://sts.googleapis.com/v1/token BYOID_TokenUrl
    Application Default Credentials None N/A N/A

Install and configure the ODBC driver

You can install and configure the ODBC driver for BigQuery using either a Windows or non-Windows operating system.

Windows

  1. Install the driver that corresponds to your application's architecture:

  2. Create a Data Source Name (DSN) by doing the following:

    1. From the Windows Start menu, go to ODBC Data Sources, and select the version that has the same bitness as your client application.
    2. On the ODBC Data Source Administrator page, click the Drivers tab.
    3. In the list of installed ODBC drivers, locate ODBC Driver for BigQuery.
    4. Select either the System DSN tab to create a DSN for all users or the User DSN tab to create a DSN for the current user. System DSNs are generally recommended because some applications load data using different user accounts and might not detect other User DSNs.
    5. Click Add.
    6. In the Create New Data Source dialog, select ODBC Driver for BigQuery, and then click Finish. The ODBC Driver for BigQuery DSN Setup dialog opens.
    7. In the Data Source Name field, enter a name for your DSN.
    8. Add connection properties. For a full list of properties, see Connection properties.

Non-Windows

  1. Install the driver that corresponds to your operating system:

  2. Extract the contents of the downloaded ZIP or TAR file.

  3. Move the contents of the ZIP or TAR file to the directory where you want to install the connector. The ODBC driver for BigQuery shared object path is INSTALL_DIR/lib/libgoogle_cloud_odbc_bq_driver.so, where INSTALL_DIR is your installation directory.

  4. Update your .ini files to reflect the new path of the connector.

    The following example updates the .ini files in a Linux system:

    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

    Replace VERSION with the driver version.

Establish a connection

To establish a connection between your application and BigQuery with the ODBC driver for BigQuery, identify your connection string. You can skip this step if you already configured connection properties through your DSN.

The connection string has the following format:

Driver=ODBC Driver for BigQuery;ProjectId=PROJECT_ID;OAuthType=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 that you used. Select one of the following:
    • 0: for service account authentication
    • 3: for Application Default Credential authentication
    • 4: for Workload Identity Federation or Workforce Identity Federation authentication
  • 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, KeyFilePath=my-sa-key, if you authenticated with a service account.
  • OTHER_PROPS (optional): additional connection properties for the ODBC driver, listed in the property_1=value_1; property_2=value_2;... format. For a full list of connection properties, see Connection properties.

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.

Connection property Description Default value Data type Required
AdditionalProjects Projects that the driver can access for queries and metadata operations, in addition to the primary project set by the ProjectId property. N/A Comma-separated string No
AllowHtapiForLargeResults Determines whether the driver can use the BigQuery Storage Read API. 0 Boolean No
AllowLargeResults Determines if the driver processes query results that are larger than 128 MB when the QueryDialect property is set to BIG_QUERY. If the QueryDialect property is set to SQL, the driver always processes large query results. 0 Boolean No
BYOID_AudienceUrl Contains the resource name for the Workload Identity Pool or the Workforce Pool and the provider identifier in that pool. N/A String Only when OAuthMechanism=4
BYOID_CredentialSource Sets the necessary information to retrieve the token itself, as well as some environmental information. N/A String Only when OAuthMechanism=4
BYOID_PoolUserProject Set the project when it is a Workforce Pool and not a Workload Identity Pool. N/A String Only when OAuthMechanism=4 and using a Workforce Pool
BYOID_SubjectTokenType Sets the STS token type based on the Oauth2.0 token exchange specification. Expected values include:
  • 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
 N/A String Only when OAuthMechanism=4
BYOID_TokenUrl Sets the STS token exchange endpoint. https://sts.googleapis.com/v1/token String No
DefaultDataset Serves as a designated dataset within a project that the driver automatically references when you execute queries without explicitly specifying a dataset. N/A String No
FilterTablesOnDefaultDataset Determines the scope of metadata that table or column metadata methods return. When false, no filtering occurs. You must also set the DefaultDataset property to enable filtering. FALSE Boolean No
EnableSession Determines whether a connection starts a session. When enabled, the first query run by that particular connection starts a session and the driver passes the session ID to all subsequent queries. 0 Boolean No
JobCreationMode Lets you enable the low latency query path. Choose one of the following:
  • 1: The driver creates jobs for every query (JOB_CREATION_REQUIRED)
  • 2: The driver executes queries without jobs (JOB_CREATION_OPTIONAL)
 2 Integer No
KeyFilePath The path to the service account key when using service account authentication. N/A String Only when OAuthMechanism=0
KMSKeyName Specifies the name of the KMS key to use when encrypting and decrypting data. N/A String No
LargeResultsDataSetId Specifies the destination dataset for storing large query results. N/A String No
LargeResultsDatasetExpirationTime Specifies the lifetime of all tables in the large results dataset, in milliseconds. 3600000 Long No
Location Specifies the location where the driver creates or queries datasets. N/A String No
LogLevel Limits the detail that the driver logs during interactions. Choose one of the following:
  • 0: OFF
  • 1: ERROR
  • 2: WARNING
  • 3: INFO
 0 Integer No
LogPath Specifies the directory where the driver writes log files. N/A String No
LogFileCount Specifies the maximum number of log files to keep. 0 Integer No
LogFileSize Specifies the maximum size of each log file in bytes. 0 Long No
MaxResults Specifies the number of results per page in the BigQuery API result. 10000 Long No
MaxThreads Defines the maximum number of threads that the connector can use for concurrent processing in a thread pool. To configure this property as a connector-wide setting for non-Windows connectors, specify it in the googlebigqueryodbc.ini file. 8 Integer No
OAuthMechanism The authentication type. Choose one of the following:
  • 0: Service account authentication
  • 3: Application Default Credential authentication
  • 4: Workload Identity Federation or Workforce Identity Federation authentication
 N/A Integer Yes
ProjectId The default project ID for the driver. The driver uses this project to execute queries and bills it for resource usage. N/A String Yes
ProxyHost Hostname or IP address of a proxy server. N/A String No
ProxyPort Port number on which the proxy server is listening. N/A String No
ProxyPwd Password for authentication when connecting through a proxy server. N/A String No
ProxyUid Username for authentication when connecting through a proxy server. N/A String No
PrivateServiceConnectUris Custom endpoints to overwrite default endpoints. Examples:
  • BIGQUERY=https://bigquery.us-east4.rep.googleapis.com/
  • READ_API=bigquerystorage.us-east4.rep.googleapis.com
  • OAUTH2=oauth2.us-east4.rep.googleapis.com
 N/A Comma-separated string No
QueryDialect Specifies which query dialect to use. Use SQL for GoogleSQL (highly recommended) and BIG_QUERY for legacy SQL. SQL String No
QueryProperties Configures properties which can modify the query behavior. N/A Map<String, String> No
UniverseDomain Specifies the universe domain for your organization. googleapis.com String No
UseQueryCache Enables the query caching feature in BigQuery. true Boolean No

Data type mapping

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

GoogleSQL type ODBC SQL type
INT64SQL_BIGINT
BOOLSQL_BIT
DATESQL_TYPE_DATE
FLOAT64SQL_DOUBLE
TIMESQL_TYPE_TIME
TIMESTAMPSQL_TYPE_TIMESTAMP
DATETIMESQL_TYPE_TIMESTAMP
BYTESSQL_VARBINARY
STRINGSQL_VARCHAR
ARRAYSQL_VARCHAR
STRUCTSQL_VARCHAR
INTERVALSQL_VARCHAR
JSONSQL_VARCHAR
GEOGRAPHYSQL_VARCHAR
RANGESQL_VARCHAR
NUMERICSQL_NUMERIC
BIGNUMERICSQL_NUMERIC

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

You can download the ODBC driver for BigQuery at no cost. However, when you use the driver, standard BigQuery analysis pricing applies.

What's next