Hive SQL DQL SELECT语法树 ALL、DISTINCT、WHERE、GROUP BY、HAVING、LIMIT、执行顺序、JOINORDER BY、CLUSTER BY

最新推荐文章于 2024-05-24 20:43:27 发布
最新推荐文章于 2024-05-24 20:43:27 发布
阅读量1.1k 收藏 5
点赞数
分类专栏： Hive 文章标签： hive sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/pljnb/article/details/128922237
版权

文章目录

Hive SQL DQL SELECT语法树

[WITH CommonTableExpression(,CommonTableExxpression)*]
SELECT [ALL|DISTINCT] select_expr,select_expr,...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[CLUSTER BY col_list | [DISTRIBUTE BY col_list][SORT BY col_list]]
[LIMIT[offset,]row];

基础语法

select_expr

  • 表示检索查询返回的列，必须至少有一个select_expr

查询指定字段

SELECT country,cases,deaths FROM t_usa_covid12_p;

查询匹配正则表达式的所有字段

# 设置反引号不再解释为其他含义，被解释为正则表达式
SET hive.support.quoted.identifiers = none;
SELECT `^C.*` FROM t_usa_covid19_p;

查询当前数据库

SELECT current_database(); --省去from

查询使用函数

SELECT COUNT(country) FROM t_usa_covid19_p;

ALL、DISTINCT

  • 用于指定查询返回结果中重复的行如何处理
  • 默认为ALL，即返回所有匹配的行
  • DISTINCT指定从结果集中删除重复的行

匹配所有行

SELECT state FROM t_usa_covid12_p;

相当于

SELECT ALL state FROM t_usa_covid12_p;

返回所有匹配的行，去处重复结果

  • 若是多个字段，则是整体去重
SELECT DISTINCT state FROM t_usa_covid12_p;

WHERE

  • WHERE后面是一个布尔表达式，用于查询过滤。
  • WHERE表达式中，可以使用Hive支持的任何函数和运算符，但聚合函数除外。
  • 要使用聚合函数进行条件过滤，可以使用HAVING关键字。
  • Hive0.13后，WHERE子句支持某些类型的子查询

WHERE中使用函数

SELECT * FROM t_usa_covid19_p WHERE length(state) > 10;

WHERE中使用子查询

SELECT *
FROM A
WHERE A.a IN (SELECT foo FROM B);

分区查询、分区裁剪

  • 针对Hive分区表，查询时可以指定分区查询，减少全表扫描，也叫做分区裁剪。
  • 分区裁剪在对分区表进行查询时，会检查WHERE子句或JOIN中的ON子句是否存在对分区字段的过滤，如果存在，则仅访问查询符合条件的分区，即裁剪掉没必要访问的分区。

GROUP BY

  • 出现在GROUP BY 中的select_expr字段，要么是GROUP BY分组的字段，要么是被聚合函数应用的字段。（避免一个字段多个值的歧义）

HAVING

  • WHERE关键字无法与聚合函数一起使用，因此出现了HAVING
  • HAVING子句可以筛选分组后的各组数据，并可以在HAVING中使用聚合函数。
  • 例：统计死亡病例大于10000的州
SELECT state,sum(deaths)
FROM count_date = "2021-01-28"
GROUP BY state
HAVING sum(deaths) > 10000;

LIMIT

  • LIMIT用于限制SELECT语句返回的行数。
  • LIMIT接收一个或两个数字参数，这两个参数都必须是非负整数常量。
  • 第一个参数指定要返回的第一行的偏移量，第二个参数指定要返回的最大行数。
  • 给出一个参数时，代表最大行数，偏移量默认为0。
# 从第3条数据开始的三条数据。（偏移量初始值为0）
SELECT * FROM t_usa_covid19_p
WHERE count_date = "2021-01-28"
AND state = "California"
LIMIT 2,3;

执行顺序

  • 查询过程中的执行顺序：
    FROM > WHERE > GROUP(含聚合) > HAVING > ORDER > SELECT
  • 聚合函数(sum,min.max.avg.count)优先于Having子句执行。
  • Where子句优先于聚合语句
  • 例：
SELECT state,sum(deaths) AS cnts
FROM count_date = "2021-01-28"
GROUP BY state
HAVING cnts > 10000;

