Complete a SSB Test with MatrixOne

Star Schema Benchmark（SSB）Test is a popular scenario for OLAP database performance tests. By going through this tutorial, you’ll learn how to complete a SSB Test with MatrixOne.

In v0.1.0, only single table can work in MatrixOne. The following contents can be followed to data generation, data importing and querying.

Before you begin

Make sure you have already installed MatrixOne and connected to MatrixOne Server.

1. Compile dbgen

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

2. Generate data

With -s 1 dbgen generates 6 million rows (670MB), while while -s 10 it generates 60 million rows (which takes some time)

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

3. Create tables in MatrixOne

  1. create database if not exists ssb;
  2. use ssb;
  4. drop table if exists lineorder;
  5. drop table if exists part;
  6. drop table if exists supplier;
  7. drop table if exists customer;
  8. drop table if exists dates;
  9. drop table if exists lineorder_flat;
  11. create table lineorder (
  12.         lo_orderkey bigint,
  13.         lo_linenumber int,
  14.         lo_custkey int,
  15.         lo_partkey int,
  16.         lo_suppkey int,
  17.         lo_orderdate date,
  18.         lo_orderpriority char (15),
  19.         lo_shippriority tinyint,
  20.         lo_quantity double,
  21.         lo_extendedprice double,
  22.         lo_ordtotalprice double,
  23.         lo_discount double,
  24.         lo_revenue double,
  25.         lo_supplycost double,
  26.         lo_tax double,
  27.         lo_commitdate date,
  28.         lo_shipmode char (10)
  29. ) ;
  31. create table part (
  32.         p_partkey int,
  33.         p_name varchar (22),
  34.         p_mfgr char (6),
  35.         p_category char (7),
  36.         p_brand char (9),
  37.         p_color varchar (11),
  38.         p_type varchar (25),
  39.         p_size int,
  40.         p_container char (10)
  41. ) ;
  43. create table supplier (
  44.         s_suppkey int,
  45.         s_name char (25),
  46.         s_address varchar (25),
  47.         s_city char (10),
  48.         s_nation char (15),
  49.         s_region char (12),
  50.         s_phone char (15)
  51. ) ;
  53. create table customer (
  54.         c_custkey int,
  55.         c_name varchar (25),
  56.         c_address varchar (25),
  57.         c_city char (10),
  58.         c_nation char (15),
  59.         c_region char (12),
  60.         c_phone char (15),
  61.         c_mktsegment char (10)
  62. ) ;
  64. create table dates (
  65.         d_datekey date,
  66.         d_date char (18),
  67.         d_dayofweek char (9),
  68.         d_month char (9),
  69.         d_yearmonthnum int,
  70.         d_yearmonth char (7),
  71.         d_daynuminweek varchar(12),
  72.         d_daynuminmonth int,
  73.         d_daynuminyear int,
  74.         d_monthnuminyear int,
  75.         d_weeknuminyear int,
  76.         d_sellingseason varchar (12),
  77.         d_lastdayinweekfl varchar (1),
  78.         d_lastdayinmonthfl varchar (1),
  79.         d_holidayfl varchar (1),
  80.         d_weekdayfl varchar (1)
  81. ) ;
  83. CREATE TABLE lineorder_flat(
  84.   LO_ORDERKEY bigint primary key,
  85.   LO_LINENUMBER int,
  86.   LO_CUSTKEY int,
  87.   LO_PARTKEY int,
  88.   LO_SUPPKEY int,
  89.   LO_ORDERDATE date,
  90.   LO_ORDERPRIORITY char(15),
  91.   LO_SHIPPRIORITY tinyint,
  92.   LO_QUANTITY double,
  93.   LO_EXTENDEDPRICE double,
  94.   LO_ORDTOTALPRICE double,
  95.   LO_DISCOUNT double,
  96.   LO_REVENUE int unsigned,
  97.   LO_SUPPLYCOST int unsigned,
  98.   LO_TAX double,
  99.   LO_COMMITDATE date,
  100.   LO_SHIPMODE char(10),
  101.   C_NAME varchar(25),
  102.   C_ADDRESS varchar(25),
  103.   C_CITY char(10),
  104.   C_NATION char(15),
  105.   C_REGION char(12),
  106.   C_PHONE char(15),
  107.   C_MKTSEGMENT char(10),
  108.   S_NAME char(25),
  109.   S_ADDRESS varchar(25),
  110.   S_CITY char(10),
  111.   S_NATION char(15),
  112.   S_REGION char(12),
  113.   S_PHONE char(15),
  114.   P_NAME varchar(22),
  115.   P_MFGR char(6),
  116.   P_CATEGORY char(7),
  117.   P_BRAND char(9),
  118.   P_COLOR varchar(11),
  119.   P_TYPE varchar(25),
  120.   P_SIZE int,
  121.   P_CONTAINER char(10)
  122. );

4. Load data into the created tables

Modify the parameter of system_vars_config.toml to a larger one in matrixone directory, such as 10GB. And restart MatrixOne service.

  1. max-entry-bytes = "10GB"

