What it is
The clickhouse-client is a command-line interface for interacting with ClickHouse databases, used for running SQL queries, managing tables, and performing administrative tasks.
Installation
Linux (using deb packages)
sudo apt-get update
sudo apt-get install clickhouse-client
macOS (using Homebrew)
brew install clickhouse
Windows
Download the appropriate .deb or .rpm package and extract it, or use a package manager like Chocolatey:
choco install clickhouse
Core Concepts
Databases
A logical grouping of tables. You can create, drop, and switch between databases.
Tables
The fundamental structure for storing data, organized into columns with defined data types.
Queries
SQL statements used to retrieve, insert, update, or delete data, as well as manage schema.
Settings
Client-side and server-side configurations that affect query execution and output.
Commands / Usage
Connecting to ClickHouse
Default connection (localhost:9000)
clickhouse-client
Connects to the ClickHouse server running on localhost with the default port 9000, using the default user default and no password.
Specifying host and port
clickhouse-client --host play.clickhouse.com --port 9440
Connects to play.clickhouse.com on port 9440.
Specifying user and password
clickhouse-client --user myuser --password mypassword
Connects using myuser and mypassword.
Specifying host, port, user, and password
clickhouse-client --host 192.168.1.100 --port 9000 --user admin --password secret
Connects to a specific server with credentials.
Using a config file
clickhouse-client --config-file ~/.clickhouse-client.xml
Connects using settings defined in a specified configuration file.
Running SQL Queries
Execute a single query
clickhouse-client --query "SELECT 1"
Executes the SELECT 1 query and prints the result.
Execute multiple queries from a file
clickhouse-client < /path/to/my_queries.sql
Reads and executes all SQL statements from the specified file.
Execute queries interactively
clickhouse-client
Starts an interactive session where you can type SQL commands one by one.
Setting query parameters
clickhouse-client --param name=value --query "SELECT {name}"
Passes parameters to be used within queries, useful for dynamic queries.
Managing Databases
List all databases
SHOW DATABASES;
Displays a list of all available databases.
Create a database
CREATE DATABASE my_database;
Creates a new database named my_database.
Switch to a database
USE my_database;
Sets my_database as the current database for subsequent queries.
Drop a database
DROP DATABASE IF EXISTS old_database;
Deletes the old_database if it exists.
Managing Tables
List tables in the current database
SHOW TABLES;
Displays a list of tables in the currently selected database.
Describe a table’s schema
DESCRIBE TABLE my_table;
Shows the columns, data types, and other details of my_table.
Create a table
CREATE TABLE users (
id UInt64,
name String,
created_at DateTime
) ENGINE = MergeTree()
ORDER BY id;
Creates a table named users with specified columns and engine.
Drop a table
DROP TABLE IF EXISTS temporary_table;
Deletes the temporary_table if it exists.
Insert data
echo "1,Alice,2023-01-15 10:00:00" | clickhouse-client --query "INSERT INTO users FORMAT CSV"
Inserts a row into the users table using CSV format.
Select data
clickhouse-client --query "SELECT name, created_at FROM users WHERE id = 1"
Retrieves specific columns for a record with id = 1.
Output Formatting
Default tab-separated output
clickhouse-client --query "SELECT 1, 'hello'"
Outputs results with columns separated by tabs.
Pretty format
clickhouse-client --format Pretty --query "SELECT 1, 'hello'"
Formats output in a human-readable, table-like structure.
JSON format
clickhouse-client --format JSON --query "SELECT 1, 'hello'"
Outputs results as a JSON array of objects.
CSV format
clickhouse-client --format CSV --query "SELECT 1, 'hello'"
Outputs results in CSV format.
TSV (Tab-Separated Values)
clickhouse-client --format TSV --query "SELECT 1, 'hello'"
Outputs results in TSV format.
Vertical format
clickhouse-client --format Vertical --query "SELECT 1, 'hello'"
Displays each row as key-value pairs, one per line.
Client Settings
Set a server-side setting
clickhouse-client --max_block_size=10000 --query "SELECT 1"
Sets the max_block_size server setting for the duration of this query.
Set a client-side setting
clickhouse-client --format_csv_delimiter=';' --query "SELECT 'a;b'"
Sets the delimiter for CSV output to a semicolon.
Show available settings
clickhouse-client --query "SETTINGS"
Displays a list of available server settings.
Other Useful Flags
Verbose output
clickhouse-client --verbose --query "SELECT 1"
Shows more detailed information about the client’s actions.
No prompt
clickhouse-client --no-user-info --query "SELECT 1"
Disables the display of user information at the start of interactive sessions.
Timeouts
clickhouse-client --connect_timeout 5 --query "SELECT 1"
Sets a connection timeout of 5 seconds.
clickhouse-client --max_execution_time 60 --query "SELECT sleep(5)"
Limits query execution time to 60 seconds.
Compression
clickhouse-client --compression 1 --query "SELECT 1"
Enables compression for client-server communication.
Common Patterns
Running a query and saving output to a file
clickhouse-client --query "SELECT * FROM my_table LIMIT 100" > output.txt
Executes a query and redirects its standard output to output.txt.
Inserting data from a CSV file
cat data.csv | clickhouse-client --query "INSERT INTO my_table FORMAT CSV"
Reads data from data.csv and pipes it into ClickHouse for insertion.
Executing a batch of SQL scripts
clickhouse-client --config-file ~/.clickhouse-client.conf < init_db.sql
Uses a configuration file and executes a SQL script for database initialization.
Querying remotely and processing with jq
clickhouse-client --host remote.clickhouse.com --query "SELECT toJsonString(column) FROM my_table" | jq .
Fetches data, converts it to JSON on the server, and then parses it with jq for further manipulation.
Running a query with specific settings for performance
clickhouse-client --max_threads 4 --optimize_for_n_rows 1000 --query "SELECT sum(value) FROM large_table"
Tunes server settings for a specific query.
Gotchas
Default Database and User
If no database is specified, ClickHouse uses the default database. If no user is specified, it uses the default user. This can lead to unexpected behavior if you have multiple databases or users.
ORDER BY in SELECT vs. ORDER BY in MergeTree
The ORDER BY clause in a MergeTree table definition defines the physical sorting of data on disk. An ORDER BY clause in a SELECT statement defines the sorting of the output of that query. For performance, it’s best if the SELECT query’s ORDER BY matches the table’s primary key.
Data Types and Formats
ClickHouse is strict about data types. Ensure the data you’re inserting matches the column types. When using formats like CSV or TSV, be mindful of delimiters and quoting.
Interactive Session State
In an interactive session, USE database_name; changes the current database for that session. Subsequent queries will operate on that database unless explicitly overridden.
INSERT requires FORMAT
When inserting data, you almost always need to specify the FORMAT clause. If omitted, ClickHouse might try to guess, but it’s best practice to be explicit.
Server Settings vs. Client Settings
Be aware of which settings are applied server-side (affecting query execution) and which are client-side (affecting output or connection). Flags like --max_threads are server-side, while --format is client-side.