HIVE中join、semi join、outer join举例详解

2024-06-12 07:38
文章标签 详解 举例 join hive outer semi

本文主要是介绍HIVE中join、semi join、outer join举例详解,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

HIVE中join、semi join、outer join举例详解

举例子:

hive> select * from zz0; 
111111 
222222 
888888 
hive> select * from zz1; 
111111 
333333 
444444 
888888

hive> select * from zz0 join zz1 on zz0.uid = zz1.uid; 
111111  111111 
888888  888888 
hive> select * from zz0 left outer join zz1 on zz0.uid = zz1.uid; 
111111  111111 
222222  NULL 
888888  888888 
hive> select * from zz0 right outer join zz1 on zz0.uid = zz1.uid; 
NULL 
111111  111111 
NULL    333333 
NULL    444444 
888888  888888 
hive> select * from zz0 full outer join zz1 on zz0.uid = zz1.uid; 
NULL 
111111  111111 
222222  NULL 
NULL    333333 
NULL    444444 
888888  888888 
hive> select * from zz0 left semi join zz1 on zz0.uid = zz1.uid; 
111111  111111 
888888  888888

 

 

写好Hive 程序的五个提示

使用Hive可以高效而又快速地编写复杂的MapReduce查询逻辑。但是某些情况下,因为不熟悉数据特性,或没有遵循Hive的优化约定,Hive计算任务会变得非常低效,甚至无法得到结果。一个”好”的Hive程序仍然需要对Hive运行机制有深入的了解。

有一些大家比较熟悉的优化约定包括:Join中需要将大表写在靠右的位置;尽量使用UDF而不是transfrom……诸如此类。下面讨论5个性能和逻辑相关的问题,帮助你写出更好的Hive程序。

全排序

Hive的排序关键字是SORT BY,它有意区别于传统数据库的ORDER BY也是为了强调两者的区别–SORT BY只能在单机范围内排序。考虑以下表定义:

CREATE TABLE if not exists t_order(id int, -- 订单编号sale_id int, -- 销售IDcustomer_id int, -- 客户IDproduct _id int, -- 产品IDamount int -- 数量) PARTITIONED BY (ds STRING);

在表中查询所有销售记录,并按照销售ID和数量排序:

set mapred.reduce.tasks=2;Select sale_id, amount from t_orderSort by sale_id, amount;

这一查询可能得到非期望的排序。指定的2reducer分发到的数据可能是(各自排序):

Reducer1

Sale_id | amount0 | 1001 | 301 | 502 | 20

Reducer2

Sale_id | amount0 | 1100 | 1203 | 504 | 20

因为上述查询没有reduce keyhive会生成随机数作为reduce key。这样的话输入记录也随机地被分发到不同reducer机器上去了。为了保证reducer之间没有重复的sale_id记录,可以使用DISTRIBUTE BY关键字指定分发keysale_id。改造后的HQL如下:

set mapred.reduce.tasks=2;Select sale_id, amount from t_orderDistribute by sale_idSort by sale_id, amount;

这样能够保证查询的销售记录集合中,销售ID对应的数量是正确排序的,但是销售ID不能正确排序,原因是hive使用hadoop默认的HashPartitioner分发数据。

这就涉及到一个全排序的问题。解决的办法无外乎两种:

1.) 不分发数据,使用单个reducer

set mapred.reduce.tasks=1;

这一方法的缺陷在于reduce端成为了性能瓶颈,而且在数据量大的情况下一般都无法得到结果。但是实践中这仍然是最常用的方法,原因是通常排序的查询是为了得到排名靠前的若干结果,因此可以用limit子句大大减少数据量。使用limit n后,传输到reduce端(单机)的数据记录数就减少到n* (map个数)。

2.) 修改Partitioner,这种方法可以做到全排序。这里可以使用Hadoop自带的TotalOrderPartitioner(来自于Yahoo!TeraSort项目),这是一个为了支持跨reducer分发有序数据开发的Partitioner,它需要一个SequenceFile格式的文件指定分发的数据区间。如果我们已经生成了这一文件(存储在/tmp/range_key_list,分成100reducer),可以将上述查询改写为

set mapred.reduce.tasks=100;set hive.mapred.partitioner=org.apache.hadoop.mapred.lib.TotalOrderPartitioner;set total.order.partitioner.path=/tmp/ range_key_list;Select sale_id, amount from t_orderCluster by sale_idSort by amount;

有很多种方法生成这一区间文件(例如hadoop自带的o.a.h.mapreduce.lib.partition.InputSampler工具)。这里介绍用Hive生成的方法,例如有一个按id有序的t_sale表:

CREATE TABLE if not exists t_sale (id int,name string,loc string);

则生成按sale_id分发的区间文件的方法是:

