Configuring Advanced Options

You can configure advanced options to modify the behavior of the connector.

To configure advanced options:

  1. To access advanced options, open the ODBC Data Source Administrator where you created the DSN, then select the DSN, then click Configure, and then click Advanced Options.
  2. From the Language Dialect drop-down list, select the SQL syntax to use when executing queries:
    • To use standard SQL syntax, select Standard SQL.
    • Or, to use the legacy BigQuery SQL syntax, select Legacy SQL.
  3. To allow query results that are larger than the BigQuery maximum result sizes when using Legacy SQL, select the Allow Large Result Sets check box.

    4. Note:

    • This option is only available if the Language Dialect drop-down list is set to Legacy SQL. Large result sets are always enabled if the Language Dialect drop-down list is set to Standard SQL.
    • For more information about BigQuery maximum result sizes, see "Quotas and Limits" in the Google BigQuery documentation: https://cloud.google.com/bigquery/quotas#query_jobs.
  4. To specify the dataset that stores temporary tables for large result sets, do one of the following:
    • To use the default dataset with the ID _bqodbc_temp_tables, select the Use Default _bqodbc_temp_tables Large Results Dataset check box.
    • Or, to specify a different dataset, clear the Use Default _bqodbc_temp_tables Large Results Dataset check box and, in the Dataset Name For Large Result Sets field, type the ID of the BigQuery dataset that you want to use.

    Note:

    • If the dataset does not exist and the data store specifies the US region, the connector creates the dataset.
    • These options are only available if the connector is configured to use large result sets.
  5. In the Default temp table expiration time (ms) field, type the length of time, in milliseconds, for which the temporary tables exist for large results datasets. The minimum value is 3600000.

    6. Note:

    This option is only available if the connector is configured to use large result sets.

  6. In the Rows Per Block field, type the maximum number of rows to fetch for each data request.
  7. To use the High-Throughput API to handle large result sets more efficiently, select the Allow High-Throughput API for Large Results queries check box. For more information, including prerequisites and detailed instructions, see Configuring the High-Throughput API.
  8. To use a customer-managed encryption key (CMEK) when executing queries, in the Path To CMEK field, type the resource ID of the CMEK. For more information, see "Protecting Data with Cloud KMS Keys" in the Google BigQuery documentation: https://cloud.google.com/bigquery/docs/customer-managed-encryption.
    9. Important:

    Important:

    • Do not specify a CMEK unless you are certain that it is the correct value to use. If you execute an INSERT statement with an incorrect CMEK, the connector returns an error or corrupts the table.
    • The connector uses the specified CMEK for all queries.
    • The KMSKeyName property is used only when the connector is configured to use the destination tables.
  9. In the Default String Column Length field, type the maximum number of characters that can be contained in STRING columns.
  10. To create a session ID from the first executed query on a connection, select the Enable Session check box.
    • Optionally, in the Session Location field, type the desired location.
  11. To return data as SQL_WVARCHAR data instead of SQL_VARCHAR data, select the Use SQL_WVARCHAR Instead Of SQL_VARCHAR check box.

    12. Note:

    This option applies only to result set columns that the connector would normally return as SQL_VARCHAR columns. It does not convert all columns into SQL_WVARCHAR.

  12. To access public projects and use them as catalogs for the connection, in the Additional Projects field, type a comma-separated list of project names.
  13. To pass properties down to the server when inserting a job, in the Query Properties field, type a comma-separated list of key value pairs.
  14. To save your settings and close the Advanced Options dialog box, click OK.

Related topics