Writing data from a device to Managed Service for PostgreSQL
In this tutorial, you will configure storing information supplied from a device in a Managed Service for PostgreSQL cluster database. To connect your device to Yandex IoT Core, you will need an MQTT broker. You will perform all the steps in the management console
To start writing information from your device to the database:
- Prepare your cloud.
- Create a service account.
- Create the required 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.
If you no longer need the resources you created, delete them.
Prepare your cloud
Sign up for Yandex Cloud and create a billing account:
- Go to the management console
and log in to Yandex Cloud or create an account if you do not have one yet. - On the Yandex Cloud Billing
page, make sure you have a billing account linked and it has theACTIVE
orTRIAL_ACTIVE
status. If you do not have a billing account, create one.
If you have an active billing account, you can go to the cloud page
Learn more about clouds and folders.
Required paid resources
The infrastructure support costs include:
- Fee for a PostgreSQL DB cluster (see Yandex Managed Service for PostgreSQL pricing).
- Fee for messaging between Yandex IoT Core resources (see Yandex IoT Core pricing).
- Fee for function calls and computing resources allocated to execute the functions (see Yandex Cloud Functions pricing).
Create a service account
- In the management console
, select the folder where you want to create a service account. - In the list of services, select Identity and Access Management.
- Click Create service account.
- Enter a name for the service account:
my-db-function-service-account
. - Click
Add role and select thefunctions.functionInvoker
andeditor
roles. - Click Create.
Create the required Yandex IoT Core resources
Note
This tutorial assumes username and password-based authorization, so you do not need to add any certificate to your registry and 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. -
In the list of services, select IoT Core.
-
Click Create registry.
-
In the Name field, enter
my-registry
. -
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.
-
Click Create.
Create a device
Once a registry is created, you will see its settings.
-
Go to the Devices tab.
-
Click Add device.
-
In the Name field, enter
my-device
. -
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.
-
Click Create.
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 DB 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.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%. - Specify the
-
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.
- Select
-
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
andLC_COLLATE=C
. You cannot change these settings after the database is created, but you can create a new database with the right settings. -
Under Hosts, click
and enable the Public access option. -
Under Advanced settings, enable the Access from the management console option.
-
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 will be automatically redirected to the Clusters page.
Wait for the cluster status to change to Alive
.
- Select the
my-pg-database
cluster. - Go to the SQL tab.
- In the Database 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.
- Click Connect.
Create a table
As a sample data source, the scenario uses an air sensor that measures the following parameters:
- Humidity
- Carbon dioxide (CO2) level
- Pressure
- Temperature
The sensor outputs the result in JSON format, e.g.:
{
"DeviceId":"0e3ce1d0-1504-4325-972f-55c9********",
"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 Execute.
Wait until a message confirms that the query is complete.
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 a function version
After creating the function, you will be automatically redirected to the Editor page.
-
Under Function code:
- In the Runtime environment field, select
python37
. - In the Method field, keep the default value, Code editor.
- In the Runtime environment field, select
-
Create a file named
myfunction.py
. -
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. -
In the Entry point field, specify
myfunction.msgHandler
. -
Specify the following version parameters:
- Timeout, sec:
10
- Memory:
128 MB
- Timeout, sec:
-
In the Service account field, select
my-db-function-service-account
. -
Add these environment variables:
VERBOSE_LOG
: Parameter displaying detailed information about the function. Type inTrue
.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:
- 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. - In the line with the database you need, click
and select Connect. - On the PowerShell tab, find a sample connection string.
- Move the values of the
host
,port
,dbname
, anduser
variables to the appropriate Value field for the function environment variables.
-
Click Save changes.
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.
-
In the left-hand panel, select
Triggers. -
Click Create trigger.
-
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).
-
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.
-
-
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
.
-
Leave the other fields empty or fill them in at your discretion.
-
Click Create trigger.
How to delete the resources you created
To shut down the infrastructure and stop paying for the resources you created: