[hive]现有一HQL语句,使用group by,在5000W下使用造成hive瘫痪,求优化 20C

需求是一个表里的两波数据进行匹配去重,单去重不是单条对单挑的,是先把相同的合并,然后在匹配
例如
编号 姓名 年龄
1 小六 null
1 小七 18

然后新进来的数据与上面的去比较查重
编号 姓名 年龄
1 小六 18

以编号相同为前提查重,这条数据的结果应该是完全重复
我的想法是先将数据库的相同编号的数据group by合并,然后将他们的其他字段保存为数组,然后转为字符串,最后join的时候看看新数据是否包含在原来的字段里

然后写了这个语句,刚开始测试没问题,可以用,后来底层查重的数据变多,5000w,hive经常会死掉,求优化

(join左边的表数据全部的都是不重复的,但是右边的表有重复)
(数据都在一章表里,我通过type来划分成两部分的)

 select t.num from test.data_test as t
inner join (select num,concat_ws(",",collect_set(t1.username)) as username,concat_ws(",",collect_set(t1.gender)) as gender,concat_ws(",",collect_set(cast(t1.age as string))) as age,concat_ws(",",collect_set(t1.city_short)) as city_short from test.data_test t1 
where t1.type != 'ty1'
group by t1.num)t2 
on t.num = t2.num
where t.type = "ty1"
and ((instr(t2.username,t.username) > 0) or t.username is null)
and ((instr(t2.gender,t.gender) > 0) or t.gender is null)
and ((instr(t2.age,cast(t.age as string)) > 0) or t.age is null)
and ((instr(t2.city_short,t.city_short) > 0) or t.city_short is null)

或者请指点这里面那里的操作会很很耗费性能,我们是单线程操作,没有并发

0

1个回答

建议你在数据库层进行操作

