Page History

Prerequisites

Install IoT Bridge for Snowflake into your AWS account
- Before being able to access the

...

- Virtual Machine you must have completed the installation process here.

...

Install an MQTT Server

Chariot MQTT Server can be installed from the AWS Marketplace.

Note
If you choose not to use Chariot MQTT Server, any Sparkplug compliant MQTT Server will work.

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 or AWS IoT Core) 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

Before configuring IoT Bridge (IBSNOW), you must register a 'thing' connection in AWS IoT Core. Begin by browsing to AWS IoT Core in your AWS account. Make sure you are in the same AWS region that you have already deployed IBSNOW to in the prerequisite step. Begin by expanding 'Secure' and click 'Policies' as shown below.

Image Removed

Now click the 'Create a policy' button. This will bring up the following page.

Image Removed

Set the following parameters for the policy.

...

Some friendly name you will remember

...

Set to the following JSON document but replace the following

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

Create a new 'SQL Worksheet' by clicking the blue + button in the upper right hand corner of the window as shown below.

Image Added

Copy and paste the following SQL script into the center pane. Click the 'Expand source' button on the right to copy the script source code

Code Block

language	sql
title	SQL Script 01
collapse	true

-- =========================
-- In this script, we are setting up assets related to the staging database
-- and associated assets. These are:
--  - Database
--  - Staging schema

-- The database & schema will be owned by SYSADMIN

-- REPLACE THE SESSION VARIABLE ACCORDING TO YOUR ENVIRONMENT
-- =========================

set cl_bridge_staging_db = 'CL_BRIDGE_STAGE_DB';
set staging_schema = 'stage_db';

-- >>>>>>>>>>>>>>>>>>>>>> DATABASE >>>>>>>>>>>>>>>>>>>>>>>>>

use role sysadmin;

create database if not exists identifier($cl_bridge_staging_db)
   -- DATA_RETENTION_TIME_IN_DAYS = 90
   -- MAX_DATA_EXTENSION_TIME_IN_DAYS = 90
   comment = 'used for storing messages received from CirrusLink Bridge'
;

-- >>>>>>>>>>>>>>>>>>>>>> STAGING SCHEMA >>>>>>>>>>>>>>>>>>>>>>>>>

use database identifier($cl_bridge_staging_db);

create schema if not exists identifier($staging_schema)
  with managed access
  -- data_retention_time_in_days = 90
  -- max_data_extension_time_in_days = 90
  comment = 'Used for staging data direct from CirrusLink Bridge';

-- >>>>>>>>>>>>>>>>>>>>>> STAGING SCHEMA ASSETS >>>>>>>>>>>>>>>>>>>>>>>>>

use schema identifier($staging_schema);

-- =========================
-- Define tables
-- =========================

create or replace table sparkplug_raw (
    msg_id varchar
    ,msg_topic varchar
    ,namespace varchar
    ,group_id varchar
    ,message_type varchar

...

ACCOUNT_ID: Your AWS Account ID

Code Block

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "iot:Connect",
        "iot:Publish",
        "iot:Subscribe",
        "iot:Receive",
        "iot:RetainPublish"
      ],
      "Resource": "arn:aws:iot:AWS_REGION:ACCOUNT_ID:*"
    }
  ]
}

Example JSON document with region and account identifier set in the ARN:

Code Block

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "iot:Connect",
        "iot:Publish",
        "iot:Subscribe",
        "iot:Receive",
        "iot:RetainPublish"
      ],
      "Resource": "arn:aws:iot:us-west-2:123456789012:*"
    }
  ]
}

Finally click 'Create' in the lower right. This will show the newly created policy.

Image Removed

At this point a 'thing' can be created. We'll use the policy a bit later in the procedure. To create the thing, expand 'Manage' on the left navigation panel and click 'All devices → Things' as shown below.

Image Removed

Click 'Create things'. This will bring up the page below.

Image Removed

Now click 'Create a single thing'. This will open the following page. Give your thing a name (such as MyEdgeNode) and then click 'Next'.

Image Removed

This will bring up the following page. Click the 'Auto-generate a new certificate (recommended)' option shown below.

Image Removed

This will bring up the page similar to what is shown below. Select the policy that was created earlier in this tutorial also shown below and click 'Create thing'.

