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/07/20 09:59:11 UTC
[doris] branch master updated: [tools] add clickbench tools (#11009)
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/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 1ca00e0107 [tools] add clickbench tools (#11009)
1ca00e0107 is described below
commit 1ca00e010785f26ca2b1b6b123db2c64437520e5
Author: Dongyang Li <he...@qq.com>
AuthorDate: Wed Jul 20 17:59:04 2022 +0800
[tools] add clickbench tools (#11009)
* [tools] add clickbench tools
Co-authored-by: stephen <he...@qq.com>
---
tools/clickbench-tools/README.md | 33 +++++
tools/clickbench-tools/conf/doris-cluster.conf | 33 +++++
tools/clickbench-tools/create-clickbench-table.sh | 98 +++++++++++++++
tools/clickbench-tools/load-clickbench-data.sh | 140 +++++++++++++++++++++
tools/clickbench-tools/run-clickbench-queries.sh | 132 +++++++++++++++++++
.../sql/create-clickbench-table.sql | 127 +++++++++++++++++++
tools/clickbench-tools/sql/queries.sql | 60 +++++++++
7 files changed, 623 insertions(+)
diff --git a/tools/clickbench-tools/README.md b/tools/clickbench-tools/README.md
new file mode 100644
index 0000000000..e63a5cc6c8
--- /dev/null
+++ b/tools/clickbench-tools/README.md
@@ -0,0 +1,33 @@
+<!--
+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 do [ClickBench](https://benchmark.clickhouse.com/) test, more info [there](https://github.com/ClickHouse/ClickBench).**
+
+## follow the steps below:
+
+### 1. create table
+ ./create-clickbench-table.sh
+
+### 2. load data
+ ./load-clickbench-data.sh
+
+### 3. run queries
+ ./run-clickbench-queries.sh
diff --git a/tools/clickbench-tools/conf/doris-cluster.conf b/tools/clickbench-tools/conf/doris-cluster.conf
new file mode 100644
index 0000000000..cc7d8a2602
--- /dev/null
+++ b/tools/clickbench-tools/conf/doris-cluster.conf
@@ -0,0 +1,33 @@
+# 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.
+
+# Any of FE host
+export FE_HOST='127.0.0.1'
+# BE host
+export BE_HOST='127.0.0.1'
+# http_port in fe.conf
+export FE_HTTP_PORT=8030
+# webserver_port in be.conf
+export BE_WEBSERVER_PORT=8040
+# query_port in fe.conf
+export FE_QUERY_PORT=9030
+# Doris username
+export USER='root'
+# Doris password
+export PASSWORD=''
+# The database name
+export DB='clickbench'
diff --git a/tools/clickbench-tools/create-clickbench-table.sh b/tools/clickbench-tools/create-clickbench-table.sh
new file mode 100755
index 0000000000..d95e134e6f
--- /dev/null
+++ b/tools/clickbench-tools/create-clickbench-table.sh
@@ -0,0 +1,98 @@
+#!/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 create ClickBench table.
+##############################################################
+
+set -eo pipefail
+
+ROOT=$(dirname "$0")
+ROOT=$(
+ cd "$ROOT"
+ pwd
+)
+
+CURDIR=${ROOT}
+
+usage() {
+ echo "
+This script is used to create ClickBench table,
+will use mysql client to connect Doris server which is specified in conf/doris-cluster.conf file.
+Usage: $0
+ "
+ exit 1
+}
+
+OPTS=$(getopt \
+ -n $0 \
+ -o '' \
+ -- "$@")
+
+eval set -- "$OPTS"
+HELP=0
+
+if [ $# == 0 ]; then
+ usage
+fi
+
+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 mysql to create tables in Doris."
+ exit 1
+ fi
+}
+
+check_prerequest "mysql --version" "mysql"
+
+source $CURDIR/conf/doris-cluster.conf
+echo "FE_HOST: $FE_HOST"
+echo "FE_QUERY_PORT: $FE_QUERY_PORT"
+echo "USER: $USER"
+echo "PASSWORD: $PASSWORD"
+echo "DB: $DB"
+
+mysql -h$FE_HOST -u$USER -P$FE_QUERY_PORT -e "CREATE DATABASE IF NOT EXISTS $DB"
+mysql -h$FE_HOST -u$USER -P$FE_QUERY_PORT -D$DB <$CURDIR/sql/create-clickbench-table.sql
+mysql -h$FE_HOST -u$USER -P$FE_QUERY_PORT -D$DB -e "show create table hits;"
+
+echo "DONE."
diff --git a/tools/clickbench-tools/load-clickbench-data.sh b/tools/clickbench-tools/load-clickbench-data.sh
new file mode 100755
index 0000000000..53d6d61b4b
--- /dev/null
+++ b/tools/clickbench-tools/load-clickbench-data.sh
@@ -0,0 +1,140 @@
+#!/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 clickbench data into Doris
+##############################################################
+
+set -eo pipefail
+
+ROOT=$(dirname "$0")
+ROOT=$(
+ cd "$ROOT"
+ pwd
+)
+
+CURDIR=${ROOT}
+DATA_DIR=$CURDIR/
+# DATA_DIR=/mnt/disk1/stephen/data/clickbench
+
+usage() {
+ echo "
+This script is used to load ClickBench data,
+will use mysql client to connect Doris server which is specified in conf/doris-cluster.conf file.
+Usage: $0
+ "
+ 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
+}
+
+check_prerequest "mysql --version" "mysql"
+check_prerequest "curl --version" "curl"
+check_prerequest "wget --version" "wget"
+
+source $CURDIR/conf/doris-cluster.conf
+
+echo "FE_HOST: $FE_HOST"
+echo "FE_HTTP_PORT: $FE_HTTP_PORT"
+echo "USER: $USER"
+echo "PASSWORD: $PASSWORD"
+echo "DB: $DB"
+
+function check_doirs_conf() {
+ cv=$(mysql -h$FE_HOST -P$FE_QUERY_PORT -u$USER -e 'admin show frontend config' | grep 'stream_load_default_timeout_second' | awk '{print $2}')
+ if (($cv < 3600)); then
+ echo "advise: revise your Doris FE's conf to set 'stream_load_default_timeout_second=3600' or above"
+ fi
+
+ cv=$(curl "${BE_HOST}:${BE_WEBSERVER_PORT}/varz" 2>/dev/null | grep 'streaming_load_max_mb' | awk -F'=' '{print $2}')
+ if (($cv < 16000)); then
+ echo -e "advise: revise your Doris BE's conf to set 'streaming_load_max_mb=16000' or above and 'flush_thread_num_per_store=5' to speed up load."
+ fi
+}
+
+function load() {
+ echo "(1/2) prepare clickbench data file"
+ need_download=false
+ cd $DATA_DIR
+ for i in $(seq 0 9); do
+ if [ ! -f "$DATA_DIR/hits_split${i}" ]; then
+ echo "will download hits_split${i} to $DATA_DIR"
+ wget --continue "https://doris-test-data.oss-cn-hongkong.aliyuncs.com/ClickBench/hits_split${i}" &
+ # wget --continue "https://doris-test-data.oss-cn-hongkong-internal.aliyuncs.com/ClickBench/hits_split${i}" &
+ fi
+ done
+
+ echo "wait for download task done..."
+ wait
+ cd -
+
+ echo "(2/2) load clickbench data file $DATA_DIR/hits_split[0-9] into Doris"
+ for i in $(seq 0 9); do
+ echo -e "
+ start loading hits_split${i}"
+ curl --location-trusted \
+ -u $USER:$PASSWORD \
+ -T "$DATA_DIR/hits_split${i}" \
+ -H "columns:WatchID,JavaEnable,Title,GoodEvent,EventTime,EventDate,CounterID,ClientIP,RegionID,UserID,CounterClass,OS,UserAgent,URL,Referer,IsRefresh,RefererCategoryID,RefererRegionID,URLCategoryID,URLRegionID,ResolutionWidth,ResolutionHeight,ResolutionDepth,FlashMajor,FlashMinor,FlashMinor2,NetMajor,NetMinor,UserAgentMajor,UserAgentMinor,CookieEnable,JavascriptEnable,IsMobile,MobilePhone,MobilePhoneModel,Params,IPNetworkID,TraficSourceID,SearchEngineID,SearchPhrase,AdvEngine [...]
+ http://$FE_HOST:$FE_HTTP_PORT/api/$DB/hits/_stream_load
+ done
+}
+
+echo "start..."
+start=$(date +%s)
+check_doirs_conf
+load
+end=$(date +%s)
+echo "load cost time: $((end - start)) seconds"
diff --git a/tools/clickbench-tools/run-clickbench-queries.sh b/tools/clickbench-tools/run-clickbench-queries.sh
new file mode 100755
index 0000000000..41a0126985
--- /dev/null
+++ b/tools/clickbench-tools/run-clickbench-queries.sh
@@ -0,0 +1,132 @@
+#!/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 run ClickBench queries
+##############################################################
+
+set -eo pipefail
+
+ROOT=$(dirname "$0")
+ROOT=$(
+ cd "$ROOT"
+ pwd
+)
+
+CURDIR=${ROOT}
+QUERIES_FILE=$CURDIR/sql/queries.sql
+
+usage() {
+ echo "
+This script is used to run ClickBench 43 queries,
+will use mysql client to connect Doris server which parameter is specified in conf/doris-cluster.conf file.
+Usage: $0
+ "
+ exit 1
+}
+
+OPTS=$(getopt \
+ -n $0 \
+ -o '' \
+ -o 'h' \
+ -- "$@")
+
+eval set -- "$OPTS"
+HELP=0
+
+if [ $# == 0 ]; then
+ usage
+fi
+
+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 mysql to create tables in Doris."
+ exit 1
+ fi
+}
+
+check_prerequest "mysql --version" "mysql"
+check_prerequest "perl --version" "perl"
+
+source $CURDIR/conf/doris-cluster.conf
+export MYSQL_PWD=$PASSWORD
+
+echo "FE_HOST: $FE_HOST"
+echo "FE_QUERY_PORT: $FE_QUERY_PORT"
+echo "USER: $USER"
+echo "PASSWORD: $PASSWORD"
+echo "DB: $DB"
+
+pre_set() {
+ echo $@
+ mysql -h$FE_HOST -u$USER -P$FE_QUERY_PORT -D$DB -e "$@"
+}
+
+pre_set "set global parallel_fragment_exec_instance_num=8;"
+pre_set "set global exec_mem_limit=8G;"
+echo '============================================'
+pre_set "show variables"
+echo '============================================'
+
+TRIES=3
+QUERY_NUM=1
+touch result.csv
+truncate -s0 result.csv
+
+cat ${QUERIES_FILE} | while read query; do
+ if [[ ! $query == SELECT* ]]; then
+ continue
+ fi
+ sync
+ echo 3 | sudo tee /proc/sys/vm/drop_caches >/dev/null
+
+ echo -n "query${QUERY_NUM}: " | tee -a result.csv
+ for i in $(seq 1 $TRIES); do
+ RES=$(mysql -vvv -h$FE_HOST -u$USER -P$FE_QUERY_PORT -D$DB -e "${query}" | perl -nle 'print $1 if /\((\d+\.\d+)+ sec\)/' || :)
+
+ echo -n "${RES}" | tee -a result.csv
+ [[ "$i" != $TRIES ]] && echo -n "," | tee -a result.csv
+ done
+ echo "" | tee -a result.csv
+
+ QUERY_NUM=$((QUERY_NUM + 1))
+done
diff --git a/tools/clickbench-tools/sql/create-clickbench-table.sql b/tools/clickbench-tools/sql/create-clickbench-table.sql
new file mode 100644
index 0000000000..89f79b3741
--- /dev/null
+++ b/tools/clickbench-tools/sql/create-clickbench-table.sql
@@ -0,0 +1,127 @@
+-- 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.
+
+CREATE TABLE IF NOT EXISTS hits (
+ CounterID INT NOT NULL,
+ EventDate Date NOT NULL,
+ UserID BIGINT NOT NULL,
+ EventTime DateTime NOT NULL,
+ WatchID BIGINT NOT NULL,
+ JavaEnable SMALLINT NOT NULL,
+ Title STRING NOT NULL,
+ GoodEvent SMALLINT NOT NULL,
+ ClientIP INT NOT NULL,
+ RegionID INT NOT NULL,
+ CounterClass SMALLINT NOT NULL,
+ OS SMALLINT NOT NULL,
+ UserAgent SMALLINT NOT NULL,
+ URL STRING NOT NULL,
+ Referer STRING NOT NULL,
+ IsRefresh SMALLINT NOT NULL,
+ RefererCategoryID SMALLINT NOT NULL,
+ RefererRegionID INT NOT NULL,
+ URLCategoryID SMALLINT NOT NULL,
+ URLRegionID INT NOT NULL,
+ ResolutionWidth SMALLINT NOT NULL,
+ ResolutionHeight SMALLINT NOT NULL,
+ ResolutionDepth SMALLINT NOT NULL,
+ FlashMajor SMALLINT NOT NULL,
+ FlashMinor SMALLINT NOT NULL,
+ FlashMinor2 STRING NOT NULL,
+ NetMajor SMALLINT NOT NULL,
+ NetMinor SMALLINT NOT NULL,
+ UserAgentMajor SMALLINT NOT NULL,
+ UserAgentMinor VARCHAR(255) NOT NULL,
+ CookieEnable SMALLINT NOT NULL,
+ JavascriptEnable SMALLINT NOT NULL,
+ IsMobile SMALLINT NOT NULL,
+ MobilePhone SMALLINT NOT NULL,
+ MobilePhoneModel STRING NOT NULL,
+ Params STRING NOT NULL,
+ IPNetworkID INT NOT NULL,
+ TraficSourceID SMALLINT NOT NULL,
+ SearchEngineID SMALLINT NOT NULL,
+ SearchPhrase STRING NOT NULL,
+ AdvEngineID SMALLINT NOT NULL,
+ IsArtifical SMALLINT NOT NULL,
+ WindowClientWidth SMALLINT NOT NULL,
+ WindowClientHeight SMALLINT NOT NULL,
+ ClientTimeZone SMALLINT NOT NULL,
+ ClientEventTime DateTime NOT NULL,
+ SilverlightVersion1 SMALLINT NOT NULL,
+ SilverlightVersion2 SMALLINT NOT NULL,
+ SilverlightVersion3 INT NOT NULL,
+ SilverlightVersion4 SMALLINT NOT NULL,
+ PageCharset STRING NOT NULL,
+ CodeVersion INT NOT NULL,
+ IsLink SMALLINT NOT NULL,
+ IsDownload SMALLINT NOT NULL,
+ IsNotBounce SMALLINT NOT NULL,
+ FUniqID BIGINT NOT NULL,
+ OriginalURL STRING NOT NULL,
+ HID INT NOT NULL,
+ IsOldCounter SMALLINT NOT NULL,
+ IsEvent SMALLINT NOT NULL,
+ IsParameter SMALLINT NOT NULL,
+ DontCountHits SMALLINT NOT NULL,
+ WithHash SMALLINT NOT NULL,
+ HitColor CHAR NOT NULL,
+ LocalEventTime DateTime NOT NULL,
+ Age SMALLINT NOT NULL,
+ Sex SMALLINT NOT NULL,
+ Income SMALLINT NOT NULL,
+ Interests SMALLINT NOT NULL,
+ Robotness SMALLINT NOT NULL,
+ RemoteIP INT NOT NULL,
+ WindowName INT NOT NULL,
+ OpenerName INT NOT NULL,
+ HistoryLength SMALLINT NOT NULL,
+ BrowserLanguage STRING NOT NULL,
+ BrowserCountry STRING NOT NULL,
+ SocialNetwork STRING NOT NULL,
+ SocialAction STRING NOT NULL,
+ HTTPError SMALLINT NOT NULL,
+ SendTiming INT NOT NULL,
+ DNSTiming INT NOT NULL,
+ ConnectTiming INT NOT NULL,
+ ResponseStartTiming INT NOT NULL,
+ ResponseEndTiming INT NOT NULL,
+ FetchTiming INT NOT NULL,
+ SocialSourceNetworkID SMALLINT NOT NULL,
+ SocialSourcePage STRING NOT NULL,
+ ParamPrice BIGINT NOT NULL,
+ ParamOrderID STRING NOT NULL,
+ ParamCurrency STRING NOT NULL,
+ ParamCurrencyID SMALLINT NOT NULL,
+ OpenstatServiceName STRING NOT NULL,
+ OpenstatCampaignID STRING NOT NULL,
+ OpenstatAdID STRING NOT NULL,
+ OpenstatSourceID STRING NOT NULL,
+ UTMSource STRING NOT NULL,
+ UTMMedium STRING NOT NULL,
+ UTMCampaign STRING NOT NULL,
+ UTMContent STRING NOT NULL,
+ UTMTerm STRING NOT NULL,
+ FromTag STRING NOT NULL,
+ HasGCLID SMALLINT NOT NULL,
+ RefererHash BIGINT NOT NULL,
+ URLHash BIGINT NOT NULL,
+ CLID INT NOT NULL
+)
+DUPLICATE KEY (CounterID, EventDate, UserID, EventTime, WatchID)
+DISTRIBUTED BY HASH(UserID) BUCKETS 48
+PROPERTIES ( "replication_num"="1");
diff --git a/tools/clickbench-tools/sql/queries.sql b/tools/clickbench-tools/sql/queries.sql
new file mode 100644
index 0000000000..2f54a52229
--- /dev/null
+++ b/tools/clickbench-tools/sql/queries.sql
@@ -0,0 +1,60 @@
+-- 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.
+
+SELECT COUNT(*) FROM hits;
+SELECT COUNT(*) FROM hits WHERE AdvEngineID <> 0;
+SELECT SUM(AdvEngineID), COUNT(*), AVG(ResolutionWidth) FROM hits;
+SELECT AVG(UserID) FROM hits;
+SELECT COUNT(DISTINCT UserID) FROM hits;
+SELECT COUNT(DISTINCT SearchPhrase) FROM hits;
+SELECT MIN(EventDate), MAX(EventDate) FROM hits;
+SELECT AdvEngineID, COUNT(*) FROM hits WHERE AdvEngineID <> 0 GROUP BY AdvEngineID ORDER BY COUNT(*) DESC;
+SELECT RegionID, COUNT(DISTINCT UserID) AS u FROM hits GROUP BY RegionID ORDER BY u DESC LIMIT 10;
+SELECT RegionID, SUM(AdvEngineID), COUNT(*) AS c, AVG(ResolutionWidth), COUNT(DISTINCT UserID) FROM hits GROUP BY RegionID ORDER BY c DESC LIMIT 10;
+SELECT MobilePhoneModel, COUNT(DISTINCT UserID) AS u FROM hits WHERE MobilePhoneModel <> '' GROUP BY MobilePhoneModel ORDER BY u DESC LIMIT 10;
+SELECT MobilePhone, MobilePhoneModel, COUNT(DISTINCT UserID) AS u FROM hits WHERE MobilePhoneModel <> '' GROUP BY MobilePhone, MobilePhoneModel ORDER BY u DESC LIMIT 10;
+SELECT SearchPhrase, COUNT(*) AS c FROM hits WHERE SearchPhrase <> '' GROUP BY SearchPhrase ORDER BY c DESC LIMIT 10;
+SELECT SearchPhrase, COUNT(DISTINCT UserID) AS u FROM hits WHERE SearchPhrase <> '' GROUP BY SearchPhrase ORDER BY u DESC LIMIT 10;
+SELECT SearchEngineID, SearchPhrase, COUNT(*) AS c FROM hits WHERE SearchPhrase <> '' GROUP BY SearchEngineID, SearchPhrase ORDER BY c DESC LIMIT 10;
+SELECT UserID, COUNT(*) FROM hits GROUP BY UserID ORDER BY COUNT(*) DESC LIMIT 10;
+SELECT UserID, SearchPhrase, COUNT(*) FROM hits GROUP BY UserID, SearchPhrase ORDER BY COUNT(*) DESC LIMIT 10;
+SELECT UserID, SearchPhrase, COUNT(*) FROM hits GROUP BY UserID, SearchPhrase LIMIT 10;
+SELECT UserID, extract(minute FROM EventTime) AS m, SearchPhrase, COUNT(*) FROM hits GROUP BY UserID, m, SearchPhrase ORDER BY COUNT(*) DESC LIMIT 10;
+SELECT UserID FROM hits WHERE UserID = 435090932899640449;
+SELECT COUNT(*) FROM hits WHERE URL LIKE '%google%';
+SELECT SearchPhrase, MIN(URL), COUNT(*) AS c FROM hits WHERE URL LIKE '%google%' AND SearchPhrase <> '' GROUP BY SearchPhrase ORDER BY c DESC LIMIT 10;
+SELECT SearchPhrase, MIN(URL), MIN(Title), COUNT(*) AS c, COUNT(DISTINCT UserID) FROM hits WHERE Title LIKE '%Google%' AND URL NOT LIKE '%.google.%' AND SearchPhrase <> '' GROUP BY SearchPhrase ORDER BY c DESC LIMIT 10;
+SELECT * FROM hits WHERE URL LIKE '%google%' ORDER BY EventTime LIMIT 10;
+SELECT SearchPhrase FROM hits WHERE SearchPhrase <> '' ORDER BY EventTime LIMIT 10;
+SELECT SearchPhrase FROM hits WHERE SearchPhrase <> '' ORDER BY SearchPhrase LIMIT 10;
+SELECT SearchPhrase FROM hits WHERE SearchPhrase <> '' ORDER BY EventTime, SearchPhrase LIMIT 10;
+SELECT CounterID, AVG(length(URL)) AS l, COUNT(*) AS c FROM hits WHERE URL <> '' GROUP BY CounterID HAVING COUNT(*) > 100000 ORDER BY l DESC LIMIT 25;
+SELECT REGEXP_REPLACE(Referer, '^https?://(?:www\.)?([^/]+)/.*$', '\1') AS k, AVG(length(Referer)) AS l, COUNT(*) AS c, MIN(Referer) FROM hits WHERE Referer <> '' GROUP BY k HAVING COUNT(*) > 100000 ORDER BY l DESC LIMIT 25;
+SELECT SUM(ResolutionWidth), SUM(ResolutionWidth + 1), SUM(ResolutionWidth + 2), SUM(ResolutionWidth + 3), SUM(ResolutionWidth + 4), SUM(ResolutionWidth + 5), SUM(ResolutionWidth + 6), SUM(ResolutionWidth + 7), SUM(ResolutionWidth + 8), SUM(ResolutionWidth + 9), SUM(ResolutionWidth + 10), SUM(ResolutionWidth + 11), SUM(ResolutionWidth + 12), SUM(ResolutionWidth + 13), SUM(ResolutionWidth + 14), SUM(ResolutionWidth + 15), SUM(ResolutionWidth + 16), SUM(ResolutionWidth + 17), SUM(Resolutio [...]
+SELECT SearchEngineID, ClientIP, COUNT(*) AS c, SUM(IsRefresh), AVG(ResolutionWidth) FROM hits WHERE SearchPhrase <> '' GROUP BY SearchEngineID, ClientIP ORDER BY c DESC LIMIT 10;
+SELECT WatchID, ClientIP, COUNT(*) AS c, SUM(IsRefresh), AVG(ResolutionWidth) FROM hits WHERE SearchPhrase <> '' GROUP BY WatchID, ClientIP ORDER BY c DESC LIMIT 10;
+SELECT WatchID, ClientIP, COUNT(*) AS c, SUM(IsRefresh), AVG(ResolutionWidth) FROM hits GROUP BY WatchID, ClientIP ORDER BY c DESC LIMIT 10;
+SELECT URL, COUNT(*) AS c FROM hits GROUP BY URL ORDER BY c DESC LIMIT 10;
+SELECT 1, URL, COUNT(*) AS c FROM hits GROUP BY 1, URL ORDER BY c DESC LIMIT 10;
+SELECT ClientIP, ClientIP - 1, ClientIP - 2, ClientIP - 3, COUNT(*) AS c FROM hits GROUP BY ClientIP, ClientIP - 1, ClientIP - 2, ClientIP - 3 ORDER BY c DESC LIMIT 10;
+SELECT URL, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-01' AND EventDate <= '2013-07-31' AND DontCountHits = 0 AND IsRefresh = 0 AND URL <> '' GROUP BY URL ORDER BY PageViews DESC LIMIT 10;
+SELECT Title, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-01' AND EventDate <= '2013-07-31' AND DontCountHits = 0 AND IsRefresh = 0 AND Title <> '' GROUP BY Title ORDER BY PageViews DESC LIMIT 10;
+SELECT URL, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-01' AND EventDate <= '2013-07-31' AND IsRefresh = 0 AND IsLink <> 0 AND IsDownload = 0 GROUP BY URL ORDER BY PageViews DESC LIMIT 10 OFFSET 1000;
+SELECT TraficSourceID, SearchEngineID, AdvEngineID, CASE WHEN (SearchEngineID = 0 AND AdvEngineID = 0) THEN Referer ELSE '' END AS Src, URL AS Dst, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-01' AND EventDate <= '2013-07-31' AND IsRefresh = 0 GROUP BY TraficSourceID, SearchEngineID, AdvEngineID, Src, Dst ORDER BY PageViews DESC LIMIT 10 OFFSET 1000;
+SELECT URLHash, EventDate, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-01' AND EventDate <= '2013-07-31' AND IsRefresh = 0 AND TraficSourceID IN (-1, 6) AND RefererHash = 3594120000172545465 GROUP BY URLHash, EventDate ORDER BY PageViews DESC LIMIT 10 OFFSET 100;
+SELECT WindowClientWidth, WindowClientHeight, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-01' AND EventDate <= '2013-07-31' AND IsRefresh = 0 AND DontCountHits = 0 AND URLHash = 2868770270353813622 GROUP BY WindowClientWidth, WindowClientHeight ORDER BY PageViews DESC LIMIT 10 OFFSET 10000;
+SELECT DATE_FORMAT(EventTime, '%Y-%m-%d %H:%i:00') AS M, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-14' AND EventDate <= '2013-07-15' AND IsRefresh = 0 AND DontCountHits = 0 GROUP BY DATE_FORMAT(EventTime, '%Y-%m-%d %H:%i:00') ORDER BY DATE_FORMAT(EventTime, '%Y-%m-%d %H:%i:00') LIMIT 10 OFFSET 1000;
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org