Parse url-encoded form data with SQL

To parse url-encoded form data and query its fields with SQL, use clickhouse local. It runs SQL directly on files from the command line, with no server to install. It's part of ClickHouse, so the same query scales to billions of rows when you outgrow your laptop.

Install it with clickhousectl:

Then point it at the form body with the Form format:

The Form format reads one application/x-www-form-urlencoded body and turns each key=value pair into a column, with the file read in place and no import step. One body becomes one queryable row.

Every field comes back as a String #

A form body carries no type information; it is all text after the =. So Form infers every column as Nullable(String). DESCRIBE confirms it:

The keys became column names, which is what makes the format pleasant to work with. If you want to compute on a field (sum the amounts, filter by a numeric id), give it a real type by passing an explicit structure as the third argument to file():

Now user_id is an integer and amount is a decimal, ready for arithmetic.

The gotcha: percent-encoding is decoded, but + is not #

This is the one thing to know about Form. Real form bodies escape special characters: a space becomes + or %20, an ampersand becomes %26, accented letters become percent sequences like %C3%A3. Form decodes the percent escapes for you, but it does not convert a literal + back into a space. Watch what comes out of a body that uses both:

The body was name=Jane+Doe&city=S%C3%A3o+Paulo&note=a%26b%3Dc&amount=19.99:

%C3%A3 decoded to ã, %26 to & and %3D to = (so the & and = inside note did not split into new fields). But Jane+Doe kept its +. If your source encodes spaces as +, fix it in the query with replaceAll:

One row, cleaned, no scripting.

One row per file, so glob a folder of them #

Because a Form file is exactly one row, a directory of captured webhook bodies is a table waiting to happen. Point file() at a glob and ClickHouse reads each file as a row. The virtual _file column tells you which body each row came from:

From there the full SQL surface applies (WHERE, GROUP BY, aggregates, joins) across the whole folder at once:

That turns a heap of raw POST bodies on disk into an answer. For more on the glob pattern across formats, see run SQL across multiple files.

How fast is it? #

The cost here is opening many small files, not crunching numbers. On a folder of 2,000 one-body form files, parsing every file and grouping by plan runs in:

~0.18 seconds, best of three with the files warm in the OS page cache, on an Apple M4 Pro laptop (14 cores, 24 GB RAM; clickhouse local 26.6.1.117). That includes opening all 2,000 files from scratch on every run — there is no cached table.

The same SQL scales unchanged #

The query you ran against a folder of form bodies is ordinary ClickHouse SQL. When those webhook captures grow past what a laptop folder can hold, you swap the file() glob for a table (or an s3() glob over a bucket) and the SELECT ... GROUP BY stays exactly the same. You prototype against files on your machine and ship the identical logic to a ClickHouse server or to ClickHouse Cloud.

Run it yourself #

The complete, runnable example lives here. It has generate.sh (creates the sample bodies and the 2,000-file perf set), run.sh (every command above), and expected_output.txt:

github.com/ClickHouse/examples → local-analytics/clickhouse-local-form

Related #

• What is clickhouse-local?
• Run SQL across multiple files
• Run SQL on a JSON file
• Run SQL on a CSV file