Quick Start
ClickHouse can run on any Linux, FreeBSD, or Mac OS X with x86_64, AArch64, or PowerPC64LE CPU architecture. Follow these steps to get up and running with ClickHouse.
note
If your OS is not supported or for other install options, view the installation details in the technical reference guide.
1. Start ClickHouse
- Linux
- macOS
The simplest way to download ClickHouse locally is to run the following command. If your operating system is supported, an appropriate ClickHouse binary will be downloaded and made executable:
curl https://clickhouse.com/ | sh
Run the
install
command, which defines a collection of useful symlinks along with the files and folders used by ClickHouse - all of which you can see in the output of the install script:sudo ./clickhouse install
At the end of the install script, you are prompted for a password for the
default
user. Feel free to enter a password, or you can optionally leave it blank:Creating log directory /var/log/clickhouse-server.
Creating data directory /var/lib/clickhouse.
Creating pid directory /var/run/clickhouse-server.
chown -R clickhouse:clickhouse '/var/log/clickhouse-server'
chown -R clickhouse:clickhouse '/var/run/clickhouse-server'
chown clickhouse:clickhouse '/var/lib/clickhouse'
Enter password for default user:You should see the following output:
ClickHouse has been successfully installed.
Start clickhouse-server with:
sudo clickhouse start
Start clickhouse-client with:
clickhouse-clientRun the following command to start the ClickHouse server:
sudo clickhouse start
The simplest way to download ClickHouse locally is to run the following command. If your operating system is supported, an appropriate ClickHouse binary will be downloaded and made executable:
curl https://clickhouse.com/ | sh
Run the following command to start the ClickHouse server. A user named
default
with no password is created on the initial startup:./clickhouse server
Important
The examples throughout the documentation use the Linux commands for running the ClickHouse client (
clickhouse-client
).To run the ClickHouse server and client on a Mac, use
./clickhouse server
and./clickhouse client
, respectively.
2. Connect to ClickHouse
The ClickHouse server listens for HTTP clients on port 8123 by default. There is a built-in UI for running SQL queries at http://127.0.0.1:8123/play (change the hostname accordingly).
Notice in your Play UI that the username was populated with default and the password text field was left empty. If you assigned a password to the
default
user, enter it into the password field.Try running a query. For example, the following returns the names of the predefined databases:
SHOW databases
Click the RUN button and the response is displayed in the lower portion of the Play UI:
3. Create a Table
As in most databases management systems, ClickHouse logically groups tables into databases. Use the
CREATE DATABASE
command to create a new database in ClickHouse:CREATE DATABASE IF NOT EXISTS helloworld
Even the simplest of tables in ClickHouse must specify a table engine. The engine determines details about the table like:
- how and where the data is stored,
- which queries are supported, and
- whether or not the data is replicated.
There are many engines to choose from, but for a simple table on a single-node ClickHouse server, MergeTree is your likely choice. Run the following command to create a table named
my_first_table
in thehelloworld
database:CREATE TABLE helloworld.my_first_table
(
user_id UInt32,
message String,
timestamp DateTime,
metric Float32
)
ENGINE = MergeTree()
PRIMARY KEY (user_id, timestamp)
A Brief Intro to Primary Keys
Before you go any further, it is important to understand how primary keys work in ClickHouse (the implementation of primary keys might seem unexpected!):
- primary keys in ClickHouse are not unique for each row in a table
The primary key of a ClickHouse table determines how the data is sorted when written to disk. Every 8,192 rows or 10MB of
data (referred to as the index granularity) creates an entry in the primary key index file. This granularity concept
creates a sparse index that can easily fit in memory, and the granules represent a stripe of the smallest amount of
column data that gets processed during SELECT
queries.
The primary key can be defined using the PRIMARY KEY
command. If you define a table without a PRIMARY KEY
specified,
then the key becomes the tuple specified in the ORDER BY
clause. If you specify both a PRIMARY KEY
and an ORDER BY
, the primary key must be a subset of the sort order.
In the example above, my_first_table
is a MergeTree table with four columns:
user_id
: a 32-bit unsigned integermessage
: a String data type, which replaces types like VARCHAR, BLOB, CLOB and others from other database systemstimestamp
: a DateTime value, which represents an instant in timemetric
: a 32-bit floating point number
The primary key is also the sorting key, which is a tuple of (user_id, timestamp)
. Therefore, the data stored in each
column file will be sorted by user_id
, then timestamp
.
4. Insert Data
You can use the familiar INSERT INTO TABLE
command with ClickHouse, but it is important to understand that each insert into a MergeTree
table causes a part to be created in storage.
The best practice with ClickHouse is to insert a large number of rows per batch - tens of thousands or even millions of rows at once. (Don't worry - ClickHouse can easily handle that type of volume!)
Even for a simple example, let's insert more than one row at a time:
INSERT INTO helloworld.my_first_table (user_id, message, timestamp, metric) VALUES
(101, 'Hello, ClickHouse!', now(), -1.0 ),
(102, 'Insert a lot of rows per batch', yesterday(), 1.41421 ),
(102, 'Sort your data based on your commonly-used queries', today(), 2.718 ),
(101, 'Granules are the smallest chunks of data read', now() + 5, 3.14159 )note
Notice the
timestamp
column is populated using various Date and DateTime functions. ClickHouse has hundreds of useful functions that you can view in the Functions section.Let's verify it worked:
SELECT * FROM helloworld.my_first_table
You should see the four rows of data that were inserted:
5. The ClickHouse Client
- Linux
- macOS
- You can also connect to your ClickHouse server using a command-line tool named clickhouse-client:
clickhouse-client
- You can also connect to your ClickHouse server using a command-line tool named clickhouse-client. Open a new terminal and change directories to where you downloaded the
clickhouse
binary in step 1 above, then run the following command:./clickhouse client
If you get the smiley face prompt, you are ready to run queries!
:)
Give it a try by running the following query:
SELECT *
FROM helloworld.my_first_table
ORDER BY timestampNotice the response comes back in a nice table format:
SELECT *
FROM helloworld.my_first_table
ORDER BY timestamp ASC
Query id: f7a33012-bc8c-4f0f-9641-260ee1ffe4b8
┌─user_id─┬─message────────────────────────────────────────────┬───────────timestamp─┬──metric─┐
│ 102 │ Insert a lot of rows per batch │ 2022-03-21 00:00:00 │ 1.41421 │
│ 102 │ Sort your data based on your commonly-used queries │ 2022-03-22 00:00:00 │ 2.718 │
│ 101 │ Hello, ClickHouse! │ 2022-03-22 14:04:09 │ -1 │
│ 101 │ Granules are the smallest chunks of data read │ 2022-03-22 14:04:14 │ 3.14159 │
└─────────┴────────────────────────────────────────────────────┴─────────────────────┴─────────┘
4 rows in set. Elapsed: 0.008 sec.Add a
FORMAT
clause to specify one of the many supported output formats of ClickHouse:SELECT *
FROM helloworld.my_first_table
ORDER BY timestamp
FORMAT TabSeparatedIn the above query, the output is returned as tab-separated:
Query id: 3604df1c-acfd-4117-9c56-f86c69721121
102 Insert a lot of rows per batch 2022-03-21 00:00:00 1.41421
102 Sort your data based on your commonly-used queries 2022-03-22 00:00:00 2.718
101 Hello, ClickHouse! 2022-03-22 14:04:09 -1
101 Granules are the smallest chunks of data read 2022-03-22 14:04:14 3.14159
4 rows in set. Elapsed: 0.005 sec.To exit the
clickhouse-client
, enter the exit command::) exit
Bye.
6. Insert a CSV file
A common task when getting started with a database is to insert some data that you already have in files. We have some sample data online that you can insert that represents clickstream data - it includes a user ID, a URL that was visited, and the timestamp of the event.
Suppose we have the following text in a CSV file named
data.csv
:102,This is data in a file,2022-02-22 10:43:28,123.45
101,It is comma-separated,2022-02-23 00:00:00,456.78
103,Use FORMAT to specify the format,2022-02-21 10:43:30,678.90The following command inserts the data into
my_first_table
:clickhouse-client --query='INSERT INTO helloworld.my_first_table FORMAT CSV' < data.csv
Notice the new rows appear in the table now:
What's Next?
- The Tutorial has you insert 2 million rows into a table and write some analytical queries
- We have a list of example datasets with instructions on how to insert them
- Check out our 25-minute video on Getting Started with ClickHouse
- If your data is coming from an external source, view our collection of integration guides for connecting to message queues, databases, pipelines and more
- If you are using a UI/BI visualization tool, view the user guides for connecting a UI to ClickHouse
- The user guide on primary keys is everything you need to know about primary keys and how to define them