Skip to content

SQL Sinker IoT Consumer Definition#

The SQL Sinker IoT Consumer Definition is provided by the system to allow the user to extract data from the IoT Data Platform into a relational structure.

sqlsinker_main

The SQL Sinker maps IoT Events as described in the picture below.

sqlsinker_model

For example, if an event with the EventId 111111 and with the measurement values for three wafers is received. The following happens:

  • The SysProperties and AppProperties sections of the event map into the {TableName_Event} table with one row of fixed columns (some properties may be null).
  • The top-level indexed properties of the data section of the event map into the {TableName_EventAttribute} with one row per application property in a name-value style.
  • The array property (at this time only one array is supported) of the data section map into the main table CVDMeasurement with one row per array entry. Note that the same structure still applies for events with non-array structures, in which case the only difference would be that the main table, in this example the CVDMeasurement, would contain only one row per event.

This process is illustrated in the image below.

sqlsinker_process

Furthermore, the SQL Server Consumer will be marked as a Data Source, so it can be used as a Data Source in the Critical Manufacturing MES (for Data Series, Data Value or Streaming Data). The SQL Server Consumer will create a Database View and expose it as a data set (via OAuth or a database view) in a consolidated format as shown below.

sqlsinker_format

When creating or editing an IoT Consumer with the SQL Sinker IoT Consumer Definition, the user can provide some parameters in the configuration step:

  • Metadata Connection String - For the database which contains the IoT Event Definition metadata.
  • Connection String - For the database that will receive the mapped IoT Events.
  • Table Name - The name of the table.
  • Use Global Event Tables - Whether to use global event tables or not. If global event tables are to be used, the global event table names will be:
    • Event
    • EventAttribute
  • Starting Offsets
    • Earliest - processes every event stored
    • Latest - processes events that arrive after the consumer starts
  • Start Date
    • Alternative to Starting Offsets, taking precedence over it
    • Ingests events that were not previously consumed by this consumer and have an Event Timestamp later than this date.
  • Properties - the event properties that will be considered by the sinker