Connect to Google Big Query and queries structure
BigQuery jdbc Connector
How to connect
To connect to google BigQuery using the jdbc connector in digdash, do not specify a login or password.
Leave these field empty and add all the info in the URL :
jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=MYBIGQUERYPROJECTID;OAuthType=0;OAuthServiceAcctEmail=EMAILBIGQUERYPROJECT;OAuthPvtKeyPath=/usr/share/digdash_enterprise_2019R2_p20191030_64/access/MYJSON.json;Timeout=3600;AllowLargeResults=1;
Explanation :
MYBIGQUERYPROJECTID : Project id name provided by google in your BigQuery account
EMAILBIGQUERYPROJECT : Gservice email account provided by google
/usr/[...]/MYJSON.json: Export your Big QUery PvtKey (provided by google in your BigQuery account) and place it on the server. Then add the path to it the url.
Connection options in the URL
At the end of the url, you may add multiple arguments to configure your connexion, using this format :
Argument=Value;
There are two arguments that you should always add :
Timeout=3600;
(otherwise by default, jdbc timeout is around 10 seconds only)
and
AllowLargeResults=1;
(to retrieve query results bigger then > 128 Mo)
To get all connection options, please refer to the Simba documentation :
https://www.simba.com/products/BigQuery/doc/JDBC_InstallGuide/content/jdbc/bq/options/intro-general.htm
Debug
While using the jdbc simba connector to connect to BigQuery, DigDash will throw a fatal in the log about autocommit.
There is nothing to be done here. This is a response for the jdbc connector about the autocommit.
Connection will work despite this error.
Requesting Google BigQuery
Depending on the data in google BigQuery, performing a SELECT * might trigger an error
This error comes from the metadata, or, specific googleBigQuert data type.
To prevent this issue; you must CAST as STRING every column. sur chaque colonne.
Example :
cast(ticket_id as STRING) as ticket_id
Only STRING, DATE and INT64 are available for the CAST.
If you need a date in a DATETIME format, cast it as string, then convert it in DigDash