SQL collector
Basic settings
ConnectionString
Description: The connection string used to connect to the SQL database. Use $USER and $PASSWORD as placeholders for the necessary credentials Required: yes Examples:
MariaDB
- MariaDB was created as a fork of MySQL. Therefore, it uses the same connection string format as MySQL.
- It is required to add parseTime=true in the connection string to parse the timestamp correctly for a MariaDB connection using the MySQL driver.
- The NULL values should be handled, e.g. by using COALESCE(column_name, '') in the SQL query to read values from a column having NULL values, as otherwise NULL values will cause parsing issues for a MariaDB connection using the MySQL driver.
Oracle
The oracle driver used in the SQL collector supports v9+ Oracle SQL databases. Find the service name or SID name by performing the following command in powershell/cmd on the Windows Oracle Database server:
lsnrctl statusAlternatively, look for the content of the tnsnames.ora file typically found in a similar path as C:\oracle\ora90\network\ADMIN. The hostname and the tcp port is mentioned on which the Oracle DB is listening (IP and/or DNS name). Each item lists the service name, which is needed to setup a SQL connection.
Make sure that the ODBC library path is added to $PATH on the machine running the SQL collector (typically is automatically performed on installation of the ODBC driver).
ODBC
The ODBC driver for the underlying SQL database and the ODBC connection must be configured on the same machine that runs the SQL collector. For Linux that is performed in unixODBC and for Windows that is done in the ODBC Data Source Administrator. The name of the ODBC connection should be used after DSN= in the ODBC connection string.
User
Description: The user used to connect to the SQL database Required: no
Password
Description: The password used to connect to the SQL database Required: no
Driver
Description: The database driver that will be used to connect to the SQL database. Required: yes Options: postgres | oracle | sqlserver | mysql | sglite3 | hdb | ase | odbc
QueryInterval
Description: The interval in seconds between execution of the SQL query. Required: yes Default: 5 Minimum: 1
QueryTimeout
Description: The timeout in seconds of the SQL query. Required: yes Default: 5 Minimum: 1
QueryString
Description: The query string used to query the SQL database. Required: yes Examples:
- postgres: SELECT * FROM table_name WHERE updated > $1;
- oracle: SELECT * FROM table_name WHERE updated > :1;
- sqlserver: SELECT * FROM table_name WHERE updated > @p1;
- mysql/mariadb: SELECT * FROM table_name WHERE updated > ?;
- sqlite3: SELECT * FROM table_name WHERE updated > ?;
- hdb: SELECT * FROM table_name WHERE updated > ?;
- ase: SELECT * FROM table_name WHERE updated > ?;
- odbc: SELECT * FROM table_name WHERE updated > ?;
For the ODBC driver, the syntax of the underlying SQL database is used.
Advanced settings
HeartBeatInterval
Description: The SQL heartbeat timeout in milliseconds, minimum 5s. Required: no Default: 10000 Minimum: 5000
Measurement settings
The measurement settings reflect the configuration possibilities for mapping a measurement to an OPC-UA tag.
MeasurementKey
Description: The key that will get mapped to this measurement from the query result. Required: yes
Data collection method
The SQL collector allows you to collect data from a SQL database. This is done by periodically executing the configured queryconfigured 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
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 > ?;measurement_key | value | ts | updated | quality | tag_prodnr |
|---|---|---|---|---|---|
example_key | 12.0 | 2022-09-29 11:30:00+00 | 2022-09-29 11:50:00+00 | Good | 42 |