ClickHouse圧力測定

9695 ワード

文書ディレクトリ

  • 一、圧力測定工具
  • 1.1ダウンロードコンパイルdbgen
  • 1.2生産データ
  • 1.3試験表
  • を作成する
  • 1.4試験データ
  • を導入する.
  • 1.5「スターモード」を非正規化「フラットモード」
  • に変換
  • 二、圧力測定クエリー

  •  本明細書では、CKが公式に提供するテストスキーム:SSBM(Star Schema Benchmark)を選択します.

    一、圧力測定工具


    1.1 dbgenのダウンロードとコンパイル

    yum -y install gcc gcc-c++ make cmake git 
    git clone https://github.com/vadimtk/ssb-dbgen.git
    make
    

    1.2生産データ

    ./dbgen -s 1 -T c
    ./dbgen -s 1 -T l
    ./dbgen -s 1 -T p
    ./dbgen -s 1 -T s
    ./dbgen -s 1 -T d
    [root@zijie ssb-dbgen]# wc -l *tbl
        30000 customer.tbl
         2556 date.tbl
      6001215 lineorder.tbl
       200000 part.tbl
         2000 supplier.tbl
      6235771 total
    
    c--customer.tbl
    p--part.tbl
    s--supplier.tbl
    d--date.tbl
    l--lineorder.tbl
     :(for all SSBM tables)
    dbgen -s 1 -T a
    

    1.3試験表の作成

    CREATE DATABASE bench;
    use bench;
    CREATE TABLE 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 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 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 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;
    

    1.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
    zijie :) select count(*) from customer;
    
    SELECT count(*)
    FROM customer
    
    ┌─count()─┐
    │   30000 │
    └─────────┘
    
    1 rows in set. Elapsed: 0.005 sec.
    
    zijie :) select count(*) from lineorder;
    
    SELECT count(*)
    FROM lineorder
    
    ┌─count()─┐
    │ 6001215 │
    └─────────┘
    
    1 rows in set. Elapsed: 0.002 sec.
    
    zijie :) select count(*) from part;
    
    SELECT count(*)
    FROM part
    
    ┌─count()─┐
    │  200000 │
    └─────────┘
    
    1 rows in set. Elapsed: 0.002 sec.
    
    zijie :) select count(*) from supplier;
    
    SELECT count(*)
    FROM supplier
    
    ┌─count()─┐
    │    2000 │
    └─────────┘
    
    1 rows in set. Elapsed: 0.002 sec.
    
    

    1.5スターモードを非正規化されたプレーンモードに変換する

    SET max_memory_usage = 20000000000;
    
    CREATE TABLE lineorder_flat
    ENGINE = MergeTree
    PARTITION BY toYear(LO_ORDERDATE)
    ORDER BY (LO_ORDERDATE, LO_ORDERKEY) AS
    SELECT
        l.LO_ORDERKEY AS LO_ORDERKEY,
        l.LO_LINENUMBER AS LO_LINENUMBER,
        l.LO_CUSTKEY AS LO_CUSTKEY,
        l.LO_PARTKEY AS LO_PARTKEY,
        l.LO_SUPPKEY AS LO_SUPPKEY,
        l.LO_ORDERDATE AS LO_ORDERDATE,
        l.LO_ORDERPRIORITY AS LO_ORDERPRIORITY,
        l.LO_SHIPPRIORITY AS LO_SHIPPRIORITY,
        l.LO_QUANTITY AS LO_QUANTITY,
        l.LO_EXTENDEDPRICE AS LO_EXTENDEDPRICE,
        l.LO_ORDTOTALPRICE AS LO_ORDTOTALPRICE,
        l.LO_DISCOUNT AS LO_DISCOUNT,
        l.LO_REVENUE AS LO_REVENUE,
        l.LO_SUPPLYCOST AS LO_SUPPLYCOST,
        l.LO_TAX AS LO_TAX,
        l.LO_COMMITDATE AS LO_COMMITDATE,
        l.LO_SHIPMODE AS LO_SHIPMODE,
        c.C_NAME AS C_NAME,
        c.C_ADDRESS AS C_ADDRESS,
        c.C_CITY AS C_CITY,
        c.C_NATION AS C_NATION,
        c.C_REGION AS C_REGION,
        c.C_PHONE AS C_PHONE,
        c.C_MKTSEGMENT AS C_MKTSEGMENT,
        s.S_NAME AS S_NAME,
        s.S_ADDRESS AS S_ADDRESS,
        s.S_CITY AS S_CITY,
        s.S_NATION AS S_NATION,
        s.S_REGION AS S_REGION,
        s.S_PHONE AS S_PHONE,
        p.P_NAME AS P_NAME,
        p.P_MFGR AS P_MFGR,
        p.P_CATEGORY AS P_CATEGORY,
        p.P_BRAND AS P_BRAND,
        p.P_COLOR AS P_COLOR,
        p.P_TYPE AS P_TYPE,
        p.P_SIZE AS P_SIZE,
        p.P_CONTAINER AS P_CONTAINER
    FROM lineorder AS l
    INNER JOIN customer AS c ON c.C_CUSTKEY = l.LO_CUSTKEY
    INNER JOIN supplier AS s ON s.S_SUPPKEY = l.LO_SUPPKEY
    INNER JOIN part AS p ON p.P_PARTKEY = l.LO_PARTKEY;
    

    二、圧力測定クエリー


    Q1.1
    SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
    FROM lineorder_flat
    WHERE toYear(LO_ORDERDATE) = 1993 AND LO_DISCOUNT BETWEEN 1 AND 3 AND LO_QUANTITY < 25;
    

    Q1.2
    SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
    FROM lineorder_flat
    WHERE toYYYYMM(LO_ORDERDATE) = 199401 AND LO_DISCOUNT BETWEEN 4 AND 6 AND LO_QUANTITY BETWEEN 26 AND 35;
    

    Q1.3
    SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
    FROM lineorder_flat
    WHERE toISOWeek(LO_ORDERDATE) = 6 AND toYear(LO_ORDERDATE) = 1994
      AND LO_DISCOUNT BETWEEN 5 AND 7 AND LO_QUANTITY BETWEEN 26 AND 35;
    

    Q2.1
    SELECT
        sum(LO_REVENUE),
        toYear(LO_ORDERDATE) AS year,
        P_BRAND
    FROM lineorder_flat
    WHERE P_CATEGORY = 'MFGR#12' AND S_REGION = 'AMERICA'
    GROUP BY
        year,
        P_BRAND
    ORDER BY
        year,
        P_BRAND;
    

    Q2.2
    SELECT
        sum(LO_REVENUE),
        toYear(LO_ORDERDATE) AS year,
        P_BRAND
    FROM lineorder_flat
    WHERE P_BRAND >= 'MFGR#2221' AND P_BRAND <= 'MFGR#2228' AND S_REGION = 'ASIA'
    GROUP BY
        year,
        P_BRAND
    ORDER BY
        year,
        P_BRAND;
    

    Q2.3
    SELECT
        sum(LO_REVENUE),
        toYear(LO_ORDERDATE) AS year,
        P_BRAND
    FROM lineorder_flat
    WHERE P_BRAND = 'MFGR#2239' AND S_REGION = 'EUROPE'
    GROUP BY
        year,
        P_BRAND
    ORDER BY
        year,
        P_BRAND;
    

    Q3.1
    SELECT
        C_NATION,
        S_NATION,
        toYear(LO_ORDERDATE) AS year,
        sum(LO_REVENUE) AS revenue
    FROM lineorder_flat
    WHERE C_REGION = 'ASIA' AND S_REGION = 'ASIA' AND year >= 1992 AND year <= 1997
    GROUP BY
        C_NATION,
        S_NATION,
        year
    ORDER BY
        year ASC,
        revenue DESC;
    

    Q3.2
    SELECT
        C_CITY,
        S_CITY,
        toYear(LO_ORDERDATE) AS year,
        sum(LO_REVENUE) AS revenue
    FROM lineorder_flat
    WHERE C_NATION = 'UNITED STATES' AND S_NATION = 'UNITED STATES' AND year >= 1992 AND year <= 1997
    GROUP BY
        C_CITY,
        S_CITY,
        year
    ORDER BY
        year ASC,
        revenue DESC;
    

    Q3.3
    SELECT
        C_CITY,
        S_CITY,
        toYear(LO_ORDERDATE) AS year,
        sum(LO_REVENUE) AS revenue
    FROM lineorder_flat
    WHERE (C_CITY = 'UNITED KI1' OR C_CITY = 'UNITED KI5') AND (S_CITY = 'UNITED KI1' OR S_CITY = 'UNITED KI5') AND year >= 1992 AND year <= 1997
    GROUP BY
        C_CITY,
        S_CITY,
        year
    ORDER BY
        year ASC,
        revenue DESC;
    

    Q3.4
    SELECT
        C_CITY,
        S_CITY,
        toYear(LO_ORDERDATE) AS year,
        sum(LO_REVENUE) AS revenue
    FROM lineorder_flat
    WHERE (C_CITY = 'UNITED KI1' OR C_CITY = 'UNITED KI5') AND (S_CITY = 'UNITED KI1' OR S_CITY = 'UNITED KI5') AND toYYYYMM(LO_ORDERDATE) = 199712
    GROUP BY
        C_CITY,
        S_CITY,
        year
    ORDER BY
        year ASC,
        revenue DESC;
    

    Q4.1
    SELECT
        toYear(LO_ORDERDATE) AS year,
        C_NATION,
        sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
    FROM lineorder_flat
    WHERE C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND (P_MFGR = 'MFGR#1' OR P_MFGR = 'MFGR#2')
    GROUP BY
        year,
        C_NATION
    ORDER BY
        year ASC,
        C_NATION ASC;
    

    Q4.2
    SELECT
        toYear(LO_ORDERDATE) AS year,
        S_NATION,
        P_CATEGORY,
        sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
    FROM lineorder_flat
    WHERE C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND (year = 1997 OR year = 1998) AND (P_MFGR = 'MFGR#1' OR P_MFGR = 'MFGR#2')
    GROUP BY
        year,
        S_NATION,
        P_CATEGORY
    ORDER BY
        year ASC,
        S_NATION ASC,
        P_CATEGORY ASC;
    

    Q4.3
    SELECT
        toYear(LO_ORDERDATE) AS year,
        S_CITY,
        P_BRAND,
        sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
    FROM lineorder_flat
    WHERE S_NATION = 'UNITED STATES' AND (year = 1997 OR year = 1998) AND P_CATEGORY = 'MFGR#14'
    GROUP BY
        year,
        S_CITY,
        P_BRAND
    ORDER BY
        year ASC,
        S_CITY ASC,
        P_BRAND ASC;