Elasticsearch:Synchronize MySQL data to Alibaba Cloud Elasticsearch with DataWorks

Prerequisites

• Create an RDS for MySQL instance. For more information, see Create an RDS for MySQL instance. This topic uses MySQL 5.7 as an example.

• Create an Alibaba Cloud Elasticsearch instance and enable its automatic index creation feature. For more information, see Create an Alibaba Cloud Elasticsearch instance and Configure YML parameters.

• Create a DataWorks workspace. For more information, see Create a workspace.

Billing

• For information about the fees for an Alibaba Cloud Elasticsearch instance, see Elasticsearch billable items.

• For information about the fees for a Data Integration resource group, see Resource group fees.

Step 1: Prepare the source data

Create a database and a table in the RDS for MySQL instance.

• You can use an Alibaba Cloud RDS database or a self-managed database on a local server. This topic uses an RDS for MySQL database as an example. For more information, see Quick start.

• The following sample code shows how to create a table and insert data.

  -- create table
  CREATE TABLE `es_test` (
      `id` bigint(32) NOT NULL,
      `name` varchar(32) NULL,
      `age` bigint(32) NULL,
      `hobby` varchar(32) NULL,
      PRIMARY KEY (`id`)
  ) ENGINE=InnoDB
  DEFAULT CHARACTER SET=utf8;
  
  -- insert data
  INSERT INTO `es_test` (`id`,`name`,`age`,`hobby`) VALUES (1,'user1',22,'music');
  INSERT INTO `es_test` (`id`,`name`,`age`,`hobby`) VALUES (2,'user2',23,'sport');
  INSERT INTO `es_test` (`id`,`name`,`age`,`hobby`) VALUES (3,'user3',43,'game');
  INSERT INTO `es_test` (`id`,`name`,`age`,`hobby`) VALUES (4,'user4',24,'run');
  INSERT INTO `es_test` (`id`,`name`,`age`,`hobby`) VALUES (5,'user5',42,'basketball');

Step 2: Purchase and configure a resource group

Purchase a Data Integration resource group and associate a VPC and a workspace with the resource group.

1. Log on to the DataWorks console.

2. In the top navigation bar, select a region. In the left-side navigation pane, click Resource Groups.

3. Click Create Resource Group and configure the parameters as prompted.

4. In the Actions column of the created resource group, click Network Settings to associate a VPC with the resource group. For more information, see Configure network settings.

   To allow data synchronization, the resource group must be able to connect to both the RDS for MySQL instance and the Elasticsearch instance. Therefore, you must associate the resource group with the same Virtual Private Cloud (VPC), Zone, and VSwitch as your instances. To find the VPC information for your instances, see Switch the VPC and vSwitch for an RDS for MySQL instance and View the basic information of an Elasticsearch instance.

   Important

   After you associate the VPC, you must add the CIDR block of the vSwitch CIDR Block to the IP whitelist of both the RDS for MySQL instance and the Elasticsearch instance. For more information, see Configure an IP whitelist for an RDS for MySQL instance and Configure a public or private IP whitelist for an Elasticsearch instance.

5. In the upper-left corner of the page, click the back icon to return to the Resource List page.

6. In the Actions column of the created resource group, click Bind workspace to associate the target workspace with the resource group.

Step 3: Add data sources

Add the RDS for MySQL and Elasticsearch data sources to the Data Integration service in DataWorks.

1. Go to the Data Integration page in DataWorks.

   1. Log on to the DataWorks console.

   2. In the left-side navigation pane, click Workspace.

   3. In the Actions column of the target workspace, choose Quick access > Data Integration.

2. In the left-side navigation pane, click Data Source.

3. Add an RDS for MySQL data source.

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

   2. On the Add Data Source page, search for and select MySQL.

   3. In the Add MySQL data source dialog box, configure the data source parameters in the Basic Information section.

      For more information, see Configure a MySQL data source.

   4. In the Connection Configuration section, click Test Connectivity. A Connected status indicates a successful connection.

   5. Click Complete.

4. Add an Elasticsearch data source in the same way. For more information, see Configure an Elasticsearch data source.

Step 4: Configure and run an offline synchronization task

An offline synchronization task runs on a resource group, which serves as the computing resource. The resource group retrieves data from the configured data source and writes it to Elasticsearch.

1. Go to the Data development page in DataWorks.

   1. Log on to the DataWorks console.

   2. In the left-side navigation pane, click Workspace.

   3. In the Actions column of the target workspace, choose Quick access > Data development.

2. Create an offline synchronization node.

   1. On the Data Studio tab ( icon) in the left-side navigation pane, choose Create > New business process. Then, create a workflow as prompted.

   2. Right-click the created workflow and choose Create Node > Data Integration > Batch Synchronization.

   3. In the Create Node dialog box, enter a node name and click OK.

3. Configure the network and resources.

   1. In the Source area, for Source, select MySQL, and for Data Source Name, select the name of the data source to synchronize.

   2. In the Resource Group section, select a resource group.

   3. In the Destination section, for Destination, select Elasticsearch, and for Data Source Name, select the name of the data source to synchronize.

4. Click Next step.

5. Configure the task.

   1. In the Source section, select the table that you want to synchronize.

   2. In the Destination section, configure the parameters for the destination.

   3. In the Field Mapping section, configure the mappings between Source Column and Target Column.

   4. In the Channel Control section, configure the channel parameters.

   For more information about the configuration, see Configure a synchronization task by using the wizard.

6. Run the task.

   1. (Optional) Configure scheduling properties for the task. In the right-side pane, click Properties and configure the scheduling parameters as required. For more information about the parameters, see Configure scheduling properties.

   2. In the upper-left corner of the node editor, click the Save icon to save the task.

   3. In the upper-left corner of the node editor, click the Submit icon to submit the task.

      If you configured scheduling properties, the task runs automatically based on the schedule. You can also click the Run icon in the upper-left corner of the node editor to immediately run the task.

      The Shell run successfully! message in the operational log indicates that the task ran successfully.

Step 5: Verify the result

1. Log on to the Kibana console of the destination Alibaba Cloud Elasticsearch instance. For more information, see Log on to the Kibana console.

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

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

   POST /es_test/_search?pretty
   {
   "query": { "match_all": {}}
   }

   Note

   Replace es_test with the index name that you specified in the data synchronization task.

   If the data is synchronized successfully, the following result is returned.

   GET /es_test/_search
   
   {
     "took" : 0,
     "timed_out" : false,
     "_shards" : {
       "total" : 1,
       "successful" : 1,
       "skipped" : 0,
       "failed" : 0
     },
     "hits" : {
       "total" : 5,
       "max_score" : 1.0,
       "hits" : [
         {
           "_index" : "es_test",
           "_type" : "es_test",
           "_id" : "2",
           "_score" : 1.0,
           "_source" : {
             "age" : 23,
             "hobby" : "sport",
             "id" : 2,
             "name" : "user2"
           }
         },
         {
           "_index" : "es_test",
           "_type" : "es_test",
           "_id" : "1",
           "_score" : 1.0,
           "_source" : {
             "age" : 22,
             "hobby" : "music",
             "id" : 1,
             "name" : "user1"
           }
         }
       ]
     }
   }