DataGrip is one of the most valuable tools for our engineers for exploring and querying a myriad of different database technologies. DataGrip doesn’t yet come bundled with a BigQuery driver so in this post we’ll explore how to setup a custom data source so that you can connect to BigQuery using DataGrip.
In this post we’ll use:
You will also require a Google service account that has access to BigQuery. If you don’t have one setup you can find instructions here.
Take note of the email address and the associated key pair with this account. You’ll need both of these pieces to setup the BigQuery connection.
Ensure that this service account has sufficient permissions to access BigQuery. If you aren’t sure if this is the case navigate to IAM, select your project and edit the role associated to the service account (e.g., BigQuery -> BigQuery Data Editor/Viewer)
Add a new data source by clicking on the ‘+’ icon, select ‘Driver’ - the second last option in the drop down menu.
Enter ‘BigQuery’ as the name of the data source.
Under ‘Additional files’ click the ‘+’ icon and browse and select the all the jar files (ending in *.jar). There should be 7 in all.
Under ‘Class’ select
Click the ‘+’ icon in URL templates and add the following URL template:
This concludes setting up the JDBC driver for BigQuery.
Next let’s set up a data source to ensure we can connect.
where PROJECTID is your Google project ID, OAuthType=0 (for service account authorisation), EMAIL is the email address (ending in .gserviceaccount.com) associated with the service account and KEYPATH is the absolute path to your keyfile (e.g., /Users/mike/Downloads/example.json) - both .json or .p12 keys will work here.
Hit the ‘Test Connection’ and if everything has been set up correctly Datagrip should notify you that the connection has succeeded.
HttpTransport IO error
Check that your OAuthServiceAcctEmail and OAuthPvtKeyPath are set correctly and you’ve specified the absolute path to your key.
There a number of other options that can be customised for the JDBC driver by selecting ‘Advanced’ in the Data Source connection.
It is also possible to use other authentication methods to connect to BigQuery including:
Google User Account using OAuth 2 (OAuthType=1) Using pregenerated access and refresh tokens (OAuthType=2) Using the Application Default Credentials in your environment (OAuthType=3)
For more information about setting up these authentication methods please refer to the Simba JDBC Driver for Google BigQuery installation guide from the zip file earlier in the post.
Subscribe to this blog via RSS.