03 Load the data — two ways

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 1 — ClickPipes, the managed, production way

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

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

Data sources is also where "Upload file" and "Add sample data" live.

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:

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

The screenshot shows the presenter's techthai database — use whichever one holds your table.

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

No Spark job, no custom loader.

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;

Method 2 — the s3() one-liner, fastest in a demo

Now load the exact same data with a single SQL statement, straight from the public file. First empty the table so the count doesn't double, then insert:

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

NOSIGN means "no credentials" — that's all it takes to read a public bucket. SELECT * just works because the table's column order matches the file.

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

Same 26,488,218 rows, loaded two ways. 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.