Versions Compared

Old Version 3

changes.mady.by.user Wes Johnson

Saved on

compared with

New Version Current

changes.mady.by.user Segan Epton

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Warning
titleIoT Bridge for Snowflake v1.x and v2.0.0 x Compatibility
The IoT Bridge for Snowflake v3.0.0 is now shipped as a component of Chariot. If you are using an older version of IoT Bridge for Snowflake, you will need to continue to refer to documentation here.

...

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

...

It is also important to note that Ignition in conjunction with Cirrus Link's MQTT Transmission module converts Ignition User Defined Types (UDTs) to Sparkplug Templates. This is done automatically by the MQTT Transmission module. So, much of this document will refer to UDTs rather than Sparkplug Templates since that is what they are in Ignition. More information on Inductive Automation's Ignition platform can be found here. Additional information on Cirrus Link's MQTT Transmission module can be found here.

Snowflake Setup

If you don't have a Snowflake account, open a Web Browser and go to https://www.snowflake.com. Follow the instructions there to start a free trial. After creating an account, log in to Snowflake via the Web Console. You should see something like what is shown below.

Image Added


IoT Bridge Setup Assistant

Find the IoT Bridge Setup Assistant Snowflake application in the Snowflake Marketplace.

Image Added

Click into the offering and click the Request button to submit a request for the IoT Bridge Setup Assistant application. 

Image Added

Cirrus Link will typically approved the request within 24 hours. Once the request has been fulfilled, the IoT Bridge Setup Assistant application will be visible in Data Products → Apps. Click the 'Get' button download and install the application.

Image Added

After completing this step, the IoT Bridge Setup Assistant is installed as shown below.

Image Added


The IoT Bridge Setup Assistant application will aid in creating and configuring all required Snowflake databases, stored procedures, user defined functions, roles, warehouses, etc. To run the application, simply click on the installed IoT Bridge Setup Assistant application and follow the steps shown.

Image Added

Navigate to 'Setup Step 1' to accept default configuration as it defined or customize the configuration as desired. Note the cl_bridge_reader_warehouse must already exist and the cl_bridge_snowflake_user_public_key must contain the public key created following the steps below.

Generate an unencrypted key pair and copy the public key contents into the cl_bridge_snowflake_user_public_key text area. This will be used for authentication by the IoT Bridge for Snowflake application to push data to Snowflake via the Snowflake Streaming API.

Tip

See this document for details on how to generate this unencrypted key pair: https://docs.snowflake.com/en/user-guide/key-pair-auth. The IoT Bridge Setup Assistant will create the required IoT Ingest user and assign the public key provided to this user.

Note: The step "Configuring the Snowflake Client to User Key Pair Authentication" in the linked tutorial can be skipped.

Warning
The generated key MUST NOT be encrypted


Image Added

When 'Setup Step 1' configuration is finalized, click the 'Setup Environment' button and wait for the step to complete. Once complete, navigate to 'Setup Step 2'. This step will output a custom Snowflake SQL script with the configuration provided in Step 1 and this script will create all required Snowflake artifacts when executed. To run this script, follow the instructions in 'Setup Step 2'.

Image Added


Once Step 2 is complete by running the script generated in this step in a worksheet, the application will display the following message.

Image Added

Confirm your Snowflake resources (databases, stored procedures, user defined functions, roles, warehouses, etc) have been successfully created by the script execution.

Image Added

IoT Bridge Setup

Log into the Chariot Web UI and navigate to 'Certificates' under IOT Bridge for Snowflake as shown below:

Image Added

Click 'UPLOAD FILE' and upload the private key previously generated (rsa_key.p8). Confirm it is uploaded as shown below:

Image Added

Now navigate to 'Servers' under IOT Bridge for Snowflake as shown below:

Image AddedNow click 'Add Server' which will bring up the following dialog:

Image Added


Change the following fields leaving all others default:

  • Name = Chariot MQTT Server
  • Username = admin
  • Password = changeme
    • If you have changed the password of the Chariot MQTT Server username, use whatever you set it to
  • URL = tcp://localhost:1883
    • Note we're using a local non-secure connection for this tutorial. If this were a production system connecting to a remote MQTT Server, TLS should be used.
  • Sparkplug Client ID = IBSNOW-testing-client
  • Verify Hostname = false

Save the configuration and confirm a new Server appears in the list as shown below:

Image Added

Now navigate to 'IoT Bridge for Snowflake → Configuration' on the left navigation panel as shown below:

Image Added

Modify the following properties leaving all others default:

  • General → Snowflake Sparkplug MQTT Application Enabled = true
  • General → Primary Host = IamHost
  • Notify → Notify Database Name = cl_bridge_node_demo_db
  • Notify → Notify Warehouse Name = cl_bridge_ingest_demo_wh
  • Streaming Profile → User = IBSNOW_DEMO_INGEST
  • Streaming Profile → URL = https://ACCOUNT_ID.snowflakecomputing.com:443
    • Replace ACCOUNT_ID with your Snowflake account ID
  • Streaming Profile → Account = ACCOUNT_ID
    • Replace ACCOUNT_ID with your Snowflake account ID
  • Streaming Profile → Private Key = PRIVATE_KEY_FILE_NAME
    • Replace PRIVATE_KEY_FILE_NAME with the filename of your private key file (i.e. rsa_key.p8)
  • Streaming Profile → Host = ACCOUNT_ID.snowflakecomputing.com
    • Replace ACCOUNT_ID with your Snowflake account ID
  • Streaming Profile → Database = cl_bridge_stage_demo_db
  • Streaming Profile → Connect String = jdbc:snowflake://ACCOUNT_ID.snowflakecomputing.com:443
    • Replace ACCOUNT_ID with your Snowflake account ID
  • Streaming Profile → Warehouse = cl_bridge_ingest_DEMO_wh
  • Streaming Profile → Role = cl_bridge_process_DEMO_rl

Once you've made these changes, save the configuration and navigate to the 'Status' tab under IoT Bridge for Snowflake.

At this point, make sure the trial license is running or you've purchased a license for the system. Also, ensure you've restarted IoT Bridge for Snowflake. If you have not done so, the 'RESTART SNOWFLAKE' banner will be present as shown below.

Image Added


Once the trial is running and the configuration is saved, you should see the IoT Bridge for Snowfalke is connected to the Chariot MQTT Server as shown below.

Image Added


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.

Once it is launched, navigate to the 'default' tag provider in the Tag Browser, expand the tag tree to see the automatically created tags as shown below and delete tags Example Tag and MQTT Quickstart.Image Added

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


Review the Ignition Exporting and Importing Tags document if needed

You can view the imported UDT Definition and instance in the tag browser:

Image AddedImage Added

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.

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

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 Definitions' option. Leave it selected as shown below.Image 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
    • To connect Transmission over SSL/TLS, set it up in Chariot according to this document. 
    • Alternatively, you may connect over TCP using the form tcp://ENDPOINT_URL:1883, though this should not be used in production over the public internet. 
  • Username
    • Your username for the Chariot MQTT Server connection
    • If using Chariot MQTT Server, 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 Added

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 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.


Successful Insert

Code Block
languagebash
titleSuccessful Insert
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. 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.

Review the IBSNOW: Viewing the data in Snowflake document for details on the DB tables and views available