The CData Cmdlets PowerShell Module 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 cmdlet uses these to report metadata about the Google BigQuery projects and datsets it is connected to.

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