Data collection method

The SQL collector allows you to collect data from a SQL database. This is done by periodically executing the configured query . The collector expects the following columns to be returned:

• measurement_key : used to link the row to a measurement.
• quality: the quality of the data, will get added as the status tag. An empty value is considered as a “Good” status.
• value: the value of the data point, should be of the corresponding measurement datatype.
• ts: the timestamp of the data point.
• updated: this value used to make sure we don’t query data more than once, the most recent timestamp is kept and passed to the query as a parameter.
• tag_: all columns starting with tag_ are considered as tags with everything after tag_ being used as the tag name.

Example query

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT
  a.ID AS measurement_key,
  a.VAL AS value,
  b.TIMESTAMP AS ts,
  a.UPDATED AS updated,
  'Good' AS quality,
  b.PRODUCT_NR AS tag_prodnr,
FROM FACTRY.A a
INNER JOIN FACTRY.B b ON b.NR = a.B_NR
WHERE updated > ?;