Image Removed

This will show the following download page.

Image Removed

At this point, you must download the following files. Do not proceed until you have downloaded them as you will not get another chance.

Device certificate
- This file ends in 'certificate.pem.crt'
Public key file
- This file ends in 'public.pem.key'
Private key file
- This file ends in 'private.pem.key'
Root CA certificate
- Amazon Root CA 1

These will all be used in the IBSNOW configuration to connect to AWS IoT later. These files will not be accessible later and must be downloaded now. In addition, there is a link to download the root CA for AWS IoT. Make sure to download this as well. The 'RSA 2048 bit key: Amazon Root CA 1' is the preferred root certificate to download at the time of this writing.

At this point you should have three files where 'UUID' will be some UUID specific to your thing. Do not proceed until you have at least the following three files.

UUID.certificate.pem.crt
UUID.private.pem.key
AmazonRootCA1.pem

Once you have these, click the 'Done' button. This will bring up the following page showing the new thing.

Image Removed

At this point, a thing has been defined with an associated certificate which also has a policy attached to it.

Now we need to get the AWS IoT Core Endpoint. Do so by selecting 'Settings' from the bottom section of the left navigation panel as shown below.

Image Removed

When selected, your AWS IoT Endpoint will be displayed. Note it for future use when configuring IBSNOW.

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 Removed

Create a new 'SQL Worksheet' by clicking the blue + button in the upper right hand corner of the window as shown below.

Image Removed

Copy and paste the following SQL script into the center pane. Click the 'Expand source' button on the right to copy the script source code

Code Block

language	sql
title	SQL Script 01
collapse	true

-- =========================
-- In this script, we are setting up assets related to the staging database
-- and associated assets. These are:
--  - Database
--  - Staging schema

-- The database & schema will be owned by SYSADMIN

-- REPLACE THE SESSION VARIABLE ACCORDING TO YOUR ENVIRONMENT
-- =========================

set cl_bridge_staging_db = 'CL_BRIDGE_STAGE_DB';
set staging_schema = 'stage_db';

-- >>>>>>>>>>>>>>>>>>>>>> DATABASE >>>>>>>>>>>>>>>>>>>>>>>>>

use role sysadmin;

create database if not exists identifier($cl_bridge_staging_db)
   -- DATA_RETENTION_TIME_IN_DAYS = 90
   -- MAX_DATA_EXTENSION_TIME_IN_DAYS = 90
   comment = 'used for storing messages received from CirrusLink Bridge'
;

-- >>>>>>>>>>>>>>>>>>>>>> STAGING SCHEMA >>>>>>>>>>>>>>>>>>>>>>>>>

use database identifier($cl_bridge_staging_db);

create schema if not exists identifier($staging_schema)
  with managed access
  -- data_retention_time_in_days = 90
  -- max_data_extension_time_in_days = 90
  comment = 'Used for staging data direct from CirrusLink Bridge';

-- >>>>>>>>>>>>>>>>>>>>>> STAGING SCHEMA ASSETS >>>>>>>>>>>>>>>>>>>>>>>>>

use schema identifier($staging_schema);

-- =========================
-- Define tables
-- =========================

create or replace table sparkplug_raw (
    msg_id varchar
    ,msg_topic varchar
    ,namespace varchar
    ,group_id varchar
    ,message_type varchar
    ,edge_node_id varchar
    ,device_id varchar
    ,msg variant
    ,inserted_at number
)
change_tracking = true
cluster by (message_type ,group_id ,edge_node_id ,device_id)
comment = 'Used for storing json messages from sparkplug bridge/gateway'
;

...

Code Block

language	bash

