Query staged dataframes
unhcr_dataframe_queryRead-onlyIdempotent
Run a single-statement SELECT against the dataframes staged by the unhcr_get_* tools. Check a dataframe’s columns with unhcr_dataframe_describe first. Read-only: writes, DDL, DROP, COPY, PRAGMA, ATTACH, external-file functions, and system catalogs (information_schema, pg_catalog, sqlite_master, duckdb_*) are rejected. Optional register_as saves the result as a new dataframe with a fresh TTL. Recompute rates from summed counts rather than averaging rate columns.
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| sql | Yes | One DuckDB SELECT against df_XXXXX_XXXXX tables, at most 20,000 characters — joins, aggregates, window functions, and CTEs work. SUM and COUNT results come back as JSON strings (BIGINT); CAST(… AS DOUBLE) for inline arithmetic. Staged tables add origin/asylum UNHCR and UN region columns for regional GROUP BY. | |
| preview | No | Rows to return inline when that should be fewer than the query materializes, e.g. a small sample while register_as keeps the whole result; a value above row_limit is treated as row_limit. Omit to return every row up to row_limit. | |
| row_limit | No | Most rows the query materializes (1–10000, default 1000). When more match, row_count_capped is true; use register_as to keep the full result. | |
| register_as | No | Save the result as a new dataframe under this name (df_ plus two groups of 5 uppercase letters or digits, e.g. df_ABCDE_12345) with a fresh TTL, to chain analyses. The name must not already be staged. The saved rows count toward the 1,000,000-row staging budget: the oldest other dataframes are evicted to make room, and a result larger than the budget is not saved. |
Output Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| cap | No | The cap that bound: preview when lower than row_limit, otherwise row_limit. | |
| rows | No | Result rows, one object per row keyed by column name, bounded by preview and row_limit. BIGINT values (SUM, COUNT) arrive as strings. | |
| error | No | Present when the call failed. Absent on success. | |
| shown | No | Rows returned inline. | |
| notice | No | Guidance when the query returned no rows or rows were withheld. | |
| columns | No | Column names in projection order. | |
| evicted | No | Older dataframes dropped, oldest first, to keep the staged total within the 1,000,000-row budget once register_as saved this result; they can no longer be queried. Present only when any were evicted. | |
| row_count | No | Rows the query produced, up to row_limit. When row_count_capped is true this is the cap, not the full size. | |
| truncated | No | True when rows were withheld by a cap. | |
| expires_at | No | ISO 8601 expiry of the new dataframe, when register_as was set. | |
| attribution | No | Attribution UNHCR requires when these figures are reused. | |
| registered_as | No | The new dataframe, when register_as was set. | |
| row_count_capped | No | True when more rows matched than row_limit allowed through. |