Page History

...

• Staging database e.g., CL_BRIDGE_STAGE_V2_DB
• Node database e.g., CL_BRIDGE_NODE_V2_DB

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:

...

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

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

    as detailed below

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

• MQTT_RAW

  • This table contains

    inserted date/time, message, message id and topic

    information derived from each MQTT message as detailed below

    • INSERTED_AT
    • INSERTED_AT_DATE
    • MSG
    • MSG_ID
    • MSG_TOPIC

Node Database

The Node database contains five tables: SPARKPLUG_DEVICE_MESSAGES, SPARKPLUG_DEVICE_REGISTRY, SPARKPLUG_EDGE_NODE_REGISTRY and SPARKPLUG_TEMPLATE_DEFINITION_REGISTRY

...

• 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
• 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
• 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
• SPARKPLUG_TEMPLATE DEFINITION_REGISTRY
  • Shows the templates (UDT defs), their version, and their definitions
    • TEMPLATE_PATH
    • TEMPLATE_VERSION
    • TEMPLATE_DEFINITION
    • LAST_UPDATE
• 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

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 two views per model:

...