All Products
Search
Document Center

IoT Platform:Forward data to ApsaraDB RDS

Last Updated:Dec 11, 2023

You can use the data forwarding feature of the rules engine to forward processed data to ApsaraDB RDS instances for storage. This topic describes the data forwarding process. In this example, a Thing Specification Language (TSL) communication topic is used as a source topic.

Prerequisites

  • An IoT Platform instance whose data can be forwarded to ApsaraDB RDS is created in a region that supports data forwarding to ApsaraDB RDS. For more information about the regions that support data forwarding to ApsaraDB RDS, see Regions.

  • A data source named DataSource is created and a TSL communication topic is added to the data source. For more information, see Create a data source.

  • An ApsaraDB RDS instance is created in the region where your IoT Platform instance resides. The database engine of the instance must be MySQL or Microsoft SQL Server and the network type of the instance must be VPC. A database and table are created. For more information about how to use ApsaraDB RDS, see the ApsaraDB RDS documentation.

Background information

After you configure the data destination, the following configurations are automatically completed. Then, you can forward the device data to the ApsaraDB RDS instance by using the rules engine of IoT Platform.
  • IoT Platform occupies two IP addresses of the vSwitch in which the ApsaraDB RDS instance resides. The IPv4 CIDR block of the vSwitch is added to the whitelist of the ApsaraDB RDS instance.
  • A managed security group is created in the virtual private cloud (VPC) in which the ApsaraDB RDS instance resides. The default name of the security group starts with sg-nsm-.

Limits

  • Data forwarding is supported between IoT Platform instances and ApsaraDB RDS instances that reside in the same region. For example, you can forward the data of an IoT Platform instance in the China (Shanghai) region only to an ApsaraDB RDS table that resides in the China (Shanghai) region.
  • You can forward data to ApsaraDB RDS instances that resides in VPCs only.
  • You can forward data to MySQL and SQL Server instances.
  • Data forwarding is supported between standard databases and privileged databases.
  • You can forward only data in the JSON format.

Usage notes

You can use the new version or previous version of the data forwarding feature to forward data to ApsaraDB RDS. For more information about examples on how to use the previous version of the data forwarding feature, see Forward data to ApsaraDB RDS.

Create a data destination

  1. Log on to the IoT Platform console.
  2. On the Overview page, click All environment. On the All environment tab, find the instance that you want to manage and click the instance ID or instance name.

  3. In the left-side navigation pane, choose Message Forwarding > Data Forwarding.
  4. In the upper-right corner of the Data Forwarding page, click Go to New Version to go to the new version.

    Note

    If you have performed this step, the Data Forwarding page of the new version appears after you choose Message Forwarding > Data Forwarding.

  5. Click the Data Destination tab. On this tab, click Create Data Destination.
  6. In the Create Data Destination dialog box, enter a name for the data destination. Example: DataPurpose. Configure other parameters and click OK. The following table describes the parameters.

    Parameter

    Description

    Operation

    Select Store Data to ApsaraDB RDS.

    Region

    Select the region where your IoT Platform instance resides.

    RDS Instance

    Select the ApsaraDB RDS instance to which you want to forward data.

    Database

    Enter the database name.
    Note If you use a privileged database, you must manually specify the database name.

    Account

    Enter the account of the ApsaraDB RDS instance. This account must have the read and write permissions on the database. Otherwise, the rules engine cannot write data to the ApsaraDB RDS instance.
    Note After the rules engine obtains the account, the rules engine writes only the data that matches the specified rule to the database. No extra operations are performed.

    Password

    Enter the password that is used to log on to the ApsaraDB RDS instance.

    Table Name

    Enter the name of the table that is created in the database. The rules engine writes data to the table.

    Role

    Authorize IoT Platform to write data to ApsaraDB RDS.

    If you do not have Resource Access Management (RAM) roles, click Create RAM Role to go to the RAM console, create a RAM role, and then grant permissions to the role. For more information, see Create a RAM role.