高阶语法

ORDER BY

  • 对输出的结果进行全局排序，如果行数太大，会导致需要很长时间才能完成全局排序。
  • 默认为ASC升序排序，指定DESC则为降序排序
  • Hive2.0后版本中，支持在ORDER BY子句中为每个列指定null类型结果排序顺序。ASC顺序默认空排序顺序为NULLS FIRST。
  • 在使用ORDER BY时，建议于LIMIT一起使用，避免数据集行数过大
  • 当hive.mapred.mode设置为strict严格模式时，使用不带LIMIT的ORDER BY时会引发异常。
SELECT * FROM t_usa_covid19_p
WHERE count_date = "2021-01-28"
AND state = "California"
ORDER BY deaths DESC
LIMIT 3;

CLUSTER BY

  • 根据指定字段将数据分组，每组内再根据该字段正序排序(只能正序)
  • 分组规则为hash散列(于分桶表规则一样)：Hash_Func(col_name) % reducetask个数
  • 分几组取决于reducetask的个数。
  • 例：
# 手动设置reducetask个数，否则将根据数据量自主推断。
SET mapreduce.job.reduces=2;
SELECT * FROM student CLUSTER BY num;

DISTRIBUTE BY + SORT BY

  • 相当于把CLUSTER BY的功能一分为二：
  1. DISTRIBUTE BY负责根据指定字段分组；
  2. SORT BY负责分组内排序规则。
  • 分组和排序的字段可以不同。
SELECT * FROM student DISTRIBUTE BY sex SORT BY age DESC;

UNION联合查询

  • 用于将来自多个SELECT语句的结果合并为一个结果集。
  • 使用DISTINCT关键字与只使用UNION默认值效果一样，会删除重复行
  • 使用ALL关键字时不会删除重复行
  • 每个查询结果集返回列的数量和名称必须相同。
  • 语法规则
select_statement UNION [ALL|DISTINCT] select_statement UNION ...
  • 例：
SELECT num,name FROM student_local
UNION ALL
SELECT num,name FROM student_hdfs;
  • 若要将ORDER BY，LIMIT等筛选条件应用到单个SELECT中，需要应用子查询：
SELECT num,name FROM (SELECT num,name FROM student_local LIMIT 2) subq1
UNION ALL
SELECT num,name FROM (SELECT num,name FROM student_hdfs LIMIT 3) subq2;

FROM子句中的子查询

  • Hive支持任意级别的子查询，即嵌套子查询。
  • 子查询必须有一个名称，其AS关键字可选
  • 不相关子查询：该子查询不引用父查询中的列，可以将查询结果视为IN或NOT IN语句的常量。
  • 相关子查询：子查询引用父查询中的列。

CTE公用表达式

  • 公用表达式是一个临时结果集：该结果集是从WITH子句中指定的简单查询派生而来的，紧接在SELECT或INSERT关键字之前。
  • CTE仅在单个语句的执行范围内定义，即仅在当前SQL语句有效。
  • CTE可以在SELECT，INSERT，CREATE TABLE AS SELECT或CREATE VIEW AS SELECT语句中使用。
[WITH CommonTableExpression(,CommonTableExxpression)*]
  • 例：WITH定义一个q1表，在当前SQL语句中使用。
WITH q1 AS (SELECT num,name,age FROM student WHERE num = 95002)
SELECT * FROM q1

CTES链式SQL：

  • 前面定义的CTE可以在后续的CTE中使用。
WITH q1 AS (SELECT * FROM student WHERE num = 95002),
		 q2 AS (SELECT num,name,age FROM q1)
SELECT * FROM (SELECT num FROM q2) a;

CTE与UNION

WITH q1 AS (SELECT * FROM student WHERE num = 95002),
		 q2 AS (SELECT * FROM student WHERE num = 95004)
SELECT * FROM q1 UNION ALL SELECT * FROM q2;

CTE与INSERT

WITH q1 AS (SELECT * FROM student WHERE num = 95002)
INSERT OVERWRITE TABLE table_name SELECT * FROM q1;

CTE与CREATE

CREATE TABLE table_name AS 
WITH q1 AS (SELECT * FROM student WHERE num = 95002)
SELECT * FROM q1;