INFO|199857/0||23-04-21 15:29:52|15:29:52.401 [Thread-2] INFO  org.eclipse.tahu.mqtt.TahuClient - IBSNOW-79456ef7-8c90-45: Creating the MQTT Client to ssl://a3een7lsei6n10-ats.iot.us-west-2.amazonaws.com:8883 on thread Thread-2
INFO|199857/0||23-04-21 15:29:55|15:29:55.836 [MQTT Call: IBSNOW-79456ef7-8c90-45] INFO  org.eclipse.tahu.mqtt.TahuClient - IBSNOW-79456ef7-8c90-45: connect with retry succeeded
INFO|199857/0||23-04-21 15:29:55|15:29:5552.839401 [MQTT Call: IBSNOW-79456ef7-8c90-45Thread-2] INFO  org.eclipse.tahu.mqtt.TahuClient - IBSNOW-79456ef7-8c90-45: Connected Creating the MQTT Client to ssl://a3een7lsei6n10-ats.iot.us-west-2.amazonaws.com:8883 on thread Thread-2
INFO|199857/0||23-04-21 15:29:5655|15:29:5655.046836 [Thread-2MQTT Call: IBSNOW-79456ef7-8c90-45] INFO  org.eclipse.tahu.mqtt.TahuClient - IBSNOW-79456ef7-8c90-45: MQTTconnect Clientwith connected to ssl://a3een7lsei6n10-ats.iot.us-west-2.amazonaws.com:8883 on thread Thread-2

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 AWS IoT Core using the same certificates 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 as shown below.

Image Removed

Once it is launched, you should see something similar to what is shown below. Note the Tag Browser has been expanded and the automatically created example tags have been highlighted.

Image Removed

Begin by deleting these two tags (Example Tag and MQTT Quickstart). Then click the 'UDT Definitions' tab as shown below. We will use this view to create a very simple UDT definition.

Image Removed

Now, click the '+' icon in the upper left corner of the tag browser as shown below and select 'New Data Type'

Image Removed

This will open the following dialog box.

Image Removed

Change the name of the tag to Motor as shown below. Also, note the highlighted 'new member tag' icon in the middle of the dialog. We'll use this to create some member tags.

Image Removed

Now use the 'new member tag' button to create a new 'memory tag' as shown below.

Image Removed

Then, set the following parameters for the new memory tag.

Name
- Set to 'Temperature'
Date Type
- Set to 'Float'
Engineering Units
- Set to 'Celsius'

Image Removed

Now create two additional member tags with the following configuration.

Amps
- Memory tag
- Data Type = Integer
RPMs
- Memory tag
- Data Type = Integer

When complete, the UDT definition should look as follows.

Image Removed

Now switch back to the 'Tags' tab of the Tag Browser. Right click on the 'PLC 1' folder and select 'New Tag → Data Type Instance → Motor' as shown below.

Image Removed

Now set the name to 'My Motor' as shown below and click OK.

Image Removed

Now, set some values under the instance as shown below.

...

retry succeeded
INFO|199857/0||23-04-21 15:29:55|15:29:55.839 [MQTT Call: IBSNOW-79456ef7-8c90-45] INFO  org.eclipse.tahu.mqtt.TahuClient - IBSNOW-79456ef7-8c90-45: Connected to ssl://a3een7lsei6n10-ats.iot.us-west-2.amazonaws.com:8883
INFO|199857/0||23-04-21 15:29:56|15:29:56.046 [Thread-2] INFO  org.eclipse.tahu.mqtt.TahuClient - IBSNOW-79456ef7-8c90-45: MQTT Client connected to ssl://a3een7lsei6n10-ats.iot.us-west-2.amazonaws.com:8883 on thread Thread-2

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 Designer' in the upper right hand corner of the Ignition Gateway Web UI as shown below.

Image Added

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

Tip
Review Ignition Designer Launcher for assistance if needed

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

Image Added

Begin by deleting these two tags (Example Tag and MQTT Quickstart). Then import these tags IBSNOW_Quickstart_tags.json to create a UDT Definition and instance.

Tip
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 AWS IoT Corethe 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.

...

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 Defintions' option. Leave it selected as shown below.

Then select the 'Servers' tab and 'Certificates' tab as shown below.

Image Removed

Now

...

Image Removed

...

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
...
- The AWS Root CA certificate
...
- The AWS client certificate for your provisioned 'thing'
- Username
  - Your username for the Chariot MQTT Server connection
- Password
  - Your password for the Chariot MQTT Server connection
...
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. This 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.
...

Contents

Cirrus Link Resources

Inductive Resources

Versions Compared

Old Version 66

New Version 67

Key

Prerequisites

Summary

Snowflake Setup

Snowflake Setup

Edge Setup with Ignition and MQTT Transmission

Edge Setup with Ignition and MQTT Transmission