Connect and Query Google BigQuery using JDBC Connector

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 .

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.

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.

   
   
   

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 –

   
   
   

5. Click on Create. There is an option to assign Service Account permissions. Under that is a drop down. Choose BigQuery-> BigQuery Admin.

   
   
   

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.

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 Google BigQuery.
2. Register the BigQuery connector with Dbeaver. Go to Database -> Driver Manager. The Driver Manager will appear as shown below.

   
   
   

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
   googlebigquery.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.

   
   
   

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.
    
    
    

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.