JOIN连接操作

  • Hive中共支持六种join语法，分别是：INNER JOIN（内连接）、LEFT JOIN（左连接）、RIGHT JOIN（右连接）、FULL OUTER JOIN（全外连接）、LEFT SEMI JOIN（左半开连接）、CORSS JOIN（交叉连接，也叫笛卡尔乘积）。

JOIN语法规则

  • table_reference：是join查询中使用的表名，也可以是子查询别名
  • table_factor：与table_reference相同，是联接查询中使用的表名，也可以是子查询别名。
  • join_condition：join查询关联的条件，如果两个以上的表需要连接，则需要使用AND关键字。
  • Hive2.0后，支持不相等连接。
table_reference [INNER] JOIN table_factor [join_condition]
| table_reference {LEFT|RIGHT|FULL}[OUTER]JOIN table_reference join_condition
| table_reference LEFT SEMI JOIN table_reference join_condition
| table_reference CROSS JOIN table_reference [join_condition]

join_condition: ON expression

内连接 INNER JOIN

  • 最常用的一种连接，取两表交集，INNER可以省略
SELECT e.id,e.name,e_a.city,e_a.street
FROM employee e INNER JOIN employee_adress e_a
ON e.id = e_a.id;
  • 隐式连接表示法：
SELECT e.id,e.name,e_a.city,e_a.street
FROM employee e, employee_adress e_a
WHERE e.id = e_a.id;

左(右)外连接 LEFT(RIGHT) OUTER JOIN

  • OUTER 可以省略
  • 左外连接即取左表全部数据和左右交集数据，右表关联不上的返回NULL。
SELECT e.id,e.name,e_a.city,e_a.street
FROM employee e LEFT JOIN employee_adress e_a
ON e.id = e_a.id;

还可以实现非等值连接：

SELECT e.id,e.name,e_a.city,e_a.street
FROM employee e LEFT JOIN employee_adress e_a
ON (e.id <> e_a.id);

全外连接 FULL OUTER JOIN

  • OUTER可以省略
  • 包括左右两个表的全部行，功能上等价于两个表分别做左外连接和右外连接，然后消除重复行将两个结果集合并为一个结果集。
SELECT e.id,e.name,e_a.city,e_a.street
FROM employee e FULL OUTER JOIN employee_adress e_a
ON e.id = e_a.id;

左半开连接LEFT SEMI JOIN

  • 只会返回左表的数据，但其记录对于右表满足ON语句中的判定条件。
  • 从效果上来看，相当于INNER JOIN后只返回左表的数据，但效率高一些。
SELECT *
FROM employee e LEFT SEMI JOIN employee_adress e_a
ON e.id = e_a.id;

结果上相当于：

SELECT e.*
FROM employee e INNER JOIN employee_adress e_a
ON e.id = e_a.id;

交叉连接 CROSS JOIN

  • 将返回连接两个表的笛卡尔积。
  • 在SQL标准中定义的cross join就是无条件inner join，无序指定关联键。
  • 在HiveSQL中，cross join后面可以接where子句或ON条件进行过滤。
  • 如果使用where子句，会先生成笛卡尔积表再过滤，速度很慢不建议使用。
  • 我不知道它存在的意义是什么。
SELECT e.*
FROM employee e CORSS JOIN employee_adress e_a
ON e.id = e_a.id;

JOIN注意事项

  1. 允许使用复杂的连接表达式，支持非等值连接
  2. 同一查询中可以连接两个以上的表。
  3. 如果每个表在连接子句中使用相同的列，则Hive将多个表上的连接转换为单个MR作业。
# 连接中仅设计b的key1列，因此转换为1个MR作业
SELECT a.val,b.val,c.val
FROM a JOIN b
ON (a.key = b.key1) JOIN c ON (c.key=b.key1);
# 连接仅设计b的key1列和key2列，因此转换为2个MR作业
SELECT a.val,b.val,c.val
FROM a JOIN b
ON (a.key = b.key1) JOIN c ON (c.key=b.key2);
  1. join多个表时的最后一个表会通过reducer流式传输，并在其中缓冲之前的其他表，因此将大表放置在最后有助于减少redcer阶段缓存数据所需要的内存。
  2. 在join时，可以通过STREAMTABLE提示指定要流式传输的表。
