ClickHouse installation on Ubuntu 22.04

ClickHouse is an open-source column-oriented database management system that allows generating analytical data reports in real-time.

OLAP scenarios require real-time responses on top of large datasets for complex analytical queries with the following characteristics:

• Datasets can be massive – billions or trillions of rows
• Data is organized in tables that contain many columns
• Only a few columns are selected to answer any particular query
• Results must be returned in milliseconds or second

Update package index files

sudo apt update

Install dependencies

sudo apt install apt-transport-https ca-certificates dirmngr -y

Import ClickHouse GPG Key

GNUPGHOME=$(mktemp -d)
sudo GNUPGHOME="$GNUPGHOME" gpg --no-default-keyring --keyring /usr/share/keyrings/clickhouse-keyring.gpg --keyserver hkp://keyserver.ubuntu.com:80 --recv-keys 8919F6BD2B48D754
sudo rm -r "$GNUPGHOME"
sudo chmod +r /usr/share/keyrings/clickhouse-keyring.gpg

Add APT repository to Ubuntu 22.04

echo "deb [signed-by=/usr/share/keyrings/clickhouse-keyring.gpg] https://packages.clickhouse.com/deb stable main" | sudo tee \
    /etc/apt/sources.list.d/clickhouse.list

Update package index files

sudo apt update

Install ClickHouse server and client

sudo apt install clickhouse-server clickhouse-client -y

Note : Enter password for default user when prompted during installation.

Start ClickHouse server by running below command

sudo service clickhouse-server start

Access ClickHouse using clickhouse-client

 clickhouse-client

Terminal output for reference

$ clickhouse-client
ClickHouse client version 23.7.1.2470 (official build).
Connecting to localhost:9000 as user default.
Password for user (default): 
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 23.7.1 revision 54464.

Warnings:
 * Maximum number of threads is lower than 30000. There could be problems with handling a lot of simultaneous queries.

abb :)

Create a database

CREATE DATABASE hyrule

Terminal output for reference

abb :) CREATE DATABASE hyrule

CREATE DATABASE hyrule

Query id: fa4aae46-f2e8-403c-a1e8-2f8bad0a6ec6

Ok.

0 rows in set. Elapsed: 0.003 sec.

Create a table

CREATE TABLE hyrule.warriors
(
 user_id UInt32,
 name String,
 timestamp DateTime,
 strength Float32
)
ENGINE = MergeTree()
PRIMARY KEY (user_id, timestamp)

The above command will create a MergeTree table with four columns:

user_id: it is used to assign a 32-bit unsigned integer
name: a String data type holds the name
timestamp: a DateTime value to represent time
strength: a 32-bit floating point number

Insert some data into the table

INSERT INTO hyrule.warriors (user_id, name, timestamp, strength) VALUES
(701, 'Link', now(), 99.99 ),
(702, 'King Daphnes', today(), 98.88 ),
(703, 'Ganondorf', yesterday(), 97.99 ),
(704, 'Zant', now()-1, 90.39 )

Get the table data

SELECT * from hyrule.warriors

Terminal output for reference

abb :) SELECT * from hyrule.warriors

SELECT *
FROM hyrule.warriors

Query id: 4f0bd822-836b-496d-b071-e7342d5e7d81

┌─user_id─┬─name─────────┬───────────timestamp─┬─strength─┐
│     701 │ Link         │ 2023-08-03 09:36:34 │    99.99 │
│     702 │ King Daphnes │ 2023-08-03 00:00:00 │    98.88 │
│     703 │ Ganondorf    │ 2023-08-02 00:00:00 │    97.99 │
│     704 │ Zant         │ 2023-08-03 09:36:33 │    90.39 │
└─────────┴──────────────┴─────────────────────┴──────────┘

4 rows in set. Elapsed: 0.002 sec.