MuleSoft + BigQuery Series 3

Author: Eswara Pendli

Thank you for great support on Mulesoft + BigQuery Series!

In previous blogs, we have exploded multiple ways of connecting to Google BigQuery:

• For basic understanding of BigQuery, connect to BigQuery using REST API and for features of BigQuery, please refer Mulesoft + BigQuery Series 1.
• For configuring/using BigQuery connector provided by third party connectivity partners, please refer  Mulesoft + BigQuery Series 2. 

In this series, we will be exploring Google BigQuery with MuleSoft Database connector and a simple demo on Fetching records from Table using Database SELECT Operation.

Quick Points?

• We will be using MuleSoft’s Database Connector with JDBC Driver to connect Google BigQuery to allow users to select, insert, and update data easily. 
• We will be using BigQuery’s Service account to connect with MuleSoft.

Pre-Requisites:

• We need to have a google cloud account/sandbox account.

Setup Service Account @Google BigQuery:

• Go to Google Cloud Platform location: https://console.cloud.google.com/home/dashboard?project=navigation-api-demo
• Select IAM & Admin and go to Service Accounts

• Click on Create Service Account

• Configure Service Account Name and Copy Service Account Email:

• Add BigQuery relevant roles for this user:

• After successful creation of a service account, go to Service Accounts. Click on Create Key:

• Select Key Type as JSON and download the Key file:

Setup Database Configuration:

• We need to download a simba JDBC driver to setup connectivity between BigQuery and MuleSoft.
• We can find relevant jar files from here.

• We will be using “GoogleBigQueryJDBC42.jar” from the extracted zip file.
• We need to add common dependencies to the project (I will be sharing a POM file at the bottom of the blog for reference).
• Now go to the Database connector and Select Generic Connection. Configure JDBC Driver as below: 

• We can edit/update JDBC Connection URL in this way:

Format:

jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=${project-id};OAuthType=0;OAuthPvtKeyPath=${service-account-JsonKey-path};OAuthServiceAcctEmail=${service-account-email};

• Example: (Update this according to your values/placeholder)

jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=navigation-api-demo;OAuthType=0;OAuthPvtKeyPath=/Users/Eswara Pendli/AnypointStudio/studio-workspace1/poc-bigquery-database-blog/src/main/resources/navigation-api-demo-1cfaf2c56e84.json;OAuthServiceAcctEmail=mule-db-blog@navigation-api-demo.iam.gserviceaccount.com;

• We can get project-id & service-account-email / client_email from the downloaded key file.

• Update the JDBC URL and Driver Class Name section as below:

Driver Class Name: com.simba.googlebigquery.jdbc42.Driver

• That’s it! We are now able to establish connectivity between Google’s BigQuery and MuleSoft using Service Account.

Troubleshooting

1. If connectivity is not successful, try to add GoogleBigQueryJDBC.jar to build path manually:

2. Make sure Service Account has enough privileges to read/write the data from BigQuery’s table: 

3. If you are getting the following exception after updating POM file:

Then, add GoogleBigQuery.jar file manually by providing respective jar file location, groupId & artifactId:

Make sure you have removed existing sharedLibrary & dependency regarding GoogleBigQuery.jar:

Now, we are able to establish connectivity.

Configure Database Select Operation

• Configure/Update SQL Query for BigQuery using DatasetName & TableName:

Ex: Select * from datasetName.tableName from Table

Here is the POM file for reference :

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.mycompany</groupId>
    <artifactId>poc-bigquery-database-blog</artifactId>
    <version>1.0.0-SNAPSHOT</version>
    <packaging>mule-application</packaging>

    <name>poc-bigquery-database-blog</name>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>

        <app.runtime>4.2.2</app.runtime>
        <mule.maven.plugin.version>3.3.5</mule.maven.plugin.version>
    </properties>

    <build>
        <plugins>
            <plugin>
                <groupId>org.mule.tools.maven</groupId>
                <artifactId>mule-maven-plugin</artifactId>
                <version>${mule.maven.plugin.version}</version>
                <extensions>true</extensions>
                <configuration>
                <sharedLibraries>
                       <!--  <sharedLibrary>
                            <groupId>com.test</groupId>
                            <artifactId>GoogleBigQueryJDBC42</artifactId>
                        </sharedLibrary> -->
                    <sharedLibrary>
                            <groupId>com.gbq</groupId>
                            <artifactId>GoogleBigQueryJDBC42</artifactId>
                        </sharedLibrary>
                    </sharedLibraries>
                </configuration>
            </plugin>
        </plugins>
    </build>

    <dependencies>
        <dependency>
            <groupId>org.mule.connectors</groupId>
            <artifactId>mule-http-connector</artifactId>
            <version>1.3.2</version>
            <classifier>mule-plugin</classifier>
        </dependency>
        <dependency>
            <groupId>org.mule.connectors</groupId>
            <artifactId>mule-sockets-connector</artifactId>
            <version>1.1.2</version>
            <classifier>mule-plugin</classifier>
        </dependency>
        <dependency>
            <groupId>org.mule.connectors</groupId>
            <artifactId>mule-db-connector</artifactId>
            <version>1.4.0</version>
            <classifier>mule-plugin</classifier>
        </dependency>
        <!-- <dependency>
            <groupId>com.test</groupId>
            <artifactId>GoogleBigQueryJDBC42</artifactId>
            <version>1.0</version>
        </dependency> -->
        
    	<dependency>
            <groupId>com.gbq</groupId>
            <artifactId>GoogleBigQueryJDBC42</artifactId>
            <version>1.0</version>
        </dependency>
        <dependency>
