Task History | Spice.ai Cloud Documentation

For the complete documentation index, see llms.txt. This page is also available as Markdown.

The Spice runtime stores information about completed tasks in the spice.runtime.task_history table. A task is a single unit of execution within the runtime, such as a SQL query or an AI chat completion (see Task Types below). Tasks can be nested, and the runtime will record the parent-child relationship between tasks.

Each task executed has a row in this table, and by default the data is retained for 8 hours. Use a SELECT query to return information about each task as shown in this example:

SELECT
  *
FROM
  spice.runtime.task_history
LIMIT
  100;

Output:

+----------------------------------+------------------+----------------+---------------------+----------------------------------------------+-----------------+----------------------------+----------------------------+-----------------------+--------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------+
| trace_id                         | span_id          | parent_span_id | task                | input                                        | captured_output | start_time                 | end_time                   | execution_duration_ms | error_message                                                | labels                                                                                                                              |
+----------------------------------+------------------+----------------+---------------------+----------------------------------------------+-----------------+----------------------------+----------------------------+-----------------------+--------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------+
| f94dba6b89de98c6e54b074f2353a897 | 4eb243d9b5347762 |                | accelerated_refresh | runtime.metrics                              |                 | 2024-09-23T23:17:39.907789 | 2024-09-23T23:17:39.917777 | 9.988                 |                                                              | {sql: SELECT * FROM runtime.metrics}                                                                                                |
| 1f1f8305520e15ea7ad9b0a43e5d2c7e | 6aadf7c91caea3c4 |                | accelerated_refresh | runtime.task_history                         |                 | 2024-09-23T23:17:39.907873 | 2024-09-23T23:17:39.917797 | 9.924000000000001     |                                                              | {sql: SELECT * FROM runtime.task_history}                                                                                           |
| 1432e30c5ed7764f4ef35f6508dfd56c | fbb31c60d41d8232 |                | accelerated_refresh | logs_file                                    |                 | 2024-09-23T23:17:40.143699 | 2024-09-23T23:17:40.271678 | 127.97900000000001    |                                                              | {sql: SELECT * FROM logs_file}                                                                                                      |
| fd0b909b789938384d99f0e4e6f4b68b | 624ea4751bb6727a |                | accelerated_refresh | logs                                         |                 | 2024-09-23T23:17:40.676838 | 2024-09-23T23:17:42.345932 | 1669.0939999999998    |                                                              | {sql: SELECT * FROM "logs"}                                                                                                         |
| 3db5488039408825ac0829a3feb49b05 | e3e5ac928b497eef |                | accelerated_refresh | decimal                                      |                 | 2024-09-23T23:17:41.592359 | 2024-09-23T23:17:43.781699 | 2189.34               |                                                              | {sql: SELECT * FROM "decimal"}                                                                                                      |
| 5c5ddd481d1e19df823da74fe33f261f | 6afcfd1e65385a16 |                | sql_query           | select * from runtime.task_history limit 100 |                 | 2024-09-23T23:17:48.305649 | 2024-09-23T23:17:48.307369 | 1.72                  |                                                              | {runtime_query: true, query_execution_duration_ms: 1.429375, protocol: FlightSQL, datasets: runtime.task_history, rows_produced: 5} |
| 4c3dd314b874aa63fcd15023e67fc645 | cab3cdc2d31c1b6a |                | sql_query           | select block_number from logs_file limit 5   |                 | 2024-09-23T23:18:00.267218 | 2024-09-23T23:18:00.269278 | 2.06                  |                                                              | {datasets: logs_file, rows_produced: 5, query_execution_duration_ms: 1.940291, accelerated: true, protocol: FlightSQL}              |
| f135c00df3aecd68dfa4d2360eff78f5 | db3474855449715c |                | sql_query           | select * from foobar                         |                 | 2024-09-23T23:18:12.865122 | 2024-09-23T23:18:12.865196 | 0.074                 | Error during planning: table 'spice.public.foobar' not found | {protocol: FlightSQL, error_code: QueryPlanningError, rows_produced: 0, query_execution_duration_ms: 0.126959, datasets: }          |
+----------------------------------+------------------+----------------+---------------------+----------------------------------------------+-----------------+----------------------------+----------------------------+-----------------------+--------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------+

Task Types

The following top-level task types are currently recorded:

Task Type Description
CLI Command sql_query
spice sql
nsql_query
Natural Language to SQL Query ai_chat
spice chat
vector_search
spice search
accelerated_refresh
text_embed

