Query EIA Route Data
eia_query_routeRead-only
Fetches data from a leaf route with optional facet filters, date range, frequency, and column selection. Use eia_describe_route first to discover valid facet IDs, facet values, column IDs, and frequency codes. Data values are strings in the response (EIA API returns all numeric values as strings, e.g. "9.13"); cast to DOUBLE in SQL when arithmetic is needed. Returns a preview inline and stages nothing by default — one upstream request, whatever total says. Pass stage: true to also page past the preview and stage the accumulated set as a DataCanvas table, then pass the returned dataset name to eia_dataframe_describe for its column names and eia_dataframe_query for SQL. Every dataset a tenant stages lands in the same canvas, so tables from different routes cross-join by name with nothing to thread between calls.
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| end | No | Period end (same format as start). | |
| sort | No | Result ordering. | |
| route | Yes | Leaf route path (e.g. "electricity/retail-sales", "steo"). Discoverable via eia_browse_routes or eia_search_routes. Leading, trailing, and doubled slashes are stripped, so an EIA-doc spelling like "/electricity/retail-sales/" resolves to the same route. | |
| stage | No | Stage the matching rows as a DataCanvas table for SQL (default false). Off, the call makes one upstream request and returns the preview alone. On, the service pages past the preview up to EIA_CANVAS_MAX_ROWS and registers the accumulated rows, returning the handle in dataset — several extra upstream requests and seconds of latency on a large route, so turn it on when moving to analysis, not while exploring. Requires a canvas (CANVAS_PROVIDER_TYPE=duckdb); without one nothing is staged whatever this is set to. | |
| start | No | Period start in the route date format (e.g. "2020-01" for monthly, "2020" for annual). Format from eia_describe_route. | |
| length | No | Rows to fetch for the inline page (default 100, max 5000 per EIA limit). Every fetched row is used, but the response carries only as many as fit a 64,000-byte budget per surface — wide routes end the page early, said so in canvas_preview_note, with next_offset naming where to continue. With stage: true, staging is not bounded by this — it pages past the preview on its own. | |
| offset | No | Row offset into the matching set (default 0). An offset at or beyond total returns zero rows. | |
| columns | No | Data column IDs to return (reduces payload). Defaults to all. IDs discoverable via eia_describe_route data_columns[].id. Every row also carries period and each facet ID whatever this lists, so naming those here is unnecessary and they are dropped from the request. | |
| filters | No | Facet filters keyed by facet ID (e.g. { "stateid": "TX", "sectorid": ["RES", "COM"] }). Use the facets[].id values returned by eia_describe_route as keys here. Every value travels in the request URI, which EIA caps at about 8 KB — a few hundred values across all filters; split a longer list across calls. | |
| frequency | No | Aggregation frequency ID (e.g. "monthly", "annual"). Defaults to route default. Valid IDs from eia_describe_route. |
Output Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| data | No | Inline rows: the first length rows from offset, ended early when the next whole row would push either response surface past the 64,000-byte budget. All numeric values are strings per the EIA API (e.g. "9.13"). Cast to DOUBLE in SQL for arithmetic: CAST(value AS DOUBLE). Per-column units appear as {col}-units fields inline in each row. Keys are dynamic column IDs from the EIA route. | |
| error | No | Present when the call failed. Absent on success. | |
| route | No | The route path queried, in canonical spelling — any leading, trailing, or doubled slashes the input carried are stripped. Reusable verbatim in a follow-up call. | |
| total | No | Total matching rows in the EIA dataset for this query (may exceed returned rows when pagination or spillover applies). | |
| notice | No | Informational message when EIA returned no rows — either zero rows matched the filters (broaden the query) or offset paged past the last row (reduce offset below total). A page left empty because its first row alone exceeds the response budget carries no notice; canvas_preview_note names the offset that skips it. | |
| dataset | No | df_<id> handle for the staged rows — eia_dataframe_describe lists its column names; eia_dataframe_query runs SQL over it (SELECT … FROM df_<id>). Present only when a stage: true call registered a table on a deployment with a canvas configured; absent otherwise, since nothing was staged. Every dataset a tenant stages shares one canvas, so handles from different routes join directly. | |
| frequency | No | Frequency of the returned data. | |
| appliedEnd | No | Echo of the end period as applied, when an end was provided. | |
| totalCount | No | Total matching rows in the EIA dataset. | |
| appliedSort | No | Echo of the result ordering as applied, when a sort was provided — the ordering that decided which rows a capped stage holds. | |
| date_format | No | Period format for the returned data (e.g. "YYYY-MM"). | |
| next_offset | No | Offset that continues right after the rows in data (offset + returned_count) — pass it as offset for the next page, with no gap or overlap. Present whenever rows remain past this page; absent on the last page. | |
| appliedStart | No | Echo of the start period as applied, when a start was provided. | |
| appliedLength | No | Preview row count requested for this call. | |
| appliedOffset | No | Row offset applied to the query — the cause when a page comes back empty. | |
| returnedCount | No | Rows in this response. When returnedCount < totalCount, continue at next_offset or stage the rest. | |
| appliedColumns | No | Echo of the column projection as sent to EIA, when columns named at least one data column — period and facet IDs, which every row carries anyway, are not part of it. | |
| appliedFilters | No | Facet filters applied to the query, when provided. Past 4,000 bytes the echo keeps the leading values and adds a "…" entry counting those not echoed per facet — every value was still applied. | |
| effectiveRoute | No | The route path that was queried. | |
| returned_count | No | Number of rows in data. When returned_count < total, continue at next_offset or stage the rest for SQL. | |
| appliedFrequency | No | Echo of the frequency as applied, when a frequency was provided. | |
| truncation_warning | No | Upstream advisories forwarded verbatim from EIA's warnings[], joined with '; ' when more than one applies. These describe the inline page — EIA's "incomplete return" entry fires whenever the requested length is under total, at any size — not a 5,000-row ceiling on this response. Absent when the response already accounts for the gap the advisory names (the staged table reaches the last row, notice explains the empty page, or canvas_preview_note places the inline page against total because nothing was staged). | |
| canvas_preview_note | No | Human-readable note, present whenever dataset is set or staging failed, and otherwise when total exceeds the inline rows. On a stage: true call it opens with the stage outcome: the staged row count and dataset name, naming eia_dataframe_describe for the staged column names (quoting one rename when staging renamed a column) and then eia_dataframe_query for SQL — or, when the canvas failed, that no dataframe was created (dataset is then absent), with the retry and offset paging. Next, where in the matching set the staged rows sit whenever the stage does not start at row 1, and when staging stopped short of total (the EIA_CANVAS_MAX_ROWS cap, or an upstream page that did not return) the offset to resume from; last, the inline page against total and next_offset — saying when the 64,000-byte response budget rather than length ended the page, or that a single row too large for the budget was withheld and offset + 1 steps past it — and, after a registered stage, that the inline rows page with stage left off, since another stage: true call stages a new table. Where staging was not requested it places the inline page against total and names stage: true and eia_dataframe_query as the way to get SQL access to the rest; where no canvas is configured at all, staging is unavailable, so it names offset paging and CANVAS_PROVIDER_TYPE=duckdb instead. |