Loading CSV Into ClickHouse From the Command Line Fast

ClickHouse is the odd one among the warehouses in this series. There is no staging step and no load job: you insert rows, and a CSV file is just one of the formats the insert can read. The command line client reads the file from standard input and sends it to the server, which parses and stores it. That makes the shortest path to loading a CSV very short, and it moves the questions to parsing settings, batch size and the table's sort key. This article shows the command, the settings that matter, how to look at a file before loading it and what to watch for with errors. It belongs with the engines covered in loading CSV into cloud warehouses.

The table first

Create the table with the types and the sort key you want. A MergeTree table keeps data sorted by its ORDER BY expression, and queries that filter on a prefix of that key are fast:

CREATE TABLE orders
(
    order_id   UInt64,
    customer   String,
    zip        FixedString(5),
    placed_on  Date,
    total      Decimal(12, 2),
    note       String
)
ENGINE = MergeTree
ORDER BY (placed_on, order_id);

Two type choices matter for CSV. zip is text (FixedString(5) or String), so 02134 stays whole. total is a Decimal and not Float64, so cents stay exact. Date reads YYYY-MM-DD. For a column with few distinct values, such as a status, LowCardinality(String) saves space and speeds filters. Decide the sort key now, because changing it later means creating a new table and copying the data.

The insert

With a header in the file, CSVWithNames reads it and maps columns by name:

clickhouse-client --query="INSERT INTO orders FORMAT CSVWithNames" < orders.csv

The client reads standard input and streams it to the server as the insert body. FORMAT CSV takes the same data without a header and maps by position, so with CSV and a file that has a header, the header is treated as a data row and fails to parse. CSVWithNamesAndTypes expects two header lines, names and types, the form ClickHouse itself writes. Compressed input works too; the client can read a gzip if you decompress into the pipe (gunzip -c orders.csv.gz | clickhouse-client --query=...), and the server detects compression in some cases when reading from a file with a table function.

The dialect is controlled by settings, which you can append to the command as flags:

clickhouse-client \
  --format_csv_delimiter=';' \
  --input_format_allow_errors_num=10 \
  --date_time_input_format=best_effort \
  --query="INSERT INTO orders FORMAT CSVWithNames" < orders-semicolon.csv

format_csv_delimiter sets the separator (a single character). input_format_allow_errors_num is the number of malformed rows to skip, and input_format_allow_errors_ratio is the same as a fraction; both default to zero, so the first bad row fails the whole insert. date_time_input_format=best_effort makes the parser accept more date and time layouts than the strict YYYY-MM-DD hh:mm:ss, at the price of accepting ambiguous ones: 03/04/2026 may be read in a way you do not intend, so prefer to give ClickHouse ISO 8601 and keep the strict mode. format_csv_null_representation names the string that loads as NULL for Nullable columns (the default is \N), and input_format_null_as_default turns a NULL into the column's default for non-Nullable ones. Empty fields in a non-Nullable String column load as empty strings, and for numbers they fail unless the empty-as-default setting is on.

Look at the file first

clickhouse-local is the ClickHouse engine as a command, with no server. It reads files and runs SQL on them, which makes it the right tool to check a file before it goes anywhere:

clickhouse-local --query "
  SELECT count() AS n, sum(total) AS total, min(placed_on) AS first_day, max(placed_on) AS last_day
  FROM file('orders.csv', CSVWithNames)"

The function file() reads the CSV and infers a schema from the first rows. You can see what it inferred with DESCRIBE file('orders.csv', CSVWithNames), and then override it by passing a structure as the third argument, for instance 'order_id UInt64, customer String, zip String, ...'. Inference reads a limited number of rows, so a zip code column with no leading zeros in the sample comes back as an integer, the same trap as with every inferring reader (why a CSV file has no types and what that costs you). State the types when you check, as you will when you load. The same function family reads from object storage: s3('https://bucket.s3.amazonaws.com/orders.csv', 'CSVWithNames') is a table function that you can INSERT INTO ... SELECT from, which loads straight from a bucket without the client in the path.

Batch size

ClickHouse stores each insert as a new part on disk and merges parts in the background. Many tiny inserts create many tiny parts, the merges fall behind and eventually the server slows or refuses inserts with a "too many parts" error. The documentation's guidance is to insert in batches of at least a thousand rows and preferably tens of thousands to hundreds of thousands, and not more often than about once a second per table. A CSV file is already a batch, so loading one file per insert is usually fine; the case to avoid is a loop that sends a file of five rows every second.

For a producer that cannot batch, there are asynchronous inserts (async_insert=1), where the server buffers small inserts and writes them together. They solve the many-parts problem and change the guarantee: the insert may return before the data is durable, depending on settings, so read the documentation before using them for data you cannot lose. For a large file, you can also split it and run several inserts in parallel, and check that the client's connection, and not the server, is the bottleneck (splitting a large CSV into smaller files).

Errors and repeats

A failed parse reports the row and the column, and the message shows the text around the failure, including the expected delimiter or type. The common causes are the same as elsewhere: a header read as data (CSV where you needed CSVWithNames), a delimiter that does not match, a field with an unescaped quote and a date in a layout the strict parser rejects. Use clickhouse-local or a small head of the file to reproduce, fix the setting and retry.

Repeating a load is the risk. A plain MergeTree table keeps every row you insert, so loading the same file twice doubles it. For a replicated table, ClickHouse can deduplicate identical insert blocks within a window, which protects against a retry of the same insert and not against a deliberate second load of a re-exported file. Do not design around it: keep a ledger of loaded files by content hash, as in idempotent CSV imports that are safe to run twice. If corrections must replace old rows, ReplacingMergeTree collapses rows with the same sorting key during background merges, with the caveat that duplicates remain visible until the merge or unless you query with FINAL; it is eventual, not immediate.

When a CSV load is the wrong insert path

ClickHouse is excellent at consuming files and equally happy reading Parquet, which carries types and compresses better, so if you control the producer, send Parquet (loading CSV into cloud warehouses shows the size difference). For event streams, use the Kafka engine or an insert from the application in large batches. For mutable data, where rows are updated and deleted frequently, ClickHouse is the wrong database to load a CSV into, since updates are expensive, and a row store is a better home.

When the data is a one-off analysis, you may not need the server at all: clickhouse-local answers the question from the file, with the same SQL and the same speed. The format settings are documented in ClickHouse's CSV format page and the tool in its clickhouse-local guide.