SQL-derived table
Overview
sql-derived-table creates a read-time SQL view over single-file table inputs. It changes no source bytes and is queried rather than executed with pond run.
Example configuration
patterns: # Default: {}; supply an example input.
source: table:///sensors/readings # 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.
All fields have omission behavior, but an empty patterns map does not make
the default query useful: it needs an input named source. Set query to your
own SQL to select, filter, or calculate columns. For the restricted local
variant, replace locality with {type: timestamp-local, event_time: timestamp};
the event-time field is required for that variant.
Use and behavior
Install with pond mknod sql-derived-table /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'.
Table inputs reject multi-file matches. Output schema is the SQL query schema.
global is the conservative locality default; timestamp-local supports a
single-source immutable projection/filter that preserves its configured
event-time column, optionally with ORDER BY without a row limit.
LIMIT, OFFSET, top-k sorts, aggregates, windows, subquery expressions, and non-immutable
expressions remain unsupported in timestamp-local mode.
The separate timestamp-grouped locality variant permits immutable aggregation
with exactly the configured event-time column as its grouping key. Event time
must be typed before grouping and preserved through projections; text may be
parsed into nanosecond timestamps. Bucketing, precision reduction, time-value
replacement, and nonlocal operations remain rejected. It requires complete
input groups at every included timestamp, not just the newest row or version.
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.