Hadoop之旅--HIVE的一些技巧

（一）字段名称重复
错误提示：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);