hive数据查询语法

思维导图

基本查询

基本语法

SELECT [ALL | DISTINCT] 字段名, 字段名, ...
FROM 表名 [inner | left outer | right outer | full outer | left semi JOIN 表名 ON 关联条件 ]
[WHERE 非聚合条件]
[GROUP BY 分组字段名]
[HAVING 聚合条件]
[ORDER BY 排序字段名 asc | desc]
[CLUSTER  BY 字段名 | [DISTRIBUTE BY 字段名 SORT BY 字段名]]
[LIMIT x,y]

整体上和普通SQL差不多,部分有区别,如:CLUSTER BY、DISTRIBUTE BY、SORT BY等

基础查询格式: select distinct 字段名 from 表名;  

        注意: *代表所有字段 distinct去重 as给表或者字段起别名

条件查询格式: select distinct 字段名 from 表名 where 条件;  

        比较运算符: > < >= <= != <>              

        逻辑运算符: and or not              

        模糊查询: %代表任意0个或者多个字符   _代表任意1个字符              

        空判断: 为空is null   不为空is not null              

        范围查询: x到y的连续范围:between x and y   x或者y或者z类的非连续范围: in(x,y,z)

排序查询格式: select distinct 字段名 from 表名 [where 条件] order by 排序字段名 asc|desc ;         asc : 升序 默认升序  

        desc: 降序

聚合查询格式: select 聚合函数(字段名) from 表名;  

        聚合函数: 又叫分组函数或者统计函数  

        聚合函数: count() sum() avg() max() min()

分组查询格式: select 分组字段名,聚合函数(字段名) from 表名 [where 非聚合条件] group by 分组字段名 [having 聚合条件];  

        注意: 当分组查询的时候,select后的字段名要么在groupby后出现过,要么放在聚合函数内,否则报错  

        where和having区别?  

        区别1: 书写顺序不同,where在group by关键字前,having在group by关键字后  

        区别2: 执行顺序不同,where在分组之前过滤数据,having在分组之后过滤数据  

        区别3: 筛选数据不同,where只能在分组之前过滤非聚合数据,having在分组之后主要过滤聚合数据  

        区别4: 操作对象不同,where底层操作伪表,having底层操作运算区

分页查询格式: select 字段名 from 表名 [ order by 排序字段名 asc|desc] limit x,y;  

        x: 起始索引 默认从0开始,如果x为0可以省略   计算格式: x=(页数-1)*y  

        y: 本次查询记录数

数据准备

准备数据:订单表

