mysql shell py load command for SSB

gistfile1.txt

util.import_table("/data/lineorder.tbl", {"schema": "ssb_sf100_warp", "table": "lineorder", "fieldsTerminatedBy": "|", "skipRows": 0, "showProgress": "true", "threads": 12});
100% (7.95 GB / 7.95 GB), 30.94 MB/s
File '/data/lineorder.tbl' (7.95 GB) was imported in 2 min 31.2739 sec at 52.53 MB/s
Total rows affected in ssb.lineorder: Records: 71985077  Deleted: 0  Skipped: 0  Warnings: 0
 MySQL  127.0.0.1:3306 ssl  Py > util.import_table("/data/date.tbl", {"schema": "ssb", "table": "dim_date", "dialect": "csv-unix", "skipRows": 0, "showProgress": "true", "threads": 12});
Importing from file '/data/date.tbl' to table `ssb`.`dim_date` in MySQL Server at 127.0.0.1:3306 using 1 thread
[Worker000] date.tbl: Records: 2556  Deleted: 0  Skipped: 0  Warnings: 0
100% (273.42 KB / 273.42 KB), 0.00 B/s
File '/data/date.tbl' (273.42 KB) was imported in 0.0586 sec at 273.42 KB/s
Total rows affected in ssb.dim_date: Records: 2556  Deleted: 0  Skipped: 0  Warnings: 0
 MySQL  127.0.0.1:3306 ssl  Py > util.import_table("/data/customer.tbl", {"schema": "ssb", "table": "customer", "dialect": "csv-unix", "skipRows": 0, "showProgress": "true", "threads": 12});
Importing from file '/data/customer.tbl' to table `ssb`.`customer` in MySQL Server at 127.0.0.1:3306 using 1 thread
[Worker000] customer.tbl: Records: 360000  Deleted: 0  Skipped: 0  Warnings: 0
100% (39.09 MB / 39.09 MB), 24.62 MB/s
File '/data/customer.tbl' (39.09 MB) was imported in 1.5882 sec at 24.61 MB/s
Total rows affected in ssb.customer: Records: 360000  Deleted: 0  Skipped: 0  Warnings: 0
 MySQL  127.0.0.1:3306 ssl  Py > util.import_table("/data/supplier.tbl", {"schema": "ssb", "table": "supplier", "dialect": "csv-unix", "skipRows": 0, "showProgress": "true", "threads": 12});
Importing from file '/data/supplier.tbl' to table `ssb`.`supplier` in MySQL Server at 127.0.0.1:3306 using 1 thread
[Worker000] supplier.tbl: Records: 24000  Deleted: 0  Skipped: 0  Warnings: 0
100% (2.29 MB / 2.29 MB), 0.00 B/s
File '/data/supplier.tbl' (2.29 MB) was imported in 0.1365 sec at 2.29 MB/s
Total rows affected in ssb.supplier: Records: 24000  Deleted: 0  Skipped: 0  Warnings: 0
 MySQL  127.0.0.1:3306 ssl  Py > util.import_table("/data/part.tbl", {"schema": "ssb", "table": "part", "dialect": "csv-unix", "skipRows": 0, "showProgress": "true", "threads": 12});
Importing from file '/data/part.tbl' to table `ssb`.`part` in MySQL Server at 127.0.0.1:3306 using 2 threads
[Worker001] part.tbl: Records: 295106  Deleted: 0  Skipped: 0  Warnings: 0
[Worker000] part.tbl: Records: 504894  Deleted: 0  Skipped: 0  Warnings: 0
100% (79.29 MB / 79.29 MB), 27.40 MB/s
File '/data/part.tbl' (79.29 MB) was imported in 2.8948 sec at 27.39 MB/s
Total rows affected in ssb.part: Records: 800000  Deleted: 0  Skipped: 0  Warnings: 0


-- Q0.1
 MySQL  127.0.0.1:3306 ssl  ssb  SQL > select count(*) from lineorder;
+----------+
| count(*) |
+----------+
| 71985077 |
+----------+
1 row in set (11.4380 sec)
 MySQL  127.0.0.1:3306 ssl  ssb  SQL > select count(*) from lineorder;
+----------+
| count(*) |
+----------+
| 71985077 |
+----------+
1 row in set (11.3356 sec)



-- Q1.1
select sum(lo_extendedprice*lo_discount) as revenue from lineorder join  dim_date on lo_orderdatekey = d_datekey where d_year = 1993 and lo_discount between 1 and 3 and lo_quantity < 25;
+--------------+
| revenue      |
+--------------+
| 965049065847 |
+--------------+
1 row in set (20.7530 sec)
+--------------+
| revenue      |
+--------------+
| 965049065847 |
+--------------+
1 row in set (0.4531 sec)

-- Q1.2
select sum(lo_extendedprice*lo_discount) as revenue from lineorder join  dim_date on lo_orderdatekey = d_datekey where  d_yearmonthnum = 199401 and lo_discount between 4 and 6 and lo_quantity between 26 and 35;
+--------------+
| revenue      |
+--------------+
| 965049065847 |
+--------------+
1 row in set (0.4950 sec)
+--------------+
| revenue      |
+--------------+
| 965049065847 |
+--------------+
1 row in set (0.4259 sec)

-- Q1.3
select sum(lo_extendedprice*lo_discount) as revenue from lineorder join  dim_date on lo_orderdatekey = d_datekey where  d_weeknuminyear = 6 and d_year = 1994 and lo_discount between 5 and 7 and lo_quantity between 26 and 35;
+--------------+
| revenue      |
+--------------+
| 261356323969 |
+--------------+
1 row in set (0.3148 sec)
+--------------+
| revenue      |
+--------------+
| 261356323969 |
+--------------+
1 row in set (0.3072 sec)

-- Q2.1
select sum(lo_revenue), d_year, p_brand
from lineorder
join dim_date
  on lo_orderdatekey = d_datekey
join part
  on lo_partkey = p_partkey
join supplier
  on lo_suppkey = s_suppkey
where 
p_category = 'MFGR#12'
and s_region = 'AMERICA'
group by d_year, p_brand
order by d_year, p_brand;
|      3945522796 |   1998 | MFGR#127  |
|      3942185810 |   1998 | MFGR#128  |
|      3361726461 |   1998 | MFGR#129  |
+-----------------+--------+-----------+
280 rows in set (2 min 21.9012 sec)
|      3945522796 |   1998 | MFGR#127  |
|      3942185810 |   1998 | MFGR#128  |
|      3361726461 |   1998 | MFGR#129  |
+-----------------+--------+-----------+
280 rows in set (2 min 13.9965 sec)


-- Q2.2
select sum(lo_revenue), d_year, p_brand
from lineorder
join dim_date
  on lo_orderdatekey = d_datekey
join part
  on lo_partkey = p_partkey
join supplier
  on lo_suppkey = s_suppkey
where 
p_brand between 'MFGR#2221' and 'MFGR#2228'
and s_region = 'ASIA'
group by d_year, p_brand
order by d_year, p_brand;
|      3945522796 |   1998 | MFGR#127  |
|      3942185810 |   1998 | MFGR#128  |
|      3361726461 |   1998 | MFGR#129  |
+-----------------+--------+-----------+
280 rows in set (2 min 13.4926 sec)
|      3945522796 |   1998 | MFGR#127  |
|      3942185810 |   1998 | MFGR#128  |
|      3361726461 |   1998 | MFGR#129  |
+-----------------+--------+-----------+
280 rows in set (2 min 13.0293 sec)


-- Q2.3
select sum(lo_revenue), d_year, p_brand
from lineorder
join dim_date
  on lo_orderdatekey = d_datekey
join part
  on lo_partkey = p_partkey
join supplier
  on lo_suppkey = s_suppkey
where 
p_brand= 'MFGR#2239'
and s_region = 'EUROPE'
group by d_year, p_brand
order by d_year, p_brand;
+-----------------+--------+-----------+
| sum(lo_revenue) | d_year | p_brand   |
+-----------------+--------+-----------+
|      6461032309 |   1992 | MFGR#2239 |
|      6491793353 |   1993 | MFGR#2239 |
|      6138599572 |   1994 | MFGR#2239 |
|      6401965581 |   1995 | MFGR#2239 |
|      6380864363 |   1996 | MFGR#2239 |
|      6616808054 |   1997 | MFGR#2239 |
|      3737796454 |   1998 | MFGR#2239 |
+-----------------+--------+-----------+
7 rows in set (2 min 11.7652 sec)
+-----------------+--------+-----------+
| sum(lo_revenue) | d_year | p_brand   |
+-----------------+--------+-----------+
|      6461032309 |   1992 | MFGR#2239 |
|      6491793353 |   1993 | MFGR#2239 |
|      6138599572 |   1994 | MFGR#2239 |
|      6401965581 |   1995 | MFGR#2239 |
|      6380864363 |   1996 | MFGR#2239 |
|      6616808054 |   1997 | MFGR#2239 |
|      3737796454 |   1998 | MFGR#2239 |
+-----------------+--------+-----------+
7 rows in set (2 min 10.5868 sec)



