2 BIGQUERY
============================================================================== Magnitude Simba Google BigQuery ODBC Data Connector Release Notes ==============================================================================
The release notes provide details of enhancements, features, known issues, and workflow changes in Simba Google BigQuery ODBC Connector 2.5.0, as well as the version history.
For information about upcoming support deprecations or removals, see the Workflow Changes section. Deprecated features will not receive any updates, but will continue to be usable in their current state until support is removed in a future release.
2.5.0 ========================================================================
Released 2022-09-07
Enhancements & New Features
[GAUSS-1506] External account authentication support
You can now configure the connector to authenticate the connection using an external account (workforce identity federation), with limited support, using Azure AD and Okta identity providers. For more information, see the Installation and Configuration Guide.
[GAUSS-1488] Private Service Connect support
You can now configure the connector to use Private Service Connect URLs. To do this, set the PrivateServiceConnectUris property to the desired URI. For more information, see the Installation and Configuration Guide.
[GAUSS-1493] Transaction API support
The connector now supports ODBC transaction APIs. BigQuery supports multi-statement transactions inside a single query, or across multiple queries, when using sessions. For more information about transactions, see: https://cloud.google.com/bigquery/docs/reference/standard-sql/transactions.
To use transaction APIs, and work with transactions across multiple queries, set the EnableSession property to 1. For more information about sessions, see: https://cloud.google.com/bigquery/docs/sessions-intro
[GAUSS-1462] Default project for dataset support
The connector is now verified to use a default project for datasets. To do this, set the dataset_project_id property in the QueryProperties connection property to the desired project. For more information, see the Installation and Configuration Guide and the Google BigQuery documentation: https://cloud.google.com/bigquery/docs/reference/rest/v2/ConnectionProperty
[GAUSS-1486] MATERIALIZED_VIEW table support
MATERIALIZED_VIEW has been added to the list of table types. To retrieve these table types, configure SQLTables to TABLE_TYPES_ONLY.
[GAUSS-1457] JSON data type support
The connector now supports the JSON data type. For more information, see the Installation and Configuration Guide and the Google BigQuery documentation: https://cloud.google.com/bigquery/docs/reference/standard-sql/json-data
[GAUSS-1526] Upgraded third-party libraries
The connector has been updated to use the following libraries:
- LibCurl version 7.84.0 (previously 7.78.0)
- OpenSSL 1.1.1q (previously 1.1.1n)
[GAUSS-1537] Support for multiple operating systems
The connector now supports the following operating systems:
- Windows Server 2022
- macOS 11 and 12
- RedHat Enterprise Linux (RHEL) 8
- Debian 10 and 11
- Ubuntu 18.04 and 20.04
For a list of supported operating systems, see the Installation and Configuration Guide.
Resolved Issues The following issues have been resolved in Simba Google BigQuery ODBC Connector 2.5.0.
[GAUSS-1475] In some cases, when a semicolon ( ; ) is present in a comment, the connector returns incorrect results.
[GAUSS-1491] When a GHighThroughputApiError exception occurs, the connector does not return an error message in the ErrorMessage file.
This issue has been resolved. Now, the connector returns an error message.
[GAUSS-1495] On non-Windows platforms, the connector makes redundant authentication calls for each SQL statement.
[GAUSS-1499] When the COMMIT TRANSACTIONS statement fails, the connector does not return an error.
[GAUSS-1507] In some cases, the connector hides HTTP errors.
Known Issues The following are known issues that you may encounter due to limitations in the data source, the connector, or an application.
[GAUSS-1535] On Windows, encrypted connection property values cannot be decrypted. This is due to a security update. The following properties are affected and must be regenerated:
- Auth_AccessToken
- Auth_BaseRedirectUri
- Auth_Client_ID
- Auth_Client_Secret
- Auth_CompletedRedirectUri
- Auth_RefreshToken
- AuthorizationUrl
- ByoidSubjectToken
- ClientId
- ClientSecret
- KeyFile
- KeyFilePath
- ProxyPwd
- RefreshToken
The connector does not support parameterized types for Resultset and Parameter metadata.
This is a limitation of the Google BigQuery server.
The connector does not support parameters in the exception block.
This is a limitation of the Google BigQuery server discovered on Mar 2021.
On macOS or Linux platforms, when the connector converts SQL_DOUBLE data to SQL_C_CHAR or SQL_C_WCHAR, data which is small or large enough to require representation in scientific notation may prepend a 0 to the exponent.
This is a limitation of Google BigQuery. For a list of BigQuery data types that the connector maps to the SQL_DOUBLE ODBC type, see the Installation and Configuration Guide.
When casting data, you must specify the data type according to Google BigQuery standards.
When casting data to a specific data type, you must use the corresponding data type name shown in the “Casting” section of the Query Reference: https://cloud.google.com/bigquery/sql-reference/functions-and-operators#cas ting
For example, to cast the “salary” column to the INTEGER type, you must specify INT64 instead of INTEGER:
SELECT position, CAST(salary AS INT64) from Employee
When using the Standard SQL dialect, the connector’s ODBC escape functionality is subject to the following limitations:
Standard SQL does not support the seed in the RAND([seed]) scalar function. As a result, the connector maps RAND() and RAND(6) to RAND().
For the following scalar functions, BigQuery only returns values in UTC, but ODBC expects the values in local time:
- CURDATE()
- CURRENT_DATE()
- CURRENT_TIME[(TIME_PRECISION)]
- CURRENT_TIMESTAMP[(TIME_PRECISION)]
- CURTIME()
- NOW()
Time precision values are not supported for the CURRENT_TIME[(TIME_PRECISION)] and CURRENT_TIMESTAMP[(TIME_PRECISION)] scalar functions.
TIME data types are not supported for the following scalar functions:
- EXTRACT(interval FROM datetime)
- TIMESTAMPADD(interval,integer_exp,timestamp_exp
- TIMESTAMPDIFF(interval,timestamp_exp1,timestamp_exp2) For TIMESTAMPADD and TIMESTAMPDIFF, only the TIMESTAMP and DATE data types are supported.
When calling the TIMESTAMPADD() scalar function to work with DAY, WEEK, MONTH, QUARTER, or YEAR intervals, the connector escapes the function and calls DATE_ADD() instead. DATE_ADD() only supports DATE types, so time information is lost if the function is called on TIMESTAMP data.
When calling the TIMESTAMPDIFF() scalar function to work with DAY, MONTH, QUARTER, or YEAR intervals, the connector escapes the function and calls DATE_DIFF() instead. DATE_DIFF() only supports DATE types, so time information is lost if the function is called on TIMESTAMP data.
For the BIT_LENGTH scalar function, only the STRING and BYTES data types are supported. This behavior aligns with the SQL-92 specification, but not the ODBC specification.
When using the Legacy SQL dialect, the connector’s ODBC escape functionality is subject to the following limitations:
For the following scalar functions, BigQuery only returns values in UTC, but ODBC expects the values in local time:
- CURDATE()
- CURRENT_DATE()
- CURRENT_TIME[(TIME_PRECISION)]
- CURRENT_TIMESTAMP[(TIME_PRECISION)]
- CURTIME()
Time precision values are not supported for the CURRENT_TIME[(TIME_PRECISION)] and CURRENT_TIMESTAMP[(TIME_PRECISION)] scalar functions.
For the following scalar functions, TIME data types are not supported. Only the TIMESTAMP and DATE data types are supported.
- TIMESTAMPADD(interval,integer_exp,timestamp_exp
- TIMESTAMPDIFF(interval,timestamp_exp1,timestamp_exp2)
Workflow Changes =============================================================
The following changes may disrupt established workflows for the connector.
In addition to changes that are already implemented in the current version of the connector, this section describes potentially disruptive changes that will be implemented in a future version of the connector, so that you can plan accordingly.
Upcoming ———————————————————————
[GAUSS-1537] Removing support for multiple operating systems
As early as November 2022, the connector will no longer support the following operating systems:
- Windows 7 SP1
- Windows Server 2008 R2 SP1
- macOS 10.14
- SUSE Linux Enterprise Server 11
- Debian 8
- Ubuntu 14.04 and 16.04
For a list of supported operating systems, see the Installation and Configuration Guide.
2.5.0 ————————————————————————
[GAUSS-1508] Updated authentication interface
On Windows, the Connector DSN Setup dialog box has been updated. For more information, see the Installation and Configuration Guide.
2.4.5 ————————————————————————
[GAUSS-1434] Updated MaxThreads property
The default value of the MaxThreads property is now 8. Previously, the default value was 16. For more information, see the Installation and Configuration Guide.
2.3.5 ————————————————————————
[GAUSS-1246] Removed support for macOS earlier than 10.14
Beginning with this release, the connector no longer supports macOS versions earlier than 10.14. For a list of supported macOS versions, see the Installation and Configuration Guide.
2.2.4 ————————————————————————
[GAUSS-980] Removed support for the Visual C++ Redistributable for Visual Studio 2013
Beginning with this release, the driver no longer supports this version of the dependency, and requires Visual C++ Redistributable for Visual Studio 2015 instead.
2.2.2 ————————————————————————
[GAUSS-875] New service endpoints
The driver now uses a new set of service endpoints to connect to the Google BigQuery API. The previous service endpoints have been deprecated. For a list of the new endpoints, see the “Service Endpoints” section of the Installation and Configuration Guide.
[GAUSS-897] Precedence for default large result dataset
If the Use Default _bqodbc_temp_tables Large Results Dataset check box is selected (the UseDefaultLargeResultsDataset property is set to 1) and a dataset is specified in the Dataset Name For Large Result Sets field (the LargeResultsDataSetID property), the driver now uses the default _bqodbc_temp_tables dataset. For more information, see the Installation and Configuration Guide.
2.2.0 ————————————————————————
Linux support changes
Beginning with this release, the Linux version of the driver now requires glibc 2.17 or later to be installed on the target machine.
As a result, the driver no longer supports CentOS 6 or RedHat Enterprise Linux (RHEL) 6. Only CentOS 7, RHEL 7, and SUSE Linux Enterprise Server (SLES) 11 and 12 are supported.
2.1.22 ———————————————————————–
[GAUSS-653] Updated large result set behavior
The driver’s behavior for handling large result sets with legacy SQL has been changed. When the driver sends a query, it checks whether the “Allow Large Results” option is enabled and if there is a dataset name specified. If the option is enabled, it requests a temporary large result set for your data. This data storage has cost implications for your Big Query account, consult the Big Query service documentation for details.
2.1.14 ———————————————————————–
Minimum TLS Version
Beginning with this release, the driver requires a minimum version of TLS for encrypting the data store connection. By default, the driver requires TLS version 1.2. This requirement may cause existing DSNs and connection strings to stop working, if they are used to connect to data stores that use a TLS version earlier than 1.2.
To resolve this, in your DSN or connection string, set the Minimum TLS option (the Min_TLS property) to the appropriate version of TLS for your server. For more information, see the Installation and Configuration Guide.
Large result set handling
If you have a default destination set for large datasets but have not enabled the Allow Large Result Sets option (the AllowLargeResults property) the driver reports an error.
To resolve this, enable the Allow Large Result Sets option (the AllowLargeResults property).
Version History ==============================================================
2.4.6 ========================================================================
Released 2022-04-28
Enhancements & New Features
[GAUSS-1309] Windows Server 2019 support
The connector now supports Windows Server 2019. For a list of supported Windows versions, see the Installation and Configuration Guide.
[GAUSS-1426] Improvements to service account authentication flow
The service account authentication flow has been interally improved and no longer uses the client_x509_cert_url endpoint.
[GAUSS-1442][GAUSS-1474] Upgraded third-party libraries
The connector has been updated to use the following libraries:
- Expat version 2.4.6 (previously 2.2.9)
- OpenSSL 1.1.1n (previously 1.1.1l)
[GAUSS-1447] Improvements to BigQuery connectionProperties flow
BigQuery connectionProperties is now set in dry run in addition to query execution.
Resolved Issues The following issue has been resolved in Simba Google BigQuery ODBC Connector 2.4.6.
[GAUSS-1453] The default Timeout property is incorrectly set to 300 thousand seconds.
This issue has been resolved. The default Timeout property is now set to 300 seconds.
2.4.5 ————————————————————————
Released 2022-01-31
Enhancements & New Features
[GAUSS-1306] Updated trusted CA certificates
The list of trusted CA certificates in the cacerts.pem file has been updated.
[GAUSS-1411][GAUSS-1443] Upgraded LibCurl third-party library
The connector has been updated to use LibCurl version 7.78.0. Previously, the connector used 7.74.0.
[GAUSS-1413] Windows 11 support
The connector now supports Windows 11. For a list of supported Windows versions, see the Installation and Configuration Guide.
Resolved Issues The following issues have been resolved in Simba Google BigQuery ODBC Connector 2.4.5.
[GAUSS-1410] On Windows, when proxy settings are set natively in the connector configuration dialog, the connector displays incorrect Host and Port fields.
[GAUSS-1414] In some cases, when retrieving data using REST API via proxy, the connector terminates unexpectedly.
[GAUSS-1433][GAUS-1375] When a single-line comment contains DDL or DML keywords, the connector does not return the result set.
[GAUSS-1424] The GAPIErr error message is incorrectly reported as GApiErr.
2.4.3 ————————————————————————
Released 2021-10-27
Enhancements & New Features
[GAUSS-1190] EnableSession support
You can now create a session ID from the first executed query on a connection. To do this, select the Enable Session check box (set the EnableSession property to 1). For more information, see the Installation and Configuration Guide.
[GAUSS-1307] EXPORT DATA support
The connector now supports the EXPORT DATA statements in both atomic and script queries as a DML statement.
[GAUSS-1402] UnsupportedHTAPIFallback support
You can now set the connector to fall back to the REST API when using fetch workflows not supported on the High-Throughput API. To do this, set the UnsupportedHTAPIFallback property to 1. For more information, see the Installation and Configuration Guide.
[GAUSS-1358] INTERVAL data type support
The connector now has limited support for the INTERVAL data type. For more information, see the Installation and Configuration Guide and the Google BigQuery documentation: https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#interval_type.
The INTERVAL data type cannot be retrieved through the High-Throughput API. The default connector behavior is to fallback to the REST API for this data. To disable this fallback, set the UnsupportedHTAPIFallback property to 0.
[GAUSS-1401] Upgraded third-party library
The connector has been updated to use OpenSSL version 1.1.1l. Previously, the connector used 1.1.1k.
Resolved Issues The following issues have been resolved in Simba Google BigQuery ODBC Connector 2.4.3.
[GAUSS-1393] When configured through the configuration dialog, the connector does not use the configured proxy port.
[GAUSS-1395] The connector does not allow data blocks larger than 4 MB to be read through the High-Throughout API.
[GAUSS-1396] In some cases, when using logging statements, the connector
terminates unexpectedly.[GAUSS-1390] When logging query parameter values and logging is enabled at the TRACE level, the connector terminates unexpectedly.
[GAUSS-1394] In some cases, the connector does not retrieve all the data from a result set due to a Read API configuration issue.
[GAUSS-1397] When the connector fails to read a block of data through the Read API, the connector treats it as no-data.
This issue has been resolved. The connector now returns an error.
2.4.2 ————————————————————————
Released 2021-09-10
Resolved Issues The following issue has been resolved in Simba Google BigQuery ODBC Connector 2.4.2.
- [GAUSS-1374] In some cases, when a query runs longer than a few seconds, the connector sends requests that are too large.
2.4.1 ————————————————————————
Released 2021-08-26
Enhancements & New Features
[GAUSS-1279] Multi-statement transactions support
The connector now supports multi-statement transactions. For more information, see: https://cloud.google.com/bigquery/docs/reference/standard-sql/transactions.
[GAUSS-1315] Updated gRPC support
The connector now uses gRPC version 1.37.1 for the High-Throughput API.
[GAUSS-1316] Retrieve data through High-Throughput API proxy
The High-Throughput API can now be configured to use a proxy to retrieve data. For more information on how to configure a proxy, see the Installation and Configuration Guide.
[GAUSS-1344] Data control language support
The connector now supports data control language. For more information, see: https://cloud.google.com/bigquery/docs/reference/standard-sql/data-control-language.
Resolved Issues The following issues have been resolved in Simba Google BigQuery ODBC Connector 2.4.1.
[GAUSS-1308] SQLDescribeParam does not return the correct value for NUMERIC data types in the DecimalDigits field.
This issue has been resolved. The connector now reports precision and scale for NUMERIC parameters through SQLDescribeParam.
[GAUSS-1340] In some cases, while retrieving data due to erroneous thread handling in multi-threaded environments, the connector becomes unresponsive.
[GAUSS-1341] The connector does not correctly bind all parameters based on the BigQuery type for the respective column.
[GAUSS-1360] In some cases, the connector successfully executes a script query with DML operations but fails to return the correct number of affected rows.
2.4.0 ————————————————————————
Released 2021-06-30
Enhancements & New Features
[GAUSS-1227][GAUSS-1239][GAUSS-1240][GAUSS-1241] Improved performance for metadata functions
Improvements in parallelization and new design have been made to the following metadata functions:
- SQLColumns
- SQLProcedures
- SQLProcedureColumns
- SQLTables
[GAUSS-1218] New MaxThreads connector-wide property
You can now control the size of the threadpool used by statements to execute concurrently. To do this, set the MaxThreads connector-wide property to the maximum number of threads. For more information on how to set connector-wide properties, see the Installation and Configuration Guide.
[GAUSS-1290] Updated routine argument type support
The routine argument type now supports ANY_TYPE. ANY_TYPE can be considered as SQL_UNKNOWN_TYPE when listing the argument in the SQLProcedureColumns metadata. The type name would be ANY_TYPE.
[GAUSS-1303] Parameterized data types support
The connector now supports parameterized data types. For metadata functions, SQLColumns returns the size, length, precision, and scale related to these types.
Due to current limitations of the BigQuery server on providing metadata for Resultset and Parameter, the connector does not provide specific information (size, scale, etc.) on these parametrized data types when retrieving metadata for them. In these cases, the connector returns the default value.
For example: CREATE TABLE Test(str STRING(100));
The SQLColumn function returns the correct value of 100 for the size and buffer length. SQLDescribeCol which is used for retrieving metadata of Resultset, returns the default string length that has been set for the connector. Due to ODBC limitations, the connector only returns string and byte lengths up to INT32_MAX.
Resolved Issues The following issue has been resolved in Simba Google BigQuery ODBC Connector 2.4.0.
[GAUSS-1255] When a table or view has user only permissions, the connector returns 0 rows.
This issue has been resolved. When using the HTAPI, the user must ensure that their account has the following permissions:
- bigquery.readsessions.create
- bigquery.readsessions.getData
- bigquery.readsessions.update
These permissions are included in the following predefined roles:
- roles/bigquery.admin
- roles/bigquery.user
- roles/bigquery.readSessionUser
==============================================================================