# query_history

A log of the SQL queries run against your data, whether from the [console](https://dev.nominal.io/quickstart/quick/quick-sql.md#in-the-nominal-app), the [query endpoint](https://dev.nominal.io/sql-reference/rest-api/query.md), [export](https://dev.nominal.io/sql-reference/rest-api/export.md), or a [Postgres client](https://dev.nominal.io/sql-guides/postgres-clients.md).

No `dataset_rid` predicate is required.

What you can read depends on your role. Org admins read every query in their organization, and everyone else reads only their own, from every workspace in the organization. A predicate on `user_rid` narrows your results but never widens them. Entries expire 60 days after their event time.

A query writes one row when it starts and another when it finishes or fails. A query rejected before it starts writes only an `ERROR_BEFORE_START` row. Filter on `query_id` to follow one execution across its rows.

## Columns

| Column | Type | Nullable | Notes |
| --- | --- | --- | --- |
| `query_id` | `VARCHAR` | No | Identifier for one execution, shared by that execution’s rows. This is the `query_id` returned by the query endpoint. |
| `type` | `VARCHAR` | No | Which event this row records. See [Event types](https://dev.nominal.io/sql-reference/schema/query_history.md#event-types). |
| `user_rid` | `VARCHAR` | No | The user who ran the query. Your own RID unless you are an org admin. |
| `query_text` | `VARCHAR` | No | The submitted SQL, cut off after 4,096 characters. |
| `event_time` | `TIMESTAMP` | No | When this event was recorded, nanosecond precision. |
| `started_at` | `TIMESTAMP` | Yes | When the service received the request. |
| `duration_ms` | `BIGINT` | Yes | Elapsed milliseconds from `started_at` to `event_time`. Null on `QUERY_START` rows, which have not finished yet. |
| `error_code` | `BIGINT` | Yes | On a failed query’s final row, the number of the [error](https://dev.nominal.io/sql-reference/schema/query_history.md#error-codes) it failed with. Null otherwise. |
| `error_message` | `VARCHAR` | Yes | On a failed query’s final row, the error’s description and detail. Null otherwise. |
| `peak_memory_bytes` | `BIGINT` | Yes | Peak memory the query used, on the final row. |
| `execution_metrics` | `MAP` | No | Row and byte counters for the execution, such as `SelectedRows` and `SelectedBytes`, on the final row. |

### Event types

| `type` | Meaning |
| --- | --- |
| `QUERY_START` | The query was accepted and began executing. |
| `QUERY_FINISH` | The query completed successfully. |
| `ERROR_BEFORE_START` | The query failed before execution began, such as an invalid query or a dataset you can’t read. |
| `ERROR_WHILE_PROCESSING` | The query failed after execution began, including timeouts and cancellations. |

### Error codes

`error_code` holds the number of the error named in the [error response](https://dev.nominal.io/sql-reference/rest-api/errors.md):

| `error_code` | Error |
| --- | --- |
| 1 | `SQL_ERROR_INVALID_QUERY` |
| 2 | `SQL_ERROR_DATASETS_NOT_FOUND` |
| 3 | `SQL_ERROR_EXECUTION_FAILED` |
| 4 | `SQL_ERROR_EXPORT_NOT_SUPPORTED_FOR_BACKEND` |
| 5 | `SQL_ERROR_EXPORT_NOT_CONFIGURED` |
| 6 | `SQL_ERROR_QUERY_TIMEOUT` |
| 7 | `SQL_ERROR_RESOURCE_EXHAUSTED` |
| 9 | `SQL_ERROR_DATA_NOT_FOUND_FOR_TYPE` |
| 10 | `SQL_ERROR_RESULT_SIZE_EXCEEDED` |
| 11 | `SQL_ERROR_QUERY_SCOPE_TOO_LARGE` |
| 12 | `SQL_ERROR_QUERY_CANCELLED` |
| 14 | `SQL_ERROR_UNAUTHENTICATED` |
| 15 | `SQL_ERROR_PERMISSION_DENIED` |

## Examples

The most recent queries you can read:

```sql
SELECT event_time, type, duration_ms, query_text
FROM query_history
ORDER BY event_time DESC
LIMIT 50
```

The slowest completed queries:

```sql
SELECT query_id, duration_ms, query_text
FROM query_history
WHERE type = 'QUERY_FINISH'
  AND duration_ms IS NOT NULL
ORDER BY duration_ms DESC
LIMIT 20
```

Queries that failed:

```sql
SELECT event_time, type, query_text
FROM query_history
WHERE type IN ('ERROR_BEFORE_START', 'ERROR_WHILE_PROCESSING')
ORDER BY event_time DESC
```

Follow one execution end to end:

```sql
SELECT type, event_time, duration_ms
FROM query_history
WHERE query_id = '<query-id>'
ORDER BY event_time
```

As an org admin, find the heaviest query writers:

```sql
SELECT user_rid, COUNT(*) AS queries
FROM query_history
WHERE type = 'QUERY_FINISH'
GROUP BY user_rid
ORDER BY queries DESC
```

> **Note**
>
> Writes to `query_history` are asynchronous, so a query you just ran may take several seconds to appear. Until your organization’s first query has been recorded, querying the table returns `SQL_ERROR_INVALID_QUERY`.

## Next steps

- [Query](https://dev.nominal.io/sql-reference/rest-api/query.md): the endpoint these rows record.
- [Error handling](https://dev.nominal.io/sql-reference/rest-api/errors.md): what the failure types mean.
- [Writing queries](https://dev.nominal.io/sql-guides/writing-queries.md): scoping and query patterns.