You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by yi...@apache.org on 2022/05/28 09:28:09 UTC
[incubator-doris] branch master updated: [tools]add ssb flat test scripts (#9796)
This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/incubator-doris.git
The following commit(s) were added to refs/heads/master by this push:
new 92c1c82422 [tools]add ssb flat test scripts (#9796)
92c1c82422 is described below
commit 92c1c8242267321dad7115ce37d9d3611d9cee16
Author: Dongyang Li <he...@qq.com>
AuthorDate: Sat May 28 17:28:03 2022 +0800
[tools]add ssb flat test scripts (#9796)
Co-authored-by: stephen <he...@qq.com>
---
docs/en/benchmark/ssb.md | 39 ++--
docs/zh-CN/benchmark/ssb.md | 40 +++--
tools/ssb-tools/README.md | 46 +++++
.../create-ssb-flat-table.sh} | 10 +-
.../create-ssb-tables.sh} | 10 +-
.../create-ssb-flat-table.sql} | 86 ++-------
.../create-ssb-tables.sql} | 0
tools/ssb-tools/doris-cluster.conf | 6 +-
tools/ssb-tools/gen-ssb-data.sh | 52 ++++--
...imension-data.sh => load-ssb-dimension-data.sh} | 30 +++-
.../{load-fact-data.sh => load-ssb-fact-data.sh} | 61 ++++---
tools/ssb-tools/load-ssb-flat-data.sh | 196 +++++++++++++++++++++
.../run-ssb-flat-queries.sh} | 19 +-
.../run-ssb-queries.sh} | 17 +-
.../q1.3.sql => ssb-flat-queries/q1.1.sql} | 17 +-
.../{queries => ssb-flat-queries}/q1.2.sql | 16 +-
.../{queries => ssb-flat-queries}/q1.3.sql | 18 +-
.../q1.2.sql => ssb-flat-queries/q2.1.sql} | 16 +-
.../q1.1.sql => ssb-flat-queries/q2.2.sql} | 19 +-
.../q1.1.sql => ssb-flat-queries/q2.3.sql} | 18 +-
.../q1.1.sql => ssb-flat-queries/q3.1.sql} | 21 ++-
.../q1.1.sql => ssb-flat-queries/q3.2.sql} | 21 ++-
.../q1.1.sql => ssb-flat-queries/q3.3.sql} | 21 ++-
.../q1.1.sql => ssb-flat-queries/q3.4.sql} | 21 ++-
.../q1.1.sql => ssb-flat-queries/q4.1.sql} | 19 +-
.../q1.1.sql => ssb-flat-queries/q4.2.sql} | 25 ++-
.../q1.1.sql => ssb-flat-queries/q4.3.sql} | 21 ++-
tools/ssb-tools/{queries => ssb-queries}/q1.1.sql | 15 +-
tools/ssb-tools/{queries => ssb-queries}/q1.2.sql | 15 +-
tools/ssb-tools/{queries => ssb-queries}/q1.3.sql | 18 +-
tools/ssb-tools/{queries => ssb-queries}/q2.1.sql | 14 +-
tools/ssb-tools/{queries => ssb-queries}/q2.2.sql | 15 +-
tools/ssb-tools/{queries => ssb-queries}/q2.3.sql | 14 +-
tools/ssb-tools/{queries => ssb-queries}/q3.1.sql | 24 +--
tools/ssb-tools/{queries => ssb-queries}/q3.2.sql | 24 +--
tools/ssb-tools/{queries => ssb-queries}/q3.3.sql | 32 ++--
tools/ssb-tools/{queries => ssb-queries}/q3.4.sql | 31 ++--
tools/ssb-tools/{queries => ssb-queries}/q4.1.sql | 28 +--
tools/ssb-tools/{queries => ssb-queries}/q4.2.sql | 34 ++--
tools/ssb-tools/{queries => ssb-queries}/q4.3.sql | 28 +--
tools/tpch-tools/build-tpch-dbgen.sh | 6 +-
tools/tpch-tools/create-tpch-tables.sh | 2 +-
tools/tpch-tools/doris-cluster.conf | 2 +-
tools/tpch-tools/load-tpch-data.sh | 2 +-
tools/tpch-tools/run-tpch-queries.sh | 3 +-
45 files changed, 774 insertions(+), 398 deletions(-)
diff --git a/docs/en/benchmark/ssb.md b/docs/en/benchmark/ssb.md
index 727c2cf485..9b4b1dbc30 100644
--- a/docs/en/benchmark/ssb.md
+++ b/docs/en/benchmark/ssb.md
@@ -76,45 +76,50 @@ Under the `-s 100` parameter, the generated data set size is:
|supplier|200,000 (200,000) |17M |1|
|date| 2556|228K |1|
-3. Build a table
-
- Copy the table creation statement in [create-tables.sql](https://github.com/apache/incubator-doris/tree/master/tools/ssb-tools/create-tables.sql) and execute it in Doris.
-
-4. Import data
+### 3. Build a table
0. Prepare the 'doris-cluster.conf' file.
Before calling the load script, you need to write the FE's ip port and other information in the `doris-cluster.conf` file.
- 'doris-cluster.conf' in the same directory as `load-dimension-data.sh`.
+ 'doris-cluster.conf' in the same directory as `load-ssb-dimension-data.sh`.
The contents of the file include FE's ip, HTTP port, user name, password and the DB name of the data to be loaded:
````
export FE_HOST="xxx"
export FE_HTTP_PORT="8030"
+ export FE_QUERY_PORT="9030"
export USER="root"
export PASSWORD='xxx'
export DB="ssb"
````
+ 1. Create SSB tables with the following command:
+ ```
+ sh create-ssb-tables.sh
+ ```
+ Or, mannually copy the table creation statement in [create-ssb-tables.sql](https://github.com/apache/incubator-doris/tree/master/tools/ssb-tools/ddl/create-ssb-tables.sql) and execute it in Doris.
+
+### 4. Import data
+
1. Load 4 dimension table data (customer, part, supplier and date)
Because the data volume of these 4 dimension tables is small, and the load is simpler, we use the following command to load the data of these 4 tables first:
- `sh load-dimension-data.sh`
+ `sh load-ssb-dimension-data.sh`
2. Load the fact table lineorder.
Load the lineorder table data with the following command:
- `sh load-fact-data.sh -c 5`
+ `sh load-ssb-fact-data.sh -c 5`
- `-c 5` means to start 5 concurrent threads to import (the default is 3). In the case of a single BE node, the load time of lineorder data generated by `sh gen-ssb-data.sh -s 100 -c 100` using `sh load-fact-data.sh -c 3` is about 10 minutes. The memory overhead is about 5-6GB. If you turn on more threads, you can speed up the load speed, but it will increase additional memory overhead.
+ `-c 5` means to start 5 concurrent threads to import (the default is 3). In the case of a single BE node, the load time of lineorder data generated by `sh gen-ssb-data.sh -s 100 -c 100` using `sh load-ssb-fact-data.sh -c 3` is about 10 minutes. The memory overhead is about 5-6GB. If you turn on more threads, you can speed up the load speed, but it will increase additional memory overhead.
> Note: To get a faster import speed, you can add `flush_thread_num_per_store=5` in be.conf and restart BE. This configuration indicates the number of disk write threads for each data directory, and the default is 2. Larger data can increase write data throughput, but may increase IO Util. (Reference value: 1 mechanical disk, when the default is 2, the IO Util during the import process is about 12%, when it is set to 5, the IO Util is about 26%. If it is an SSD disk, it is almost 0) .
-5. Check the loaded data
+### 5. Check the loaded data
```
select count(*) from part;
@@ -126,9 +131,21 @@ Under the `-s 100` parameter, the generated data set size is:
The amount of data should be the same as the number of rows of generated data.
+### 6. Run queries
+
+Execute the following script to run SSB queries:
+
+```
+sh run-ssb-queries.sh
+```
+
+> Note 1: You can try to modify the serssion variables in the script to see the change in query latency.
+>
+> Note 2: Run each query three times in a row, and take the average time as each query time.
+
## Query test
-There are 4 groups of 14 SQL in the SSB test set. The query statement is in the [queries/](https://github.com/apache/incubator-doris/tree/master/tools/ssb-tools/queries) directory.
+There are 4 groups of 14 SQL in the SSB test set. The query statement is in the [queries/](https://github.com/apache/incubator-doris/tree/master/tools/ssb-tools/ssb-queries) directory.
## testing report
diff --git a/docs/zh-CN/benchmark/ssb.md b/docs/zh-CN/benchmark/ssb.md
index 38207d02df..d0f140ef12 100644
--- a/docs/zh-CN/benchmark/ssb.md
+++ b/docs/zh-CN/benchmark/ssb.md
@@ -76,45 +76,51 @@ sh gen-ssb-data.sh -s 100 -c 100
|supplier|20万（200000） |17M |1|
|date| 2556|228K |1|
-3. 建表
-
- 复制 [create-tables.sql](https://github.com/apache/incubator-doris/tree/master/tools/ssb-tools/create-tables.sql) 中的建表语句，在 Doris 中执行。
-
-4. 导入数据
+### 3. 建表
0. 准备 'doris-cluster.conf' 文件。
在调用导入脚本前，需要将 FE 的 ip 端口等信息写在 `doris-cluster.conf` 文件中。
- 文件位置和 `load-dimension-data.sh` 平级。
+ 文件位置和 `load-ssb-dimension-data.sh` 平级。
文件内容包括 FE 的 ip，HTTP 端口，用户名，密码以及待导入数据的 DB 名称：
```
export FE_HOST="xxx"
export FE_HTTP_PORT="8030"
+ export FE_QUERY_PORT="9030"
export USER="root"
export PASSWORD='xxx'
export DB="ssb"
```
+ 1. 执行以下脚本生成创建 SSB 表：
+
+ ```
+ sh create-ssb-tables.sh
+ ```
+ 或者复制 [create-ssb-tables.sql](https://github.com/apache/incubator-doris/tree/master/tools/ssb-tools/ddl/create-ssb-tables.sql) 中的建表语句，在 Doris 中执行。
+
+### 4. 导入数据
+
1. 导入 4 张维度表数据（customer, part, supplier and date）
因为这4张维表数据量较小，导入较简单，我们使用以下命令先导入这4表的数据：
- `sh load-dimension-data.sh`
+ `sh load-ssb-dimension-data.sh`
2. 导入事实表 lineorder。
通过以下命令导入 lineorder 表数据：
- `sh load-fact-data.sh -c 5`
+ `sh load-ssb-fact-data.sh -c 5`
- `-c 5` 表示启动 5 个并发线程导入（默认为3）。在单 BE 节点情况下，由 `sh gen-ssb-data.sh -s 100 -c 100` 生成的 lineorder 数据，使用 `sh load-fact-data.sh -c 3` 的导入时间约为 10min。内存开销约为 5-6GB。如果开启更多线程，可以加快导入速度，但会增加额外的内存开销。
+ `-c 5` 表示启动 5 个并发线程导入（默认为3）。在单 BE 节点情况下，由 `sh gen-ssb-data.sh -s 100 -c 100` 生成的 lineorder 数据，使用 `sh load-ssb-fact-data.sh -c 3` 的导入时间约为 10min。内存开销约为 5-6GB。如果开启更多线程，可以加快导入速度，但会增加额外的内存开销。
> 注：为获得更快的导入速度，你可以在 be.conf 中添加 `flush_thread_num_per_store=5` 后重启BE。该配置表示每个数据目录的写盘线程数，默认为2。较大的数据可以提升写数据吞吐，但可能会增加 IO Util。（参考值：1块机械磁盘，在默认为2的情况下，导入过程中的 IO Util 约为12%，设置为5时，IO Util 约为26%。如果是 SSD 盘，则几乎为 0）。
-5. 检查导入数据
+### 5. 检查导入数据
```
select count(*) from part;
@@ -125,10 +131,22 @@ sh gen-ssb-data.sh -s 100 -c 100
```
数据量应和生成数据的行数一致。
+
+### 6. 执行查询
+
+执行以下脚本跑 SSB 的查询：
+
+```
+sh run-ssb-queries.sh
+```
+
+> 注1：可修改脚本中的设置的session变量来查看变化。
+>
+> 注2：脚本中对于每个query连续跑三次，取平均时间为每个query的耗时。
## 查询测试
-SSB 测试集共 4 组 14 个 SQL。查询语句在 [queries/](https://github.com/apache/incubator-doris/tree/master/tools/ssb-tools/queries) 目录下。
+SSB 测试集共 4 组 14 个 SQL。查询语句在 [queries/](https://github.com/apache/incubator-doris/tree/master/tools/ssb-tools/ssb-queries) 目录下。
## 测试报告
diff --git a/tools/ssb-tools/README.md b/tools/ssb-tools/README.md
new file mode 100644
index 0000000000..5cb2a8e39e
--- /dev/null
+++ b/tools/ssb-tools/README.md
@@ -0,0 +1,46 @@
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+# Usage
+
+ These scripts are used to make ssb and ssb flat test.
+ The ssb flat data comes from ssb tables by way of 'INSERT INTO ... SELECT ...',
+ which means ssb test steps 1 to 4 should have been done before loading ssb flat data.
+
+## ssb test, follow the steps below:
+### 1. build ssb dbgen tool.
+ ./build-ssb-dbgen.sh
+### 2. generate ssb data. use -h for more infomations.
+ ./gen-ssb-data.sh -s 1
+### 3. create ssb tables. modify `doris-cluster.conf` to specify doris info, then run script below.
+ ./create-ssb-tables.sh
+### 4. load ssb data. use -h for help.
+ ./load-ssb-dimension-data.sh
+ ./load-ssb-fact-data.sh
+### 5. run ssb queries.
+ ./run-ssb-queries.sh
+
+## ssb flat test, follow the steps below:
+### 1. prepare ssb data, which means ssb test steps 1 to 4 have been done.
+### 2. create ssb flat table in the same database of ssb tables.
+ ./create-ssb-flat-table.sh
+### 3. load ssb flat data.
+ ./load-ssb-flat-data.sh
+### 4. run ssb flat queries.
+ ./run-ssb-flat-queries.sh
diff --git a/tools/tpch-tools/create-tpch-tables.sh b/tools/ssb-tools/create-ssb-flat-table.sh
similarity index 89%
copy from tools/tpch-tools/create-tpch-tables.sh
copy to tools/ssb-tools/create-ssb-flat-table.sh
index 3c3f2dccbb..9d8ca105bb 100755
--- a/tools/tpch-tools/create-tpch-tables.sh
+++ b/tools/ssb-tools/create-ssb-flat-table.sh
@@ -17,7 +17,7 @@
# under the License.
##############################################################
-# This script is used to create TPC-H tables
+# This script is used to create ssb flat table
##############################################################
set -eo pipefail
@@ -29,10 +29,11 @@ ROOT=$(
)
CURDIR=${ROOT}
+DDL="${CURDIR}/ddl/create-ssb-flat-table.sql"
usage() {
echo "
-This script is used to create TPC-H tables,
+This script is used to create ssb flat table,
will use mysql client to connect Doris server which is specified in doris-cluster.conf file.
Usage: $0
"
@@ -42,6 +43,7 @@ Usage: $0
OPTS=$(getopt \
-n $0 \
-o '' \
+ -o 'h' \
-- "$@")
eval set -- "$OPTS"
@@ -95,5 +97,5 @@ echo "DB: $DB"
mysql -h$FE_HOST -u$USER -P$FE_QUERY_PORT -e "CREATE DATABASE IF NOT EXISTS $DB"
-echo $CURDIR/create-tpch-tables.sql
-mysql -h$FE_HOST -u$USER -P$FE_QUERY_PORT -D$DB <$CURDIR/create-tpch-tables.sql
+echo "Run DDL from ${DDL}"
+mysql -h$FE_HOST -u$USER -P$FE_QUERY_PORT -D$DB <${DDL}
diff --git a/tools/tpch-tools/create-tpch-tables.sh b/tools/ssb-tools/create-ssb-tables.sh
similarity index 89%
copy from tools/tpch-tools/create-tpch-tables.sh
copy to tools/ssb-tools/create-ssb-tables.sh
index 3c3f2dccbb..8b7b4c48fb 100755
--- a/tools/tpch-tools/create-tpch-tables.sh
+++ b/tools/ssb-tools/create-ssb-tables.sh
@@ -17,7 +17,7 @@
# under the License.
##############################################################
-# This script is used to create TPC-H tables
+# This script is used to create SSB tables
##############################################################
set -eo pipefail
@@ -29,10 +29,11 @@ ROOT=$(
)
CURDIR=${ROOT}
+DDL="${CURDIR}/ddl/create-ssb-tables.sql"
usage() {
echo "
-This script is used to create TPC-H tables,
+This script is used to create SSB tables,
will use mysql client to connect Doris server which is specified in doris-cluster.conf file.
Usage: $0
"
@@ -42,6 +43,7 @@ Usage: $0
OPTS=$(getopt \
-n $0 \
-o '' \
+ -o 'h' \
-- "$@")
eval set -- "$OPTS"
@@ -95,5 +97,5 @@ echo "DB: $DB"
mysql -h$FE_HOST -u$USER -P$FE_QUERY_PORT -e "CREATE DATABASE IF NOT EXISTS $DB"
-echo $CURDIR/create-tpch-tables.sql
-mysql -h$FE_HOST -u$USER -P$FE_QUERY_PORT -D$DB <$CURDIR/create-tpch-tables.sql
+echo "Run DDL from ${DDL}"
+mysql -h$FE_HOST -u$USER -P$FE_QUERY_PORT -D$DB <${DDL}
diff --git a/tools/ssb-tools/create-tables.sql b/tools/ssb-tools/ddl/create-ssb-flat-table.sql
similarity index 65%
copy from tools/ssb-tools/create-tables.sql
copy to tools/ssb-tools/ddl/create-ssb-flat-table.sql
index 5e99f89146..723cf2cdcf 100644
--- a/tools/ssb-tools/create-tables.sql
+++ b/tools/ssb-tools/ddl/create-ssb-flat-table.sql
@@ -15,7 +15,7 @@
-- specific language governing permissions and limitations
-- under the License.
-CREATE TABLE `lineorder` (
+CREATE TABLE `lineorder_flat` (
`lo_orderkey` bigint(20) NOT NULL COMMENT "",
`lo_linenumber` bigint(20) NOT NULL COMMENT "",
`lo_custkey` int(11) NOT NULL COMMENT "",
@@ -32,62 +32,20 @@ CREATE TABLE `lineorder` (
`lo_supplycost` bigint(20) NOT NULL COMMENT "",
`lo_tax` bigint(20) NOT NULL COMMENT "",
`lo_commitdate` bigint(20) NOT NULL COMMENT "",
- `lo_shipmode` varchar(11) NOT NULL COMMENT ""
-)
-PARTITION BY RANGE(`lo_orderdate`)
-(PARTITION p1992 VALUES [("-2147483648"), ("19930101")),
-PARTITION p1993 VALUES [("19930101"), ("19940101")),
-PARTITION p1994 VALUES [("19940101"), ("19950101")),
-PARTITION p1995 VALUES [("19950101"), ("19960101")),
-PARTITION p1996 VALUES [("19960101"), ("19970101")),
-PARTITION p1997 VALUES [("19970101"), ("19980101")),
-PARTITION p1998 VALUES [("19980101"), ("19990101")))
-DISTRIBUTED BY HASH(`lo_orderkey`) BUCKETS 48
-PROPERTIES (
-"replication_num" = "1"
-);
-
-CREATE TABLE `customer` (
- `c_custkey` int(11) NOT NULL COMMENT "",
+ `lo_shipmode` varchar(11) NOT NULL COMMENT "",
`c_name` varchar(26) NOT NULL COMMENT "",
`c_address` varchar(41) NOT NULL COMMENT "",
`c_city` varchar(11) NOT NULL COMMENT "",
`c_nation` varchar(16) NOT NULL COMMENT "",
`c_region` varchar(13) NOT NULL COMMENT "",
`c_phone` varchar(16) NOT NULL COMMENT "",
- `c_mktsegment` varchar(11) NOT NULL COMMENT ""
-)
-DISTRIBUTED BY HASH(`c_custkey`) BUCKETS 10
-PROPERTIES (
-"replication_num" = "1"
-);
-
-CREATE TABLE `date` (
- `d_datekey` int(11) NOT NULL COMMENT "",
- `d_date` varchar(20) NOT NULL COMMENT "",
- `d_dayofweek` varchar(10) NOT NULL COMMENT "",
- `d_month` varchar(11) NOT NULL COMMENT "",
- `d_year` int(11) NOT NULL COMMENT "",
- `d_yearmonthnum` int(11) NOT NULL COMMENT "",
- `d_yearmonth` varchar(9) NOT NULL COMMENT "",
- `d_daynuminweek` int(11) NOT NULL COMMENT "",
- `d_daynuminmonth` int(11) NOT NULL COMMENT "",
- `d_daynuminyear` int(11) NOT NULL COMMENT "",
- `d_monthnuminyear` int(11) NOT NULL COMMENT "",
- `d_weeknuminyear` int(11) NOT NULL COMMENT "",
- `d_sellingseason` varchar(14) NOT NULL COMMENT "",
- `d_lastdayinweekfl` int(11) NOT NULL COMMENT "",
- `d_lastdayinmonthfl` int(11) NOT NULL COMMENT "",
- `d_holidayfl` int(11) NOT NULL COMMENT "",
- `d_weekdayfl` int(11) NOT NULL COMMENT ""
-)
-DISTRIBUTED BY HASH(`d_datekey`) BUCKETS 1
-PROPERTIES (
-"replication_num" = "1"
-);
-
-CREATE TABLE `part` (
- `p_partkey` int(11) NOT NULL COMMENT "",
+ `c_mktsegment` varchar(11) NOT NULL COMMENT "",
+ `s_name` varchar(26) NOT NULL COMMENT "",
+ `s_address` varchar(26) NOT NULL COMMENT "",
+ `s_city` varchar(11) NOT NULL COMMENT "",
+ `s_nation` varchar(16) NOT NULL COMMENT "",
+ `s_region` varchar(13) NOT NULL COMMENT "",
+ `s_phone` varchar(16) NOT NULL COMMENT "",
`p_name` varchar(23) NOT NULL COMMENT "",
`p_mfgr` varchar(7) NOT NULL COMMENT "",
`p_category` varchar(8) NOT NULL COMMENT "",
@@ -97,21 +55,15 @@ CREATE TABLE `part` (
`p_size` int(11) NOT NULL COMMENT "",
`p_container` varchar(11) NOT NULL COMMENT ""
)
-DISTRIBUTED BY HASH(`p_partkey`) BUCKETS 10
-PROPERTIES (
-"replication_num" = "1"
-);
-
-CREATE TABLE `supplier` (
- `s_suppkey` int(11) NOT NULL COMMENT "",
- `s_name` varchar(26) NOT NULL COMMENT "",
- `s_address` varchar(26) NOT NULL COMMENT "",
- `s_city` varchar(11) NOT NULL COMMENT "",
- `s_nation` varchar(16) NOT NULL COMMENT "",
- `s_region` varchar(13) NOT NULL COMMENT "",
- `s_phone` varchar(16) NOT NULL COMMENT ""
-)
-DISTRIBUTED BY HASH(`s_suppkey`) BUCKETS 10
+PARTITION BY RANGE(`lo_orderdate`)
+(PARTITION p1992 VALUES [("-2147483648"), ("19930101")),
+PARTITION p1993 VALUES [("19930101"), ("19940101")),
+PARTITION p1994 VALUES [("19940101"), ("19950101")),
+PARTITION p1995 VALUES [("19950101"), ("19960101")),
+PARTITION p1996 VALUES [("19960101"), ("19970101")),
+PARTITION p1997 VALUES [("19970101"), ("19980101")),
+PARTITION p1998 VALUES [("19980101"), ("19990101")))
+DISTRIBUTED BY HASH(`lo_orderkey`) BUCKETS 48
PROPERTIES (
"replication_num" = "1"
-);
+);
\ No newline at end of file
diff --git a/tools/ssb-tools/create-tables.sql b/tools/ssb-tools/ddl/create-ssb-tables.sql
similarity index 100%
rename from tools/ssb-tools/create-tables.sql
rename to tools/ssb-tools/ddl/create-ssb-tables.sql
diff --git a/tools/ssb-tools/doris-cluster.conf b/tools/ssb-tools/doris-cluster.conf
index bef6c7b54c..5567a2f588 100644
--- a/tools/ssb-tools/doris-cluster.conf
+++ b/tools/ssb-tools/doris-cluster.conf
@@ -19,9 +19,11 @@
export FE_HOST='127.0.0.1'
# http_port in fe.conf
export FE_HTTP_PORT=8030
+# query_port in fe.conf
+export FE_QUERY_PORT=9030
# Doris username
-export USER='admin'
+export USER='root'
# Doris password
export PASSWORD=''
# The database where SSB tables located
-export DB='db1'
+export DB='ssb'
diff --git a/tools/ssb-tools/gen-ssb-data.sh b/tools/ssb-tools/gen-ssb-data.sh
index 594f1296a2..929a9e1eea 100755
--- a/tools/ssb-tools/gen-ssb-data.sh
+++ b/tools/ssb-tools/gen-ssb-data.sh
@@ -22,8 +22,11 @@
set -eo pipefail
-ROOT=`dirname "$0"`
-ROOT=`cd "$ROOT"; pwd`
+ROOT=$(dirname "$0")
+ROOT=$(
+ cd "$ROOT"
+ pwd
+)
CURDIR=${ROOT}
SSB_DBGEN_DIR=$CURDIR/ssb-dbgen/
@@ -56,23 +59,38 @@ SCALE_FACTOR=100
PARALLEL=10
HELP=0
-if [ $# == 0 ] ; then
- usage
+if [ $# == 0 ]; then
+ usage
fi
while true; do
- case "$1" in
- -h) HELP=1 ; shift ;;
- -s) SCALE_FACTOR=$2 ; shift 2 ;;
- -c) PARALLEL=$2 ; shift 2 ;;
- --) shift ; break ;;
- *) echo "Internal error" ; exit 1 ;;
- esac
+ case "$1" in
+ -h)
+ HELP=1
+ shift
+ ;;
+ -s)
+ SCALE_FACTOR=$2
+ shift 2
+ ;;
+ -c)
+ PARALLEL=$2
+ shift 2
+ ;;
+ --)
+ shift
+ break
+ ;;
+ *)
+ echo "Internal error"
+ exit 1
+ ;;
+ esac
done
if [[ ${HELP} -eq 1 ]]; then
- usage
- exit
+ usage
+ exit
fi
echo "Scale Factor: $SCALE_FACTOR"
@@ -80,13 +98,13 @@ echo "Parallelism: $PARALLEL"
# check if dbgen exists
if [[ ! -f $SSB_DBGEN_DIR/dbgen ]]; then
- echo "$SSB_DBGEN_DIR/dbgen does not exist. Run build-ssb-dbgen.sh first to build it first."
- exit 1
+ echo "$SSB_DBGEN_DIR/dbgen does not exist. Run build-ssb-dbgen.sh first to build it first."
+ exit 1
fi
if [[ -d $SSB_DATA_DIR/ ]]; then
- echo "$SSB_DATA_DIR exists. Remove it before generating data"
- exit 1
+ echo "$SSB_DATA_DIR exists. Remove it before generating data"
+ exit 1
fi
mkdir $SSB_DATA_DIR/
diff --git a/tools/ssb-tools/load-dimension-data.sh b/tools/ssb-tools/load-ssb-dimension-data.sh
similarity index 59%
rename from tools/ssb-tools/load-dimension-data.sh
rename to tools/ssb-tools/load-ssb-dimension-data.sh
index f24ad3af59..9e438ee213 100755
--- a/tools/ssb-tools/load-dimension-data.sh
+++ b/tools/ssb-tools/load-ssb-dimension-data.sh
@@ -25,8 +25,11 @@
set -eo pipefail
-ROOT=`dirname "$0"`
-ROOT=`cd "$ROOT"; pwd`
+ROOT=$(dirname "$0")
+ROOT=$(
+ cd "$ROOT"
+ pwd
+)
CURDIR=${ROOT}
SSB_DATA_DIR=$CURDIR/ssb-data/
@@ -59,10 +62,25 @@ echo "PASSWORD: $PASSWORD"
echo "DB: $DB"
echo 'Loading data for table: part'
-curl --location-trusted -u $USER:$PASSWORD -H "column_separator:|" -H "columns:p_partkey,p_name,p_mfgr,p_category,p_brand,p_color,p_type,p_size,p_container,p_dummy" -T $SSB_DATA_DIR/part.tbl http://$FE_HOST:$FE_HTTP_PORT/api/$DB/part/_stream_load
+curl --location-trusted -u $USER:$PASSWORD \
+ -H "column_separator:|" \
+ -H "columns:p_partkey,p_name,p_mfgr,p_category,p_brand,p_color,p_type,p_size,p_container,p_dummy" \
+ -T $SSB_DATA_DIR/part.tbl http://$FE_HOST:$FE_HTTP_PORT/api/$DB/part/_stream_load
+
echo 'Loading data for table: date'
-curl --location-trusted -u $USER:$PASSWORD -H "column_separator:|" -H "columns:d_datekey,d_date,d_dayofweek,d_month,d_year,d_yearmonthnum,d_yearmonth,d_daynuminweek,d_daynuminmonth,d_daynuminyear,d_monthnuminyear,d_weeknuminyear,d_sellingseason,d_lastdayinweekfl,d_lastdayinmonthfl,d_holidayfl,d_weekdayfl,d_dummy" -T $SSB_DATA_DIR/date.tbl http://$FE_HOST:$FE_HTTP_PORT/api/$DB/date/_stream_load
+curl --location-trusted -u $USER:$PASSWORD \
+ -H "column_separator:|" \
+ -H "columns:d_datekey,d_date,d_dayofweek,d_month,d_year,d_yearmonthnum,d_yearmonth,d_daynuminweek,d_daynuminmonth,d_daynuminyear,d_monthnuminyear,d_weeknuminyear,d_sellingseason,d_lastdayinweekfl,d_lastdayinmonthfl,d_holidayfl,d_weekdayfl,d_dummy" \
+ -T $SSB_DATA_DIR/date.tbl http://$FE_HOST:$FE_HTTP_PORT/api/$DB/date/_stream_load
+
echo 'Loading data for table: supplier'
-curl --location-trusted -u $USER:$PASSWORD -H "column_separator:|" -H "columns:s_suppkey,s_name,s_address,s_city,s_nation,s_region,s_phone,s_dummy" -T $SSB_DATA_DIR/supplier.tbl http://$FE_HOST:$FE_HTTP_PORT/api/$DB/supplier/_stream_load
+curl --location-trusted -u $USER:$PASSWORD \
+ -H "column_separator:|" \
+ -H "columns:s_suppkey,s_name,s_address,s_city,s_nation,s_region,s_phone,s_dummy" \
+ -T $SSB_DATA_DIR/supplier.tbl http://$FE_HOST:$FE_HTTP_PORT/api/$DB/supplier/_stream_load
+
echo 'Loading data for table: customer'
-curl --location-trusted -u $USER:$PASSWORD -H "column_separator:|" -H "columns:c_custkey,c_name,c_address,c_city,c_nation,c_region,c_phone,c_mktsegment,no_use" -T $SSB_DATA_DIR/customer.tbl http://$FE_HOST:$FE_HTTP_PORT/api/$DB/customer/_stream_load
+curl --location-trusted -u $USER:$PASSWORD \
+ -H "column_separator:|" \
+ -H "columns:c_custkey,c_name,c_address,c_city,c_nation,c_region,c_phone,c_mktsegment,no_use" \
+ -T $SSB_DATA_DIR/customer.tbl http://$FE_HOST:$FE_HTTP_PORT/api/$DB/customer/_stream_load
diff --git a/tools/ssb-tools/load-fact-data.sh b/tools/ssb-tools/load-ssb-fact-data.sh
similarity index 71%
rename from tools/ssb-tools/load-fact-data.sh
rename to tools/ssb-tools/load-ssb-fact-data.sh
index 24bc7f3bf4..17aa3b6252 100755
--- a/tools/ssb-tools/load-fact-data.sh
+++ b/tools/ssb-tools/load-ssb-fact-data.sh
@@ -23,14 +23,17 @@
set -eo pipefail
-ROOT=`dirname "$0"`
-ROOT=`cd "$ROOT"; pwd`
+ROOT=$(dirname "$0")
+ROOT=$(
+ cd "$ROOT"
+ pwd
+)
CURDIR=${ROOT}
SSB_DATA_DIR=$CURDIR/ssb-data/
usage() {
- echo "
+ echo "
Usage: $0 <options>
Optional options:
-c parallelism to load data of lineorder table, default is 5.
@@ -39,30 +42,42 @@ Usage: $0 <options>
$0 load data using default value.
$0 -c 10 load lineorder table data using parallelism 10.
"
- exit 1
+ exit 1
}
OPTS=$(getopt \
- -n $0 \
- -o '' \
- -o 'c:' \
- -- "$@")
+ -n $0 \
+ -o '' \
+ -o 'hc:' \
+ -- "$@")
eval set -- "$OPTS"
PARALLEL=3
HELP=0
-if [ $# == 0 ] ; then
+if [ $# == 0 ]; then
usage
fi
while true; do
case "$1" in
- -h) HELP=1 ; shift ;;
- -c) PARALLEL=$2 ; shift 2 ;;
- --) shift ; break ;;
- *) echo "Internal error" ; exit 1 ;;
+ -h)
+ HELP=1
+ shift
+ ;;
+ -c)
+ PARALLEL=$2
+ shift 2
+ ;;
+ --)
+ shift
+ break
+ ;;
+ *)
+ echo "Internal error"
+ exit 1
+ ;;
esac
done
@@ -99,28 +114,30 @@ echo "USER: $USER"
echo "PASSWORD: $PASSWORD"
echo "DB: $DB"
-function load()
-{
+function load() {
echo $@
- curl --location-trusted -u $USER:$PASSWORD -H "column_separator:|" -H "columns:lo_orderkey,lo_linenumber,lo_custkey,lo_partkey,lo_suppkey,lo_orderdate,lo_orderpriority,lo_shippriority,lo_quantity,lo_extendedprice,lo_ordtotalprice,lo_discount,lo_revenue,lo_supplycost,lo_tax,lo_commitdate,lo_shipmode,lo_dummy" -T $@ http://$FE_HOST:$FE_HTTP_PORT/api/$DB/lineorder/_stream_load
+ curl --location-trusted -u $USER:$PASSWORD \
+ -H "column_separator:|" \
+ -H "columns:lo_orderkey,lo_linenumber,lo_custkey,lo_partkey,lo_suppkey,lo_orderdate,lo_orderpriority,lo_shippriority,lo_quantity,lo_extendedprice,lo_ordtotalprice,lo_discount,lo_revenue,lo_supplycost,lo_tax,lo_commitdate,lo_shipmode,lo_dummy" \
+ -T $@ http://$FE_HOST:$FE_HTTP_PORT/api/$DB/lineorder/_stream_load
}
-
# set parallelism
[ -e /tmp/fd1 ] || mkfifo /tmp/fd1
exec 3<>/tmp/fd1
rm -rf /tmp/fd1
-for ((i=1;i<=$PARALLEL;i++))
-do
+for ((i = 1; i <= $PARALLEL; i++)); do
echo >&3
done
-for file in `ls $SSB_DATA_DIR/lineorder.tbl.*`
-do
+for file in $(ls $SSB_DATA_DIR/lineorder.tbl.*); do
read -u3
{
load $file
echo >&3
- }&
+ } &
done
+
+# wait for child thread finished
+wait
diff --git a/tools/ssb-tools/load-ssb-flat-data.sh b/tools/ssb-tools/load-ssb-flat-data.sh
new file mode 100755
index 0000000000..268c1c48b6
--- /dev/null
+++ b/tools/ssb-tools/load-ssb-flat-data.sh
@@ -0,0 +1,196 @@
+#!/usr/bin/env bash
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+##############################################################
+# This script is used to load generated ssb data set to Doris
+# Only for 1 fact table: lineorder
+##############################################################
+
+set -eo pipefail
+
+ROOT=$(dirname "$0")
+ROOT=$(
+ cd "$ROOT"
+ pwd
+)
+
+CURDIR=${ROOT}
+SSB_DATA_DIR=$CURDIR/ssb-data/
+
+usage() {
+ echo "
+The ssb flat data actually comes from ssb tables, and will load by 'INSERT INTO ... SELECT ...'
+Usage: $0 <options>
+ "
+ exit 1
+}
+
+OPTS=$(getopt \
+ -n $0 \
+ -o '' \
+ -o 'h' \
+ -- "$@")
+
+eval set -- "$OPTS"
+
+HELP=0
+while true; do
+ case "$1" in
+ -h)
+ HELP=1
+ shift
+ ;;
+ --)
+ shift
+ break
+ ;;
+ *)
+ echo "Internal error"
+ exit 1
+ ;;
+ esac
+done
+
+if [[ ${HELP} -eq 1 ]]; then
+ usage
+ exit
+fi
+
+check_prerequest() {
+ local CMD=$1
+ local NAME=$2
+ if ! $CMD; then
+ echo "$NAME is missing. This script depends on cURL to load data to Doris."
+ exit 1
+ fi
+}
+
+run_sql() {
+ sql="$@"
+ echo $sql
+ mysql -h$FE_HOST -u$USER -P$FE_QUERY_PORT -D$DB -e "$@"
+}
+
+load_lineitem_flat() {
+ # Loading data in batches by year.
+ for con in 'lo_orderdate<19930101' 'lo_orderdate>=19930101 and lo_orderdate<19940101' 'lo_orderdate>=19940101 and lo_orderdate<19950101' 'lo_orderdate>=19950101 and lo_orderdate<19960101' 'lo_orderdate>=19960101 and lo_orderdate<19970101' 'lo_orderdate>=19970101 and lo_orderdate<19980101' 'lo_orderdate>=19980101'; do
+ echo -e "\n$con"
+ run_sql "
+INSERT INTO lineorder_flat
+SELECT
+ lo_orderkey,
+ lo_linenumber,
+ lo_custkey,
+ lo_partkey,
+ lo_suppkey,
+ lo_orderdate,
+ lo_orderpriority,
+ lo_shippriority,
+ lo_quantity,
+ lo_extendedprice,
+ lo_ordtotalprice,
+ lo_discount,
+ lo_revenue,
+ lo_supplycost,
+ lo_tax,
+ lo_commitdate,
+ lo_shipmode,
+ c_name,
+ c_address,
+ c_city,
+ c_nation,
+ c_region,
+ c_phone,
+ c_mktsegment,
+ s_name,
+ s_address,
+ s_city,
+ s_nation,
+ s_region,
+ s_phone,
+ p_name,
+ p_mfgr,
+ p_category,
+ p_brand,
+ p_color,
+ p_type,
+ p_size,
+ p_container
+FROM (
+ SELECT
+ lo_orderkey,
+ lo_linenumber,
+ lo_custkey,
+ lo_partkey,
+ lo_suppkey,
+ lo_orderdate,
+ lo_orderpriority,
+ lo_shippriority,
+ lo_quantity,
+ lo_extendedprice,
+ lo_ordtotalprice,
+ lo_discount,
+ lo_revenue,
+ lo_supplycost,
+ lo_tax,
+ lo_commitdate,
+ lo_shipmode
+ FROM lineorder
+ WHERE ${con}
+) l
+INNER JOIN customer c
+ON (c.c_custkey = l.lo_custkey)
+INNER JOIN supplier s
+ON (s.s_suppkey = l.lo_suppkey)
+INNER JOIN part p
+ON (p.p_partkey = l.lo_partkey);
+"
+ done
+}
+
+check_prerequest "curl --version" "curl"
+
+# load lineorder
+source $CURDIR/doris-cluster.conf
+
+echo "FE_HOST: $FE_HOST"
+echo "FE_HTTP_PORT: $FE_HTTP_PORT"
+echo "USER: $USER"
+echo "PASSWORD: $PASSWORD"
+echo "DB: $DB"
+
+echo 'Loading data for table: lineorder_flat'
+
+echo '============================================'
+echo "change some session variables before load, and then restore after load."
+origin_query_timeout=$(run_sql 'select @@query_timeout;' | sed -n '3p')
+origin_parallel=$(run_sql 'select @@parallel_fragment_exec_instance_num;' | sed -n '3p')
+# set parallel_fragment_exec_instance_num=1, loading maybe slow but stable.
+run_sql "set global query_timeout=7200;"
+run_sql "set global parallel_fragment_exec_instance_num=1;"
+
+echo '============================================'
+load_lineitem_flat
+
+echo '============================================'
+echo "restore session variables"
+run_sql "set global query_timeout=${origin_query_timeout};"
+run_sql "set global parallel_fragment_exec_instance_num=${origin_parallel};"
+
+echo '============================================'
+echo "DONE."
diff --git a/tools/tpch-tools/run-tpch-queries.sh b/tools/ssb-tools/run-ssb-flat-queries.sh
similarity index 83%
copy from tools/tpch-tools/run-tpch-queries.sh
copy to tools/ssb-tools/run-ssb-flat-queries.sh
index d5a5bb7282..bf88072d8b 100755
--- a/tools/tpch-tools/run-tpch-queries.sh
+++ b/tools/ssb-tools/run-ssb-flat-queries.sh
@@ -17,7 +17,7 @@
# under the License.
##############################################################
-# This script is used to create TPC-H tables
+# This script is used to create ssb flat queries
##############################################################
set -eo pipefail
@@ -29,11 +29,11 @@ ROOT=$(
)
CURDIR=${ROOT}
-QUERIES_DIR=$CURDIR/queries
+QUERIES_DIR=$CURDIR/ssb-flat-queries
usage() {
echo "
-This script is used to run TPC-H 22queries,
+This script is used to run SSB flat 13queries,
will use mysql client to connect Doris server which parameter is specified in doris-cluster.conf file.
Usage: $0
"
@@ -43,6 +43,7 @@ Usage: $0
OPTS=$(getopt \
-n $0 \
-o '' \
+ -o 'h' \
-- "$@")
eval set -- "$OPTS"
@@ -100,17 +101,19 @@ pre_set() {
}
pre_set "set global enable_vectorized_engine=1;"
-pre_set "set global parallel_fragment_exec_instance_num=8;"
-pre_set "set global exec_mem_limit=48G;"
+pre_set "set global parallel_fragment_exec_instance_num=1;"
+pre_set "set global exec_mem_limit=8G;"
pre_set "set global batch_size=4096;"
-# pre_set "show variables like 'batch_size';"
+echo '============================================'
+pre_set "show variables"
+echo '============================================'
-for i in $(seq 1 22); do
+for i in '1.1' '1.2' '1.3' '2.1' '2.2' '2.3' '3.1' '3.2' '3.3' '3.4' '4.1' '4.2' '4.3'; do
total=0
# Each query is executed three times and takes the average time
for j in $(seq 1 3); do
start=$(date +%s%3N)
- mysql -h$FE_HOST -u $USER -P$FE_QUERY_PORT -D$DB <$QUERIES_DIR/q$i.sql >/dev/null
+ mysql -h$FE_HOST -u $USER -P$FE_QUERY_PORT -D$DB <$QUERIES_DIR/q${i}.sql >/dev/null
end=$(date +%s%3N)
total=$((total + end - start))
done
diff --git a/tools/tpch-tools/run-tpch-queries.sh b/tools/ssb-tools/run-ssb-queries.sh
similarity index 84%
copy from tools/tpch-tools/run-tpch-queries.sh
copy to tools/ssb-tools/run-ssb-queries.sh
index d5a5bb7282..75cbbb089d 100755
--- a/tools/tpch-tools/run-tpch-queries.sh
+++ b/tools/ssb-tools/run-ssb-queries.sh
@@ -17,7 +17,7 @@
# under the License.
##############################################################
-# This script is used to create TPC-H tables
+# This script is used to create ssb queries
##############################################################
set -eo pipefail
@@ -29,11 +29,11 @@ ROOT=$(
)
CURDIR=${ROOT}
-QUERIES_DIR=$CURDIR/queries
+QUERIES_DIR=$CURDIR/ssb-queries
usage() {
echo "
-This script is used to run TPC-H 22queries,
+This script is used to run SSB 13queries,
will use mysql client to connect Doris server which parameter is specified in doris-cluster.conf file.
Usage: $0
"
@@ -43,6 +43,7 @@ Usage: $0
OPTS=$(getopt \
-n $0 \
-o '' \
+ -o 'h' \
-- "$@")
eval set -- "$OPTS"
@@ -103,14 +104,18 @@ pre_set "set global enable_vectorized_engine=1;"
pre_set "set global parallel_fragment_exec_instance_num=8;"
pre_set "set global exec_mem_limit=48G;"
pre_set "set global batch_size=4096;"
-# pre_set "show variables like 'batch_size';"
+pre_set "set global enable_projection=true;"
+pre_set "set global runtime_filter_mode=global;"
+echo '============================================'
+pre_set "show variables"
+echo '============================================'
-for i in $(seq 1 22); do
+for i in '1.1' '1.2' '1.3' '2.1' '2.2' '2.3' '3.1' '3.2' '3.3' '3.4' '4.1' '4.2' '4.3'; do
total=0
# Each query is executed three times and takes the average time
for j in $(seq 1 3); do
start=$(date +%s%3N)
- mysql -h$FE_HOST -u $USER -P$FE_QUERY_PORT -D$DB <$QUERIES_DIR/q$i.sql >/dev/null
+ mysql -h$FE_HOST -u $USER -P$FE_QUERY_PORT -D$DB <$QUERIES_DIR/q${i}.sql >/dev/null
end=$(date +%s%3N)
total=$((total + end - start))
done
diff --git a/tools/ssb-tools/queries/q1.3.sql b/tools/ssb-tools/ssb-flat-queries/q1.1.sql
similarity index 78%
copy from tools/ssb-tools/queries/q1.3.sql
copy to tools/ssb-tools/ssb-flat-queries/q1.1.sql
index ed6e51b1cf..fbc2c6e4a1 100644
--- a/tools/ssb-tools/queries/q1.3.sql
+++ b/tools/ssb-tools/ssb-flat-queries/q1.1.sql
@@ -14,12 +14,11 @@
-- KIND, either express or implied. See the License for the
-- specific language governing permissions and limitations
-- under the License.
-
-SELECT SUM(lo_extendedprice*lo_discount) AS
-REVENUE
-FROM lineorder, date
-WHERE lo_orderdate = d_datekey
-AND d_weeknuminyear= 6
-AND d_year = 1994
-AND lo_discount BETWEEN 5 AND 7
-AND lo_quantity BETWEEN 26 AND 35;
+--Q1.1
+SELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
+FROM lineorder_flat
+WHERE
+ LO_ORDERDATE >= 19930101
+ AND LO_ORDERDATE <= 19931231
+ AND LO_DISCOUNT BETWEEN 1 AND 3
+ AND LO_QUANTITY < 25;
\ No newline at end of file
diff --git a/tools/ssb-tools/queries/q1.2.sql b/tools/ssb-tools/ssb-flat-queries/q1.2.sql
similarity index 78%
copy from tools/ssb-tools/queries/q1.2.sql
copy to tools/ssb-tools/ssb-flat-queries/q1.2.sql
index 1b8442bd93..3a899c9344 100644
--- a/tools/ssb-tools/queries/q1.2.sql
+++ b/tools/ssb-tools/ssb-flat-queries/q1.2.sql
@@ -14,11 +14,11 @@
-- KIND, either express or implied. See the License for the
-- specific language governing permissions and limitations
-- under the License.
-
-SELECT SUM(lo_extendedprice*lo_discount) AS
-REVENUE
-FROM lineorder, date
-WHERE lo_orderdate = d_datekey
-AND d_yearmonth = 'Jan1994'
-AND lo_discount BETWEEN 4 AND 6
-AND lo_quantity BETWEEN 26 AND 35;
+--Q1.2
+SELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
+FROM lineorder_flat
+WHERE
+ LO_ORDERDATE >= 19940101
+ AND LO_ORDERDATE <= 19940131
+ AND LO_DISCOUNT BETWEEN 4 AND 6
+ AND LO_QUANTITY BETWEEN 26 AND 35;
\ No newline at end of file
diff --git a/tools/ssb-tools/queries/q1.3.sql b/tools/ssb-tools/ssb-flat-queries/q1.3.sql
similarity index 75%
copy from tools/ssb-tools/queries/q1.3.sql
copy to tools/ssb-tools/ssb-flat-queries/q1.3.sql
index ed6e51b1cf..5aaeff83a7 100644
--- a/tools/ssb-tools/queries/q1.3.sql
+++ b/tools/ssb-tools/ssb-flat-queries/q1.3.sql
@@ -14,12 +14,12 @@
-- KIND, either express or implied. See the License for the
-- specific language governing permissions and limitations
-- under the License.
-
-SELECT SUM(lo_extendedprice*lo_discount) AS
-REVENUE
-FROM lineorder, date
-WHERE lo_orderdate = d_datekey
-AND d_weeknuminyear= 6
-AND d_year = 1994
-AND lo_discount BETWEEN 5 AND 7
-AND lo_quantity BETWEEN 26 AND 35;
+--Q1.3
+SELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
+FROM lineorder_flat
+WHERE
+ weekofyear(LO_ORDERDATE) = 6
+ AND LO_ORDERDATE >= 19940101
+ AND LO_ORDERDATE <= 19941231
+ AND LO_DISCOUNT BETWEEN 5 AND 7
+ AND LO_QUANTITY BETWEEN 26 AND 35;
\ No newline at end of file
diff --git a/tools/ssb-tools/queries/q1.2.sql b/tools/ssb-tools/ssb-flat-queries/q2.1.sql
similarity index 79%
copy from tools/ssb-tools/queries/q1.2.sql
copy to tools/ssb-tools/ssb-flat-queries/q2.1.sql
index 1b8442bd93..254ea6481a 100644
--- a/tools/ssb-tools/queries/q1.2.sql
+++ b/tools/ssb-tools/ssb-flat-queries/q2.1.sql
@@ -14,11 +14,11 @@
-- KIND, either express or implied. See the License for the
-- specific language governing permissions and limitations
-- under the License.
-
-SELECT SUM(lo_extendedprice*lo_discount) AS
-REVENUE
-FROM lineorder, date
-WHERE lo_orderdate = d_datekey
-AND d_yearmonth = 'Jan1994'
-AND lo_discount BETWEEN 4 AND 6
-AND lo_quantity BETWEEN 26 AND 35;
+--Q2.1
+SELECT
+ SUM(LO_REVENUE), (LO_ORDERDATE DIV 10000) 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;
\ No newline at end of file
diff --git a/tools/ssb-tools/queries/q1.1.sql b/tools/ssb-tools/ssb-flat-queries/q2.2.sql
similarity index 77%
copy from tools/ssb-tools/queries/q1.1.sql
copy to tools/ssb-tools/ssb-flat-queries/q2.2.sql
index 4ef15e93ea..6a636f3a9e 100644
--- a/tools/ssb-tools/queries/q1.1.sql
+++ b/tools/ssb-tools/ssb-flat-queries/q2.2.sql
@@ -14,11 +14,14 @@
-- KIND, either express or implied. See the License for the
-- specific language governing permissions and limitations
-- under the License.
-
-SELECT SUM(lo_extendedprice*lo_discount) AS
-REVENUE
-FROM lineorder, date
-WHERE lo_orderdate = d_datekey
-AND d_year = 1993
-AND lo_discount BETWEEN 1 AND 3
-AND lo_quantity < 25;
+--Q2.2
+SELECT
+ SUM(LO_REVENUE), (LO_ORDERDATE DIV 10000) 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;
\ No newline at end of file
diff --git a/tools/ssb-tools/queries/q1.1.sql b/tools/ssb-tools/ssb-flat-queries/q2.3.sql
similarity index 79%
copy from tools/ssb-tools/queries/q1.1.sql
copy to tools/ssb-tools/ssb-flat-queries/q2.3.sql
index 4ef15e93ea..a2ef0c6df3 100644
--- a/tools/ssb-tools/queries/q1.1.sql
+++ b/tools/ssb-tools/ssb-flat-queries/q2.3.sql
@@ -14,11 +14,13 @@
-- KIND, either express or implied. See the License for the
-- specific language governing permissions and limitations
-- under the License.
-
-SELECT SUM(lo_extendedprice*lo_discount) AS
-REVENUE
-FROM lineorder, date
-WHERE lo_orderdate = d_datekey
-AND d_year = 1993
-AND lo_discount BETWEEN 1 AND 3
-AND lo_quantity < 25;
+--Q2.3
+SELECT
+ SUM(LO_REVENUE), (LO_ORDERDATE DIV 10000) 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;
\ No newline at end of file
diff --git a/tools/ssb-tools/queries/q1.1.sql b/tools/ssb-tools/ssb-flat-queries/q3.1.sql
similarity index 71%
copy from tools/ssb-tools/queries/q1.1.sql
copy to tools/ssb-tools/ssb-flat-queries/q3.1.sql
index 4ef15e93ea..8df98222c4 100644
--- a/tools/ssb-tools/queries/q1.1.sql
+++ b/tools/ssb-tools/ssb-flat-queries/q3.1.sql
@@ -14,11 +14,16 @@
-- KIND, either express or implied. See the License for the
-- specific language governing permissions and limitations
-- under the License.
-
-SELECT SUM(lo_extendedprice*lo_discount) AS
-REVENUE
-FROM lineorder, date
-WHERE lo_orderdate = d_datekey
-AND d_year = 1993
-AND lo_discount BETWEEN 1 AND 3
-AND lo_quantity < 25;
+--Q3.1
+SELECT
+ C_NATION,
+ S_NATION, (LO_ORDERDATE DIV 10000) AS YEAR,
+ SUM(LO_REVENUE) AS revenue
+FROM lineorder_flat
+WHERE
+ C_REGION = 'ASIA'
+ AND S_REGION = 'ASIA'
+ AND LO_ORDERDATE >= 19920101
+ AND LO_ORDERDATE <= 19971231
+GROUP BY C_NATION, S_NATION, YEAR
+ORDER BY YEAR ASC, revenue DESC;
\ No newline at end of file
diff --git a/tools/ssb-tools/queries/q1.1.sql b/tools/ssb-tools/ssb-flat-queries/q3.2.sql
similarity index 71%
copy from tools/ssb-tools/queries/q1.1.sql
copy to tools/ssb-tools/ssb-flat-queries/q3.2.sql
index 4ef15e93ea..c588b5bbce 100644
--- a/tools/ssb-tools/queries/q1.1.sql
+++ b/tools/ssb-tools/ssb-flat-queries/q3.2.sql
@@ -14,11 +14,16 @@
-- KIND, either express or implied. See the License for the
-- specific language governing permissions and limitations
-- under the License.
-
-SELECT SUM(lo_extendedprice*lo_discount) AS
-REVENUE
-FROM lineorder, date
-WHERE lo_orderdate = d_datekey
-AND d_year = 1993
-AND lo_discount BETWEEN 1 AND 3
-AND lo_quantity < 25;
+--Q3.2
+SELECT
+ C_CITY,
+ S_CITY, (LO_ORDERDATE DIV 10000) AS YEAR,
+ SUM(LO_REVENUE) AS revenue
+FROM lineorder_flat
+WHERE
+ C_NATION = 'UNITED STATES'
+ AND S_NATION = 'UNITED STATES'
+ AND LO_ORDERDATE >= 19920101
+ AND LO_ORDERDATE <= 19971231
+GROUP BY C_CITY, S_CITY, YEAR
+ORDER BY YEAR ASC, revenue DESC;
\ No newline at end of file
diff --git a/tools/ssb-tools/queries/q1.1.sql b/tools/ssb-tools/ssb-flat-queries/q3.3.sql
similarity index 69%
copy from tools/ssb-tools/queries/q1.1.sql
copy to tools/ssb-tools/ssb-flat-queries/q3.3.sql
index 4ef15e93ea..9a099d1732 100644
--- a/tools/ssb-tools/queries/q1.1.sql
+++ b/tools/ssb-tools/ssb-flat-queries/q3.3.sql
@@ -14,11 +14,16 @@
-- KIND, either express or implied. See the License for the
-- specific language governing permissions and limitations
-- under the License.
-
-SELECT SUM(lo_extendedprice*lo_discount) AS
-REVENUE
-FROM lineorder, date
-WHERE lo_orderdate = d_datekey
-AND d_year = 1993
-AND lo_discount BETWEEN 1 AND 3
-AND lo_quantity < 25;
+--Q3.3
+SELECT
+ C_CITY,
+ S_CITY, (LO_ORDERDATE DIV 10000) AS YEAR,
+ SUM(LO_REVENUE) AS revenue
+FROM lineorder_flat
+WHERE
+ C_CITY IN ('UNITED KI1', 'UNITED KI5')
+ AND S_CITY IN ('UNITED KI1', 'UNITED KI5')
+ AND LO_ORDERDATE >= 19920101
+ AND LO_ORDERDATE <= 19971231
+GROUP BY C_CITY, S_CITY, YEAR
+ORDER BY YEAR ASC, revenue DESC;
\ No newline at end of file
diff --git a/tools/ssb-tools/queries/q1.1.sql b/tools/ssb-tools/ssb-flat-queries/q3.4.sql
similarity index 69%
copy from tools/ssb-tools/queries/q1.1.sql
copy to tools/ssb-tools/ssb-flat-queries/q3.4.sql
index 4ef15e93ea..6bd71b5891 100644
--- a/tools/ssb-tools/queries/q1.1.sql
+++ b/tools/ssb-tools/ssb-flat-queries/q3.4.sql
@@ -14,11 +14,16 @@
-- KIND, either express or implied. See the License for the
-- specific language governing permissions and limitations
-- under the License.
-
-SELECT SUM(lo_extendedprice*lo_discount) AS
-REVENUE
-FROM lineorder, date
-WHERE lo_orderdate = d_datekey
-AND d_year = 1993
-AND lo_discount BETWEEN 1 AND 3
-AND lo_quantity < 25;
+--Q3.4
+SELECT
+ C_CITY,
+ S_CITY, (LO_ORDERDATE DIV 10000) AS YEAR,
+ SUM(LO_REVENUE) AS revenue
+FROM lineorder_flat
+WHERE
+ C_CITY IN ('UNITED KI1', 'UNITED KI5')
+ AND S_CITY IN ('UNITED KI1', 'UNITED KI5')
+ AND LO_ORDERDATE >= 19971201
+ AND LO_ORDERDATE <= 19971231
+GROUP BY C_CITY, S_CITY, YEAR
+ORDER BY YEAR ASC, revenue DESC;
\ No newline at end of file
diff --git a/tools/ssb-tools/queries/q1.1.sql b/tools/ssb-tools/ssb-flat-queries/q4.1.sql
similarity index 73%
copy from tools/ssb-tools/queries/q1.1.sql
copy to tools/ssb-tools/ssb-flat-queries/q4.1.sql
index 4ef15e93ea..aedd0e047e 100644
--- a/tools/ssb-tools/queries/q1.1.sql
+++ b/tools/ssb-tools/ssb-flat-queries/q4.1.sql
@@ -14,11 +14,14 @@
-- KIND, either express or implied. See the License for the
-- specific language governing permissions and limitations
-- under the License.
-
-SELECT SUM(lo_extendedprice*lo_discount) AS
-REVENUE
-FROM lineorder, date
-WHERE lo_orderdate = d_datekey
-AND d_year = 1993
-AND lo_discount BETWEEN 1 AND 3
-AND lo_quantity < 25;
+--Q4.1
+SELECT (LO_ORDERDATE DIV 10000) 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 IN ('MFGR#1', 'MFGR#2')
+GROUP BY YEAR, C_NATION
+ORDER BY YEAR ASC, C_NATION ASC;
\ No newline at end of file
diff --git a/tools/ssb-tools/queries/q1.1.sql b/tools/ssb-tools/ssb-flat-queries/q4.2.sql
similarity index 66%
copy from tools/ssb-tools/queries/q1.1.sql
copy to tools/ssb-tools/ssb-flat-queries/q4.2.sql
index 4ef15e93ea..b9891ee408 100644
--- a/tools/ssb-tools/queries/q1.1.sql
+++ b/tools/ssb-tools/ssb-flat-queries/q4.2.sql
@@ -14,11 +14,20 @@
-- KIND, either express or implied. See the License for the
-- specific language governing permissions and limitations
-- under the License.
-
-SELECT SUM(lo_extendedprice*lo_discount) AS
-REVENUE
-FROM lineorder, date
-WHERE lo_orderdate = d_datekey
-AND d_year = 1993
-AND lo_discount BETWEEN 1 AND 3
-AND lo_quantity < 25;
+--Q4.2
+SELECT (LO_ORDERDATE DIV 10000) 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 LO_ORDERDATE >= 19970101
+ AND LO_ORDERDATE <= 19981231
+ AND P_MFGR IN ('MFGR#1', 'MFGR#2')
+GROUP BY YEAR, S_NATION, P_CATEGORY
+ORDER BY
+ YEAR ASC,
+ S_NATION ASC,
+ P_CATEGORY ASC;
\ No newline at end of file
diff --git a/tools/ssb-tools/queries/q1.1.sql b/tools/ssb-tools/ssb-flat-queries/q4.3.sql
similarity index 69%
copy from tools/ssb-tools/queries/q1.1.sql
copy to tools/ssb-tools/ssb-flat-queries/q4.3.sql
index 4ef15e93ea..6871023137 100644
--- a/tools/ssb-tools/queries/q1.1.sql
+++ b/tools/ssb-tools/ssb-flat-queries/q4.3.sql
@@ -14,11 +14,16 @@
-- KIND, either express or implied. See the License for the
-- specific language governing permissions and limitations
-- under the License.
-
-SELECT SUM(lo_extendedprice*lo_discount) AS
-REVENUE
-FROM lineorder, date
-WHERE lo_orderdate = d_datekey
-AND d_year = 1993
-AND lo_discount BETWEEN 1 AND 3
-AND lo_quantity < 25;
+--Q4.3
+SELECT (LO_ORDERDATE DIV 10000) AS YEAR,
+ S_CITY,
+ P_BRAND,
+ SUM(LO_REVENUE - LO_SUPPLYCOST) AS profit
+FROM lineorder_flat
+WHERE
+ S_NATION = 'UNITED STATES'
+ AND LO_ORDERDATE >= 19970101
+ AND LO_ORDERDATE <= 19981231
+ AND P_CATEGORY = 'MFGR#14'
+GROUP BY YEAR, S_CITY, P_BRAND
+ORDER BY YEAR ASC, S_CITY ASC, P_BRAND ASC;
\ No newline at end of file
diff --git a/tools/ssb-tools/queries/q1.1.sql b/tools/ssb-tools/ssb-queries/q1.1.sql
similarity index 80%
rename from tools/ssb-tools/queries/q1.1.sql
rename to tools/ssb-tools/ssb-queries/q1.1.sql
index 4ef15e93ea..d9496e0d28 100644
--- a/tools/ssb-tools/queries/q1.1.sql
+++ b/tools/ssb-tools/ssb-queries/q1.1.sql
@@ -14,11 +14,10 @@
-- KIND, either express or implied. See the License for the
-- specific language governing permissions and limitations
-- under the License.
-
-SELECT SUM(lo_extendedprice*lo_discount) AS
-REVENUE
-FROM lineorder, date
-WHERE lo_orderdate = d_datekey
-AND d_year = 1993
-AND lo_discount BETWEEN 1 AND 3
-AND lo_quantity < 25;
+SELECT SUM(lo_extendedprice * lo_discount) AS REVENUE
+FROM lineorder, date
+WHERE
+ lo_orderdate = d_datekey
+ AND d_year = 1993
+ AND lo_discount BETWEEN 1 AND 3
+ AND lo_quantity < 25;
\ No newline at end of file
diff --git a/tools/ssb-tools/queries/q1.2.sql b/tools/ssb-tools/ssb-queries/q1.2.sql
similarity index 78%
rename from tools/ssb-tools/queries/q1.2.sql
rename to tools/ssb-tools/ssb-queries/q1.2.sql
index 1b8442bd93..f86cb7e71c 100644
--- a/tools/ssb-tools/queries/q1.2.sql
+++ b/tools/ssb-tools/ssb-queries/q1.2.sql
@@ -14,11 +14,10 @@
-- KIND, either express or implied. See the License for the
-- specific language governing permissions and limitations
-- under the License.
-
-SELECT SUM(lo_extendedprice*lo_discount) AS
-REVENUE
-FROM lineorder, date
-WHERE lo_orderdate = d_datekey
-AND d_yearmonth = 'Jan1994'
-AND lo_discount BETWEEN 4 AND 6
-AND lo_quantity BETWEEN 26 AND 35;
+SELECT SUM(lo_extendedprice * lo_discount) AS REVENUE
+FROM lineorder, date
+WHERE
+ lo_orderdate = d_datekey
+ AND d_yearmonth = 'Jan1994'
+ AND lo_discount BETWEEN 4 AND 6
+ AND lo_quantity BETWEEN 26 AND 35;
\ No newline at end of file
diff --git a/tools/ssb-tools/queries/q1.3.sql b/tools/ssb-tools/ssb-queries/q1.3.sql
similarity index 77%
rename from tools/ssb-tools/queries/q1.3.sql
rename to tools/ssb-tools/ssb-queries/q1.3.sql
index ed6e51b1cf..94c191a01b 100644
--- a/tools/ssb-tools/queries/q1.3.sql
+++ b/tools/ssb-tools/ssb-queries/q1.3.sql
@@ -14,12 +14,12 @@
-- KIND, either express or implied. See the License for the
-- specific language governing permissions and limitations
-- under the License.
-
-SELECT SUM(lo_extendedprice*lo_discount) AS
-REVENUE
-FROM lineorder, date
-WHERE lo_orderdate = d_datekey
-AND d_weeknuminyear= 6
-AND d_year = 1994
-AND lo_discount BETWEEN 5 AND 7
-AND lo_quantity BETWEEN 26 AND 35;
+SELECT
+ SUM(lo_extendedprice * lo_discount) AS REVENUE
+FROM lineorder, date
+WHERE
+ lo_orderdate = d_datekey
+ AND d_weeknuminyear = 6
+ AND d_year = 1994
+ AND lo_discount BETWEEN 5 AND 7
+ AND lo_quantity BETWEEN 26 AND 35;
\ No newline at end of file
diff --git a/tools/ssb-tools/queries/q2.1.sql b/tools/ssb-tools/ssb-queries/q2.1.sql
similarity index 83%
rename from tools/ssb-tools/queries/q2.1.sql
rename to tools/ssb-tools/ssb-queries/q2.1.sql
index e1a1f52d18..0089ef61bb 100644
--- a/tools/ssb-tools/queries/q2.1.sql
+++ b/tools/ssb-tools/ssb-queries/q2.1.sql
@@ -14,13 +14,13 @@
-- KIND, either express or implied. See the License for the
-- specific language governing permissions and limitations
-- under the License.
-
SELECT SUM(lo_revenue), d_year, p_brand
FROM lineorder, date, part, supplier
-WHERE lo_orderdate = d_datekey
-AND lo_partkey = p_partkey
-AND lo_suppkey = s_suppkey
-AND p_category = 'MFGR#12'
-AND s_region = 'AMERICA'
+WHERE
+ lo_orderdate = d_datekey
+ AND lo_partkey = p_partkey
+ AND lo_suppkey = s_suppkey
+ AND p_category = 'MFGR#12'
+ AND s_region = 'AMERICA'
GROUP BY d_year, p_brand
-ORDER BY d_year, p_brand;
+ORDER BY d_year, p_brand;
\ No newline at end of file
diff --git a/tools/ssb-tools/queries/q2.2.sql b/tools/ssb-tools/ssb-queries/q2.2.sql
similarity index 81%
rename from tools/ssb-tools/queries/q2.2.sql
rename to tools/ssb-tools/ssb-queries/q2.2.sql
index 3db6170119..95c5c67f48 100644
--- a/tools/ssb-tools/queries/q2.2.sql
+++ b/tools/ssb-tools/ssb-queries/q2.2.sql
@@ -14,14 +14,13 @@
-- KIND, either express or implied. See the License for the
-- specific language governing permissions and limitations
-- under the License.
-
SELECT SUM(lo_revenue), d_year, p_brand
FROM lineorder, date, part, supplier
-WHERE lo_orderdate = d_datekey
-AND lo_partkey = p_partkey
-AND lo_suppkey = s_suppkey
-AND p_brand BETWEEN 'MFGR#2221'
-AND 'MFGR#2228'
-AND s_region = 'ASIA'
+WHERE
+ lo_orderdate = d_datekey
+ AND lo_partkey = p_partkey
+ AND lo_suppkey = s_suppkey
+ AND p_brand BETWEEN 'MFGR#2221' AND 'MFGR#2228'
+ AND s_region = 'ASIA'
GROUP BY d_year, p_brand
-ORDER BY d_year, p_brand;
+ORDER BY d_year, p_brand;
\ No newline at end of file
diff --git a/tools/ssb-tools/queries/q2.3.sql b/tools/ssb-tools/ssb-queries/q2.3.sql
similarity index 83%
rename from tools/ssb-tools/queries/q2.3.sql
rename to tools/ssb-tools/ssb-queries/q2.3.sql
index b70ca90666..b747ee12e5 100644
--- a/tools/ssb-tools/queries/q2.3.sql
+++ b/tools/ssb-tools/ssb-queries/q2.3.sql
@@ -14,13 +14,13 @@
-- KIND, either express or implied. See the License for the
-- specific language governing permissions and limitations
-- under the License.
-
SELECT SUM(lo_revenue), d_year, p_brand
FROM lineorder, date, part, supplier
-WHERE lo_orderdate = d_datekey
-AND lo_partkey = p_partkey
-AND lo_suppkey = s_suppkey
-AND p_brand = 'MFGR#2239'
-AND s_region = 'EUROPE'
+WHERE
+ lo_orderdate = d_datekey
+ AND lo_partkey = p_partkey
+ AND lo_suppkey = s_suppkey
+ AND p_brand = 'MFGR#2239'
+ AND s_region = 'EUROPE'
GROUP BY d_year, p_brand
-ORDER BY d_year, p_brand;
+ORDER BY d_year, p_brand;
\ No newline at end of file
diff --git a/tools/ssb-tools/queries/q3.1.sql b/tools/ssb-tools/ssb-queries/q3.1.sql
similarity index 74%
rename from tools/ssb-tools/queries/q3.1.sql
rename to tools/ssb-tools/ssb-queries/q3.1.sql
index 70f17d789b..ae8b6ea6b3 100644
--- a/tools/ssb-tools/queries/q3.1.sql
+++ b/tools/ssb-tools/ssb-queries/q3.1.sql
@@ -14,15 +14,19 @@
-- KIND, either express or implied. See the License for the
-- specific language governing permissions and limitations
-- under the License.
-
-SELECT c_nation, s_nation, d_year,
-SUM(lo_revenue) AS REVENUE
+SELECT
+ c_nation,
+ s_nation,
+ d_year,
+ SUM(lo_revenue) AS REVENUE
FROM customer, lineorder, supplier, date
-WHERE lo_custkey = c_custkey
-AND lo_suppkey = s_suppkey
-AND lo_orderdate = d_datekey
-AND c_region = 'ASIA'
-AND s_region = 'ASIA'
-AND d_year >= 1992 AND d_year <= 1997
+WHERE
+ lo_custkey = c_custkey
+ AND lo_suppkey = s_suppkey
+ AND lo_orderdate = d_datekey
+ AND 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;
+ORDER BY d_year ASC, REVENUE DESC;
\ No newline at end of file
diff --git a/tools/ssb-tools/queries/q3.2.sql b/tools/ssb-tools/ssb-queries/q3.2.sql
similarity index 73%
rename from tools/ssb-tools/queries/q3.2.sql
rename to tools/ssb-tools/ssb-queries/q3.2.sql
index a416fbea8b..2a8ed7ab0a 100644
--- a/tools/ssb-tools/queries/q3.2.sql
+++ b/tools/ssb-tools/ssb-queries/q3.2.sql
@@ -14,15 +14,19 @@
-- KIND, either express or implied. See the License for the
-- specific language governing permissions and limitations
-- under the License.
-
-SELECT c_city, s_city, d_year, sum(lo_revenue)
-AS REVENUE
+SELECT
+ c_city,
+ s_city,
+ d_year,
+ SUM(lo_revenue) AS REVENUE
FROM customer, lineorder, supplier, date
-WHERE lo_custkey = c_custkey
-AND lo_suppkey = s_suppkey
-AND lo_orderdate = d_datekey
-AND c_nation = 'UNITED STATES'
-AND s_nation = 'UNITED STATES'
-AND d_year >= 1992 AND d_year <= 1997
+WHERE
+ lo_custkey = c_custkey
+ AND lo_suppkey = s_suppkey
+ AND lo_orderdate = d_datekey
+ AND 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;
+ORDER BY d_year ASC, REVENUE DESC;
\ No newline at end of file
diff --git a/tools/ssb-tools/queries/q3.3.sql b/tools/ssb-tools/ssb-queries/q3.3.sql
similarity index 68%
rename from tools/ssb-tools/queries/q3.3.sql
rename to tools/ssb-tools/ssb-queries/q3.3.sql
index 98e29b72e7..5a526e9eaa 100644
--- a/tools/ssb-tools/queries/q3.3.sql
+++ b/tools/ssb-tools/ssb-queries/q3.3.sql
@@ -14,17 +14,25 @@
-- KIND, either express or implied. See the License for the
-- specific language governing permissions and limitations
-- under the License.
-
-SELECT c_city, s_city, d_year, SUM(lo_revenue)
-AS REVENUE
+SELECT
+ c_city,
+ s_city,
+ d_year,
+ SUM(lo_revenue) AS REVENUE
FROM customer, lineorder, supplier, date
-WHERE lo_custkey = c_custkey
-AND lo_suppkey = s_suppkey
-AND lo_orderdate = d_datekey
-AND (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
+WHERE
+ lo_custkey = c_custkey
+ AND lo_suppkey = s_suppkey
+ AND lo_orderdate = d_datekey
+ AND (
+ 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;
+ORDER BY d_year ASC, REVENUE DESC;
\ No newline at end of file
diff --git a/tools/ssb-tools/queries/q3.4.sql b/tools/ssb-tools/ssb-queries/q3.4.sql
similarity index 68%
rename from tools/ssb-tools/queries/q3.4.sql
rename to tools/ssb-tools/ssb-queries/q3.4.sql
index 65fe992ca4..fe205af5c2 100644
--- a/tools/ssb-tools/queries/q3.4.sql
+++ b/tools/ssb-tools/ssb-queries/q3.4.sql
@@ -14,17 +14,24 @@
-- KIND, either express or implied. See the License for the
-- specific language governing permissions and limitations
-- under the License.
-
-SELECT c_city, s_city, d_year, SUM(lo_revenue)
-AS REVENUE
+SELECT
+ c_city,
+ s_city,
+ d_year,
+ SUM(lo_revenue) AS REVENUE
FROM customer, lineorder, supplier, date
-WHERE lo_custkey = c_custkey
-AND lo_suppkey = s_suppkey
-AND lo_orderdate = d_datekey
-AND (c_city='UNITED KI1'
-OR c_city='UNITED KI5')
-AND (s_city='UNITED KI1'
-OR s_city='UNITED KI5')
-AND d_yearmonth = 'Dec1997'
+WHERE
+ lo_custkey = c_custkey
+ AND lo_suppkey = s_suppkey
+ AND lo_orderdate = d_datekey
+ AND (
+ 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;
+ORDER BY d_year ASC, REVENUE DESC;
\ No newline at end of file
diff --git a/tools/ssb-tools/queries/q4.1.sql b/tools/ssb-tools/ssb-queries/q4.1.sql
similarity index 70%
rename from tools/ssb-tools/queries/q4.1.sql
rename to tools/ssb-tools/ssb-queries/q4.1.sql
index bdcd730bf9..beffb53092 100644
--- a/tools/ssb-tools/queries/q4.1.sql
+++ b/tools/ssb-tools/ssb-queries/q4.1.sql
@@ -14,17 +14,21 @@
-- KIND, either express or implied. See the License for the
-- specific language governing permissions and limitations
-- under the License.
-
-SELECT d_year, c_nation,
-SUM(lo_revenue - lo_supplycost) AS PROFIT
+SELECT
+ d_year,
+ c_nation,
+ SUM(lo_revenue - lo_supplycost) AS PROFIT
FROM date, customer, supplier, part, lineorder
-WHERE lo_custkey = c_custkey
-AND lo_suppkey = s_suppkey
-AND lo_partkey = p_partkey
-AND lo_orderdate = d_datekey
-AND c_region = 'AMERICA'
-AND s_region = 'AMERICA'
-AND (p_mfgr = 'MFGR#1'
-OR p_mfgr = 'MFGR#2')
+WHERE
+ lo_custkey = c_custkey
+ AND lo_suppkey = s_suppkey
+ AND lo_partkey = p_partkey
+ AND lo_orderdate = d_datekey
+ AND 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;
+ORDER BY d_year, c_nation;
\ No newline at end of file
diff --git a/tools/ssb-tools/queries/q4.2.sql b/tools/ssb-tools/ssb-queries/q4.2.sql
similarity index 66%
rename from tools/ssb-tools/queries/q4.2.sql
rename to tools/ssb-tools/ssb-queries/q4.2.sql
index 24c82cf682..c763b7d71c 100644
--- a/tools/ssb-tools/queries/q4.2.sql
+++ b/tools/ssb-tools/ssb-queries/q4.2.sql
@@ -14,18 +14,26 @@
-- KIND, either express or implied. See the License for the
-- specific language governing permissions and limitations
-- under the License.
-
-SELECT d_year, s_nation, p_category,
-SUM(lo_revenue - lo_supplycost) AS PROFIT
+SELECT
+ d_year,
+ s_nation,
+ p_category,
+ SUM(lo_revenue - lo_supplycost) AS PROFIT
FROM date, customer, supplier, part, lineorder
-WHERE lo_custkey = c_custkey
-AND lo_suppkey = s_suppkey
-AND lo_partkey = p_partkey
-AND lo_orderdate = d_datekey
-AND 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')
+WHERE
+ lo_custkey = c_custkey
+ AND lo_suppkey = s_suppkey
+ AND lo_partkey = p_partkey
+ AND lo_orderdate = d_datekey
+ AND 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;
+ORDER BY d_year, s_nation, p_category;
\ No newline at end of file
diff --git a/tools/ssb-tools/queries/q4.3.sql b/tools/ssb-tools/ssb-queries/q4.3.sql
similarity index 69%
rename from tools/ssb-tools/queries/q4.3.sql
rename to tools/ssb-tools/ssb-queries/q4.3.sql
index 0dcc08bd26..92859cdb92 100644
--- a/tools/ssb-tools/queries/q4.3.sql
+++ b/tools/ssb-tools/ssb-queries/q4.3.sql
@@ -14,16 +14,22 @@
-- KIND, either express or implied. See the License for the
-- specific language governing permissions and limitations
-- under the License.
-
-SELECT d_year, s_city, p_brand,
-SUM(lo_revenue - lo_supplycost) AS PROFIT
+SELECT
+ d_year,
+ s_city,
+ p_brand,
+ SUM(lo_revenue - lo_supplycost) AS PROFIT
FROM date, customer, supplier, part, lineorder
-WHERE lo_custkey = c_custkey
-AND lo_suppkey = s_suppkey
-AND lo_partkey = p_partkey
-AND lo_orderdate = d_datekey
-AND s_nation = 'UNITED STATES'
-AND (d_year = 1997 OR d_year = 1998)
-AND p_category = 'MFGR#14'
+WHERE
+ lo_custkey = c_custkey
+ AND lo_suppkey = s_suppkey
+ AND lo_partkey = p_partkey
+ AND lo_orderdate = d_datekey
+ AND 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;
+ORDER BY d_year, s_city, p_brand;
\ No newline at end of file
diff --git a/tools/tpch-tools/build-tpch-dbgen.sh b/tools/tpch-tools/build-tpch-dbgen.sh
index 9c2c63702c..76da1ba890 100755
--- a/tools/tpch-tools/build-tpch-dbgen.sh
+++ b/tools/tpch-tools/build-tpch-dbgen.sh
@@ -51,7 +51,7 @@ if [[ -d $TPCH_DBGEN_DIR ]]; then
echo "If you want to download TPC-H_Tools_v3.0.0 again, please delete this dir first."
else
wget "https://tools-chengdu.oss-cn-chengdu.aliyuncs.com/TPC-H_Tools_v3.0.0.zip"
-
+
unzip TPC-H_Tools_v3.0.0.zip -d $CURDIR/
fi
@@ -66,7 +66,7 @@ echo '
#define SET_ROWCOUNT "limit %d;\n"
#define SET_DBASE "use %s;\n"
#endif
-' >> tpcd.h
+' >>tpcd.h
# modify makefile
cp makefile.suite makefile
@@ -76,7 +76,7 @@ sed -i 's/^MACHINE =/MACHINE = LINUX/g' makefile
sed -i 's/^WORKLOAD =/WORKLOAD = TPCH/g' makefile
# compile tpch-dbgen
-make > /dev/null
+make >/dev/null
cd -
# check
diff --git a/tools/tpch-tools/create-tpch-tables.sh b/tools/tpch-tools/create-tpch-tables.sh
index 3c3f2dccbb..311d5725f0 100755
--- a/tools/tpch-tools/create-tpch-tables.sh
+++ b/tools/tpch-tools/create-tpch-tables.sh
@@ -95,5 +95,5 @@ echo "DB: $DB"
mysql -h$FE_HOST -u$USER -P$FE_QUERY_PORT -e "CREATE DATABASE IF NOT EXISTS $DB"
-echo $CURDIR/create-tpch-tables.sql
+echo "Run SQLs from $CURDIR/create-tpch-tables.sql"
mysql -h$FE_HOST -u$USER -P$FE_QUERY_PORT -D$DB <$CURDIR/create-tpch-tables.sql
diff --git a/tools/tpch-tools/doris-cluster.conf b/tools/tpch-tools/doris-cluster.conf
index 5d76264104..7367b144b2 100644
--- a/tools/tpch-tools/doris-cluster.conf
+++ b/tools/tpch-tools/doris-cluster.conf
@@ -16,7 +16,7 @@
# under the License.
# Any of FE host
-export FE_HOST='172.20.194.235'
+export FE_HOST='127.0.0.1'
# http_port in fe.conf
export FE_HTTP_PORT=8030
# query_port in fe.conf
diff --git a/tools/tpch-tools/load-tpch-data.sh b/tools/tpch-tools/load-tpch-data.sh
index f04e21bb9c..572bfb859c 100755
--- a/tools/tpch-tools/load-tpch-data.sh
+++ b/tools/tpch-tools/load-tpch-data.sh
@@ -48,7 +48,7 @@ Usage: $0 <options>
OPTS=$(getopt \
-n $0 \
-o '' \
- -o 'c:' \
+ -o 'hc:' \
-- "$@")
eval set -- "$OPTS"
diff --git a/tools/tpch-tools/run-tpch-queries.sh b/tools/tpch-tools/run-tpch-queries.sh
index d5a5bb7282..457be4447f 100755
--- a/tools/tpch-tools/run-tpch-queries.sh
+++ b/tools/tpch-tools/run-tpch-queries.sh
@@ -17,7 +17,7 @@
# under the License.
##############################################################
-# This script is used to create TPC-H tables
+# This script is used to run TPC-H queries
##############################################################
set -eo pipefail
@@ -43,6 +43,7 @@ Usage: $0
OPTS=$(getopt \
-n $0 \
-o '' \
+ -o 'h' \
-- "$@")
eval set -- "$OPTS"
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org