(一)字段名称重复
错误提示: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);
分享到:
相关推荐
spark-1.6.3-bin-hadoop2.4-without-hive.tgz 经测试,hadoop 2.8.2下可用。hive2.1.1 可用
-- spark1.6.0 hive on spark的spark包,这个是已经经过./make-distribution.sh --name "hadoop2-without-hive" --tgz "-Pyarn,hadoop-provided,hadoop-2.4,parquet-provided"编译后的了spark-1.6.0-bin-hadoop2-without-...
elasticsearch-hadoop-hive-2.3.4.jar包下载
spark--bin-hadoop2-without-hive.tgz
mongo-hadoop-hive-2.0.0.jar xx cc vvs . dd s . s ww w .
hive 加载mongdb数据所需的三个包mongo-hadoop-core-2.0.2.jar mongo-hadoop-hive-2.0.0.jar mongo-java-driver-3.6.1.jar
Hadoop-2.8.0-HA-Hive安装部署与HQL07.hive.mp4
赠送jar包:hadoop-mapreduce-client-jobclient-2.6.5.jar; 赠送原API文档:hadoop-mapreduce-client-jobclient-2.6.5-javadoc.jar; 赠送源代码:hadoop-mapreduce-client-jobclient-2.6.5-sources.jar; 赠送...
使用maven重新编译spark2.3.1源码,用以实现hive on spark
Eclipse集成Hadoop2.10.0的插件,使用`ant`对hadoop的jar包进行打包并适应Eclipse加载,所以参数里有hadoop和eclipse的目录. 必须注意对于不同的hadoop版本,` HADDOP_INSTALL_PATH/share/hadoop/common/lib`下的jar包...
Hadoop-2.8.0-HA-Hive安装部署与HQL09.hive的基本语法--建表语法.mp4
Hadoop-2.8.0-HA-Hive安装部署与HQL11.hive的基本语法--CTAS建表.mp4
赠送jar包:hadoop-yarn-client-2.6.5.jar; 赠送原API文档:hadoop-yarn-client-2.6.5-javadoc.jar; 赠送源代码:hadoop-yarn-client-2.6.5-sources.jar; 赠送Maven依赖信息文件:hadoop-yarn-client-2.6.5.pom;...
Hadoop-2.8.0-HA-Hive安装部署与HQL06.hive的安装和基本使用.mp4
本JAR包用于把Mongo数据抽取到hive种对表字段的映射,如果没有这个包,是无法映射的
Hadoop-2.8.0-HA-Hive安装部署与HQL08.hive的脚本化运行使用方式.mp4
Hadoop-2.8.0-HA-Hive安装部署与HQL04.hive的基本功能机制和概念.mp4
Hadoop-2.8.0-HA-Hive安装部署与HQL05.mysql的安装详细步骤.mp4
Hadoop-2.8.0-HA-Hive安装部署与HQL10.hive的基本语法--内部表和外部表.mp4
Hadoop-2.8.0-HA-Hive安装部署与HQL12.hive的基本语法--数据导入--从本地--从hdfs.mp4