Load data into related tables with this command in MatrixOne.

  1. load data infile '/ssb-dbgen-path/supplier.tbl' into table supplier FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
  3. load data infile '/ssb-dbgen-path/customer.tbl' into table customer FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
  5. load data infile '/ssb-dbgen-path/date.tbl' into table dates FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
  7. load data infile '/ssb-dbgen-path/part.tbl' into table part FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
  9. load data infile '/ssb-dbgen-path/lineorder.tbl' into table lineorder FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

Then you can query data in MatrixOne with the created table. If you want to run a single table SSB query test, there is still one more data files needed for lineorder_flat. You can get the data files directly:

https://community-shared-data-1308875761.cos.ap-beijing.myqcloud.com/lineorder\_flat.tar.bz2

Load data into lineorder_flat.

  1. load data infile '/ssb-dbgen-path/lineorder_flat.tbl ' into table lineorder_flat FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

5. Run SSB Queries

Flat table queries

  1. --Q1.1
  2. SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue FROM lineorder_flat WHERE year(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 year(LO_ORDERDATE)=1994 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 d_yearmonthnum=1994 AND LO_DISCOUNT BETWEEN 5 AND 7 AND LO_QUANTITY BETWEEN 26 AND 35;
  10. --Q2.1
  11. SELECT sum(LO_REVENUE),year(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), year(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), year(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, year(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue FROM lineorder_flat WHERE C_REGION = 'ASIA' AND S_REGION = 'ASIA' AND year(LO_ORDERDATE)  between 1992 AND 1997 GROUP BY C_NATION, S_NATION, year(LO_ORDERDATE) ORDER BY year asc, revenue desc;
  22. --Q3.2
  23. SELECT C_CITY, S_CITY, year(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue FROM lineorder_flat WHERE C_NATION = 'CHINA' AND S_NATION = 'CHINA' AND year(LO_ORDERDATE)  between 1992 AND 1997 GROUP BY C_CITY, S_CITY, year(LO_ORDERDATE)  ORDER BY year asc, revenue desc;
  25. --Q3.3
  26. SELECT C_CITY, S_CITY, year(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue FROM lineorder_flat WHERE (C_CITY = 'UNITED KI0' OR C_CITY = 'UNITED KI7') AND (S_CITY = 'UNITED KI0' OR S_CITY = 'UNITED KI7') AND year(LO_ORDERDATE)  between 1992 AND 1997 GROUP BY C_CITY, S_CITY, year(LO_ORDERDATE) ORDER BY year asc, revenue desc;
  28. --Q3.4
  29. SELECT C_CITY, S_CITY, year(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue FROM lineorder_flat WHERE (C_CITY = 'UNITED KI0' OR C_CITY = 'UNITED KI7') AND (S_CITY = 'MOZAMBIQU1' OR S_CITY = 'KENYA    4') AND year(LO_ORDERDATE)= 1997 GROUP BY C_CITY, S_CITY, year(LO_ORDERDATE) ORDER BY year asc, revenue desc;
  31. --Q4.1
  32. SELECT year(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(LO_ORDERDATE), C_NATION ORDER BY year, C_NATION;
  34. --Q4.2
  35. SELECT year(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(LO_ORDERDATE) = 1997 OR year(LO_ORDERDATE) = 1998) AND (P_MFGR = 'MFGR#1' OR P_MFGR = 'MFGR#2') GROUP BY  year(LO_ORDERDATE), S_NATION, P_CATEGORY ORDER BY year, S_NATION, P_CATEGORY;
  37. --Q4.3
  38. SELECT year(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(LO_ORDERDATE) = 1997 OR year(LO_ORDERDATE) = 1998) AND P_CATEGORY = 'MFGR#14' GROUP BY  year(LO_ORDERDATE), S_CITY, P_BRAND ORDER BY year, S_CITY, P_BRAND;

Multiple tables queries

  1. --Q1.1
  2. select sum(lo_revenue) as revenue
  3. from lineorder join dates on lo_orderdate = d_datekey
  4. where year(d_datekey)  = 1993 and lo_discount between 1 and 3 and lo_quantity < 25;
  6. --Q1.2
  7. select sum(lo_revenue) as revenue
  8. from lineorder
  9. join dates on lo_orderdate = d_datekey
  10. where d_yearmonthnum = 199401
  11. and lo_discount between 4 and 6
  12. and lo_quantity between 26 and 35;
  14. --Q1.3
  15. select sum(lo_revenue) as revenue
  16. from lineorder
  17. join dates on lo_orderdate = d_datekey
  18. where d_weeknuminyear = 6 and year(d_datekey)  = 1994
  19. and lo_discount between 5 and 7
  20. and lo_quantity between 26 and 35;
  22. --Q2.1
  23. select sum(lo_revenue) as lo_revenue, year(d_datekey) as year, p_brand
  24. from lineorder
  25. join dates on lo_orderdate = d_datekey
  26. join part on lo_partkey = p_partkey
  27. join supplier on lo_suppkey = s_suppkey
  28. where p_category = 'MFGR#12' and s_region = 'AMERICA'
  29. group by year, p_brand
  30. order by year, p_brand;
  32. --Q2.2
  33. select sum(lo_revenue) as lo_revenue, year(d_datekey) as year, p_brand
  34. from lineorder
  35. join dates on lo_orderdate = d_datekey
  36. join part on lo_partkey = p_partkey
  37. join supplier on lo_suppkey = s_suppkey
  38. where p_brand between 'MFGR#2221' and 'MFGR#2228' and s_region = 'ASIA'
  39. group by year, p_brand
  40. order by year, p_brand;
  42. --Q2.3
  43. select sum(lo_revenue) as lo_revenue, year(d_datekey) as year, p_brand
  44. from lineorder
  45. join dates on lo_orderdate = d_datekey
  46. join part on lo_partkey = p_partkey
  47. join supplier on lo_suppkey = s_suppkey
  48. where p_brand = 'MFGR#2239' and s_region = 'EUROPE'
  49. group by year, p_brand
  50. order by year, p_brand;
  52. --Q3.1
  53. select c_nation, s_nation, year(d_datekey) as year, sum(lo_revenue) as lo_revenue
  54. from lineorder
  55. join dates on lo_orderdate = d_datekey
  56. join customer on lo_custkey = c_custkey
  57. join supplier on lo_suppkey = s_suppkey
  58. where c_region = 'ASIA' and s_region = 'ASIA' and year(d_datekey) between 1992 and 1997
  59. group by c_nation, s_nation, year
  60. order by year asc, lo_revenue desc;
  62. --Q3.2
  63. select c_city, s_city, year(d_datekey) as year, sum(lo_revenue) as lo_revenue
  64. from lineorder
  65. join dates on lo_orderdate = d_datekey
  66. join customer on lo_custkey = c_custkey
  67. join supplier on lo_suppkey = s_suppkey
  68. where c_nation = 'UNITED STATES' and s_nation = 'UNITED STATES'
  69. and year(d_datekey) between 1992 and 1997
  70. group by c_city, s_city, year
  71. order by year asc, lo_revenue desc;
  73. --Q3.3
  74. select c_city, s_city, year(d_datekey) as year, sum(lo_revenue) as lo_revenue
  75. from lineorder
  76. join dates on lo_orderdate = d_datekey
  77. join customer on lo_custkey = c_custkey
  78. join supplier on lo_suppkey = s_suppkey
  79. where (c_city='UNITED KI1' or c_city='UNITED KI5')
  80. and (s_city='UNITED KI1' or s_city='UNITED KI5')
  81. and year(d_datekey) between 1992 and 1997
  82. group by c_city, s_city, year
  83. order by year asc, lo_revenue desc;
  85. --Q3.4
  86. select c_city, s_city, year(d_datekey) as year, sum(lo_revenue) as lo_revenue
  87. from lineorder
  88. join dates on lo_orderdate = d_datekey
  89. join customer on lo_custkey = c_custkey
  90. join supplier on lo_suppkey = s_suppkey
  91. where (c_city='UNITED KI1' or c_city='UNITED KI5') and (s_city='UNITED KI1' or s_city='UNITED KI5') and d_yearmonth = 'Dec1997'
  92. group by c_city, s_city, year
  93. order by year asc, lo_revenue desc;
  95. --Q4.1
  96. select year(d_datekey) as year, c_nation, sum(lo_revenue) - sum(lo_supplycost) as profit
  97. from lineorder
  98. join dates on lo_orderdate = d_datekey
  99. join customer on lo_custkey = c_custkey
  100. join supplier on lo_suppkey = s_suppkey
  101. join part on lo_partkey = p_partkey
  102. where c_region = 'AMERICA' and s_region = 'AMERICA' and (p_mfgr = 'MFGR#1' or p_mfgr = 'MFGR#2')
  103. group by year, c_nation
  104. order by year, c_nation;
  106. --Q4.2
  107. select year(d_datekey) as year, s_nation, p_category, sum(lo_revenue) - sum(lo_supplycost) as profit
  108. from lineorder
  109. join dates on lo_orderdate = d_datekey
  110. join customer on lo_custkey = c_custkey
  111. join supplier on lo_suppkey = s_suppkey
  112. join part on lo_partkey = p_partkey
  113. where c_region = 'AMERICA'and s_region = 'AMERICA'
  114. and (year(d_datekey) = 1997 or year(d_datekey) = 1998)
  115. and (p_mfgr = 'MFGR#1' or p_mfgr = 'MFGR#2')
  116. group by year, s_nation, p_category
  117. order by year, s_nation, p_category;
  119. --Q4.3
  120. select year(d_datekey) as year, s_city, p_brand, sum(lo_revenue) - sum(lo_supplycost) as profit,c_region , s_nation, p_category
  121. from lineorder
  122. join dates on lo_orderdate = d_datekey
  123. join customer on lo_custkey = c_custkey
  124. join supplier on lo_suppkey = s_suppkey
  125. join part on lo_partkey = p_partkey
  126. where
  127. (year(d_datekey) = 1997 or year(d_datekey) = 1998)
  128. and s_nation='ALGERIA'
  129. group by year, s_city, p_brand
  130. order by year, s_city, p_brand;