02 Create the table

One CREATE TABLE for 26.5M forex ticks, and why base and quote lead the sort key — the decision you will feel in module 04.

First create an empty table to hold the forex ticks. Open the SQL Console, paste the statement below, and run it.

-- 26.5M forex ticks (12 pairs incl. gold, Jan 2020); base+quote+time lead the sort key
CREATE TABLE forex
(
    datetime DateTime64(3, 'UTC'),    -- millisecond quote timestamp
    bid      Float64,
    ask      Float64,
    base     LowCardinality(String),  -- e.g. 'EUR', 'XAU' (gold)
    quote    LowCardinality(String),  -- e.g. 'USD', 'JPY'
    pair     String  ALIAS concat(base, '/', quote),   -- handy label, computed on read
    spread   Float64 ALIAS ask - bid,                  -- bid/ask spread
    mid      Float64 ALIAS (bid + ask) / 2             -- mid price
)
ENGINE = MergeTree
ORDER BY (base, quote, datetime);

Why this shape? base and quote lead the ORDER BY — the table's sort key. That means filtering by a currency pair, and a time range, lets ClickHouse skip almost all the data. You'll see exactly how much that matters in the last query of module 04.

You should see

Ok. — the table is created and empty. Next you'll fill it.