In this blog, we will provide steps to connect to google big query data source connection in Helical Insight Open Source BI application
Helical Insight is successfully installed
- Open setting.xml in a text editor. Path of setting.xml is (C:\Program Files\Helical Insight\hi\hi-repository\System\Admin) and search for the following tag which is generally present on Line no 38. exculdeRegexFromLoadedClass and then add the following between the opening and closing tag of exculdeRegexFromLoadedClass at the end
- Add the file present in “SQLFunctions” folder from the attachment to the SQLFunctions folder (path : C:\Program Files\Helical Insight\hi\hi-repository\System\Admin\SqlFunctions).
- Replace the files provided from the above attachment with the patch in the Admin folder (path is : C:\Program Files\Helical Insight\hi\hi-repository\System\Admin).
- Download required of simba JDBC driver for big query from the below link:
- Simba JDBC Driver for Big Query
- It will download in zip format
- Extract zip file
- Create new folder named “BigQuery” and move all the extracted files inside this folder Zip the above created BigQuery folder which is having the extracted files
- Upload zip file into Helical Insight Application:
- Login into Helical Insight application
- Go to Datasources page
- Click on “Add driver” which is generally present towards the end of the webpage and Upload (You have to upload the zip file which was created on step number 4.d)
- Observe, bigquery database icon will appear with a green tick mark. If its not appearing make sure that you click reload the page.
- Create BigQuery datasource connection:
- Click on ‘Bigquery’ icon
- Click on create
- Provide required details such URL and Datasource Name [refer below provided connection example]
- Click test connection, if test connection is success and Save dataosurce
So basically you will replace, sqlDialect.properties & sqlFunctionsXmlMapping.properties
Current JDBC driver releases: JDBC 4.2-compatible
For Example, Following is the sample connection URL for Bigquery
jdbc:bigquery://[Host]:[Port];ProjectId=[Project];OAuthType=[AuthValue];OAuthServiceAcctEmail=[emailid];OAuthPvtKeyPath=[path of p12 or JSON file]
ProjectID – property to the name of BigQuery project.
OAuthServiceAcctEmai – service account name of the p12 file
OAuthPvtKeyPath – Provide .p12 file path that is used to authenticate the service account email address. This parameter supports keys in .pl2 or .json format.