# Query the session archive

Query archived Omnia sessions with SQL in Snowflake, BigQuery, ClickHouse, Athena or DuckDB, reading the Parquet files in your bucket directly.

## Prerequisites

- The compaction job has archived sessions to your S3, GCS or Azure Blob bucket. The job archives sessions last updated more than `warmStore.retentionDays` days ago (7 when the policy sets none). See [Session retention](/explanation/agents/session-retention/).
- Your SQL engine has read access to the bucket.
- You know the layout: `sessions/year=YYYY/month=MM/day=DD/agent=<name>/part-NNNN.parquet`, partitioned by the session's creation date. See [Session archive format](/reference/platform/session-archive/) for the partitions, manifest and every column.

Point the file pattern at `*.parquet`; the bucket also holds `sessions/_manifest.json`.

## Turn on the archive with Helm

The compaction job archives when the policy it mounts has `coldArchive.enabled`, and writes to the bucket under `sessionRetention.compaction.coldArchive`:

```yaml
sessionRetention:
  defaultPolicy:
    coldArchive:
      enabled: true
      retentionDays: 365
  compaction:
    enabled: true
    coldArchive:
      backend: s3            # s3, gcs or azure
      bucket: my-omnia-archive
      region: us-east-1      # required for s3
      endpoint: ""           # set for MinIO and other S3-compatible stores
```

For Azure Blob, set `backend: azure`, put the container name in `bucket`, and set `coldArchive.azure.accountName`. Name a Secret in `coldArchive.azure.accountKeySecret` to authenticate with the account key; leave it empty to use workload identity. For S3 and GCS, grant the bucket to the compaction ServiceAccount through `sessionRetention.compaction.serviceAccount.annotations` (IRSA or Workload Identity).

The chart refuses to render when the `default` policy archives and `compaction.coldArchive.bucket` is empty.

## Convert nanosecond timestamps

The timestamp columns (`created_at`, `updated_at`, `expires_at`, `ended_at`) are `int64` nanoseconds since the Unix epoch. Convert them in your query:

| Engine | Expression |
|--------|------------|
| Snowflake | `TO_TIMESTAMP_NTZ(created_at / 1000000000)` |
| BigQuery | `TIMESTAMP_MICROS(DIV(created_at, 1000))` |
| ClickHouse | `fromUnixTimestamp64Nano(created_at)` |
| Athena / Trino | `from_unixtime(created_at / 1000000000)` |
| DuckDB | `make_timestamp(created_at // 1000)` |

The `messages_json` column holds the conversation as a JSON string. Each engine's JSON functions parse it in the queries below.

## Query from Snowflake

Create an external stage pointing at your bucket, then an external table over the Parquet files:

```sql
CREATE OR REPLACE STAGE omnia_archive_stage
  URL = 's3://my-omnia-archive/sessions/'
  STORAGE_INTEGRATION = my_s3_integration
  FILE_FORMAT = (TYPE = PARQUET);

CREATE OR REPLACE EXTERNAL TABLE omnia_sessions
  WITH LOCATION = @omnia_archive_stage
  PATTERN = '.*[.]parquet'
  FILE_FORMAT = (TYPE = PARQUET)
  AUTO_REFRESH = TRUE;
```

Query the conversation content using `VARIANT`:

```sql
SELECT
  VALUE:id::STRING                                              AS session_id,
  VALUE:agent_name::STRING                                      AS agent,
  TO_TIMESTAMP_NTZ(VALUE:created_at::NUMBER / 1000000000)       AS created_at,
  VALUE:total_input_tokens::NUMBER                              AS input_tokens,
  VALUE:estimated_cost_usd::FLOAT                               AS cost_usd,
  PARSE_JSON(VALUE:messages_json::STRING)                       AS messages
FROM omnia_sessions
WHERE VALUE:agent_name::STRING = 'my-agent'
  AND VALUE:created_at::NUMBER >= DATE_PART('epoch_nanosecond', '2026-04-01'::TIMESTAMP);
```