-- Q3.1
select c_nation, s_nation, d_year,
sum(lo_revenue) as revenue
from customer
join lineorder
  on lo_custkey = c_customerkey
join supplier
  on lo_suppkey = s_suppkey
join dim_date
  on lo_orderdatekey = d_datekey
where
c_region = 'ASIA'
and s_region = 'ASIA'
and d_year >= 1992 and d_year <= 1997
group by c_nation, s_nation, d_year
order by d_year asc, revenue desc;
| VIETNAM   | VIETNAM   |   1997 | 50463506282 |
| INDIA     | VIETNAM   |   1997 | 50374353273 |
| JAPAN     | VIETNAM   |   1997 | 49162418930 |
+-----------+-----------+--------+-------------+
150 rows in set (1 min 8.7509 sec)
| VIETNAM   | VIETNAM   |   1997 | 50463506282 |
| INDIA     | VIETNAM   |   1997 | 50374353273 |
| JAPAN     | VIETNAM   |   1997 | 49162418930 |
+-----------+-----------+--------+-------------+
150 rows in set (52.1339 sec)


-- Q3.2
select c_city, s_city, d_year, sum(lo_revenue)
as revenue
from customer
join lineorder
  on lo_custkey = c_customerkey
join supplier
  on lo_suppkey = s_suppkey
join dim_date
  on lo_orderdatekey = d_datekey
where
c_nation = 'UNITED STATES'
and s_nation = 'UNITED STATES'
and d_year >= 1992 and d_year <= 1997
group by c_city, s_city, d_year
order by d_year asc, revenue desc;
| UNITED ST0 | UNITED ST3 |   1997 | 385372165 |
| UNITED ST4 | UNITED ST2 |   1997 | 334014039 |
| UNITED ST0 | UNITED ST2 |   1997 | 313110196 |
+------------+------------+--------+-----------+
600 rows in set (15.2573 sec)
| UNITED ST0 | UNITED ST3 |   1997 | 385372165 |
| UNITED ST4 | UNITED ST2 |   1997 | 334014039 |
| UNITED ST0 | UNITED ST2 |   1997 | 313110196 |
+------------+------------+--------+-----------+
600 rows in set (15.2508 sec)

-- Q3.3
select c_city, s_city, d_year, sum(lo_revenue)
as revenue
from customer
join lineorder
  on lo_custkey = c_customerkey
join supplier
  on lo_suppkey = s_suppkey
join dim_date
  on lo_orderdatekey = d_datekey
where
(c_city='UNITED KI1' or c_city='UNITED KI5')
and (s_city='UNITED KI1' or s_city='UNITED KI5')
and d_year >= 1992 and d_year <= 1997
group by c_city, s_city, d_year
order by d_year asc, revenue desc;
| UNITED KI1 | UNITED KI1 |   1997 | 527558759 |
| UNITED KI1 | UNITED KI5 |   1997 | 487301847 |
| UNITED KI5 | UNITED KI5 |   1997 | 487127099 |
+------------+------------+--------+-----------+
24 rows in set (2.4270 sec)
| UNITED KI1 | UNITED KI1 |   1997 | 527558759 |
| UNITED KI1 | UNITED KI5 |   1997 | 487301847 |
| UNITED KI5 | UNITED KI5 |   1997 | 487127099 |
+------------+------------+--------+-----------+
24 rows in set (2.3646 sec)


-- Q3.4
select c_city, s_city, d_year, sum(lo_revenue)
as revenue
from customer
join lineorder
  on lo_custkey = c_customerkey
join supplier
  on lo_suppkey = s_suppkey
join dim_date
  on lo_orderdatekey = d_datekey
where
(c_city='UNITED KI1' or c_city='UNITED KI5')
and (s_city='UNITED KI1' or s_city='UNITED KI5')
and d_yearmonth = 'Dec1997'
group by c_city, s_city, d_year
order by d_year asc, revenue desc;
+------------+------------+--------+----------+
| c_city     | s_city     | d_year | revenue  |
+------------+------------+--------+----------+
| UNITED KI1 | UNITED KI1 |   1997 | 63303088 |
| UNITED KI1 | UNITED KI5 |   1997 | 45388550 |
| UNITED KI5 | UNITED KI1 |   1997 | 44641976 |
| UNITED KI5 | UNITED KI5 |   1997 | 32600020 |
+------------+------------+--------+----------+
4 rows in set (1.8731 sec)
+------------+------------+--------+----------+
| c_city     | s_city     | d_year | revenue  |
+------------+------------+--------+----------+
| UNITED KI1 | UNITED KI1 |   1997 | 63303088 |
| UNITED KI1 | UNITED KI5 |   1997 | 45388550 |
| UNITED KI5 | UNITED KI1 |   1997 | 44641976 |
| UNITED KI5 | UNITED KI5 |   1997 | 32600020 |
+------------+------------+--------+----------+
4 rows in set (1.8918 sec)

-- Q4.1
select d_year, c_nation,
sum(lo_revenue - lo_supplycost) as profit
from lineorder
join dim_date 
  on lo_orderdatekey = d_datekey
join customer
  on lo_custkey = c_customerkey
join supplier
  on lo_suppkey = s_suppkey
join part
  on lo_partkey = p_partkey
where
c_region = 'AMERICA'
and s_region = 'AMERICA'
and (p_mfgr = 'MFGR#1'
or p_mfgr = 'MFGR#2')
group by d_year, c_nation
order by d_year, c_nation;
|   1998 | CANADA        |  63221521425 |
|   1998 | PERU          |  60776071215 |
|   1998 | UNITED STATES |  61345891337 |
+--------+---------------+--------------+
35 rows in set (3 min 4.0839 sec)
|   1998 | CANADA        |  63221521425 |
|   1998 | PERU          |  60776071215 |
|   1998 | UNITED STATES |  61345891337 |
+--------+---------------+--------------+
35 rows in set (2 min 59.4382 sec)

-- Q4.2
select d_year, s_nation, p_category,
sum(lo_revenue - lo_supplycost) as profit
from lineorder
join dim_date 
  on lo_orderdatekey = d_datekey
join customer
  on lo_custkey = c_customerkey
join supplier
  on lo_suppkey = s_suppkey
join part
  on lo_partkey = p_partkey
where
c_region = 'AMERICA'
and s_region = 'AMERICA'
and (d_year = 1997 or d_year = 1998)
and (p_mfgr = 'MFGR#1'
or p_mfgr = 'MFGR#2')
group by d_year, s_nation, p_category
order by d_year, s_nation, p_category;
|   1998 | UNITED STATES | MFGR#23    |  5931179314 |
|   1998 | UNITED STATES | MFGR#24    |  6115408091 |
|   1998 | UNITED STATES | MFGR#25    |  6295963298 |
+--------+---------------+------------+-------------+
100 rows in set (1 min 0.0734 sec)
|   1998 | UNITED STATES | MFGR#23    |  5931179314 |
|   1998 | UNITED STATES | MFGR#24    |  6115408091 |
|   1998 | UNITED STATES | MFGR#25    |  6295963298 |
+--------+---------------+------------+-------------+
100 rows in set (44.5042 sec)


-- Q4.3
select d_year, s_city, p_brand,
sum(lo_revenue - lo_supplycost) as profit
from lineorder
join dim_date 
  on lo_orderdatekey = d_datekey
join customer
  on lo_custkey = c_customerkey
join supplier
  on lo_suppkey = s_suppkey
join part
  on lo_partkey = p_partkey
where
s_nation = 'UNITED STATES'
and (d_year = 1997 or d_year = 1998)
and p_category = 'MFGR#14'
group by d_year, s_city, p_brand
order by d_year, s_city, p_brand;
|   1998 | UNITED ST9 | MFGR#147  |  72830474 |
|   1998 | UNITED ST9 | MFGR#148  |  65976845 |
|   1998 | UNITED ST9 | MFGR#149  |  89906813 |
+--------+------------+-----------+-----------+
800 rows in set (15.4460 sec)
|   1998 | UNITED ST9 | MFGR#147  |  72830474 |
|   1998 | UNITED ST9 | MFGR#148  |  65976845 |
|   1998 | UNITED ST9 | MFGR#149  |  89906813 |
+--------+------------+-----------+-----------+
800 rows in set (15.4460 sec)



-- Q5.1
select count(*) from lineorder where LO_Revenue > (select avg(LO_Revenue) from lineorder where LO_LineNumber between 1 and 3);

+----------+
| count(*) |
+----------+
| 28687547 |
+----------+
1 row in set (1 min 30.6761 sec)


-- Q5.2
select count(*) from lineorder where LO_Revenue > (select avg(LO_Revenue) from lineorder where LO_LineNumber = 7);

gistfile2.txt

