Hive踩过的坑-Hive的insert与with as 语句搭配
本文最后更新于 2043 天前,其中的信息可能已经有所发展或是发生改变。

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


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

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


评论

  1. Windows Chrome 61.0.3163.79
    2018-11-14
    2018-11-14 21:58:54

    哇偶,哈哈

  2. 两包辣条
    Windows Chrome 85.0.4183.102
    2021-7-15
    2021-7-15 11:20:24

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

  3. z j y
    Macintosh Safari 14.1
    2021-7-22
    2021-7-22 16:02:02

    感谢!!!

发送评论 编辑评论


				
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
(╯‵□′)╯︵┴─┴
 ̄﹃ ̄
(/ω\)
∠( ᐛ 」∠)_
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ`)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ( ̄∇ ̄o)
ヾ(´・ ・`。)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò。)
Σ(っ °Д °;)っ
( ,,´・ω・)ノ"(´っω・`。)
╮(╯▽╰)╭
o(*////▽////*)q
>﹏<
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
颜文字
Emoji
小恐龙
花!
上一篇
下一篇