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

FieldAccepted typeRequiredDefaultDescription
localitySql Derived Localityno{"type": "global"}Declared query locality. Defaults to conservative global execution.
patternsmap of Watertown Urlno{}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"}
querystring | nullnonot specifiedSQL query to execute on the source data. Defaults to "SELECT * FROM source" if not specified
transformsarray | nullnonot specifiedOptional 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.

FieldAccepted typeRequiredDefaultDescription
type"global"yesnot specified

A single-source immutable projection/filter that preserves event time, optionally sorted without limiting rows

FieldAccepted typeRequiredDefaultDescription
event_timestringyesnot specifiedEvent-time column retained by the output.
input_event_timestring | nullnonot specifiedSource event-time column, defaulting to event_time. Only renaming, RFC3339 parsing, and integer-microsecond parsing preserve time.
type"timestamp-local"yesnot 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.

FieldAccepted typeRequiredDefaultDescription
event_timestringyesnot specifiedEvent-time column retained by the output.
input_event_timestring | nullnonot specifiedSource event-time column, defaulting to event_time.
type"timestamp-grouped"yesnot specified

Watertown Url

Accepted value: string.