Collector: SQL and historians

SQL databases and historians

The sql source runs read-only SELECT statements on a schedule and sends the rows as values. It reads MES, LIMS and batch databases, cycler logs, TimescaleDB and, with a preset, the SQL front ends of AVEVA (Wonderware) Historian and Aspen IP.21. Install its drivers with pip install "kymoscope-collector[sql]".

dsn (or url) takes env:NAME or file:path, so the password stays out of collector.yaml.

Queries

Every query is a single SELECT (or WITH … SELECT) that uses :since for the start time and ends with ORDER BY the time column. Write :since (and :until, if you need an end) in every database; the collector turns it into that driver's placeholder, so values are always bound and never pasted into the text. Use >= :since, not >.

Long layout: one row per value

A time, a tag name and a value in each row. Typical of historians, LIMS and event tables. Use tag: NAME instead of tag_column when the query reads one tag.

sources:
  - name: MES results
    type: sql
    dsn: env:MES_DSN                 # postgresql://kymo_ro:password@db.customer.local:5432/mes
    poll_ms: 5000
    start_from: "2026-10-01T00:00:00"   # first run only; "now" (the default) starts at the first poll
    time_zone: Europe/Madrid          # the zone of timestamps stored without one
    queries:
      - name: lab
        layout: long
        sql: >
          SELECT measured_at, sample_point, result
          FROM lab_results
          WHERE measured_at >= :since
          ORDER BY measured_at
        time_column: measured_at
        tag_column: sample_point
        value_column: result

Wide layout: one row per time

A time and one column per channel, as loggers and cycler databases store them. The column names are the channel names. A NULL in a listed column is stored as a gap; with no columns every numeric column is read and NULLs are skipped.

    queries:
      - name: cycler
        layout: wide
        sql: SELECT ts, voltage, current, temp_c FROM cycler_log WHERE ts >= :since ORDER BY ts
        time_column: ts
        columns: [voltage, current, temp_c]   # leave out for every numeric column
        overlap_s: 30                         # re-read the last 30 s each poll; repeats are dropped

Incremental reads

The collector remembers, per query, the newest time it has read (the watermark, in state_dir/sql/) and asks only for newer rows, so it survives restarts and a database that is down for a while: it resumes where it stopped. Rows it already sent at the watermark are recognised by tag and time and not sent again, so a second row arriving later with the same timestamp is still picked up. A crash between sending and saving can repeat the last few rows once; Kymoscope stores them at the same times. Rows older than the watermark that appear late are only picked up inside overlap_s. The health stream reports last_watermark_age_s: how old the newest value is.

The watermark is saved only after the values before it have been handed to the stream (sent, or in its disk buffer), so a restart never skips values that were still waiting. The file is replaced atomically after an fsync and the last good copy is kept as .bak: if the file is found damaged the collector says so in the log and continues from the copy. To de-duplicate, the file also keeps the tag and time of the rows read inside the overlap window (never more than 200,000, the newest); with a very high rate and a large overlap_s, keep the overlap short.

Presets for historians

A preset writes the queries from a list of tags. aveva-historian reads Runtime.dbo.History with wwRetrievalMode = 'Delta' (the values as stored) and turns any quality other than 192 (good) into a gap. It asks for wwTimeZone = 'UTC', so times are UTC whatever the server's zone (this is taken from AVEVA's documentation and not verified on a real server; if your version rejects it, use a queries entry without it). Delta mode also returns the value in force at the start time; it is sent once, not on every poll. ip21 reads the history table of each tag through the SQLplus ODBC driver (IP_TREND_TIME, IP_TREND_VALUE), one query per tag. timescale reads a time, tag, value table. InfluxDB is not SQL: use its HTTP API with the rest source.