<groupId>com.google.api-client</groupId>
<artifactId>google-api-client</artifactId>
<version>1.30.10</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.google.auth/google-auth-library-credentials -->
<dependency>
<groupId>com.google.auth</groupId>
<artifactId>google-auth-library-credentials</artifactId>
<version>0.21.1</version>
</dependency>
        <!-- https://mvnrepository.com/artifact/com.google.api/gax -->
<dependency>
    <groupId>com.google.api</groupId>
    <artifactId>gax</artifactId>
    <version>1.58.2</version>
</dependency>

<!-- https://mvnrepository.com/artifact/com.google.auth/google-auth-library-oauth2-http -->
<dependency>
<groupId>com.google.auth</groupId>
<artifactId>google-auth-library-oauth2-http</artifactId>
<version>0.21.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.google.http-client/google-http-client -->
<dependency>
<groupId>com.google.http-client</groupId>
<artifactId>google-http-client</artifactId>
<version>1.36.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.google.http-client/google-http-client-jackson2 -->
<dependency>
<groupId>com.google.http-client</groupId>
<artifactId>google-http-client-jackson2</artifactId>
<version>1.36.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.google.oauth-client/google-oauth-client -->
<dependency>
<groupId>com.google.oauth-client</groupId>
<artifactId>google-oauth-client</artifactId>
<version>1.31.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.fasterxml.jackson.core/jackson-core -->
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-core</artifactId>
<version>2.11.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.google.apis/google-api-services-bigquery -->
<dependency>
    <groupId>com.google.apis</groupId>
    <artifactId>google-api-services-bigquery</artifactId>
    <version>v2-rev459-1.25.0</version>
</dependency>
        <!-- <dependency>
            <groupId>com.gg</groupId>
            <artifactId>GoogleBigQueryJDBC42</artifactId>
            <version>0.1</version>
        </dependency> -->
        
        <dependency>
   <groupId>org.apache.parquet</groupId>
   <artifactId>parquet-avro</artifactId>
   <version>1.10.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.google.cloud/google-cloud-bigquerystorage -->
<dependency>
    <groupId>com.google.cloud</groupId>
    <artifactId>google-cloud-bigquerystorage</artifactId>
    <version>1.5.3</version>
</dependency>

<!-- https://mvnrepository.com/artifact/org.apache.avro/avro -->
<dependency>
    <groupId>org.apache.avro</groupId>
    <artifactId>avro</artifactId>
    <version>1.10.0-MULE_4</version>
</dependency>
<!-- https://mvnrepository.com/artifact/joda-time/joda-time -->
<dependency>
    <groupId>joda-time</groupId>
    <artifactId>joda-time</artifactId>
    <version>2.10.6</version>
</dependency>
    </dependencies>

    <repositories>
          <repository>
            <id>anypoint-exchange</id>
            <name>Anypoint Exchange</name>
            <url>https://maven.anypoint.mulesoft.com/api/v1/maven</url>
            <layout>default</layout>
        </repository>
        <repository>
            <id>mulesoft-releases</id>
            <name>MuleSoft Releases Repository</name>
            <url>https://repository.mulesoft.org/releases/</url>
            <layout>default</layout>
        </repository>
    </repositories>
    <pluginRepositories>
        <pluginRepository>
            <id>mulesoft-releases</id>
            <name>mulesoft release repository</name>
            <layout>default</layout>
            <url>https://repository.mulesoft.org/releases/</url>
            <snapshots>
                <enabled>false</enabled>
            </snapshots>
        </pluginRepository>
    </pluginRepositories>

</project>

BigQuery is fully-managed, we don’t need to deploy any resources such as disks and virtual machines. It is easy to use with high SLA.

Happy Learning!

References:

• https://apisero.com/mulesoft-bigquery-series-1/ 
• https://apisero.com/mulesoft-bigquery-series-2/ 
• https://cloud.google.com/bigquery/providers/simba-drivers/ 
• https://cloud.google.com/bigquery/docs/support/ 
• https://www.simba.com/drivers/bigquery-odbc-jdbc/ 
• https://cloud.google.com/docs/authentication/getting-started 
• https://dzone.com/articles/connect-with-google-bigquery-using-mulesoft 
• https://cloud.google.com/bigquery/ 
• https://docs.mulesoft.com/db-connector/1.8/