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:
Key | Value | Description |
ProjectId | myerpdata-1234 | The project id |
Serviceaccountsecretspath | myerpdata-1234-123123.json | Path the the with authentication |
| Wiki Markup |
|---|
\\
*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 stringTableDataSet stringTableName stringSizeKb doubleRowCount longCreationDate datetimeModifiedDate datetimeDataLocation 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\} |