Star Schema Benchmark

    开始生成数据：

    注意

    使用dbgen 将生成 6 亿行数据(67GB), 如果使用-s 1000它会生成 60 亿行数据(这需要很多时间))

    1. $ ./dbgen -s 1000 -T c
    2. $ ./dbgen -s 1000 -T l
    3. $ ./dbgen -s 1000 -T p
    4. $ ./dbgen -s 1000 -T s
    5. $ ./dbgen -s 1000 -T d

    在 ClickHouse 中创建数据表：

    1. CREATE TABLE customer
    2. (
    3.         C_CUSTKEY       UInt32,
    4.         C_NAME          String,
    5.         C_ADDRESS       String,
    6.         C_CITY          LowCardinality(String),
    7.         C_NATION        LowCardinality(String),
    8.         C_REGION        LowCardinality(String),
    9.         C_PHONE         String,
    10.         C_MKTSEGMENT    LowCardinality(String)
    11. )
    12. ENGINE = MergeTree ORDER BY (C_CUSTKEY);
    14. CREATE TABLE lineorder
    15. (
    16.     LO_ORDERKEY             UInt32,
    17.     LO_LINENUMBER           UInt8,
    18.     LO_CUSTKEY              UInt32,
    19.     LO_PARTKEY              UInt32,
    20.     LO_SUPPKEY              UInt32,
    21.     LO_ORDERDATE            Date,
    22.     LO_ORDERPRIORITY        LowCardinality(String),
    23.     LO_SHIPPRIORITY         UInt8,
    24.     LO_QUANTITY             UInt8,
    25.     LO_EXTENDEDPRICE        UInt32,
    26.     LO_ORDTOTALPRICE        UInt32,
    27.     LO_DISCOUNT             UInt8,
    28.     LO_REVENUE              UInt32,
    29.     LO_SUPPLYCOST           UInt32,
    30.     LO_TAX                  UInt8,
    31.     LO_COMMITDATE           Date,
    32.     LO_SHIPMODE             LowCardinality(String)
    33. )
    34. ENGINE = MergeTree PARTITION BY toYear(LO_ORDERDATE) ORDER BY (LO_ORDERDATE, LO_ORDERKEY);
    36. CREATE TABLE part
    37. (
    38.         P_PARTKEY       UInt32,
    39.         P_NAME          String,
    40.         P_MFGR          LowCardinality(String),
    41.         P_CATEGORY      LowCardinality(String),
    42.         P_BRAND         LowCardinality(String),
    43.         P_COLOR         LowCardinality(String),
    44.         P_TYPE          LowCardinality(String),
    45.         P_SIZE          UInt8,
    46.         P_CONTAINER     LowCardinality(String)
    47. )
    48. ENGINE = MergeTree ORDER BY P_PARTKEY;
    50. CREATE TABLE supplier
    51. (
    52.         S_SUPPKEY       UInt32,
    53.         S_NAME          String,
    54.         S_ADDRESS       String,
    55.         S_CITY          LowCardinality(String),
    56.         S_NATION        LowCardinality(String),
    57.         S_REGION        LowCardinality(String),
    58.         S_PHONE         String
    59. )
    60. ENGINE = MergeTree ORDER BY S_SUPPKEY;

    写入数据：

    1. $ clickhouse-client --query "INSERT INTO customer FORMAT CSV" < customer.tbl
    2. $ clickhouse-client --query "INSERT INTO part FORMAT CSV" < part.tbl
    3. $ clickhouse-client --query "INSERT INTO supplier FORMAT CSV" < supplier.tbl
    4. $ clickhouse-client --query "INSERT INTO lineorder FORMAT CSV" < lineorder.tbl

    将star schema转换为flat schema：

    1. SET max_memory_usage = 20000000000;
    3. CREATE TABLE lineorder_flat
    4. ENGINE = MergeTree
    5. PARTITION BY toYear(LO_ORDERDATE)
    6. ORDER BY (LO_ORDERDATE, LO_ORDERKEY) AS
    7. SELECT
    8.     l.LO_ORDERKEY AS LO_ORDERKEY,
    9.     l.LO_LINENUMBER AS LO_LINENUMBER,
    10.     l.LO_CUSTKEY AS LO_CUSTKEY,
    12.     l.LO_SUPPKEY AS LO_SUPPKEY,
    13.     l.LO_ORDERPRIORITY AS LO_ORDERPRIORITY,
    14.     l.LO_SHIPPRIORITY AS LO_SHIPPRIORITY,
    15.     l.LO_QUANTITY AS LO_QUANTITY,
    16.     l.LO_EXTENDEDPRICE AS LO_EXTENDEDPRICE,
    17.     l.LO_ORDTOTALPRICE AS LO_ORDTOTALPRICE,
    18.     l.LO_DISCOUNT AS LO_DISCOUNT,
    19.     l.LO_REVENUE AS LO_REVENUE,
    20.     l.LO_SUPPLYCOST AS LO_SUPPLYCOST,
    21.     l.LO_TAX AS LO_TAX,
    22.     l.LO_COMMITDATE AS LO_COMMITDATE,
    23.     l.LO_SHIPMODE AS LO_SHIPMODE,
    24.     c.C_NAME AS C_NAME,
    25.     c.C_ADDRESS AS C_ADDRESS,
    26.     c.C_CITY AS C_CITY,
    27.     c.C_NATION AS C_NATION,
    28.     c.C_REGION AS C_REGION,
    29.     c.C_PHONE AS C_PHONE,
    30.     c.C_MKTSEGMENT AS C_MKTSEGMENT,
    31.     s.S_NAME AS S_NAME,
    32.     s.S_ADDRESS AS S_ADDRESS,
    33.     s.S_CITY AS S_CITY,
    34.     s.S_NATION AS S_NATION,
    35.     s.S_REGION AS S_REGION,
    36.     s.S_PHONE AS S_PHONE,
    37.     p.P_NAME AS P_NAME,
    38.     p.P_MFGR AS P_MFGR,
    39.     p.P_CATEGORY AS P_CATEGORY,
    40.     p.P_BRAND AS P_BRAND,
    41.     p.P_COLOR AS P_COLOR,
    42.     p.P_TYPE AS P_TYPE,
    43.     p.P_SIZE AS P_SIZE,
    44.     p.P_CONTAINER AS P_CONTAINER
    45. FROM lineorder AS l
    46. INNER JOIN customer AS c ON c.C_CUSTKEY = l.LO_CUSTKEY
    47. INNER JOIN supplier AS s ON s.S_SUPPKEY = l.LO_SUPPKEY
    48. INNER JOIN part AS p ON p.P_PARTKEY = l.LO_PARTKEY;

    Q1.1

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

    Q1.2

    Q1.3

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

    Q2.1

    1. SELECT
    2.     sum(LO_REVENUE),
    3.     toYear(LO_ORDERDATE) AS year,
    4.     P_BRAND
    5. FROM lineorder_flat
    6. WHERE P_CATEGORY = 'MFGR#12' AND S_REGION = 'AMERICA'
    7. GROUP BY
    8.     year,
    9.     P_BRAND
    10. ORDER BY
    11.     year,
    12.     P_BRAND;

    Q2.2

    1. SELECT
    2.     sum(LO_REVENUE),
    3.     toYear(LO_ORDERDATE) AS year,
    4.     P_BRAND
    5. FROM lineorder_flat
    6. WHERE P_BRAND >= 'MFGR#2221' AND P_BRAND <= 'MFGR#2228' AND S_REGION = 'ASIA'
    7. GROUP BY
    8.     year,
    9.     P_BRAND
    10. ORDER BY
    11.     year,
    12.     P_BRAND;

    Q2.3

    1. SELECT
    2.     sum(LO_REVENUE),
    3.     toYear(LO_ORDERDATE) AS year,
    4.     P_BRAND
    5. FROM lineorder_flat
    6. WHERE P_BRAND = 'MFGR#2239' AND S_REGION = 'EUROPE'
    7. GROUP BY
    8.     year,
    9.     P_BRAND
    10. ORDER BY
    11.     year,
    12.     P_BRAND;
    1. SELECT
    3.     toYear(LO_ORDERDATE) AS year,
    4.     sum(LO_REVENUE) AS revenue
    5. FROM lineorder_flat
    6. WHERE C_REGION = 'ASIA' AND S_REGION = 'ASIA' AND year >= 1992 AND year <= 1997
    7. GROUP BY
    8.     C_NATION,
    9.     S_NATION,
    10.     year
    11. ORDER BY
    12.     year ASC,
    13.     revenue DESC;

    Q3.2

    Q3.3

    1. SELECT
    2.     C_CITY,
    3.     S_CITY,
    4.     toYear(LO_ORDERDATE) AS year,
    5.     sum(LO_REVENUE) AS revenue
    6. FROM lineorder_flat
    7. 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
    8. GROUP BY
    9.     C_CITY,
    10.     S_CITY,
    11.     year
    12. ORDER BY
    13.     year ASC,
    14.     revenue DESC;

    Q3.4

    1. SELECT
    2.     C_CITY,
    3.     S_CITY,
    4.     toYear(LO_ORDERDATE) AS year,
    5.     sum(LO_REVENUE) AS revenue
    6. FROM lineorder_flat
    7. 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
    8. GROUP BY
    9.     C_CITY,
    10.     S_CITY,
    11.     year
    12. ORDER BY
    13.     year ASC,
    14.     revenue DESC;

    Q4.1

    1. SELECT
    2.     toYear(LO_ORDERDATE) AS year,
    3.     C_NATION,
    4.     sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
    5. FROM lineorder_flat
    6. WHERE C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND (P_MFGR = 'MFGR#1' OR P_MFGR = 'MFGR#2')
    7. GROUP BY
    8.     year,
    9.     C_NATION
    10. ORDER BY
    11.     year ASC,
    12.     C_NATION ASC;

    Q4.2

    1. SELECT
    2.     toYear(LO_ORDERDATE) AS year,
    3.     S_NATION,
    4.     P_CATEGORY,
    5.     sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
    6. FROM lineorder_flat
    7. WHERE C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND (year = 1997 OR year = 1998) AND (P_MFGR = 'MFGR#1' OR P_MFGR = 'MFGR#2')
    8. GROUP BY
    9.     year,
    10.     S_NATION,
    11.     P_CATEGORY
    12. ORDER BY
    13.     year ASC,
    14.     S_NATION ASC,
    15.     P_CATEGORY ASC;

    Q4.3

    1. SELECT
    2.     toYear(LO_ORDERDATE) AS year,
    3.     S_CITY,
    4.     P_BRAND,
    5.     sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
    6. FROM lineorder_flat
    7. WHERE S_NATION = 'UNITED STATES' AND (year = 1997 OR year = 1998) AND P_CATEGORY = 'MFGR#14'
    8. GROUP BY
    9.     year,
    10.     S_CITY,
    11.     P_BRAND
    12. ORDER BY
    13.     year ASC,
    14.     S_CITY ASC,
    15.     P_BRAND ASC;