0
u011856283
你好杰米 不懂
大约一年之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
其他相关推荐
hive高阶1--sql和hive语句执行顺序、explain查看执行计划、group by生成MR
hive语句执行顺序msyql语句执行顺序代码写的顺序:select ... from... where.... group by... having... order by.. 或者 from ... select ... 代码的执行顺序:from... where...group by... having.... select ... order by... hive 语句执行顺序大致
关于hive中的count与group by的总结
Group By语句从英文的字面意义上理解就是“根据(by)一定的规则进行分组(Group)”。它的作用是通过一定的规则将一个数据集划分成若干个小的区域,然后针对若干个小区域进行数据处理。  注意:group by 是先排序后分组;  count :. 1、 并不是说group by和count()操作不能同时使用,只是说查出来的东西不一样,每个都有其应用的情况,如本例:最开始方法查出来的就...
hive语句优化-通过groupby实现distinct(数据量特别大的时候,使用distinct去重容易导致数据倾斜)
hive语句优化-通过groupby实现distinct  同事写了个hive的sql语句,执行效率特别慢,跑了一个多小时程序只是map完了,reduce进行到20%。 该Hive语句如下: select count(distinct ip)  from (select ip as ip from comprehensive.f_client_boot_daily where
Hadoop学习(十三)——hive的常用HQL语句
笔者是一个痴迷于挖掘数据中的价值的学习人,希望在平日的工作学习中,挖掘数据的价值,找寻数据的秘密,笔者认为,数据的价值不仅仅只体现在企业中,个人也可以体会到数据的魅力,用技术力量探索行为密码,让大数据助跑每一个人,欢迎直筒们关注我的公众号,大家一起讨论数据中的那些有趣的事情。 我的公众号为:livandata
hive sql优化(全排序,笛卡尔积,exist in,决定reducer个数,合并MapReduce)
hive 全排序 优化 分类: hive hadoop hadoop 2013-01-28 20:11 717人阅读 评论(0) 收藏 举报 hive hadoop 目录(?)[+] 使用Hive可以高效而又快速地编写复杂的MapReduce查询逻辑。但是某些情况下,因为不熟悉数据特性,或没有遵循Hive的优化约定,Hive计算任务会变得非常低效,甚至无法得到结果。一个”好”的Hive程...
(九)Hive的5个面试题
(九)Hive的5个面试题 目录 一、求单月访问次数和总访问次数 1、数据说明 2、数据准备 3、结果需求 4、需求分析 二、学生课程成绩  1、说明 2、需求 三、求每一年最大气温的那一天  + 温度  1、说明 2、 需求 3、解析 四、求学生选课情况 1、数据说明 2、数据准备 3、需求 4、解析 五、求月销售额和总销售额 ...
【图文详细 】Hive 优化策略 之 Group By 优化
-
hive hql语句高级用法
一,order by ,sort by,distribute by 的用法 1)order by 对全局数据的一个排序,仅仅只有一个reduce工作,最好不用。 2)sort by 对每一个reduce 内部数据进行排序,全局结果集来说不是排序。 set mapreduce.job.reuces = 3 ; 会生成3个文件,每个文件内都是排好序的。 3)distribute by 分...
hive语句优化-通过groupby实现distinct
hive语句优化-通过groupby实现distinct
Hive的HQL语句及数据倾斜解决方案
[版权申明:本文系作者原创,转载请注明出处]文章出处:http://blog.csdn.net/sdksdk0/article/details/51675005作者: 朱培          ID:sdksdk0Hive环境的搭建在这里也不重复说了,安装配置可以查看我的这篇文章:http://blog.csdn.net/sdksdk0/article/details/51512031。在这里主要是
【Hive十一】Hive数据倾斜优化
什么是Hive数据倾斜问题   操作:join,group by,count distinct 现象:任务进度长时间维持在99%(或100%),查看任务监控页面,发现只有少量(1个或几个)reduce子任务未完成;查看未完成的子任务,可以看到本地读写数据量积累非常大,通常超过10GB可以认定为发生数据倾斜。 原因:key分布不均匀 倾斜度衡量:平均记录数超过50w且最大记录数是超过...
Hive总结篇及Hive的优化
概述 Hive学习也有一段时间了,今天来对Hive进行一个总结,也是对自己的总结,作者还是个小白,有不对的地方请大家指出相互学习,共同进步。今天来谈一谈什么是Hive,产生背景,优势等一系列问题。 什么是Hive 老规矩:官网地址 Hive wiki. 先来谈谈自己的理解: 有些人可能会说Hive不就是写SQL的吗,那我们其实可以从另一个角度来理解:Hive很强大,只要写SQL就能解...
Hive的体系结构之HQL的执行过程
一 一条HQL语句如何在hive中进行查询 解释器、编译器、优化器完成HQL查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在HDFS中,并在随后有MapReduce调用执行。     二 怎样查看oracle的执行计划 1、不创建索引的执行计划   2、创建索引的执行计划     Hive的执行计划和oracle的执行计划类似
HiveQL基础语法(聚合,分组函数)
1. 聚合函数 通俗说,聚合函数是将多行数据导出一条结果 常用的聚合函数有:max,min,count,sum,avg 对emp表查询员工的最大、最小、平均工资及所有工资的和 1. hive> select * from emp; 2. OK 3. 7369 SMITH CLERK 7902 1980/12/17 800.0 ...
hive 语句执行顺序
hive语句执行顺序 msyql语句执行顺序 代码写的顺序: select … from… where…. group by… having… order by.. 或者 from … select … 1 2 3 4 代码的执行顺序: from… where…group by… having…. select … order by… 1 2 hive 语句执...
Hive之——Hive SQL优化
一、Hive优化目标在有限的资源下,提高执行效率二、Hive执行HQL——> Job——> Map/Reduce三、执行计划查看执行计划explain [extended] hql四、Hive表优化1、分区静态分区动态分区set hive.exec.dynamic.partition=true; set hive.exec.dynamic.partition.mode=nonstric...
hive中sql优化解决策略
友情提示:更多有关大数据、人工智能方面技术文章请关注博主个人微信公众号:高级大数据架构师! Hive中的sql优化 如果是按时间分区的表,查询时一定要使用分区限制,如果没有分区限制,会从该表的所有数据里面遍历。   注意sql中or的使用,or 这个逻辑必须单独括起来,否则可能引起无分区限制,下面举个例子,想查询到的是gd或gx的某天的数据。 Select x from t where ds=...
Hive体系结构之HQL执行过程
一条HQL语句如何在hive中进行查询? 解析器、编译器、优化器完成HQL查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在HDFS中,并在随后的MapReduce调用执行。 Oracle的执行计划: 例如: 全表扫描代价大些,可以先创建索引。 --先创建索引 create index myindex on emp(deptno); --
hive优化及数据倾斜总结
在对hive进行优化之前应理解mapreduce的原理 map task 程序会根据InputFormat将输入文件分割成splits,每个split会作为一个map task的输入,每个map task会有一个内存缓冲区,输入数据经过map阶段处理后的中间结果以及Partition结果都序列化成字节数组写入到缓冲区,而整个内存缓冲区就是一个字节数组。缓冲区的作用:批量收集map结果,减少...
hive执行语句优化
一、类SQL语句优化 1、跟SQL基本一致的优化原则 1.1 尽量原子化操作 尽量避免一个SQL包含复杂逻辑,可以使用中间表来完成复杂的逻辑。 1.2 尽量尽早地过滤数据 尽量先where筛选后再join,减少每个阶段的数据量,对于分区表要加分区条件,只选择需要使用到的字段。 1.3 表链接时尽量使用相同的连接键 当对3个或者更多个表进行join连接时,如果每个on子...
Hive入门(四):HQL查询语句(一)
select 查询显示表名 查询所有列 查询指定列并指定列别名 查询集合数据类型列 查询Array元素 查询Map元素 查询Struct元素 查询计算列 算数运算符 运算函数 数学函数 聚合函数 表生成函数 Limit函数 嵌套select 语句 case语句 where语句 浮点数比较问题 模糊匹配 like rlike group by select 首先可...
hive:(group by, having;order by)的使用;group by+多个字段,以及wiki说的group by两种使用限制验证
hive上 查询 hive> select * from app_data_stats_historical where os='1' group by dt limit 100; 出现结果如下: 2014-01-01 2014-01-06 2014-01-13 2014-01-20 2014-01-27 2014-02-01 2014-02-03 2014-02-04 20
hive学习3:hive常用HQL语句
show databases; show tables; desc test; #删除表 drop table t_buck; #请表数据 truncate table t_buck; ------------- 分桶表示例: #创建分桶表 drop table stu_buck; create table stu_buck(Sno int,Sname string,Sex string,Sa...
Hive(10):hive支持常用HQL语句
1、过滤 (1)where:select,update,delete,insert into  select * from emp where sal > 3000; (2)limit    select * from emp limit 1; (3)distinct,count/group by      select distinct deptno from emp; ...
hiveSQL优化思路
        主要涉及到数据存储和计算两个过程,设计出合理的数据存储格式对于数据的查询和计算具有很重要的意义。存储的优化思想就是查询数据时能够很快定位到需要的数据,通过索引技术或者缩小检索数据范围来解决;传统数据库领域通过采用索引技术来优化数据的存储达到高效检索访问,在hive数仓技术中也有索引技术,但是,最常用的技术是分区和分桶技术。        表存储思想:根据表字段变更的频率进行水平拆分...
