Google BigQuery is basically REST based web service to the Dremel technology (distributed system developed designed to querying large datasets).
In this article I’ll describe how to access Google BigQuery from your local machine by using DataGrip tool from JetBrains.
There are several ways to access Google BigQuery.
The most obvious/official ones includes Query Editor accessible via Web console and Cloud Shell Console.
Despite 2 official ways from above, there is still need for some alternatives as you might not always have access right for Google Cloud.
For example, in case you are using SaaS from some vendor, you won’t have access rights to the Google Cloud for sure, but there are other cases as well.
I’ve also found it easier to work from the feature rich client application like DataGrip, than to use Web browser for writing queries.
In case you need direct access to your data in the Cloud, instead to use REST API calls, you can ask your vendor to grant you read-only access to the BigQuery by generating a key, as you won’t be able to corrupt SaaS application.
Process of creation a key is similar to the following:
IAM & Admin –> Create Service Account –> Enter a name –> Create –> Select Role –> BigQuery –> BigQuery Data Viewer –> Create key
On the following pictures you can see how key creation process is looking like.
In most cases, Google Cloud Administrator will just send you the key associated with your project that you’ll save on your local machine, so you won’t need to do anything.
As previously mentioned, In this article I’m using DataGrip tool from JetBrains, but the same should work with some others client editors.
The most important is to enter correct URL like in the following example:
jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=<Your_Project_ID>;OAuthType=0;OAuthServiceAcctEmail=name_of_your_key@<Your_project>.iam.gserviceaccount.com;OAuthPvtKeyPath=/Path/to/the/Private/key/on/your/local/disk/key_filename.json;
After that you can test the connection you created in the previous step
and start to work with Google BigQuery from your local machine.
Summary:
In this article I’ve described cases where tools provided by Google are not enough to cover cases when you don’t have access to the Google Cloud platform.
I’ve also described the most important steps you need to do to setup access to BigQuery engine from your local machine.
For some reason, in DataGrip I can’t set values for OAuthType & OAuthServiceAcctEmail in Advanced tab of the database connection window.
Instead I need to set all values in the URL.
I hope workaround with setting all values in the URL will save your time when setting up DataGrip to work with BigQuery engine.
In the next post I’ll describe how to integrate BigQuery with your internal on-premise infrastructure to create a hybrid Cloud solution.
Comments