Google Big Query

Note: Deprecated with the 2022 Winter release.

To query Google BigQuery a file with authentication information (secrets file) must be obtained from the Google BigQuery console. To utilize large result sets it is required that the user authenticating must have permissions to create tables when executing the query job.

Connection String Parameters:

Query parameters:

The query executed must be a standard Google BigQuery SQL syntax, additional it is possible to specify additional options in this form

SELECT column_name FROM table_name [OPTIONS:optionlist]

Everything specified in the options section will be parsed and supplied to the driver as parameters during runtime and will not be executed as a part of the query.

An example of an options list could be

[OPTIONS:allowLargeResults=true;destinationDatasetID=MyTempTables;
destinationTableID=tmpSalesTrans20160401;writeDisposition=WRITE_TRUNCATE]

For explanation of the options please refer to the Google BigQuery documentation.

The query also takes a special command in the form of (CASE SENSITIVE!):

SELECT * FROM INFORMATION_SCHEMA.tables

This will return a complete list of tables available to the user with additional meta data information as medication date etc. This can be extended with this: (OPTIONS:infodatasetid=dims) restricting the returned information to the dataset dims:

SELECT * FROM INFORMATION_SCHEMA.tables (OPTIONS:infodatasetid=facts)

The meta data information returned is:

• TableProject string
• TableDataSet string
• TableName string
• SizeKb double
• RowCount long
• CreationDate datetime
• ModifiedDate datetime
• DataLocation string

Example:

LOAD ASSEMBLY 'TARGIT.GoogleBigQuery.dll'
DATASOURCE bigquery = DOTNET CONNECTION 'TARGIT.GoogleBigQuery.GoogleBigQueryConnection' 'projectid=project_id;serviceaccountsecretspath=d:\demo\CasualClothing-2070791221.json;'
IMPORT tablelist=bigquery.{SELECT * FROM INFORMATION_SCHEMA.tables}
IMPORT SalesData = bigquery.{SELECT * from facts.SalesInformation}