TDV Adapter for Google BigQuery

Build 22.0.8462

Data Model

The Google BigQuery Adapter models the data as defined within Google BigQuery for the ProjectId and DatasetId configured.

Views

Views are client-side tables that cannot be modified. The adapter uses these to report metadata about the Google BigQuery projects and datsets it is connected to.

In addition, the adapter supports server-side views defined within Google BigQuery. These views may be used in SELECT statements the same way as tables. However, view schemas can easily become out of date and require the adapter to refresh them. Please see RefreshViewSchemas for more details.

External Data Sources

Google BigQuery allows creating external datasets that store data in Amazon S3 regions (like aws-us-east-1) or Azure Storage regions (like azure-useast2). The adapter supports these datasets with two major limitations:

  1. Google BigQuery treats external tables as read-only. You cannot execute INSERT, UPDATE or DELETE queries on them. They are also incompatible with DestinationTable because Google BigQuery cannot create destination tables in an external dataset.
  2. Google BigQuery does not support the Storage API for external datasets. You must disable the UseStorageApi option in order to query them. This limits the read throughput of the adapter, so if you are executing large queries it is recommended that you copy your data into Google BigQuery for the best performance.

Stored Procedures

Stored Procedures are function-like interfaces to the data source. The adapter uses these to manage Google BigQuery tables and jobs and to perform OAuth operations.

In addition to the client-side stored procedures offered by the adapter, there is also support for server-side stored procedures defined in Google BigQuery. The adapter supports both CALL and EXEC using the procedure's parameter names. Note that adapter only supports IN parameters and resultset return values.

CALL `psychic-valve-137816`.Northwind.MostPopularProduct()
CALL `psychic-valve-137816`.Northwind.GetStockedValue(24, 0.75)

EXEC `psychic-valve-137816`.Northwind.MostPopularProduct
EXEC `psychic-valve-137816`.Northwind.GetSockedValue productId = 24, discountRate = 0.75

Additional Metadata

Table Descriptions

Google BigQuery supports setting descriptions on tables but the adapter does not report these by default. ShowTableDescriptions can be used to report table descriptions.

Primary Keys

Google BigQuery does not support primary keys natively, but the adapter allows you to define them so they can be used in environments that require primary keys to modify data. Primary keys can be defined using the PrimaryKeyIdentifiers option.

Policy Tags

If policy tags from the Data Catalog service are defined on a table, they can be retrieved from the system tables using the PolicyTags column:

SELECT ColumnName, PolicyTags FROM sys_tablecolumns
WHERE CatalogName = 'psychic-valve-137816'
AND SchemaName = 'Northwind'
AND TableName = 'Customers

Copyright (c) 2023 CData Software, Inc. - All rights reserved.
Build 22.0.8462