【Hive】5-HiveSQL 数据查询语言（DQL）

embedded/2024/10/20 7:13:37/

HiveSQL 数据查询语言（DQL）

语法树

  • 从哪里查询取决于FROM关键字后面的table_reference。可以是普通物理表、视图、join结果或子查询结果。

  • 表名和列名不区分大小写。

在这里插入图片描述

执行顺序

Hive在查询过程中执行顺序：from > where > select > group by > having > order by > limit ;

  1. 聚合语句(sum, min, max, avg, count)要比having子句优先执行
  2. where子句在查询过程中执行优先级别优先于聚合语句(sum, min, max, avg, count)

MySQL语句执行顺序：

from > where > group by > having > select > order by > limit

查询高阶语法

ORDER BY

  • Hive SQL中的ORDER BY语法类似于标准SQL语言中的ORDER BY语法，会对输出的结果进行全局排序。

    因此当底层使用lMapReduce引擎执行的时候，只会有一个reducetask执行。如果输出的行数太大，会导致需要很长的时间才能完成全局排序。

  • 默认排序为升序( ASC )，也可以指定为DESC降序。

  • 在Hive 2.1.0和更高版本中，支持在ORDER BY子句中为每个列指定null类型结果排序顺序。ASC顺序的默认空排序顺序为NULLS FIRST，而DESC顺序的默认空排序顺序为NULLS LAST。

CLUSTER BY

根据指定字段将数据分组，每组内再根据该字段正序排序(只能正序)。概况起来就是：根据同一个字段，分且排序。

  • 分组规则hash散列（分桶表规则一样)：Hash_Func(col_name) % reducetask个数

  • 分为几组取决于reducetask的个数

示例：

-- 手动设置reduce task个数
set mapreduce.job.reduces = 2;
select * from student cluster by num;

执行结果如下：分为两个部分，每个部分内正序排序。

在这里插入图片描述

DISTRIBUTE BY + SORT BY

  • DISTRIBUTE BY + SORT BY就相当于把CLUSTER BY的功能─分为二：

    1. DISTRIBUTE BY负责根据指定字段分组

    2. SORT BY负责分组内排序规则。

  • 分组和排序的字段可以不同。

-- 案例:把学生表数据根据性别分为两个部分，每个分组内根据年龄的倒序排序。
select * from student distribute by sex sort by age desc;
  • 如果DISTRIBUTE BY + SORT BY的字段一样，则：CLUSTER BY=DISTRIBUTE BY + SORT BY
小结
  1. order by全局排序，因此只有一个reducer，结果输出在一个文件中，当输入规模大时，需要较长的计算时间。
  2. distribute by根据指定字段将数据分组，算法是hash散列。sort by是在分组之后，每个组内局部排序。
  3. cluster by既有分组，又有排序，但是两个字段只能是同一个字段。

在这里插入图片描述

UNION联合查询

UNION用于将来自于多个SELECT语句的结果合并为一个结果集。

  1. 使用DISTINCT关键字与只使用INION默认值效果一样，都会删除重复行。1.2.0之前的Hive版本仅支持UNION ALL，在这种情况下不会消除重复的行。

  2. 使用ALL关键字，不会删除重复行，结果集包括所有SELECT语句的匹配行（包括重复行)。

  3. 每个select_statement返回的列的数量必须相同，名称名称不用相同、类型要相同或能隐式转化。

在这里插入图片描述

子查询

  • 在Hive0.12版本，仅在FROM子句中支持子查询。

  • 必须要给子查询一个名称，因为FROM子句中的每个表都必须有一个名称。子查询返回结果中的列必须具有唯一的名称。子查询返回结果中的列在外部查询中可用，就像真实表的列一样。子查询也可以是带有UNION的查询表达式。

  • Hive支持任意级别的子查询，也就是所谓的嵌套子查询。

  • Hive 0.13.0和更高版本中的子查询名称之前可以包含可选关键字

  • 从Hive 0.13开始，WHERE子句支持下述类型的子查询:

    • 1.不相关子查询：该子查询不引用父查询中的列，可以将查询结果视为IN和NOT IN语句的常量;
    • 2.相关子查询：子查询引用父查询中的列；如EXISTS、NOT EXISTS
-- from子句中的每个表都必须有一个名称
select * from (select * from student limit 3) t;
-- 不相关子查询
select * from student num in (select num from student limit 2);
-- 相关子查询
select * from student s1 where exists (select 1 from student s2 where s1.num=s2.num);

Common Table Expressions（CTE）

  • 公用表表达式(CTE )是一个临时结果集∶该结果集是从WITH子句中指定的简单查询派生而来的，紧接在SELECT或INSERT关键字之前。
  • CTE仅在单个语句的执行范围内定义。
  • CTE可以在SELECT，INSERT, CREATE TABLE AS SELECT或CREATE VIEW AS SELECT语句中使用。
