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:
- Create the necessary Yandex IoT Core resources.
- Connect your device to the MQTT broker.
- Prepare the database.
- Create a function for processing data.
- Create a trigger for Yandex IoT Core.
Create the necessary Yandex IoT Core resources
Note
The scenario is based on authorization using a username and password, so you don't need to add a certificate to your registry or device. In your projects, you can use authorization using certificates.
Create a registry
-
In the management console, select the folder where you want to create a registry.
-
Click Create resource.
-
Choose Device registry.
-
In the Name field, enter
my-registry
. -
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.
-
Skip the Certificates section.
-
Click Create.
Create a device
After creating the registry, you'll be automatically redirected to the Registries page.
-
From the list of registries, select
my-registry
. -
Go to the Devices tab.
-
Click Add device.
-
In the Name field, enter
my-device
. -
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.
-
Skip the Aliases and Certificates sections.
-
Click Add.
Connect your device to the MQTT broker
To connect to the MQTT server, use the following parameters:
- A certificate from the certificate authority.
- Server address:
mqtt.cloud.yandex.net
. - Server port:
8883
. - Protocol:
TLSv1.2
.
Prepare the database
Create a cluster
-
In the management console, select the folder where you want to create a cluster.
-
In the list of services, select Managed Service for PostgreSQL.
-
Click Create cluster.
-
In the Cluster name field, enter
my-pg-database
. -
In the Environment field, select
PRODUCTION
. -
In the Version field, select
12
. -
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%. - Specify the
-
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.
- Choose
-
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
andLC_COLLATE=C
. You can't change these settings after the database is created, but you can create a new database with the right settings. -
Under Hosts, add a host to be accessible from outside of Yandex Cloud. To do this, enable Public access.
-
Leave the default values in the other fields.
-
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.
- Select the
my-pg-database
cluster. - Go to the SQL tab.
- In the DB username field, specify the name of the user who owns the DB created in the previous step.
- In the Password field, enter the password specified when creating the cluster.
- In the Database field, enter the name of the database.
- 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:
-
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 )
-
Click Run.
-
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.
- In the management console, select the folder where you want to create a function.
- In the list of services, select Cloud Functions.
- Click Create function.
- Enter a function name.
- Click Create.
Create the first version of the function
After creating the function, you'll be automatically redirected to the Editor page.
-
Under Code:
- In the Runtime environment field, choose
python37
. - In the Method field, leave the default value: Code editor.
- In the Runtime environment field, choose
-
Create a file named
myfunction.py
. -
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. -
In the Entry point field, specify
myfunction.msgHandler
. -
Specify the following version parameters:
- Timeout, sec: 10.
- RAM: 128 MB.
-
Create a service account:
- Click Create account (or Create new). An additional window opens.
- In the Name field, enter
my-db-function-service-account
. - Add roles:
serverless.functions.invoker
andeditor
. - 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.
-
Add environment variables:
VERBOSE_LOG
: Parameter that displays detailed information about the function execution. Set it toTrue
.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:
- In the management console, select the folder where you created the cluster.
- In the list of services, select Managed Service for PostgreSQL.
- Select the
my-pg-database
cluster. - Click
- Click Connect.
- On the Shell tab, find a sample connection string.
- Move the values of the
host
,port
,dbname
, anduser
variables to the appropriate Value field of the function environment variables.
-
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.
-
In the management console, select the folder where you want to create a trigger.
-
In the list of services, select Cloud Functions.
-
Go to the Triggers tab.
-
Click Create trigger.
-
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.
-
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.
-
-
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
.
-
Leave the other fields empty or fill them in at your discretion.
-
Click Create trigger.