Spooling Impala Query Results
In Impala, you can control how query results are materialized and returned to clients, e.g. impala-shell, Hue, JDBC apps.
Result spooling is turned off by default, but can be enabled via the
SPOOL_QUERY_RESULTS query option.
- When query result spooling is disabled, Impala relies on clients to
fetch results to trigger the generation of more result row batches
until all the result rows have been produced. If a client issues a
query without fetching all the results, the query fragments continue
to consume the resources until the query is cancelled and
unregistered, potentially tying up resources and causing other queries
to wait for an extended period of time in admission control.
Impala would materialize rows on-demand where rows are created only when the client requests them.
- When query result spooling is enabled, result sets of queries are
eagerly fetched and spooled in the spooling location, either in memory
or on disk.
Once all result rows have been fetched and stored in the spooling location, the resources are freed up. Incoming client fetches can get the data from the spooled results.
Admission Control and Result Spooling
The maximum amount of memory used when spooling query results. If this value is exceeded when spooling results, all memory will most likely be spilled to disk. Set to 100 MB by default.
The maximum amount of memory that can be spilled to disk when spooling query results. Must be greater than or equal to
MAX_RESULT_SPOOLING_MEM. If this value is exceeded, the coordinator fragment will block until the client has consumed enough rows to free up more memory. Set to 1 GB by default.
FETCH_ROWS_TIMEOUT_MSquery option to set the timeout when clients fetch rows. Timeout applies both when query result spooling is enabled and disabled:
- When result spooling is disabled (
SPOOL_QUERY_RESULTS = FALSE), the timeout controls how long a client waits for a single row batch to be produced by the coordinator.
- When result spooling is enabled ( (
SPOOL_QUERY_RESULTS = TRUE), a client can fetch multiple row batches at a time, so this timeout controls the total time a client waits for row batches to be produced.
EXPLAINplan output for result spooling.
F01:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1 | Per-Host Resources: mem-estimate=4.02MB mem-reservation=4.00MB thread-reservation=1 PLAN-ROOT SINK | mem-estimate=4.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0
PLAN-ROOT SINKis an estimate of the amount of memory needed to spool all the rows returned by the query.
mem-reservationis the number and size of the buffers necessary to spool the query results. By default, the read and write buffers are 2 MB in size each, which is why the default is 4 MB.
In Impala, the
PlanRootSink class controls
the passing of batches of rows to the clients and acts as a queue of
rows to be sent to clients.
When result spooling is disabled, a single batch or rows is sent to the
PlanRootSink, and then the client must consume that batch before another one can be sent.
When result spooling is enabled, multiple batches of rows can be sent to the
PlanRootSink, and multiple batches can be consumed by the client.