Page History

...

• Install IoT Bridge for Snowflake into your Azure account
  • Before being able to access the Virtual Machine you must have completed the installation process here.

• Install an MQTT Server configured with a real signed TLS certificate

  
  

  Tip
  This quickstart guide uses the Chariot MQTT Server which can be installed as a free trial from the Azure Marketplace.

   

  Review the Chariot MQTT Server Configuration for details on how to upload the necessary certificates and keys for enabling SSL/TLS

  
  

  Note

  If you choose not to use Chariot MQTT Server, any Sparkplug compliant MQTT Server will work. Warning Azure IoT Hub is not Sparkplug compliant Warning AWS IoT Core has a message size limit of 128KB and will disconnect the client if it receives a message that exceeds this limit. If you have a large number of UDT definitions/instances and/or have very large UDTs, you will very likely hit this limit when sending your UDTs to AWS IoT Core. Review this document for ways to reduce the message size.

  
  

  
  

  
  

Summary

IoT Bridge for Snowflake (IBSNOW) is an application that connects to an MQTT Server (such as Chariot MQTT Server) and consumes MQTT Sparkplug messages from Edge devices.

...

After all of the scripts have successfully executed, create a new user in Snowflake. This user will be used by IoT Bridge for Snowflake to push data into Snowflake. In the Snowflake Web UI, go to Admin → Users & Roles and then click '+ User' in the upper right hand corner. Give it a username of your choice and a secure password as shown below. For this example we're calling the user IBSNOW_INGEST so we know this user is for ingest purposes. See below for an example and then click 'Create User'.

In addition, the user must have a specific role to be able to stream data into Snowflake. Click the newly created user to see the following.

...

After this has been done successfully you will see the role now associated with the new user as shown below.

Now a an unencrypted key pair must be generated and uploaded to Snowflake. This will be used for authentication by the IoT Bridge for Snowflake application to push data to Snowflake via the Snowflake Streaming API. See this document for

Attach the generated unencrypted public key to the IBSNOW_INGEST user that we just created for Snowflake ingest purposes.

...

...

...

IoT Bridge Setup

First you will need access to the Azure Virtual Machine via SSH. See this document for information on how to access the VM.

Configuring the Snowflake properties

Modify the file /opt/ibsnow/conf/ibsnow.properties file. Set the following:

• mqtt_server_url
  • This is the MQTT Server endpoint URL. It must be of the form: ssl://ENDPOINT_URL:8883
• mqtt_server_name
  • Give it a meaningful name if desired
• mqtt_username
  • The username for the MQTT connection if required
  • If using Chariot MQTT Server, the default username is 'admin'
• mqtt_mqtt_password
  • The password for the MQTT connection if required
•  mqtt_ca_cert_chain_path
  • The path to the Root Certificate if required
• mqtt_client_cert_path
  • The path to the Client Certificate if required
• mqtt_client_private_key_path
  • The path to the Client Private Key if required
• primary_host_id
  • Set it to a text string such as 'IamHost'
• snowflake_streaming_client_name
  • Some text string such as 'MY_CLIENT'
• snowflake_streaming_table_name
  • This must be 'SPARKPLUG_RAW' based on the scripts we previously used to provision Snowflake
• snowflake_notify_db_name
  • This must be 'cl_bridge_node_db' based on the scripts we previously used to provision Snowflake
• • If using Chariot MQTT Server, the default password is 'changeme'
• primary_host_id
  • Set it to a text string such as 'IamHost'
• snowflake_streaming_client_name
  • Some text string such as 'MY_CLIENT'
• snowflake_streaming_table_name
  • This is the staged_sparkplug_raw_table created by the Snowflake setup in SQL Script 02
  • If the default Snowflake setup scripts were used, this is 'SPARKPLUG_RAW'
• snowflake_notify_db_name
  • This is the cl_bridge_node_db created by the Snowflake setup in SQL Script 06
  • If the default Snowflake setup scripts were used, this is 'cl_bridge_node_db'