HiveQL常用查询语句where、group by、having、join子句记录
由于hivesql中查询语句时,常用查询条件比较多,所以专门写一篇博文对HiveQL的查询语句进行总结,理清联系和区别。 目录 1. where子句(不能跟别名) (1)比较运算符 (2)like和rlike(正则匹配) (3)逻辑运算符and or not 2. group by子句(每...) 3. having子句(只用于group by之后) 4. join子句(A joi...
Hive SQL的优化
本章只是从HQL层面介绍一下,日常开发HQL中需要注意的一些优化点,不涉及Hadoop层面的参数、配置等优化。 1 使用分区剪裁、列剪裁 在SELECT中,只拿需要的列,如果有,尽量使用分区过滤,少用SELECT *。 在分区剪裁中,当使用外关联时,如果将副表的过滤条件写在Where后面,那么就会先全表关联,之后再过滤,比如: SELECT a.id FROM lxw1234_a
Hive练习-面试题
题目1、该题目的两个核心思路:1、自连接2、倒推法现有这么一批数据,现要求出: 每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数 三个字段的意思: 用户名,月份,访问次数 A,2015-01,5 A,2015-01,15 B,2015-01,5 A,2015-01,8 B,2015-01,25 A,2015-01,5 A,2015-02,4 A,2015-02,6 B,2015...
【性能优化】Hive优化
1.概述   继续《那些年使用Hive踩过的坑》一文中的剩余部分,本篇博客赘述了在工作中总结Hive的常用优化手段和在工作中使用Hive出现的问题。下面开始本篇文章的优化介绍。 2.介绍   首先,我们来看看Hadoop的计算框架特性,在此特性下会衍生哪些问题? 数据量大不是问题,数据倾斜是个问题。jobs数比较多的作业运行效率相对比较低,比如即使有几百行的表,如果多次关联
2001-使用Hive+MR统计分析网站指标
使用Hive+MR统计分析网站指标
Hive优化及优化实例
Hive优化 Hive优化实例
Hive SQL调优总结
Hive SQL调优总结 本文参考链接(总结的很棒很全面) 这里只是总结了一下HQL上面的一些优化点,并不考虑Hadoop层面的参数、配置优化 目录 Hive SQL调优总结 目录 使用分区剪裁、列剪裁 少用count(distinct) 多对多的关联 合理使用MapJoin 合理使用Union ALL 并行执行job 使用本地MR 合理使用动态分区 避免数据倾斜 控制Map数...
解决hql子查询不能用max聚合函数的问题
hibernate框架下想查出日志表最新的一条记录,因为主键id是递增的,所以用子查询选出表中最大的id作为主查询的条件,代码如下: public class MonitorDAO extends HibernateEntityDao { public TLog findLatestLog() { String sql = "from TLog where id=(select max(id
hive使用rank实现topN的查询
前置条件: 安装好hive背景: hive表中存有cookieid和time两个字段 目标: 通过rank方法查出每个cookieid在哪一天的次数最多具体步骤如下: 一、创建表CREATE EXTERNAL TABLE tmp_dh_topN ( cookieid string, vtime string --day ) ROW FORMAT DELIMITED FIELDS TERMI
hive 中显示创建的hql语句及 desc 命令实战
1、显示创建的hql语句 show tables; show create table student_info; hive (default)> show create table student_info; OK createtab_stmt CREATE TABLE `student_info`(   `s_id` int,    `course` string,
hive 练习题 求学生选课情况
1、数据说明id course 1,a 1,b 1,c 1,e 2,a 2,c 2,d 2,f 3,a 3,b 3,c 3,e(2)字段含义表示有id为1,2,3的学生选修了课程a,b,c,d,e,f中其中几门。建表语句create table t_course(id int,course string) row format delimited fields termin...
Hive的hql命令的三种执行方式
Hive的hql命令的三种执行方式: 1、CLI 方式直接执行 shell 中键入hive,即可启动hive的cli交互模式 2、作为字符串通过shell调用hive –e执行(-S开启静默,去掉”OK”,”Time taken”) hql作为字符串在shell脚本中执行,如 hive -e "use ${database};select * from tb" ...
Hive常用hql语句(分桶和join操作)&&Hive自定义函数UDF和Transform
show databases;show tables;desc test;-------------分桶表示例:#创建分桶表drop table stu_buck;create table stu_buck(Sno int,Sname string,Sex string,Sage int,Sdept string)clustered by(Sno) sorted by(Sno DESC)into ...
06-hive面试题
一、求单月访问次数和总访问次数1、数据说明数据字段说明用户名,月份,访问次数数据格式A,2015-01,5A,2015-01,15B,2015-01,5A,2015-01,8B,2015-01,25A,2015-01,5A,2015-02,4A,2015-02,6B,2015-02,10B,2015-02,5A,2015-03,16A,2015-03,22B,2015-03,23B,2015-03...
Hive性能比较之in的使用和join的使用
问题:已知老用户表B,求解浏览表A里有多少用户是老用户? 说明: A表有130万不重复浏览用户,B表也是百万级。 方法一: select count(*) from (select pin from A group by pin) a join (select pin from B group by pin) b 出现了数据倾斜,卡在一个reducer一直跑不出来。 方...
文章热词 机器学习教程 Objective-C培训 交互设计视频教程 颜色模型 设计制作学习
相关热词 mysql关联查询两次本表 native底部 react extjs glyph 图标 大数据hive学习 hive大数据教程