您的位置:首页 > 服务器 > > 正文

docker虚拟化容器使用教程(Docker安装ClickHouse并初始化数据测试)

更多 时间:2021-10-07 00:08:11 类别:服务器 浏览量:1761

docker虚拟化容器使用教程

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
    
  • 查看下数据

    docker虚拟化容器使用教程(Docker安装ClickHouse并初始化数据测试)

    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的资料请关注开心学习网其它相关文章!

    标签:Docker ClickHouse
    您可能感兴趣