• snowflake_notify_schema_name
  • This is the stage_db created by the Snowflake setup in SQL Script 06
  • If the default Snowflake setup scripts were used, this is 'stage_db'
  snowflake_notify_schema_name
  • This must be 'stage_db' based on the scripts we previously used to provision Snowflake
• snowflake_notify_warehouse_name
  • This must be 'is the cl_bridge_ingest_wh ' based on the scripts we previously used to provision Snowflakecreated by the Snowflake setup in SQL Script 07
  • If the default Snowflake setup scripts were used, this is 'cl_bridge_ingest_wh'

When complete, it should look similar to what is shown below.ibsnow.properties

...

Configuring the snowflake streaming profile

Now, modify the file /opt/ibsnow/conf/snowflake_streaming_profile.json file as described in Setting snowflake_streaming_

Configuring the snowflake streaming profile

Now, modify the file /opt/ibsnow/conf/snowflake_streaming_profile.json file as described in Setting snowflake_streaming_profile configuration

When complete, it should look similar to what is shown below.

...

After doing so, you should see something similar to what is shown below. Note the last line is 'MQTT Client connected to ...'. That denotes we have successfully configured IBSNOW and properly provisioned MQTT Server.

...

is shown below. Note the last line is 'MQTT Client connected to ...'. That denotes we have successfully configured IBSNOW and properly provisioned MQTT Server.

INFO|7263/0||23-06-29 20:19:

...

32|20:19:

...

32.932 [Thread-2] INFO  org.eclipse.tahu.mqtt.TahuClient - IBSNOW-8bc00095-9265-41

...

: Creating the MQTT Client to ssl://54.236.16.39:8883 on thread Thread-2
INFO|7263/0||23-06-29 20:19:33|20:19:33.

...

275 [MQTT Call: IBSNOW-8bc00095-9265-41] INFO  org.eclipse.tahu.mqtt.TahuClient - IBSNOW-8bc00095-

...

9265-41: connect with retry succeeded
INFO|7263/0||23-06-29 20:19:33|20:19:33.

...

280 [MQTT Call: IBSNOW-8bc00095-9265-41] INFO

...

  org.eclipse.tahu.

...

mqtt.TahuClient - IBSNOW-8bc00095-9265-41: Connected to ssl://54.236.16.39:8883
INFO|7263/0||23-06-29 20:19:33|20:19:33.

...

294 [MQTT Call: IBSNOW-8bc00095-9265-41] INFO  o.eclipse.tahu.host.TahuHostCallback - This is a offline STATE message from IamHost - correcting with new online STATE message

...

FINEST|7263/0||23-06-29 20:19:33|20:19:33.

...

297 [MQTT Call: IBSNOW-8bc00095-9265-41

...

] INFO  o.eclipse.tahu.host.TahuHostCallback - This is a offline STATE message from IamHost - correcting with new online STATE message
FINEST|7263/0||23-06-29 20:19:33|20:19:33.957 [Thread-2] INFO  org.eclipse.tahu.mqtt.TahuClient - IBSNOW-8bc00095-9265-41: MQTT Client connected to ssl://54.236.16.39:8883 on thread Thread-2

Edge Setup with Ignition and MQTT Transmission

Install Ignition and MQTT Transmission module

At this point IoT Bridge is configured and ready to receive data. To get data flowing into IBSNOW we'll set up Inductive Automation's Ignition platform along with the MQTT Transmission module from Cirrus Link.

Installation of Ignition is very straightforward following the instructions in the Installing and Upgrading Ignition guide.

With Ignition installed, the Cirrus Link MQTT Transmission module must be installed as a plugin to Ignition. Follow the instructions in our Module Installation guide

Import UDTs and tags

Launch the Ignition Designer to connect to your Ignition instance.

Edge Setup with Ignition and MQTT Transmission

At this point IoT Bridge is configured and ready to receive data. To get data flowing into IBSNOW we'll set up Inductive Automation's Ignition platform along with the MQTT Transmission module from Cirrus Link. Begin by downloading Ignition here.

https://inductiveautomation.com/downloads

Installation of Ignition is very straightforward and fast. There is a guide to do so here.