create external table range_keys(sale_id int)row format serde'org.apache.hadoop.hive.serde2.binarysortable.BinarySortableSerDe'stored asinputformat'org.apache.hadoop.mapred.TextInputFormat'outputformat'org.apache.hadoop.hive.ql.io.HiveNullValueSequenceFileOutputFormat'location '/tmp/range_key_list'; insert overwrite table range_keysselect distinct sale_idfrom source t_sale sampletable(BUCKET 100 OUT OF 100 ON rand()) ssort by sale_id;

生成的文件(/tmp/range_key_list目录下)可以让TotalOrderPartitionersale_id有序地分发reduce处理的数据。区间文件需要考虑的主要问题是数据分发的均衡性,这有赖于对数据深入的理解。

怎样做笛卡尔积?

Hive设定为严格模式(hive.mapred.mode=strict)时,不允许在HQL语句中出现笛卡尔积,这实际说明了Hive对笛卡尔积支持较弱。因为找不到Join keyHive只能使用1reducer来完成笛卡尔积。

当然也可以用上面说的limit的办法来减少某个表参与join的数据量,但对于需要笛卡尔积语义的需求来说,经常是一个大表和一个小表的Join操作,结果仍然很大(以至于无法用单机处理),这时MapJoin才是最好的解决办法。

MapJoin,顾名思义,会在Map端完成Join操作。这需要将Join操作的一个或多个表完全读入内存。

MapJoin的用法是在查询/子查询的SELECT关键字后面添加/*+ MAPJOIN(tablelist) */提示优化器转化为MapJoin(目前Hive的优化器不能自动优化MapJoin)。其中tablelist可以是一个表,或以逗号连接的表的列表。tablelist中的表将会读入内存,应该将小表写在这里。

PS:有用户说MapJoin在子查询中可能出现未知BUG。在大表和小表做笛卡尔积时,规避笛卡尔积的方法是,给Join添加一个Join key,原理很简单:将小表扩充一列join key,并将小表的条目复制数倍,join key各不相同;将大表扩充一列join key为随机数。

怎样写exist in子句?

Hive不支持where子句中的子查询,SQL常用的exist in子句需要改写。这一改写相对简单。考虑以下SQL查询语句:

SELECT a.key, a.valueFROM aWHERE a.key in(SELECT b.keyFROM B);

可以改写为

SELECT a.key, a.valueFROM a LEFT OUTER JOIN b ON (a.key = b.key)WHERE b.key <> NULL;

一个更高效的实现是利用left semi join改写为:

SELECT a.key, a.valFROM a LEFT SEMI JOIN b on (a.key = b.key);

left semi join0.5.0以上版本的特性。

Hive怎样决定reducer个数?

Hadoop MapReduce程序中,reducer个数的设定极大影响执行效率,这使得Hive怎样决定reducer个数成为一个关键问题。遗憾的是Hive的估计机制很弱,不指定reducer个数的情况下,Hive会猜测确定一个reducer个数,基于以下两个设定:

