You can create a pipeline that writes back data from an Anaplan Data Orchestrator dataset to Databricks.

Prerequisites

You need a connection to Databricks to create a pipeline. Make sure you meet the prerequisites in these sections before you create a connection to Databricks and a writeback pipeline.

Create a connection to Databricks

To create a connection:

  1. Select Data Orchestrator from the top-left navigation menu.
  2. Choose a dataspace from the list.
  3. Select Connections from the left-side panel.
  4. Select Create connection.
  5. On the Create connection page, select Databricks, and then select Next.
    If you don't find the connector, enter a search term in the Find... field.
  6. On the Connection details page, enter these details and select Next:
    • Name: Create a name for your connection. The name can contain alphanumeric characters and underscores.
    • Description: Optionally, enter a description about your connection.
  7. On the Connection credentials page, enter your Google credentials and select Next.
    See the table below for information about each field.
Connection credentialsDescription
HostThe Databricks workspace URL. You can find the host in your browser’s address bar. You don't need to include https:// in the host.
Port

The port number for your Databricks SQL warehouse connections. The default port number is 443.

To find the port number in your Databricks workspace:

  1. Select SQL Warehouses in the left-side panel.
  2. Select the specific SQL warehouse you want to connect to.
  3. Select ‌the Connection details tab on the SQL Warehouse page.
    The port number displays in the JDBC URL.
HTTP Path

The HTTP path of your Databricks database.

To find the HTTP path in your Databricks workspace:

  1. Select SQL Warehouses in the left-side panel.
  2. Select the specific SQL warehouse you want to connect to.
  3. Select ‌the Connection details tab on the SQL Warehouse page.
    The HTTP path displays and starts with /sql/1.0/warehouses/.
Catalog

The Databricks database catalog defines the data namespace you want to access.

To find the catalog in your Databricks workspace, select Catalog in the left-side panel. A list of available catalogs displays. Use the catalog name for the Catalog field in your Databricks connection.

Schema

The Databricks database schema defines the data namespace you want to access.

To find the schema in your Databricks workspace, select Catalog in the left-side panel, and then select a catalog. The schemas display within the catalog. Use the schema name for the Schema field in your Databricks connection.

Client ID and Secret

These credentials are used to authenticate your application.

You can get the client ID and secret by creating a service principal in Databricks. This is the recommended and most secure method for headless authentication. To create a service principle, you must be a workspace administrator in Databricks.

To create a service principal and get credentials:

  1. Select ‌your user name in the top-right corner of your Databricks workspace, and select Settings.
  2. Select Identity and access.
  3. Select Manage next to Service principals.
  4. Select Add service principal.
  5. Enter a name for your service principal (for example, NewConnectorServicePrincipal) and select Add.
    The service principal is now created.
  6. Follow these steps to get the client ID:
    1. Select the service principal name to open its details page.
      The Application Id is your client ID. 
    2. Copy the Application Id.
  7. Follow these steps to get the secret:
    1. Select the Secrets tab.
    2. Under OAuth secrets, select Generate secret.
    3. Copy the generated secret.
      This is your secret. You won't be able to see it again after you navigate away from this page.

Important: Remember to grant this new service principal the necessary permissions on your SQL warehouse, catalog, and schema for the connector to be able to access the data. You can do this from the permissions tabs in the respective UI sections.

  1. After the connection test is complete, select Done.

Create a writeback pipeline

When you set up the writeback pipeline, you'll use your connection to export either a source dataset or a transformation view dataset (derived dataset) from Data Orchestrator to Databricks.

To create a writeback pipeline:

  1. Select Data Orchestrator from the top-left navigation menu.
  2. Choose a dataspace from the list.
  3. Select Pipelines from the left-side panel.
  4. Select Create pipeline.
  5. Enter a Name for your pipeline and then select Create.
    You are taken to the pipeline designer view.
  6. Select the Source icon, and then complete these steps in the right-side panel:
    1. Select Anaplan from the Connection type dropdown. 
    2. Select Datasets from the Choose connection dropdown.
    3. Enter a new Label to change the source display name in the designer view.
    4. Select Source location, choose a Data Orchestrator dataset, and then select Confirm.
      The dataset is used as the source for your pipeline.
  7. Optionally, select the add icon that appears between the Source and Sink nodes.
    You can add steps to your pipeline to process data.
  8. Select the Sink icon, and then complete these steps in the right-side panel:
    1. Select Databricks from the Connection type dropdown.
    2. Select the Databricks connection you created from the Choose connection dropdown.
    3. Enter a new Label to change the sink name that displays in the designer view.
    4. Select Target location > Table, select a Databricks table, and then select Done.
    5. Select Target mapping, map the Data Orchestrator source dataset values to the Databricks target values, and then select Done.
    6. Select a Write option for the target table: Append, Full replace, or Upsert.
      This determines how data is written to the target table. See the Write options section below for more details.
  9. Select Publish, and then select Run to execute the data transfer.

Write options

Review this table to determine which write option to select.

Write optionsDescription
Append

Adds all rows from the source data to the existing rows in the target table.

If you select Append, a staging table in Databricks is required:

  • To automatically create a staging table, don't select the Specify staging table name checkbox. This checkbox is under the Advanced options section. This requires CREATE TABLE access in Databricks.
  • To manually specify an existing staging table in Databricks, 
    1. Expand the Advanced options section.
    2. Select the Specify staging table name checkbox.
    3. Enter the Databricks staging table name in the Specify staging table name field.

See the Staging table section below for more information.

Note: Append load isn't suitable for target tables with primary keys. 

Full replace

Deletes all existing data in the target table and replaces it with the source data.

If you select Full replace, a staging table in Databricks is recommended and selected by default: 

  • If you don't want to create a staging table, don't select ‌the Stage data before replacing the target checkbox. This checkbox is under the Advanced options section.
  • If you want to create a staging table: 
    1. Expand the Advanced options section.
    2. Select the Stage data before replacing the target checkbox.
    3. Specify the staging table details:
      • To automatically create a staging table, don't select the Specify staging table name checkbox. This requires CREATE TABLE access in Databricks.
      • To manually specify an existing staging table, select the Specify staging table name checkbox. Then enter the existing staging table name in the Specify staging table name field.
Upsert

Updates existing rows and adds new rows based on a specified key. 

If you select Upsert, a staging table in Databricks is required:

  1. Select a Primary key.
    The columns you select are used to identify existing rows when using the upsert write option.
  2. Specify the staging table details:
    • To automatically create a staging table, don't select the Specify staging table name checkbox. This checkbox is under the Advanced options section. This requires CREATE TABLE access in Databricks.
    • To manually specify an existing staging table in Databricks: 
      1. Expand the Advanced options section.
      2. Select the Specify staging table name checkbox.
      3. Enter the Databricks staging table name in the Specify staging table name field.

See the Staging table section below for more information.

Staging tables

Staging tables are used to compare and process data before Data Orchestrator loads the data to the final target table in Databricks.

If you want to use a staging table, you have the option to:

  • Automatically let Data Orchestrator create a staging table in Databricks.
  • Manually specify an existing staging table in Databricks.

This table describes how staging tables are used in Databricks for each option.

Staging table optionsResults
Automatically create a staging table in Databricks

After you run the pipeline:

  • Data Orchestrator automatically creates a temporary staging table in Databricks. Databricks then merges the data in the temporary table with the target Databricks table you selected for the mapping. 
  • After the data has been successfully loaded into the target table, Data Orchestrator deletes the temporary staging table.
Manually specify an existing staging table from Databricks

After you run the pipeline:

  • The data is pushed from Data Orchestrator to the Databricks staging table you specified.
  • Databricks automatically merges the Data Orchestrator data in the staging table with the target Databricks table you selected for the mapping.

Note: To prevent data mismatch errors with Databricks, make sure your data in Data Orchestrator and in Databricks have a schema alignment. This means the tables in Data Orchestrator and in Databricks must have the same columns and data types.

Verify the writeback in Databricks 

Once the pipeline is successfully completed, you can log in to your Databricks account to verify the data load. 

Navigate to the target database, schema, and table specified in the pipeline configuration. The data written from the Data Orchestrator dataset is available in the target table, based on the selected write option (append, full replace, or upsert).