Connect and Query Google BigQuery using JDBC Connector

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.

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.

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.

   
   
   

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 –

   
   
   

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.

   
   
   

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.

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.

   
   
   

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.

   
   
   

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.
    
    
    

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.