Star Schema Benchmark

Compiling dbgen:

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

Generating data:

  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

Creating tables in 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;

Inserting data:

  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

Converting “star schema” to denormalized “flat schema”:

  1. SET max_memory_usage = 20000000000, allow_experimental_multiple_joins_emulation = 1;
  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. FROM lineorder
  9. ANY INNER JOIN customer ON LO_CUSTKEY = C_CUSTKEY
  10. ANY INNER JOIN supplier ON LO_SUPPKEY = S_SUPPKEY
  11. ANY INNER JOIN part ON LO_PARTKEY = P_PARTKEY;
  13. ALTER TABLE lineorder_flat DROP COLUMN C_CUSTKEY, DROP COLUMN S_SUPPKEY, DROP COLUMN P_PARTKEY;

Running the queries:

  1. Q1.1
  2. 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;
  4. Q1.2
  5. 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;
  7. Q1.3
  8. 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;
  10. Q2.1
  11. 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;
  13. Q2.2
  14. SELECT sum(LO_REVENUE), toYear(LO_ORDERDATE) AS year, P_BRAND FROM lineorder_flat WHERE P_BRAND BETWEEN 'MFGR#2221' AND 'MFGR#2228' AND S_REGION = 'ASIA' GROUP BY year, P_BRAND ORDER BY year, P_BRAND;
  16. Q2.3
  17. 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;
  19. Q3.1
  20. 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;
  22. Q3.2
  23. 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;
  25. Q3.3
  26. 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;
  28. Q3.4
  29. 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;
  31. Q4.1
  32. 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, C_NATION;
  34. Q4.2
  35. 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, S_NATION, P_CATEGORY;
  37. Q4.3
  38. 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, S_CITY, P_BRAND;

Original article