HIVE INSERT OVERWRITE和WITH AS语句同时使用

with as子句要放到insert语句的前边,并且with语句和insert之间不能又分号。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE ramesh_test
(key BIGINT,
text_value STRING,
roman_value STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

WITH v_text
AS
(SELECT 1 AS key, 'One' AS value),
v_roman
AS
(SELECT 1 AS key, 'I' AS value)
INSERT OVERWRITE TABLE ramesh_test
SELECT v_text.key, v_text.value, v_roman.value
FROM v_text JOIN v_roman
ON (v_text.key = v_roman.key);

参考 https://stackoverflow.com/questions/38246042/hive-insert-overwrite-using-with-clause

分享