https://www.docs.inductiveautomation.com/docs/8.1/getting-started/installing-and-upgrading

With Ignition installed, MQTT Transmission must be installed as well as a plugin to Ignition. Get MQTT Transmission for your version of Ignition here.

https://inductiveautomation.com/downloads/third-party-modules

Now use the procedure below to install the MQTT Transmission module.

https://www.docs.inductiveautomation.com/docs/8.1/getting-started/installing-and-upgrading/installing-or-upgrading-a-module

With Ignition and MQTT Transmission installed, we can configure the MQTT Transmission module to connect to Chariot MQTT Server that we provisioned earlier. Begin by clicking 'Get Desginer' in the upper right hand corner of the Ignition Gateway Web UI as shown below.

Image Removed

Now launch the Ignition Designer using the Designer Launcher to connect to your Ignition instance.

...

Once it is launched, navigate to the 'default' tag provider in the Tag Browser. In the image below , expand the tag tree has been expanded to show see the automatically created example tags.

Image Removed

Begin by deleting these two tags (tags as shown below and delete tags Example Tag and MQTT Quickstart.

Image Added

From the Designer ). Then import these tags IBSNOW_Quickstart_tags.json to MQTT Tags > PLC 1 to create a UDT Definition and instance.

...

At this point, our tags are configured. A UDT definition will map to a model in Snowflake and UDT instances in Ignition will map to Snowflake.

But, before this will happen we need to point MQTT Transmission to the Chariot MQTT Server. To do so, browse back to the Ignition Gateway Web UI and select MQTT Transmission → Settings from the left navigation panel as shown below.

Image Removed

Now select Select the 'Transmitters' tab as shown below.Image Added

Image Removed

Now click the 'edit' button to the right of the 'Example Transmitter'. Scroll down to the 'Convert UDTs' option and uncheck it as shown below. This will also un-grey the 'Publish UDT DefintionsDefinitions' option. Leave it selected as shown below.Image RemovedImage Added

Now switch to the 'Servers' and 'Settings' tab. Delete the existing 'Chariot SCADA' pre-seeded MQTT Server Definition. Then create a new one with the following configuration.

• Name
  • Chariot MQTT Server
• URL
  • Your MQTT Server Endpoint URL of the form ssl://ENDPOINT_URL:8883
• Username
  
  • Your username for the for the Chariot MQTT Server connection
  • If using Chariot MQTT Server connection, the default username is 'admin'
• Password
  • Your password for the Chariot MQTT Server connection
  • If using Chariot MQTT Server, the default password is 'changeme'

When complete, you should see something similar to the following. However, the 'Connected' state should show '1 of 1' if everything was configured properly.

Image AddedImage Removed

At this point, data should be flowing into Snowflake.

By tailing the log in IBSNOW you should see something similar to what is shown below . This which shows IBSNOW receiving the messages published from Ignition/MQTT Transmission.

When IBSNOW receives the Sparkplug MQTT messages, it creates and updates asset models and assets in Snowflake. The log below is also a useful debugging tool if things don't appear to work as they should.

FINEST|199857/0||23-04-21 15:46:22|15:46:22.951 [TahuHostCallback--3deac7a5]

...

 INFO  o.e.tahu.host.TahuPayloadHandler - Handling NBIRTH from My MQTT Group/Edge Node ee38b1

...

FINEST|199857/0||23-04-21 15:46:22|15:46:22.953 [TahuHostCallback--3deac7a5]

...

 INFO  o.e.t.host.manager.SparkplugEdgeNode - Edge Node My MQTT Group/Edge Node ee38b1 set online at Fri Apr 21 15:46:22 UTC 2023

...

FINEST|199857/0||23-04-21 15:46:23|15:46:23.072 [TahuHostCallback--3deac7a5]

...

 INFO  o.e.tahu.host.TahuPayloadHandler - Handling DBIRTH from My MQTT Group/Edge Node ee38b1/PLC 1

...

FINEST|199857/0||23-04-21 15:46:23|15:46:23.075 [TahuHostCallback--3deac7a5]