SELECT /*+STREAMTABLE(a) */a.val,b.val,c.val
FROM a JOIN b
ON (a.key = b.key1) JOIN c ON (c.key=b.key2);
  1. JOIN在WHERE之前执行。
  2. 如果只有一个表很大，其他表都很小，则可以将其作为仅map作业执行(mpajoin)。
SELECT /*+MAPJOIN(b) */a.val,b.val,c.val
FROM a JOIN b
ON (a.key = b.key1) JOIN c ON (c.key=b.key2);
pljnb
关注
  • 0
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Hive hql语句 group by 和 having
shuyv的博客
12-19 3759
文章目录分组1.group by案例实操：2.havinghaving和where的区别：案例实操 分组 1.group by group by 语句通常会和聚合函数一起使用，按照莫格字段的内容j进行分组，然后每个分组进行聚合操作。 案例实操： score表的数据 然后我们使用hql语句： select s_id ,avg(s_score) from score group by s_id; 以s_id作为分组条件，求得平均值，，结果如下： 2.having having和where的区别： 1.w
Hive基础知识（十三）：Hive的Group by语句与Having语句
zuodingquan666的博客
01-14 1170
GROUP BY 语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。1）案例实操：（1）计算 emp 表每个部门的平均工资（2）计算 emp 每个部门中平均薪资大于2000，及部门平均薪资。（3）计算 emp 每个部门中每个岗位的最高薪水。
语法树和语法树的查询优化
qq_74474809的博客
05-24 145
2、利用等价转换规则反复地对查询表达式进行尝试性转换，将原始的语法树转換成“优化”的形式 对每一个选择，利用等价变换规则尽可能把它移到树的叶端。对每一个投影利用等价变换规则尽可能把它移向树的叶端。目的是使投影操作尽早执行 对每个叶节点加必要的投影操作，以消除对查询无用的属性。查询树以自底向上的方式执行：当一个内结点的操作分量可用时，这个内结点所表示的操作启动执行，执行结束后用结果关系代替这个内结点。在一个查询树中，叶子结点表示关系，内结点表示关系代数操作。在每个操作后，应做个投影操作，去掉不用的属性值)。
[hive] sql中distinct的用法和注意事项
言之。
12-25 1万+
在 Hive SQL 中，DISTINCT用于去重查询结果中的行。它返回唯一的行，消除结果集中的重复项。以下是DISTINCT。
大数据之Hive：hive中的cross join函数
weixin_43597208的博客
06-03 7241
目录1.简介2.简单示例3.实战需求一：具体实现需求二： 1.简介 CROSS JOIN是sql中的一种连接方式，区别于内连接和外连接，对于cross join连接来说，其实使用的就是笛卡尔连接。在SQL中，当CROSS JOIN不使用WHERE子句时，CROSS JOIN产生了一个结果集，该结果集是两个关联表的行的乘积。通常，如果每个表分别具有n和m行，则结果集将具有n*m行； 附注:看一下官方的解释 2.简单示例 SELECT * FROM [TABLE 1] CROSS JOIN [TABLE 2]
hive中distinct用法
fuyun6363的博客
01-05 4万+
hive中的distinct是去重的意思，和group by在某些情况下有相同的功能 下面测试下distinct的部分功能，先创建一张测试表 create table test.trip_tmp( id int, user_id int, salesman_id int, huose_id int ); 插入模拟数据 insert into test.trip_tmp values(1, 2, ...
Hive拉拉扯扯的order by,sort by,distribute by, cluster by
01-07
Hive支持两个层面的排序： 全局排序 部分排序 全局排序用 order by col [ASC | DESC] 实现，效果和传统的RDMS一样，...鉴于此，Hive有一些自己特定，比如order by的实现需要把所有数据汇集到一个reducer中处理，如果数
Hive查询优化：Sort By、Order By、Cluster By、Distribute深入解析
最新发布
06-28
2. **SQL-like Query Language**：Hive提供了一种称为HiveQL的查询语言，它是SQL的一种方言，允许用户执行数据查询、数据摘要和分析等操作。 3. **数据抽象**：Hive使用表和分区等概念来抽象底层数据存储，使得用户...
02.hive查询语法--分组聚合--groupby查询--where过滤和having过滤的区别.mp4
04-29
02.hive查询语法--分组聚合--groupby查询--where过滤和having过滤的区别.mp4
hive sql + left join 数据缺失
02-29
hive sql + left join 数据缺失
Hive_SQL语法大全
07-13
Hive_Sql语法详述，平时工作中遇到的问题，用于Hadoop平台的数据挖掘。
hive sql 的三种去重方法总结
maligebilaowang的博客
07-28 1万+
sql字段去重 在从数据库中取数据或者进行数据清洗的过程中，经常会遇到去重复问题，常用的去重方法包括三种方式。接下来主要介绍常用的三种方式以及最优方法。 distinct group by row_number() over() 需求分析 对下表中uid字段去重，并且把去重后所有字段输出。 distinct方法 ditinct方法适合于单字段去重，但是单字段去重还要保留其他字段数据，就无法完成了； distinct对NULL是不进行过滤的，即返回的结果中是包含NULL值的； distinct应用到
SELECT语法详解
幻听
09-16 1410
SELECT语法详解   转载▼   SELECT     [ALL | DISTINCT | DISTINCTROW ]       [HIGH_PRIORITY]       [STRAIGHT_JOIN]       [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]  
HiveQL常用查询语句where、group by、having、join子句记录
攻城狮Kevin
02-28 4965
由于hivesql中查询语句时，常用查询条件比较多，所以专门写一篇博文对HiveQL的查询语句进行总结，理清联系和区别。 目录 1. where子句（不能跟别名） （1）比较运算符 （2）like和rlike（正则匹配） （3）逻辑运算符and or not 2. group by子句（每...） 3. having子句（只用于group by之后） 4. join子句（A joi...
Hive第六天——Hive函数（GROUP BY语句、HAVING语句、ORDER BY语句）
你怎么回事
09-27 7446
本文部分参考自：https://blog.csdn.net/qq_23897391/article/details/90897373 Hive第六天——Hive函数 自己的话：千里之行，始于足下。 每天都要保持前进，我势必要有强劲的实力，再跟明天的自己问好。 GROUP BY语句、HAVING语句、ORDER BY语句 一、GROUP BY语句 GROUP BY语句 表示按照某些字段的值进行...
Hive的数据去重Group by Having count()等方法
wunanliu的采坑日记
01-31 8114
查询出当前表中，电话号码出现数次数为两次的数据！并且删除掉重复多余的数据，只留下一条数据！having count and group by select mobile,count(1)cnt from rbc_audience_users group by mobile having count(mobile) >1 查询出rbc_audience_users表中 mobile大于一条...
Hive--count(distinct)多字段问题
修行的博客
11-09 1796
count(distinct)多字段问题 select count(distinct(字段a | 字段b)) from 表名;
sql去重复操作详解SQL中distinct的用法
热门推荐
boss2967的博客
01-10 53万+
在表中，可能会包含重复值。这并不成问题，不过，有时您也许希望仅仅列出不同（distinct）的值。关键词 distinct用于返回唯一不同的值。 表A： 表B: 1.作用于单列 select distinct name from A 执行后结果如下： 2.作用于多列 示例2.1 select distinct name, id fro
Hive SQL优化一之DISTINCT
数仓老六
08-20 661
我们利用Hive对嵌套语句的支持，将原来一个MapReduce作业转换为两个作业，在第一阶段选出全部的非重复id，在第二阶段再对这些已消重的id进行计数。这样在第一阶段我们可以通过增大Reduce的并发数，并发处理Map输出。在第二阶段，由于id已经消重，因此COUNT(*)操作在Map阶段不需要输出原id数据，只输出一个合并后的计数即可。这样即使第二阶段Hive强制指定一个Reduce Task，极少量的Map输出数据也不会使单一的Reduce Task成为瓶颈。改进后的SQL语句如下： SELE...
hivesql&sparksql—count(distinct ) over (partition by )报错解决方案
03-16
您好，针对"hivesql&sparksql—count(distinct ) over (partition by )报错解决方案"的问题，我提供以下解决方案： 1. 确认语法是否正确：在使用count(distinct) over (partition by)时，需要注意语法是否正确，...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值