Google BigQuery can be used as a data source with SQL-compatible database connector and official BigQuery ODBC driver.
There are no any limitations on the dataset size and in this you can get reports by billions-size datasets in the near real-time. This youtube video illustrates how sample 1.3Gb data is aggregated by BigQuery in seconds and displayed as a pivot table in SeekTable.
Connection String should include settings that are supported by official BigQuery ODBC driver:
ClientId=oauth_client_id;ClientSecret=oauth_client_secret;RefreshToken=your_google_oauth_refresh_token;Catalog=your_api_project_id;
Catalog | The name of your BigQuery project. This project is the default project that the Simba ODBC Driver for Google BigQuery queries against. |
---|---|
RefreshToken | The refresh token that you obtain from Google for authorizing access to BigQuery. Section below explains how to generate the token. |
ClientId |
OAuth Client ID used for refresh token creation (may be omitted if refresh token was generated with get_refresh_token.py script).
|
ClientSecret |
Corresponding OAuth Client secret (may be omitted if refresh token was generated with get_refresh_token.py script).
|
If you don't have a refresh token provided by your BigQuery administrator, you can get it in one of the following ways:
ClientId=@BQ_SEEKTABLE_CLIENT_ID;ClientSecret=@BQ_SEEKTABLE_CLIENT_SECRET;
https://developers.google.com/oauthplayground
https://www.googleapis.com/auth/bigqueryand click on "Authorize APIs" button.
ClientId
and ClientSecret
connection string options.
Alternatively, you can
(section "Configuring Authentication on a Non-Windows Machine")
and generate a refresh token using Simba-provided python script get_refresh_token.py
.
If your table has an ARRAY-type columns and you got an error like Error converting invalid input with source encoding UTF-8 using ICU:
SELECT * except(array_col1, array_col2) FROM some_tableand save the cube form.
SELECT * FROM some_tabledo NOT check Infer dimensions and measures by dataset. Save the form.
To define date-part dimensions (date's "year", "month", "day" etc) you may add dimensions defined with an appropriate SQL expression:
Field
EXTRACT(YEAR FROM date_column)
EXTRACT(MONTH FROM date_column)
+ Format={0:MMM}
(Jan, Feb etc) or {0:MMMM} (January, February etc)
EXTRACT(DAY FROM date_column)
EXTRACT(DAYOFWEEK FROM date_column)-1
+ Format={0:ddd}
(Mon, Tue etc) or {0:dddd}
(Monday, Tuesday etc)EXTRACT(QUARTER FROM date_column)
EXTRACT(ISOWEEK FROM date_column)