-- select语句中的CTE
with q1 as (select * from student where num in (95001,95003))
select * from q1;-- from风格
with q1 as (select * from student where num in (95001,95003)) from q1
select *;-- CTE链式
with q1 as (select * from student where num in (95001,95003)),q2 as (select num,name from q1)
select * from (select num from q2) t;-- union
with q1 as (select * from student where num in (95001,95003)),q2 as (select * from student where num in (95006,95009))
select * from q1 union select * from q2;-- insert
with q1 as (select * from student where num in (95001,95003))
insert overwrite table student2
select * from q1;-- create table
create table if not exists student2 as
with q1 as (select * from student where num in (95001,95003))
select * from q1;-- create view
create view v1 as
with q1 as (select * from student where num in (95001,95003))
select * from q1;

JOIN连接操作

JOIN语法规则

在Hive中，当下版本3.1.2总共支持6种join语法。分别是︰

inner join(内连接)、left join(左连接)、right join(右连接)、full outer join(全外连接)、left semi join(左半开连接)、cross join(交叉连接，也叫做笛卡尔乘积）。

默认join就是内连接，left join等价于left outer join，right join等价于right outer join

  • table_reference : 是join查询中使用的表名，也可以是子查询别名（查询结果当成表参与join )。

  • table_factor : 与table_reference相同,是联接查询中使用的表名,也可以是子查询别名。

  • join_condition : join查询关联的条件，如果在两个以上的表上需要连接，则使用AND关键字。

在这里插入图片描述

JOIN语法丰富化
  • Hive中join语法从面世开始其实并不丰富，不像在RDBMS中那么灵活。
  • 从Hive 0.13.0开始，支持隐式联接表示法（请参阅HIVE-5558 )。允许FROM子句连接以逗号分隔的表列表，而省略JOIN关键字。
  • 从Hive 2.2.0开始，支持ON子句中的复杂表达式，支持不相等连接（请参阅HIVE-15211和HIVE-15251 )。在此之前，Hive不支持不是相等条件的联接条件。
-- 隐式联接表示法
select * from table1 t1,table2 t2 where t1.id=t2.id and t1.name='Aiw';
-- 支持ON子句复杂表达式
select t1.* from table1 t1 join table2 t2 on t1.id=t2.id;
select t1.* from table1 t1 join table2 t2 on t1.id=t2.id and a.dept=b.dept;
-- 支持不相等连接
select t1.* from table1 t1 left join table2 t2 on t1.id<>t2.id
inner join 内连接

内连接是最常见的一种连接，它也被称为普通连接，其中inner可以省略：inner join == join ;

只有进行连接的两个表中都存在与连接条件相匹配的数据才会被留下来。

在这里插入图片描述

left join 左连接

left join中文叫做是左外连接(Left Outer Join)或者左连接，其中outer可以省略，left outer join是早期的写法。

left join的核心就在于left左。左指的是join关键字左边的表，简称左表。

通俗解释:join时以左表的全部数据为准，右边与之关联;左表数据全部返回，右表关联上的显示返回，关联不上的显示null返回。

在这里插入图片描述

right join 右连接

right join中文叫做是右外连接(Right Outer Jion)或者右连接，其中outer可以省略。

right join的核心就在于Right右。右指的是join关键字右边的表，简称右表。

通俗解释:join时以右表的全部数据为准，左边与之关联﹔右表数据全部返回，左表关联上的显示返回，关联不上的显示null返回。

很明显，right join和left join之间很相似，重点在于以哪边为准，也就是一个方向的问题。

在这里插入图片描述

full outer join 全外连接

full outer join等价full join ,中文叫做全外连接或者外连接。

包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行;

在功能上︰等价于对这两个数据集合分别进行左外连接和右外连接，然后再使用消去重复行的操作将上述两个结果集合并为一个结果集。

在这里插入图片描述

left semi join 左半开连接

左半开连接(LEFT SEMI JOIN)会返回左边表的记录，前提是其记录对于右边的表满足ON语句中的判定条件。

从效果上来看有点像inner join之后只返回左表的结果。

-- left semi join，相当于inner join，只返回左表的字段，只不过效率高一些
select * from table1 t1 left semi join table2 t2 on t1.id=t2.id;
-- inner join
select t1.* from table1 t1 join table2 t2 on t1.id=t2.id;
cross join 交叉连接

交叉连接cross join，将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积。对于大表来说，cross join慎用。

在SQL标准中定义的cross join就是无条件的inner join。返回两个表的笛卡尔积,无需指定关联键。

在HiveSQL语法中，cross join后面可以跟where子句进行过滤，或者on条件过滤。

注意事项

a）允许使用复杂的联接表达式,支持非等值连接

在这里插入图片描述

