Docker installs ClickHouse and initializes data testing

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

Recommend

The rel attribute of the HTML link tag

The <link> tag defines the relationship bet...

How to install MySQL 5.7 from source code in CentOS 7 environment

This article describes how to install MySQL 5.7 f...

Detailed explanation of the spacing problem between img tags

IMG tag basic analysis In HTML5, the img tag has ...

MySQL 8.0.18 installation and configuration method graphic tutorial

This article records the installation and configu...

Example usage of Linux compression file command zip

The ".zip" format is used to compress f...

Two box models in web pages (W3C box model, IE box model)

There are two types of web page box models: 1: Sta...

Centos7.5 installs mysql5.7.24 binary package deployment

1. Environmental preparation: Operating system: C...

MySQL controls the number of attempts to enter incorrect passwords

1. How to monitor MySQL deadlocks in production e...

XHTML 2.0 New Features Preview

<br />Before browsers can handle the next ge...

MySQL index for beginners

Preface Since the most important data structure i...

Quick solution for forgetting MySQL8 password

Preface When we forget the MySQL database passwor...

Install Docker environment in Linux environment (no pitfalls)

Table of contents Installation Prerequisites Step...

Vue implements scroll loading table

Table of contents Achieve results Rolling load kn...

Advanced crawler - Use of Scrapy_splash component for JS automatic rendering

Table of contents 1. What is scrapy_splash? 2. Th...

The marquee tag in HTML achieves seamless scrolling marquee effect

The <marquee> tag is a tag that appears in ...