For high-frequency BI dashboards where external table read cost becomes noticeable, use [Snowpipe](https://docs.snowflake.com/en/user-guide/data-load-snowpipe-intro) to load the files into a native table on an S3 `ObjectCreated` event.

## Query from BigQuery

Create a Hive-partitioned external table:

```sql
CREATE OR REPLACE EXTERNAL TABLE `my_project.omnia.sessions`
  WITH PARTITION COLUMNS (
    year INT64,
    month INT64,
    day INT64,
    agent STRING
  )
  OPTIONS (
    format = 'PARQUET',
    hive_partition_uri_prefix = 'gs://my-omnia-archive/sessions/',
    uris = ['gs://my-omnia-archive/sessions/year=*/month=*/day=*/agent=*/*.parquet']
  );
```

Partition pruning works automatically because BigQuery recognizes the `year=/month=/day=/agent=` segments:

```sql
SELECT
  id,
  agent_name,
  TIMESTAMP_MICROS(DIV(created_at, 1000)) AS created_at,
  total_input_tokens,
  estimated_cost_usd,
  JSON_EXTRACT_ARRAY(messages_json, '$') AS messages
FROM `my_project.omnia.sessions`
WHERE year = 2026 AND month = 4
  AND agent_name = 'my-agent';
```

For native-table performance, use [BigQuery Data Transfer Service](https://cloud.google.com/bigquery/docs/dts-introduction) to ingest the same files into a partitioned managed table.

## Query from ClickHouse

ClickHouse reads S3 Parquet natively via the `s3` table function:

```sql
SELECT
  id,
  agent_name,
  fromUnixTimestamp64Nano(created_at) AS created_at,
  total_input_tokens,
  estimated_cost_usd,
  JSONExtractArrayRaw(messages_json)  AS messages
FROM s3(
  'https://my-omnia-archive.s3.us-east-1.amazonaws.com/sessions/year=2026/month=04/**/*.parquet',
  'AKIA...', 'SECRET...',
  'Parquet'
)
WHERE agent_name = 'my-agent';
```

Or create a persistent table using the `S3` engine:

```sql
CREATE TABLE omnia_sessions
ENGINE = S3(
  'https://my-omnia-archive.s3.us-east-1.amazonaws.com/sessions/**/*.parquet',
  'AKIA...', 'SECRET...',
  'Parquet'
);
```

## Query from Athena

Athena reads Parquet on S3 via a Glue-registered external table:

```sql
CREATE EXTERNAL TABLE omnia_sessions (
  id                   STRING,
  agent_name           STRING,
  namespace            STRING,
  workspace_name       STRING,
  status               STRING,
  created_at           BIGINT,
  updated_at           BIGINT,
  expires_at           BIGINT,
  ended_at             BIGINT,
  message_count        INT,
  tool_call_count      INT,
  total_input_tokens   BIGINT,
  total_output_tokens  BIGINT,
  estimated_cost_usd   DOUBLE,
  tags                 STRING,
  state                STRING,
  last_message_preview STRING,
  messages_json        STRING
)
PARTITIONED BY (year INT, month INT, day INT, agent STRING)
STORED AS PARQUET
LOCATION 's3://my-omnia-archive/sessions/'
TBLPROPERTIES ('parquet.compression' = 'SNAPPY');

MSCK REPAIR TABLE omnia_sessions;
```

Then query with partition pruning:

```sql
SELECT
  id,
  agent_name,
  from_unixtime(created_at / 1000000000) AS created_at,
  CAST(json_extract(messages_json, '$') AS ARRAY<JSON>) AS messages
FROM omnia_sessions
WHERE year = 2026 AND month = 4
  AND agent_name = 'my-agent';
```

## Query from DuckDB

For local / ad-hoc exploration, DuckDB reads Parquet on object storage directly:

```sql
INSTALL httpfs;
LOAD httpfs;

SET s3_region = 'us-east-1';
SET s3_access_key_id = 'AKIA...';
SET s3_secret_access_key = 'SECRET...';

SELECT
  id,
  agent_name,
  make_timestamp(created_at // 1000)                 AS created_at,
  total_input_tokens,
  estimated_cost_usd,
  json_extract(messages_json, '$')::JSON             AS messages
FROM read_parquet('s3://my-omnia-archive/sessions/**/*.parquet', hive_partitioning = true)
WHERE year = 2026 AND month = 4
  AND agent_name = 'my-agent';
```

DuckDB infers the `year`, `month`, `day`, and `agent` partition columns automatically when `hive_partitioning = true` is set.

## Verify

Run the engine's query with a filter on one agent. It returns one row per archived session of that agent, with `messages` holding the parsed conversation.

## Troubleshooting

### The query returns no rows

A session is archived only after the compaction CronJob runs and the session is older than the warm retention. With cold archive disabled, the same job deletes expired sessions and keeps no copy. Check that the bucket holds `.parquet` objects under `sessions/`.

### The engine fails on `_manifest.json`

Restrict the file pattern to `*.parquet`, as the queries above do.

### Athena returns no rows for new days

Run `MSCK REPAIR TABLE omnia_sessions;` again to register the new partitions.

## Related pages

- [Session archive format](/reference/platform/session-archive/): layout, partitions and Parquet schema
- [Session retention](/explanation/agents/session-retention/): how sessions reach the archive and when cold data is purged
- [SessionRetentionPolicy CRD](/reference/policies/sessionretentionpolicy/): warm and cold retention fields