Syncing a lookup table from an external database
Available from version 8.3 and later.
A lookup table can take its rows from a query on an external database. Historian runs the query on an interval and replaces the rows of the table with the result.
Syncing lookup tables is experimental and off by default. To turn it on, add lookuptablesync to the FEATURE_TOGGLES_ENABLE environment variable of the Historian server (comma-separated if it already holds other names) and restart Historian.
Before you start
- Create a connection to the external database. See Creating an external database connection.
- You need the MANAGE_LOOKUP_TABLES and READ_EXTERNAL_DATABASES privileges, and the lookup tables feature in your license.
The query runs unattended on every sync, under the account of the external database connection. Give that connection an account that can only read.
Create a synced lookup table
Whether a table is synced is chosen when you create it. You cannot change it later.
- Click Lookup tables in the main menu.
- Click Create lookup table.
- Enter a Name and, optionally, a Description.
- Set Source to External database.
- Under Sync, fill in:
- External database: the connection to read from.
- Sync interval: how often the rows are read again, as a duration such as 5m or 1h. The default is 5m, the minimum is 1m.
- Query: the query that returns the rows.
- Watermark query (optional): lets a scheduled sync skip a run when the data has not changed (see below).
- Click Preview. Historian runs the query and shows how many rows it returns. The columns of the table are taken from the query result.
- Under Columns, check the type of each column, and remove the columns you do not need. The column names come from the query and cannot be changed.
- Click Save. Historian runs the first sync right away.
You cannot save a new query until you have previewed it. A query that returns more than 500,000 rows is refused.
The rows of a synced table cannot be edited or imported. They only change through the sync. You can still export them.
Skip unchanged data with a watermark query
A watermark query is a cheap query that returns one row, whose value changes whenever the data changes. Before a scheduled sync, Historian runs the watermark query. When its result is the same as at the previous sync, the sync is skipped and shows as Unchanged.
The watermark query must also notice deleted rows. max(updated_at) alone does not, so combine it with a count, for example:
SELECT count(*), max(updated_at) FROM product_specsIf the watermark query fails, every sync reads the rows.
Sync now
To read the rows right away, select the table and click Sync now at the top of the panel. Sync now ignores the watermark and always reads the rows. Save or cancel your changes first.
Check the sync status
The Sync status section of a synced table shows:
- Last run: when the last sync ran, and whether it Synced, was Unchanged or Failed.
- Last synced: when the rows were last replaced.
- Rows: the number of rows the last successful sync wrote.
- Watermark: the result of the last watermark query.
- Error: why the last sync failed, and how many times in a row it failed.
When a sync fails, the table keeps its rows. The table is listed under Lookup tables whose sync failed in the issues panel on the homepage until a sync succeeds.
Change the query
- Select the table and click Edit. Open the Settings tab.
- Change the query or the external database, and click Preview again.
- Click Save.
Saving a changed query or changed columns runs a sync right away. When the columns change, the old rows are dropped and read again for the new columns.
When syncing is turned off
If you remove lookuptablesync from FEATURE_TOGGLES_ENABLE, synced tables stop syncing and keep their rows. Users can then edit them like any other lookup table. The sync settings are kept, and syncing resumes when you turn the feature on again.