Configuration

Set the following parameters in the runtime.task_history section of the spicepod.yaml file to configure task history:

Examples

Adjust the retention period for task history:

runtime:
  task_history:
    retention_period: 1h # Keep tasks for 1 hour
    retention_check_interval: 1m # Check for expired tasks every minute

Disable task history:

runtime:
  task_history:
    enabled: false

Disable capturing output from tasks:

runtime:
  task_history:
    captured_output: none # none or truncated

Table Schema

Column Name Data Type Is Nullable Description
trace_id Utf8 NO Unique identifier for the entire trace this task happened in
span_id Utf8 NO Unique identifier for this specific task within the trace
parent_span_id Utf8 YES Identifier of the parent task, if any
task Utf8 NO Name or description of the task being performed (e.g. sql_query)
input Utf8 NO Input data or parameters for the task
captured_output Utf8 YES Output or result of the task, if available
start_time Timestamp(Nanosecond, None) NO Time when the task started
end_time Timestamp(Nanosecond, None) NO Time when the task ended
execution_duration_ms Float64 NO Duration of the task execution in milliseconds
error_message Utf8 YES Error message if the task failed, otherwise null
labels Map(Utf8, Utf8) NO Key-value pairs for additional metadata or attributes associated with the task

Example Queries

Retrieve all tasks within a specific timeframe

SELECT
    trace_id,
    span_id,
    task,
    start_time,
    end_time,
    execution_duration_ms,
    error_message
FROM spice.runtime.task_history
WHERE start_time >= NOW() - INTERVAL '10 MINUTES'
  AND end_time <= NOW();

Retrieve the most recent error messages

SELECT
    trace_id,
    task,
    error_message,
    SUBSTRING(input, 1, 100) AS input_preview,
    start_time
FROM spice.runtime.task_history
WHERE error_message IS NOT NULL
ORDER BY start_time DESC
LIMIT 5;

Summarize number of tasks by type

SELECT task, COUNT(*) AS task_count, AVG(execution_duration_ms) AS avg_duration_ms
FROM spice.runtime.task_history
GROUP BY task
ORDER BY task_count DESC;

Identify the longest-running tasks

SELECT
    task,
    trace_id,
    parent_span_id,
    execution_duration_ms,
    labels
FROM spice.runtime.task_history
ORDER BY execution_duration_ms DESC
LIMIT 10;

Retrieve details of all tasks associated with a specific trace

SELECT
    task,
    trace_id,
    parent_span_id,
    span_id,
    execution_duration_ms,
    start_time,
    end_time,
    SUBSTRING(input, 1, 100) AS input_preview,
    SUBSTRING(captured_output, 1, 100) AS output_preview,
    error_message,
    labels
FROM spice.runtime.task_history
WHERE trace_id = 'b2a69503a1b83215603ead321eea6f61'
ORDER BY start_time;

Retrieve details of most recent chat query

SELECT
    task,
    trace_id,
    execution_duration_ms,
    start_time,
    SUBSTRING(input, 1, 100) AS input_preview,
    SUBSTRING(captured_output, 1, 100) AS output_preview,
    error_message,
    labels
FROM spice.runtime.task_history
WHERE trace_id = (
    SELECT trace_id
    FROM spice.runtime.task_history
    WHERE task = 'ai_chat'
    ORDER BY start_time DESC
    LIMIT 1
)
ORDER BY start_time;

Retrieve Recent Queries for Specific Dataset

SELECT
    task,
    start_time,
    execution_duration_ms,
    SUBSTRING(input, 1, 100) AS input_preview,
    error_message,
    labels
FROM spice.runtime.task_history
WHERE 'catalog_sales' = ANY(string_to_array(labels['datasets'], ','))
ORDER BY start_time DESC
LIMIT 5;

Example output:

| task      | start_time                 | execution_duration_ms | input_preview                        | error_message | labels                                          |
|-----------|----------------------------|-----------------------|--------------------------------------|---------------|------------------------------------------------|
| sql_query | 2024-11-25T07:15:31.278018 | 19.424                | with ss as (  select …              |               | {accelerated: true, protocol: FlightSQL, …}   |
| sql_query | 2024-11-25T07:15:31.233147 | 1.661                 | select  sum(cs_ext_discount_amt)  |               | {query_execution_duration_ms: 1.380667, …}     |
| sql_query | 2024-11-25T07:15:30.889905 | 30.101                | select      i_item_id …            |               | {query_execution_duration_ms: 29.511086, …}    |