The CData SSIS Components for Google BigQuery 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 component uses these to report metadata about the Google BigQuery projects and datsets it is connected to.

In addition, the component 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 component 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 component 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 component, 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 component 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 component, there is also support for server-side stored procedures defined in Google BigQuery. The component supports both CALL and EXEC using the procedure's parameter names. Note that component 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 component 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 component 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