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

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

Connectivity prerequisites

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

ItemNotes
MySQL account

You must have active access to an MySQL account and instance where the target tables reside. 

For example, the MySQL instance can be hosted on these cloud platforms: Amazon Web Services (AWS), Azure, Google Cloud Platform (GCP), or any other cloud provider.

Certificate Authority (CA) certificateAn unencrypted CA certificate in a Privacy-Enhanced Mail (PEM) format.
Database user

A database user with a password and permissions (at least read permission).

For example:

CREATE USER <user_name> IDENTIFIED BY 'your_password_here';
GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *&ZeroWidthSpace;.* TO <user_name>;

Appropriate permissions The MySQL user associated with the connection must have INSERT and SELECT privileges on the target table. They must also have the USAGE privilege on the database schema where the table resides.
Target table in MySQLA target table must already exist within your MySQL database.   

Create a connection to MySQL

You need your MySQL credentials to connect the MySQL data with Data Orchestrator. See the MySQL documentation for more information.

To create a connection:

  1. Select Data Orchestrator from the top-left navigation menu.
  2. Choose a dataspace from the list.
  3. Select Connections on the left-side panel.
  4. Select Create connection.
  5. Select MySQL and then select Next.
    If you can'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: Enter a description about your connection.
  7. On the Connection credentials page, enter your MySQL credentials, and then select Next.
    • Username: The MySQL user name required to authenticate.
    • Password: The MySQL password required to authenticate.
    • Host: The MySQL host domain. The host is provided by the administrator of your MySQL instance.
    • Port: The MySQL port number. The port number is provided by the administrator of your MySQL instance.
    • Database: The MySQL database name.
    • CA Certificate: The MySQL connector supports TLS/SSL encryption. TLS/SSL encryption enables you to establish a secure, encrypted connection to MySQL instances. It protects data in transit from being intercepted or read by unauthorized parties. The connection is secured using a digital certificate installed on the server. This provides an essential layer of data protection.

      For the connector to use the CA certificate, you must first decode the Base64-encoded CA certificate in the PEM format. For example:

-----BEGIN CERTIFICATE-----
MIIDBzCCAe+....................
.......multiple line...........
-----END CERTIFICATE-----

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

Note: If you need to edit the connection, you must enter the CA certificate again.

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

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 MySQL from the Connection type dropdown.
    2. Select the MySQL connection you created from the Choose connection dropdown.
    3. Select Target location, select a MySQL table, and then select Done.
    4. Select Target mapping, map the Data Orchestrator source dataset values to the MySQL 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 MySQL, make sure your data in Data Orchestrator and in MySQL have a schema alignment. This means the tables in Data Orchestrator and in MySQL 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 MySQL, make sure your data in Data Orchestrator and in MySQL have a schema alignment. This means the tables in Data Orchestrator and in MySQL 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 MySQL.

When you configure the Sink details, Data Orchestrator uses a staging table in MySQL 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, and Data Orchestrator creates the staging table in MySQL. If Data Orchestrator doesn't have access to create tables in MySQL, 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 MySQL.
  • Manually create a staging table in MySQL.

Verify the writeback in MySQL 

Once the pipeline is successfully completed, you can log in to your MySQL 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).