SSB测试.md · BoomLee/bigdata-notes

# SSB 测试

Star schema benchmark（以下简称SSB）是学术界和工业界广泛使用的一个星型模型测试集（来源[论文](https://www.cs.umb.edu/~xuedchen/research/publications/StarSchemaB.PDF)），通过这个测试集合可以方便的对比各种OLAP产品的基础性能指标。Clickhouse 通过改写SSB，将星型模型打平转化成宽表，改造成了一个单表测试benchmark（参考[链接](https://clickhouse.tech/docs/en/getting-started/example-datasets/star-schema/)）。

clickhouse ssb

1.下载编译 dbgen

```
yum -y install gcc gcc-c++ make cmake git 
git clone https://github.com/vadimtk/ssb-dbgen.git
make
```

2.生产数据

```
./dbgen -s 1 -T c
./dbgen -s 1 -T l
./dbgen -s 1 -T p
./dbgen -s 1 -T s
./dbgen -s 1 -T d
[root@zijie ssb-dbgen]# wc -l *tbl
    30000 customer.tbl
     2556 date.tbl
  6001215 lineorder.tbl
   200000 part.tbl
     2000 supplier.tbl
  6235771 total
```

```
上面的表数据可以用如下的命令一次性生成：(for all SSBM tables)
dbgen -s 1 -T a
```

DorisDB ssb

```
wget http://dorisdb-public.oss-cn-zhangjiakou.aliyuncs.com/ssb-poc-0.9.1.zip
unzip ssb-poc-0.9.1.zip
cd ssb-poc
make && make install 
```

进入output目录，生成数据

```
cd output
bin/gen-ssb.sh 100 data_dir
```

3.创建测试表

hive

```
CREATE TABLE customer
(			
        C_CUSTKEY       String,
        C_NAME          String,
        C_ADDRESS       String,
        C_CITY          String,
        C_NATION        String,
        C_REGION        String,
        C_PHONE         String,
        C_MKTSEGMENT    String
)row format delimited
fields terminated by ','
```

```
CREATE TABLE lineorder
(
    LO_ORDERKEY             String,
    LO_LINENUMBER           String,
    LO_CUSTKEY              String,
    LO_PARTKEY              String,
    LO_SUPPKEY              String,
    LO_ORDERDATE            String,
    LO_ORDERPRIORITY        String,
    LO_SHIPPRIORITY         String,
    LO_QUANTITY             String,
    LO_EXTENDEDPRICE        String,
    LO_ORDTOTALPRICE        String,
    LO_DISCOUNT             String,
    LO_REVENUE              String,
    LO_SUPPLYCOST           String,
    LO_TAX                  String,
    LO_COMMITDATE           String,
    LO_SHIPMODE             String
)row format delimited
fields terminated by ','
```

```
CREATE TABLE part
(
        P_PARTKEY       String,
        P_NAME          String,
        P_MFGR          String,
        P_CATEGORY      String,
        P_BRAND         String,
        P_COLOR         String,
        P_TYPE          String,
        P_SIZE          String,
        P_CONTAINER     String
)row format delimited
fields terminated by ','
```

```
CREATE TABLE supplier
(
        S_SUPPKEY       String,
        S_NAME          String,
        S_ADDRESS       String,
        S_CITY          String,
        S_NATION        String,
        S_REGION        String,
        S_PHONE         String
)row format delimited
fields terminated by ','
```

```
create table ssb.lineorder_flat (

lo_orderkey String,
    lo_linenumber String,
    lo_custkey String,
    lo_partkey String,
    lo_suppkey String,
    lo_orderdate String,
    lo_orderpriority String,
    lo_shippriority String,
    lo_quantity String,
    lo_extendedprice String,
    lo_ordtotalprice String,
    lo_discount String,
    lo_revenue String,
    lo_supplycost String,
    lo_tax String,
    lo_commitdate String,
    lo_shipmode String,
    c_name String,
    c_address String,
    c_city String,
    c_nation String,
    c_region String,
    c_phone String,
    c_mktsegment String,
    s_name String,
    s_address String,
    s_city String,
    s_nation String,
    s_region String,
    s_phone String,
    p_name String,
    p_mfgr String,
    p_category String,
    p_brand String,
    p_color String,
    p_type String,
    p_size String,
    p_container String
) stored as orc
```

```
CREATE TABLE IF NOT EXISTS dates (
  d_datekey String COMMENT "",
  d_date String  COMMENT "",
  d_dayofweek String COMMENT "",
  d_month String COMMENT "",
  d_year String COMMENT "",
  d_yearmonthnum String COMMENT "",
  d_yearmonth String COMMENT "",
  d_daynuminweek String COMMENT "",
  d_daynuminmonth String COMMENT "",
  d_daynuminyear String COMMENT "",
  d_monthnuminyear String COMMENT "",
  d_weeknuminyear String COMMENT "",
  d_sellingseason String COMMENT "",
  d_lastdayinweekfl String COMMENT "",
  d_lastdayinmonthfl String COMMENT "",
  d_holidayfl String COMMENT "",
  d_weekdayfl String COMMENT ""
)row format delimited
fields terminated by ','
```

将数据插入大宽表

```
INSERT INTO lineorder_flat SELECT
    l.lo_orderkey as lo_orderkey,
    l.lo_linenumber as lo_linenumber,
    l.lo_custkey as lo_custkey,
    l.lo_partkey as lo_partkey,
    l.lo_suppkey as lo_suppkey,
    l.lo_orderdate as lo_orderdate,
    l.lo_orderpriority as lo_orderpriority,
    l.lo_shippriority as lo_shippriority,
    l.lo_quantity as lo_quantity,
    l.lo_extendedprice as lo_extendedprice,
    l.lo_ordtotalprice as lo_ordtotalprice,
    l.lo_discount as lo_discount,
    l.lo_revenue as lo_revenue,
    l.lo_supplycost as lo_supplycost,
    l.lo_tax as lo_tax,
    l.lo_commitdate as lo_commitdate,
    l.lo_shipmode as lo_shipmode,
    c.c_name as c_name,
    c.c_address as c_address,
    c.c_city as c_city,
    c.c_nation as c_nation,
    c.c_region as c_region,
    c.c_phone as c_phone,
    c.c_mktsegment as c_mktsegment,
    s.s_name as s_name,
    s.s_address as s_address,
    s.s_city as s_city,
    s.s_nation as s_nation,
    s.s_region as s_region,
    s.s_phone as s_phone,
    p.p_name as p_name,
    p.p_mfgr as p_mfgr,
    p.p_category as p_category,
    p.p_brand as p_brand,
    p.p_color as p_color,
    p.p_type as p_type,
    p.p_size as p_size,
    p.p_container as p_container
FROM
	lineorder  as l
	INNER JOIN customer as c ON ( c.c_custkey = l.lo_custkey )
	INNER JOIN supplier as  s ON ( s.s_suppkey = l.lo_suppkey )
	INNER JOIN part as p ON ( p.p_partkey = l.lo_partkey );
```

数据导入

```
sudo -uhive hdfs dfs  -put customer.tbl /tmp/lee/data_dir/customer.tbl
```

```
load data local inpath '/opt/package/ssb-dbgen/lineorder.tbl' into table lineorder;
```

SQL测试

Q1.1

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

HIVE

```
SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
FROM lineorder_flat
WHERE substr(LO_ORDERDATE,2,4) = 1993 AND LO_DISCOUNT BETWEEN 1 AND 3 AND LO_QUANTITY < 25;
```

Q1.2

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

HIVE

```
SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
FROM lineorder_flat
WHERE substr(LO_ORDERDATE,2,4) = 1994 AND LO_DISCOUNT BETWEEN 4 AND 6 AND LO_QUANTITY BETWEEN 26 AND 35;
```

Q1.3

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

HIVE

```
SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
FROM lineorder_flat
WHERE  substr(LO_ORDERDATE,2,4) = 1994
  AND LO_DISCOUNT BETWEEN 5 AND 7 AND LO_QUANTITY BETWEEN 26 AND 35;
```

Q2.1

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

HIVE

```
SELECT
    sum(LO_REVENUE),
    P_BRAND
FROM lineorder_flat
    WHERE P_CATEGORY = 'MFGR#12' AND S_REGION = 'AMERICA'
GROUP BY
    P_BRAND
ORDER BY
    P_BRAND;
```

Q2.2

```
SELECT
    sum(LO_REVENUE),
    toYear(LO_ORDERDATE) AS year,
    P_BRAND
FROM lineorder_flat
WHERE P_BRAND >= 'MFGR#2221' AND P_BRAND <= 'MFGR#2228' AND S_REGION = 'ASIA'
GROUP BY
    year,
    P_BRAND
ORDER BY
    year,
    P_BRAND;
```

HIVE

```sql
SELECT
    sum(LO_REVENUE),
    P_BRAND
FROM lineorder_flat
WHERE P_BRAND >= 'MFGR#2221' AND P_BRAND <= 'MFGR#2228' AND S_REGION = 'ASIA'
GROUP BY
    P_BRAND
ORDER BY
    P_BRAND;
```

Q2.3

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

HIVE

```
SELECT
    sum(LO_REVENUE),
  
    P_BRAND
FROM lineorder_flat
WHERE P_BRAND = 'MFGR#2239' AND S_REGION = 'EUROPE'
GROUP BY

P_BRAND
ORDER BY

P_BRAND;
```

Q3.1

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

HIVE

```
SELECT
    C_NATION,
    S_NATION,
    sum(LO_REVENUE) AS revenue
FROM lineorder_flat
WHERE C_REGION = 'ASIA' AND S_REGION = 'ASIA' AND substr(LO_ORDERDATE,2,4) >= 1992 AND substr(LO_ORDERDATE,2,4) <= 1997
GROUP BY
    C_NATION,
    S_NATION
    
ORDER BY

revenue DESC;
```

Q3.2

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

HIVE

```
SELECT
    C_CITY,
    S_CITY,
    substr(LO_ORDERDATE,2,4) AS aa,
    sum(LO_REVENUE) AS revenue
FROM lineorder_flat
WHERE C_NATION = 'UNITED STATES' AND S_NATION = 'UNITED STATES' AND substr(LO_ORDERDATE,2,4) >= 1992 AND substr(LO_ORDERDATE,2,4) <= 1997
GROUP BY
    C_CITY,
    S_CITY,
    substr(LO_ORDERDATE,2,4)
ORDER BY
    aa ASC,
    revenue DESC;
```

Q3.3

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

Q3.4

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

Q4.1

```
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 ASC,
    C_NATION ASC;
```

Q4.2

```
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 ASC,
    S_NATION ASC,
    P_CATEGORY ASC;
```

Q4.3

```
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 ASC,
    S_CITY ASC,
    P_BRAND ASC;
```

csv 上传 hdfs

load data local inpath 'customer.tbl' into table ssb.customer_txt;

| QL   | Hive  （MR） | Hive  （Spark） |
| ---- | ------------ | --------------- |
| Q1.1 | 47.307       |                 |
| Q1.2 | 46.379       |                 |
| Q1.3 | 44.896       |                 |
| Q2.1 | 19.72        |                 |
| Q2.2 | 85.893       |                 |
| Q2.3 | 83.475       |                 |
| Q3.1 | 83.65        |                 |
| Q3.2 |              |                 |
| Q3.3 |              |                 |
| Q3.4 |              |                 |
| Q4.1 |              |                 |
| Q4.2 |              |                 |
| Q4.3 |              |                 |

select bitmap_union( lo_orderkey)	 from lineorder

BoomLee/bigdata-notes

简介

发行版

贡献者

近期动态

BoomLee/bigdata-notes .gitee-modal { width: 500px !important; }

简介

发行版

贡献者

近期动态

搜索帮助

BoomLee/bigdata-notes