Connecting to Google BigQuery with DBeaver with JDBC Drivers

Update (2018-10-07)

Shortly after this post DBeaver was updated with a native connector. Please see this post for a more up to date connection instructions if you have updated DBeaver to 5.2.2 or later.

0) Install DBeaver

You can find installation instructions here

1) Download the latest drivers

You can find the latest drivers on Google’s website

2) Create a folder to store the drivers

mkdir ~/.dbeaver-drivers/bigquery/

3) Extract driver jars and move to the folder we made earlier

jars_in_folder

4) Create a New Driver in DBeaver

  1. Navigate to Database > Driver Manager > New
  2. Add all the files from ~/.dbeaver-drivers/bigquery/
  3. Driver name: BigQuery (for labeling only)
  4. Class name: com.simba.googlebigquery.jdbc42.Driver (at the time of this writing)
  5. Default port: 443
  6. URL template: jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId={server};OAuthType=0;OAuthServiceAcctEmail={user};OAuthPvtKeyPath={host};
    • Note there are 4 different ways to connect to BigQuery using the JDBC driver. This tutorial illustrates connecting using the service account authorization method.
    • Additionally, at the time of this writing, Dbeaver only supports a couple URL template variables (e.g. server, user, host) which is why I’ve used the host variable for a path to key file rather than something like key_path

5) Create a new service account

  1. Instructions & details for creating a new service account can be found on Google’s website
  2. Grant your desired BigQuery permissions to your new service account
  3. Download the service account key

6) Create a New Connection

  1. In the menu bar navigate to Database > New Connection
  2. Select BigQuery
  3. Fill in the appropriate values for host, server, user
  4. Set host to the path the service account key e.g., /Users/admin/.dbeaver_drivers/bigquery/project_name-####.json
  5. Set server to project id for your BigQuery project
  6. Set user to the email address for the generated service account
  7. Press finish

Congrats you’ve successfully connected to BigQuery using Dbeaver!

successful connection

7) Troubleshooting

  • If you receive [Simba][BigQueryJDBCDriver](100004) HttpTransport IO error : 403 Forbidden make sure you’ve created a service account with the necessary permissions.
  • -As of Dbeaver 5.2.0 Dbeaver is unable to return arrays of ints and other numerics-
    • e.g., select [1, 2] as ids results in org.jkiss.dbeaver.model.exec.DBCException: SQL Error [10140] [22003]: [Simba][JDBC](10140) Error converting value to long.
Avatar
r-richmond
Data Wizard

Author of justnumbersandthings

comments powered by Disqus

Related