Delivering data from a YDS queue to ClickHouse®

With Data Transfer, you can deliver data from a data stream Data Streams to Managed Service for ClickHouse®.

To transfer data:

1. Prepare a Data Streams data stream.
2. Prepare and activate the transfer.
3. Test the transfer.

If you no longer need the resources you created, delete them.

Getting startedGetting started

Prepare the infrastructure:

Prepare a Data Streams data streamPrepare a Data Streams data stream

1. Create a Data Streams data stream.
2. Send test data to the data stream. Use data from the vehicle sensors in JSON format as a message:

{
    "device_id":"iv9a94th6rzt********",
    "datetime":"2022-06-05 17:27:00",
    "latitude":55.70329032,
    "longitude":37.65472196,
    "altitude":427.5,
    "speed":0,
    "battery_voltage":"23.5",
    "cabin_temperature":17,
    "fuel_level":null
}

Prepare and activate the transferPrepare and activate the transfer

1. Create a source endpoint:

   • Database type: Yandex Data Streams.

   • Endpoint parameters:

     • Connection settings:

       • Database: Select the Managed Service for YDB database from the list.
       • Stream: Specify the name of the Data Streams data stream.
       • Service account: Select or create a service account with the yds.editor role.

     • Advanced settings:

       • Conversion rules: JSON
       • Data scheme: You can specify a schema in two ways:

         • Field list.

           Set a list of topic fields manually:

           Name	Type	Key
           device_id	STRING	Yes
           datetime	DATETIME
           latitude	DOUBLE
           longitude	DOUBLE
           altitude	DOUBLE
           speed	DOUBLE
           battery_voltage	ANY
           cabin_temperature	DOUBLE
           fuel_level	ANY

         • JSON specification.

           Create and upload the json_schema.json file in JSON format:

           json_schema.json

           [
               {
                   "name": "device_id",
                   "type": "string",
                   "key": true
               },
               {
                   "name": "datetime",
                   "type": "datetime"
               },
               {
                   "name": "latitude",
                   "type": "double"
               },
               {
                   "name": "longitude",
                   "type": "double"
               },
               {
                   "name": "altitude",
                   "type": "double"
               },
               {
                   "name": "speed",
                   "type": "double"
               },
               {
                   "name": "battery_voltage",
                   "type": "any"
               },
               {
                   "name": "cabin_temperature",
                   "type": "double"
               },
               {
                   "name": "fuel_level",
                   "type": "any"
               }
           ]
           

2. Create a transfer:

   Manually

   Terraform

   1. Create a transfer of the Replication type that will use the created endpoints.
   2. Activate your transfer.

   1. Specify the variables in the data-transfer-yds-mch.tf file:

      • source_endpoint_id: ID of the source endpoint.
      • transfer_enabled: Set to 1 to enable transfer creation.

   2. Make sure the Terraform configuration files are correct using this command:

      terraform validate
      

      If there are any errors in the configuration files, Terraform will point them out.

   3. Create the required infrastructure:

      1. Run the command to view planned changes:

         terraform plan
         

         If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.

      2. If you are happy with the planned changes, apply them:

         1. Run the command:

            terraform apply
            

         2. Confirm the update of resources.

         3. Wait for the operation to complete.

      Once created, your transfer will be activated automatically.

Test the transferTest the transfer

1. Wait for the transfer status to change to Replicating.

2. Send a new message to the data stream Data Streams:

   {
       "device_id":"rhibbh3y08qm********",
       "datetime":"2022-06-06 09:49:54",
       "latitude":55.71294467,
       "longitude":37.66542005,
       "altitude":429.13,
       "speed":55.5,
       "battery_voltage":null,
       "cabin_temperature":18,
       "fuel_level":32
   }
   

3. Make sure the data from the Data Streams stream has been moved to the Managed Service for ClickHouse® cluster database:

   1. Connect to the Managed Service for ClickHouse® target cluster.
   2. Check that the ClickHouse® database contains a table with the name of the created Data Streams data stream with the same columns as the data schema in the source endpoint and the sent test data.

Delete the resources you createdDelete the resources you created

Some resources are not free of charge. To avoid paying for them, delete the resources you no longer need:

1. Delete the transfer.

2. Delete the resources depending on how they were created:

   Manually

   Terraform

   • Delete the target endpoint.
   • Delete the Managed Service for YDB database.
   • Delete the Managed Service for ClickHouse® cluster.

   1. In the terminal window, go to the directory containing the infrastructure plan.

   2. Delete the data-transfer-yds-mch.tf configuration file.

   3. Make sure the Terraform configuration files are correct using this command:

      terraform validate
      

      If there are any errors in the configuration files, Terraform will point them out.

   4. Confirm updating the resources.

      1. Run the command to view planned changes:

         terraform plan
         

         If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.

      2. If you are happy with the planned changes, apply them:

         1. Run the command:

            terraform apply
            

         2. Confirm the update of resources.

         3. Wait for the operation to complete.

      All the resources described in the configuration file data-transfer-yds-mch.tf will be deleted.

3. Delete the source endpoint.

ClickHouse® is a registered trademark of ClickHouse, Inc.