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

You must create a connection to Databricks before you can create a pipeline.

Connectivity prerequisites

Before you can create a connection to Databricks, you need the connectivity prerequisites listed in this table.

ItemNotes
Databricks workspaceA Databricks workspace hosted on Amazon Web Services (AWS), Microsoft Azure, or Google Cloud Platform (GCP). 
Databricks SQL warehouse

A running Databricks SQL warehouse or compute resource that the service principal can access.

When you configure the Databricks SQL warehouse, make sure to give the service principal the appropriate permissions.

Databricks service principal 

You need a Databricks service principal that's configured with these items:

  • OAuth authentication: Configured for OAuth authentication with a Client ID and Secret. For more information, see the Databricks OAuth documentation.
  • Entitlements: Make sure to grant the service principal the necessary entitlements. 
    • These entitlements are required: Databricks SQL access and Workspace access.
    • For more information, see the Databricks entitlements documentation.
Service principal accessAccess for the service principal to the SQL Warehouse or compute resource used for the connection. 
Unity Catalog permissions

The required Unity Catalog permissions for the service principal to access the target catalog and schema: 

  • USE CATALOG
  • USE SCHEMA 
  • SELECT for data extraction 
  • MODIFY and CREATE TABLE if required for writeback

For example: 

GRANT USE CATALOG ON CATALOG <catalog_name> TO `<service_principal_id>`; 

GRANT USE SCHEMA ON SCHEMA <catalog_name>.<schema_name> TO `<service_principal_id>`; 

GRANT SELECT ON SCHEMA <catalog_name>.<schema_name> TO `<service_principal_id>`; 

GRANT MODIFY, CREATE TABLE ON SCHEMA <catalog_name>.<schema_name> TO `<service_principal_id>`; 

Databricks connection details

You need the following Databricks connection details: 

  • Host 
  • Port 
  • HTTP Path 
  • Catalog 
  • Schema 
  • Client ID 
  • Secret 

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 credentialsDesctiption
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 port number is typically 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 Databicks 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. Select Source location, choose a Data Orchestrator dataset, and then select Confirm.
      The dataset is used as the source for your pipeline.
    4. Enter a new Label if you want to change the source display name in the designer view.
      By default, the name of the source dataset you selected displays as the label name.
  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. Select Target location, select an Databricks table, and then select Done.
    4. Select Target mapping, map the Data Orchestrator source dataset values to the Databricks target values, and then select Done.
    5. Select the desired Write option in the right-side panel.
      This determines how data is written to the target table. Your write options are described in this table:
Write optionsDescription
Append

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

If you select Append as the write option, determine if you want to configure a staging table. To configure the staging table, see the Staging table section below.

To prevent data mismatch errors with Databricks has schema alignment, 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.

Note: Append load is not suitable for target tables with primary keys. 

Full replaceDeletes all existing data in the target table and replaces it with the source data.
Upsert

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

If you select Upsert as the write option, select Conflict columns, which are used to identify existing rows when you use this write option.  

You also need to determine if you want to configure a staging table. See the Staging table section below for more information.

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.

  1. Enter a new Label if you want to change the sink display name in the designer view.
    By default, the name of the target table you selected displays as the label name.
  2. Select Publish, and then select Run to execute the data transfer.

Staging table preparations

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

When you configure the Sink details, Data Orchestrator uses a staging table in Databricks if you select the Append or Upsert write option. The Specify staging table name checkbox displays after you select the Append or Upsert write option. By default, the checkbox is unselected.

By default, the checkbox is unselected, and Data Orchestrator creates the staging table in Databricks. If Data Orchestrator doesn't have access to create tables in Databricks, you must manually create and provide the staging table information in Data Orchestrator.

There are two ways to create staging tables:

  • Let Data Orchestrator automatically create a staging table in Databricks.
  • Manually create a staging table in Databricks.

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).