CREATE TABLE orders (orderId bigint COMMENT '订单id',orderNo string COMMENT '订单编号',shopId bigint COMMENT '门店id',userId bigint COMMENT '用户id',orderStatus tinyint COMMENT '订单状态 -3:用户拒收 -2:未付款的订单 -1:用户取消 0:待发货 1:配送中 2:用户确认收货',goodsMoney double COMMENT '商品金额',deliverMoney double COMMENT '运费',totalMoney double COMMENT '订单金额(包括运费)',realTotalMoney double COMMENT '实际订单金额(折扣后金额)',payType tinyint COMMENT '支付方式,0:未知;1:支付宝,2:微信;3、现金;4、其他',isPay tinyint COMMENT '是否支付 0:未支付 1:已支付',userName string COMMENT '收件人姓名',userAddress string COMMENT '收件人地址',userPhone string COMMENT '收件人电话',createTime timestamp COMMENT '下单时间',payTime timestamp COMMENT '支付时间',totalPayFee int COMMENT '总支付金额'
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
LOAD DATA LOCAL INPATH '/home/hadoop/orders.txt' INTO TABLE orders;

基本查询

这是一张订单销售表,我们基于此表,做一下简单的Hive基本查询

SELECT 基础查询

-- 查询所有
SELECT * FROM orders;
-- 查询单列
SELECT orderid, totalmoney, username, useraddress, paytime FROM orders;
-- 查询数据量
SELECT COUNT(orderid) FROM orders;
-- 过滤广东省订单
SELECT * FROM orders WHERE useraddress LIKE '%广东%';
-- 找出广东省单笔营业额最大的订单
SELECT * FROM orders WHERE useraddress like '%广东%' ORDER BY totalmoney DESC LIMIT 1;

SELECT 分组、聚合查询

-- 统计未支付、已支付各自的人数
SELECT ispay, COUNT(*) AS cnt FROM orders GROUP BY ispay;
-- 在已付款订单中,统计每个用户最高的一笔消费金额
SELECT userid, MAX(totalmoney) AS max_money FROM orders WHERE ispay = 1 GROUP BY userid;
-- 统计每个用户的平均订单消费额
SELECT userid, AVG(totalmoney) FROM orders GROUP BY userid;
-- 统计每个用户的平均订单消费额,过滤大于10000的数据
SELECT userid, AVG(totalmoney) AS avg_money FROM orders GROUP BY userid HAVING avg_money > 10000;

hive 多表join查询

演示join查询

1. 准备数据源, 建表, 添加表数据.

table1: 商品表

-- table1: 商品表
CREATE TABLE product(pid string,pname string,category_id string) row format delimited
fields terminated by ',';

表中数据:

table2: 分类表

-- table2: 分类表
CREATE TABLE category (cid string,cname string
) row format delimited
fields terminated by ',';

表中数据:

多表查询 cross join------交叉连接--------------

select * from product cross join category;

多表查询 inner join------内连接--------------

-- 多表查询 inner join------内连接--------------
select * from product inner join category on product.category_id = category.cid;

多表查询 left join on------左外连接--------------

-- 多表查询 left join on------左外连接--------------
select * from product left join category on product.category_id = category.cid;

多表查询 right join on------右外连接--------------

-- 多表查询 right join on------右外连接--------------
select * from product right join category on product.category_id = category.cid;

多表查询 full join on------全外连接--------------

-- todo  多表查询 full join on------全外连接--------------
select * from product full join category on product.category_id = category.cid;

多表查询 union -----联合查询--------------

-- 多表查询 union -----联合查询--------------
select * from product left join category on product.category_id = category.cid
union
select * from product right join category on product.category_id = category.cid;

多表查询 left semi join------左半连接--------------

-- 多表查询 left semi join------左半连接--------------
select * from product left semi join category on product.category_id = category.cid;

hive有别于mysql的join

满外连接, full outer join

左半连接, left semi join

 多表查询 full join on------全外连接--------------

-- todo  多表查询 full join on------全外连接--------------
select * from product full join category on product.category_id = category.cid;

 多表查询 left semi join------左半连接--------------

-- 多表查询 left semi join------左半连接--------------
select * from product left semi join category on product.category_id = category.cid;

union查询

union 是上下拼接的连接查询: 要求上下字段和类型要保持一种.
主要实现功能: 把两个select查询结果上下拼接起来.

# union 拼接过程中会去除重复.

select * from product
union
select * from product where pid='p1';


# union all 拼接过程中不会去重.

select * from product
union all
select * from product where pid='p1';

hive有别于mysql的排序

cluster by关键字

distribute by+sort by关键字

set mapreduce.job.reduces:  查看当前设置的reduce数量 默认结果是-1,代表自动匹配reduce数量和桶数量一致
set mapreduce.job.reduces = 数量 : -- 修改reduces数量

cluster by 字段名:  分桶且正序排序   弊端: 分和排序是同一个字段,相对不灵活

distribute by 字段名 sort by 字段名: distribute by负责分,sort by负责排序, 相对比较灵活

order by 字段名: 只能全局排序

注意: cluster by 和 distribute by 字段名 sort by 字段名 受当前设置的reduces数量影响,但是设置的reduces数量对order by无影响,因为orderby就是全局排序,就是一个reduce

建表的时候指定分桶字段和排序字段: clustered by (字段名) sorted by (字段名) into 桶数量 buckets
     注意: 如果建表的时候设置了桶数量,那么reduces建议设置值-1或者值大于桶数量

Hive SQL中的cluster by语法可以指定根据后面的字段将数据分桶,桶内再根据这个字段正序排序, 概括起来就是:根据同一个字段,分且排序

-- 创建基础表主要用于给分桶表准备数据
create table students
(id     int,name   string,gender string,age    int,class  string
)
row format delimited
fields terminated by ',';

设置reduce数量,注意:此种方式num默认是-1,代表自动匹配reduce数量和桶数量一致.

这里设置reduce数量其实可以确定查询分桶个数.

set mapreduce.job.reduces = num;

本示例设了三个桶 

set mapreduce.job.reduces = 3;
-- 创建基础表后,上传students.txt文件
-- 查询数据,观察结果
select * from students cluster by id;



Hive SQL中的distribute by+sort by就相当于把cluster by的功能一分为二:

distribute by和sort by可以跟不同的字段

DISTRIBUTE BY负责分

SORT BY负责分组内排序

如果DISTRIBUTE BY +SORT BY的后面字段一样,可以得出下列结论:

CLUSTER BY=DISTRIBUTE BY +SORT BY(字段一样)

-- 直接基于基础表查询,观察结果
select * from students distribute by id sort by gender;

总结

        Hive中使用基本查询SELECT、WHERE、GROUP BY、聚合函数、HAVING、JOIN和普通的SQL语句几乎没有区别

        hive的JOIN查询和排序查询相比普通sql新增了部分功能

抽样查询

TABLESAMPLE抽样好处: 尽可能实现随机抽样,并且不走MR,查询效率相对较快 ​

基于随机分桶抽样格式: SELECT 字段名 FROM tbl TABLESAMPLE(BUCKET x OUT OF y ON(字段名 | rand()))  

        y:决定将表数据随机划分成多少份  

        x:决定从第几份数据开始采样  

        | : 或者  

        字段名: 表示随机的依据基于某个列的值,每次按相关规则取样结果都是一致  

        rand(): 表示随机的依据基于整行,每次取样结果不同

为什么需要抽样表数据

对表进行随机抽样是非常有必要的。

大数据体系下,在真正的企业环境中,很容易出现很大的表,比如体积达到TB级别。

对这种表一个简单的SELECT * 都会非常的慢,哪怕LIMIT 10想要看10条数据,也会走MapReduce流程 这个时间等待是不合适的。

Hive提供的快速抽样的语法,可以快速从大表中随机抽取一些数据供用户查看。

TABLESAMPLE函数

进行随机抽样,本质上就是用TABLESAMPLE函数

基于随机分桶抽样:

SELECT ... FROM tbl TABLESAMPLE(BUCKET x OUT OF y ON(colname | rand()))

        y表示将表数据随机划分成y份(y个桶)

        x表示从分好的桶中获取第几个桶的数据.(x的值不能大于y)

        colname表示随机的依据基于某个列的值

        rand()表示随机的依据基于整行

示例:

SELECT id,name,gender from students TABLESAMPLE(BUCKET 1 OUT OF 10 ON id);

SELECT id,name,gender from students TABLESAMPLE(BUCKET 1 OUT OF 10 ON rand());

注意:

        使用colname作为随机依据,则其它条件不变下,每次抽样结果一致

        使用rand()作为随机依据,每次抽样结果都不同

TABLESAMPLE函数其他用法

-- 快速取前面部分数据 : 快但没有随机
-- 前100条
select  * from orders tablesample ( 100 rows );
-- 前10%数据
select  * from orders tablesample ( 10 percent );
-- 取1k或者1m的数据
select  * from orders tablesample (16k);
select  * from orders tablesample (167k);
select  * from orders tablesample (1m);

总结

1. 为什么需要抽样?

大数据体系下,表内容一般偏大,小操作也要很久

所以如果想要简单看看数据,可以通过抽样快速查看

2. TABLESAMPLE函数的使用

桶抽样方式,TABLESAMPLE(BUCKET x OUT OF y ON(colname | rand())),推荐,完全随机,速度略慢块抽样,使用分桶表可以加速

RLIKE 正则匹配

sql模糊查询关键字: like     任意0个或者多个: %     任意1个: _     ​

正则模糊查询关键字: rlike     任意0个或者多个: .*     任意1个: .     正则语法还有很多......

正则表达式

正则表达式是一种规则集合,通过特定的规则字符描述,来判断字符串是否符合规则。

RLIKE

Hive中提供RLIKE关键字,可以供用户使用正则和数据进行匹配。

我们以上一节中使用的订单表为例,来简单使用一下RLIKE正则匹配。

查找广东省的数据

SELECT * FROM orders WHERE useraddress RLIKE '.*广东.*';

查找用户地址是:xx省 xx市 xx区的数据

SELECT * FROM orders WHERE useraddress RLIKE '..省 ..市 ..区';

查找用户姓为张、王、邓

SELECT * FROM orders WHERE username RLIKE '[张王邓]\\S+';

查找手机号符合:188****0*** 规则

SELECT * FROM orders WHERE userphone  RLIKEE '188\\S{4}0\\S{3}';

总结

1. 什么是正则表达式

正则表达式就是一种规则的集合。通过特定的规则字符来匹配字符串是否满足规则的描述。

2. RLIKE的作用

可以基于正则表达式,对数据内容进行匹配

CTE表达式[补充]

CTE: 公用表表达式(CTE)是一个在查询中定义的临时命名结果集将在from子句中使用它。
注意: 每个CTE仅被定义一次(但在其作用域内可以被引用任意次),仅适用于当前运行的sql语句
语法如下:

   with 临时结果集的别名1  as (子查询语句),临时结果集的别名2  as (子查询语句)...select 字段名 from (子查询语句);

根据实际使用次数可以决定用"CTE表达式"还是"视图"还是"建表":  with 临时结果名 as (select语句)  > create view 视图名 as select语句   >   create table 表名 as select语句

-- with: CTE表达式
-- 可以理解成把子查询语句从主查询语句中抽取出来起别名,这样的话主查询语句就比较清晰了

withman as (select name,gender from students where gender = '男' ),woman as (select name,gender from students where gender = '女')
select * from man
union
select * from woman;


​内置虚拟列

Virtual Columns虚拟列

虚拟列是Hive内置的可以在查询语句中使用的特殊标记,可以查询数据本身的详细参数。

Hive目前可用3个虚拟列:

        INPUT__FILE__NAME,显示数据行所在的具体文件

        BLOCK__OFFSET__INSIDE__FILE,显示数据行所在文件的偏移量         ROW__OFFSET__INSIDE__BLOCK,显示数据所在HDFS块的偏移量

                此虚拟列需要设置:SET hive.exec.rowoffset=true 才可使用

SET hive.exec.rowoffset=true;
select * ,INPUT__FILE__NAME,BLOCK__OFFSET__INSIDE__FILE,ROW__OFFSET__INSIDE__BLOCK from students;

按照位置分组

-- 按照位置分组
select INPUT__FILE__NAME ,count(*) from students group by INPUT__FILE__NAME;

按照偏移量查询

select *,BLOCK__OFFSET__INSIDE__FILE  from students where BLOCK__OFFSET__INSIDE__FILE > 100;

总结

1. 什么是虚拟列,有哪些虚拟列?

虚拟列是Hive内置的可以在查询语句中使用的特殊标记,可以查询数据本身的详细参数。         INPUT__FILE__NAME,显示数据行所在的具体文件                BLOCK__OFFSET__INSIDE__FILE,显示数据行所在文件的偏移量         ROW__OFFSET__INSIDE__BLOCK,显示数据所在HDFS块的偏移量

                此虚拟列需要设置:SET hive.exec.rowoffset=true 才可使用

2. 虚拟列的作用

        查看行级别的数据详细参数

        可以用于WHERE、GROUP BY等各类统计计算中

        可以协助进行错误排查工作

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/469230.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

分段式爬虫和数据采集有什么关系

今天有人问我&#xff1a;分段式爬虫和数据采集有什么关系。 我想了想&#xff0c;我说我认为分段式爬虫其实是数据采集的一种手段或者说一种具体的方法。 咱就说数据采集吧&#xff0c;那就是想办法把各种有用的数据从不同的地方收集过来。这里面就有很多种方式&#xff0c;而…

最新网盘资源搜索系统,电视直播,Alist聚合播放

项目乃是基于 Vue 与 Nuxt.js 技术打造的网盘搜索项目&#xff0c;持续开源并保持维护更新。其旨在让人人皆可拥有属于自己的网盘搜索网站。强烈建议自行部署 更新日志&#xff1a; tv播放 新增Alist源聚合播放 新增批量删除功能 新增博客功能 &#xff08;分支&#xff1…

从零开始使用Intel的AIPC使用xpu加速comfyui

Intel的AIPC使用xpu加速跑comfyui 环境安装python环境搭建驱动及oneAPI安装创建python环境验证环境是否生效 ComfyUI的安装下载、汉化comfyui下载checkpoint 测试使用xpu加速测试使用cpu执行测试 环境安装 python环境搭建 直接下载Anaconda 下载地址 安装好后&#xff0c;通…

关于git使用的图文教程(包括基本使用,处理冲突问题等等)超详细

目录 用户签名,初始化git git提交流程图 提交到本地库 版本穿梭 分支操作 分支合并冲突 团队协作 github的使用 推送代码 克隆 拉取代码 团队协作冲突 团队协作之分支管理 推送分支到分支&#xff1a; 拉去远程库分支到本地库&#xff1a; 本地删除远程分支&am…

Android Studio打包时不显示“Generate Signed APK”提示信息

Android Studio打包时&#xff0c;默认显示“Generate Signed APK”提示信息&#xff0c;如下图所示&#xff1a; 如果在打包时不显示“Generate Signed APK”提示信息&#xff0c;解决办法是&#xff1a; Android Studio菜单栏&#xff0c;“File->Settings->Appearan…

【Go】-gRPC入门

目录 什么是gRPC 从Hello开始的简单使用 proto server端 client端 Proto的语法介绍 定义一个消息类型 指定字段类型 分配标识号 指定字段规则 添加更多消息类型 保留标识符&#xff08;Reserved&#xff09; 从.proto文件生成了什么&#xff1f; 标量数值类型 默…

后端SpringBoot学习项目-用户管理-增删改查

最终代码结构 仓库地址 Entity文件 数据库表设计 entity层实现 文件创建 ● 创建entity文件夹 ● 在entity层创建Java类&#xff0c;名字为User (关键字不可使用) 代码实现 package com.example.drhtspringboot.entity;import com.baomidou.mybatisplus.annotation.IdT…

网络管理之---3种网络模式配置

目标&#xff1a; 了解几个概念&#xff1a; 1.什么是IP&#xff1f;什么是IP地址&#xff1f; 2.什么是桥接、NAT、仅主机模式 3.端口&#xff1f; 4.什么是网络接口命名规则 5.网络管理器 IP&#xff1a;指网络之间互联的协议&#xff0c;是TCP/IP 体系中的网络协议 I…

uniapp解析蓝牙设备响应数据bug

本文章为了解决《uniapp 与蓝牙设备收发指令详细步骤(完整项目版)》中第十步的Array 解析成 number函数bug 1、原代码说明 function array16_to_number(arrayValue) {const newArray arrayValue.filter(item > String(item) ! 00 || String(item) ! 0)const _number16 ne…

【测试框架篇】单元测试框架pytest(3):用例执行参数详解

一、前言 上一篇内容介绍了用例编写的规则以及执行用例&#xff0c;执行用例时我们发现有些print输出内容&#xff0c;结果没有给我们展示&#xff0c;这是因为什么原因呢&#xff1f;接下来我们会针对这些问题进行阐述。 二、参数大全 我们可以在cmd中通过输入 pytest -h 或…

再见 阿里巴巴EasyExcel替代品EasyExcel-Plus即将诞生

最近阿里发布公告通知&#xff0c;停止对EasyExcel 更新和维护&#xff0c;EasyExcel 是一款知名的 Java Excel 工具库&#xff0c;由阿里巴巴开源&#xff0c;作者是玉霄&#xff0c;在 GitHub 上有 30k stars、7.5k forks。 据了解&#xff0c;EasyExcel作者玉霄)去年已经从…

VBA08-if语句

一、单行 If 语句 If x > 10 Then MsgBox "x is greater than 10"二、多行 If...Then...End If 语句 If x > 10 ThenMsgBox "x is greater than 10"y x 5 End If 三、If...Then...Else 语句 If condition Then 当条件为真时执行的代码块stateme…

闯关leetcode——202. Happy Number

大纲 题目地址内容 解题代码地址 题目 地址 https://leetcode.com/problems/happy-number/description/ 内容 Write an algorithm to determine if a number n is happy. A happy number is a number defined by the following process: Starting with any positive inte…

Apache Kylin 添加MSSQL等第三方数据源(MySQL 亦可)

Apache Kylin 添加MSSQL等数据源 Kylin 版本要求是3 PS&#xff1a;根据Kylin文档kylin 4.0和kylin 3.1的区别中所示&#xff1a; Kylin 3.1.0 支持 Kafka/Hive/JDBC 作为数据源Kylin 4.0 支持 Hive/CSV 作为数据源 官方文档请参考:Setup JDBC Data Source 第三方文档参考…

工位管理优化:Spring Boot企业级系统

3系统分析 3.1可行性分析 通过对本企业级工位管理系统实行的目的初步调查和分析&#xff0c;提出可行性方案并对其一一进行论证。我们在这里主要从技术可行性、经济可行性、操作可行性等方面进行分析。 3.1.1技术可行性 本企业级工位管理系统采用SSM框架&#xff0c;JAVA作为开…

java双向链表解析实现双向链表的创建含代码

双向链表 一.双向链表二.创建MyListCode类实现双向链表创建一.AddFirst创建&#xff08;头插法&#xff09;二.AddLast创建&#xff08;尾叉法&#xff09;三.size四.remove(指定任意节点的首位删除)五.removeAll(包含任意属性值的所有删除)六.AddIndex(给任意位置添加一个节点…

flink 同步oracle11g数据表到pg库

1. 关闭防火墙和selinux systemctl stop firewalld systemctl disable firewalld systemctl status firewalldvi /etc/selinux/config 修改为disabled2.安装java8 yum list java-1.8* yum install java-1.8.0-openjdk* -yjava -version3.下载和部署postgresql 看需求安装pg库…

用接地气的例子趣谈 WWDC 24 全新的 Swift Testing 入门(三)

概述 从 WWDC 24 开始&#xff0c;苹果推出了全新的测试机制&#xff1a;Swift Testing。利用它我们可以大幅度简化之前“老态龙钟”的 XCTest 编码范式&#xff0c;并且使得单元测试更加灵动自由&#xff0c;更符合 Swift 语言的优雅品味。 在这里我们会和大家一起初涉并领略…

Vue 2 —Vue Router 页面导航和参数传递

当从A页面跳转到B页面的时候把数据也一起传递过去&#xff0c;可用Vue Router 功能&#xff1a; 一、. this.$router.push 方法 Vue Router 是 Vue.js 的官方路由管理器&#xff0c;允许你在应用中进行页面导航&#xff08;即跳转到不同的 URL 路径&#xff09;。 this.$rout…