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:
- The latest Simbda JDBC 4.2-compatible drivers (1.1.4.1004 as of writing).
- DataGrip 2017.3.5
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)
Setting up the BigQuery driver
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 com.simba.googlebigquery.jdbc42.Driver
Click the ‘+’ icon in URL templates and add the following URL template:
Name: default Template: jdbc:bigquery://[Host]:[Port];ProjectId=[Project];OAuthType=[AuthValue];
This concludes setting up the JDBC driver for BigQuery.
Setting up a BigQuery data source
Next let’s set up a data source to ensure we can connect.
- In the data sources window hit ‘+’ and select BigQuery.
- Enter the name of your datasource in the Name field.
- Leave the user and password fields blank.
- In the URL field customise and paste the following JDBC connection string:
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.
jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=PROJECTID;OAuthType=0;OAuthServiceAcctEmail==EMAIL;OAuthPvtKeyPath=KEYFILE
Hit the ‘Test Connection’ and if everything has been set up correctly Datagrip should notify you that the connection has succeeded.
Troubleshooting
Error: HttpTransport IO error
Check that your OAuthServiceAcctEmail and OAuthPvtKeyPath are set correctly and you’ve specified the absolute path to your key.
Tips
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.
