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

/ 3评 / 10

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


写惯了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数据。

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


3条回应:“Hive踩过的坑-Hive的insert与with as 语句搭配”

  1. 鸟叔说道:

    哇偶,哈哈

  2. 两包辣条说道:

    gp数据库正好相反,insert 语句是要放在 with as 前面的,insert >> with as >> select

  3. z j y说道:

    感谢!!!

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注