Skip to main content
clickhouse-local is a CLI with an embedded version of ClickHouse. It gives users the power of ClickHouse without having to install a server. In this guide, we will learn how to use a clickhouse-local database from chDB.

​
Setup

Let’s first create a virtual environment: 
python -m venv .venv
source .venv/bin/activate
And now we’ll install chDB. Make sure you have version 2.0.2 or higher: 
pip install "chdb>=2.0.2"
And now we’re going to install ipython: 
pip install ipython
We’re going to use ipython to run the commands in the rest of the guide, which you can launch by running: 
ipython

​
Installing clickhouse-local

Downloading and installing clickhouse-local is the same as downloading and installing ClickHouse. We can do this by running the following command: 
curl https://clickhouse.com/ | sh
To launch clickhouse-local with the data being persisted to a directory, we need to pass in a --path: 
./clickhouse -m --path demo.chdb

​
Ingesting data into clickhouse-local

The default database only stores data in memory, so we’ll need to create a named database to make sure any data we ingest is persisted to disk. 
CREATE DATABASE foo;
Let’s create a table and insert some random numbers: 
CREATE TABLE foo.randomNumbers
ORDER BY number AS
SELECT rand() AS number
FROM numbers(10_000_000);
Let’s write a query to see what data we’ve got: 
SELECT quantilesExact(0, 0.5, 0.75, 0.99)(number) AS quants
FROM foo.randomNumbers

┌─quants────────────────────────────────┐
│ [69,2147776478,3221525118,4252096960] │
└───────────────────────────────────────┘
Once you’ve done that, make sure you exit; from the CLI as only one process can hold a lock on this directory. If we don’t do that, we’ll get the following error when we try to connect to the database from chDB: 
ChdbError: Code: 76. DB::Exception: Cannot lock file demo.chdb/status. Another server instance in same directory is already running. (CANNOT_OPEN_FILE)

​
Connecting to a clickhouse-local database

Go back to the ipython shell and import the session module from chDB: 
from chdb import session as chs
Initialize a session pointing to demo.chdb: 
sess = chs.Session("demo.chdb")
We can then run the same query that returns the quantiles of numbers: 
sess.query("""
SELECT quantilesExact(0, 0.5, 0.75, 0.99)(number) AS quants
FROM foo.randomNumbers
""", "Vertical")

Row 1:
──────
quants: [0,9976599,2147776478,4209286886]
We can also insert data into this database from chDB: 
sess.query("""
INSERT INTO foo.randomNumbers
SELECT rand() AS number FROM numbers(10_000_000)
""")

Row 1:
──────
quants: [0,9976599,2147776478,4209286886]
We can then re-run the quantiles query from chDB or clickhouse-local.