Docker安装ClickHouse并初始化数据测试 Docker安装ClickHouse并初始化数据

clickhouse简介

ClickHouse是一个面向列存储的数据库管理系统，可以使用SQL查询实时生成分析数据报告，主要用于OLAP(在线分析处理查询)场景。关于clickhouse原理以及基础知识在以后学习中慢慢总结。
1、Docker安装ClickHouse

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、下载SSBM工具

1、git clone https://github.com/vadimtk/ssb-dbgen.git2、cd ssb-dbgen3、make

3、生成数据

./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

查看下数据

文章图片

4、建表

CREATE TABLE default.customer(C_CUSTKEYUInt32,C_NAMEString,C_ADDRESSString,C_CITYLowCardinality(String),C_NATIONLowCardinality(String),C_REGIONLowCardinality(String),C_PHONEString,C_MKTSEGMENTLowCardinality(String))ENGINE = MergeTree ORDER BY (C_CUSTKEY);

CREATE TABLE default.lineorder(LO_ORDERKEYUInt32,LO_LINENUMBERUInt8,LO_CUSTKEYUInt32,LO_PARTKEYUInt32,LO_SUPPKEYUInt32,LO_ORDERDATEDate,LO_ORDERPRIORITYLowCardinality(String),LO_SHIPPRIORITYUInt8,LO_QUANTITYUInt8,LO_EXTENDEDPRICEUInt32,LO_ORDTOTALPRICEUInt32,LO_DISCOUNTUInt8,LO_REVENUEUInt32,LO_SUPPLYCOSTUInt32,LO_TAXUInt8,LO_COMMITDATEDate,LO_SHIPMODELowCardinality(String))ENGINE = MergeTree PARTITION BY toYear(LO_ORDERDATE) ORDER BY (LO_ORDERDATE, LO_ORDERKEY);

CREATE TABLE default.part(P_PARTKEYUInt32,P_NAMEString,P_MFGRLowCardinality(String),P_CATEGORYLowCardinality(String),P_BRANDLowCardinality(String),P_COLORLowCardinality(String),P_TYPELowCardinality(String),P_SIZEUInt8,P_CONTAINERLowCardinality(String))ENGINE = MergeTree ORDER BY P_PARTKEY;

CREATE TABLE default.supplier(S_SUPPKEYUInt32,S_NAMEString,S_ADDRESSString,S_CITYLowCardinality(String),S_NATIONLowCardinality(String),S_REGIONLowCardinality(String),S_PHONEString)ENGINE = MergeTree ORDER BY S_SUPPKEY;

5、导入数据
准备工作：
先把ssb-dbgen(lineorder.tbl,customer.tbl,part.tbl,supplier.tbl)考到clickhouse-server容器里面

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

注意：如果此处报错，检查clickhouse的配置（端口是否占用，是否设置用户和密码）
6、测试

编号	查询语句SQL	耗时(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;	21
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

官网参考：
https://clickhouse.tech/docs/zh/getting-started/example-datasets/star-schema/#star-schema-benchmark
【Docker安装ClickHouse并初始化数据测试】以上就是Docker创建ClickHouse 并初始化数据测试的详细内容，更多关于Docker的资料请关注脚本之家其它相关文章！