Version 2021

How to Create a DSN Connection for Google BigQuery

  1. Open the Microsoft ODBC Administrator and choose Add.

  2. Select MicroStrategy Google BigQuery ODBC Driver to create a new data source.
  3. Enter a new Data Source Name.
  4. Set the OAuth Mechanism drop-down to User Authentication.

  5. Click Sign In to generate the refresh token that will authorize your connection to Google BigQuery.
  6. Copy and paste the code you receive into the Confirmation Code dialog.
  7. Click inside the Refresh Token dialog. The dialog automatically populates with the refresh token.
  8. Select a Catalog (Project) using the drop-down.
  9. Click Test to confirm the DSN can successfully connect to Google BigQuery.
  10. To enable faster data transfer, enable the high-throughput API.

    Before enabling this setting, the Storage API must be enabled in the Google BigQuery project. Note that Storage API is only supported if the Google BigQuery dataset exists in the US or EU regions.

    To transfer result sets larger than 10 GB in size, extend the LargeResultsTempTableExpirationTime to 36000000 milliseconds, which is the equivalent of 10 hours or more.

    1. Click Advanced Options and check the Enable High-Throughput API checkbox.
    2. Set Minimum Query Result Size for HTAPI to 1000.
    3. Set Ratio of Results to Rows Per Block to 3.

    4. Click OK.
  11. To enable dataset listing from multiple projects, click Advanced Options.
  12. Click OK.

  1. Open the MicroStrategy odbc.ini.example file and locate the example DSN for the Google BigQuery ODBC driver.

    If you decide to not specify the dataset prefix, ensure that the DefaultDataset= field has a value associated with it. DefaultDataset= is the name of the dataset that the driver queries by default. Specifying a default dataset enables you to use unqualified table names in SQL statements. The driver treats unqualified tables as part of the default dataset.

  2. Copy the example DSN entries from the odbc.ini.example file into the MicroStrategy odbc.ini file.
  3. Add the name of the Google BigQuery project in the Catalog entry of the DSN.

  4. Generate the refresh token to authorize your connection to Google BigQuery:

    1. Go to the BigQuery Client Tools page in a browser.
    2. Enter your credentials for accessing BigQuery and click Sign In.
    3. Select Allow on the BigQuery Client Tools page.

    4. Copy the code provided into the get_refresh_token.sh shell script found in the library directory of the MicroStrategy installation directory.

      /get_refresh_token.sh YourAccessCodeWillGoHereAfterYouGenerateItFromGoogle

    5. Run the script.
    6. Copy the refresh token from the output of the script.

      /get_refresh_token.sh YourAccessCodeWillGoHereAfterYouGenerateItFromGoogle
      refresh_token : YouWillFindTheRefreshTokenHereAndWillCopyThisToTheDSN
    7. In the odbc.ini file, set the RefreshToken value for the MicroStrategy Google BigQuery DSN by pasting in the refresh token.

  5. To enable faster data transfer, enable the high-throughput API option by setting the parameter EnableHTAPI from 0 to 1.

    Before changing this parameter, the Storage API must be enabled in the Google BigQuery project. Note that Storage API is only supported if the Google BigQuery dataset exists in the US or EU regions.

    To transfer result sets larger than 10 GB in size, extend the LargeResultsTempTableExpirationTime to 36000000 milliseconds, which is the equivalent of 10 hours or more.

  6. Save the odbc.ini file and exit.