0
No reviews. Be the first!

Google BigQuery Connector

Big Data Analytics for Google BigQuery

Native reporting for Google BigQuery with Jaspersoft Studio, JasperReports, and JasperReports Server

Big Data systems are generally recognized as those providing massive horizontal scale out capabilities. Jaspersoft's architecture is data source agnostic, enabling native reporting for Hadoop and popular NoSQL systems in a variety of ways. This connector provides access to data stored in Google BigQuery for your reporting and analytical needs.

First steps

Download

Begin by downloading the JDBC connector.

Review the Documentation

After you have downloaded the connector, Review the BigQuery SQL documentation available to learn how to:

  • Install the connector,
  • Write queries that the connector can process, and
  • Create Reports that leverage the connector.

Google Big Query Setup


Create a project using the Google Could Platform

https://console.cloud.google.com/home/

Name your project using only lowercase letters ensure projectname and projectid are the same

In BILLING -> Overview: Ensure your project is billable

 

Create a service account and a  .p12 certificate for your project

In IAM & Admin -> service accounts click create a service account

Ensure the service name have no spaces or special character and uses only lowercase letters

Follow the screenshot then click create

 

Once you create the certificate you will be prompted to download it

Remember the location !

The password is set by default to notasecret (indeed we won't need it)

 

Capture the service account id

Return in IAM & Admin -> Service Accounts to list your services accounts

From that list, take note of your service account id, as you will need it for your connection URL.

 

Give the role "big query admin" to your service account

In IAM & Admin -> IAM. At the right side of the service account you created, select the role BigQuery -> BigQuery Admin

 


Define your connection URL

You need :

- your [project id] done in the first step : testjasperbq

- your [service account id] : testjasperservice@testjasperbq.iam.gserviceaccount.com

- the location of your .p12 certificate : D:\Jaspersoft\testjasperbq-d18aec8ede11.p12

 

The url is composed like this :

jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=[projectid] ;OAuthType=0;OAuthServiceAcctEmail=[service account id ;OAuthPvtKeyPath=D:\Jaspersoft\testjasperbq-d18aec8ede11.p12;QueryDialect=SQL

 

In our case :

jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=testjasperbq;OAuthT...\Jaspersoft\testjasperbq-d18aec8ede11.p12;QueryDialect=SQL

 

This will be used in your BigQuery data adapters in Studio and in Data Sources in JasperReports Server.

 


JasperReportServer Setup

 

Copy your BigQuery service account p12 certificate into a folder on the JasperReports Server without spaces or special characters in it's name (ie C:\Jaspersoft).

Since you are there, put the Simba BigQuery JDBC driver you downloaded into  <Tomcat>/webapps/jasperserver-pro/WEB-INF/lib


In JasperReports Server, create a data source.

Select JDBC Data Source.

In the URL, copy/paste the connection URL you defined in the previous step

jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=testjasperbq;OAuthT...\Jaspersoft\testjasperbq-d18aec8ede11.p12;QueryDialect=SQL

No need for username and password - the p12 key provides authentication.

 

Test your connection

  • if you get an error message about http transport IO error, you have an issue with the URL provided.

Save your connection.

 

Now you can use it in reports and domains etc in JasperReports Server.

If the server has some conflicts indicated in the jasperserver.log, remove the Cassandra driver in <Tomcat>/webapps/jasperserver-pro/WEB-INF/lib.

 

Known requirements

  • projectId contains only lowercase letters testjasperbq

  • no space in the name of the p12 certificate path (expl : D:\Jaspersoft\mysertificate.p12)

  • the user 'of google big query certificate' needs to have the role "Big qeury Admin"

Feedback
randomness