Step-by-Step Guide to Insert Data from a JSON 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 JSON file correctly using the CLI, including handling header rows and using MergeTree engine for efficient querying.

 

Let’s create orders.json file.

 

orders.json

{"order_id": 1001, "customer_name": "Ram", "amount": 250.75, "order_date": "2024-06-01"}
{"order_id": 1002, "customer_name": "Krishna", "amount": 145.00, "order_date": "2024-06-03"}
{"order_id": 1003, "customer_name": "Chamu", "amount": 320.00, "order_date": "2024-06-04"}

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 orders table.

USE demo_db;

CREATE TABLE IF NOT EXISTS orders
(
    order_id UInt32,
    customer_name String,
    amount Float64,
    order_date Date
)
ENGINE = MergeTree
ORDER BY order_id;

krishna :) USE demo_db;

USE demo_db

Query id: 3e19bba2-86a7-4690-b2b8-aa81a37780ae

Ok.

0 rows in set. Elapsed: 0.002 sec. 

krishna :) 
krishna :) 
krishna :) CREATE TABLE IF NOT EXISTS orders
(
    order_id UInt32,
    customer_name String,
    amount Float64,
    order_date Date
)
ENGINE = MergeTree
ORDER BY order_id;

CREATE TABLE IF NOT EXISTS orders
(
    `order_id` UInt32,
    `customer_name` String,
    `amount` Float64,
    `order_date` Date
)
ENGINE = MergeTree
ORDER BY order_id

Query id: f440335b-dd78-4593-8765-267b8e1e5a3c

Ok.

0 rows in set. Elapsed: 0.009 sec.

Step 4: Insert Data from JSON File

Now use the following command to insert the JSON file into ClickHouse.  

clickhouse-client \
  --query "INSERT INTO demo_db.orders FORMAT JSONEachRow" \
  < orders.json

   

Step 5: Query orders table to confirm the data insertion.

 

SELECT * FROM demo_db.orders;

krishna :) SELECT * FROM demo_db.orders;

SELECT *
FROM demo_db.orders

Query id: 27886ca3-e10f-48b2-9b19-77ac912289c6

   ┌─order_id─┬─customer_name─┬─amount─┬─order_date─┐
1. │     1001 │ Ram           │ 250.75 │ 2024-06-01 │
2. │     1002 │ Krishna       │    145 │ 2024-06-03 │
3. │     1003 │ Chamu         │    320 │ 2024-06-04 │
   └──────────┴───────────────┴────────┴────────────┘

3 rows in set. Elapsed: 0.004 sec.

 

  

Previous                                                    Next                                                    Home