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.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_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、导入数据

准备工作：
先把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的资料请关注其它相关文章！