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"
