Writing data from a device to Managed Service for PostgreSQL

In this scenario, you'll learn how to store information received from a device in the Managed Service for PostgreSQL cluster database. To connect your device to Yandex IoT Core, you'll need an MQTT broker. All scenario steps are performed in the management console.

To start writing information from your device to the database:

1. Create the necessary Yandex IoT Core resources.
   1. Create a registry.
   2. Create a device.
2. Connect your device to the MQTT broker.
3. Prepare the database.
   1. Create a cluster.
   2. Connect to the cluster.
   3. Create a table.
4. Create a function for processing data.
5. Create a trigger for Yandex IoT Core.

Create the necessary Yandex IoT Core resources

Create a registry

1. In the management console, select the folder where you want to create a registry.

2. Click Create resource.

3. Choose Device registry.

4. In the Name field, enter my-registry.

5. Enter the password.

   • Minimum password length is 14 characters.
   • The password must contain uppercase and lowercase letters and numbers.

   Warning

   Save your password as you won't be able to read it from Yandex IoT Core.

6. Skip the Certificates section.

7. Click Create.

Create a device

After creating the registry, you'll be automatically redirected to the Registries page.

1. From the list of registries, select my-registry.

2. Go to the Devices tab.

3. Click Add device.

4. In the Name field, enter my-device.

5. Enter the password.

   • Minimum password length is 14 characters.
   • The password must contain uppercase and lowercase letters and numbers.

   Warning

   Save your password as you won't be able to read it from Yandex IoT Core.

6. Skip the Aliases and Certificates sections.

7. Click Add.

Connect your device to the MQTT broker

Connect to the MQTT broker using the following parameters:

• Certification authority certificate.
• Broker address: mqtt.cloud.yandex.net
• Broker port: 8883
• Protocol: TLSv1.2

Prepare the database

Create a cluster

1. In the management console, select the folder where you want to create a cluster.

2. In the list of services, select Managed Service for PostgreSQL.

3. Click Create cluster.

4. In the Cluster name field, enter my-pg-database.

5. In the Environment field, select PRODUCTION.

6. In the Version field, select 12.

7. Under Host class:

   • Specify the Intel Cascade Lake platform.
   • Select the burstable type.
   • Specify the b2.nano class.

   Warning

   The b2.nano class was only selected as part of testing. In real projects, it isn't recommended to use hosts with a guaranteed vCPU share under 100%.

8. Under Storage size:

   • Choose network-ssd.
   • Set the size to 10 GB.

   Note

   The type and size of the disk should be selected according to the projects to be implemented. The above values are used for testing.

9. Under Database, specify the DB attributes:

   • Database name. The DB name must be unique within the folder and contain only Latin letters, numbers, and underscores.
   • The name of the user who is the DB owner. The username may only contain Latin letters, numbers, and underscores.
   • User password. From 8 to 128 characters.

   For a database created with the cluster, the character set and collate settings are specified as LC_CTYPE=C and LC_COLLATE=C. You can't change these settings after the database is created, but you can create a new database with the right settings.

10. Under Hosts, add a host to be accessible from outside of Yandex Cloud. To do this, enable Public access.

11. Leave the default values in the other fields.

12. Click Create cluster.

For more information about creating a cluster, see How to create a PostgreSQL cluster.

Afterwards, the cluster settings can be updated.

Connect to the cluster

After creating the cluster, you'll be automatically redirected to the Clusters page.

1. Select the my-pg-database cluster.
2. Go to the SQL tab.
3. In the DB username field, specify the name of the user who owns the DB created in the previous step.
4. In the Password field, enter the password specified when creating the cluster.
5. In the Database field, enter the name of the database.
6. Click Connect.

Create a table

As a sample data source, the scenario uses an air sensor that measures the following parameters:

• Humidity.
• The level of carbon dioxide (CO2).
• Pressure.
• Temperature.

The sensor outputs the result in JSON format. For example:

