Configuring a SQL collector

the sql collector in factry historian allows you to connect to a sql database and collect data as time series once configured, the data is historized and made available for further processing in factry historian, and for trending, reporting, and analysis the collector periodically executes a configured sql query the query must return specific columns (measurement key, value, ts, updated, quality, and optionally tag ) to ensure that the data can be historized correctly prerequisites permissions in factry historian to manage collectors see privileges docid\ k2nnyecuxqqsiuclmpfkh an installed sql collector if you have not done so already, first installing a collector docid\ iv4 sd56tdxcqkrkcj4kv a working sql connection if you are running into issues or setting this up for the first time, follow our guide to testing an sql connection docid\ y9wuz3pblwctkvewfirfm a configured creating a time series database connection docid\ sq5uvlchyg7umi930nyml to write the collected data to configure the collector after installing the collector, you should see that the collector is in an initial state initial state and is actively running this means that the collector has established a connection to factry historian and is awaiting further configuration to configure the collector, select it from the collectors overview in factry historian and click the edit button configure the following fields connectionstring the connection string defines how the collector connects to your sql database it includes the database type, host, port, database name, and credentials examples postgres postgres\ //$user $password\@host 5432/database oracle oracle //$user $password\@server/service name sql server sqlserver //$user $password\@host/instance?param=value mysql/mariadb $user $password\@tcp(host 3306)/dbname?parsetime=true sqlite file\ test db?cache=shared\&mode=memory odbc dsn=my odbc datasource name;uid=$user;pwd=$password for mariadb, always add parsetime=true and handle null values with coalesce( ) to prevent parsing issues user and password optional fields if not already embedded in the connection string provide credentials with appropriate database permissions driver the sql driver to use options postgres | oracle | sqlserver | mysql | sqlite3 | hdb | ase | odbc queryinterval how often (in seconds) the query should be executed default 5 minimum 1 querytimeout maximum time (in seconds) before the query is aborted default 5 minimum 1 querystring the actual sql query that the collector executes it must return at least these columns measurement key — maps each row to a measurement value — the numeric or string value to historize ts — the timestamp of the datapoint updated — used to ensure each row is only collected once quality — the data quality (empty = “good”) any column prefixed with tag is stored as a tag (e g tag prodnr ) examples postgres select from table name where updated > $1; oracle select from table name where updated > 1; sql server select from table name where updated > @p1; mysql/mariadb select from table name where updated > ?; always filter by updated > ? to avoid re reading old data the ? placeholder is automatically replaced with the collector’s internal state you can leave the querystring empty if you only want to test whether the collector can connect to your database example query considering we have the following tables sensors pk id value timestamp updated 1 sensor temp 14 2025 09 10 00 00 30+00 2025 09 10 02 00 42+00 2 sensor volume 100 2025 09 10 00 13 45+00 2025 09 10 00 15 30+00 3 sensor weight 66 2025 09 10 00 21 40+00 2025 09 10 00 33 30+00 tags pk line nr sensor pk 1 2 3 2 45 1 3 6 2 using the following query in the collector’s querystring field select s id as measurement key, s value as value, s timestamp as ts, s updated as updated, 'good' as quality, t line nr as tag line nr from sensors s inner join tags t on s pk = t sensor pk where updated > ?; the collector will return measurement key value ts updated quality tag line nr sensor temp 14 2025 09 10 00 00 30z 2025 09 10 02 00 42z good 45 sensor volume 100 2025 09 10 00 13 45z 2025 09 10 00 15 30z good 6 sensor weight 66 2025 09 10 00 21 40z 2025 09 10 00 33 30z good 2 state the collector automatically tracks the updated column as its state this ensures only new rows are collected each cycle the state can be reset to re collect historic data always pause the collector before editing its state expected outcome after saving, a green start button will appear in the top right corner click start to launch the collector if the connection is successful, the collector status changes to collecting adding measurements best practices always handle null values in queries with coalesce( ) use date filters (updated > ?) to limit rows keep queries efficient to avoid overloading the source database limits support varies by driver see the sql collector docid\ aryaqtyz4bk koyzry6ui reference documentation for a complete list performance is usually limited by the sql database itself (indexes, query complexity, network speed) troubleshooting cannot connect to the database check the host and port used in the sql collector settings are correct if a user is used for connecting to the database, check if user credentials are correct check if the user has correct privileges to connect to the database query times out if a user is used, check if the user has correct privileges to read data try increasing the querytimeout setting in the sql collector docid\ aryaqtyz4bk koyzry6ui settings