You can create a pipeline that reads a data table in MySQL. The read pipeline imports the data from MySQL to Anaplan Data Orchestrator. It then uses the imported data to create or update a source dataset.

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 read pipeline

Use the MySQL connection you created to create the read pipeline. The read pipeline uses the connection to import data from a table in MySQL to a source dataset in Data Orchestrator.

To create a read 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 MySQL from the Connection type dropdown. 
    2. Select the MySQL connection you created from the Choose connection dropdown.
    3. Enter a Label that displays as the source name in the designer view.
    4. Select a Source location, and then select a table in MySQL that you want to connect.
  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 Anaplan from the Connection type dropdown.
    2. Select Datasets from the Choose connection dropdown.
    3. Select Target location > Table, and choose a dataset destination:
      • Choose existing dataset: If you choose this option, you will be asked to select an existing source dataset.
      • Create new dataset: If you choose this option, you will be asked to enter a Name and Description for the new dataset.
        When you select a write option for the pipeline, it only applies to later pipeline runs if you update the table in MySQL.
    4. Choose a write option for the dataset.
      • Upsert: Updates the existing rows and adds new rows if needed.
      • Append: Adds new data to the dataset without overwriting existing data.
      • Replace: Replaces all existing data with the new data being extracted, and overwrites any previous data.
    5. Review the source data from the MySQL table that's being imported to the dataset, and then select Done.
  9. Select Publish, and then select Run to execute the data transfer.

The dataset displays in the Source datasets screen in Data Orchestrator. If you don't see the dataset, refresh the screen.

Run the pipeline with a Workflow

After you create the pipeline, optionally, you can choose to run the pipeline as part of an Anaplan Workflow. This enables you to automate data transfers based on a schedule, or trigger them manually as part of a larger sequence of tasks.