Connect and Query Google BigQuery using JDBC Connector

Introduction

In this tutorial, we will walk through how to connect to Google BigQuery from SQL/BI/ETL tools using Progress DataDirect's JDBC Connector for Google BigQuery .

Download and Install Progress DataDirect's JDBC Connector for Google BigQuery

  1. Download the Progress DataDirect JDBC Connector for Google BigQuery.
  2. For downloads of 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 connector.
  3. For downloads of 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 the terminal
      java -jar PROGRESS_DATADIRECT_JDBC_INSTALL.jar
    3. Follow the instructions on the GUI/Terminal to install the connector.

Authenticating with Google BigQuery

  1. Progress DataDirect's JDBC Connector for Google BigQuery 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 through how to do this and query the Google BigQuery data.
  3. Login to your Google Cloud Console. Open the burger menu on the 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. There is 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. There is an option to assign Service Account permissions. Under that is a drop down. Choose BigQuery-> BigQuery Admin.


    Choose Service Account Permissions

  6. Click on Continue. On the Next Screen, there is an option to Create Key. Click on Create Key. There is a menu on the right asking 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 Google BigQuery data

  1. For this tutorial, we will use a simple query tool called Dbeaver, which lets us query data using Progress DataDirect's JDBC Connector for Googler BigQuery.
  2. Register the BigQuery connector with Dbeaver. Go to Database -> Driver Manager. The Driver Manager will appear as shown below.


    Add New Driver

  3. Click on New and to 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. The form should have the information as shown below when the above steps are complete. Click on OK to register the connector.


    Add BigQuery JAR

  6. Click on Ok.
  7. Now let’s create a connection to 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. Now you are ready to run your SQL queries on BigQuery.

    Query your bigquery

 

We hope this tutorial explained connecting to Google BigQuery and querying data using Progress DataDirect's JDBC Connector for Google BigQuery. Feel free to contact us with any questions.


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