util.import_table("/home/justin/warp/storage/warp/benchmark/ssb-dbgen/lineorder.tbl.*", {"schema": "ssb", "table": "lineorder", "dialect": "csv-unix", "skipRows": 0, "showProgress": "true", "threads": 12});
util.import_table("/home/justin/warp/storage/warp/benchmark/ssb-dbgen/date.tbl", {"schema": "ssb", "table": "dim_date", "dialect": "csv-unix", "skipRows": 0, "showProgress": "true", "threads": 12});
util.import_table("/home/justin/warp/storage/warp/benchmark/ssb-dbgen/part.tbl", {"schema": "ssb", "table": "part", "dialect": "csv-unix", "skipRows": 0, "showProgress": "true", "threads": 12});
util.import_table("/home/justin/warp/storage/warp/benchmark/ssb-dbgen/supplier.tbl", {"schema": "ssb", "table": "supplier", "dialect": "csv-unix", "skipRows": 0, "showProgress": "true", "threads": 12});
util.import_table("/home/justin/warp/storage/warp/benchmark/ssb-dbgen/customer.tbl", {"schema": "ssb", "table": "customer", "dialect": "csv-unix", "skipRows": 0, "showProgress": "true", "threads": 12});

mysql> select sum(lo_extendedprice*lo_discount) as revenue from lineorder join  dim_date on lo_orderdatekey = d_datekey where  d_yearmonthnum = 199401 and lo_discount between 4 and 6 and lo_quantity between 26 and 35;
+---------------+
| revenue       |
+---------------+
| 1926447117517 |
+---------------+
1 row in set (2.52 sec)

Query OK, 0 rows affected (2.52 sec)

mysql> select sum(lo_extendedprice*lo_discount) as revenue from lineorder join  dim_date on lo_orderdatekey = d_datekey where d_year = 1993 and lo_discount between 1 and 3 and lo_quantity < 25;
+---------------+
| revenue       |
+---------------+
| 8939702894840 |
+---------------+
1 row in set (2.58 sec)

mysql> select sum(lo_extendedprice*lo_discount) as revenue from lineorder join  dim_date on lo_orderdatekey = d_datekey where  d_yearmonthnum = 199401 and lo_discount between 4 and 6 and lo_quantity between 26 and 35;
+---------------+
| revenue       |
+---------------+
| 1926447117517 |
+---------------+
1 row in set (4.60 sec)

Query OK, 0 rows affected (4.60 sec)

mysql> select sum(lo_extendedprice*lo_discount) as revenue from lineorder join  dim_date on lo_orderdatekey = d_datekey where  d_weeknuminyear = 6 and d_year = 1994 and lo_discount between 5 and 7 and lo_quantity between 26 and 35;
+--------------+
| revenue      |
+--------------+
| 519638498830 |
+--------------+
1 row in set (1.66 sec)

mysql> select sum(lo_extendedprice*lo_discount) as revenue from lineorder join  dim_date on lo_orderdatekey = d_datekey where  d_weeknuminyear = 6 and d_year = 1994 and lo_discount between 5 and 7 and lo_quantity between 26 and 35;
+--------------+
| revenue      |
+--------------+
| 519638498830 |
+--------------+
1 row in set (1.70 sec)

|      7538377911 |   1998 | MFGR#126  |
|      7811046637 |   1998 | MFGR#127  |
|      7790578551 |   1998 | MFGR#128  |
|      6961936311 |   1998 | MFGR#129  |
+-----------------+--------+-----------+
280 rows in set (6.18 sec)

Query OK, 0 rows affected (6.18 sec)

|      7811396637 |   1998 | MFGR#2225 |
|      7729246433 |   1998 | MFGR#2226 |
|      8013433999 |   1998 | MFGR#2227 |
|      8306416932 |   1998 | MFGR#2228 |
+-----------------+--------+-----------+
56 rows in set (1 min 2.65 sec)

mysql>                                                                                                                                                                                                             mysql> select sum(lo_revenue), d_year, p_brand                                                                                                                                                                         -> from lineorder
    -> join dim_date
    ->   on lo_orderdatekey = d_datekey
    -> join part
    ->   on lo_partkey = p_partkey
    -> join supplier
    ->   on lo_suppkey = s_suppkey
    -> where
    -> p_brand= 'MFGR#2239'
    -> and s_region = 'EUROPE'
    -> group by d_year, p_brand
    -> order by d_year, p_brand;
+-----------------+--------+-----------+
| sum(lo_revenue) | d_year | p_brand   |
+-----------------+--------+-----------+
|     12779281559 |   1992 | MFGR#2239 |
|     12983600841 |   1993 | MFGR#2239 |
|     12735674271 |   1994 | MFGR#2239 |
|     13204067775 |   1995 | MFGR#2239 |
|     12898303300 |   1996 | MFGR#2239 |
|     12924571665 |   1997 | MFGR#2239 |
|      7635306063 |   1998 | MFGR#2239 |
+-----------------+--------+-----------+
7 rows in set (1 min 15.92 sec)

Query OK, 0 rows affected (1 min 15.92 sec)

mysql> select sum(lo_revenue), d_year, p_brand from lineorder join dim_date   on lo_orderdatekey = d_datekey join part   on lo_partkey = p_partkey join supplier   on lo_suppkey = s_suppkey where  p_brand= 'MFGR#2239' and s_region = 'EUROPE' group by d_year, p_brand order by d_year, p_brand;
+-----------------+--------+-----------+
| sum(lo_revenue) | d_year | p_brand   |
+-----------------+--------+-----------+
|     12779281559 |   1992 | MFGR#2239 |
|     12983600841 |   1993 | MFGR#2239 |
|     12735674271 |   1994 | MFGR#2239 |
|     13204067775 |   1995 | MFGR#2239 |
|     12898303300 |   1996 | MFGR#2239 |
|     12924571665 |   1997 | MFGR#2239 |
|      7635306063 |   1998 | MFGR#2239 |
+-----------------+--------+-----------+
7 rows in set (1 min 31.99 sec)

Query OK, 0 rows affected (1 min 31.99 sec)

| INDIA     | INDIA     |   1997 | 109284243869 |
| CHINA     | INDIA     |   1997 | 109488575684 |
| JAPAN     | INDIA     |   1997 | 110097774851 |
| INDONESIA | INDIA     |   1997 | 110154459637 |
| INDONESIA | INDONESIA |   1997 | 110950681157 |
+-----------+-----------+--------+--------------+
150 rows in set (1 min 34.96 sec)

| UNITED ST1 | UNITED ST0 |   1997 | 1248471119 |
| UNITED ST8 | UNITED ST4 |   1997 | 1263036967 |
| UNITED ST1 | UNITED ST4 |   1997 | 1264216455 |
| UNITED ST1 | UNITED ST7 |   1997 | 1277871900 |
| UNITED ST0 | UNITED ST0 |   1997 | 1287377776 |
+------------+------------+--------+------------+
600 rows in set (1 min 32.51 sec)

Query OK, 0 rows affected (1 min 32.52 sec)

| UNITED KI5 | UNITED KI1 |   1996 | 1159731435 |
| UNITED KI5 | UNITED KI5 |   1997 | 1059730150 |
| UNITED KI1 | UNITED KI5 |   1997 | 1086879756 |
| UNITED KI5 | UNITED KI1 |   1997 | 1126275559 |
| UNITED KI1 | UNITED KI1 |   1997 | 1242674090 |
+------------+------------+--------+------------+
24 rows in set (1 min 4.94 sec)

+------------+------------+--------+-----------+
| c_city     | s_city     | d_year | revenue   |
+------------+------------+--------+-----------+
| UNITED KI1 | UNITED KI5 |   1997 |  68612586 |
| UNITED KI5 | UNITED KI5 |   1997 |  83890257 |
| UNITED KI5 | UNITED KI1 |   1997 |  89832476 |
| UNITED KI1 | UNITED KI1 |   1997 | 101889067 |
+------------+------------+--------+-----------+
4 rows in set (1 min 4.77 sec)

Query OK, 0 rows affected (1 min 4.77 sec)

mysql> select c_city, s_city, d_year, sum(lo_revenue) as revenue from customer join lineorder   on lo_custkey = c_customerkey join supplier   on lo_suppkey = s_suppkey join dim_date   on lo_orderdatekey = d_datekey where (c_city='UNITED KI1' or c_city='UNITED KI5') and (s_city='UNITED KI1' or s_city='UNITED KI5') and d_yearmonth = 'Dec1997' group by c_city, s_city, d_year order by d_year asc, revenue desc;
+------------+------------+--------+-----------+
| c_city     | s_city     | d_year | revenue   |
+------------+------------+--------+-----------+
| UNITED KI1 | UNITED KI5 |   1997 |  68612586 |
| UNITED KI5 | UNITED KI5 |   1997 |  83890257 |
| UNITED KI5 | UNITED KI1 |   1997 |  89832476 |
| UNITED KI1 | UNITED KI1 |   1997 | 101889067 |
+------------+------------+--------+-----------+
4 rows in set (1 min 3.47 sec)

Query OK, 0 rows affected (1 min 3.47 sec)

