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 625ac83f72 [enhancement](test) add opensky cases to p2 (#12693)
625ac83f72 is described below
commit 625ac83f72c52b6baabaaa340007341c3bde3df2
Author: Yongqiang YANG <98214048+dataroar...@users.noreply.github.com>
AuthorDate: Mon Sep 19 08:38:17 2022 +0800
[enhancement](test) add opensky cases to p2 (#12693)
---
.../data/opensky_p2/sql/avgDistance.out | 4 +
regression-test/data/opensky_p2/sql/count.out | 4 +
.../data/opensky_p2/sql/flightsCount.out | 134 +++++++++++++++++++++
.../data/opensky_p2/sql/mostBusyOrigin.out | 103 ++++++++++++++++
.../data/opensky_p2/sql/totalDistance.out | 4 +
regression-test/suites/opensky_p2/ddl/opensky.sql | 18 +++
regression-test/suites/opensky_p2/load.groovy | 68 +++++++++++
.../suites/opensky_p2/sql/avgDistance.sql | 1 +
regression-test/suites/opensky_p2/sql/count.sql | 1 +
.../suites/opensky_p2/sql/flightsCount.sql | 7 ++
.../suites/opensky_p2/sql/mostBusyOrigin.sql | 11 ++
.../suites/opensky_p2/sql/totalDistance.sql | 1 +
12 files changed, 356 insertions(+)
diff --git a/regression-test/data/opensky_p2/sql/avgDistance.out
b/regression-test/data/opensky_p2/sql/avgDistance.out
new file mode 100644
index 0000000000..35c0077a6f
--- /dev/null
+++ b/regression-test/data/opensky_p2/sql/avgDistance.out
@@ -0,0 +1,4 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !avgDistance --
+1040768
+
diff --git a/regression-test/data/opensky_p2/sql/count.out
b/regression-test/data/opensky_p2/sql/count.out
new file mode 100644
index 0000000000..7db64bddff
--- /dev/null
+++ b/regression-test/data/opensky_p2/sql/count.out
@@ -0,0 +1,4 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !count --
+66010789
+
diff --git a/regression-test/data/opensky_p2/sql/flightsCount.out
b/regression-test/data/opensky_p2/sql/flightsCount.out
new file mode 100644
index 0000000000..33eea84a68
--- /dev/null
+++ b/regression-test/data/opensky_p2/sql/flightsCount.out
@@ -0,0 +1,134 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !flightsCount --
+201852 4347
+201901 6290
+201902 5779
+201903 5667
+201904 5714
+201905 5678
+201906 5740
+201907 5733
+201908 5857
+201909 5951
+201910 5896
+201911 5834
+201912 5881
+201913 6058
+201914 6107
+201915 6177
+201916 6404
+201917 6759
+201918 6619
+201919 6656
+201920 6553
+201921 6744
+201922 6854
+201923 7073
+201924 7021
+201925 6850
+201926 7240
+201927 7263
+201928 7197
+201929 7085
+201930 7231
+201931 7393
+201932 7430
+201933 7495
+201934 7567
+201935 7262
+201936 7311
+201937 6621
+201938 6021
+201939 6934
+201940 6528
+201941 6696
+201942 6721
+201943 6305
+201944 6303
+201945 6179
+201946 6012
+201947 6019
+201948 5770
+201949 4959
+201950 5673
+201951 5967
+201952 5921
+202001 6098
+202002 5745
+202003 5269
+202004 5493
+202005 5812
+202006 5645
+202007 5935
+202008 6006
+202009 6112
+202010 5840
+202011 4918
+202012 3841
+202013 2055
+202014 889
+202015 734
+202016 688
+202017 944
+202018 829
+202019 1028
+202020 1120
+202021 1239
+202022 1733
+202023 1897
+202024 2248
+202025 2755
+202026 3308
+202027 3531
+202028 3610
+202029 3679
+202030 3893
+202031 4320
+202032 4549
+202033 4745
+202034 4907
+202035 4762
+202036 4622
+202037 4612
+202038 4477
+202039 4439
+202040 4248
+202041 4240
+202042 4034
+202043 3614
+202044 3593
+202045 3434
+202046 3351
+202047 3247
+202048 3172
+202049 3282
+202050 3339
+202051 3637
+202052 4023
+202101 3839
+202102 3597
+202103 3144
+202104 3211
+202105 3178
+202106 3294
+202107 3465
+202108 3518
+202109 3775
+202110 3624
+202111 3237
+202112 3759
+202113 4197
+202114 4294
+202115 4253
+202116 4346
+202117 4790
+202118 4966
+202119 5143
+202120 5046
+202121 5094
+202122 4635
+202123 5650
+202124 5590
+202125 6016
+202126 3461
+
diff --git a/regression-test/data/opensky_p2/sql/mostBusyOrigin.out
b/regression-test/data/opensky_p2/sql/mostBusyOrigin.out
new file mode 100644
index 0000000000..a1cb7bacd5
--- /dev/null
+++ b/regression-test/data/opensky_p2/sql/mostBusyOrigin.out
@@ -0,0 +1,103 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !mostBusyOrigin --
+KORD 745006 1545579
+KDFW 696702 1358356
+KATL 667286 1169451
+KDEN 582709 1287105
+KLAX 581949 2628301
+KLAS 447789 1336521
+KPHX 428558 1344938
+KSEA 412592 1757171
+KCLT 404612 880154
+VIDP 363074 1446759
+EDDF 362643 2263164
+KSFO 361869 2445545
+KJFK 349232 2996621
+KMSP 346010 1286682
+LFPG 344748 2205349
+EGLL 341370 3215745
+EHAM 340272 2115434
+KEWR 337695 1826368
+KPHL 320762 1291422
+OMDB 308855 2855438
+UUEE 307098 1554257
+KBOS 304416 1621088
+LEMD 291787 1694186
+YSSY 272977 1875510
+KMIA 265121 1925366
+ZGSZ 263497 745210
+EDDM 256691 1360465
+WMKK 254264 1627631
+CYYZ 251192 2174916
+KLGA 248699 1106278
+VHHH 248473 3457996
+RJTT 243477 1271850
+KBWI 241440 1186848
+KIAD 239558 1683218
+KIAH 234202 1538193
+KFLL 223447 1465818
+KDAL 212055 1081956
+KDCA 207883 1012912
+LIRF 207047 1427062
+PANC 206005 2524856
+LTFJ 205415 859915
+KDTW 204020 1106339
+VABB 201679 1301971
+OTHH 200797 3759551
+KMDW 200796 1232101
+KSAN 198003 1495154
+KPDX 197760 1269035
+SBGR 197623 2042769
+VOBL 189011 1042172
+LEBL 188956 1282274
+YBBN 188010 1254268
+LSZH 187934 1571073
+YMML 187642 1869850
+RCTP 184466 2774386
+KSNA 180045 778417
+EGKK 176420 1693763
+LOWW 176191 1273392
+UUDD 176099 1367376
+RKSI 173466 3079505
+EKCH 172128 1228412
+KOAK 171119 1114208
+RPLL 170122 1441477
+KRDU 167001 830357
+KAUS 164524 1256162
+KBNA 163242 1022066
+KSDF 162655 1380312
+ENGM 160732 908358
+LIMC 160696 1563714
+KSJC 159278 1080900
+KSTL 157984 1026331
+UUWW 156811 1260028
+KIND 153929 987417
+ESSA 153390 1201754
+KMCO 153351 1509510
+KDVT 152895 73859
+VTBS 152645 2256326
+CYVR 149574 2026992
+EIDW 148723 1502980
+LFPO 143277 1151886
+EGSS 140830 1347033
+KAPA 140776 419958
+KHOU 138985 1068669
+KTPA 138033 1338877
+KFFZ 137333 55312
+NZAA 136091 1581353
+YPPH 133916 1272160
+RJBB 133522 1804668
+EDDL 133018 1264868
+ULLI 130501 1195231
+KIWA 127195 250718
+KTEB 126969 1189208
+VOMM 125616 1129088
+LSGG 123998 1047998
+LPPT 122733 1778416
+WSSS 120493 3266158
+EBBR 118539 1578904
+VTBD 118107 662654
+KVNY 116326 692575
+EDDT 115122 940481
+EFHK 114860 1627722
+
diff --git a/regression-test/data/opensky_p2/sql/totalDistance.out
b/regression-test/data/opensky_p2/sql/totalDistance.out
new file mode 100644
index 0000000000..53c175c8ba
--- /dev/null
+++ b/regression-test/data/opensky_p2/sql/totalDistance.out
@@ -0,0 +1,4 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !totalDistance --
+68700204389
+
diff --git a/regression-test/suites/opensky_p2/ddl/opensky.sql
b/regression-test/suites/opensky_p2/ddl/opensky.sql
new file mode 100644
index 0000000000..5e378724d0
--- /dev/null
+++ b/regression-test/suites/opensky_p2/ddl/opensky.sql
@@ -0,0 +1,18 @@
+Create table opensky (
+ origin varchar(128),
+ destination varchar(128),
+ callsign varchar(128),
+ number String,
+ icao24 String,
+ registration String,
+ typecode String,
+ firstseen DateTime,
+ lastseen DateTime,
+ day DateTime,
+ latitude_1 double,
+ longitude_1 double,
+ altitude_1 double,
+ latitude_2 double,
+ longitude_2 double,
+ altitude_2 double )
+ DUPLICATE KEY (origin, destination, callsign) DISTRIBUTED BY HASH(origin,
destination, callsign) BUCKETS 4 properties ("replication_num"="1");
diff --git a/regression-test/suites/opensky_p2/load.groovy
b/regression-test/suites/opensky_p2/load.groovy
new file mode 100644
index 0000000000..9874a8bfb4
--- /dev/null
+++ b/regression-test/suites/opensky_p2/load.groovy
@@ -0,0 +1,68 @@
+// 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.
+
+// The cases is copied from https://github.com/trinodb/trino/tree/master
+// /testing/trino-product-tests/src/main/resources/sql-tests/testcases/tpcds
+// and modified by Doris.
+suite("load"){
+ def tableName="opensky"
+ def sourceFiles=["reorder_flightlist_20190101_20190131.csv.gz",
"reorder_flightlist_20190201_20190228.csv.gz",
"reorder_flightlist_20190301_20190331.csv.gz",
"reorder_flightlist_20190401_20190430.csv.gz",
"reorder_flightlist_20190501_20190531.csv.gz",
"reorder_flightlist_20190601_20190630.csv.gz",
"reorder_flightlist_20190701_20190731.csv.gz",
"reorder_flightlist_20190801_20190831.csv.gz",
"reorder_flightlist_20190901_20190930.csv.gz",
"reorder_flightlist_20191001_20191031.csv.gz", " [...]
+
+ sql """ DROP TABLE IF EXISTS $tableName """
+
+ scriptDir = new
File(getClass().protectionDomain.codeSource.location.path).parent
+
+ sql new File("""${scriptDir}/ddl/${tableName}.sql""").text
+
+ for (String sourceFile in sourceFiles) {
+ streamLoad {
+ // you can skip declare db, because a default db already specify
in ${DORIS_HOME}/conf/regression-conf.groovy
+ // db 'regression_test'
+ table tableName
+
+ // default label is UUID:
+ // set 'label' UUID.randomUUID().toString()
+
+ // default column_separator is specify in doris fe config, usually
is '\t'.
+ // this line change to ','
+ set 'column_separator', ','
+ set 'compress_type', 'GZ'
+ set 'max_filter_ratio', '0.1'
+ set 'timeout', '3600'
+ // relate to
${DORIS_HOME}/regression-test/data/demo/streamload_input.csv.
+ // also, you can stream load a http stream, e.g.
http://xxx/some.csv
+ file """${getS3Url() + '/regression/clickhouse/opensky/' +
sourceFile}"""
+
+ time 0
+
+ // stream load action will check result, include Success status,
and NumberTotalRows == NumberLoadedRows
+
+ // if declared a check callback, the default check condition will
ignore.
+ // So you must check all condition
+ check { result, exception, startTime, endTime ->
+ if (exception != null) {
+ throw exception
+ }
+ log.info("Stream load result: ${result}".toString())
+ def json = parseJson(result)
+ assertEquals("success", json.Status.toLowerCase())
+ // assertEquals(json.NumberTotalRows,
json.NumberLoadedRows)
+ assertTrue(json.NumberLoadedRows > 0 && json.LoadBytes > 0)
+ }
+ }
+ }
+}
diff --git a/regression-test/suites/opensky_p2/sql/avgDistance.sql
b/regression-test/suites/opensky_p2/sql/avgDistance.sql
new file mode 100644
index 0000000000..54c07b65b8
--- /dev/null
+++ b/regression-test/suites/opensky_p2/sql/avgDistance.sql
@@ -0,0 +1 @@
+SELECT round(avg(st_distance_sphere(longitude_1, latitude_1, longitude_2,
latitude_2))) FROM opensky;
diff --git a/regression-test/suites/opensky_p2/sql/count.sql
b/regression-test/suites/opensky_p2/sql/count.sql
new file mode 100644
index 0000000000..939493b303
--- /dev/null
+++ b/regression-test/suites/opensky_p2/sql/count.sql
@@ -0,0 +1 @@
+SELECT count() FROM opensky;
diff --git a/regression-test/suites/opensky_p2/sql/flightsCount.sql
b/regression-test/suites/opensky_p2/sql/flightsCount.sql
new file mode 100644
index 0000000000..4c51fa70e9
--- /dev/null
+++ b/regression-test/suites/opensky_p2/sql/flightsCount.sql
@@ -0,0 +1,7 @@
+SELECT
+ yearweek(day) AS k,
+ count() AS c
+FROM opensky
+WHERE origin IN ('UUEE', 'UUDD', 'UUWW')
+GROUP BY k
+ORDER BY k ASC;
diff --git a/regression-test/suites/opensky_p2/sql/mostBusyOrigin.sql
b/regression-test/suites/opensky_p2/sql/mostBusyOrigin.sql
new file mode 100644
index 0000000000..59a5bd30fd
--- /dev/null
+++ b/regression-test/suites/opensky_p2/sql/mostBusyOrigin.sql
@@ -0,0 +1,11 @@
+SELECT
+ origin,
+ count(),
+ round(avg(st_distance_sphere(longitude_1, latitude_1, longitude_2,
latitude_2))) AS distance
+FROM opensky
+WHERE origin != ''
+GROUP BY origin
+ORDER BY count() DESC
+LIMIT 100;
+
+
diff --git a/regression-test/suites/opensky_p2/sql/totalDistance.sql
b/regression-test/suites/opensky_p2/sql/totalDistance.sql
new file mode 100644
index 0000000000..85e799b2d8
--- /dev/null
+++ b/regression-test/suites/opensky_p2/sql/totalDistance.sql
@@ -0,0 +1 @@
+SELECT round(sum(st_distance_sphere(longitude_1, latitude_1, longitude_2,
latitude_2) / 1000)) FROM opensky;
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
