Real-Time Market Analytics with ClickHouse A 90-minute hands-on lab

In the next 90 minutes you'll stand up your own ClickHouse Cloud service, load ~26.5 million foreign-exchange ticks from public cloud storage, run real market-analytics queries on it, and then let AI write and fix SQL for you.

Everything is copy-paste. Each query block has a Copy button — paste it into the SQL Console and run it. Green boxes tell you what you should see, so if you fall behind you can always catch up on your own. Let's go.

You need a running service before you can load data. If you already have one, skip ahead.

1. Go to clickhouse.cloud and sign up with email, Google, or GitHub. The current free trial includes $300 in credits for 30 days.
2. Verify your email if prompted.
3. Create one service. Any cloud/region is fine; pick the one closest to Thailand (e.g. ap-southeast-1 Singapore) for the snappiest experience.
4. Wait for the service to show as running, then open the SQL Console from the left-hand menu. This is where you'll run everything below.

First you'll 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 this matters in the last query of Step 4.

The data lives in a public S3 bucket, so no keys or credentials are needed — you just point at the URL. The transfer runs server-side from S3 to ClickHouse, so it does not stream 26.5 million rows through your laptop or depend on the venue Wi-Fi.

You'll load the same 26.5M ticks two different ways so you can see both. Method 1 is ClickPipes, the managed, click-through pipeline you'd use in production. Method 2 is a single SQL line — the fastest way to get data in during a demo. Do them in order; a TRUNCATE in between keeps the row count clean.

Method 1ClickPipes — the managed, production way

ClickPipes is a fully managed ingestion service: point it at object storage or a stream and it keeps loading, no connector to build. Here's the whole flow.

1. In the left menu, click Data sources, then the Create ClickPipe button.

2. Under Select the data source, choose Amazon S3 (top of the Popular list).

3. On Setup your ClickPipe connection, give it any name, set Authentication method → Public (the bucket is public), and paste this into S3 file path:

S3 file pathCopy

https://partner-workshop.s3.ap-southeast-1.amazonaws.com/fx/ticks.parquet

Leave Continuous ingestion off (this is a one-time file), then click Incoming data →.

4. On Incoming data, ClickHouse previews the matching file — you'll see fx/ticks.parquet at 158.43 MB. Confirm File type → Parquet and leave compression on Detect automatically, then click Parse information →.

5. On Parse information, ClickHouse previews a sample row and detects the columns. Under Upload data to, choose Existing table, pick the Database that holds your table (usually default), and set Table → forex. Check that the source fields (datetime, bid, ask, base, quote) line up with the matching columns — they should map automatically. Leave the extra _path / _file / _size fields unmapped. Then click Details and settings →.

6. On Details and settings, leave the default Permissions as-is (ClickPipes creates a dedicated writer user for you), then click Create ClickPipe.

7. You're taken back to Data sources, where your ClickPipe appears. In a few seconds its Status turns to Completed and Records shows 26,488,218 — all the ticks loaded from object storage.

Check what loaded. Run this in the SQL Console:

-- expect 26,488,218 ticks across 12 pairs
SELECT count() AS ticks, uniqExact(concat(base,'/',quote)) AS pairs FROM forex;

-- clear the rows ClickPipes just loaded so we don't double up
TRUNCATE TABLE forex;

-- load all ~26.5M ticks from the public S3 file in one line (server-side)
INSERT INTO forex
SELECT * FROM s3('https://partner-workshop.s3.ap-southeast-1.amazonaws.com/fx/ticks.parquet', NOSIGN, 'Parquet');

-- and check again (expect 26,488,218)
SELECT count() AS ticks, uniqExact(concat(base,'/',quote)) AS pairs FROM forex;

ClickPipes vs the s3() function — when to use which

Both got the same data in. The difference is what happens after the first load — whether you want a managed, ongoing pipeline or a quick one-shot read.

Rule of thumb: reach for ClickPipes when data keeps arriving and you want it managed; reach for s3() when you just want to pull a file in right now. Today you used the one-liner to get querying fast — now let's query it.

These are ClickHouse's "functions that replace a page of SQL." Paste each block, run it, and read the green box for what to expect. All of them run against the forex table you just loaded.

4.1  Approximate vs exact — the headline trick

How many distinct quote timestamps are in 26.5M ticks? Three functions answer the same question with different trade-offs. Run them one at a time and watch the timer in the query stats — running them separately is the whole point.

-- Approximate count of distinct timestamps (HyperLogLog)
SELECT uniq(datetime) AS distinct_ts FROM forex;

-- Exact count — precise, but scans every value
SELECT uniqExact(datetime) AS distinct_ts FROM forex;

-- Adaptive + tunable accuracy vs memory
SELECT uniqCombined(datetime) AS distinct_ts FROM forex;

4.2  Top-N in one function

The most actively quoted pairs — no GROUP BY / ORDER BY / LIMIT needed:

-- One row: an array of the 8 most actively quoted pairs (by tick count)
SELECT topK(8)(pair) AS most_active FROM forex;

4.3  Time-series in one pass — OHLC candlesticks

Daily open / high / low / close for gold — the bread-and-butter market query:

-- One row per day: gold (XAU/USD) open, high, low, close — a candlestick chart
SELECT
    toDate(datetime)      AS day,
    argMin(bid, datetime) AS open,    -- bid at the day's first tick
    max(bid)              AS high,
    min(bid)              AS low,
    argMax(bid, datetime) AS close    -- bid at the day's last tick
