Elasticsearch:Synchronize PolarDB-X data to Alibaba Cloud Elasticsearch with DataWorks

Prerequisites

• A PolarDB-X instance is created. For more information, see .

• An Alibaba Cloud Elasticsearch instance is created, and the auto-indexing feature is enabled. For more information, see Create an Alibaba Cloud Elasticsearch instance and Configure the YML file.

• A DataWorks workspace is created. For more information, see Create a workspace.

Billing

• For Elasticsearch instance fees, see Elasticsearch billable items.

• For Data Integration resource group fees, see Resource group fees.

Step 1: Prepare source data

1. Insert data into the PolarDB-X 1.0 instance.

   For more information, see Basic SQL operations. This topic uses the test data shown in the following figure.

Step 2: Create an exclusive resource group

Purchase an exclusive resource group for Data Integration and associate it with your VPC and workspace to ensure fast and stable data transfer.

1. Log on to the DataWorks console.

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

3. On the Exclusive Resource Group tab, click Create Resource Group for DI > Resource Group for Data Integration.

4. On the DataWorks Exclusive Resources (Subscription) purchase page, set Exclusive Resource Type to Exclusive Resource Group For Data Integration, enter a name for the resource group, and then click Buy Now to purchase the exclusive resource group.

   For more configuration information, see Step 1: Purchase a resource group.

5. In the Actions column of the created exclusive resource group, click Network Settings to associate the exclusive resource group with a VPC. For more information, see Associate a VPC.

   Note

   This topic describes how to synchronize data over a VPC internal network using an exclusive resource group for Data Integration. For information about synchronizing data over the internet, see Add a whitelist.

   To synchronize data, the exclusive resource group must connect to the VPCs of the PolarDB-X and Elasticsearch instances. Associate the exclusive resource group with the Virtual Private Cloud (VPC), Zone, and VSwitch of the PolarDB-X and Elasticsearch instances. To view the VPC information of an instance, see View the basic information of an Elasticsearch instance.

   Important

   After associating the exclusive resource group with a VPC, add the CIDR block of the vSwitch CIDR Block to the private access whitelists of the PolarDB-X and Elasticsearch instances. For more information, see Configure a public or private access whitelist for an Elasticsearch instance.

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

7. In the Actions column of the created exclusive resource group, click Associate Workspace to associate the exclusive resource group with the destination workspace.

   For more information, see Step 2: Associate the resource group with a workspace.

Step 3: Add data sources

Add PolarDB-X and Elasticsearch as data sources in DataWorks Data Integration.

1. Go to the Data Integration page.

   1. Log on to the DataWorks console.

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

   3. Find the target workspace and choose Shortcuts > Data Integration in the Actions column.

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

3. Add a PolarDB-X data source.

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

5. On the Add Data Source page, search for and select DRDS.

6. On the Add DRDS Data Source page, configure the data source information and test the connectivity. After the connectivity test succeeds, click Complete.

   For more information, see Add a DRDS data source.

7. Follow the same procedure to add an Elasticsearch data source. For more information, see Configure an Elasticsearch data source.

Step 4: Configure and run a synchronization task

A batch synchronization task runs on an exclusive resource group to read data from the source in Data Integration and write it to Elasticsearch.

1. Go to the Data development page in DataWorks.

   1. Log on to the DataWorks console.

   2. In the left navigation pane, click Workspace.

   3. In the Actions column of the destination workspace, choose Quick Access > Data development.

2. Create a batch synchronization task.

   1. On the Data Development page, click the () icon, choose Create > New business process, and then create a Business Flow, following the prompts.

   2. Right-click the created Business Flow and choose Create Node > 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 section, set Source to DRDS and set Data Source Name to the source data source.

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

   3. In the Destination section, set Destination to Elasticsearch and set Data Source Name to the destination data source.

4. Click Next.

5. Configure the task.

   1. In the Source section, select the source table.

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

   3. In the Field Mapping section, map the Source Column to the Target Column. For more information about the configuration, see Configure a task in wizard mode.

      In this example, keep the Source Column unchanged and modify only the Target Column. To the right of Target Column, click the icon and enter the following field definitions in the dialog box.

      {"name":"Name","type":"text"}
      {"name":"Platform","type":"text"}
      {"name":"Year_of_Release","type":"date"}
      {"name":"Genre","type":"text"}
      {"name":"Publisher","type":"text"}
      {"name":"na_Sales","type":"float"}
      {"name":"EU_Sales","type":"float"}
      {"name":"JP_Sales","type":"float"}
      {"name":"Other_Sales","type":"float"}
      {"name":"Global_Sales","type":"float"}
      {"name":"Critic_Score","type":"long"}
      {"name":"Critic_Count","type":"long"}
      {"name":"User_Score","type":"float"}
      {"name":"User_Count","type":"long"}
      {"name":"Developer","type":"text"}
      {"name":"Rating","type":"text"}

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

   For detailed configuration information, see Configure a task in wizard mode.

6. Run the task.

   1. (Optional) Configure the scheduling properties for the task. On the right side of the page, click Properties and configure the scheduling parameters as required. For more information about each parameter, see Scheduling configuration.

   2. In the toolbar of the node editing area, click the save icon to save the task.

   3. In the toolbar of the node editing area, click the submit icon to submit the task.

      If you configured scheduling properties for the task, the task runs periodically. You can also click the run icon in the upper-right corner of the node area to immediately run the task.

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

Step 5: Verify synchronization results

1. Log on to the Kibana console for the destination Alibaba Cloud Elasticsearch instance.

   For more information, see Log on to the Kibana console.

2. In the left navigation pane, click Dev Tools.

3. In the Console, run the following command to query the data count in the destination.

   Note

   Compare the data count in the source and destination to verify that all data was synchronized.

   GET drdstest/_search
   {
     "query": {
       "match_all": {}
     }
   }

   If the command runs successfully, the following result is returned.

4. Run the following command to search for data by a specified field.

   GET drdstest/_search
   {
     "query": {
       "term": {
         "Publisher.keyword": {
           "value": "Nintendo"
         }
       }
     }
   }

   If the command runs successfully, the following result is returned.