Skip to content
Tutorials Center
My learning

Topics

Lesson 7 of 35 ยท Architecture and Setup

Clients, HTTP and Data Formats

Talk to ClickHouse with clickhouse-client, the HTTP interface and clickhouse-local, and learn the input and output formats: CSV, TSV, JSONEachRow, Parquet and Pretty.

ClickHouse speaks several protocols and dozens of data formats. Knowing the handful you will use daily (the command-line client, the HTTP interface, and formats like CSV and JSONEachRow) makes loading data, exporting results and wiring up applications straightforward. This lesson covers each, with the commands as read-only snippets and SQLite stand-ins for the format ideas.

ClickHouse cannot run on this site. Shell commands and ClickHouse statements are read-only; the runnable blocks show how CSV and JSON lines look and how to shape result sets in SQLite.

clickhouse-client

The interactive client uses the native protocol (port 9000). Common options:

Terminal
clickhouse-client                                  # local server, user default
clickhouse-client --host db.example.com --port 9000 --user analyst --password '...'
clickhouse-client --secure --host my-service.example.com   # TLS
clickhouse-client --query "SELECT count() FROM events"      # one-shot query
clickhouse-client --multiquery < setup.sql                  # run a script
clickhouse-client --format PrettyCompact -q "SELECT * FROM system.numbers LIMIT 3"
clickhouse-client --time --progress -q "SELECT ..."        # show timing

Inside the client, a few handy habits: the up-arrow keeps history, Ctrl+C cancels a running query, and ending a statement with \G prints each row vertically (like the FORMAT Vertical format), which is perfect for wide system tables.

The HTTP interface

Every ClickHouse server also answers HTTP on port 8123. This is how most applications and BI tools connect, and it works with curl:

Terminal
# SELECT (GET works, but only for read-only queries)
curl 'http://localhost:8123/?query=SELECT+1'

# POST the query as the body
curl --data-binary 'SELECT count() FROM events' http://localhost:8123/

# INSERT: query in the URL, data in the body
curl 'http://localhost:8123/?query=INSERT+INTO+events+FORMAT+CSV' \
     --data-binary @events.csv

# pick the output format and credentials
curl -u analyst:secret \
  'http://localhost:8123/?default_format=JSON&query=SELECT+version()'

Use POST for anything with a body and for writes; a GET request is treated as read-only. Parameters such as database=, max_execution_time= or readonly= can be added to the URL. For queries that include user values, use parameterized queries instead of string concatenation:

SQL
SELECT count() FROM events WHERE country = {country:String} AND day >= {since:Date}
Terminal
curl 'http://localhost:8123/?param_country=PT&param_since=2026-05-01' \
     --data-binary 'SELECT count() FROM events WHERE country = {country:String} AND day >= {since:Date}'

The server validates and escapes the values by their declared types, which avoids SQL injection.

clickhouse-local: SQL on files

clickhouse-local runs the full engine as a throwaway process, no server needed:

Terminal
clickhouse-local --query "
  SELECT country, count() AS n
  FROM file('visits.csv', CSVWithNames)
  GROUP BY country ORDER BY n DESC"

# pipe data in
cat visits.csv | clickhouse-local \
  --input-format CSVWithNames \
  --structure "ts DateTime, country String, ms UInt32" \
  --query "SELECT country, avg(ms) FROM table GROUP BY country"

It is excellent for log analysis, for converting between formats (CSV to Parquet) and for trying a query on a sample before loading it into a server. The functions that read files and remote sources (file, url, s3) are called table functions; see Integrations later in the track.

Formats

A format says how rows are written on the wire or in a file. You choose one with FORMAT at the end of a query, in an INSERT ... FORMAT, or with the HTTP parameter default_format.

Format Use it for
TabSeparated (TSV) Default text format; fast, simple
TabSeparatedWithNames TSV with a header row
CSV, CSVWithNames Spreadsheets and exports
JSONEachRow One JSON object per line; the most common ingestion format
JSON One object with meta, data, rows and statistics
JSONCompact JSON with rows as arrays
Parquet, ORC, Avro, Arrow Columnar and big-data interchange
Native ClickHouse's fastest binary format
Pretty, PrettyCompact, Vertical Human-readable terminal output
Values (1, 'a'), (2, 'b') like an INSERT ... VALUES
Null Run the query, discard the output (benchmarking)
SQL
SELECT page, count() AS views
FROM events
GROUP BY page
ORDER BY views DESC
LIMIT 3
FORMAT JSONEachRow;
-- {"page":"/home","views":"42"}  (64-bit integers are quoted by default)

Note that UInt64 and Int64 values appear as quoted strings in the JSON formats by default, because JavaScript numbers lose precision beyond 2^53. The setting output_format_json_quote_64bit_integers = 0 turns that off.

Loading files

Terminal
clickhouse-client -q "INSERT INTO events FORMAT CSVWithNames" < events.csv
clickhouse-client -q "INSERT INTO events FORMAT JSONEachRow"  < events.ndjson
clickhouse-client -q "INSERT INTO events FORMAT Parquet"      < events.parquet

Or from SQL, reading a file visible to the server:

SQL
INSERT INTO events
SELECT * FROM file('events.csv', CSVWithNames);

Useful tolerance settings for messy input: input_format_allow_errors_num (skip a number of bad rows), input_format_skip_unknown_fields (ignore extra JSON keys) and date_time_input_format = 'best_effort' (parse many date styles).

See formats with stand-ins

SQLite has no FORMAT clause, but it can produce CSV-like and JSON-lines text, which is exactly what ingestion formats look like.

SQL Stand-in: JSONEachRow-style lines (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

SQL Stand-in: CSV text from a query (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

Which interface for which job

flowchart TB
    A["Your tool"] --> B{"What are you doing?"}
    B -->|"Exploring by hand"| C["clickhouse-client"]
    B -->|"App or BI tool"| D["HTTP 8123 or a driver"]
    B -->|"Files, no server"| E["clickhouse-local"]
    B -->|"Loading a big file"| F["INSERT ... FORMAT via client or HTTP"]

Pick the interface by task, not by habit.

Official drivers exist for Python, Go, Java, JavaScript/TypeScript, Rust and more; they all use HTTP or the native protocol underneath. Reads and writes use the same SQL, so what you learn at the client carries over.

Common beginner mistakes

  • Sending a write with GET. It is rejected as read-only.
  • Building SQL by concatenating user input instead of using {name:Type} parameters.
  • Reading UInt64 as a number from JSON output and being surprised by quoted strings.
  • Loading a huge file with VALUES rather than a file format.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which format is the most common for ingesting one JSON object per line?

    JSONEachRow expects one JSON object per line and is widely used for streams of events.
  2. 2. Which tool runs SQL on a local CSV file with no server?

    clickhouse-local runs the full engine as a command-line process.
  3. 3. Why does JSON output show 64-bit integers as quoted strings by default?

    Quoting avoids precision loss in JSON parsers that use 64-bit floats.
  4. 4. What is the safe way to put a user-supplied value into a query over HTTP?

    The server validates and escapes parameters by their types, preventing injection.

Summary

  • clickhouse-client (native, port 9000) is for humans; HTTP (port 8123) is for applications; clickhouse-local queries files without a server.
  • Formats such as CSV, JSONEachRow, Parquet, Native and Pretty are chosen with FORMAT.
  • Use POST for writes and parameterized queries for user input.
  • 64-bit integers are quoted in JSON output unless you change the setting.

Next: Settings, users and configuration