How to Import CSV Files into ClickHouse

How do I import a CSV file into ClickHouse?

Imports rows from a local or remote CSV file into a ClickHouse table using INSERT … FORMAT CSV or clickhouse-client/clickhouse-local.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

How do I import a CSV file into ClickHouse quickly?

Pipe the file into clickhouse-client and run INSERT INTO table FORMAT CSV. This streams rows directly, bypassing intermediate parsing tools.

What is the basic CLI syntax?

Use cat file.csv | clickhouse-client --query "INSERT INTO db.table FORMAT CSV". Replace db.table with your actual table path.

Can I load a file without a running server?

Yes. clickhouse-local lets you create a temporary table and import CSV without connecting to a cluster, ideal for ad-hoc analysis.

How do I map CSV columns to table columns?

Order matters. CSV columns must appear in the same sequence as the table definition or as explicitly listed after INSERT INTO table (col1,col2).

How do I handle headers in the CSV?

If the file has a header row, load it with FORMAT CSVWithNames. ClickHouse treats the first line as column names and ignores it during insertion.

What about type mismatches?

ClickHouse will refuse to insert rows that can’t be cast to the column type. Cast or clean data beforehand, or store raw strings then transform.

Is batch size configurable?

Yes. Set --max_insert_block_size when invoking clickhouse-client. Larger blocks speed up imports at the cost of memory.

Can I compress input on the fly?

Pipe a gzipped file through gunzip -c before the client. Example: gunzip -c orders.csv.gz | clickhouse-client ….

How do I verify the load?

Run SELECT count(*) FROM table before and after the import, or compare against the expected row count from wc -l file.csv.

Best practices for large CSV imports?

Use FORMAT CSV with streaming, disable compression on low-powered machines, and temporarily increase max_partitions_to_read and max_insert_threads if needed.

Why How to Import CSV Files into ClickHouse is important

How to Import CSV Files into ClickHouse Example Usage


# Load new product prices
cat new_prices.csv | clickhouse-client \
  --query "INSERT INTO Products (id, price) FORMAT CSV"

How to Import CSV Files into ClickHouse Syntax


# Import entire CSV
cat customers.csv | clickhouse-client \
  --query "INSERT INTO ecommerce.Customers FORMAT CSV"

# Import selected columns in a custom order
cat orders_items.csv | clickhouse-client \
  --query "INSERT INTO ecommerce.OrderItems (order_id, product_id, quantity) FORMAT CSV"

# Skip header row
cat products.csv | clickhouse-client \
  --query "INSERT INTO ecommerce.Products FORMAT CSVWithNames"

# Local, server-less load for quick checks
clickhouse-local -S "id UInt32, name String, price Float32, stock UInt16" \
  -i products.csv --query "SELECT count(*) FROM table"

Common Mistakes

Using INSERT VALUES for large files. It sends one row per statement, drastically slowing down imports. Fix by piping the file into INSERT … FORMAT CSV.
Leaving the header row in a plain CSV import. ClickHouse tries to parse the header as data and fails. Fix by switching to FORMAT CSVWithNames or removing the header before loading.