Forwarding Data to MySQL for Storage

Scenarios

IoTDA can forward data reported by devices to RDS for MySQL for storage. You can use device data for service processing without developing additional code to store data.

In this example, data reported by all devices is forwarded to a MySQL database.

Prerequisites

• You have purchased an IoTDA Enterprise or Standard edition instance.
• You have purchased an RDS for MySQL instance.

Creating a MySQL Database

1. Log in to the Huawei Cloud official website, visit RDS for MySQL, and purchase an instance. IoTDA enterprise edition instances support Connecting to an RDS for MySQL DB Instance Through a Private Network. IoTDA standard edition instances support Connecting to an RDS for MySQL DB Instance Through a Public Network.
2. When you purchase a MySQL instance, design database tables by referring to Data Transfer APIs to enable editing of forwarded data using filter statements. In this example, the default formats in Push a Device Property Reporting Notification are used to store the resource, event, notify_data, and event_time fields in the forwarded data to the resource, event, content, and event_time fields in the database table.
   Figure 1 Example of creating a database table
   

Configuring IoTDA

Using IoTDA, you can create a product model, register a device, and set a data forwarding rule to forward data reported by the device to MySQL.

1. Access the IoTDA service page and click Access Console. Click the target instance card.
2. In the navigation pane, choose Products. Click Create Product and select the resource space to which the new product will belong.
   

   The product model and device used in this topic are only examples. You can use your own product model and device.

3. Click Create Product to create a product using MQTT. Set the parameters and click OK.

   Basic Information
   Product Name	Enter a value, for example, MQTT_Device.
   Protocol Type	Select MQTT.
   Data Type	Select JSON.
   Industry	Set the parameters as required.
   Device Type

4. Click here to download a sample product model.
5. On the Basic Information tab page, click Import from Local. In the displayed dialog box, load the local product model and click OK.
   Figure 2 Product - Uploading a product model
   

6. In the navigation pane, choose Devices > All Devices. Click Register Device, set device registration parameters, and click OK. Save the device ID and secret returned after the registration.
   Figure 3 Device - Registering a secret device
   

   Parameter	Description
   Resource Space	Select the resource space (created in step 3) to which the product will belong.
   Product	Select the product created in step 3.
   Node ID	Set this parameter to the IMEI, MAC address, or serial number of the device. If the device is not a physical one, set this parameter to a custom character string that contains letters and digits.
   Device Name	Customize the product name.
   Device ID	Customize the value. You can leave it empty, then the platform will automatically generate a device ID.
   Authentication Type	Select Secret.
   Secret	Customize the secret used for device access. If the secret is left blank, the platform automatically generates one.

7. In the navigation pane, choose Rules > Data Forwarding, and click Create Rule in the upper left corner.
   

   You can also add a MySQL database as the forwarding target on the details page of a created rule.

8. Set the parameters based on the table below. The following parameter values are only examples. You can configure parameters of data forwarding rules by referring to Data Forwarding. After configuring the parameters, click Create Rule.

   Parameter	Description
   Rule Name	Customize a name, for example, iotda-mysql.
   Description	Enter a rule description, for example, forwarding data to MySQL for storage.
   Data Source	Select Device property.
   Trigger	Device property reported is automatically populated.
   Resource Spaces	Select a resource space to which the data source to be forwarded belongs or all resource spaces.

9. Click the Set Forwarding Target tab, and then click Add to set a forwarding target.

   Parameter	Description
   Forwarding Target	Select MySQL (RDS).
   Database Instance Address	Enter the IP address (or port number) for connecting the database instance.
   Database Name	Enter the name of the destination database in the database instance.
   Access Account	Enter the account of the database instance.
   Access Password	Enter the password of the database instance.
   SSL	Select whether to connect to the database in SSL mode. You are advised to use SSL for connection. If not, security risks may exist during data transmission. To use the SSL mode, configure an SSL connection in the database instance first.
   Certificate ID	Truststore certificate, which is used by the client to verify the server certificate. If this parameter is left blank, the default certificate provided by RDS for MySQL is used. For RDS for MySQL instances using custom certificates, upload the custom CA on the Rules > Server Certificates page and complete the binding.

10. Click Next. IoTDA will connect to the database during the process.
11. Select the target table and configure the mapping between the data to forward and the database table.
    • Forwarding Field: JSON key of the data to forwarded.
    • Target Field: field in the database table. After a target field is selected, the field type is automatically matched.
    Figure 4 Setting MySQL field mapping
    

12. Click Enable Rule to activate the configured data forwarding rule.

Verifying the Configurations

An event (for example, device property reporting) triggers data forwarding.

Expected result:

Log in to the RDS for MySQL console and open the target table. Data forwarded to the target fields is displayed in the table.

Figure 5 Querying data reported