IBSNOW: Quickstart

Prerequisites

• Install IoT Bridge for Snowflake 
  • 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. Note Chariot MQTT Server ships with IoT Bridge for Snowflake and can also run in trial mode.

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

Summary

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.

When these messages are formatted as Sparkplug Templates, as defined in the Sparkplug Specification, the 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 Chariot MQTT Server implementation which ships with IoT Bridge for Snowflake. However, IBSNOW does work with any MQTT v3.1.1 compliant MQTT Server.

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.

IoT Bridge Setup Assistant

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

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

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.

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

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.

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.

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

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

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