插入覆盖目录的Hive Case声明 [英] Hive Case Statement for Insert Overwrite Directory

问题描述

当尝试使用以下逻辑运行HQL脚本时，我收到错误：

  ParseException line 4：0 can not识别'CASE'附近的输入当在serde属性说明中给出'mytable'
  

脚本逻辑

  INSERT OVERWRITE DIRECTORY'/ example / path'
 ROW FORMAT DELIMITED 
 FIELDS TERMINATED BY'，'
 CASE WHEN $ {hiveconf：tbl_name} ='mytable'
然后SELECT * FROM $ {hiveconf：tbl_name} LEFT OUTER JOIN ...; 
当$ {hiveconf：tbl_name} ='mytable2'
那么SELECT * FROM $ {hiveconf：tbl_name} LEFT OUTER JOIN ...; 
 ELSE THEN 
 END 
  

如何正确指定select语句根据我传入的tbl_name参数的值，使用INSERT OVERWRITE DIRECTORY来使用？

更新1：
修改为：

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

出错：

  FAILED：ParseException第9：9行无法识别表达式规范中'*''FROM''mytable'附近的输入
  
 
 
  SELECT * CASE WHEN $ {hiveconf：tbl_name} ='mytable'
然后从$ {hiveconf：tbl_name} ='mytable'
当$ {hiveconf：tbl_name} ='mytable2'
然后从$ {hiveconf：tbl_name} LEFT OUTER JOIN ...; 
 ELSE THEN 
 END 
  
出错：
  FAILED：ParseException行8：9无法识别谓词'CASE'。 Failed rule：'regularBody'in statement 
  
 
 
解决方案
快速解决方案是使用UNION ALL + WHERE。像这样： 
 
 
 插入覆盖目录
 ... 
 select * from mytable其中'$ {hiveconf ：tbl_name}'='mytable'
 union all 
 select * from mytable2其中'$ {hiveconf：tbl_name}'='mytable2'
  
为每个选择添加您的连接。
 
When attempting to run an HQL script with the following logic, I receive the error:
ParseException line 4:0 cannot recognize input near 'CASE' 'WHEN' 'mytable' in serde properties specification 
Script Logic
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
How can I correctly assign a select statement for the INSERT OVERWRITE DIRECTORY to use based on the value of the tbl_name parameter which I am passing in? 

Update 1:
When modifying as:
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
It errors with:
FAILED: ParseException line 9:9 cannot recognize input near '*' 'FROM' 'mytable' in expression specification
Update 2:  When modifying as:
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
It errors with:
FAILED: ParseException line 8:9 Failed to recognize predicate 'CASE'. Failed rule: 'regularBody' in statement

解决方案
Quick solution is to use UNION ALL+WHERE. Like this:
insert overwrite directory
...
select * from mytable where '${hiveconf:tbl_name}'='mytable'
union all
select * from mytable2 where '${hiveconf:tbl_name}'='mytable2'
Add your joins to each select.

                        
这篇关于插入覆盖目录的Hive Case声明的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！