Google BigQuery¶

Google BigQuery is an enterprise data warehouse that allows to store and query massive datasets by enabling super-fast SQL queries using the processing power of Google's infrastructure.

Setup¶

Before using the power of Google BigQuery within ChartFactor, you need to set up a Google Cloud account and a project within it. You will also need to create a OAuth client ID for that project in order to authenticate in your cloud account using Oauth2.

To connecto to Google BigQuery from your browser, include in your HTML page the following:

The google api client library
The ChartFactor BigQuery provider library

Example below:

<script src="https://accounts.google.com/gsi/client" async defer></script>
<script src="./cft-google-bigquery-provider.min.js"></script>

Then, declare your provider in your Javascript code as follows:

// define providers
var providers = [{
    name:'Google BQ',
    provider:'google-bigquery',
    projectId: 'my-project-id',
    clientId: 'oauthClientId', // Browser environment
    scope: [],  // Optional
    file: "../bigquery-private-key.json", // Non browser environment
    otherProjects: [] //Optional
}]

The description of the properties above is below:

name¶

This is the name you'd like to use to identify your provider. It is a standard property for all providers.

provider¶

This is the provider type, in this case google-bigquery

projectId¶

This is the project ID in your Google Cloud account where your BigQuery data is stored. This is required to access your datasets and tables.

clientId¶

Defaults to ChartFactor’s Client ID if not provided. This is a unique application identifier assigned by Google Cloud. You should obtain your own Client ID when deploying your ChartFactor web apps on your own domain. To obtain your own Client ID, follow these steps:

Go to the Google Cloud Console.
Navigate to "APIs & Services" > "Credentials".
Click on "Create Credentials" and select "OAuth client ID".
Configure the consent screen if you haven't already (this is required for OAuth credentials).
Choose the application type "Web Application" and provide the necessary details.
Click "Create". The Client ID will be displayed on the screen and can also be found in the "Credentials" section under "OAuth 2.0 Client IDs".

The Client ID is applicable when running on the browser to enable the OAuth2 authentication. If you are deploying the BigQuery provider on the server side, you need to set a different property with your service account private key as specified below.

scope¶

This property is optional. It is needed only if your application requires special access to Google services or APIs that are not included in the default scope provided by ChartFactor for the Google BigQuery provider. The scopes already included are:

1	`'https://www.googleapis.com/auth/bigquery.readonly'`

file¶

This property is only applicable when deploying the BigQuery provider on the server-side in a Node.js environment. It is the location of the private key JSON file containing the security information necessary to establish a connection to BigQuery. An example of a private key JSON file is below:

{
  "type": "service_account",
  "project_id": "project-id",
  "private_key_id": "1c934j3l2l7l5j6h3j5h67h5h3h9j3b5b6h8k1k3",
  "private_key": "private_key",
  "client_email": "bigquery@project-id.iam.gserviceaccount.com",
  "client_id": "client_id",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/bigquery%40project-id.iam.gserviceaccount.com"
}

otherProjects¶

This is a list of projects that you would also like to access, such as projects containing public datasets. This property is optional. By default the public project bigquery-public-data will always be included.

Using your data provider¶

Now that you have your providers array initialized (previous section) with your BigQuery provider, use the ChartFactor setProviders() method to set your data provider definition:

cf.setProviders(providers);

Google BigQuery uses a hierarchical naming convention to identify its projects, datasets, and tables. You must use this same naming convention to specify table names in the aql:

cf.provider('Google BQ')
.source('bigquery-public-data:austin_crime.crime')
.groupby(...)
...

In general the format used is the following: project-id:dataset.table

Nested structures¶

Google BigQuery allows objects (nested and repeated data) to be stored by using fields of type RECORD. You can learn more about nested structures here.

ChartFactor supports the interaction with these structure by flattening them transparently so they can be used as any other field:

let repoUrl = cf.Attribute('repository.url')
                .limit(10)
                .sort('asc', 'repository.url')
cf.provider('Google BQ')
.source('bigquery-public-data:samples:github_nested')
...

Response timeout¶

In some occasions, the Google BigQuery's engine may take time to process and execute queries, especially if the dataset is large and the queries have not been cached.

By default, when a request is sent by the BigQuery provider to the data engine, it will wait for a 60,000 milliseconds (a full minute) to receive the response. After that time it will throw a Timeout error for the query. This value can be modified to a greater one in the custom metadata:

// define providers
var providers = [{
    name:'Google BQ',
    provider:'google-bigquery',
    ...
    metadata: {
      timeoutMs: 120000
    }
}]

In the example above we are setting timeoutMs with a waiting time of 2 mins for the response to come back. The value should always be set in milliseconds.

Supported Aggregations Out-Of-The-Box¶

SUM¶

    var metric = cf.Metric("amount","sum");

AVG¶

    var metric = cf.Metric("amount","avg");

MIN¶

    var metric = cf.Metric("amount","min");

MAX¶

    var metric = cf.Metric("amount","max");

COUNT DISTINCT¶

    var metric = cf.Metric("my_attribute","unique");

PERCENTILES¶

    var metric = cf.Metric('commission', 'percentiles');

GEOHASH¶

Please refer to Geo Queries for more information.

Dependencies¶

Google Api javascript client