Versions Compared

Old Version 4

changes.mady.by.user Gill Houghton

Saved on

compared with

New Version Current

changes.mady.by.user Gill Houghton

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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

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:

Ignition EdgeMQTT SparkplugSnowflakeSnowflake tables/views
Ignition UDT DefinitionSparkplug TemplateModelTemplate_Reference
Ignition UDT InstanceSparkplug Template InstanceModel InstanceDevice_Name

Staging Database

The Staging database contains only a single table, 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.

The table is populated by the Snowflake Snowpipe Streaming API as data is streamed in by the IoT bridge.

*** Add image ***Image Added

Node Database

The Node database also contains only a single table, SPARKPLUG_DEVICE_MESSAGES, but also has additional views, user defined functions, store procedures, streams, etc.

The SPARKPLUG_DEVICE_MESSAGES table contains partially processed data where each row in the table is a single message containing data for each model instance (aka. Device Name). This 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.Image Added

Image Added

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:

...

  • Example: CL_BRIDGE_NODE_DB.NB_GROUP_EDGE.MOTOR

*** Add image ***Image Added

Intermediate View

...

  • Example: CL_BRIDGE_NODE_DB.NB_GROUP_EDGE.MOTOR_VW

*** Add image ***Image 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_GROUP_EDGE.MOTOR_ASOF_VW

*** Add image ***Image Added