Menu
UAQ --:--:--

Documentation

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.

View as Markdown

What you need

1. Get the hours

The synthetic example hour and the hour before it:

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:

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:

check schema
duckdb -csv <<SQL
SELECT column_name, column_type
FROM (DESCRIBE FROM read_parquet('$HOUR/trades.parquet'));
SQL
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

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
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.

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:

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
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:

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
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:

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
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 on them first. To see which hours are partial and why, read their manifests, as gaps, reconnections and replicas 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 lists every column, and the raw file holds every field the tables do not define.

Nox works in writing. Email contact@noxfze.com with your venue and the instruments to record.

Open mail ↗

Copied to clipboard