Elasticsearch:Use DataWorks to synchronize data from RDS SQL Server to Alibaba Cloud ES

Step 1: Prepare the source data

Create the test table and insert a record in your ApsaraDB RDS for SQL Server instance.

Create the table:

CREATE TABLE students(
    id INT,
    name VARCHAR(20),
    age INT
)

Insert a record:

INSERT INTO [students]( [id] , [name] , [age] ) values('1', 'Xiaoming','21');

Step 2: Purchase and configure an exclusive resource group

An exclusive resource group for Data Integration provides a dedicated, isolated runtime environment for sync tasks. It connects to your VPC so that Data Integration can reach both your RDS SQL Server and Elasticsearch instances over the internal network.

Network connectivity overview

To synchronize data over the VPC internal network, you must:

1. Purchase an exclusive resource group.

2. Attach the VPC, Zone, and vSwitch shared by both instances to the resource group.

3. Add the vSwitch CIDR block to the internal-facing whitelists of both instances.

4. Attach the workspace to the resource group.

Procedure

1. Log on to the DataWorks consoleDataWorks consoleDataWorks console.

2. In the top menu bar, select a region. In the left navigation pane, click Resource Group.

3. On the Exclusive Resource Groups tab, click Create Legacy Resource Group > Data Integration Resource Group.

4. On the DataWorks Exclusive Resource (Subscription) purchase page, set Exclusive Resource Type to Exclusive Resource for Data Integration, enter a name for the resource group, and then click Buy Now. For more configuration options, see Step 1: Purchase a resource group.

5. Attach a VPC to the exclusive resource group. In the Operation column for the resource group, click Network Settings. Attach the VPC, Zone, and vSwitch where both the RDS SQL Server and Elasticsearch instances reside. To look up the VPC information for each instance, see Switch the vSwitch for an ApsaraDB RDS for SQL Server instance and View the basic information of an Elasticsearch instance. For step-by-step instructions, see Attach a VPC.

   Important

   After attaching the VPC, add the vSwitch CIDR block to the internal-facing whitelists of both the RDS SQL Server and Elasticsearch instances. See Configure a whitelist for an ApsaraDB RDS for SQL Server instance and Configure a public or internal-facing whitelist for an Elasticsearch instance.

6. Click the back icon in the upper-left corner to return to the Resource Group List page.

7. In the Operation column for the resource group, click Attach Workspace and select your workspace. For details, see Step 2: Attach a workspace.

Step 3: Add data sources

Add both the ApsaraDB RDS for SQL Server and the Elasticsearch data sources to Data Integration.

1. Go to the Data Integration page.

   1. Log on to the DataWorks consoleDataWorks consoleDataWorks console.

   2. In the left navigation pane, click Workspaces.

   3. In the Operation column of your workspace, choose Quick Access > Data Integration.

2. In the navigation pane, click Data Source.

3. Add the SQL Server data source.

   1. On the Data Source List page, click Add Data Source.

   2. Search for and select SQL Server.

   3. In the Add SQL Server Data Source dialog box, fill in the parameters in the Basic Information area. For parameter details, see Configure a SQL Server data source.

   4. In the Connection Configuration area, click Test Connectivity. A status of Connected confirms a successful connection.

   5. Click Finish.

4. Add the Elasticsearch data source the same way. For details, see Configure an Elasticsearch data source.

Step 4: Configure and run an offline sync task

The offline sync task runs on the exclusive resource group, which pulls data from the SQL Server data source and writes it to Elasticsearch.

1. Go to the Data Development page.

   1. Log on to the DataWorks consoleDataWorks consoleDataWorks console.

   2. In the left navigation pane, click Workspaces.

   3. In the Operation column of your workspace, choose Quick Access > Data Development.

2. Create an offline sync task.

   1. In the navigation pane, click the icon and choose New > New Business Flow.

   2. Right-click the new business flow and choose New Node > Offline Synchronization.

   3. In the New Node dialog box, enter a node name and click Confirm.

3. Configure the network and resources.

   1. In the Data Source area, set Data Source to SQL Server and select your data source from Data Source Name.

   2. In the My Resource Groups area, select the exclusive resource group.

   3. In the Data Destination area, set Data Destination to Elasticsearch and select your data source from Data Source Name.

4. Click Next.

5. Configure the task details. For the full list of configuration options, see Configure an offline sync task using the codeless UI.

   1. In the Data Source area, select the table to sync.

   2. Configure the parameters in the Data Destination area.

   3. In the Field Mapping area, map the Source Field to the Destination Field. For details, see Configure an offline sync task using the codeless UI.

   4. In the Channel Control area, you can configure the channel parameters.

6. Run the task. If you configured a schedule, the task runs automatically at the specified intervals. To run the task immediately, click the run icon in the upper-right corner. A log message of Shell run successfully! confirms that the task completed successfully.

   1. (Optional) In the right pane, click Scheduling Configuration to configure the scheduling parameters for the task. For parameter details, see Scheduling configuration.

   2. In the upper-right corner of the node configuration tab, click the save icon.

   3. Click the submit icon to submit the task.

Step 5: Verify the data synchronization result

1. Log on to the Kibana console of your Elasticsearch instance. See Log on to the Kibana console.

2. In the top-left corner of the Kibana page, click the menu icon and select Dev Tools.

3. In the Console, run the following query to view the synchronized data:

   POST /dbo.students/_search?pretty
   {
      "query": { "match_all": {}}
   }

   Note

   Replace dbo.students with the index name you specified when configuring the sync task in DataWorks.

   The expected result is shown in the following figure.