Console
    Start free

    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 Startmenu, go to ODBC Data Sources, and select the version that has the same bitness as your client application.
      2. On the ODBC Data Source Administratorpage, click the Driverstab.
      3. In the list of installed ODBC drivers, locate ODBC Driver for BigQuery.
      4. Select either the System DSNtab to create a DSN for all users or the User DSNtab 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 Sourcedialog, select ODBC Driver for BigQuery, and then click Finish. The ODBC Driver for BigQuery DSN Setupdialog opens.
      7. In the Data Source Namefield, 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
    INT64 SQL_BIGINT
    BOOL SQL_BIT
    DATE SQL_TYPE_DATE
    FLOAT64 SQL_DOUBLE
    TIME SQL_TYPE_TIME
    TIMESTAMP SQL_TYPE_TIMESTAMP
    DATETIME SQL_TYPE_TIMESTAMP
    BYTES SQL_VARBINARY
    STRING SQL_VARCHAR
    ARRAY SQL_VARCHAR
    STRUCT SQL_VARCHAR
    INTERVAL SQL_VARCHAR
    JSON SQL_VARCHAR
    GEOGRAPHY SQL_VARCHAR
    RANGE SQL_VARCHAR
    NUMERIC SQL_NUMERIC
    BIGNUMERIC SQL_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
    Design a Mobile Site
    View Site in Mobile | Classic
    Share by: