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

Docker image analysis tool dive principle analysis

Today I recommend such an open source tool for ex...

Detailed explanation of the general steps for SQL statement optimization

Preface This article mainly shares with you the g...

Explanation of the new feature of Hadoop 2.X, the recycle bin function

By turning on the Recycle Bin function, you can r...

Simple Mysql backup BAT script sharing under Windows

Preface This article introduces a simple BAT scri...

Simplify complex website navigation

<br />Navigation design is one of the main t...

A detailed introduction to the Linux directory structure

When you first start learning Linux, you first ne...

InnoDB type MySql restore table structure and data

Prerequisite: Save the .frm and .ibd files that n...

CSS uses BEM naming convention practice

When you see a class, what information do you wan...

Summary of some efficient magic operators in JS

JavaScript now releases a new version every year,...

How to monitor array changes in Vue

Table of contents Preface Source code Where do I ...

Detailed tutorial for installing mysql5.7.18 on centos7.3

1 Check the Linux distribution version [root@type...

Teach you how to use AWS server resources for free

AWS - Amazon's cloud computing service platfo...

A brief discussion of 3 new features worth noting in TypeScript 3.7

Table of contents Preface Optional Chaining Nulli...