|   1998 | BRAZIL        | 120033046329 |
|   1998 | CANADA        | 119247087269 |
|   1998 | PERU          | 120157743905 |
|   1998 | UNITED STATES | 119847905007 |
+--------+---------------+--------------+
35 rows in set (2 min 35.30 sec)

Query OK, 0 rows affected (2 min 35.30 sec)

|   1998 | BRAZIL        | 120033046329 |
|   1998 | CANADA        | 119247087269 |
|   1998 | PERU          | 120157743905 |
|   1998 | UNITED STATES | 119847905007 |
+--------+---------------+--------------+
35 rows in set (2 min 38.87 sec)

Query OK, 0 rows affected (2 min 38.87 sec)

|   1998 | UNITED STATES | MFGR#14    | 12072671178 |
|   1998 | UNITED STATES | MFGR#15    | 11491754056 |
|   1998 | UNITED STATES | MFGR#21    | 12068284630 |
|   1998 | UNITED STATES | MFGR#22    | 11924814779 |
|   1998 | UNITED STATES | MFGR#23    | 11504788144 |
|   1998 | UNITED STATES | MFGR#24    | 11863719312 |
|   1998 | UNITED STATES | MFGR#25    | 12049700131 |
+--------+---------------+------------+-------------+
100 rows in set (1 min 11.90 sec)

Query OK, 0 rows affected (1 min 11.90 sec)
|   1998 | UNITED STATES | MFGR#21    | 12068284630 |
|   1998 | UNITED STATES | MFGR#22    | 11924814779 |
|   1998 | UNITED STATES | MFGR#23    | 11504788144 |
|   1998 | UNITED STATES | MFGR#24    | 11863719312 |
|   1998 | UNITED STATES | MFGR#25    | 12049700131 |
+--------+---------------+------------+-------------+
100 rows in set (1 min 8.20 sec)

Query OK, 0 rows affected (1 min 8.20 sec)

|   1998 | UNITED ST9 | MFGR#145  | 176757954 |
|   1998 | UNITED ST9 | MFGR#146  | 100090734 |
|   1998 | UNITED ST9 | MFGR#147  | 125169985 |
|   1998 | UNITED ST9 | MFGR#148  |  88953589 |
|   1998 | UNITED ST9 | MFGR#149  | 103827257 |
+--------+------------+-----------+-----------+
800 rows in set (6.24 sec)

gistfile3.txt

SF20 XFS second partition discarded
https://gist.github.com/greenlion/70e16e03736d51dee4ec9e0f76d3a1fb/edit

 MySQL  127.0.0.1:3306 ssl  ssb  SQL > select count(*) from lineorder;
+-----------+
| count(*)  |
+-----------+
| 119994608 |
+-----------+
1 row in set (38.4184 sec)

Query OK, 0 rows affected (38.4184 sec)
 MySQL  127.0.0.1:3306 ssl  ssb  SQL > select count(*) from lineorder;
+-----------+
| count(*)  |
+-----------+
| 119994608 |
+-----------+
1 row in set (38.3716 sec)

Query OK, 0 rows affected (38.3716 sec)

 MySQL  127.0.0.1:3306 ssl  ssb  SQL > select sum(lo_extendedprice*lo_discount) as revenue from lineorder join  dim_date on lo_orderdatekey = d_datekey where d_year = 1993 and lo_discount between 1 and 3 and lo_quantity < 25;
+---------------+
| revenue       |
+---------------+
| 8939702894840 |
+---------------+
1 row in set (8.5348 sec)

Query OK, 0 rows affected (8.5348 sec)
 MySQL  127.0.0.1:3306 ssl  ssb  SQL > select sum(lo_extendedprice*lo_discount) as revenue from lineorder join  dim_date on lo_orderdatekey = d_datekey where d_year = 1993 and lo_discount between 1 and 3 and lo_quantity < 25;
+----------------+
| revenue        |
+----------------+
| 11785446864168 |
+----------------+
1 row in set (3.2202 sec)

Query OK, 0 rows affected (3.2202 sec)

 MySQL  127.0.0.1:3306 ssl  ssb  SQL > select sum(lo_extendedprice*lo_discount) as revenue from lineorder join  dim_date on lo_orderdatekey = d_datekey where  d_yearmonthnum = 199401 and lo_discount between 4 and 6 and lo_quantity between 26 and 35;
+---------------+
| revenue       |
+---------------+
| 1926447117517 |
+---------------+
1 row in set (2.1641 sec)

Query OK, 0 rows affected (2.1641 sec)
 MySQL  127.0.0.1:3306 ssl  ssb  SQL > select sum(lo_extendedprice*lo_discount) as revenue from lineorder join  dim_date on lo_orderdatekey = d_datekey where  d_yearmonthnum = 199401 and lo_discount between 4 and 6 and lo_quantity between 26 and 35;
+---------------+
| revenue       |
+---------------+
| 1926447117517 |
+---------------+
1 row in set (2.2548 sec)

Query OK, 0 rows affected (2.2548 sec)

-- why is this query slower (investigate)
 MySQL  127.0.0.1:3306 ssl  ssb  SQL > select sum(lo_extendedprice*lo_discount) as revenue from lineorder join  dim_date on lo_orderdatekey = d_datekey where  d_weeknuminyear = 6 and d_year = 1994 and lo_discount between 5 and 7 and lo_quantity between 26 and 35;
+--------------+
| revenue      |
+--------------+
| 519638498830 |
+--------------+
1 row in set (28.6200 sec)

Query OK, 0 rows affected (28.6200 sec)
 MySQL  127.0.0.1:3306 ssl  ssb  SQL > select sum(lo_extendedprice*lo_discount) as revenue from lineorder join  dim_date on lo_orderdatekey = d_datekey where  d_weeknuminyear = 6 and d_year = 1994 and lo_discount between 5 and 7 and lo_quantity between 26 and 35;
+--------------+
| revenue      |
+--------------+
| 519638498830 |
+--------------+
1 row in set (37.0004 sec)

Query OK, 0 rows affected (37.0004 sec)

select sum(lo_revenue), d_year, p_brand
from lineorder
join dim_date
  on lo_orderdatekey = d_datekey
join part
  on lo_partkey = p_partkey
join supplier
  on lo_suppkey = s_suppkey
where 
p_category = 'MFGR#12'
and s_region = 'AMERICA'
group by d_year, p_brand
order by d_year, p_brand;
|      7538377911 |   1998 | MFGR#126  |
|      7811046637 |   1998 | MFGR#127  |
|      7790578551 |   1998 | MFGR#128  |
|      6961936311 |   1998 | MFGR#129  |
+-----------------+--------+-----------+
280 rows in set (2 min 35.2368 sec)

|      7559245564 |   1998 | MFGR#125  |
|      7538377911 |   1998 | MFGR#126  |
|      7811046637 |   1998 | MFGR#127  |
|      7790578551 |   1998 | MFGR#128  |
|      6961936311 |   1998 | MFGR#129  |
+-----------------+--------+-----------+
280 rows in set (2 min 38.0197 sec)

Query OK, 0 rows affected (2 min 38.0197 sec)

|      7811396637 |   1998 | MFGR#2225 |
|      7729246433 |   1998 | MFGR#2226 |
|      8013433999 |   1998 | MFGR#2227 |
|      8306416932 |   1998 | MFGR#2228 |
+-----------------+--------+-----------+
56 rows in set (1 min 41.3351 sec)

Query OK, 0 rows affected (1 min 41.3351 sec)

gistfile4.txt

SSB SF100 VMWARE 6 CORE 54GB RAM VIRTUALIZED HDD XFS 10M rows per partition
--Q1.1
mysql> select sum(lo_extendedprice*lo_discount) as revenue from lineorder join  dim_date on lo_orderdatekey = d_datekey where d_year = 1993 and lo_discount between 1 and 3 and lo_quantity < 25;
+----------------+
| revenue        |
+----------------+
| 44652567249651 |
+----------------+
1 row in set (5.82 sec)

--Q1.2
mysql> select sum(lo_extendedprice*lo_discount) as revenue from lineorder join  dim_date on lo_orderdatekey = d_datekey where  d_yearmonthnum = 199401 and lo_discount between 4 and 6 and lo_quantity between 26 and 35;
+---------------+
| revenue       |
+---------------+
| 9624332170119 |
+---------------+
1 row in set (2.15 sec)

Query OK, 0 rows affected (2.15 sec)

--Q1.3
mysql> select sum(lo_extendedprice*lo_discount) as revenue from lineorder join  dim_date on lo_orderdatekey = d_datekey where  d_weeknuminyear = 6 and d_year = 1994 and lo_discount between 5 and 7 and lo_quantity between 26 and 35;
+---------------+
| revenue       |
+---------------+
| 2604026374315 |
+---------------+
1 row in set (1.85 sec)


