Using BigQuery from IntelliJ-based IDE

Florin Pățan

The 2021.1 update brings full native support for BigQuery. To learn how to use this, head over to our Documentation.

This article covers support pre-2021.1 for BigQuery. If you are still using such a version, we encourage you to upgrade to the latest IDE version and check out the native support.

Continuing the series of posts on how to connect DataGrip (or any other IntelliJ-based IDE) to various data sources, in this post we’ll show you how to connect to Google’s BigQuery. BigQuery is a low-cost enterprise data warehouse designed to handle data analytics at a massive scale.

Currently, DataGrip does not come with a built-in connector for BigQuery, so let’s connect the IDE to it.

Update: if you prefer to watch a video instead of reading this post, please see a recent version of this tutorial created in April 2019.

First, let’s get all the details needed to connect to BigQuery from the Cloud Console. Make sure you have the right project selected, then click on the Create service account button. Type the name of the account, for example “datagrip”, in the Role section, select the BigQuery | BigQuery Data View, BigQuery | BigQuery Job User, and BigQuery | BigQuery User roles, then check the Furnish a new private key box. Make sure that the Key Type is set to JSON, then click on the Create button. A credentials file will be downloaded and you can click on the Close button on the dialog.

Note: Make sure you place that file in a safe place as it will allow anything with rights to read it to connect to your BigQuery dataset you are allowed to access.

Now, back to the IDE, we need to add a new Data Source. Go to Database Tool Window, then select Data Source Properties, click on the “+” button and then, at the bottom of the list you’ll see an entry named Driver, click on this.

Start by changing the name of the configuration to “BigQuery”. You will notice that there is a section called Driver files. You can download the BigQuery JDBC driver from this page. Download the “JDBC 4.2-compatible” zip file, named “SimbaJDBCDriverforGoogleBigQuery42_1.1.6.1006.zip” at the time of writing, then unpack it into a new directory.

Once you downloaded the driver, go back to the IDE, and in the Additional files section, click on “+” and add all the “.jar” files from the newly created directory. Under the Class drop-down, now you can select “com.simba.googlebigquery.jdbc42.Driver” from it. In the Dialect drop-down, select Generic SQL.

Next, let’s define the connection URL template. Click on the “+” button under the URL templates section and add a new entry named “default” with the value: “jdbc:bigquery://[{host::https\://www.googleapis.com/bigquery/v2}[:{port::443}]][<;,{:identifier}={:param}>]”. Go to the Advanced tab, and in the OAuthType field, type “0”(zero).

With the driver is configured, go to the “+” sign at the top left of the Data Sources and Drivers window and select “BigQuery”. Leave the User and Password fields empty.
If you don’t want to modify the data in BigQuery, or your user can only view the data without modifying it, then mark the connection as Read-only.

Click on the Advanced tab and add your service account to OAuthServiceAcctEmail. This is the email we have from the earlier step in the Google Cloud Console. In the OAuthPvtKeyPath type the path to the file downloaded earlier from the Cloud Console. Finally, type the project id in the ProjectId field.

Go back to the General tab, click on the Test Connection button and you should see a “Successful” message. This means you configured everything correctly and you can now connect to BigQuery.

Close the window by pressing the OK button, and now you’ll be presented with a database console ready for use.

Let’s run a few sample queries to see how everything. For this, we’ll use the BigQuery public dataset available, and borrow one query from the official documentation:

SELECT corpus FROM `bigquery-public-data.samples.shakespeare` GROUP BY corpus;

Press CTRL+ENTER and the query will start running.

Note: Every time you run a query against BigQuery which requires it to process data, you will pay for it. Make sure that you understand the data size and the query you are about to run before doing so. For more details about the pricing, please see the official BigQuery documentation or contact your Google Cloud administrators.

And that’s it. Now you can use BigQuery just like any other database and have all the IDE power for completion, data export, and visualization. Please let us know in the comments below if you have any questions, or features requests, we look forward to your feedback.

Subscribe to DataGrip Blog updates

Subscribe form

By submitting this form, I agree to the JetBrains Privacy Policy Notification icon

By submitting this form, I agree that JetBrains s.r.o. ("JetBrains") may use my name, email address, and location data to send me newsletters, including commercial communications, and to process my personal data for this purpose. I agree that JetBrains may process said data using third-party services for this purpose in accordance with the JetBrains Privacy Policy. I understand that I can revoke this consent at any time in my profile. In addition, an unsubscribe link is included in each email.

Submit

Thanks, we've got you!