Page History

...

...

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

  
  

  
  

  
  

...

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.

Image Removed

Copy and paste SQL Script 01 from Snowflake Setup Scripts 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.

Image Removed

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

Image Removed

Now, repeat the process for each of the following scripts in the Snowflake Setup 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.

• SQL Script 02 Expected Result: Stream NBIRTH_STREAM successfully created.

• SQL Script 03 Expected Result: Function GENERATE_DEVICE_ASOF_VIEW_DDL successfully created.

• SQL Script 04 Expected Result: Function CREATE_EDGE_NODE_SCHEMA successfully created.

• SQL Script 05 Expected Result: Function CREATE_ALL_EDGE_NODE_SCHEMAS successfully created.

• SQL Script 06 Expected Result: Statement executed successfully.

• SQL Script 07 Expected Result: Statement executed successfully.

• SQL Script 08 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'.

Image Removed

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.

Image Removed

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.

Image Removed

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

Image Removed

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

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

...

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.

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

See this document for details on how to generate this unencrypted key and assign this to a user in your snowflake account: https://docs.snowflake.com/en/user-guide/key-pair-auth.

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

...

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.

...

• 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 desireda meaningful name if desired

• mqtt_sparkplug_subscriptions

  • Optionally, customize your Sparkplug MQTT subscriptions
• mqtt_subscriptions
  • Optionally, uncomment and customize your non-Sparkplug MQTT subscriptions
• mqtt_username
  • The username for the MQTT connection if required
  • If using Chariot MQTT Server, the default username is 'admin'
• mqtt_password
  • The password for the MQTT connection if required
  • 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 the default Snowflake configuration was 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 configuration was used, this is 'SPARKPLUG_RAWcl_bridge_node_db'
• snowflake_notify_dbschema_name
  • This is the cl_bridge_nodestage_db created by the Snowflake setup in SQL Script 06
  • If the the default Snowflake setup scripts were configuration was used, this is 'cl_bridge_nodestage_db'
• snowflake_notify_schemawarehouse_name
  • This is the stage_db cl_bridge_ingest_wh created by the Snowflake setup in SQL Script 0607
  • If the default Snowflake setup scripts were configuration was used, this is 'stage_db'
• snowflake_notify_warehouse_name
  • This is the cl_bridge_ingest_wh created by the Snowflake setup in SQL Script 07
  • If the default Snowflake setup scripts were used, this is 'cl_bridge_ingest_wh'

...

• • cl_bridge_ingest_wh'

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.

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

...

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.

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

Additional Resources

...