Step 1: Select MySQL ingest connector
- From the main menu, click on Add connector
- In the drop down list, find and select MySQL connector
Step 2: Configure connection parameters
Datacoral currently supports 2 methods of connecting to your source DB, a Direct connection and connecting via an SSH Host.
Direct Connection to source DB
Fill the below mandatory fields.
- Database - name of your MySQL database
- Host - database host (FQDN)
- Port - database port, for more on ports refer this link
- Username - database user name
- Password - database password
Connecting to source via SSH (optional)
Please specify the input parameters below:
- sshTunnelHost - SSH server host name
- sshUser - SSH server username
- privateKey - SSH server private key (We support only RSA keys)
- passphrase - SSH server passphrase
- Click Check connection and Next
Step 3: Configure source information
- Interval : Set the frequency of data extraction
- Sync Historical data : It will load the entire past database as a one-time activity
- Click on Fetch Source Metadata to see all the load units and click Next
Step 4: Configure load units information
The list of loadunits with extraction mode and schedule is displayed.
Extraction mode is auto detected based on the table size and availability of primary key and timestamp column at the source table. Click on Edit to update edit configuration per loadunit.
- Extraction mode: Can be snapshot or incremental Snapshot is the default option, on choosing incremental you should identify the Timestamp Column as an input
- Data Format: Note that MySQL supports JSON format
- Interval: The frequency of the extraction mode ranges in discrete interval from 5 minutes
- Column list: Column name input, add one name at a time and click Add
- Paginate: Should be chosen when the data volume is high
On choosing Paginate, provide additional information on page count, pagination columns and page size
Step 5: Edit data layouts
Update data type as needed and click on Next to add the connector
Step 6: Configure warehouse
For each of the load units on the left, you can decide the load mode
Load Mode: Datacoral supports the below load modes
- Replace : This is a wipe and load operation replacing all the rows of the destination table with the results of the transformation query
- Append: Insert operation where, the result of the transformation query are inserted into the destination table, rows already in the destination table are not updated
- Merge: Upsert operation where the transformation query results in rows that indicate that the destination table rows have to be inserted, updated, or even deleted. This mode allows for efficient incremental updates to destination tables
When done with the configuration changes, please click on Update and Next on the top right.
Step 7: Confirm the configuration
You will see the below dialog, click Next to confirm addition of the connector.
You have successfully added the connector once you have landed on the below page.