sources:
  - name: Wonderware
    type: sql
    dsn: "mssql://kymo_ro:password@historian.customer.local/Runtime?TrustServerCertificate=yes"
    preset: aveva-historian
    tags: [FIC101.PV, TIC201.PV]       # a list, or the path of a tags.csv (node_id = the tag name)

  - name: IP21
    type: sql
    dsn: "DRIVER={AspenTech SQLplus};HOST=ip21.customer.local;PORT=10014"
    preset: ip21
    tags: tags.csv

  - name: Timescale
    type: sql
    dsn: env:TSDB_DSN
    preset: timescale
    table: public.readings             # columns time, tag, value; every tag when 'tags' is left out

Backfill and discover

kymoscope-collector discover --config collector.yaml --source NAME --out tags.csv lists the tags: the tags list, the preset's tag query, or the distinct tag values of the last discover_hours (long), or the columns (wide). backfill runs the same queries with :since and :until set to the window you ask for, in passes of max_rows, and does not move the watermark.

Read-only by design

The collector refuses any statement that is not a single SELECT or WITH (and any that contains words such as INSERT, UPDATE, DELETE, DROP or INTO), and PostgreSQL and Oracle queries run in read-only transactions. That check is a safety net, not a security boundary: it is a text check, not a parser, and databases have statements it cannot recognise (SQL Server accepts SELECT 1 SHUTDOWN, and OPENQUERY or linked servers can run anything the login may). What really protects the database is the user the collector connects as. Ask the database administrator for a user that can only read what the queries touch:

-- PostgreSQL / TimescaleDB
CREATE ROLE kymo_ro LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE mes TO kymo_ro;
GRANT USAGE ON SCHEMA public TO kymo_ro;
GRANT SELECT ON lab_results TO kymo_ro;

-- SQL Server / Historian
CREATE LOGIN kymo_ro WITH PASSWORD = '...';
USE Runtime; CREATE USER kymo_ro FOR LOGIN kymo_ro;
GRANT SELECT ON dbo.History TO kymo_ro;   -- or ALTER ROLE db_datareader ADD MEMBER kymo_ro;

Installing the ODBC driver

SQL Server, the Historian and IP.21 connect through ODBC; PostgreSQL and Oracle need no extra driver.

# Windows: download "Microsoft ODBC Driver 18 for SQL Server" from Microsoft and run the installer.
# Ubuntu / Debian (after adding Microsoft's package repository):
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18 unixodbc
# RHEL / Rocky:
sudo ACCEPT_EULA=Y dnf install -y msodbcsql18 unixODBC
# check that the driver is registered
python -c "import pyodbc; print(pyodbc.drivers())"

Troubleshooting

• "needs the asyncpg package" (or pyodbc, oracledb): install the extra: pip install "kymoscope-collector[sql]".
• "Data source name not found" or "can't open lib" (ODBC): the driver is not installed or its name in DRIVER= differs; compare with pyodbc.drivers().
• Certificate or login errors on SQL Server: driver 18 encrypts by default. Use a trusted certificate, or add ?TrustServerCertificate=yes knowing the link is not verified.
• "must ORDER BY" / "must use :since": an incremental query needs both; the message names the query.
• Values arrive hours off, or nothing arrives with start_from: now: set time_zone to the zone the database stores its timestamps in (the default is UTC; a database in local time west of UTC looks hours in the past). Columns with a time zone (timestamptz, datetimeoffset) are exact whatever it says. The collector logs a warning when the newest row is hours away from now at the start.
• Local times with daylight saving: times stored without a zone repeat for one hour in autumn. The collector tells the second pass apart as long as it runs, and reads an hour back around the change, but storing UTC timestamps is the only exact solution; it warns when you configure such a zone.
• A query hangs: after query_timeout_s it is cancelled and the poll fails; check the database for locks.
• Nothing arrives, no errors: the first run starts at start_from (now by default); use an earlier time to read history, or backfill.
• A warning about max_rows: more than max_rows rows share a single timestamp; raise it.
• poll_errors rising: read the collector log: a missing column, a permission error or a lost connection (the collector reconnects by itself with backoff).
• Health values for this source: connected, reconnects, polls, poll_errors, overruns, rows, values_per_s, last_watermark_age_s.