(一)字段名称重复
错误提示:Ambiguous column reference property_id
表示property_id字段名称有重复的,如下面的写法:
cast(category_level as string) as property_id,
cast(property_id as string) as property_id,
cast(0 as string) as ipv,
cast(0 as string) as ipvuv,
(二)union all字段名称一致
UNION ALL必须要让union的各个表保持字段名称一样
案例:
cast(category_level as string) as category_id,
cast(property_id as string) as property_id,
cast(0 as string) as ipv,
cast(0 as string) as ipvuv,
是可以的
但是
cast(category_level as string) ,
cast(property_id as string) ,
cast(0 as string) as ipv,
cast(0 as string) as ipvuv,
会报错说字段不匹配
(三)UNION ALL 嵌套错误
错误信息如下:
Hive history file=/tmp/weique.lqf/hive_job_log_weique.lqf_201209041729_460174_1017312942.txt
FAILED: Hive Internal Error: java.lang.NullPointerException(null)
java.lang.NullPointerException
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.generateErrorMessage(SemanticAnalyzer.java:435)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genUnionPlan(SemanticAnalyzer.java:5802)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:6163)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:6160)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:6178)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:6953)
at org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:137)
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:294)
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:392)
at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:177)
at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:257)
at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:389)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.apache.hadoop.util.RunJar.main(RunJar.java:165)
at org.apache.hadoop.mapred.JobShell.run(JobShell.java:54)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:79)
at org.apache.hadoop.mapred.JobShell.main(JobShell.java:68)
over
说明了你的unino all里面有嵌套的union all,这个不知道为什么出现问题,
建议的写法是创建一张临时表用union all,然后union all临时表。
(四)HIVE 中null的处理
hive里面null也是一个字符串,长度为4,在用sum等统计汇总的时候,会得到不一样的结果,
建议用下面的语句,在你建表之后将null和''进行等值处理。
ALTER TABLE tmall_data_fdi_search_query_cat_qp_temp_1 SET SERDEPROPERTIES ('serialization.null.format'='');
这样null和‘’等价
(五)exist in的写法
left semi join是0.5.0以上版本的特性。
可以替代in
SELECT a.key, a.val FROM a LEFT SEMI JOIN b on (a.key = b.key); |
|