{
"DeviceId":"0e3ce1d0-1504-4325-972f-55c961319814",
"TimeStamp":"2020-05-21T22:53:16Z",
"Values":[
    {"Type":"Float","Name":"Humidity","Value":"25.281837"},
    {"Type":"Float","Name":"CarbonDioxide","Value":"67.96608"},
    {"Type":"Float","Name":"Pressure","Value":"110.7021"},
    {"Type":"Float","Name":"Temperature","Value":"127.708824"}
    ]
}

Write the received information to a DB table using a function.

Once you connect to the cluster, create a table. To do this:

1. In the edit window, enter the following query:

   Warning

   The query below is given as an example. If your device sends different information, change the columns in the table you're creating.

   CREATE TABLE iot_events (
       event_id varchar(24) not null,
       device_id varchar(50) not null,
       event_datetime timestamptz not null,
       humidity float8 null,
       carbon_dioxide float8 null,
       pressure float8 null,
       temperature float8 null
   )
   

2. Click Run.

3. Wait until a message that the query is executed appears.

Create a function for processing data

The function will receive messages from the MQTT broker and write data to the table created in the previous step.

1. In the management console, select the folder where you want to create a function.
2. In the list of services, select Cloud Functions.
3. Click Create function.
4. Enter a function name.
5. Click Create.

Create the first version of the function

After creating the function, you'll be automatically redirected to the Editor page.

1. Under Code:

   • In the Runtime environment field, choose python37.
   • In the Method field, leave the default value: Code editor.

2. Create a file named myfunction.py.

3. In the file editing area, insert the function code from Github.

   Note

   The query used to write data to the DB is generated in the makeInsertStatement method. If you need to remove or add parameters, make changes to this method.

4. In the Entry point field, specify myfunction.msgHandler.

5. Specify the following version parameters:

   • Timeout, sec: 10.
   • RAM: 128 MB.

6. Create a service account:

   1. Click Create account (or Create new). An additional window opens.
   2. In the Name field, enter my-db-function-service-account.
   3. Add roles: serverless.functions.invoker and editor.
   4. Click Create.

   The created account is automatically added to the Service account field. On behalf of this account, the function will write data to the DB.

7. Add environment variables:

   • VERBOSE_LOG: Parameter that displays detailed information about the function execution. Set it to True.
   • DB_HOSTNAME: Name of the PostgreSQL DB host to connect to.
   • DB_PORT: Port to connect to.
   • DB_NAME: Name of the database to connect to.
   • DB_USER: Username for the connection.
   • DB_PASSWORD: Password that you entered when creating your cluster.

   To define the values of connection parameters:

   1. In the management console, select the folder where you created the cluster.
   2. In the list of services, select Managed Service for PostgreSQL.
   3. Select the my-pg-database cluster.
   4. Click in the line with the desired DB.
   5. Click Connect.
   6. On the Shell tab, find a sample connection string.
   7. Move the values of the host, port, dbname, and user variables to the appropriate Value field of the function environment variables.

8. Click Create version.

Create a trigger for Yandex IoT Core

The trigger will accept copies of messages from the device topic and pass them to the function for processing.

1. In the management console, select the folder where you want to create a trigger.

2. In the list of services, select Cloud Functions.

3. Go to the Triggers tab.

4. Click Create trigger.

5. Under Basic parameters:

   • In the Name field, enter a name for the trigger.
   • In the Description field, enter a description for the trigger.
   • In the Type field, select Yandex IoT Core.

6. Under Yandex IoT Core message settings:

   • In the Registry field, enter my-registry.

   • In the Device field, enter my-device.

   • In the Topic field, specify the topic that the device sends data to:

     $devices/<device id>/events
     

     Where <device id> is the ID of your device.

     The trigger works when new data appears in the specified topic.

7. Under Function settings:

   • Select the data processing function that you created earlier.
   • In the Tag version field, specify $latest.
   • In the Service account field, specify my-db-function-service-account.

8. Leave the other fields empty or fill them in at your discretion.

9. Click Create trigger.