-- Q2.1
select sum(lo_revenue), d_year, p_brand from lineorder join dim_date   on lo_orderdatekey = d_datekey join part   on lo_partkey = p_partkey join supplier   on lo_suppkey = s_suppkey where  p_category = 'MFGR#12' and s_region = 'AMERICA' group by d_year, p_brand order by d_year, p_brand;
|     38248439933 |   1998 | MFGR#125  |
|     37043563027 |   1998 | MFGR#126  |
|     38499217759 |   1998 | MFGR#127  |
|     39679892915 |   1998 | MFGR#128  |
|     35300513083 |   1998 | MFGR#129  |
+-----------------+--------+-----------+
280 rows in set (5 min 29.67 sec)

Query OK, 0 rows affected (5 min 29.67 sec)

|     37043563027 |   1998 | MFGR#126  |
|     38499217759 |   1998 | MFGR#127  |
|     39679892915 |   1998 | MFGR#128  |
|     35300513083 |   1998 | MFGR#129  |
+-----------------+--------+-----------+
280 rows in set (18.54 sec)

Query OK, 0 rows affected (18.54 sec)

--Q2.2
select sum(lo_revenue), d_year, p_brand from lineorder join dim_date   on lo_orderdatekey = d_datekey join part   on lo_partkey = p_partkey join supplier   on lo_suppkey = s_suppkey where  p_brand between 'MFGR#2221' and 'MFGR#2228' and s_region = 'ASIA' group by d_year, p_brand order by d_year, p_brand;
|     38328423898 |   1998 | MFGR#2225 |
|     38705033272 |   1998 | MFGR#2226 |
|     39907545239 |   1998 | MFGR#2227 |
|     40654201840 |   1998 | MFGR#2228 |
+-----------------+--------+-----------+
56 rows in set (1 min 31.60 sec)

Query OK, 0 rows affected (1 min 31.60 sec)
|     38328423898 |   1998 | MFGR#2225 |
|     38705033272 |   1998 | MFGR#2226 |
|     39907545239 |   1998 | MFGR#2227 |
|     40654201840 |   1998 | MFGR#2228 |
+-----------------+--------+-----------+
56 rows in set (1 min 23.79 sec)

Query OK, 0 rows affected (1 min 23.79 sec)

--Q2.3
select sum(lo_revenue), d_year, p_brand from lineorder join dim_date   on lo_orderdatekey = d_datekey join part   on lo_partkey = p_partkey join supplier   on lo_suppkey = s_suppkey where  p_brand= 'MFGR#2239' and s_region = 'EUROPE' group by d_year, p_brand order by d_year, p_brand;
+-----------------+--------+-----------+
| sum(lo_revenue) | d_year | p_brand   |
+-----------------+--------+-----------+
|     65751589723 |   1992 | MFGR#2239 |
|     64532844801 |   1993 | MFGR#2239 |
|     64722599002 |   1994 | MFGR#2239 |
|     65616432683 |   1995 | MFGR#2239 |
|     64802884686 |   1996 | MFGR#2239 |
|     64485541165 |   1997 | MFGR#2239 |
|     37276536361 |   1998 | MFGR#2239 |
+-----------------+--------+-----------+
7 rows in set (11.95 sec)

Query OK, 0 rows affected (11.95 sec)

+-----------------+--------+-----------+
| sum(lo_revenue) | d_year | p_brand   |
+-----------------+--------+-----------+
|     65751589723 |   1992 | MFGR#2239 |
|     64532844801 |   1993 | MFGR#2239 |
|     64722599002 |   1994 | MFGR#2239 |
|     65616432683 |   1995 | MFGR#2239 |
|     64802884686 |   1996 | MFGR#2239 |
|     64485541165 |   1997 | MFGR#2239 |
|     37276536361 |   1998 | MFGR#2239 |
+-----------------+--------+-----------+
7 rows in set (11.65 sec)

Query OK, 0 rows affected (11.65 sec)

--Q3.1
select c_nation, s_nation, d_year, sum(lo_revenue) as revenue from customer join lineorder   on lo_custkey = c_customerkey join supplier   on lo_suppkey = s_suppkey join dim_date   on lo_orderdatekey = d_datekey where c_region = 'ASIA' and s_region = 'ASIA' and d_year >= 1992 and d_year <= 1997 group by c_nation, s_nation, d_year order by d_year asc, revenue desc;
| INDONESIA | JAPAN     |   1997 | 489220733770 |
| INDIA     | JAPAN     |   1997 | 490546446494 |
| INDONESIA | INDIA     |   1997 | 491353073578 |
| VIETNAM   | INDIA     |   1997 | 491592756006 |
| INDIA     | INDIA     |   1997 | 493676438324 |
+-----------+-----------+--------+--------------+
150 rows in set (2 min 52.71 sec)

Query OK, 0 rows affected (2 min 52.72 sec)

| INDIA     | JAPAN     |   1997 | 536191417932 |
| INDONESIA | INDIA     |   1997 | 538590860858 |
| VIETNAM   | INDIA     |   1997 | 538662748346 |
| INDIA     | INDIA     |   1997 | 539454308193 |
+-----------+-----------+--------+--------------+
150 rows in set (1 min 59.65 sec)

Query OK, 0 rows affected (1 min 59.65 sec)

--Q3.2
select c_city, s_city, d_year, sum(lo_revenue) as revenue from customer join lineorder   on lo_custkey = c_customerkey join supplier   on lo_suppkey = s_suppkey join dim_date   on lo_orderdatekey = d_datekey where c_nation = 'UNITED STATES' and s_nation = 'UNITED STATES' and d_year >= 1992 and d_year <= 1997 group by c_city, s_city, d_year order by d_year asc, revenue desc;
| UNITED ST6 | UNITED ST4 |   1997 | 5783920014 |
| UNITED ST4 | UNITED ST6 |   1997 | 5790922221 |
| UNITED ST3 | UNITED ST6 |   1997 | 5799554133 |
| UNITED ST6 | UNITED ST1 |   1997 | 5818428108 |
| UNITED ST5 | UNITED ST1 |   1997 | 5820845531 |
+------------+------------+--------+------------+
600 rows in set (19.96 sec)

Query OK, 0 rows affected (19.96 sec)

| UNITED ST6 | UNITED ST4 |   1997 | 5783920014 |
| UNITED ST4 | UNITED ST6 |   1997 | 5790922221 |
| UNITED ST3 | UNITED ST6 |   1997 | 5799554133 |
| UNITED ST6 | UNITED ST1 |   1997 | 5818428108 |
| UNITED ST5 | UNITED ST1 |   1997 | 5820845531 |
+------------+------------+--------+------------+
600 rows in set (18.71 sec)

Query OK, 0 rows affected (18.72 sec)

--Q3.3
select c_city, s_city, d_year, sum(lo_revenue)
as revenue
from customer
join lineorder
  on lo_custkey = c_customerkey
join supplier
  on lo_suppkey = s_suppkey
join dim_date
  on lo_orderdatekey = d_datekey
where
(c_city='UNITED KI1' or c_city='UNITED KI5')
and (s_city='UNITED KI1' or s_city='UNITED KI5')
and d_year >= 1992 and d_year <= 1997
group by c_city, s_city, d_year
order by d_year asc, revenue desc;
| UNITED KI1 | UNITED KI1 |   1996 | 5582184547 |
| UNITED KI5 | UNITED KI5 |   1997 | 5090286645 |
| UNITED KI1 | UNITED KI5 |   1997 | 5180772174 |
| UNITED KI1 | UNITED KI1 |   1997 | 5369255242 |
| UNITED KI5 | UNITED KI1 |   1997 | 5433144227 |
+------------+------------+--------+------------+
24 rows in set (10.58 sec)

Query OK, 0 rows affected (10.58 sec)
| UNITED KI1 | UNITED KI1 |   1996 | 5582184547 |
| UNITED KI5 | UNITED KI5 |   1997 | 5090286645 |
| UNITED KI1 | UNITED KI5 |   1997 | 5180772174 |
| UNITED KI1 | UNITED KI1 |   1997 | 5369255242 |
| UNITED KI5 | UNITED KI1 |   1997 | 5433144227 |
+------------+------------+--------+------------+
24 rows in set (10.67 sec)

Query OK, 0 rows affected (10.67 sec)

--Q3.4
select c_city, s_city, d_year, sum(lo_revenue)
as revenue
from customer
join lineorder
  on lo_custkey = c_customerkey
join supplier
  on lo_suppkey = s_suppkey
join dim_date
  on lo_orderdatekey = d_datekey
where
(c_city='UNITED KI1' or c_city='UNITED KI5')
and (s_city='UNITED KI1' or s_city='UNITED KI5')
and d_yearmonth = 'Dec1997'
group by c_city, s_city, d_year
order by d_year asc, revenue desc;
+------------+------------+--------+-----------+
| c_city     | s_city     | d_year | revenue   |
+------------+------------+--------+-----------+
| UNITED KI5 | UNITED KI5 |   1997 | 382582307 |
| UNITED KI1 | UNITED KI1 |   1997 | 390210301 |
| UNITED KI1 | UNITED KI5 |   1997 | 479937243 |
| UNITED KI5 | UNITED KI1 |   1997 | 517847873 |
+------------+------------+--------+-----------+
4 rows in set (2.42 sec)

