{"id":1469,"date":"2024-11-04T18:03:44","date_gmt":"2024-11-04T16:03:44","guid":{"rendered":"https:\/\/www.rocworks.at\/wordpress\/?p=1469"},"modified":"2024-11-04T20:16:45","modified_gmt":"2024-11-04T18:16:45","slug":"data-from-opc-ua-mqtt-sparkplugb-to-snowflake-database-with-frankenstein-automation-gatewayy","status":"publish","type":"post","link":"https:\/\/www.rocworks.at\/wordpress\/?p=1469","title":{"rendered":"From OPC UA & MQTT\/SparkplugB to Snowflake with Frankenstein Automation-Gateway"},"content":{"rendered":"\n

In that example we take SparkplugB messages from a MQTT Broker, decode it and write it to Snowflake. And we take some OPC UA nodes and write it also to the same table.<\/p>\n\n\n\n

Create a database and a schema for your destination table.<\/p>\n\n\n\n

CREATE OR REPLACE SCHEMA scada;<\/code><\/pre>\n\n\n\n
Create the table for the incoming data:<\/p>\n\n\n\n
CREATE TABLE IF NOT EXISTS scada.gateway (\n  system character varying(1000) NOT NULL,\n  address character varying(1000) NOT NULL,\n  sourcetime timestamp with time zone NOT NULL,\n  servertime timestamp with time zone NOT NULL,\n  numericvalue float,\n  stringvalue text,\n  status character varying(30),\n  CONSTRAINT gateway_pk PRIMARY KEY (system, address, sourcetime)\n  );<\/code><\/pre>\n\n\n\n
Generate a private key:<\/strong><\/p>\n\n\n\n
> openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out snowflake.p8 -nocrypt<\/p>\n\n\n\n
Generate a public key:<\/strong><\/p>\n\n\n\n
> openssl rsa -in snowflake.p8 -pubout -out snowflake.pub<\/p>\n\n\n\n
Set the public key to your user:<\/strong><\/p>\n\n\n\n
> ALTER USER xxxxxx SET RSA_PUBLIC_KEY=’MIIBIjANBgkqh…’; <\/p>\n\n\n\n
Replace MIIBIjANBgkqh… with your public key from the snowflake.pub file (without —–BEGIN PRIVATE KEY—– and without —–END PRIVATE KEY—–)<\/p>\n\n\n\n
Details about creating keys can be found here<\/a><\/p>\n\n\n\n
Prepare the Gateway<\/strong><\/p>\n\n\n\n
Add a Snowflake logger section to the gateways config.yml. In that example we take SparkplugB messages from a MQTT Broker, decode it and write it to Snowflake. And we take some OPC UA nodes and write it also to the same table.<\/p>\n\n\n\n
Drivers:\n  OpcUa:\n    - Id: \"test1\"\n      Enabled: true\n      LogLevel: INFO\n      EndpointUrl: \"opc.tcp:\/\/test.monstermq.com:4840\/server\"\n      UpdateEndpointUrl: true\n      SecurityPolicy: None\n\n  Mqtt:\n    - Id: \"test2\"\n      Enabled: true\n      LogLevel: INFO\n      Host: test.monstermq.com\n      Port: 1883\n      Format: SparkplugB\n\nLoggers:\n  Snowflake:\n    - Id: \"snowflake\"\n      Enabled: true\n      LogLevel: INFO\n      PrivateKeyFile: \"snowflake.p8\"\n      Account: xx00000\n      Url: https:\/\/xx00000.eu-central-1.snowflakecomputing.com:443\n      User: xxxxxx\n      Role: accountadmin\n      Scheme: https\n      Port: 443\n      Database: SCADA\n      Schema: SCADA\n      Table: GATEWAY\n      Logging:\n        - Topic: opc\/test1\/path\/Objects\/Mqtt\/#\n        - Topic: mqtt\/test2\/path\/spBv1.0\/vogler\/DDATA\/+\/#<\/code><\/pre>\n\n\n\n
The Url you can find in the Snowflake web console by going to Admin\/Accounts and then hover over the “Locator” column.<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
Start the Gateway<\/strong><\/p>\n\n\n\n
> git checkout snowflake\n> cd automation-gateway\/source\/app  \n> ..\/gradlew run\n<\/code><\/pre>\n\n\n\n

Note: Using gradlew to start the gateway is not recommended for production. Instead, consider using a Docker image or the files from the build\/distribution for a more robust setup..<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n","protected":false},"excerpt":{"rendered":"
In that example we take SparkplugB messages from a MQTT Broker, decode it and write it to Snowflake. And we take some OPC UA nodes and write it also to the same table. Create a database and a schema for … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1469","post","type-post","status-publish","format-standard","hentry","category-allgemein"],"_links":{"self":[{"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1469","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1469"}],"version-history":[{"count":8,"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1469\/revisions"}],"predecessor-version":[{"id":1481,"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1469\/revisions\/1481"}],"wp:attachment":[{"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1469"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1469"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rocworks.at\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1469"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}