Step-by-Step Guide to Insert Data from a CSV File into ClickHouse Table Using clickhouse-client

  

Loading data from files into ClickHouse is a common task in data engineering workflows, especially when dealing with pre-processed datasets, exported reports, or external system dumps. ClickHouse supports ingesting data directly from local files using the clickhouse-client with input redirection (<) and the appropriate FORMAT (like CSV, TSV, etc.). This method is fast, scriptable, and ideal for batch operations. However, it’s important to ensure that the file content must matche the table schema and format expectations. This guide demonstrates how to set up a ClickHouse table and load data from a CSV file correctly using the CLI, including handling header rows and using MergeTree engine for efficient querying.

 

Let’s create users.csv file.

 

users.csv

id,name,email,signup_date
1,Ram Gurram,ram@example.com,2024-01-01
2,Krishna Majery,krishna@example.com,2024-01-05
3,Sailu Dokku,sailu@example.com,2024-01-10

   

Follow below step-by-step procedure to insert users.csv content into Clickhouse table.

 

Step 1: Connect to Clickhouse server by executing following statement in the terminal.

  

clickhouse-client

$clickhouse-client 
ClickHouse client version 25.5.1.1919 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 25.5.1.

Warnings:
 * Maximum number of threads is lower than 30000. There could be problems with handling a lot of simultaneous queries.

krishna :)

   

Step 2: Create database demo_db

Execute following statement to create demo_db database.

 

CREATE DATABASE IF NOT EXISTS demo_db;

krishna :) CREATE DATABASE IF NOT EXISTS demo_db;

CREATE DATABASE IF NOT EXISTS demo_db

Query id: b0c94453-a9c0-45d6-a17d-a630fd62311d

Ok.

0 rows in set. Elapsed: 0.006 sec. 

krishna :) 
krishna :) 
krishna :) SHOW DATABASES;

SHOW DATABASES

Query id: 624348a6-077d-4304-a5d6-1af3db7012a1

   ┌─name───────────────┐
1. │ INFORMATION_SCHEMA │
2. │ default            │
3. │ demo_db            │
4. │ information_schema │
5. │ system             │
   └────────────────────┘

5 rows in set. Elapsed: 0.003 sec. 

   

Step 3: Create a Table

Execute following statements to create users table.

USE demo_db;

CREATE TABLE IF NOT EXISTS users
(
    id UInt32,
    name String,
    email String,
    signup_date Date
)
ENGINE = MergeTree
ORDER BY id;

krishna :) USE demo_db;

USE demo_db

Query id: 6aa6567f-920c-4e9f-9270-87fdb9968bd0

Ok.

0 rows in set. Elapsed: 0.002 sec. 

krishna :)
krishna :) CREATE TABLE IF NOT EXISTS users
(
    id UInt32,
    name String,
    email String,
    signup_date Date
)
ENGINE = MergeTree
ORDER BY id;

CREATE TABLE IF NOT EXISTS users
(
    `id` UInt32,
    `name` String,
    `email` String,
    `signup_date` Date
)
ENGINE = MergeTree
ORDER BY id

Query id: 12987cff-75bc-45ec-9378-c0e4ba59f2c7

Ok.

0 rows in set. Elapsed: 0.015 sec. 

krishna :) 
krishna :) 
krishna :) SHOW TABLES;

SHOW TABLES

Query id: 0ffd6ef3-ac47-4b70-9c31-57ce0eb2247f

   ┌─name──┐
1. │ users │
   └───────┘

1 row in set. Elapsed: 0.004 sec. 

krishna :)

   

Step 4: ClickHouse does not support CSVs with a header row by default. You must either remove it or tell ClickHouse to skip it using the input_format_csv_skip_first_lines setting.

 

clickhouse-client \
  --query "INSERT INTO demo_db.users FORMAT CSV" \
  --input_format_csv_skip_first_lines=1 \
  < users.csv

   

Step 5: Upon successful insertion you can query users table to see the data.

 

krishna :) SELECT * FROM demo_db.users;

SELECT *
FROM demo_db.users

Query id: 208b0025-92f8-4eb6-b7b1-220568e8f457

   ┌─id─┬─name───────────┬─email───────────────┬─signup_date─┐
1. │  1 │ Ram Gurram     │ ram@example.com     │  2024-01-01 │
2. │  2 │ Krishna Majery │ krishna@example.com │  2024-01-05 │
3. │  3 │ Sailu Dokku    │ sailu@example.com   │  2024-01-10 │
   └────┴────────────────┴─────────────────────┴─────────────┘

3 rows in set. Elapsed: 0.009 sec. 

 I’ll explain how to insert JSON data into a ClickHouse table in my next post.

 

Previous                                                    Next                                                    Home