Query OK, 0 rows affected (2.42 sec)
+------------+------------+--------+-----------+
| c_city     | s_city     | d_year | revenue   |
+------------+------------+--------+-----------+
| UNITED KI5 | UNITED KI5 |   1997 | 382582307 |
| UNITED KI1 | UNITED KI1 |   1997 | 390210301 |
| UNITED KI1 | UNITED KI5 |   1997 | 479937243 |
| UNITED KI5 | UNITED KI1 |   1997 | 522356894 |
+------------+------------+--------+-----------+
4 rows in set (2.29 sec)

Query OK, 0 rows affected (2.29 sec)

--Q4.1
select d_year, c_nation,
sum(lo_revenue - lo_supplycost) as profit
from lineorder
join dim_date 
  on lo_orderdatekey = d_datekey
join customer
  on lo_custkey = c_customerkey
join supplier
  on lo_suppkey = s_suppkey
join part
  on lo_partkey = p_partkey
where
c_region = 'AMERICA'
and s_region = 'AMERICA'
and (p_mfgr = 'MFGR#1'
or p_mfgr = 'MFGR#2')
group by d_year, c_nation
order by d_year, c_nation;
|   1998 | ARGENTINA     |  601137547318 |
|   1998 | BRAZIL        |  596982761801 |
|   1998 | CANADA        |  597455978868 |
|   1998 | PERU          |  597738365230 |
|   1998 | UNITED STATES |  598834706777 |
+--------+---------------+---------------+
35 rows in set (2 min 58.53 sec)

Query OK, 0 rows affected (2 min 58.53 sec)

|   1998 | BRAZIL        |  603410055625 |
|   1998 | CANADA        |  604141593637 |
|   1998 | PERU          |  604353719791 |
|   1998 | UNITED STATES |  605251674591 |
+--------+---------------+---------------+
35 rows in set (3 min 2.02 sec)

Query OK, 0 rows affected (3 min 2.02 sec)

--Q4.2
select d_year, s_nation, p_category,
sum(lo_revenue - lo_supplycost) as profit
from lineorder
join dim_date 
  on lo_orderdatekey = d_datekey
join customer
  on lo_custkey = c_customerkey
join supplier
  on lo_suppkey = s_suppkey
join part
  on lo_partkey = p_partkey
where
c_region = 'AMERICA'
and s_region = 'AMERICA'
and (d_year = 1997 or d_year = 1998)
and (p_mfgr = 'MFGR#1'
or p_mfgr = 'MFGR#2')
group by d_year, s_nation, p_category
order by d_year, s_nation, p_category;
|   1998 | UNITED STATES | MFGR#21    |  60501774545 |
|   1998 | UNITED STATES | MFGR#22    |  60222212055 |
|   1998 | UNITED STATES | MFGR#23    |  62101067823 |
|   1998 | UNITED STATES | MFGR#24    |  60365874394 |
|   1998 | UNITED STATES | MFGR#25    |  60132603874 |
+--------+---------------+------------+--------------+
100 rows in set (2 min 42.36 sec)

Query OK, 0 rows affected (2 min 42.36 sec)

|   1998 | UNITED STATES | MFGR#21    |  60501774545 |
|   1998 | UNITED STATES | MFGR#22    |  60222212055 |
|   1998 | UNITED STATES | MFGR#23    |  62101067823 |
|   1998 | UNITED STATES | MFGR#24    |  60365874394 |
|   1998 | UNITED STATES | MFGR#25    |  60132603874 |
+--------+---------------+------------+--------------+
100 rows in set (2 min 43.74 sec)

Query OK, 0 rows affected (2 min 43.74 sec)

-Q4.3
select d_year, s_city, p_brand, sum(lo_revenue - lo_supplycost) as profit from lineorder join dim_date    on lo_orderdatekey = d_datekey join customer   on lo_custkey = c_customerkey join supplier   on lo_suppkey = s_suppkey join part   on lo_partkey = p_partkey where s_nation = 'UNITED STATES' and (d_year = 1997 or d_year = 1998) and p_category = 'MFGR#14' group by d_year, s_city, p_brand order by d_year, s_city, p_brand;
|   1998 | UNITED ST9 | MFGR#145  |  671271586 |
|   1998 | UNITED ST9 | MFGR#146  |  770184207 |
|   1998 | UNITED ST9 | MFGR#147  |  640908455 |
|   1998 | UNITED ST9 | MFGR#148  |  811919859 |
|   1998 | UNITED ST9 | MFGR#149  |  603099066 |
+--------+------------+-----------+------------+
800 rows in set (41.44 sec)


Query OK, 0 rows affected (41.44 sec)
|   1998 | UNITED ST9 | MFGR#145  |  671271586 |
|   1998 | UNITED ST9 | MFGR#146  |  770184207 |
|   1998 | UNITED ST9 | MFGR#147  |  640908455 |
|   1998 | UNITED ST9 | MFGR#148  |  811919859 |
|   1998 | UNITED ST9 | MFGR#149  |  603099066 |
+--------+------------+-----------+------------+
800 rows in set (40.36 sec)

gistfile5.txt

SSB SF100 VMWARE 6 CORE 54GB RAM VIRTUALIZED HDD XFS COLUMNSTORE - LOAD
MariaDB [ssb]> load data local infile '/home/justin/warp/storage/warp/benchmark/ssb-dbgen/lineorder.tbl.1' into table lineorder fields terminated by ',' optionally enclosed by '"';
Query OK, 49987886 rows affected (2 min 48.267 sec)  
Records: 49987886  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [ssb]> load data local infile '/home/justin/warp/storage/warp/benchmark/ssb-dbgen/lineorder.tbl.2' into table lineorder fields terminated by ',' optionally enclosed by '"';
Query OK, 50001130 rows affected (2 min 46.365 sec)  
Records: 50001130  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [ssb]> load data local infile '/home/justin/warp/storage/warp/benchmark/ssb-dbgen/lineorder.tbl.3' into table lineorder fields terminated by ',' optionally enclosed by '"';
Query OK, 50007339 rows affected (2 min 57.335 sec)  
Records: 50007339  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [ssb]> load data local infile '/home/justin/warp/storage/warp/benchmark/ssb-dbgen/lineorder.tbl.4' into table lineorder fields terminated by ',' optionally enclosed by '"';
Query OK, 50003909 rows affected (2 min 53.346 sec)  
Records: 50003909  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [ssb]> load data local infile '/home/justin/warp/storage/warp/benchmark/ssb-dbgen/lineorder.tbl.5' into table lineorder fields terminated by ',' optionally enclosed by '"';
Query OK, 50009819 rows affected (2 min 59.372 sec)  
Records: 50009819  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [ssb]> load data local infile '/home/justin/warp/storage/warp/benchmark/ssb-dbgen/lineorder.tbl.6' into table lineorder fields terminated by ',' optionally enclosed by '"';
Query OK, 49995728 rows affected (2 min 51.332 sec)  
Records: 49995728  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [ssb]> load data local infile '/home/justin/warp/storage/warp/benchmark/ssb-dbgen/lineorder.tbl.7' into table lineorder fields terminated by ',' optionally enclosed by '"';
Query OK, 50002899 rows affected (2 min 48.356 sec)  
Records: 50002899  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [ssb]> load data local infile '/home/justin/warp/storage/warp/benchmark/ssb-dbgen/lineorder.tbl.8' into table lineorder fields terminated by ',' optionally enclosed by '"';
Query OK, 50010814 rows affected (3 min 7.353 sec)   
Records: 50010814  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [ssb]> load data local infile '/home/justin/warp/storage/warp/benchmark/ssb-dbgen/lineorder.tbl.9' into table lineorder fields terminated by ',' optionally enclosed by '"';
Query OK, 50000177 rows affected (2 min 44.315 sec)  
Records: 50000177  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [ssb]> load data local infile '/home/justin/warp/storage/warp/benchmark/ssb-dbgen/lineorder.tbl.10' into table lineorder fields terminated by ',' optionally enclosed by '"';
Query OK, 50007361 rows affected (2 min 49.318 sec)  
Records: 50007361  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [ssb]> load data local infile '/home/justin/warp/storage/warp/benchmark/ssb-dbgen/lineorder.tbl.11' into table lineorder fields terminated by ',' optionally enclosed by '"';
Query OK, 50003145 rows affected (2 min 47.344 sec)  
Records: 50003145  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [ssb]> load data local infile '/home/justin/warp/storage/warp/benchmark/ssb-dbgen/lineorder.tbl.12' into table lineorder fields terminated by ',' optionally enclosed by '"';
Query OK, 50007695 rows affected (2 min 43.334 sec)  
Records: 50007695  Deleted: 0  Skipped: 0  Warnings: 0
MariaDB [ssb]> load data local infile '/home/justin/warp/storage/warp/benchmark/ssb-dbgen/customer.tbl' into table customer fields terminated by ',' optionally enclosed by '"';
Query OK, 3000000 rows affected (10.272 sec)         
Records: 3000000  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [ssb]> load data local infile '/home/justin/warp/storage/warp/benchmark/ssb-dbgen/part.tbl' into table part fields terminated by ',' optionally enclosed by '"';
Query OK, 1400000 rows affected (6.219 sec)          
Records: 1400000  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [ssb]> load data local infile '/home/justin/warp/storage/warp/benchmark/ssb-dbgen/supplier.tbl' into table supplier fields terminated by ',' optionally enclosed by '"';
Query OK, 200000 rows affected (1.215 sec)           
Records: 200000  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [ssb]> load data local infile '/home/justin/warp/storage/warp/benchmark/ssb-dbgen/date.tbl' into table dim_date fields terminated by ',' optionally enclosed by '"';
Query OK, 2556 rows affected (1.192 sec)             
Records: 2556  Deleted: 0  Skipped: 0  Warnings: 0

