Writing data from a device to Managed Service for PostgreSQL

1. In the management console, select the folder to create a service account in.
2. At the top of the screen, go to the Service accounts tab.
3. Click Create service account.
4. Enter a name for the service account: my-db-function-service-account.
5. Click Add role and select the functions.functionInvoker and editor roles.
6. Click Create.

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

2. In the list of services, select IoT Core.

3. Click Create registry.

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

5. Enter the password.

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

   Warning

   Save your password as you will not be able to read it from Yandex IoT Core.

6. Click Create.

Once a registry is created, you will see its settings.

1. Go to the Devices tab.

2. Click Add device.

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

4. Enter the password.

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

   Warning

   Save your password as you will not be able to read it from Yandex IoT Core.

5. Click Create.

1. In the management console, select the folder where you want to create a DB 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.medium class.

   Warning

   The b2.medium class was selected for testing only. In real projects, we do not recommend using hosts with a guaranteed vCPU share less than 100%.

8. Under Size of storage:

   • Select 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. It must be unique within the folder and contain only Latin letters, numbers, and underscores.
   • DB owner username. It may only contain Latin letters, numbers, and underscores.
   • User password. It must be from 8 to 128 characters long.

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

10. Under Hosts, click and enable the Public access option.

11. Under Advanced settings, enable the Access from the management console option.

12. Click Create cluster.

1. Select the my-pg-database cluster.
2. Go to the SQL tab.
3. In the Database 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. Click Connect.

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 Execute.

Wait until a message confirms that the query is complete.

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.

1. Under Function code:

   • In the Runtime environment field, select python37.
   • In the Method field, keep the default value, Code editor.

2. Create a file named myfunction.py.

3. In the file editing area, add 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
   • Memory: 128 MB

6. In the Service account field, select my-db-function-service-account.

7. Add environment variables:

   • VERBOSE_LOG: Parameter displaying detailed information about the function. Enter True.
   • DB_HOSTNAME: Name of the PostgreSQL database 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 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. In the line with the database you need, click the icon and select Connect.
   5. On the PowerShell tab, find a sample connection string.
   6. Move the values of the host, port, dbname, and user variables to the appropriate Value field for the function environment variables.

8. Click Save changes.

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. In the left-hand panel, select Triggers.

4. Click Create trigger.

5. Under Basic settings:

   • 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 IoT Core (device).

6. Under IoT Core message settings:

   • In the Registry field, enter my-registry.

   • In the Device field, enter my-device.

   • In the MQTT 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 will fire as soon as new data appears in the specified topic.

7. Under Function settings:

   • Select the data processing function that you created earlier.
   • In the Function version tag 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.