Configure and start a parser

  1. Create a parser named DataParser. For more information, see Create a parser.

  2. On the Parser Details page, associate the parser with the created data source.

    1. In the Data Source step of the wizard, click Associate Data Source.

    2. In the dialog box that appears, select DataSource from the Data Source drop-down list, and then click OK.

  3. On the Parser Details page, associate the parser with the created data destination.

    1. Click Data Destination in the wizard. In the Data Destination section, click Associate Data Destination.

    2. In the dialog box that appears, select DataPurpose from the Data Destination drop-down list, and then click OK.

    3. In the Data Destination section, view and save the data destination ID. In this example, the ID is 1000.

      When you write the parser script, you must use the data destination ID.

  4. On the Parser Details page, click Parser.
  5. In the code editor, enter a script. For more information about how modify a script, see Script syntax.

    For more information about function parameters, see Functions.

    • Write data

      // Call the payload() function to obtain data that is submitted by devices and convert the data into the JSON format. 
      var data = payload("json"); 
      // Filter the submitted temperature and humidity values. 
      var h = data.items.Humidity.value;
      var t = data.items.Temperature.value;
      // An ApsaraDB RDS table includes the following columns: id (auto-increment primary key), deviceName, temperature, humidity, and time. You can call the writeRds() method to write values to the columns by using the column:value format. 
      // Configure a rule that is triggered if the temperature value is greater than 38. 
      if (t > 38) { 
          writeRds(1000, {"deviceName":deviceName(), "temperature":t, "time":timestamp(), "humidity":h});  
      }
    • Update data

      // Call the payload() function to obtain the data that is submitted by devices and convert the data into the JSON format. 
      var data = payload("json"); 
      // Filter the submitted temperature and humidity values. 
      var h = data.items.Humidity.value;
      var t = data.items.Temperature.value;
      // An ApsaraDB RDS table includes the following columns: id (auto-increment primary key), deviceName, temperature, humidity, and time. You can call the updateRds() method to update the values of the corresponding columns by using the column:value format. 
      // Specify deviceName as a conditional column to update data. 
      var condition = "deviceName = '" + deviceName() +"'";
      // Update the data of an entry that includes a specified device temperature value and humidity value. If multiple lines of data match the condition, the system automatically updates data from the first line in the result. The value 1 of the limit parameter indicates that the system updates data from the first line. 
      updateRds(1000, {"temperature":t, "time":timestamp(), "humidity":h}, condition, 1);
  6. Click Debugging. In the dialog box that appears, select a product and a device, specify a topic, and then enter payload data to check whether the script runs as expected.

    The following figure shows sample settings.调试示例

    If the following information is returned from the updateRds() method, the script runs as expected.

    调试结果

  7. Click Publish.

  8. Go to the Parser tab of the Data Forwarding page. Find the DataParser parser and click Start in the Actions column to start the parser.

  9. Log on to the ApsaraDB RDS console. On the Data Security page, create a whitelist or view whitelists. After you set the parameters, the rules engine adds the following IP addresses to the ApsaraDB RDS whitelist. Then, the rules engine can communicate with ApsaraDB RDS. If one of the following IP addresses is not displayed in the whitelist, you must manually add the IP address. For more information, see What is ApsaraDB RDS?.
    • China (Shanghai): 100.104.53.192/26, 100.104.148.64/26, 100.104.6.192/26, 100.104.143.128/26, 100.104.76.0/24, 100.104.73.128/26, 100.104.200.64/26, 100.104.40.64/26, 100.104.3.0/26, 100.104.29.128/26, 100.104.121.0/26, and 100.104.84.64/26
    • Singapore (Singapore): 100.104.106.0/24
    • US (Silicon Valley): 100.104.8.0/24
    • US (Virginia): 100.104.133.64/26
    • Germany (Frankfurt): 100.104.160.192/26
    • Japan (Tokyo): 100.104.160.192/26
    ApsaraDB RDS databases