hive case语句

ulmd4ohb  于 2021-06-02  发布在  Hadoop
关注(0)|答案(1)|浏览(611)

尝试使用以下逻辑运行hql脚本时,收到错误:

ParseException line 4:0 cannot recognize input near 'CASE' 'WHEN' 'mytable' in serde properties specification

脚本逻辑

INSERT OVERWRITE DIRECTORY '/example/path'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
CASE WHEN ${hiveconf:tbl_name}='mytable'
THEN SELECT * FROM ${hiveconf:tbl_name} LEFT OUTER JOIN ...;
WHEN ${hiveconf:tbl_name}='mytable2'
THEN SELECT * FROM ${hiveconf:tbl_name} LEFT OUTER JOIN ...;
ELSE THEN
END

如何根据传入的tbl\u name参数的值为insert overwrite目录正确分配select语句?
更新1:修改为时:

SELECT CASE WHEN ${hiveconf:tbl_name}='mytable'
THEN * FROM WHEN ${hiveconf:tbl_name}='mytable'
WHEN ${hiveconf:tbl_name}='mytable2'
THEN * FROM ${hiveconf:tbl_name} LEFT OUTER JOIN ...;
ELSE THEN
END

它的错误是:

FAILED: ParseException line 9:9 cannot recognize input near '*' 'FROM' 'mytable' in expression specification

更新2:修改为时:

SELECT * CASE WHEN ${hiveconf:tbl_name}='mytable'
THEN FROM WHEN ${hiveconf:tbl_name}='mytable'
WHEN ${hiveconf:tbl_name}='mytable2'
THEN FROM ${hiveconf:tbl_name} LEFT OUTER JOIN ...;
ELSE THEN
END

它的错误是:

FAILED: ParseException line 8:9 Failed to recognize predicate 'CASE'. Failed rule: 'regularBody' in statement
nbewdwxp

nbewdwxp1#

快速解决方法是使用union all+where。这样地:

insert overwrite directory
...
select * from mytable where '${hiveconf:tbl_name}'='mytable'
union all
select * from mytable2 where '${hiveconf:tbl_name}'='mytable2'

将连接添加到每个选择。

相关问题