1. hive.exec.reducers.bytes.per.reducer(默认为1000^3

2. hive.exec.reducers.max(默认为999

计算reducer数的公式很简单:

N=min(参数2,总输入数据量/参数1)

通常情况下,有必要手动指定reducer个数。考虑到map阶段的输出数据量通常会比输入有大幅减少,因此即使不设定reducer个数,重设参数2还是必要的。依据Hadoop的经验,可以将参数2设定为0.95*(集群中TaskTracker个数)

 

合并MapReduce操作

Multi-group by

Multi-group byHive的一个非常好的特性,它使得Hive中利用中间结果变得非常方便。例如,

FROM (SELECT a.status, b.school, b.genderFROM status_updates a JOIN profiles bON (a.userid = b.userid anda.ds='2009-03-20' )) subq1INSERT OVERWRITE TABLE gender_summaryPARTITION(ds='2009-03-20')SELECT subq1.gender, COUNT(1) GROUP BY subq1.genderINSERT OVERWRITE TABLE school_summaryPARTITION(ds='2009-03-20')SELECT subq1.school, COUNT(1) GROUP BY subq1.school

上述查询语句使用了Multi-group by特性连续group by2次数据,使用不同的group by key。这一特性可以减少一次MapReduce操作。

 

Multi-distinct

Multi-distinct是淘宝开发的另一个multi-xxx特性,使用Multi-distinct可以在同一查询/子查询中使用多个distinct,这同样减少了多次MapReduce操作。

摘自:http://www.alidata.org/archives/622


这篇关于HIVE中join、semi join、outer join举例详解的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1053593

相关文章

Spring Security基于数据库验证流程详解

Spring Security 校验流程图 相关解释说明(认真看哦) AbstractAuthenticationProcessingFilter 抽象类 /*** 调用 #requiresAuthentication(HttpServletRequest, HttpServletResponse) 决定是否需要进行验证操作。* 如果需要验证,则会调用 #attemptAuthentica

OpenHarmony鸿蒙开发( Beta5.0)无感配网详解

1、简介 无感配网是指在设备联网过程中无需输入热点相关账号信息,即可快速实现设备配网,是一种兼顾高效性、可靠性和安全性的配网方式。 2、配网原理 2.1 通信原理 手机和智能设备之间的信息传递,利用特有的NAN协议实现。利用手机和智能设备之间的WiFi 感知订阅、发布能力,实现了数字管家应用和设备之间的发现。在完成设备间的认证和响应后,即可发送相关配网数据。同时还支持与常规Sof

6.1.数据结构-c/c++堆详解下篇(堆排序,TopK问题)

上篇:6.1.数据结构-c/c++模拟实现堆上篇(向下,上调整算法,建堆,增删数据)-CSDN博客 本章重点 1.使用堆来完成堆排序 2.使用堆解决TopK问题 目录 一.堆排序 1.1 思路 1.2 代码 1.3 简单测试 二.TopK问题 2.1 思路(求最小): 2.2 C语言代码(手写堆) 2.3 C++代码(使用优先级队列 priority_queue)

K8S(Kubernetes)开源的容器编排平台安装步骤详解

K8S(Kubernetes)是一个开源的容器编排平台,用于自动化部署、扩展和管理容器化应用程序。以下是K8S容器编排平台的安装步骤、使用方式及特点的概述: 安装步骤: 安装Docker:K8S需要基于Docker来运行容器化应用程序。首先要在所有节点上安装Docker引擎。 安装Kubernetes Master:在集群中选择一台主机作为Master节点,安装K8S的控制平面组件,如AP

嵌入式Openharmony系统构建与启动详解

大家好,今天主要给大家分享一下,如何构建Openharmony子系统以及系统的启动过程分解。 第一:OpenHarmony系统构建      首先熟悉一下,构建系统是一种自动化处理工具的集合,通过将源代码文件进行一系列处理,最终生成和用户可以使用的目标文件。这里的目标文件包括静态链接库文件、动态链接库文件、可执行文件、脚本文件、配置文件等。      我们在编写hellowor

LabVIEW FIFO详解

在LabVIEW的FPGA开发中,FIFO(先入先出队列)是常用的数据传输机制。通过配置FIFO的属性,工程师可以在FPGA和主机之间,或不同FPGA VIs之间进行高效的数据传输。根据具体需求,FIFO有多种类型与实现方式,包括目标范围内FIFO(Target-Scoped)、DMA FIFO以及点对点流(Peer-to-Peer)。 FIFO类型 **目标范围FIFO(Target-Sc

019、JOptionPane类的常用静态方法详解

目录 JOptionPane类的常用静态方法详解 1. showInputDialog()方法 1.1基本用法 1.2带有默认值的输入框 1.3带有选项的输入对话框 1.4自定义图标的输入对话框 2. showConfirmDialog()方法 2.1基本用法 2.2自定义按钮和图标 2.3带有自定义组件的确认对话框 3. showMessageDialog()方法 3.1

脏页的标记方式详解

脏页的标记方式 一、引言 在数据库系统中,脏页是指那些被修改过但还未写入磁盘的数据页。为了有效地管理这些脏页并确保数据的一致性,数据库需要对脏页进行标记。了解脏页的标记方式对于理解数据库的内部工作机制和优化性能至关重要。 二、脏页产生的过程 当数据库中的数据被修改时,这些修改首先会在内存中的缓冲池(Buffer Pool)中进行。例如,执行一条 UPDATE 语句修改了某一行数据,对应的缓

OmniGlue论文详解(特征匹配)

OmniGlue论文详解(特征匹配) 摘要1. 引言2. 相关工作2.1. 广义局部特征匹配2.2. 稀疏可学习匹配2.3. 半稠密可学习匹配2.4. 与其他图像表示匹配 3. OmniGlue3.1. 模型概述3.2. OmniGlue 细节3.2.1. 特征提取3.2.2. 利用DINOv2构建图形。3.2.3. 信息传播与新的指导3.2.4. 匹配层和损失函数3.2.5. 与Super

web群集--nginx配置文件location匹配符的优先级顺序详解及验证

文章目录 前言优先级顺序优先级顺序(详解)1. 精确匹配(Exact Match)2. 正则表达式匹配(Regex Match)3. 前缀匹配(Prefix Match) 匹配规则的综合应用验证优先级 前言 location的作用 在 NGINX 中,location 指令用于定义如何处理特定的请求 URI。由于网站往往需要不同的处理方式来适应各种请求,NGINX 提供了多种匹