forked from apache/spark
/
HiveQuerySuite.scala
184 lines (141 loc) · 5.8 KB
/
HiveQuerySuite.scala
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
/*
* 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.
*/
package org.apache.spark.sql.hive.execution
import org.apache.spark.sql.hive.test.TestHive._
/**
* A set of test cases expressed in Hive QL that are not covered by the tests included in the hive distribution.
*/
class HiveQuerySuite extends HiveComparisonTest {
createQueryTest("between",
"SELECT * FROM src WHERE key between 1 and 2"
)
test("Query expressed in SQL") {
assert(sql("SELECT 1").collect() === Array(Seq(1)))
}
test("Query expressed in HiveQL") {
hql("FROM src SELECT key").collect()
hiveql("FROM src SELECT key").collect()
}
createQueryTest("Constant Folding Optimization for AVG_SUM_COUNT",
"SELECT AVG(0), SUM(0), COUNT(null), COUNT(value) FROM src GROUP BY key")
createQueryTest("Simple Average",
"SELECT AVG(key) FROM src")
createQueryTest("Simple Average + 1",
"SELECT AVG(key) + 1.0 FROM src")
createQueryTest("Simple Average + 1 with group",
"SELECT AVG(key) + 1.0, value FROM src group by value")
createQueryTest("string literal",
"SELECT 'test' FROM src")
createQueryTest("Escape sequences",
"""SELECT key, '\\\t\\' FROM src WHERE key = 86""")
createQueryTest("IgnoreExplain",
"""EXPLAIN SELECT key FROM src""")
createQueryTest("trivial join where clause",
"SELECT * FROM src a JOIN src b WHERE a.key = b.key")
createQueryTest("trivial join ON clause",
"SELECT * FROM src a JOIN src b ON a.key = b.key")
createQueryTest("small.cartesian",
"SELECT a.key, b.key FROM (SELECT key FROM src WHERE key < 1) a JOIN (SELECT key FROM src WHERE key = 2) b")
createQueryTest("length.udf",
"SELECT length(\"test\") FROM src LIMIT 1")
createQueryTest("partitioned table scan",
"SELECT ds, hr, key, value FROM srcpart")
createQueryTest("hash",
"SELECT hash('test') FROM src LIMIT 1")
createQueryTest("create table as",
"""
|CREATE TABLE createdtable AS SELECT * FROM src;
|SELECT * FROM createdtable
""".stripMargin)
createQueryTest("create table as with db name",
"""
|CREATE DATABASE IF NOT EXISTS testdb;
|CREATE TABLE testdb.createdtable AS SELECT * FROM default.src;
|SELECT * FROM testdb.createdtable;
|DROP DATABASE IF EXISTS testdb CASCADE
""".stripMargin)
createQueryTest("insert table with db name",
"""
|CREATE DATABASE IF NOT EXISTS testdb;
|CREATE TABLE testdb.createdtable like default.src;
|INSERT INTO TABLE testdb.createdtable SELECT * FROM default.src;
|SELECT * FROM testdb.createdtable;
|DROP DATABASE IF EXISTS testdb CASCADE
""".stripMargin)
createQueryTest("insert into and insert overwrite",
"""
|CREATE TABLE createdtable like src;
|INSERT INTO TABLE createdtable SELECT * FROM src;
|INSERT INTO TABLE createdtable SELECT * FROM src1;
|SELECT * FROM createdtable;
|INSERT OVERWRITE TABLE createdtable SELECT * FROM src WHERE key = 86;
|SELECT * FROM createdtable;
""".stripMargin)
createQueryTest("transform",
"SELECT TRANSFORM (key) USING 'cat' AS (tKey) FROM src")
createQueryTest("LIKE",
"SELECT * FROM src WHERE value LIKE '%1%'")
createQueryTest("DISTINCT",
"SELECT DISTINCT key, value FROM src")
ignore("empty aggregate input") {
createQueryTest("empty aggregate input",
"SELECT SUM(key) FROM (SELECT * FROM src LIMIT 0) a")
}
createQueryTest("lateral view1",
"SELECT tbl.* FROM src LATERAL VIEW explode(array(1,2)) tbl as a")
createQueryTest("lateral view2",
"SELECT * FROM src LATERAL VIEW explode(array(1,2)) tbl")
createQueryTest("lateral view3",
"FROM src SELECT key, D.* lateral view explode(array(key+3, key+4)) D as CX")
createQueryTest("lateral view4",
"""
|create table src_lv1 (key string, value string);
|create table src_lv2 (key string, value string);
|
|FROM src
|insert overwrite table src_lv1 SELECT key, D.* lateral view explode(array(key+3, key+4)) D as CX
|insert overwrite table src_lv2 SELECT key, D.* lateral view explode(array(key+3, key+4)) D as CX
""".stripMargin)
createQueryTest("lateral view5",
"FROM src SELECT explode(array(key+3, key+4))")
createQueryTest("lateral view6",
"SELECT * FROM src LATERAL VIEW explode(map(key+3,key+4)) D as k, v")
test("sampling") {
hql("SELECT * FROM src TABLESAMPLE(0.1 PERCENT) s")
}
test("SchemaRDD toString") {
hql("SHOW TABLES").toString
hql("SELECT * FROM src").toString
}
test("parse HQL set commands") {
// Adapted from SQLConfSuite.
sqlConf.clear()
val testKey = "spark.sql.key"
val testVal = "val0,val_1,val2.3,my_table"
hql(s"set $testKey=$testVal")
assert(sqlConf.get(testKey, testVal + "_") == testVal)
hql("set mapred.reduce.tasks=20")
assert(sqlConf.get("mapred.reduce.tasks", "0") == "20")
hql("set mapred.reduce.tasks = 40")
assert(sqlConf.get("mapred.reduce.tasks", "0") == "40")
hql(s"set $testKey=$testVal")
assert(sqlConf.get(testKey, "0") == testVal)
hql(s"set$testKey=")
assert(sqlConf.get(testKey, "0") == "")
sqlConf.clear()
}
}