Hive踩过的坑-Hive的insert与with as 语句搭配

人生就是不断的挖坑;而我也不例外。

写惯了ORACLE语句的童鞋，坑爹对with as语句不陌生吧。简单的说with as 语句也可以说是子查询语句。

首先，我们先来一个栗子(例子)吧。

CREATE TABLE TABLE_A AS
	WITH TEMP_A AS (
		SELECT TIME,IOT_ID,NAME FROM IOT_XX_A
	),TEMP_B AS (
		SELECT TIME,IOT_ID,NAME,COUNT(DISTINCT IOT_ID) AS TIMES FROM TEMP_A
			GROUP BY TIME,IOT_ID,NAME
	)
SELECT TIME,IOT_ID,NAME,TIMES FROM TEMP_B;

按照上面的SQL语句，在ORACLE会执行，而在hive呢，不然也会执行..前提最后压缩一下sql格式。

因为把查询结果创建成表，二者都是支持的。

但是如果是INSERT (插入)表呢?

再举个栗子(例子)

INSERT INTO TABLE_B
	WITH TEMP_A AS (
		SELECT TIME,IOT_ID,NAME FROM IOT_XX_A
	),TEMP_B AS (
		SELECT TIME,IOT_ID,NAME,COUNT(DISTINCT IOT_ID) AS TIMES FROM TEMP_A
			GROUP BY TIME,IOT_ID,NAME
	)
SELECT TIME,IOT_ID,NAME,TIMES FROM TEMP_B;

按照上面的SQL语句，在ORACLE会执行，而在hive呢?他就会报错，具体的错是：Error: Error while compiling statement: FAILED: ParseException line 2:0 cannot recognize input near 'WITH' 'TABLE_B' 'AS' in statement

错误的原因就是INSERT INTO 位置放得不合适。不也还是hive语句与sql语句规则不一样导致的?把INSERT INTO语句换一下位置就可以执行了

	WITH TEMP_A AS (
		SELECT TIME,IOT_ID,NAME FROM IOT_XX_A
	),TEMP_B AS (
		SELECT TIME,IOT_ID,NAME,COUNT(DISTINCT IOT_ID) AS TIMES FROM TEMP_A
			GROUP BY TIME,IOT_ID,NAME
	)
INSERT INTO TABLE_B	
	SELECT TIME,IOT_ID,NAME,TIMES FROM TEMP_B;

区分：

insert into 是直接追加到table

insert overwrite 是覆盖table数据。

好了，坑埋上了。以后的自己记得看哈.