FROM forex
WHERE base = 'XAU' AND quote = 'USD'
GROUP BY day
ORDER BY day;

4.4  Percentiles without window gymnastics

The bid/ask spread is a liquidity gauge — and averages hide the tail, so use percentiles:

-- One row per pair: median and 99th-percentile bid/ask spread (tighter = more liquid)
SELECT
    pair,
    round(quantile(0.5)(ask - bid), 6)  AS median_spread,
    round(quantile(0.99)(ask - bid), 6) AS p99_spread
FROM forex
GROUP BY pair
ORDER BY median_spread ASC;

4.5  Combinators — one scan, two answers

Average spread during active vs quiet trading hours, side by side, in a single scan:

-- One row per pair: avg spread during the active window vs quiet hours
SELECT
    pair,
    count()                                                       AS ticks,
    round(avgIf(ask - bid, toHour(datetime) BETWEEN 7 AND 20), 6)     AS spread_active,
    round(avgIf(ask - bid, toHour(datetime) NOT BETWEEN 7 AND 20), 6) AS spread_quiet
FROM forex
GROUP BY pair
ORDER BY ticks DESC;

4.6  The latest value — argMax

The most recent quote for every pair, in one pass:

-- One row per pair: the latest quoted bid and the timestamp it was seen
SELECT
    pair,
    argMax(bid, datetime) AS last_bid,
    max(datetime)         AS as_of
FROM forex
GROUP BY pair
ORDER BY pair;

4.7  The finale — sort key vs no sort key

Same query shape — count the ticks matching a condition — but one filter hits the sort key and the other doesn't. First, run both counts:

-- Optimized: base+quote ARE the leading sort key — the index skips to that pair
SELECT count() FROM forex WHERE base = 'XAU' AND quote = 'USD';

-- Non-optimized: bid is NOT in the sort key — no index, scans all 26.5M ticks.
-- (cache off so the full scan shows on every run)
SELECT count() FROM forex WHERE bid > 1.5
SETTINGS use_query_condition_cache = 0;

-- Optimized: the primary index skips straight to the XAU/USD rows
EXPLAIN indexes = 1
SELECT count() FROM forex WHERE base = 'XAU' AND quote = 'USD';

-- Non-optimized: bid isn't in the sort key, so nothing can be skipped
EXPLAIN indexes = 1
SELECT count() FROM forex WHERE bid > 1.5;

ClickHouse Cloud has a built-in co-pilot, the ClickHouse Assistant, right in the SQL Console. It knows ClickHouse functions and best practices — not generic SQL.

1. Open the Assistant in the SQL Console (look for the assistant / sparkle icon).
2. Paste the non-optimized query from Step 4.7:
   SQL · the slow oneCopy

   SELECT count() FROM forex WHERE bid > 1.5;

3. Ask it, in plain English: "Why does this query scan the whole table, and how can I make it faster?"
4. Read its answer — it diagnoses why the query scans everything (bid isn't in the sort key), then proposes concrete ClickHouse fixes with ready-to-run SQL: a lightweight minmax skip index on bid, or a projection that keeps a copy of the data pre-sorted by bid.

ClickHouse Agents is the Claude-powered agent platform (currently in public beta). It answers questions in natural language — generating the SQL, running it, and drawing a chart. Open it from the SQL Console, or go directly to ai.clickhouse.cloud, and point it at the service containing the data you just loaded.

Try these, one at a time:

• "Show gold's (XAU/USD) daily closing price in January 2020 as a line chart."
• "Which currency pair had the widest average bid/ask spread?"
• "What were the highest and lowest EUR/USD prices, and when did they happen?"

Watch it generate the SQL, run it, and produce a visualization — then ask it a question of your own.

Want to feel the speed? Run a small dashboard on your own laptop that queries the forex table in your ClickHouse Cloud service. It draws an interactive candlestick + volume chart, and every time you change a filter it shows the ClickHouse query time and how many rows were scanned — usually a few milliseconds. That's the whole point: change the pair, drag the date range, and the data comes back instantly.

Step 1 — Install Docker

If you don't already have it, install Docker Desktop and start it. It includes the docker compose command you'll use below. Check it's running:

docker --version

Step 2 — Clone the repo

Clone the workshop repository and move into the dashboard folder:

git clone https://github.com/ClickHouse/ClickHouse_Demos.git
cd ClickHouse_Demos
git switch build-workshop-v1
cd workshops/RTA-mini-workshop/dashboard

Step 3 — Add your connection details

Copy the template to a real .env file, then open it in any text editor and fill in your service's details:

cp .env.example .env
# now edit .env and fill in the values below

Find these in the Cloud console under Connect (left menu) → HTTPS:

Step 4 — Run it

Build and start the container. The first run downloads the base image and installs dependencies (a minute or two); after that it's instant.

docker compose up --build

When you see it listening on port 8000, open http://localhost:8000 in your browser. Stop it later with Ctrl-C.

• Your trial keeps running for 30 days. Come back, load your own data, and keep exploring — the $300 in credits is yours.
• Try your own dataset. Point a ClickPipe at your own S3 bucket or a live feed, or drop a CSV/Parquet via Data sources → Upload file.
• Go deeper on the functions. Everything you ran today — uniq*, topK, argMin/argMax, quantile, the -If combinators — is documented at clickhouse.com/docs.
• Scan the QR / link on the closing slide to stay in touch and to keep your service going after the trial.

If you got stuck at any point