This is an automated email from the ASF dual-hosted git repository.
liaoxin pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 40624cd3f08 [Chore](tools) update ssb tools (#32308)
40624cd3f08 is described below
commit 40624cd3f083ab254e71fe992ca22627ea16efd2
Author: feifeifeimoon <[email protected]>
AuthorDate: Mon Mar 18 20:02:23 2024 +0800
[Chore](tools) update ssb tools (#32308)
---
tools/ssb-tools/README.md | 2 +-
tools/ssb-tools/bin/create-ssb-tables.sh | 41 ++++++--
tools/ssb-tools/bin/load-ssb-data.sh | 7 ++
tools/ssb-tools/bin/run-ssb-flat-queries.sh | 97 +++++++++---------
tools/ssb-tools/bin/run-ssb-queries.sh | 112 +++++++++------------
...at-table.sql => create-ssb-flat-tables-sf1.sql} | 12 +--
...-table.sql => create-ssb-flat-tables-sf100.sql} | 12 +--
...table.sql => create-ssb-flat-tables-sf1000.sql} | 16 +--
...te-ssb-tables.sql => create-ssb-tables-sf1.sql} | 36 +++----
...-ssb-tables.sql => create-ssb-tables-sf100.sql} | 36 +++----
...ssb-tables.sql => create-ssb-tables-sf1000.sql} | 40 +++-----
11 files changed, 201 insertions(+), 210 deletions(-)
diff --git a/tools/ssb-tools/README.md b/tools/ssb-tools/README.md
index 6d8e85e97d1..8f857057f99 100644
--- a/tools/ssb-tools/README.md
+++ b/tools/ssb-tools/README.md
@@ -28,7 +28,7 @@ under the License.
### 2. generate ssb data. use -h for more infomations.
./bin/gen-ssb-data.sh -s 1
### 3. create ssb tables. modify `conf/doris-cluster.conf` to specify Doris
cluster info, then run script below.
- ./bin/create-ssb-tables.sh
+ ./bin/create-ssb-tables.sh -s 1
### 4. load ssb data. use -h for help.
./bin/load-ssb-data.sh
### 5. run ssb queries.
diff --git a/tools/ssb-tools/bin/create-ssb-tables.sh
b/tools/ssb-tools/bin/create-ssb-tables.sh
index eee4d59cc7a..65fb3bbcc80 100755
--- a/tools/ssb-tools/bin/create-ssb-tables.sh
+++ b/tools/ssb-tools/bin/create-ssb-tables.sh
@@ -29,8 +29,6 @@ ROOT=$(
)
CURDIR="${ROOT}"
-SSB_DDL="${CURDIR}/../ddl/create-ssb-tables.sql"
-SSB_FLAT_DDL="${CURDIR}/../ddl/create-ssb-flat-table.sql"
usage() {
echo "
@@ -44,11 +42,12 @@ Usage: $0
OPTS=$(getopt \
-n "$0" \
-o '' \
- -o 'h' \
+ -o 'hs:' \
-- "$@")
eval set -- "${OPTS}"
HELP=0
+SCALE_FACTOR=100
if [[ $# == 0 ]]; then
usage
@@ -60,6 +59,10 @@ while true; do
HELP=1
shift
;;
+ -s)
+ SCALE_FACTOR=$2
+ shift 2
+ ;;
--)
shift
break
@@ -75,6 +78,11 @@ if [[ "${HELP}" -eq 1 ]]; then
usage
fi
+if [[ ${SCALE_FACTOR} -ne 1 ]] && [[ ${SCALE_FACTOR} -ne 100 ]] && [[
${SCALE_FACTOR} -ne 1000 ]]; then
+ echo "${SCALE_FACTOR} scale is not supported"
+ exit 1
+fi
+
check_prerequest() {
local CMD=$1
local NAME=$2
@@ -93,13 +101,30 @@ echo "FE_HOST: ${FE_HOST}"
echo "FE_QUERY_PORT: ${FE_QUERY_PORT}"
echo "USER: ${USER}"
echo "DB: ${DB}"
+echo "SF: ${SCALE_FACTOR}"
mysql -h"${FE_HOST}" -u"${USER}" -P"${FE_QUERY_PORT}" -e "CREATE DATABASE IF
NOT EXISTS ${DB}"
-echo "Run DDL from ${SSB_DDL}"
-mysql -h"${FE_HOST}" -u"${USER}" -P"${FE_QUERY_PORT}" -D"${DB}" <"${SSB_DDL}"
-
-echo "Run DDL from ${SSB_FLAT_DDL}"
-mysql -h"${FE_HOST}" -u"${USER}" -P"${FE_QUERY_PORT}" -D"${DB}"
<"${SSB_FLAT_DDL}"
+if [[ ${SCALE_FACTOR} -eq 1 ]]; then
+ echo "Run SQLs from ${CURDIR}/../ddl/create-ssb-tables-sf1.sql"
+ mysql -h"${FE_HOST}" -u"${USER}" -P"${FE_QUERY_PORT}" -D"${DB}"
<"${CURDIR}"/../ddl/create-ssb-tables-sf1.sql
+
+ echo "Run SQLs from ${CURDIR}/../ddl/create-ssb-flat-tables-sf1.sql"
+ mysql -h"${FE_HOST}" -u"${USER}" -P"${FE_QUERY_PORT}" -D"${DB}"
<"${CURDIR}"/../ddl/create-ssb-flat-tables-sf1.sql
+elif [[ ${SCALE_FACTOR} -eq 100 ]]; then
+ echo "Run SQLs from ${CURDIR}/../ddl/create-ssb-tables-sf100.sql"
+ mysql -h"${FE_HOST}" -u"${USER}" -P"${FE_QUERY_PORT}" -D"${DB}"
<"${CURDIR}"/../ddl/create-ssb-tables-sf100.sql
+
+ echo "Run SQLs from ${CURDIR}/../ddl/create-ssb-flat-tables-sf100.sql"
+ mysql -h"${FE_HOST}" -u"${USER}" -P"${FE_QUERY_PORT}" -D"${DB}"
<"${CURDIR}"/../ddl/create-ssb-flat-tables-sf100.sql
+elif [[ ${SCALE_FACTOR} -eq 1000 ]]; then
+ echo "Run SQLs from ${CURDIR}/../ddl/create-ssb-tables-sf1000.sql"
+ mysql -h"${FE_HOST}" -u"${USER}" -P"${FE_QUERY_PORT}" -D"${DB}"
<"${CURDIR}"/../ddl/create-ssb-tables-sf1000.sql
+
+ echo "Run SQLs from ${CURDIR}/../ddl/create-ssb-flat-tables-sf1000.sql"
+ mysql -h"${FE_HOST}" -u"${USER}" -P"${FE_QUERY_PORT}" -D"${DB}"
<"${CURDIR}"/../ddl/create-ssb-flat-tables-sf1000.sql
+else
+ echo "${SCALE_FACTOR} scale is NOT supported currently"
+fi
echo "ssb tables has been created"
diff --git a/tools/ssb-tools/bin/load-ssb-data.sh
b/tools/ssb-tools/bin/load-ssb-data.sh
index d30720d7f1e..71ec01ac431 100755
--- a/tools/ssb-tools/bin/load-ssb-data.sh
+++ b/tools/ssb-tools/bin/load-ssb-data.sh
@@ -397,3 +397,10 @@ end_time=$(date +%s)
echo "End time: $(date)"
echo "Finish load ssb data, Time taken: $((end_time - start_time)) seconds"
+
+start=$(date +%s)
+run_sql "analyze database ${DB} with full with sync;"
+end=$(date +%s)
+totalTime=$((end - start))
+echo "analyze database ${DB} with full with sync total time: ${totalTime} s"
+echo '============================================'
diff --git a/tools/ssb-tools/bin/run-ssb-flat-queries.sh
b/tools/ssb-tools/bin/run-ssb-flat-queries.sh
index 5b82c56ab85..ab57bccc728 100755
--- a/tools/ssb-tools/bin/run-ssb-flat-queries.sh
+++ b/tools/ssb-tools/bin/run-ssb-flat-queries.sh
@@ -83,73 +83,72 @@ check_prerequest() {
fi
}
-check_prerequest "mysqlslap --version" "mysqlslap"
check_prerequest "mysql --version" "mysql"
-check_prerequest "bc --version" "bc"
source "${CURDIR}/../conf/doris-cluster.conf"
-export MYSQL_PWD=${PASSWORD}
+export MYSQL_PWD=${PASSWORD:-}
-echo "FE_HOST: ${FE_HOST}"
-echo "FE_QUERY_PORT: ${FE_QUERY_PORT}"
-echo "USER: ${USER}"
-echo "DB: ${DB}"
+echo "FE_HOST: ${FE_HOST:='127.0.0.1'}"
+echo "FE_QUERY_PORT: ${FE_QUERY_PORT:='9030'}"
+echo "USER: ${USER:='root'}"
+echo "DB: ${DB:='ssb'}"
run_sql() {
echo "$@"
mysql -h"${FE_HOST}" -P"${FE_QUERY_PORT}" -u"${USER}" -D"${DB}" -e "$@"
}
-echo '============================================'
-echo "optimize some session variables before run, and then restore it after
run."
-origin_parallel_fragment_exec_instance_num=$(
- set -e
- run_sql 'select @@parallel_fragment_exec_instance_num;' | sed -n '3p'
-)
-origin_exec_mem_limit=$(
- set -e
- run_sql 'select @@exec_mem_limit;' | sed -n '3p'
-)
-origin_batch_size=$(
- set -e
- run_sql 'select @@batch_size;' | sed -n '3p'
-)
-run_sql "set global parallel_fragment_exec_instance_num=8;"
-run_sql "set global exec_mem_limit=8G;"
-run_sql "set global batch_size=4096;"
-run_sql "set global query_timeout=900;"
echo '============================================'
run_sql "show variables;"
echo '============================================'
run_sql "show table status;"
echo '============================================'
-start=$(date +%s)
-run_sql "analyze table lineorder_flat with sync;"
-end=$(date +%s)
-totalTime=$((end - start))
-echo "analyze database ${DB} with sync total time: ${totalTime} s"
-echo '============================================'
-sum=0
-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
- # Each query is executed 3 times and takes the min time
- res1=$(mysql -vvv -h"${FE_HOST}" -u"${USER}" -P"${FE_QUERY_PORT}"
-D"${DB}" -e "$(cat "${QUERIES_DIR}"/q"${i}".sql)" | perl -nle 'print $1 if
/\((\d+\.\d+)+ sec\)/' || :)
- res2=$(mysql -vvv -h"${FE_HOST}" -u"${USER}" -P"${FE_QUERY_PORT}"
-D"${DB}" -e "$(cat "${QUERIES_DIR}"/q"${i}".sql)" | perl -nle 'print $1 if
/\((\d+\.\d+)+ sec\)/' || :)
- res3=$(mysql -vvv -h"${FE_HOST}" -u"${USER}" -P"${FE_QUERY_PORT}"
-D"${DB}" -e "$(cat "${QUERIES_DIR}"/q"${i}".sql)" | perl -nle 'print $1 if
/\((\d+\.\d+)+ sec\)/' || :)
+RESULT_DIR="${CURDIR}/result"
+if [[ -d "${RESULT_DIR}" ]]; then
+ rm -r "${RESULT_DIR}"
+fi
+mkdir -p "${RESULT_DIR}"
+touch result.csv
- min_value=$(echo "${res1} ${res2} ${res3}" | tr ' ' '\n' | sort -n | head
-n 1)
- echo -e "q${i}:\t${res1}\t${res2}\t${res3}\tfast:${min_value}"
+cold_run_sum=0
+best_hot_run_sum=0
- cost=$(echo "${min_value}" | cut -d' ' -f1)
- sum=$(echo "${sum} + ${cost}" | bc)
+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
+ cold=0
+ hot1=0
+ hot2=0
+ echo -ne "q${i}\t" | tee -a result.csv
+ start=$(date +%s%3N)
+ mysql -h"${FE_HOST}" -u "${USER}" -P"${FE_QUERY_PORT}" -D"${DB}"
--comments <"${QUERIES_DIR}"/q"${i}".sql >"${RESULT_DIR}"/result"${i}".out
2>"${RESULT_DIR}"/result"${i}".log
+ end=$(date +%s%3N)
+ cold=$((end - start))
+ echo -ne "${cold}\t" | tee -a result.csv
+
+ start=$(date +%s%3N)
+ mysql -h"${FE_HOST}" -u "${USER}" -P"${FE_QUERY_PORT}" -D"${DB}"
--comments <"${QUERIES_DIR}"/q"${i}".sql >"${RESULT_DIR}"/result"${i}".out
2>"${RESULT_DIR}"/result"${i}".log
+ end=$(date +%s%3N)
+ hot1=$((end - start))
+ echo -ne "${hot1}\t" | tee -a result.csv
+
+ start=$(date +%s%3N)
+ mysql -h"${FE_HOST}" -u "${USER}" -P"${FE_QUERY_PORT}" -D"${DB}"
--comments <"${QUERIES_DIR}"/q"${i}".sql >"${RESULT_DIR}"/result"${i}".out
2>"${RESULT_DIR}"/result"${i}".log
+ end=$(date +%s%3N)
+ hot2=$((end - start))
+ echo -ne "${hot2}\t" | tee -a result.csv
+
+ cold_run_sum=$((cold_run_sum + cold))
+ if [[ ${hot1} -lt ${hot2} ]]; then
+ best_hot_run_sum=$((best_hot_run_sum + hot1))
+ echo -ne "${hot1}" | tee -a result.csv
+ echo "" | tee -a result.csv
+ else
+ best_hot_run_sum=$((best_hot_run_sum + hot2))
+ echo -ne "${hot2}" | tee -a result.csv
+ echo "" | tee -a result.csv
+ fi
done
-echo "total time: ${sum} seconds"
-
-echo '============================================'
-echo "restore session variables"
-run_sql "set global
parallel_fragment_exec_instance_num=${origin_parallel_fragment_exec_instance_num};"
-run_sql "set global exec_mem_limit=${origin_exec_mem_limit};"
-run_sql "set global batch_size=${origin_batch_size};"
-echo '============================================'
+echo "Total cold run time: ${cold_run_sum} ms"
+echo "Total hot run time: ${best_hot_run_sum} ms"
echo 'Finish ssb-flat queries.'
diff --git a/tools/ssb-tools/bin/run-ssb-queries.sh
b/tools/ssb-tools/bin/run-ssb-queries.sh
index 1394d8e3ec4..58cfb5ef7be 100755
--- a/tools/ssb-tools/bin/run-ssb-queries.sh
+++ b/tools/ssb-tools/bin/run-ssb-queries.sh
@@ -83,88 +83,72 @@ check_prerequest() {
fi
}
-check_prerequest "mysqlslap --version" "mysql slap"
check_prerequest "mysql --version" "mysql"
-check_prerequest "bc --version" "bc"
source "${CURDIR}/../conf/doris-cluster.conf"
-export MYSQL_PWD=${PASSWORD}
+export MYSQL_PWD=${PASSWORD:-}
-echo "FE_HOST: ${FE_HOST}"
-echo "FE_QUERY_PORT: ${FE_QUERY_PORT}"
-echo "USER: ${USER}"
-echo "DB: ${DB}"
+echo "FE_HOST: ${FE_HOST:='127.0.0.1'}"
+echo "FE_QUERY_PORT: ${FE_QUERY_PORT:='9030'}"
+echo "USER: ${USER:='root'}"
+echo "DB: ${DB:='ssb'}"
run_sql() {
echo "$@"
mysql -h"${FE_HOST}" -P"${FE_QUERY_PORT}" -u"${USER}" -D"${DB}" -e "$@"
}
-echo '============================================'
-echo "optimize some session variables before run, and then restore it after
run."
-origin_parallel_fragment_exec_instance_num=$(
- set -e
- run_sql 'select @@parallel_fragment_exec_instance_num;' | sed -n '3p'
-)
-origin_exec_mem_limit=$(
- set -e
- run_sql 'select @@exec_mem_limit;' | sed -n '3p'
-)
-origin_batch_size=$(
- set -e
- run_sql 'select @@batch_size;' | sed -n '3p'
-)
-origin_enable_projection=$(
- set -e
- run_sql 'select @@enable_projection;' | sed -n '3p'
-)
-origin_runtime_filter_mode=$(
- set -e
- run_sql 'select @@runtime_filter_mode;' | sed -n '3p'
-)
-run_sql "set global parallel_fragment_exec_instance_num=8;"
-run_sql "set global exec_mem_limit=48G;"
-run_sql "set global batch_size=4096;"
-run_sql "set global enable_projection=true;"
-run_sql "set global runtime_filter_mode=global;"
echo '============================================'
run_sql "show variables;"
echo '============================================'
run_sql "show table status;"
echo '============================================'
-start=$(date +%s)
-run_sql "analyze table part with sync;"
-run_sql "analyze table customer with sync;"
-run_sql "analyze table supplier with sync;"
-run_sql "analyze table dates with sync;"
-run_sql "analyze table lineorder with sync;"
-end=$(date +%s)
-totalTime=$((end - start))
-echo "analyze database ${DB} with sync total time: ${totalTime} s"
-echo '============================================'
-sum=0
-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
- # Each query is executed 3 times and takes the min time
- res1=$(mysql -vvv -h"${FE_HOST}" -u"${USER}" -P"${FE_QUERY_PORT}"
-D"${DB}" -e "$(cat "${QUERIES_DIR}"/q"${i}".sql)" | perl -nle 'print $1 if
/\((\d+\.\d+)+ sec\)/' || :)
- res2=$(mysql -vvv -h"${FE_HOST}" -u"${USER}" -P"${FE_QUERY_PORT}"
-D"${DB}" -e "$(cat "${QUERIES_DIR}"/q"${i}".sql)" | perl -nle 'print $1 if
/\((\d+\.\d+)+ sec\)/' || :)
- res3=$(mysql -vvv -h"${FE_HOST}" -u"${USER}" -P"${FE_QUERY_PORT}"
-D"${DB}" -e "$(cat "${QUERIES_DIR}"/q"${i}".sql)" | perl -nle 'print $1 if
/\((\d+\.\d+)+ sec\)/' || :)
+RESULT_DIR="${CURDIR}/result"
+if [[ -d "${RESULT_DIR}" ]]; then
+ rm -r "${RESULT_DIR}"
+fi
+mkdir -p "${RESULT_DIR}"
+touch result.csv
- min_value=$(echo "${res1} ${res2} ${res3}" | tr ' ' '\n' | sort -n | head
-n 1)
- echo -e "q${i}:\t${res1}\t${res2}\t${res3}\tfast:${min_value}"
+cold_run_sum=0
+best_hot_run_sum=0
- cost=$(echo "${min_value}" | cut -d' ' -f1)
- sum=$(echo "${sum} + ${cost}" | bc)
+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
+ cold=0
+ hot1=0
+ hot2=0
+ echo -ne "q${i}\t" | tee -a result.csv
+ start=$(date +%s%3N)
+ mysql -h"${FE_HOST}" -u "${USER}" -P"${FE_QUERY_PORT}" -D"${DB}"
--comments <"${QUERIES_DIR}"/q"${i}".sql >"${RESULT_DIR}"/result"${i}".out
2>"${RESULT_DIR}"/result"${i}".log
+ end=$(date +%s%3N)
+ cold=$((end - start))
+ echo -ne "${cold}\t" | tee -a result.csv
+
+ start=$(date +%s%3N)
+ mysql -h"${FE_HOST}" -u "${USER}" -P"${FE_QUERY_PORT}" -D"${DB}"
--comments <"${QUERIES_DIR}"/q"${i}".sql >"${RESULT_DIR}"/result"${i}".out
2>"${RESULT_DIR}"/result"${i}".log
+ end=$(date +%s%3N)
+ hot1=$((end - start))
+ echo -ne "${hot1}\t" | tee -a result.csv
+
+ start=$(date +%s%3N)
+ mysql -h"${FE_HOST}" -u "${USER}" -P"${FE_QUERY_PORT}" -D"${DB}"
--comments <"${QUERIES_DIR}"/q"${i}".sql >"${RESULT_DIR}"/result"${i}".out
2>"${RESULT_DIR}"/result"${i}".log
+ end=$(date +%s%3N)
+ hot2=$((end - start))
+ echo -ne "${hot2}\t" | tee -a result.csv
+
+ cold_run_sum=$((cold_run_sum + cold))
+ if [[ ${hot1} -lt ${hot2} ]]; then
+ best_hot_run_sum=$((best_hot_run_sum + hot1))
+ echo -ne "${hot1}" | tee -a result.csv
+ echo "" | tee -a result.csv
+ else
+ best_hot_run_sum=$((best_hot_run_sum + hot2))
+ echo -ne "${hot2}" | tee -a result.csv
+ echo "" | tee -a result.csv
+ fi
done
-echo "total time: ${sum} seconds"
-
-echo '============================================'
-echo "restore session variables"
-run_sql "set global
parallel_fragment_exec_instance_num=${origin_parallel_fragment_exec_instance_num};"
-run_sql "set global exec_mem_limit=${origin_exec_mem_limit};"
-run_sql "set global batch_size=${origin_batch_size};"
-run_sql "set global enable_projection=${origin_enable_projection};"
-run_sql "set global runtime_filter_mode=${origin_runtime_filter_mode};"
-echo '============================================'
+echo "Total cold run time: ${cold_run_sum} ms"
+echo "Total hot run time: ${best_hot_run_sum} ms"
echo 'Finish ssb queries.'
diff --git a/tools/ssb-tools/ddl/create-ssb-flat-table.sql
b/tools/ssb-tools/ddl/create-ssb-flat-tables-sf1.sql
similarity index 93%
copy from tools/ssb-tools/ddl/create-ssb-flat-table.sql
copy to tools/ssb-tools/ddl/create-ssb-flat-tables-sf1.sql
index b1e1681a942..2262e88715d 100644
--- a/tools/ssb-tools/ddl/create-ssb-flat-table.sql
+++ b/tools/ssb-tools/ddl/create-ssb-flat-tables-sf1.sql
@@ -58,17 +58,17 @@ CREATE TABLE IF NOT EXISTS `lineorder_flat` (
DUPLICATE KEY(`LO_ORDERDATE`, `LO_ORDERKEY`)
COMMENT "OLAP"
PARTITION BY RANGE(`LO_ORDERDATE`)
-(PARTITION p1992 VALUES [("-2147483648"), ("19930101")),
+(
+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")))
+PARTITION p1998 VALUES [("19980101"), ("19990101"))
+)
DISTRIBUTED BY HASH(`LO_ORDERKEY`) BUCKETS 48
PROPERTIES (
-"replication_num" = "1",
-"colocate_with" = "groupxx1",
-"in_memory" = "false",
-"storage_format" = "DEFAULT"
+ "replication_num" = "1",
+ "colocate_with" = "groupxx1"
);
\ No newline at end of file
diff --git a/tools/ssb-tools/ddl/create-ssb-flat-table.sql
b/tools/ssb-tools/ddl/create-ssb-flat-tables-sf100.sql
similarity index 93%
copy from tools/ssb-tools/ddl/create-ssb-flat-table.sql
copy to tools/ssb-tools/ddl/create-ssb-flat-tables-sf100.sql
index b1e1681a942..2262e88715d 100644
--- a/tools/ssb-tools/ddl/create-ssb-flat-table.sql
+++ b/tools/ssb-tools/ddl/create-ssb-flat-tables-sf100.sql
@@ -58,17 +58,17 @@ CREATE TABLE IF NOT EXISTS `lineorder_flat` (
DUPLICATE KEY(`LO_ORDERDATE`, `LO_ORDERKEY`)
COMMENT "OLAP"
PARTITION BY RANGE(`LO_ORDERDATE`)
-(PARTITION p1992 VALUES [("-2147483648"), ("19930101")),
+(
+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")))
+PARTITION p1998 VALUES [("19980101"), ("19990101"))
+)
DISTRIBUTED BY HASH(`LO_ORDERKEY`) BUCKETS 48
PROPERTIES (
-"replication_num" = "1",
-"colocate_with" = "groupxx1",
-"in_memory" = "false",
-"storage_format" = "DEFAULT"
+ "replication_num" = "1",
+ "colocate_with" = "groupxx1"
);
\ No newline at end of file
diff --git a/tools/ssb-tools/ddl/create-ssb-flat-table.sql
b/tools/ssb-tools/ddl/create-ssb-flat-tables-sf1000.sql
similarity index 90%
rename from tools/ssb-tools/ddl/create-ssb-flat-table.sql
rename to tools/ssb-tools/ddl/create-ssb-flat-tables-sf1000.sql
index b1e1681a942..182e79daa53 100644
--- a/tools/ssb-tools/ddl/create-ssb-flat-table.sql
+++ b/tools/ssb-tools/ddl/create-ssb-flat-tables-sf1000.sql
@@ -17,7 +17,7 @@
CREATE TABLE IF NOT EXISTS `lineorder_flat` (
`LO_ORDERDATE` int(11) NOT NULL COMMENT "",
- `LO_ORDERKEY` int(11) NOT NULL COMMENT "",
+ `LO_ORDERKEY` bigint(20) NOT NULL COMMENT "",
`LO_LINENUMBER` tinyint(4) NOT NULL COMMENT "",
`LO_CUSTKEY` int(11) NOT NULL COMMENT "",
`LO_PARTKEY` int(11) NOT NULL COMMENT "",
@@ -58,17 +58,17 @@ CREATE TABLE IF NOT EXISTS `lineorder_flat` (
DUPLICATE KEY(`LO_ORDERDATE`, `LO_ORDERKEY`)
COMMENT "OLAP"
PARTITION BY RANGE(`LO_ORDERDATE`)
-(PARTITION p1992 VALUES [("-2147483648"), ("19930101")),
+(
+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
+PARTITION p1998 VALUES [("19980101"), ("19990101"))
+)
+DISTRIBUTED BY HASH(`LO_ORDERKEY`) BUCKETS 120
PROPERTIES (
-"replication_num" = "1",
-"colocate_with" = "groupxx1",
-"in_memory" = "false",
-"storage_format" = "DEFAULT"
+ "replication_num" = "1",
+ "colocate_with" = "groupxx1"
);
\ No newline at end of file
diff --git a/tools/ssb-tools/ddl/create-ssb-tables.sql
b/tools/ssb-tools/ddl/create-ssb-tables-sf1.sql
similarity index 87%
copy from tools/ssb-tools/ddl/create-ssb-tables.sql
copy to tools/ssb-tools/ddl/create-ssb-tables-sf1.sql
index 273d2d90b9d..cb9111a3eeb 100644
--- a/tools/ssb-tools/ddl/create-ssb-tables.sql
+++ b/tools/ssb-tools/ddl/create-ssb-tables-sf1.sql
@@ -37,19 +37,19 @@ CREATE TABLE IF NOT EXISTS `lineorder` (
DUPLICATE KEY(`lo_orderkey`)
COMMENT "OLAP"
PARTITION BY RANGE(`lo_orderdate`)
-(PARTITION p1 VALUES [("-2147483648"), ("19930101")),
+(
+PARTITION p1 VALUES [("-2147483648"), ("19930101")),
PARTITION p2 VALUES [("19930101"), ("19940101")),
PARTITION p3 VALUES [("19940101"), ("19950101")),
PARTITION p4 VALUES [("19950101"), ("19960101")),
PARTITION p5 VALUES [("19960101"), ("19970101")),
PARTITION p6 VALUES [("19970101"), ("19980101")),
-PARTITION p7 VALUES [("19980101"), ("19990101")))
+PARTITION p7 VALUES [("19980101"), ("19990101"))
+)
DISTRIBUTED BY HASH(`lo_orderkey`) BUCKETS 48
PROPERTIES (
-"replication_num" = "1",
-"colocate_with" = "groupa1",
-"in_memory" = "false",
-"storage_format" = "DEFAULT"
+ "replication_num" = "1",
+ "colocate_with" = "groupa1"
);
CREATE TABLE IF NOT EXISTS `customer` (
@@ -66,10 +66,8 @@ DUPLICATE KEY(`c_custkey`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`c_custkey`) BUCKETS 12
PROPERTIES (
-"replication_num" = "1",
-"colocate_with" = "groupa2",
-"in_memory" = "false",
-"storage_format" = "DEFAULT"
+ "replication_num" = "1",
+ "colocate_with" = "groupa2"
);
CREATE TABLE IF NOT EXISTS `dates` (
@@ -95,10 +93,8 @@ DUPLICATE KEY(`d_datekey`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`d_datekey`) BUCKETS 1
PROPERTIES (
-"replication_num" = "1",
-"in_memory" = "false",
-"colocate_with" = "groupa3",
-"storage_format" = "DEFAULT"
+ "replication_num" = "1",
+ "colocate_with" = "groupa3"
);
CREATE TABLE IF NOT EXISTS `supplier` (
@@ -114,10 +110,8 @@ DUPLICATE KEY(`s_suppkey`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`s_suppkey`) BUCKETS 12
PROPERTIES (
-"replication_num" = "1",
-"colocate_with" = "groupa4",
-"in_memory" = "false",
-"storage_format" = "DEFAULT"
+ "replication_num" = "1",
+ "colocate_with" = "groupa4"
);
CREATE TABLE IF NOT EXISTS `part` (
@@ -135,8 +129,6 @@ DUPLICATE KEY(`p_partkey`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`p_partkey`) BUCKETS 12
PROPERTIES (
-"replication_num" = "1",
-"colocate_with" = "groupa5",
-"in_memory" = "false",
-"storage_format" = "DEFAULT"
+ "replication_num" = "1",
+ "colocate_with" = "groupa5"
);
diff --git a/tools/ssb-tools/ddl/create-ssb-tables.sql
b/tools/ssb-tools/ddl/create-ssb-tables-sf100.sql
similarity index 87%
copy from tools/ssb-tools/ddl/create-ssb-tables.sql
copy to tools/ssb-tools/ddl/create-ssb-tables-sf100.sql
index 273d2d90b9d..cb9111a3eeb 100644
--- a/tools/ssb-tools/ddl/create-ssb-tables.sql
+++ b/tools/ssb-tools/ddl/create-ssb-tables-sf100.sql
@@ -37,19 +37,19 @@ CREATE TABLE IF NOT EXISTS `lineorder` (
DUPLICATE KEY(`lo_orderkey`)
COMMENT "OLAP"
PARTITION BY RANGE(`lo_orderdate`)
-(PARTITION p1 VALUES [("-2147483648"), ("19930101")),
+(
+PARTITION p1 VALUES [("-2147483648"), ("19930101")),
PARTITION p2 VALUES [("19930101"), ("19940101")),
PARTITION p3 VALUES [("19940101"), ("19950101")),
PARTITION p4 VALUES [("19950101"), ("19960101")),
PARTITION p5 VALUES [("19960101"), ("19970101")),
PARTITION p6 VALUES [("19970101"), ("19980101")),
-PARTITION p7 VALUES [("19980101"), ("19990101")))
+PARTITION p7 VALUES [("19980101"), ("19990101"))
+)
DISTRIBUTED BY HASH(`lo_orderkey`) BUCKETS 48
PROPERTIES (
-"replication_num" = "1",
-"colocate_with" = "groupa1",
-"in_memory" = "false",
-"storage_format" = "DEFAULT"
+ "replication_num" = "1",
+ "colocate_with" = "groupa1"
);
CREATE TABLE IF NOT EXISTS `customer` (
@@ -66,10 +66,8 @@ DUPLICATE KEY(`c_custkey`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`c_custkey`) BUCKETS 12
PROPERTIES (
-"replication_num" = "1",
-"colocate_with" = "groupa2",
-"in_memory" = "false",
-"storage_format" = "DEFAULT"
+ "replication_num" = "1",
+ "colocate_with" = "groupa2"
);
CREATE TABLE IF NOT EXISTS `dates` (
@@ -95,10 +93,8 @@ DUPLICATE KEY(`d_datekey`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`d_datekey`) BUCKETS 1
PROPERTIES (
-"replication_num" = "1",
-"in_memory" = "false",
-"colocate_with" = "groupa3",
-"storage_format" = "DEFAULT"
+ "replication_num" = "1",
+ "colocate_with" = "groupa3"
);
CREATE TABLE IF NOT EXISTS `supplier` (
@@ -114,10 +110,8 @@ DUPLICATE KEY(`s_suppkey`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`s_suppkey`) BUCKETS 12
PROPERTIES (
-"replication_num" = "1",
-"colocate_with" = "groupa4",
-"in_memory" = "false",
-"storage_format" = "DEFAULT"
+ "replication_num" = "1",
+ "colocate_with" = "groupa4"
);
CREATE TABLE IF NOT EXISTS `part` (
@@ -135,8 +129,6 @@ DUPLICATE KEY(`p_partkey`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`p_partkey`) BUCKETS 12
PROPERTIES (
-"replication_num" = "1",
-"colocate_with" = "groupa5",
-"in_memory" = "false",
-"storage_format" = "DEFAULT"
+ "replication_num" = "1",
+ "colocate_with" = "groupa5"
);
diff --git a/tools/ssb-tools/ddl/create-ssb-tables.sql
b/tools/ssb-tools/ddl/create-ssb-tables-sf1000.sql
similarity index 85%
rename from tools/ssb-tools/ddl/create-ssb-tables.sql
rename to tools/ssb-tools/ddl/create-ssb-tables-sf1000.sql
index 273d2d90b9d..730581b1ca5 100644
--- a/tools/ssb-tools/ddl/create-ssb-tables.sql
+++ b/tools/ssb-tools/ddl/create-ssb-tables-sf1000.sql
@@ -16,7 +16,7 @@
-- under the License.
CREATE TABLE IF NOT EXISTS `lineorder` (
- `lo_orderkey` int(11) NOT NULL COMMENT "",
+ `lo_orderkey` bigint(20) NOT NULL COMMENT "",
`lo_linenumber` int(11) NOT NULL COMMENT "",
`lo_custkey` int(11) NOT NULL COMMENT "",
`lo_partkey` int(11) NOT NULL COMMENT "",
@@ -37,19 +37,19 @@ CREATE TABLE IF NOT EXISTS `lineorder` (
DUPLICATE KEY(`lo_orderkey`)
COMMENT "OLAP"
PARTITION BY RANGE(`lo_orderdate`)
-(PARTITION p1 VALUES [("-2147483648"), ("19930101")),
+(
+PARTITION p1 VALUES [("-2147483648"), ("19930101")),
PARTITION p2 VALUES [("19930101"), ("19940101")),
PARTITION p3 VALUES [("19940101"), ("19950101")),
PARTITION p4 VALUES [("19950101"), ("19960101")),
PARTITION p5 VALUES [("19960101"), ("19970101")),
PARTITION p6 VALUES [("19970101"), ("19980101")),
-PARTITION p7 VALUES [("19980101"), ("19990101")))
-DISTRIBUTED BY HASH(`lo_orderkey`) BUCKETS 48
+PARTITION p7 VALUES [("19980101"), ("19990101"))
+)
+DISTRIBUTED BY HASH(`lo_orderkey`) BUCKETS 120
PROPERTIES (
-"replication_num" = "1",
-"colocate_with" = "groupa1",
-"in_memory" = "false",
-"storage_format" = "DEFAULT"
+ "replication_num" = "1",
+ "colocate_with" = "groupa1"
);
CREATE TABLE IF NOT EXISTS `customer` (
@@ -66,10 +66,8 @@ DUPLICATE KEY(`c_custkey`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`c_custkey`) BUCKETS 12
PROPERTIES (
-"replication_num" = "1",
-"colocate_with" = "groupa2",
-"in_memory" = "false",
-"storage_format" = "DEFAULT"
+ "replication_num" = "1",
+ "colocate_with" = "groupa2"
);
CREATE TABLE IF NOT EXISTS `dates` (
@@ -95,10 +93,8 @@ DUPLICATE KEY(`d_datekey`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`d_datekey`) BUCKETS 1
PROPERTIES (
-"replication_num" = "1",
-"in_memory" = "false",
-"colocate_with" = "groupa3",
-"storage_format" = "DEFAULT"
+ "replication_num" = "1",
+ "colocate_with" = "groupa3"
);
CREATE TABLE IF NOT EXISTS `supplier` (
@@ -114,10 +110,8 @@ DUPLICATE KEY(`s_suppkey`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`s_suppkey`) BUCKETS 12
PROPERTIES (
-"replication_num" = "1",
-"colocate_with" = "groupa4",
-"in_memory" = "false",
-"storage_format" = "DEFAULT"
+ "replication_num" = "1",
+ "colocate_with" = "groupa4"
);
CREATE TABLE IF NOT EXISTS `part` (
@@ -135,8 +129,6 @@ DUPLICATE KEY(`p_partkey`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`p_partkey`) BUCKETS 12
PROPERTIES (
-"replication_num" = "1",
-"colocate_with" = "groupa5",
-"in_memory" = "false",
-"storage_format" = "DEFAULT"
+ "replication_num" = "1",
+ "colocate_with" = "groupa5"
);
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
