Easily Integrate Data From Google BigQuery With Anaplan Connect

guillaume_arnau
guillaume_arnau
in Community Knowledge Base

Following recent announcements on the global strategic partnership between Anaplan and Google Cloud, we are pleased to unveil a new connector. 

Now, users can leverage Anaplan Connect for bi-directional integrations with Google BigQuery. This free connector makes integrations between Anaplan models and Google BigQuery via Java Database Connectivity (JDBC) drivers that leverage the power of BigQuery's standard SQL.

Users can easily pull any data from BigQuery—such as actuals, transactions, lists, and hierarchies—straight into Anaplan models for faster planning, modeling, and analysis. In addition, users can easily push any data from their Anaplan models back to BigQuery to easily share the results of the planning process to all users leveraging BigQuery for operational and reporting processes.

The JDBC driver is publicly available and can be downloaded with Anaplan Connect for free from the Google website.

To get started with the JDBC driver, users should be familiar with Anaplan Connect. Learn more about Anaplan Connect and how it works.

Next, follow the step-by-step instructions below that detail how to download, install, and configure the Simba JDBC driver for Google BigQuery with Anaplan Connect.

Should you encounter any issues, please contact Anaplan Support. Our Support team will work and partner with Google to resolve any issues.

Pre-requisites

  • Anaplan Connect 1.4.x
  • Java 8
  • Anaplan Action(s)/Process(es) created

Download and Install Simba BigQuery JDBC Driver

  • Download Simba BigQuery JDBC Driver (SimbaBigQueryJDBC42-1.2.10.1013.zip) 
  • To install Simba BigQuery JDBC Driver for Anaplan Connect, unzip SimbaBigQueryJDBC42-1.2.10.1013.zip to …/anaplan-connect/lib directory 

Preparing jdbc.properties File

  • Copy example-jdbc-query.properties from …/anaplan-connect/examples folder to …/anaplan-connectdirectory.
  • Rename example-jdbc-query.properties to something more meaningful (ex: bigquery-jdbc-query.properties)
  • Edit jdbc-query.properties file and update values for following variables
    • Jdbc:bigquery://[Host]:[Port];ProjectId=[Project];OAuthType=[AuthValue];IgnoreTransactions=1;[Property1]=[Value1];[Property2]=[Value2];…
    • Example:
    • Jdbc.connect.url: Use following syntax to build jdbc.connect.url string.

Jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=celtic-spider-206221;OAuthType=0;OAuthServiceAcctEmail=[email protected];OAuthPvtKeyPath=c:\certs\certs\celtic-spider-206221-6a4c046a2426.json;IgnoreTransactions=1;

Note: Refer to google documentation on how to obtain values for ProjectId, OAuthServiceAcctEmail, and OAuthPvtKeyPath.

  • Jdbc.username: Since you are using OAuth Private Key, you don’t need to specify a username. Comment this variable with a ‘#’ (ex: #jdbc.username).
  • Jdbc.password: Comment this variable (ex: #jdbc.password) since you are using OAuth Private Key.
  • Jdbc.fetch.size: Leave this to default value of 10.
  • jdbcisStoredProcedure: Set this value to ‘true’ if you are invoking a stored procedure.
  • Jdbc.query: Provide either a SELECT or INSERT statement you would like to execute.
    • Note(s): 
      • While performing a SELECT statement, you may need to prefix table name with projectId and dataset name (ex: celtic-spider-206221.anaplandi.Accounts).
      • While performing an INSERT on float64 or int64 data types, Anaplan may pass the data as a string. You may need to use “cast()” function to convert string to an appropriate data type. 
      • For example: INSERT into anaplandi.AccountsTable (AccountID, AccountName, Industry, AnnualRevenue, EmployeeCount) VALUES (?,?,?, cast(? as float64), cast(? as int64)) 

References

Refer to Simba JDBC Driver for Google BigQuery Install and Configuration Guide.pdf for additional information on Simba JDBC Driver for BigQuery.

Find additional information using Google Service Account for authentication. 

Note: Simba documentation versioning can sometimes differ from the version of the driver Google is distributing.

Find steps to create a service account and generate a private key for OAuth 2.0.

Tagged:

Comments

Categories