Easily Integrate Data From Google BigQuery With Anaplan Connect

AnaplanOEG
AnaplanOEG
edited September 2023 in Best Practices

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 with all users, leveraging BigQuery for operational and reporting processes.

Users should be familiar with Anaplan Connect. Learn more about Anaplan Connect and how it works.

Requirements

  • Anaplan Connect 4.2.x installed and configured.
  • The Google BigQuery JDBC Driver.
  • A designated Google project with the service account's private key JSON file.
  • Anaplan Action(s)/Process(es) created.

Step #1: Install and configure Anaplan Connect

  • If you are new to Anaplan Connect, check out the Anaplan Connect Quickstart for Linux & macOS and Microsoft Windows. These articles will quickly get you up and running.

Step #2: Download and Install the Google BigQuery JDBC Driver

  • Download the Google BigQuery JDBC Driver (SimbaJDBCDriverforGoogleBigQuery42_1.5.0.1001.zip) 
  • Unzip the entire ZIP folder into the ./lib directory just underneath where Anaplan Connect is installed.
image.png

Step #3: Create a Google service account

To enable the connection, create a service account in the BigQuery project for the connector to use and download its private key file.

image.png
  • Enter a unique name and description to help identify the service account, and click DONE. Please note that additional roles and users can be optionally added.
image.png
  • For Role, select Owner, and click CONTINUE, and then DONE.
    Note: Depending on your environment, additional permissions might need to be added, such as bigquery.jobs.create.
image.png
  • Open the new Service Account and click on the KEYS tab. From this tab, click the ADD KEY button to create a new key. Select the JSON format, and the key will be downloaded to your local environment.
2023-09-23_10-35-12 (1).gif
  • Move the downloaded JSON file to the directory where your particular Anaplan Connect batch or shell script executes. This directory will also contain the properties file, which will be prepared in the next step.
    Note: your JSON file will have a different name.
image.png

Step #4: Prepare the jdbc.properties File

  • In the same directory as the previous step, create a new file and give it a meaningful name, such as bigquery-jdbc-query.properties.
  • Using a text editor such as Notepad.exe, copy and paste the following lines to the file:
    Note: Do not use document editors such as Microsoft Word, as this might inadvertently introduce hidden characters, creating unintended or hidden syntax errors.
jdbc.connect.url=jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=sample-gbq-data;OAuthType=0;OAuthServiceAcctEmail=anaplan-bigquery-integration@sample-gbq-data.iam.gserviceaccount.com;OAuthPvtKeyPath=C:/automation/my-anaplan-connect-scripts/sample-gbq-data-6f7d2b692613.json;IgnoreTransactions=1;
jdbc.fetch.size=10
jdbc.query=SELECT * FROM bigquery-public-data.new_york_citibike.citibike_stations LIMIT 1000
  • Update the following parameters in the jdbc.connect.url
    • ProjectId - Replace with your specific Google BigQuery Project ID
    • OAuthServiceAcctEmail - Replace with your service account. To ensure the correct address is entered, please use the Copy to clipboard tool available from the Project Service Account summary:
      image.png
    • OAuthPvtKeyPath - Update this value with the absolute path to the private key JSON file created in step #3. If the operating system is Microsoft Windows, set the path to the JSON file with forward slashes (/) instead of backslashes (\). Backslashes are used as "escape" characters, which will create issues in properly pathing to the file. Alternatively, use two backslashes (\\).
  • Update the jdbc.query with your own query based on your dataset.
  • Optionally add the following parameters:
    • jdbc.isStoredProcedure=true - Set this value to true if invoking a stored procedure.
    • jdbc.params - Set for parametrized SQL query in {jdbc.query} above.
  • Notes: 
    • 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 strings to an appropriate data type. For example:
      INSERT into anaplandi.AccountsTable (AccountID, AccountName, Industry, AnnualRevenue, EmployeeCount) VALUES (?,?,?, cast(? as float64), cast(? as int64)) 

Step #5: Finalize the Anaplan Connect script and execute

  • Copy a boilerplate Anaplan Connect script from the ./examples directory just underneath where Anaplan Connect is installed to the directory where the private key JSON and jdbc.properites files are located.
  • If the operating environment is Windows, add the various values and Operation line similar to the following example, but please update with your own values. To ensure proper execution, please maintain the order of the -file and -jdbcproperties parameters before any -import or -process parameters.
set AnaplanUser="YOUR_EMAIL:YOUR_PASSWORD"
set WorkspaceId="YOUR_WORKSPACE"
set ModelId="YOUR_MODEL"
set ServiceUrl="https://api.anaplan.com"
set AuthUrl="https://auth.anaplan.com"
set ImportName="Citibike Stations from my-big-query-data.csv"
set JDBCProperties="bigquery-jdbc-query.properties"
set Operation=-debug -service %ServiceUrl% -auth %AuthUrl% -workspace %WorkspaceId% -model %ModelId% -file "my-big-query-data.csv" -jdbcproperties %JDBCProperties% -import %ImportName% -execute
  • If the environment is Linux or macOS, then add the various values and Operation line similar to the following example using your own values. 
AnaplanUser="YOUR_EMAIL:YOUR_PASSWORD"
WorkspaceId="YOUR_WORKSPACE"
ModelId="YOUR_MODEL"
ServiceUrl="https://api.anaplan.com"
AuthUrl="https://auth.anaplan.com"
ImportName="Citibike Stations from my-big-query-data.csv"
JDBCProperties="bigquery-jdbc-query.properties"
Operation="-debug -service %ServiceUrl% -auth %AuthUrl% -workspace %WorkspaceId% -model %ModelId% -file "my-big-query-data.csv" -jdbcproperties %JDBCProperties% -import %ImportName% -execute"
  • Assuming everything is set up correctly, your script should be able to directly query from Google BigQuery into Anaplan or, conversely, export from Anaplan and directly insert into Google BigQuery. When executing, the Anaplan Connect logging should produce output similar to the following example:
ac-google-biqquery2 (1).gif

Authors: Quin Eddy, @QuinE, Operational Excellence Group (OEG) & @guillaume_arnau, Head of Solutions Engineering

Tagged:

Comments

  • ashish.banka
    ashish.banka

    @guillaume_arnau Great news and I am sure most of the customers who rely on Google BigQuery for actuals data would definitely benefit from Anaplan + Google Cloud partnership.

Categories