Google Big Query

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

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}
Was this article helpful?
0 out of 0 found this helpful

Comments

0 comments

Please sign in to leave a comment.