完成SSB测试

完成SSB测试

SSB星型模式基准测试是OLAP数据库性能测试的常用场景，通过本篇教程，您可以了解到如何在MatrixOne中实现SSB测试。

准备工作

1. 编译dbgen

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

2. 生成数据

当使用-s 1时dbgen命令会生产近600万行数据(670MB)，当使用-s 10时会生产近6000万行数据，会耗费大量时间。

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. 在MatrixOne中建表

1. create database if not exists ssb;
2. use ssb;
3. drop table if exists lineorder;
4. drop table if exists part;
5. drop table if exists supplier;
6. drop table if exists customer;
7. drop table if exists dates;
8. drop table if exists lineorder_flat;

10. create table lineorder (
11. lo_orderkey bigint,
12. lo_linenumber int,
13. lo_custkey int,
14. lo_partkey int,
15. lo_suppkey int,
16. lo_orderdate date,
17. lo_orderpriority char (15),
18. lo_shippriority tinyint,
19. lo_quantity double,
20. lo_extendedprice double,
21. lo_ordtotalprice double,
22. lo_discount double,
23. lo_revenue double,
24. lo_supplycost double,
25. lo_tax double,
26. lo_commitdate date,
27. lo_shipmode char (10)
28. ) ;

30. create table part (
31. p_partkey int,
32. p_name varchar (22),
33. p_mfgr char (6),
34. p_category char (7),
35. p_brand char (9),
36. p_color varchar (11),
37. p_type varchar (25),
38. p_size int,
39. p_container char (10)
40. ) ;

42. create table supplier (
43. s_suppkey int,
44. s_name char (25),
45. s_address varchar (25),
46. s_city char (10),
47. s_nation char (15),
48. s_region char (12),
49. s_phone char (15)
50. ) ;

52. create table customer (
53. c_custkey int,
54. c_name varchar (25),
55. c_address varchar (25),
56. c_city char (10),
57. c_nation char (15),
58. c_region char (12),
59. c_phone char (15),
60. c_mktsegment char (10)
61. ) ;

63. create table dates (
64. d_datekey date,
65. d_date char (18),
66. d_dayofweek char (9),
67. d_month char (9),
68. d_yearmonthnum int,
69. d_yearmonth char (7),
70. d_daynuminweek varchar(12),
71. d_daynuminmonth int,
72. d_daynuminyear int,
73. d_monthnuminyear int,
74. d_weeknuminyear int,
75. d_sellingseason varchar (12),
76. d_lastdayinweekfl varchar (1),
77. d_lastdayinmonthfl varchar (1),
78. d_holidayfl varchar (1),
79. d_weekdayfl varchar (1)
80. ) ;

82. CREATE TABLE lineorder_flat(
83. LO_ORDERKEY bigint primary key,
84. LO_LINENUMBER int,
85. LO_CUSTKEY int,
86. LO_PARTKEY int,
87. LO_SUPPKEY int,
88. LO_ORDERDATE date,
89. LO_ORDERPRIORITY char(15),
90. LO_SHIPPRIORITY tinyint,
91. LO_QUANTITY double,
92. LO_EXTENDEDPRICE double,
93. LO_ORDTOTALPRICE double,
94. LO_DISCOUNT double,
95. LO_REVENUE int unsigned,
96. LO_SUPPLYCOST int unsigned,
97. LO_TAX double,
98. LO_COMMITDATE date,
99. LO_SHIPMODE char(10),
100. C_NAME varchar(25),
101. C_ADDRESS varchar(25),
102. C_CITY char(10),
103. C_NATION char(15),
104. C_REGION char(12),
105. C_PHONE char(15),
106. C_MKTSEGMENT char(10),
107. S_NAME char(25),
108. S_ADDRESS varchar(25),
109. S_CITY char(10),
110. S_NATION char(15),
111. S_REGION char(12),
112. S_PHONE char(15),
113. P_NAME varchar(22),
114. P_MFGR char(6),
115. P_CATEGORY char(7),
116. P_BRAND char(9),
117. P_COLOR varchar(11),
118. P_TYPE varchar(25),
119. P_SIZE int,
120. P_CONTAINER char(10)
121. );

4. 导入数据

将system_vars_config.toml参数增大，例如10GB，然后重新启动MatrixOne服务。

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

使用如下命令将数据导入相关表：

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';

接着你可以在MatrixOne中进行查询操作：如果你想要运行SSB宽表测试，你还需要lineorder_flat表数据，你可以从以下链接获取数据：

<https://pan.baidu.com/s/1dCpcKsygdVuHzd-H-RWHFA> code: k1rs

运行以下命令将数据导入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. 运行SSB测试命令

单表查询

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;

多表查询

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;