IBSNOW: Azure Quickstart

Prerequisites

• 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
  • Chariot MQTT Server can be installed using this guide. However, any Sparkplug compliant MQTT Server will work. Note Azure IoT Hub is not Sparkplug compliant.

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.  These messages must be formatted as Sparkplug Templates. Sparkplug Templates are defined in the Sparkplug Specification. These Templates are used to create the data in Snowflake automatically with no additional coding or configuration.  Then multiple instances of these Templates generate the Assets and start to populate with real time data sent on change only, thus significantly reducing the amount of data being sent to the cloud.  For further details on Snowflake, refer to the documentation here. For further details on Eclipse Sparkplug, refer to the Eclipse Sparkplug resources.

This Quickstart document covers how IoT Bridge can be used to consume MQTT Sparkplug data and create and update data in Snowflake. This will show how to configure IoT Bridge as well as show how to use Inductive Automation's Ignition platform along with Cirrus Link's MQTT modules to publish device data to an MQTT Server. This data will ultimately be consumed by IoT Bridge to create and update the Snowflake components. This tutorial will use the AWS IoT Core MQTT Server implementation. However, IBSNOW does work with any MQTT v3.1.1 compliant MQTT Server including Cirrus Link's MQTT Servers.

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.

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

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
SQL Script 01  Expand source

After pasting the code into the center pane of the SQL Worksheet, click the drop down arrow next to the blue play button in the upper right corner of the window and click 'Run All' as shown below.

After doing so, you should see a message in the 'Results' pane denoting the SPARKPLUG_RAW table was created successfully as shown below.

Now, repeat the process for each of the following scripts in order. Each time, fully replace the contents of the SQL script with the new script and click the 'Run All' button after pasting each script. Make sure no errors are displayed in the Results window after running each script.

• Script 02
  SQL Script 02  Expand source

  • Expected Result: Stream NBIRTH_STREAM successfully created.

• Script 03
  SQL Script 03  Expand source

  • Expected Result: Function GENERATE_DEVICE_ASOF_VIEW_DDL successfully created.

• Script 04
  SQL Script 04  Expand source

  • Expected Result: Function CREATE_EDGE_NODE_SCHEMA successfully created.

• Script 05
  SQL Script 05  Expand source

  • Expected Result: Function CREATE_ALL_EDGE_NODE_SCHEMAS successfully created.

• Script 10
  SQL Script 10  Expand source

  • Expected Result: Statement executed successfully.

• Script 11
  SQL Script 11  Expand source

  • Expected Result: Statement executed successfully.

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.

In the bottom of the center 'Granted Roles' pane you will see this user has no roles. Click 'Grant Role' to set up a new role. Then, select the 'CL_BRIDGE_PROCESS_RL' role and click 'Grant' as shown below.

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

Now a 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 details on how to generate this and assign this to a user in your snowflake account: https://docs.snowflake.com/en/user-guide/key-pair-auth. Step 6 (Configuring the Snowflake Client to User Key Pair Authentication) in the linked tutorial can be skipped. This tutorial will cover configuring IoT Bridge for Snowflake with the generated key. Attach the public key to the user that we just created for Snowflake ingest purposes.

The generated key MUST NOT be encrypted

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.

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
• 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
• 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 'cl_bridge_ingest_wh' based on the scripts we previously used to provision Snowflake

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

Now, modify the file /opt/ibsnow/conf/snowflake_streaming_profile.json file. Set the following:

• user
  
  • This must be 'IBSNOW_INGEST' based on the user we provisioned in Snowflake earlier in this tutorial
• url
  • Replace 'ACCOUNT_ID' with your Snowflake account id. Leave the other parts of the URL the same.
• account
  • Replace 'ACCOUNT_ID' with your Snowflake account id
• private_key
  • Replace with the text string that is the private key you generated earlier in this tutorial
• host
  • Replace 'ACCOUNT_ID' with your Snowflake account id. Leave the other parts of the hostname the same.
• schema
  • Set this to 'stage_db' based on the scripts we previously used to provision Snowflake
• database
  • Set this to 'cl_bridge_stage_db' based on the scripts we previously used to provision Snowflake
• connect_string
  • Replace 'ACCOUNT_ID' with your Snowflake account id. Leave the other parts of the connection string the same.
• warehouse
  • Set this to 'cl_bridge_ingest_wh' based on the scripts we previously used to provision Snowflake
• role
  • Set this to 'cl_bridge_process_rl' based on the scripts we previously used to provision Snowflake

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

Now the service can be restarted to pick up the new configuration. Do so by running the following command.

At this point, IBSNOW should connect to the MQTT Server and be ready to receive MQTT Sparkplug messages. Verify by running the following command.

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.

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://docs.inductiveautomation.com/display/DOC80/Installing+and+Upgrading+Ignition

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://docs.inductiveautomation.com/display/DOC80/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.

Now launch the Ignition Designer using the Designer Launcher as shown below.

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.

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.

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

This will open the following dialog box.

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.

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

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

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

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.

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.

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

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

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 Core. 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 select the 'Transmitters' tab 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.

Now upload the three certificate files that were acquired during the AWS IoT thing provisioning. Upload all three to the MQTT Transmission configuration. When done, you should see something similar to what is shown below.

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
  • AWS IoT
• URL
  • Your AWS IoT Endpoint URL of the form ssl://ENDPOINT_URL:8883
• CA Certificate File
  • The AWS Root CA certificate
• Client Certificate File
  • The AWS client certificate for your provisioned 'thing'
• Client Private Key File
  • The AWS client private key for your provisioned 'thing'

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

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

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

Additional Resources

• Snowflake
• IoT Bridge for Snowflake configuration
• Cirrus Link MQTT Servers
• IoT Bridge for Snowflake FAQ
• Ignition
• MQTT Modules for Ignition