gistfile6.txt

--Q1.1
MariaDB [ssb]> select sum(lo_extendedprice*lo_discount) as revenue from lineorder join  dim_date on lo_orderdatekey = d_datekey where d_year = 1993 and lo_discount between 1 and 3 and lo_quantity < 25;
+----------------+
| revenue        |
+----------------+
| 44652567249651 |
+----------------+
1 row in set (39.708 sec)

MariaDB [ssb]> select sum(lo_extendedprice*lo_discount) as revenue from lineorder join  dim_date on lo_orderdatekey = d_datekey where d_year = 1993 and lo_discount between 1 and 3 and lo_quantity < 25;
+----------------+
| revenue        |
+----------------+
| 44652567249651 |
+----------------+
1 row in set (6.743 sec)

-- Q1.2
MariaDB [ssb]> select sum(lo_extendedprice*lo_discount) as revenue from lineorder join  dim_date on lo_orderdatekey = d_datekey where  d_yearmonthnum = 199401 and lo_discount between 4 and 6 and lo_quantity between 26 and 35;
+---------------+
| revenue       |
+---------------+
| 9624332170119 |
+---------------+
1 row in set (5.803 sec)

MariaDB [ssb]> select sum(lo_extendedprice*lo_discount) as revenue from lineorder join  dim_date on lo_orderdatekey = d_datekey where  d_yearmonthnum = 199401 and lo_discount between 4 and 6 and lo_quantity between 26 and 35;
+---------------+
| revenue       |
+---------------+
| 9624332170119 |
+---------------+
1 row in set (5.900 sec)

--Q1.3
MariaDB [ssb]>  select sum(lo_extendedprice*lo_discount) as revenue from lineorder join  dim_date on lo_orderdatekey = d_datekey where  d_weeknuminyear = 6 and d_year = 1994 and lo_discount between 5 and 7 and lo_quantity between 26 and 35;
+---------------+
| revenue       |
+---------------+
| 2604026374315 |
+---------------+
1 row in set (5.710 sec)

MariaDB [ssb]>  select sum(lo_extendedprice*lo_discount) as revenue from lineorder join  dim_date on lo_orderdatekey = d_datekey where  d_weeknuminyear = 6 and d_year = 1994 and lo_discount between 5 and 7 and lo_quantity between 26 and 35;
+---------------+
| revenue       |
+---------------+
| 2604026374315 |
+---------------+
1 row in set (5.946 sec)

--Q2.1
select sum(lo_revenue), d_year, p_brand from lineorder join dim_date   on lo_orderdatekey = d_datekey join part   on lo_partkey = p_partkey join supplier   on lo_suppkey = s_suppkey where  p_category = 'MFGR#12' and s_region = 'AMERICA' group by d_year, p_brand order by d_year, p_brand;
|     38248439933 |   1998 | MFGR#125  |
|     37043563027 |   1998 | MFGR#126  |
|     38499217759 |   1998 | MFGR#127  |
|     39679892915 |   1998 | MFGR#128  |
|     35300513083 |   1998 | MFGR#129  |
+-----------------+--------+-----------+
280 rows in set (25.323 sec)
|     38248439933 |   1998 | MFGR#125  |
|     37043563027 |   1998 | MFGR#126  |
|     38499217759 |   1998 | MFGR#127  |
|     39679892915 |   1998 | MFGR#128  |
|     35300513083 |   1998 | MFGR#129  |
+-----------------+--------+-----------+
280 rows in set (18.887 sec)

--Q2.2
select sum(lo_revenue), d_year, p_brand from lineorder join dim_date   on lo_orderdatekey = d_datekey join part   on lo_partkey = p_partkey join supplier   on lo_suppkey = s_suppkey where  p_brand between 'MFGR#2221' and 'MFGR#2228' and s_region = 'ASIA' group by d_year, p_brand order by d_year, p_brand;
|     38020572188 |   1998 | MFGR#2224 |
|     38328423898 |   1998 | MFGR#2225 |
|     38705033272 |   1998 | MFGR#2226 |
|     39907545239 |   1998 | MFGR#2227 |
|     40654201840 |   1998 | MFGR#2228 |
+-----------------+--------+-----------+
56 rows in set (15.397 sec)
|     38020572188 |   1998 | MFGR#2224 |
|     38328423898 |   1998 | MFGR#2225 |
|     38705033272 |   1998 | MFGR#2226 |
|     39907545239 |   1998 | MFGR#2227 |
|     40654201840 |   1998 | MFGR#2228 |
+-----------------+--------+-----------+
56 rows in set (15.325 sec)

--Q2.3
select sum(lo_revenue), d_year, p_brand from lineorder join dim_date   on lo_orderdatekey = d_datekey join part   on lo_partkey = p_partkey join supplier   on lo_suppkey = s_suppkey where  p_brand= 'MFGR#2239' and s_region = 'EUROPE' group by d_year, p_brand order by d_year, p_brand;
+-----------------+--------+-----------+
| sum(lo_revenue) | d_year | p_brand   |
+-----------------+--------+-----------+
|     65751589723 |   1992 | MFGR#2239 |
|     64532844801 |   1993 | MFGR#2239 |
|     64722599002 |   1994 | MFGR#2239 |
|     65616432683 |   1995 | MFGR#2239 |
|     64802884686 |   1996 | MFGR#2239 |
|     64485541165 |   1997 | MFGR#2239 |
|     37276536361 |   1998 | MFGR#2239 |
+-----------------+--------+-----------+
7 rows in set (10.044 sec)
+-----------------+--------+-----------+
| sum(lo_revenue) | d_year | p_brand   |
+-----------------+--------+-----------+
|     65751589723 |   1992 | MFGR#2239 |
|     64532844801 |   1993 | MFGR#2239 |
|     64722599002 |   1994 | MFGR#2239 |
|     65616432683 |   1995 | MFGR#2239 |
|     64802884686 |   1996 | MFGR#2239 |
|     64485541165 |   1997 | MFGR#2239 |
|     37276536361 |   1998 | MFGR#2239 |
+-----------------+--------+-----------+
7 rows in set (9.877 sec)

-- Q3.1
select c_nation, s_nation, d_year, sum(lo_revenue) as revenue from customer join lineorder   on lo_custkey = c_customerkey join supplier   on lo_suppkey = s_suppkey join dim_date   on lo_orderdatekey = d_datekey where c_region = 'ASIA' and s_region = 'ASIA' and d_year >= 1992 and d_year <= 1997 group by c_nation, s_nation, d_year order by d_year asc, revenue desc;
| JAPAN     | INDONESIA |   1997 | 528373398958 |
| CHINA     | CHINA     |   1997 | 528062067737 |
| JAPAN     | VIETNAM   |   1997 | 527438042699 |
| CHINA     | VIETNAM   |   1997 | 527389697124 |
| JAPAN     | CHINA     |   1997 | 524733209087 |
+-----------+-----------+--------+--------------+
150 rows in set (25.002 sec)
| JAPAN     | INDONESIA |   1997 | 528373398958 |
| CHINA     | CHINA     |   1997 | 528062067737 |
| JAPAN     | VIETNAM   |   1997 | 527438042699 |
| CHINA     | VIETNAM   |   1997 | 527389697124 |
| JAPAN     | CHINA     |   1997 | 524733209087 |
+-----------+-----------+--------+--------------+
150 rows in set (23.105 sec)

