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.git 2、cd ssb-dbgen 3、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_NAME String, C_ADDRESSString, 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_CUSTKEYUInt32,
LO_PARTKEYUInt32,
LO_SUPPKEYUInt32,
LO_ORDERDATE Date,
LO_ORDERPRIORITY LowCardinality(String),
LO_SHIPPRIORITY UInt8,
LO_QUANTITY UInt8,
LO_EXTENDEDPRICE UInt32,
LO_ORDTOTALPRICE UInt32,
LO_DISCOUNT UInt8,
LO_REVENUEUInt32,
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_PARTKEYUInt32, 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_SUPPKEYUInt32, S_NAME String, S_ADDRESSString, 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容器里面
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的资料请关注考高分网其它相关文章！
