Lookup Table Sync
Available from version 8.3 and later.
Lookup table sync is experimental and off by default. To turn it on, add lookuptablesync to the FEATURE_TOGGLES_ENABLE environment variable (comma-separated) and restart Factry Historian. The setting applies to every organization on the server.
A synced lookup table takes its rows from a query on an external database. Historian runs the query on an interval, or when you click Sync now, and replaces every row of the table with the result. Users cannot edit the rows of a synced table.
Creating a synced table
- On the Lookup tables page, click Create lookup table.
- Set Source to External database. The source cannot change after the table is created.
- Fill in the Sync fieldset and click Preview.
- Check the columns the preview returned. Choose a type for each column, or remove the columns you do not need.
- Click Save. The first sync runs right away.
A synced table can only be saved with the columns of a preview of its current query. After changing the query or the external database, click Preview again before saving.
Sync settings
External database
Description: The external database the query runs on. One of the external databases of the organization.
Sync interval
Description: The time between two scheduled syncs, as a duration literal such as 5m or 1h. At least 1m. Default: 5m
Query
Description: The query that returns the rows. The query runs unattended on every sync, under the account of the external database connection. Give that connection an account that can only read.
The result columns are matched to the table columns by name:
- A result column the table does not have is ignored.
- A table column the result does not have, or has more than once, fails the sync.
- A result column without a name, with a reserved name (row, params) or with a duplicate name has to be renamed with an alias in the query.
Watermark query (optional)
Description: A cheap query that returns one row that changes whenever the data does. When its result is the same as on the previous run, the sync skips reading the rows. The status then shows 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 productsThe result can hold at most 16 columns and 4096 bytes. When the watermark query fails, returns no row or returns more than one row, the sync reads the rows anyway, so a broken watermark query never hides a change.
Preview
Preview runs the query and the watermark query, and writes nothing. It shows:
- The columns of the result, with a type detected from the first 1,000 rows. The type follows the database type of the column, so a text column of digits stays a string.
- The number of rows the query returns, or a warning when it returns more than 500,000 rows, the most a sync can write.
- The result of the watermark query, or the error it failed with.
- The result columns that need an alias.
Sync now
Sync now runs the sync of the table immediately, even when the watermark is unchanged. Save or cancel your changes first.
Scheduling
Historian checks every 15 seconds for synced tables whose interval has passed since their last run started. One sync runs at a time on the server, so a sync waits for the one in progress. After a restart, the schedule continues from the stored state of each table.
Saving a synced table runs a sync right away when its query, external database or columns changed. Unlike on a manual table, the type of a saved column can be changed on the Settings tab. Changing the columns or their types clears the rows first, because they were read for the old columns. If that sync fails, the table stays empty until a sync succeeds.
Values
A sync converts each value to the type of its column:
- String: the value as text. A date and time is written in UTC.
- Number: a numeric value, a decimal, or text that holds a number. An integer larger than 2^53 cannot be stored exactly and fails the sync. Cast it to text in the query and use a string column.
- Boolean: a boolean, the integers 0 and 1, or the text true, yes, 1, false, no and 0.
A NULL is an empty cell. A value that does not fit its column fails the sync with an error that names the row and the column.
Sync status
The Sync status fieldset on the Settings tab shows:
- Last run: when the last sync started, with its outcome: Synced, Unchanged or Failed.
- Last synced: when the rows were last replaced.
- Rows: the number of rows the last successful sync wrote.
- Watermark: the last watermark query result.
- Error: why the last sync failed, and how many times in a row it failed.
The list of lookup tables shows the outcome and time of the last run in its Sync status column.
When a sync fails
A failed sync keeps the rows the table already has (except after a column change, see Scheduling). The table keeps working for calculations with those rows.
A sync fails, among other reasons, when the query fails, when a table column is missing from the result, when a value does not fit its column, when the result has more than 500,000 rows, when the rows do not fit in the lookup table memory limit, or when the external database is not available.
After a failure:
- The Sync status shows the error.
- The table is listed under Lookup tables whose sync failed on the homepage issues panel, for users with READ_LOOKUP_TABLES.
- The stored watermark is cleared, so the next run reads the rows again.
Privileges
Configuring a sync, Preview and Sync now need both MANAGE_LOOKUP_TABLES and READ_EXTERNAL_DATABASES. Without READ_EXTERNAL_DATABASES, the sync settings are shown read-only and Sync now is hidden. Scheduled syncs run without a user.
Turning sync off
With lookuptablesync removed from FEATURE_TOGGLES_ENABLE, scheduled syncs stop and a synced table behaves like a manual table: users can edit its rows. Its sync settings are kept. When sync is turned on again, a table whose rows were edited in the meantime reads its rows again on its next run.