--Q3.2
select c_city, s_city, d_year, sum(lo_revenue) as revenue from customer join lineorder   on lo_custkey = c_customerkey join supplier   on lo_suppkey = s_suppkey join dim_date   on lo_orderdatekey = d_datekey where c_nation = 'UNITED STATES' and s_nation = 'UNITED STATES' and d_year >= 1992 and d_year <= 1997 group by c_city, s_city, d_year order by d_year asc, revenue desc;
| UNITED ST9 | UNITED ST5 |   1997 | 4900232243 |
| UNITED ST2 | UNITED ST2 |   1997 | 4899683887 |
| UNITED ST5 | UNITED ST2 |   1997 | 4850999922 |
| UNITED ST9 | UNITED ST9 |   1997 | 4825393800 |
| UNITED ST9 | UNITED ST2 |   1997 | 4801196288 |
+------------+------------+--------+------------+
600 rows in set (15.624 sec)
| UNITED ST9 | UNITED ST5 |   1997 | 4900232243 |
| UNITED ST2 | UNITED ST2 |   1997 | 4899683887 |
| UNITED ST5 | UNITED ST2 |   1997 | 4850999922 |
| UNITED ST9 | UNITED ST9 |   1997 | 4825393800 |
| UNITED ST9 | UNITED ST2 |   1997 | 4801196288 |
+------------+------------+--------+------------+
600 rows in set (15.771 sec)

--Q3.3
select c_city, s_city, d_year, sum(lo_revenue)
as revenue
from customer
join lineorder
  on lo_custkey = c_customerkey
join supplier
  on lo_suppkey = s_suppkey
join dim_date
  on lo_orderdatekey = d_datekey
where
(c_city='UNITED KI1' or c_city='UNITED KI5')
and (s_city='UNITED KI1' or s_city='UNITED KI5')
and d_year >= 1992 and d_year <= 1997
group by c_city, s_city, d_year
order by d_year asc, revenue desc;
| UNITED KI5 | UNITED KI5 |   1996 | 5202073275 |
| UNITED KI5 | UNITED KI1 |   1997 | 5433144227 |
| UNITED KI1 | UNITED KI1 |   1997 | 5369255242 |
| UNITED KI1 | UNITED KI5 |   1997 | 5180772174 |
| UNITED KI5 | UNITED KI5 |   1997 | 5090286645 |
+------------+------------+--------+------------+
24 rows in set (9.933 sec)
| UNITED KI5 | UNITED KI5 |   1996 | 5202073275 |
| UNITED KI5 | UNITED KI1 |   1997 | 5433144227 |
| UNITED KI1 | UNITED KI1 |   1997 | 5369255242 |
| UNITED KI1 | UNITED KI5 |   1997 | 5180772174 |
| UNITED KI5 | UNITED KI5 |   1997 | 5090286645 |
+------------+------------+--------+------------+
24 rows in set (10.267 sec)

--Q3.4
select c_city, s_city, d_year, sum(lo_revenue)
as revenue
from customer
join lineorder
  on lo_custkey = c_customerkey
join supplier
  on lo_suppkey = s_suppkey
join dim_date
  on lo_orderdatekey = d_datekey
where
(c_city='UNITED KI1' or c_city='UNITED KI5')
and (s_city='UNITED KI1' or s_city='UNITED KI5')
and d_yearmonth = 'Dec1997'
group by c_city, s_city, d_year
order by d_year asc, revenue desc;
+------------+------------+--------+-----------+
| c_city     | s_city     | d_year | revenue   |
+------------+------------+--------+-----------+
| UNITED KI5 | UNITED KI1 |   1997 | 522356894 |
| UNITED KI1 | UNITED KI5 |   1997 | 479937243 |
| UNITED KI1 | UNITED KI1 |   1997 | 390210301 |
| UNITED KI5 | UNITED KI5 |   1997 | 382582307 |
+------------+------------+--------+-----------+
4 rows in set (7.933 sec)

+------------+------------+--------+-----------+
| c_city     | s_city     | d_year | revenue   |
+------------+------------+--------+-----------+
| UNITED KI5 | UNITED KI1 |   1997 | 522356894 |
| UNITED KI1 | UNITED KI5 |   1997 | 479937243 |
| UNITED KI1 | UNITED KI1 |   1997 | 390210301 |
| UNITED KI5 | UNITED KI5 |   1997 | 382582307 |
+------------+------------+--------+-----------+
4 rows in set (8.003 sec)

--Q4.1
select d_year, c_nation,
sum(lo_revenue - lo_supplycost) as profit
from lineorder
join dim_date 
  on lo_orderdatekey = d_datekey
join customer
  on lo_custkey = c_customerkey
join supplier
  on lo_suppkey = s_suppkey
join part
  on lo_partkey = p_partkey
where
c_region = 'AMERICA'
and s_region = 'AMERICA'
and (p_mfgr = 'MFGR#1'
or p_mfgr = 'MFGR#2')
group by d_year, c_nation
order by d_year, c_nation;
|   1998 | ARGENTINA     |  607314434298 |
|   1998 | BRAZIL        |  603410055625 |
|   1998 | CANADA        |  604141593637 |
|   1998 | PERU          |  604353719791 |
|   1998 | UNITED STATES |  605251674591 |
+--------+---------------+---------------+
35 rows in set (28.945 sec)
|   1998 | ARGENTINA     |  607314434298 |
|   1998 | BRAZIL        |  603410055625 |
|   1998 | CANADA        |  604141593637 |
|   1998 | PERU          |  604353719791 |
|   1998 | UNITED STATES |  605251674591 |
+--------+---------------+---------------+
35 rows in set (26.328 sec)

select d_year, s_nation, p_category,
sum(lo_revenue - lo_supplycost) as profit
from lineorder
join dim_date 
  on lo_orderdatekey = d_datekey
join customer
  on lo_custkey = c_customerkey
join supplier
  on lo_suppkey = s_suppkey
join part
  on lo_partkey = p_partkey
where
c_region = 'AMERICA'
and s_region = 'AMERICA'
and (d_year = 1997 or d_year = 1998)
and (p_mfgr = 'MFGR#1'
or p_mfgr = 'MFGR#2')
group by d_year, s_nation, p_category
order by d_year, s_nation, p_category;
|   1998 | UNITED STATES | MFGR#21    |  60501774545 |
|   1998 | UNITED STATES | MFGR#22    |  60222212055 |
|   1998 | UNITED STATES | MFGR#23    |  62101067823 |
|   1998 | UNITED STATES | MFGR#24    |  60365874394 |
|   1998 | UNITED STATES | MFGR#25    |  60132603874 |
+--------+---------------+------------+--------------+
100 rows in set (14.452 sec)

|   1998 | UNITED STATES | MFGR#21    |  60501774545 |
|   1998 | UNITED STATES | MFGR#22    |  60222212055 |
|   1998 | UNITED STATES | MFGR#23    |  62101067823 |
|   1998 | UNITED STATES | MFGR#24    |  60365874394 |
|   1998 | UNITED STATES | MFGR#25    |  60132603874 |
+--------+---------------+------------+--------------+
100 rows in set (15.238 sec)

select d_year, s_city, p_brand, sum(lo_revenue - lo_supplycost) as profit from lineorder join dim_date    on lo_orderdatekey = d_datekey join customer   on lo_custkey = c_customerkey join supplier   on lo_suppkey = s_suppkey join part   on lo_partkey = p_partkey where s_nation = 'UNITED STATES' and (d_year = 1997 or d_year = 1998) and p_category = 'MFGR#14' group by d_year, s_city, p_brand order by d_year, s_city, p_brand;
|   1998 | UNITED ST9 | MFGR#1440 |  655714398 |
|   1998 | UNITED ST9 | MFGR#145  |  671271586 |
|   1998 | UNITED ST9 | MFGR#146  |  770184207 |
|   1998 | UNITED ST9 | MFGR#147  |  640908455 |
|   1998 | UNITED ST9 | MFGR#148  |  811919859 |
|   1998 | UNITED ST9 | MFGR#149  |  603099066 |
+--------+------------+-----------+------------+
800 rows in set (12.027 sec)

|   1998 | UNITED ST9 | MFGR#145  |  671271586 |
|   1998 | UNITED ST9 | MFGR#146  |  770184207 |
|   1998 | UNITED ST9 | MFGR#147  |  640908455 |
|   1998 | UNITED ST9 | MFGR#148  |  811919859 |
|   1998 | UNITED ST9 | MFGR#149  |  603099066 |
+--------+------------+-----------+------------+
800 rows in set (12.156 sec)

gistfile7.txt

REDSHIFT SSB SF100 dc2.large
Q1.1 - 7.7s
Q1.2 - 10.58s
Q1.3 - 9.6s
Q2.1 - 15.9s
Q2.2 - 47s
Q2.3 - 45.5s
Q3.1 - 1min 7s
Q3.2 - 16s
Q3.3 - 38s
Q3.4 - 36.7s
Q4.1 - 1min 37s
Q4.2 - 1min 6.887s
Q4.3 - 46.5s

WARP SSB SF100 t3.large
Q1.1 - 8min
Q1.2 - 6min 14s
Q1.3 - 6min 37s
Q2.1 - 6min 9s
Q2.2 - 8min 45s
Q2.3 - 4min 15s
Q3.1 - 9min 41s
Q3.2 - 5min 45s
Q3.3 - 3min 39s
Q3.4 - 1min 13s
Q4.1 - 10min 14s
Q4.2 - 8min 53s
Q4.3 - 7min 3s

Notes:
I think the redshift node has local SSD.  It also has compression.  Those two things combined give it about a 10x boost over WARP on most of the queries.