Star Schema Benchmark

编译 dbgen:

  1. $ git clone [email protected]:vadimtk/ssb-dbgen.git
  2. $ cd ssb-dbgen
  3. $ make

开始生成数据:

注意

使用-s 100dbgen 将生成 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);
  13. CREATE TABLE lineorder
  14. (
  15. LO_ORDERKEY UInt32,
  16. LO_LINENUMBER UInt8,
  17. LO_CUSTKEY UInt32,
  18. LO_PARTKEY UInt32,
  19. LO_SUPPKEY UInt32,
  20. LO_ORDERDATE Date,
  21. LO_ORDERPRIORITY LowCardinality(String),
  22. LO_SHIPPRIORITY UInt8,
  23. LO_QUANTITY UInt8,
  24. LO_EXTENDEDPRICE UInt32,
  25. LO_ORDTOTALPRICE UInt32,
  26. LO_DISCOUNT UInt8,
  27. LO_REVENUE UInt32,
  28. LO_SUPPLYCOST UInt32,
  29. LO_TAX UInt8,
  30. LO_COMMITDATE Date,
  31. LO_SHIPMODE LowCardinality(String)
  32. )
  33. ENGINE = MergeTree PARTITION BY toYear(LO_ORDERDATE) ORDER BY (LO_ORDERDATE, LO_ORDERKEY);
  34. CREATE TABLE part
  35. (
  36. P_PARTKEY UInt32,
  37. P_NAME String,
  38. P_MFGR LowCardinality(String),
  39. P_CATEGORY LowCardinality(String),
  40. P_BRAND LowCardinality(String),
  41. P_COLOR LowCardinality(String),
  42. P_TYPE LowCardinality(String),
  43. P_SIZE UInt8,
  44. P_CONTAINER LowCardinality(String)
  45. )
  46. ENGINE = MergeTree ORDER BY P_PARTKEY;
  47. CREATE TABLE supplier
  48. (
  49. S_SUPPKEY UInt32,
  50. S_NAME String,
  51. S_ADDRESS String,
  52. S_CITY LowCardinality(String),
  53. S_NATION LowCardinality(String),
  54. S_REGION LowCardinality(String),
  55. S_PHONE String
  56. )
  57. 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;
  2. CREATE TABLE lineorder_flat
  3. ENGINE = MergeTree
  4. PARTITION BY toYear(LO_ORDERDATE)
  5. ORDER BY (LO_ORDERDATE, LO_ORDERKEY) AS
  6. SELECT
  7. l.LO_ORDERKEY AS LO_ORDERKEY,
  8. l.LO_LINENUMBER AS LO_LINENUMBER,
  9. l.LO_CUSTKEY AS LO_CUSTKEY,
  10. l.LO_PARTKEY AS LO_PARTKEY,
  11. l.LO_SUPPKEY AS LO_SUPPKEY,
  12. l.LO_ORDERDATE AS LO_ORDERDATE,
  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

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

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;

Q3.1

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

Q3.2

  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_NATION = 'UNITED STATES' AND S_NATION = 'UNITED STATES' 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.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;