ClickHouse是一个面向列存储的数据库管理系统，可以使用SQL查询实时生成分析数据报告，主要用于OLAP(在线分析处理查询)场景。关于clickhouse原理以及基础知识在以后学习中慢慢总结。
1、Docker安装ClickHouse
2、下载SSBM工具
?
1
2
3
1、git clone https://github.com/vadimtk/ssb-dbgen.git
2、cd ssb-dbgen
3、make
3、生成数据
?
1
2
3
4
5
./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、建表
?
1
2
3
4
5
6
7
8
9
10
11
12
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);
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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);
?
1
2
3
4
5
6
7
8
9
10
11
12
13
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;
?
1
2
3
4
5
6
7
8
9
10
11
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、导入数据
准备工作：
先把ssb-dbgen(lineorder.tbl,customer.tbl,part.tbl,supplier.tbl)考到clickhouse-server容器里面
?
1
2
3
4
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
注意：如果此处报错，检查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的资料请关注服务器之家其它相关文章！
原文链接：https://blog.csdn.net/Joe192/article/details/118159352
声明： 猿站网有关资源均来自网络搜集与网友提供，任何涉及商业盈利目的的均不得使用，否则产生的一切后果将由您自己承担！ 本平台资源仅供个人学习交流、测试使用 所有内容请在下载后24小时内删除，制止非法恶意传播，不对任何下载或转载者造成的危害负任何法律责任！也请大家支持、购置正版！ 。本站一律禁止以任何方式发布或转载任何违法的相关信息访客发现请向站长举报，会员发帖仅代表会员个人观点，并不代表本站赞同其观点和对其真实性负责。本网站的资源部分来源于网络，如有侵权烦请发送邮件至：2697268773@qq.com进行处理。