b）同一查询中可以连接2个以上的表

在这里插入图片描述

c）如果每个表在联接子句中使用相同的列，则Hive将多个表上的联接转换为单个MR作业

在这里插入图片描述

d）join时的最后一个表会通过reducer流式传输，并在其中缓冲之前的其他表，因此，将大表放置在最后有助于减少reducer阶段缓存数据所需要的内存

在这里插入图片描述

e）在join的时候，可以通过语法STREAMTABLE提示指定要流式传输的表。如果省略STREAMTABLE提示，则Hive将流式传输最右边的表。

在这里插入图片描述

f）join在WHERE条件之前进行。

g）如果除一个要连接的表之外的所有表都很小，则可以将其作为仅map作业执行(mapjoin)。

在这里插入图片描述


http://www.ppmy.cn/embedded/128927.html

相关文章

精选的四款强大视频压缩工具的整理：

精选的四款强大视频压缩工具的整理：

大家好&#xff01;今天我来跟大家分享一下我使用过的几款视频压缩软件的体验感受&#xff0c;以及它们各自的好用之处&#xff1b;在这个信息爆炸的时代&#xff0c;视频文件越来越大&#xff0c;如何快速有效地压缩视频&#xff0c;同时还能保持较好的画质&#xff0c;是很多…
阅读更多...
c++ 平衡树- 替罪羊树（非旋转）

c++ 平衡树- 替罪羊树（非旋转）

洛谷真题 **#include <algorithm> #include <array> #include <bitset> #include <cassert> #include <chrono> #include <climits> #include <cmath> #include <complex> #include <cstring> #include <functional…
阅读更多...
WPF常见容器全方位介绍

WPF常见容器全方位介绍

Windows Presentation Foundation (WPF) 是微软的一种用于构建Windows桌面应用程序的UI框架。WPF的布局系统基于容器&#xff0c;帮助开发者以灵活、响应的方式组织用户界面 (UI) 元素。本篇文章将详细介绍WPF中几种常见的容器&#xff0c;包括Grid、StackPanel、WrapPanel、Do…
阅读更多...
【DevOps工具篇】Docker的DNS原理

【DevOps工具篇】Docker的DNS原理

在使用 Docker 容器时，网络在实现容器与外界之间的通信方面起着至关重要的作用。容器网络的一个基本方面是 DNS（域名系统），它允许容器使用域名而不是依赖 IP 地址来发现彼此并相互通信。在本文中，我们将探讨 Docker DNS 以及它如何促进容器通信。 🔎 什么是 DNS？ 域名…
阅读更多...
基于SpringBoot+Vue+uniapp的诗词学习系统的详细设计和实现

基于SpringBoot+Vue+uniapp的诗词学习系统的详细设计和实现

详细视频演示 请联系我获取更详细的演示视频 项目运行截图 技术框架 后端采用SpringBoot框架 Spring Boot 是一个用于快速开发基于 Spring 框架的应用程序的开源框架。它采用约定大于配置的理念&#xff0c;提供了一套默认的配置&#xff0c;让开发者可以更专注于业务逻辑而不…
阅读更多...
[C#][winform]基于yolov8的道路交通事故检测系统C#源码+onnx模型+评估指标曲线+精美GUI界面

[C#][winform]基于yolov8的道路交通事故检测系统C#源码+onnx模型+评估指标曲线+精美GUI界面

【重要说明】 该系统以opencvsharp作图像处理,onnxruntime做推理引擎&#xff0c;使用CPU进行推理&#xff0c;适合有显卡或者没有显卡windows x64系统均可&#xff0c;不支持macOS和Linux系统&#xff0c;不支持x86的windows操作系统。由于采用CPU推理&#xff0c;要比GPU慢。…
阅读更多...
wireshark或tshark提取tcpdump捕获的数据包(附python脚本自动解析文件后缀)

wireshark或tshark提取tcpdump捕获的数据包(附python脚本自动解析文件后缀)

tcpdump 捕获数据包后&#xff0c;保存的文件通常会被命名为 capture.pcap&#xff08;或其他你指定的名称&#xff09;&#xff0c;并存储在你运行命令的当前目录中。以下是如何使用 tcpdump 进行流量捕获&#xff0c;并找到和使用捕获文件的详细步骤。 1. 使用 tcpdump 捕获…
阅读更多...
应用层协议 序列化

应用层协议 序列化

自定义应用层协议 例子&#xff1a;网络版本计算器 序列化反序列化 序列化&#xff1a;将消息&#xff0c;昵称&#xff0c;日期整合成消息-昵称-日期 反序列化&#xff1a;消息-昵称-日期->消息&#xff0c;昵称&#xff0c;日期 在序列化中&#xff0c;定义一个结构体…
阅读更多...
最新文章

Copyright @ 2022~2023