...

 INFO  o.e.t.host.manager.SparkplugDevice - Device My MQTT Group/Edge Node ee38b1/PLC 1 set online at Fri Apr 21 15:46:22 UTC 2023

...

FINEST|199857/0||23-04-21 15:46:23|15:46:23.759 [ingest-flush-thread]

...

 INFO  n.s.i.s.internal.FlushService - [SF_INGEST] buildAndUpload task added for client=MY_CLIENT, blob=2023/4/21/15/46/rth2hb_eSKU3AAtxudYKnPFztPjrokzP29ZXzv5JFbbj0YUnqUUCC_1049_48_1.bdec, buildUploadWorkers stats=java.util.concurrent.ThreadPoolExecutor@32321763[Running, pool size = 2, active threads = 1, queued tasks = 0, completed tasks = 1]

...

FINEST|199857/0||23-04-21 15:46:23|15:46:23.774 [ingest-build-upload-thread-1]

...

 INFO  n.s.i.i.a.h.io.compress.CodecPool - Got brand-new compressor [.gz]

...

FINEST|199857/0||23-04-21 15:46:23|15:46:23.822 [ingest-build-upload-thread-1]

...

 INFO  n.s.i.streaming.internal.BlobBuilder - [SF_INGEST] Finish building chunk in blob=2023/4/21/15/46/rth2hb_eSKU3AAtxudYKnPFztPjrokzP29ZXzv5JFbbj0YUnqUUCC_1049_48_1.bdec, table=CL_BRIDGE_STAGE_DB.STAGE_DB.SPARKPLUG_RAW, rowCount=2, startOffset=0, uncompressedSize=5888, compressedChunkLength=5872, encryptedCompressedSize=5888, bdecVersion=THREE

...

FINEST|199857/0||23-04-21 15:46:23|15:46:23.839 [ingest-build-upload-thread-1]

...

 INFO  n.s.i.s.internal.FlushService - [SF_INGEST] Start uploading file=2023/4/21/15/46/rth2hb_eSKU3AAtxudYKnPFztPjrokzP29ZXzv5JFbbj0YUnqUUCC_1049_48_1.bdec, size=5888

...

FINEST|199857/0||23-04-21 15:46:24|15:46:24.132 [ingest-build-upload-thread-1]

...

 INFO  n.s.i.s.internal.FlushService - [SF_INGEST] Finish uploading file=2023/4/21/15/46/rth2hb_eSKU3AAtxudYKnPFztPjrokzP29ZXzv5JFbbj0YUnqUUCC_1049_48_1.bdec, size=5888, timeInMillis=292

...

FINEST|199857/0||23-04-21 15:46:24|15:46:24.148 [ingest-register-thread]

...

 INFO  n.s.i.s.internal.RegisterService - [SF_INGEST] Start registering blobs in client=MY_CLIENT, totalBlobListSize=1, currentBlobListSize=1, idx=1

...

FINEST|199857/0||23-04-21 15:46:24|15:46:24.148 [ingest-register-thread]

...

 INFO  n.s.i.s.i.SnowflakeStreamingIngestClientInternal - [SF_INGEST] Register blob request preparing for blob=[2023/4/21/15/46/rth2hb_eSKU3AAtxudYKnPFztPjrokzP29ZXzv5JFbbj0YUnqUUCC_1049_48_1.bdec], client=MY_CLIENT, executionCount=0

...

FINEST|199857/0||23-04-21 15:46:24|15:46:24.301 [ingest-register-thread]

...

 INFO  n.s.i.s.i.SnowflakeStreamingIngestClientInternal - [SF_INGEST] Register blob request returned for blob=[2023/4/21/15/46/rth2hb_eSKU3AAtxudYKnPFztPjrokzP29ZXzv5JFbbj0YUnqUUCC_1049_48_1.bdec], client=MY_CLIENT, executionCount=0

Data will also be visible in Snowflake at this point. See below for an example. By changing data values in the UDT tags in Ignition DDATA Sparkplug messages will be produced. Every time the Edge Node connects, it will produce NBIRTH and DBIRTH messages. All of these will now appear in Snowflake with their values, timestamps, and qualities

...