tabular-query

The tabular-query transform loads one tabular parent output into a SQLite table, runs one or more SELECT queries from configuration, and returns a JSON object of result tables under tables.

CSV/TSV input is streamed into an on-disk SQLite database so large parent files (for example million-row DIS flatten CSVs) do not need to sit fully in Python memory. Query result tables keep the same JSON tables contract; rows are streamed to a temp file and uploaded via the runtime file path when available (instead of building one giant in-memory json.dumps). Prefer filtered or aggregated SELECTs when downstream consumers only need summaries, but full-row result tables remain supported.

Failures publish structured statusDetails (phase, message, exception type, and the query name for SQL errors) on the node instance so the reason is visible in the UI without searching runner logs.

When to use it

Use tabular-query when you need SQL filtering, grouping, or joins-on-one-table over CSV/TSV without exporting data outside HEAT. Downstream nodes such as tabular-to-dataservice can consume tables when inputFormat is json.

Limitations

Single tabular input only (one loaded table; no automatic multi-file joins).
Each query must be a single SELECT; all CSV columns are stored as TEXT (use CAST in SQL for numeric logic).
Optional captured_at / elapsed_ms helpers require timeColumn, timeColumnIsMs: true, and a valid baseCapturedAt.
Large SELECT * result sets used to stall while building one in-memory JSON blob; results are now streamed. Downstream still receives the same tables JSON shape. Very large result uploads still take wall time proportional to size.
Result JSON uploads carry an md5 on the node output config. An identical md5 on a retry skips republish and completes without a new artefact.

Configuration (summary)

Property	Required	Description
`queries`	yes	Map of name → SQL string; each result becomes `tables[name]`
`inputFormat`	no	`auto`, `csv`, or `tsv` (default `auto`)
`tableName`	no	SQLite table alias (default `t`)
`timeColumn`	no	Column for elapsed ms or ISO8601
`timeColumnIsMs`	no	Default `true`
`baseCapturedAt`	no	ISO8601 base when `timeColumnIsMs` is true

Example


{
  "inputFormat": "auto",
  "tableName": "t",
  "queries": {
    "fast_rows": "SELECT time_ms, speed_mps FROM t WHERE CAST(speed_mps AS REAL) > 10.0 ORDER BY CAST(time_ms AS INTEGER)"
  }
}

Output shape


{
  "tables": {
    "fast_rows": {
      "columns": ["time_ms", "speed_mps"],
      "rows": [{ "time_ms": "1000", "speed_mps": "12.5" }]
    }
  }
}