# Reading a sealed hour

Summary: A sealed hour is plain files: a Parquet table, a zstd-compressed JSON Lines file of frames and a JSON manifest. DuckDB and pyarrow read them with no Nox software. Every normalized row names the frame it came from, so each value traces back to the bytes your venue sent.

## What you need

- The DuckDB command-line client and Python with pyarrow. The commands below are tested with DuckDB 1.5.6 and
  pyarrow 26.0.0 on every change to these pages.
- A POSIX shell: `sh`, `bash` or `zsh`.
- The [synthetic example hour](https://noxfze.com/docs/example), or a copy of hours from storage you own.

## 1. Get the hours

The synthetic example hour and the hour before it:

```sh download
curl -fsSO https://noxfze.com/docs/example-hour.tar
tar -xf example-hour.tar
```

Go to the channel's directory and name the hour to read:

```sh setup
cd example-hour/recorder=r1/venue=example/channel=trades
HOUR=date=2026-01-06/hour=00
PREV=date=2026-01-05/hour=23
```

For your own record, copy hour directories of one channel from storage you own, keeping their
`date=YYYY-MM-DD/hour=HH` paths. Your provider's command-line tools copy a prefix as it is.

## 2. The table and its types

DuckDB reads the column types from the Parquet file itself. It also reads `date` and `hour` from the directory
names:

```sh check schema
duckdb -csv <<SQL
SELECT column_name, column_type
FROM (DESCRIBE FROM read_parquet('$HOUR/trades.parquet'));
SQL
```

```text output schema
column_name,column_type
raw_index,BIGINT
instrument,VARCHAR
trade_id,VARCHAR
side,VARCHAR
price,"DECIMAL(38,18)"
size,"DECIMAL(38,18)"
exchange_time,TIMESTAMP WITH TIME ZONE
receive_time,TIMESTAMP WITH TIME ZONE
date,DATE
hour,VARCHAR
```

Prices and sizes are exact decimals, as the venue sent them. The times are nanoseconds in the file. DuckDB's
`TIMESTAMP WITH TIME ZONE` holds microseconds, so read exact nanoseconds with pyarrow (step 5).

## 3. Read the trades

```sh check trades
duckdb -csv <<SQL
SET TimeZone = 'UTC';
SELECT raw_index, trade_id, side, price, size, receive_time
FROM read_parquet('$HOUR/trades.parquet')
ORDER BY raw_index;
SQL
```

```text output trades
raw_index,trade_id,side,price,size,receive_time
0,1003,buy,100.500000000000000000,0.250000000000000000,2026-01-06 00:00:05+00
1,1004,buy,100.500000000000000000,1.000000000000000000,2026-01-06 00:00:15+00
3,1005,buy,100.750000000000000000,2.000000000000000000,2026-01-06 00:00:45+00
```

`raw_index` is the line of the raw file that each row comes from, counted from 0. `receive_time` is that frame's
frame time, defined in [record format, version 1](https://noxfze.com/docs/format#raw-file).

## 4. Join each row to its frame

Each line of the raw file is one frame. This query numbers the lines and joins every row to its line, so a value in
the table sits next to the value in the venue's own payload:

```sh check join
duckdb -csv <<SQL
WITH frames AS (
  SELECT ordinality - 1 AS raw_index, payload, receipts
  FROM read_json('$HOUR/raw.jsonl.zst',
    format = 'newline_delimited') WITH ORDINALITY
)
SELECT f.raw_index AS line, t.trade_id, t.size,
  json_extract_string(f.payload, '/size') AS sent_size,
  list_transform(f.receipts, lambda r: r.replica) AS replicas
FROM frames f
LEFT JOIN read_parquet('$HOUR/trades.parquet') t USING (raw_index)
ORDER BY line;
SQL
```

```text output join
line,trade_id,size,sent_size,replicas
0,1003,0.250000000000000000,0.25,"[a, b]"
1,1004,1.000000000000000000,1.0,[a]
2,NULL,NULL,1.5,[b]
3,1005,2.000000000000000000,2.0,"[a, b]"
```

Line 2 has no row. It carries trade 1004 a second time, with another size, as replica `b` received it. The
manifest lists lines 1 and 2 under `conflicts`, and the raw file keeps both.

## 5. Exact nanoseconds with pyarrow

pyarrow reads the schema as the file states it, and the times at full precision:

```sh check pyarrow
python3 - "$HOUR" <<'PY'
import json
import sys

import pyarrow as pa
import pyarrow.parquet as pq

hour = sys.argv[1]
table = pq.read_table(f"{hour}/trades.parquet")
print(table.schema)
raw = pa.input_stream(f"{hour}/raw.jsonl.zst", compression="zstd")
frames = raw.read().decode().splitlines()
times = table["receive_time"].cast(pa.string())
for row, time in zip(table.to_pylist(), times):
    sent = json.loads(frames[row["raw_index"]])["payload"]
    print(row["raw_index"], time, json.loads(sent)["id"])
PY
```

```text output pyarrow
raw_index: int64 not null
instrument: string not null
trade_id: string
side: string
price: decimal128(38, 18) not null
size: decimal128(38, 18) not null
exchange_time: timestamp[ns, tz=UTC]
receive_time: timestamp[ns, tz=UTC] not null
0 2026-01-06 00:00:05.000000000Z 1003
1 2026-01-06 00:00:15.000000000Z 1004
3 2026-01-06 00:00:45.000000000Z 1005
```

## 6. Many hours as one table

A glob over the hour directories reads every hour as one table, with `date` and `hour` as columns:

```sh check hours
duckdb -csv <<SQL
SELECT date, hour, count(*) AS trades,
  min(trade_id) AS first, max(trade_id) AS last
FROM read_parquet('date=*/hour=*/trades.parquet')
GROUP BY ALL
ORDER BY ALL;
SQL
```

```text output hours
date,hour,trades,first,last
2026-01-05,23,2,1001,1002
2026-01-06,00,3,1003,1005
```

To read only hours that verify, run [command-line verification](https://noxfze.com/docs/verify) on them first. To see which hours are
partial and why, read their manifests, as [gaps, reconnections and replicas](https://noxfze.com/docs/gaps) shows.

## Other channels

The `book` channel holds `book_snapshots.parquet` and `book_deltas.parquet` in place of `trades.parquet`. They have
the same columns without `trade_id`, and `side` is `bid` or `ask`. The [record format](https://noxfze.com/docs/format#normalized-tables)
lists every column, and the raw file holds every field the tables do not define.