Getting Started with ClickHouse Using clickhouse-local

Share on Social

What if you could run ClickHouse SQL queries without setting up a server or managing storage? That’s exactly what clickhouse-local is for.

clickhouse-local transforms the ClickHouse query engine into a lightweight command-line utility. It allows you to:

Query files directly (CSV, TSV, Parquet, JSON, etc.).
Use ClickHouse’s full SQL dialect and built-in functions.
Perform transformations on the fly, without first importing data into a database.

In short: it’s a great way to explore datasets quickly, prototype queries, or use SQL in shell pipelines.

Table of Contents

How Does clickhouse-local differ from clickhouse-client ?

clickhouse-client connects to a running ClickHouse server, allowing you to interact with existing databases and tables.
clickhouse-local runs entirely without a server. It reads data from files (or stdin, or URLs), applies SQL queries, and outputs results directly to your terminal or pipeline.

This makes clickhouse-local particularly useful for one-off queries, file exploration, or quick data analysis scripts.

Installing and Running clickhouse-local

On macOS, you can install ClickHouse with Homebrew:

brew install clickhouse
brew services start clickhouse # if you also want to run the server

Once installed, you can launch clickhouse-local:

clickhouse-local

You’ll see something like:

ClickHouse local version 22.9.3.18 (official build).
: )

Example 1: Counting Words in a Text File

Suppose you have a text file book.txt and want to find the top 10 most frequent words.

cat book.txt | tr -s ' ' '\n' | \
clickhouse-local \
--structure "word String" \
--query "SELECT word, count() AS cnt 
FROM table 
WHERE word != '' 
GROUP BY word 
ORDER BY cnt DESC 
LIMIT 10 FORMAT Pretty"

This gives you a quick word frequency analysis using SQL, without loading the file into a database.

Example 2: Analyzing a JSON File

Imagine you have an application log stored as JSON lines in logs.json. Each row has fields like timestamp, level, and message.

clickhouse-local \
--file=logs.json \
--input-format=JSONEachRow \
--structure="timestamp DateTime, level String, message String" \
--query="SELECT level, count() AS events 
FROM table 
GROUP BY level 
ORDER BY events DESC"

This instantly tells you how many log entries exist per severity level.

Example 3: Querying Remote Data from a URL

You don’t even need local files—clickhouse-local can fetch data from a remote source. For example, let’s query a JSON API that returns cryptocurrency prices:

clickhouse-local \
--query="SELECT symbol, priceUsd 
FROM url('https://api.coincap.io/v2/assets','JSONEachRow','symbol String, priceUsd Float64') 
ORDER BY priceUsd DESC 
LIMIT 5 FORMAT PrettyCompact"

The result gives you the top 5 cryptocurrencies by price, straight from the API, without any extra scripting.

Why Use clickhouse-local ?

Here’s where it shines:

Exploratory analysis – Quickly check file contents with SQL.
Data preprocessing – Transform data before loading it into ClickHouse (or another system).
Scripting and pipelines – Combine with shell tools like awk, sed, or jq.
Performance – Faster than SQLite-based command-line SQL tools like q or textql.

Conclusion

clickhouse-local is a hidden gem in the ClickHouse ecosystem. It gives you:

The power of ClickHouse SQL without running a server.
The ability to query local files, remote files, and even URLs.
Seamless integration with shell pipelines for ad-hoc data processing.

Whether you’re exploring a CSV, analyzing logs, or fetching real-time API data, clickhouse-local offers a fast, flexible, and lightweight way to do it all.

If you’re new to ClickHouse, clickhouse-local is one of the best entry points—it lets you experiment with SQL and ClickHouse functions immediately, no setup required.

Share on Social