SQL-derived series
Overview
sql-derived-series creates a read-time SQL view over matching series inputs. Matching schemas are harmonized by column name before SQL execution.
Example configuration
patterns: # Default: {}; supply an example input.
source: series:///sensors/* # This name becomes the SQL table name.
query: null # Default: SELECT * FROM source.
transforms: null # Default: no input transforms.
locality:
type: global # Default: conservative query locality.
The example supplies source because the default query needs it; an empty
patterns map is only the deserialization default, not a working data source.
For a timestamp-local projection/filter, replace locality with
{type: timestamp-local, event_time: timestamp}. That variant requires an
event-time column and must satisfy the restrictions below.
Use and behavior
Install with pond mknod sql-derived-series /derived/readings --config-path view.yaml. Inspect it with pond describe /derived/readings and query it with pond cat /derived/readings --format=table --sql 'SELECT * FROM source'.
Missing columns become null where schemas can be harmonized; incompatible types can fail planning. Transforms run before SQL. timestamp-local is an explicit restricted locality contract, not an optimization hint for arbitrary joins or aggregates.
Timestamp-local queries may use immutable projections, filters, and ORDER BY
without a row limit. Sorting preserves the selected rows and their values;
LIMIT, OFFSET, and top-k sorts can change which rows belong to the result and
remain unsupported. Aggregates, windows, subquery expressions, and non-immutable
expressions also remain unsupported in this mode.
For aggregation within complete groups of one exact timestamp, opt in separately:
locality:
type: timestamp-grouped
event_time: timestamp
This mode requires exactly one GROUP BY key: the configured event-time column.
Its name and value must survive every projection. The grouping key must already
be a timestamp or an Int64 epoch-microsecond value; string timestamps may be
parsed into nanosecond timestamps before grouping. Time bucketing, precision
reduction, renaming another value as event time, and parsing time only after
grouping are rejected.
Aggregates and their arguments must be immutable. Filters and non-limiting
sorts are permitted, but limits, offsets, windows, grouping sets, joins, and
subquery expressions remain unsupported. Ordinary timestamp-local mode still
rejects aggregation.
Bounded execution must retain every input row contributing to an included
timestamp group, even across source versions. This contract describes query
dependencies, not group finality: it does not change materialize-series'
append-only handling of late data.
Full configuration
Settings
| Field | Accepted type | Required | Default | Description |
|---|---|---|---|---|
locality | Sql Derived Locality | no | {"type": "global"} | Declared query locality. Defaults to conservative global execution. |
patterns | map of Watertown Url | no | {} | Named URL patterns for matching files. Each pattern name becomes a table in the SQL query. Each pattern can match multiple files which are automatically harmonized with UNION ALL BY NAME. URL Format: scheme:///path/pattern - series:///pattern - FileSeries (for sql-derived-series mode) - table:///pattern - FileTable (for sql-derived-table mode) Example: {"vulink": "series:///data/vulink*.series", "at500": "series:///data/at500*.series"} |
query | string | null | no | not specified | SQL query to execute on the source data. Defaults to "SELECT * FROM source" if not specified |
transforms | array | null | no | not specified | Optional list of table transform factory paths to apply to each pattern's TableProvider. Transforms are applied in order before scope prefixes and SQL execution. Each transform is a path like "/transforms/column-rename" |
Sql Derived Locality
Explicit locality contract for user-authored SQL.
Choose one of the following:
Unrestricted SQL
Unrestricted SQL. The query is visible as global and non-incremental.
| Field | Accepted type | Required | Default | Description |
|---|---|---|---|---|
type | "global" | yes | not specified |
A single-source immutable projection/filter that preserves event time, optionally sorted without limiting rows
| Field | Accepted type | Required | Default | Description |
|---|---|---|---|---|
event_time | string | yes | not specified | Event-time column retained by the output. |
input_event_time | string | null | no | not specified | Source event-time column, defaulting to event_time. Only renaming, RFC3339 parsing, and integer-microsecond parsing preserve time. |
type | "timestamp-local" | yes | not specified |
Single-source aggregation grouped only by the exact retained event time. The event-time value must be preserved; text may be parsed into a nanosecond timestamp
Single-source aggregation grouped only by the exact retained event time. The event-time value must be preserved; text may be parsed into a nanosecond timestamp. Nonlocal operators and time bucketing are rejected.
| Field | Accepted type | Required | Default | Description |
|---|---|---|---|---|
event_time | string | yes | not specified | Event-time column retained by the output. |
input_event_time | string | null | no | not specified | Source event-time column, defaulting to event_time. |
type | "timestamp-grouped" | yes | not specified |
Watertown Url
Accepted value: string.