Connect and Query Google BigQuery using JDBC Connector

Introduction

In this tutorial, we will walk you through on how you can connect to Google BigQuery from your favorite SQL/BI/ETL tools using Progress DataDirect Google BigQuery JDBC Connector.

Download and Install Progress Google BigQuery JDBC

  1. Download the Progress DataDirect Google BigQuery JDBC Connector.
  2. If you have downloaded the Windows version:
    1. Extract the package PROGRESS_DATADIRECT_JDBC_GOOGLE_BIGQUERY_WIN.zip
    2. To install the JDBC driver, run PROGRESS_DATADIRECT_JDBC_INSTALL.exe file.
    3. Follow the prompts on the installer to complete the installation of the Google BigQuery JDBC connector.
  3. If you have downloaded the Linux version:
    1. Extract the package PROGRESS_DATADIRECT_JDBC_GOOGLE_BIGQUERY_ALL.zip
    2. To install the JDBC driver, run the below command in your terminal
      java -jar PROGRESS_DATADIRECT_JDBC_INSTALL.jar
    3. Follow the instructions on your GUI/Terminal to install the Google BigQuery JDBC connector.

Authenticating with Google BigQuery

  1. Progress DataDirect Google BigQuery JDBC Connector offers two types of Authentication:
    1. Service Account Authentication
    2. OAuth2.0 Authentication
  2. In this tutorial, we will be using Service Account authentication. We will walk you through how you can do this and query your Google BigQuery data.
  3. Login to your Google Cloud Console. Open the Burger Menu from side and Go to IAM -> Service Accounts as shown below.


    Go to Service Accounts

  4. In the Service Accounts page, click on the Create Service Account button on the top. You should now see a form to create a service account. Fill in any Service Account Name, Service Account ID, Service Account Description. Below is an example –


    Create Service Account

  5. Click on Create. Now you should see an option to assign Service Account permissions. Under that you should find a drop down. Choose BigQuery-> BigQuery Admin.


    Choose Service Account Permissions

  6. Click on Continue. On the Next Screen, you should now see an option to Create Key. Click on Create Key. You should now see a menu on the right asking you to choose between json file .p12 key file. Choose any key format and click Create.

  7. Create Key
This will start a download of a .json or .p12 file based on your choice. Save the file.

Query your BigQuery data

  1. For this tutorial, we will use a simple query tool called Dbeaver, which lets us query data using DataDirect BigQuery JDBC Connector.
  2. Register Progress DataDirect BigQuery Connector with Dbeaver. Go to Database -> Driver Manager. You should see the Driver Manager as shown below.


    Add New Driver

  3. Click on New and you should see a form for registering the driver. Fill in the below details


    Name: <Any name>
    Class Name: com.ddtek.jdbc.googlebigquery.GoogleBigQueryDriver

  4. Click on Add File and navigate to the location of autorest.jar, which should be in the below default install location, unless you chose to install the connector in a different location.


    C:\Program Files\Progress\DataDirect\JDBC_60\googlebigquery.jar


  5. Your form should have the information as shown below when you are done with all the steps above. Click on OK to register the connector.


    Add BigQuery JAR

  6. Click on Ok.
  7. Now let’s create a connection to your Google BigQuery. Go to Database -> New Connection and choose the DataDirect BigQuery connector we just created.
  8. In the JDBC URL, use the below URL to get connected


    jdbc:datadirect:googlebigquery:AuthenticationMethod=serviceaccount;Project=<yourprojectname-12345>;Dataset=<your dataset name>;ServiceAccountEmail=<email@yourprojectname-12345.iam.gserviceaccount.com;ServiceAccountPrivateKey=/path-to/json-or-p12-file


    where

    Project - Your Project ID, this can be found in your Google Console
    DataSet - The Dataset name you want to connect to
    ServiceAccountEmail – Service Account ID you created above. You should find it in IAM->Service Accounts as shown below
    ServiceAccountPrivateKey – Path to JSON or .p12 file you saved earlier.

  9. Here is an example URL for reference:

    jdbc:datadirect:googlebigquery:AuthenticationMethod=serviceaccount;Project=projectzero-249620;Dataset=datadirect;ServiceAccountEmail=googlebigquery@projectzero-249620.iam.gserviceaccount.com;ServiceAccountPrivateKey=C:\Users\sbobba\Downloads\projectzero-249620-215fa9911145.json


  10. Click on Test Connect and you should be able to connect successfully. Now you are ready to run your SQL queries on BigQuery.

    Query your bigquery

 

We hope this tutorial helped you to get started connecting to Google BigQuery and query your data using Progress DataDirect BigQuery JDBC Connector. If you have any questions feel free to contact us.


JDBC TUTORIAL

Connect and Query Google BigQuery using JDBC Connector

View all Tutorials

Connect any application to any data source anywhere

A product specialist will be glad to get in
touch with you