Page History

Data can be view viewed in the databases created by via the Snowflake Setup ScriptsAssistant during your initial setup:

• Staging database created by SQL Script 01 (e.g., CL_BRIDGE_STAGE_DB)
• Node database created by SQL Script 02 (e.g., CL_BRIDGE_NODE_DB)

Image AddedImage Removed

Terminology

As the data moves from the Ignition Edge device through MQTT Sparkplug and into Snowflake different terminology will be used with the equivalence shown below:

Anchor
staging_database staging_database

Staging Database

The Staging database contains only a single table, two tables: SPARKPLUG_RAW. This table contains information derived from each Sparkplug message, like the Sparkplug IDs: GroupId, EdgenodeId and DeviceId, the message timestamp, the model / model instance, the raw message payload in JSON, etc. and MQTT_RAW.

These tables are The table is populated by the Snowflake Snowpipe Streaming API as data is streamed in by the IoT bridge.Image Removed

Anchor
sparkplug_raw sparkplug_raw

SPARKPLUG_RAW

This table contains information derived from each Sparkplug message as detailed below:

• GROUP_ID
• EDGE_NODE_ID
• DEVICE_ID
• IINSERTED_AT
• MESSAGE_TYPE
• MSG
• MSG_ID
• MSG_TOPIC
• NAMESPACE

Image Added

Anchor
mqtt_raw mqtt_raw

MQTT_RAW

This table contains information derived from each MQTT message as detailed below:

• INSERTED_AT
• INSERTED_AT_DATE
• MSG
• MSG_ID
• MSG_TOPIC

Image Added

Anchor
node_database node_database

Node Database

The Node database also contains only a single table, contains five tables: SPARKPLUG_DEVICE_MESSAGES, but also has additional views, user defined functions, store procedures, streams, etc.The SPARKPLUG_DEVICE_REGISTRY, SPARKPLUG_EDGE_NODE_REGISTRY and SPARKPLUG_TEMPLATE DEFINITION_REGISTRY and SPARKPLUG_TEMPLATE_INSTANCE_REGISTRY

Data is sourced from SPARKPLUG_RAW table and is inserted by the synch_device_messages() stored procedure. The synch_device_messages() stored procedure is executed on demand by the IoT Bridge for Snowflake.

Anchor
sparkplug_device_messages sparkplug_device_messages

SPARKPLUG_DEVICE_MESSAGES

...

Contains contains partially processed data where each row in the table is a single message containing data for each model instance (aka. Device Name)

...

• GROUP_ID
• EDGE_NODE_ID
• DEVICE_ID
• INSERTED_AT
• IS_GOOD
• IS_HISTORICAL
• MESSAGE_SEQUENCE
• MESSAGE_TIMESTAMP
• MESSAGE_TYPE
• METRIC_NAME
• METRIC_TIMESTAMP
• METRIC_TIMESTAMP_NTZ
• METRIC_VALUE
• MSG_ID
• TEMPLATE_PATH
• TEMPLATE_REFERENCE
• TEMPLATE_VERSION

Image Added

Anchor
sparkplug_device_registry sparkplug_device_registry

SPARKPLUG_DEVICE_REGISTRY

Shows all of the devices, whether or not they’re alive, and their BIRTH messages

• GROUP_ID
• EDGE_NODE_ID
• DEVICE_ID
• IS_ALIVE
• LAST_UPDATE
• BIRTH_MESSAGE

Image Added

Anchor
sparkplug_edge_node_registry sparkplug_edge_node_registry

SPARKPLUG_EDGE_NODE_REGISTRY

Shows all of the edge nodes, whether or not they’re alive, BIRTH messages and BD seq number

• GROUP_ID
• EDGE_NODE_ID
• DEVICE_ID
• IS_ALIVE
• LAST_UPDATE
• BIRTH_MESSAGE
• BQ_SEQ

Image Added

Anchor
sparkplug_template definition_registry sparkplug_template definition_registry

SPARKPLUG_TEMPLATE DEFINITION_REGISTRY

Shows the templates (UDT defs), their version, and their definitions

• TEMPLATE_PATH
• TEMPLATE_VERSION
• TEMPLATE_DEFINITION
• LAST_UPDATE

Image Added

Anchor
sparkplug_template_instance_registry sparkplug_template_instance_registry

SPARKPLUG_TEMPLATE_INSTANCE_REGISTRY

Shows all of the template instances (UDT instances), group/edge/device IDs, TEMPLATE_PATH (used to be DEVICE_NAME), TEMPLATE_REFERENCE (UDT def name), and the template definition

• GROUP_ID
• EDGE_NODE_ID
• DEVICE_ID
• LAST_UPDATE
• TEMPLATE_PATH
• TEMPLATE_VERSION
• TEMPLATE_DEFINITION
• LAST_UPDATE
• TEMPLATE_REFERENCE

Image Added

Views

...

The IoT Bridge for Snowflake will automatically create a new schema in its “Node” database for every Sparkplug edge node it finds. The Bridge will then create Views within this schema for each model found in the Sparkplug edge node data ingested via the bridge . There are three views per model:

Base View

This view contains the model instance tag data where each row is a single tag change per model instance. Tag change values are still encoded in JSON in the MEASURES_INFO and DDATA_MSG columns.

• Example: CL_BRIDGE_NODE_DB.NB_GROUP_EDGE.MOTOR

Image Removed

two views per model:

Intermediate View

This view contains the model instance tag data where each row is a single tag change per model instance. In this view, each model instance tag is separated out into its own column with their values. This view will show each individual model instance tag change and the tags that did not changed will be marked as null.

• Example: CL_BRIDGE_NODE_DB.NB_GROUP_EDGE.MOTOR_VW

Image RemovedImage Added

Asof View

This view is basically the same as the Intermediate view - each model instance tag is separated out into its own column. However, each row in this view has the null values from the Intermediate views removed (unless the tag change really had a null value) and the last known good value for the model instance tag will be replicated in its column. Said another way, each row will have the last known good values for all model instance tags.

• Example: CL_BRIDGE_NODE_DB.NB_GROUPG1_EDGEE1.MOTOR_ASOF_VW

Image RemovedImage Added