Docker installs ClickHouse and initializes data testing

Clickhouse Introduction

ClickHouse is a column-oriented database management system that can generate analytical data reports in real time using SQL queries. It is mainly used in OLAP (online analytical processing) scenarios. The principles and basic knowledge of ClickHouse will be slowly summarized in future studies.

1. Install ClickHouse with Docker

docker run -d --name some-clickhouse-server \
-p 8123:8123 -p 9009:9009 -p 9091:9000 \
--ulimit nofile=262144:262144 \
-v /home/clickhouse:/var/lib/clickhouse \
yandex/clickhouse-server

2. Download SSBM tool

1. git clone https://github.com/vadimtk/ssb-dbgen.git
2. cd ssb-dbgen
3. make

3. Generate data

./dbgen -s 100 -T c
./dbgen -s 100 -T p
./dbgen -s 100 -T s
./dbgen -s 100 -T l
./dbgen -s 100 -T d

View the following data

insert image description here

4. Create a table

CREATE TABLE default.customer
(
        C_CUSTKEY UInt32,
        C_NAME String,
        C_ADDRESS String,
        C_CITY LowCardinality(String),
        C_NATION LowCardinality(String),
        C_REGION LowCardinality(String),
        C_PHONE String,
        C_MKTSEGMENT LowCardinality(String)
)
ENGINE = MergeTree ORDER BY (C_CUSTKEY);

CREATE TABLE default.lineorder
(
    LO_ORDERKEY UInt32,
    LO_LINENUMBER UInt8,
    LO_CUSTKEY UInt32,
    LO_PARTKEY UInt32,
    LO_SUPPKEY UInt32,
    LO_ORDERDATE Date,
    LO_ORDERPRIORITY LowCardinality(String),
    LO_SHIPPRIORITY UInt8,
    LO_QUANTITY UInt8,
    LO_EXTENDEDPRICE UInt32,
    LO_ORDTOTALPRICE UInt32,
    LO_DISCOUNT UInt8,
    LO_REVENUE UInt32,
    LO_SUPPLYCOST UInt32,
    LO_TAX UInt8,
    LO_COMMITDATE Date,
    LO_SHIPMODE LowCardinality(String)
)
ENGINE = MergeTree PARTITION BY toYear(LO_ORDERDATE) ORDER BY (LO_ORDERDATE, LO_ORDERKEY);

CREATE TABLE default.part
(
        P_PARTKEY UInt32,
        P_NAME String,
        P_MFGR LowCardinality(String),
        P_CATEGORY LowCardinality(String),
        P_BRAND LowCardinality(String),
        P_COLOR LowCardinality(String),
        P_TYPE LowCardinality(String),
        P_SIZE UInt8,
        P_CONTAINER LowCardinality(String)
)
ENGINE = MergeTree ORDER BY P_PARTKEY;

CREATE TABLE default.supplier
(
        S_SUPPKEY UInt32,
        S_NAME String,
        S_ADDRESS String,
        S_CITY LowCardinality(String),
        S_NATION LowCardinality(String),
        S_REGION LowCardinality(String),
        S_PHONE String
)
ENGINE = MergeTree ORDER BY S_SUPPKEY;

5. Import data

Preparation:
First, test ssb-dbgen (lineorder.tbl, customer.tbl, part.tbl, supplier.tbl) into the clickhouse-server container

clickhouse-client --query "INSERT INTO customer FORMAT CSV" < customer.tbl
clickhouse-client --query "INSERT INTO part FORMAT CSV" < part.tbl
clickhouse-client --query "INSERT INTO supplier FORMAT CSV" < supplier.tbl
clickhouse-client --query "INSERT INTO lineorder FORMAT CSV" < lineorder.tbl

Note: If an error occurs here, check the configuration of ClickHouse (whether the port is occupied, whether the user and password are set)

6. Testing

serial number	Query statement SQL	Time consumed (ms)
Q1	SELECT SUM(l.LO_EXTENDEDPRICE * l.LO_DISCOUNT) AS revenue FROM lineorder_flat WHERE toYear(l.LO_ORDERDATE) = 1993 AND l.LO_DISCOUNT BETWEEN 1 AND 3 AND l.LO_QUANTITY < 25;	36
Q2	SELECT SUM(l.LO_EXTENDEDPRICE * l.LO_DISCOUNT) AS revenue FROM lineorder_flat WHERE toYYYYMM(l.LO_ORDERDATE) = 199401 AND l.LO_DISCOUNT BETWEEN 4 AND 6 AND l.LO_QUANTITYBETWEEN 26 AND 35;	12
Q3	SELECT SUM(l.LO_EXTENDEDPRICE * l.LO_DISCOUNT) AS revenue FROM lineorder_flat WHERE toISOWeek(l.LO_ORDERDATE) = 6 AND toYear(l.LO_ORDERDATE) = 1994 AND l.LO_DISCOUNT BETWEEN 5 AND 7 AND l.LO_QUANTITY BETWEEN 26 AND 35;	12
Q4	SELECT SUM(l.LO_REVENUE), toYear(l.LO_ORDERDATE) AS year, p.P_BRAND FROM lineorder_flat WHERE p.P_CATEGORY = 'MFGR#12' AND s.S_REGION = 'AMERICA' GROUP BY year, p.P_BRAND ORDER BY year, p.P_BRAND;	16
Q5	SELECT SUM(l.LO_REVENUE), toYear(l.LO_ORDERDATE) AS year, p.P_BRAND FROM lineorder_flat WHERE p.P_BRAND BETWEEN 'MFGR#2221' AND 'MFGR#2228' AND s.S_REGION = 'ASIA' GROUP BY year, p.P_BRAND ORDER BY year, p.P_BRAND;	twenty one
Q6	SELECT toYear(l.LO_ORDERDATE) AS year, s.S_CITY, p.P_BRAND, SUM(l.LO_REVENUE -l.LO_SUPPLYCOST) AS profit FROM lineorder_flat WHERE s.S_NATION = 'UNITED STATES' AND (year = 1997 OR year = 1998) AND p.P_CATEGORY = 'MFGR#14' GROUP BY year, s.S_CITY, p.P_BRAND ORDER BY year, s.S_CITY, p.P_BRAND;	19

Official website reference:
https://clickhouse.tech/docs/en/getting-started/example-datasets/star-schema/#star-schema-benchmark

The above is the details of Docker creating ClickHouse and initializing data testing. For more information about Docker, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:

Execute initialization sql when docker mysql starts
Detailed explanation of using Dockerfile to build MySQL image and implement data initialization and permission setting
Solution to MySQL initialization and startup failure in Docker
How to initialize the Mysql database when the Docker container starts

<<: A brief discussion on Python's function knowledge

>>: Stealing data using CSS in Firefox