hadoop不怕数据量大,最怕数据倾斜数据倾斜,
我到过的处理的方式
1.mapjoin方式
/*+ MAPJOIN(c,d,e,f) */
其中c,d,e,f是你小表,也就是说可能会倾斜数据的表;
但是对于join,在判断小表不大于1G的情况下,使用map join,也就是要考虑c,d,e,f等表的大小,不能超过内存限制,否则会出现OOM错误;
2.控制空值分布
select
'${date}' as thedate,
a.search_type,
a.query,
a.category,
a.cat_name,
a.brand_id,
a.brand_name,
a.dir_type,
a.rewcatid,
a.new_cat_name,
a.new_brand_id,
f.brand_name as new_brand_name,
a.pv,
a.uv,
a.ipv,
a.ipvuv,
a.trans_amt,
a.trans_num,
a.alipay_uv
from fdi_search_query_cat_qp_temp a
left outer join brand f
on
f.pt='${date}000000'
and case when a.new_brand_id is null then concat('hive',rand() ) else a.new_brand_id end = f.brand_id;
这样的写法把空值的 key 变成一个字符串加上随机数,就能把倾斜的数据分到不同的reduce上 ,解决数据倾斜问题。
如果上述的方法还不能解决,比如当有多个JOIN的时候,建议建立临时表,然后拆分HIVE SQL语句;
关于数据倾斜,阿里集团数据平台上的博客文章有很好的几个方法,敢兴趣的人也可以去看一下:
http://www.tbdata.org/archives/2109
3.关于nonstrict
join同样一张表多次的时候,会出现这样的错误信息:
FAILED: Error in semantic analysis: In strict mode, cartesian product is not allowed. If you really want to perform the operation, set hive.mapred.mode=nonstrict
解决方式是在SQL前面加上如下:
set hive.mapred.mode=nonstrict;
strict模式在下面三种情况下有限制:
(1) partition表需要加上分区裁剪
(2) order by 只有一个reduce,需要加上limit
(3) join时,如果只有一个reduce,笛卡尔积不支持。
HIVE小技巧:
1.hive sql中:
sum(t.shop_gmvcount + t.GMVCOUNT_NEW + t.auc_shop_gmvcount + t.spu_gmv_cnt) gmv_cnt,
这样的统计结果,当t.t.shop_gmvcount为NULL时,即使后面的t.GMVCOUNT_NEW 不为null,那么总计的结果这个计算仍然是NULL;
修改的方法是:采用sum(coalesce(t.shop_gmvcount,cast(0 as bigint)) + coalesce(t.GMVCOUNT_NEW,cast(0 as bigint))
这样的方式,coalesce函数类似于ORACLE数据库里面的nvl
2。join中where的过滤,on里面才能起到表的过滤,放在where里面起不到提前过滤的情况;
3.left semi jioin的使用
LEFT SEMI JOIN 是 IN/EXISTS 子查询的一种更高效的实现。Hive 当前没有实现 IN/EXISTS 子查询,所以你可以用 LEFT SEMI JOIN 重写你的子查询语句。LEFT SEMI JOIN 的限制是, JOIN 子句中右边的表只能在 ON 子句中设置过滤条件,在 WHERE 子句、SELECT 子句或其他地方过滤都不行。
分享到:
相关推荐
赠送jar包:hadoop-mapreduce-client-core-2.5.1.jar; 赠送原API文档:hadoop-mapreduce-client-core-2.5.1-javadoc.jar; 赠送源代码:hadoop-mapreduce-client-core-2.5.1-sources.jar; 赠送Maven依赖信息文件:...
赠送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; 赠送...
Eclipse集成Hadoop2.10.0的插件,使用`ant`对hadoop的jar包进行打包并适应Eclipse加载,所以参数里有hadoop和eclipse的目录. 必须注意对于不同的hadoop版本,` HADDOP_INSTALL_PATH/share/hadoop/common/lib`下的jar包...
赠送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;...
赠送jar包:hadoop-yarn-common-2.6.5.jar 赠送原API文档:hadoop-yarn-common-2.6.5-javadoc.jar 赠送源代码:hadoop-yarn-common-2.6.5-sources.jar 包含翻译后的API文档:hadoop-yarn-common-2.6.5-javadoc-...
赠送jar包:hadoop-mapreduce-client-common-2.6.5.jar; 赠送原API文档:hadoop-mapreduce-client-common-2.6.5-javadoc.jar; 赠送源代码:hadoop-mapreduce-client-common-2.6.5-sources.jar; 赠送Maven依赖信息...
hadoop-annotations-3.1.1.jar hadoop-common-3.1.1.jar hadoop-mapreduce-client-core-3.1.1.jar hadoop-yarn-api-3.1.1.jar hadoop-auth-3.1.1.jar hadoop-hdfs-3.1.1.jar hadoop-mapreduce-client-hs-3.1.1.jar ...
hadoop-eclipse-plugin-2.7.3和2.7.7的jar包 hadoop-eclipse-plugin-2.7.3和2.7.7的jar包 hadoop-eclipse-plugin-2.7.3和2.7.7的jar包 hadoop-eclipse-plugin-2.7.3和2.7.7的jar包
赠送jar包:hadoop-yarn-server-resourcemanager-2.6.0.jar; 赠送原API文档:hadoop-yarn-server-resourcemanager-2.6.0-javadoc.jar; 赠送源代码:hadoop-yarn-server-resourcemanager-2.6.0-sources.jar; 赠送...
hadoop-eclipse-plugin-1.2.1hadoop-eclipse-plugin-1.2.1hadoop-eclipse-plugin-1.2.1hadoop-eclipse-plugin-1.2.1
赠送jar包:hadoop-yarn-api-2.5.1.jar; 赠送原API文档:hadoop-yarn-api-2.5.1-javadoc.jar; 赠送源代码:hadoop-yarn-api-2.5.1-sources.jar; 赠送Maven依赖信息文件:hadoop-yarn-api-2.5.1.pom; 包含翻译后...
hadoop-eclipse-plugin-3.1.1, hadoop eclipse 插件 3.1.1
hadoop-eclipse-plugin-2.7.4.jar和hadoop-eclipse-plugin-2.7.3.jar还有hadoop-eclipse-plugin-2.6.0.jar的插件都在这打包了,都可以用。
赠送jar包:hadoop-hdfs-client-2.9.1.jar 赠送原API文档:hadoop-hdfs-client-2.9.1-javadoc.jar 赠送源代码:hadoop-hdfs-client-2.9.1-sources.jar 包含翻译后的API文档:hadoop-hdfs-client-2.9.1-javadoc-...
hadoop-eclipse-plugin-3.1.3,eclipse版本为eclipse-jee-2020-03
赠送jar包:hadoop-hdfs-client-2.9.1.jar; 赠送原API文档:hadoop-hdfs-client-2.9.1-javadoc.jar; 赠送源代码:hadoop-hdfs-client-2.9.1-sources.jar; 赠送Maven依赖信息文件:hadoop-hdfs-client-2.9.1.pom;...
hadoop-common-2.2.0-bin-master(包含windows端开发Hadoop和Spark需要的winutils.exe),Windows下IDEA开发Hadoop和Spark程序会报错,原因是因为如果本机操作系统是windows,在程序中使用了hadoop相关的东西,比如写入...
flink-shaded-hadoop-2-uber-2.7.5-10.0.jar
# 解压命令 tar -zxvf flink-shaded-hadoop-2-uber-3.0.0-cdh6.2.0-7.0.jar.tar.gz # 介绍 用于CDH部署 Flink所依赖的jar包
hadoop-mapreduce-examples-2.7.1.jar