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

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.