Hive基础5

一、窗口函数

聚合，取值函数 排序函数 over(partition by 分组字段 order by 字段 row between 起始行 and 结束行)

/*创建部门表*/
CREATE TABLE dept
(
    deptno INT PRIMARY KEY,
    dname  VARCHAR(50) comment '部门名称',
    loc    VARCHAR(50) comment '工作地点'
);
​
/*创建雇员表*/
CREATE TABLE emp
(
    empno    INT PRIMARY KEY,
    ename    VARCHAR(50),
    job      VARCHAR(50) comment '职位',
    mgr      INT comment '上级',
    hiredate DATE comment '入职时间',
    sal      DECIMAL(7, 2) comment '薪资',
    COMM     DECIMAL(7, 2) comment '奖金',
    deptno   INT comment '所属部门id'
);
​
/*创建工资等级表*/
CREATE TABLE salgrade
(
    grade INT PRIMARY KEY,
    losal INT comment '最低薪资',
    hisal INT comment '最高薪资'
);
​
/*插入dept表数据*/
INSERT INTO dept
VALUES (10, '教研部', '北京');
INSERT INTO dept
VALUES (20, '学工部', '上海');
INSERT INTO dept
VALUES (30, '销售部', '广州');
INSERT INTO dept
VALUES (40, '财务部', '武汉');
​
/*插入emp表数据*/
INSERT INTO emp
VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000, NULL, 10);
INSERT INTO emp
VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750, NULL, 20);
INSERT INTO emp
VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500, NULL, 30);
INSERT INTO emp
VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500, NULL, 10);
INSERT INTO emp
VALUES (1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000, NULL, 20);
INSERT INTO emp
VALUES (1013, '庞统', '分析师', 1004, '2001-12-03', 30000, NULL, 20);
INSERT INTO emp
VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000, 3000, 30);
INSERT INTO emp
VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500, 5000, 30);
INSERT INTO emp
VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500, 14000, 30);
INSERT INTO emp
VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000, 0, 30);
INSERT INTO emp
VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500, NULL, 30);
INSERT INTO emp
VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000, NULL, 10);
INSERT INTO emp
VALUES (1011, '周泰', '文员', 1008, '2007-05-23', 11000, NULL, 20);
INSERT INTO emp
VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000, NULL, 20);
​
​
/*插入salgrade表数据*/
INSERT INTO salgrade
VALUES (1, 7000, 12000);
INSERT INTO salgrade
VALUES (2, 12010, 14000);
INSERT INTO salgrade
VALUES (3, 14010, 20000);
INSERT INTO salgrade
VALUES (4, 20010, 30000);
INSERT INTO salgrade
VALUES (5, 30010, 99990);

窗口计算范围的指定

需要使用rows 进行指定 计算行数

范围的确认：

默认情况下没有指定partition by 的字段，范围是全表，如果指定了partition by ，范围是分组内的范围

可以通过rows指定计算行的范围大小 row between 起始行 and 结束行

指定计算行范围后，只对范围内的数据进行计算

指定范围关键字

向上无限制:  unbounded preceding 向上的行数没有限制
向上指定行数: 行数 preceding
当前行:      current row
向下指定行数: 行数 following
向下无限制:  unbounded following

不同关键字可以组合成一个范围

between 起始行范围 and 结束行范围

between 2 preceding and 1 following 范围查找是以当前行为基准 计算四行

between current row and 2 following 计算3行

select *,count(ename) over(rows between unbounded preceding and current row ) cnt from emp;
​
​
select *,count(ename) over(rows between 2 preceding and 2 following ) cnt from emp;
​
-- 范围顺序要注意，一般起始行写向上查找，结束行写向下查找
# select *,count(ename) over(rows between 2 following and 2 preceding ) cnt from emp;
select *,sum(sal) over(rows between 2 following and 3 following ) cnt from emp;

order by的计算范围说明

使用了order by 后会自带计算范围统计数据
rows between unbounded preceding and current row 
-- 统计每天的用户访问量，及截止当天的总用户访问量
-- 2019-02-11 6  6
-- 2019-02-12 1  7
select distinct dt,count(user_id) over(partition by dt order by dt) cnt ,count(user_id) over(order by dt) as ct from test5;

 

over中的 order by 是计算数据时，先排序在计算数据、

from 后的 order by 对计算后的结果排序

select *,count(user_id) over(partition by dt order by dt) as ct from test5 order by ct

二、CTE语法

CTE语法类似子查询，可以将一个select语句计算的结果当成一个新的临时表使用

-- 子查询，将子查询的结果当做表使用
select empno,ename from (
select * from emp) t1;
-- 基本用法
with 临时表名 as(查询语句)
select * from 临时表名
​
-- 多个计算结果保存
with tb1 as(查询语句),,
    tb2 as(查询语句 select * from tb1),
    tb3 as(查询语句)
    .....
select * from tb3 join tb2
with tb1 as(select * from emp)
select ename,sal from tb1;

将如下子查询改为cte语法实现

SELECT t2.shop, t2.user_id, t2.cnt
FROM (SELECT t1.*,
             row_number() over (partition BY t1.shop ORDER BY t1.cnt DESC) rk
      FROM (SELECT user_id, shop, count(*) AS cnt
            FROM test2
            GROUP BY user_id, shop) t1) t2
WHERE rk <= 3;
-- CTE语法可以方便代码阅读，将多个计算步骤拆分
with tb1 as(
    SELECT user_id, shop, count(*) AS cnt
            FROM test2
            GROUP BY user_id, shop
),
    tb2 as(
        SELECT tb1.*,
             row_number() over (partition BY tb1.shop ORDER BY tb1.cnt DESC) rk
      FROM tb1
    )
select * from tb2 where rk <=3;

窗口函数和CTE语法是mysql8.0以上版本支持

三、爆炸函数和合并函数

函数的分类:

udf(user define function) 函数 数据输入多少，返回多少行数据 计算是一进一出

  • hive中的大部分函数都是udf函数

udaf （user define aggregation function）函数 输入多行数据返回一行结果 多进一出

  • 聚合方法

    • sum

    • avg

    • count

    • max

    • min

udtf函数 输入一行返回多行 一进多出

  • explode方法

    • 爆炸函数，可以将数组中的数据拆分多行

create table tb_user(
    id int,
    name string,
    hobby string
)row format delimited fields terminated by ',';
​
select id,name,split(hobby,'-') as hobby  from tb_user;
-- explode不能直接和其他字段出现在select中
select explode(split(hobby,'-')) as hobby  from tb_user;
-- 使用侧视图的方法和其他字段一起展示
-- lateral view 爆炸函数 表名 as 字段名
select id,name,new_hobby from tb_user lateral view explode(split(hobby,'-')) tb1 as new_hobby;
​
-- 不能简单使用join进行关联数据
select * from tb_user join (select explode(split(hobby,'-')) as hobby  from tb_user) tb1;

  • collect方法

    • 将一列数据中的多行数据合并成一行

-- collect_list 合并后不会去重
select collect_list(name) from tb_user;
-- collect_list 合并会对数据进行去重
select collect_set(name) from tb_user;

用户访问数据

create table tb_visit(
    id int,
    name string,
    url string
)row format delimited fields terminated by ',';
​
select * from tb_visit;
​
select collect_set(name) from tb_visit;
​
-- 统计不同用户访问了哪些网址
select name,collect_set(url) from tb_visit group by name;

四、随机抽样

从海量数据中随机抽取部分样本数据进行计算得到的结果趋势和整体趋势一致

  • 格式

SELECT ... FROM tbl TABLESAMPLE(BUCKET x OUT OF y ON(colname | rand()))
​
y表示将表数据随机划分成y份（y个桶）
x表示从y里面随机抽取x份数据作为取样
colname表示随机的依据基于某个列的值
rand()表示随机的依据基于整行

随机抽样的原理，是先将数据进行分桶，在从多个分桶中抽取数据

create table tb_stu(
    id int,
    name string,
    age int,
    gender int,
    dt string
)row format delimited fields terminated by ',';
​
-- 指定字段进行分桶抽样
select * from tb_stu tablesample (bucket 2 out of 30 on name);
-- 随机抽取
with tb1 as (select *
             from tb_stu tablesample (bucket 2 out of 20 on rand()))
select gender,count(*)
from tb1 group by gender;  

五、虚拟列

hive表中自带字段列，在进行select查询时没有指定，不会出现在 查询结果中

可以在select中指定这些字段显示内容

INPUT__FILE__NAME，显示数据行所在的具体文件
BLOCK__OFFSET__INSIDE__FILE，显示数据行所在文件的偏移量
ROW__OFFSET__INSIDE__BLOCK，显示数据所在HDFS块的偏移量 
    此虚拟列需要设置：SET hive.exec.rowoffset=true 才可使用
select *,INPUT__FILE__NAME from brand;
select * from brand where INPUT__FILE__NAME='hdfs://node1:8020/user/hive/warehouse/pydata.db/brand/000001_0';
​
select *,BLOCK__OFFSET__INSIDE__FILE from tb_stu;
SET hive.exec.rowoffset=true;
select *,ROW__OFFSET__INSIDE__BLOCK from tb_stu;
​

六、快速建表

基于已经存在的表创建新的表，对原始表复制一个新的表

  • like语法

    • 将原始表的元数据(也就是表的名字字段等信息复制一份)，不会复制行数据

    • 创建之后是一个空表

create table 新的表名 like 原始表名

  • as语法

    • 会将原始数据表的内容全部复制一份到新表中

create table 新的表名 as select * from 原始表

select * from tb_user;
create table tb_user_new like tb_user;
select * from tb_user_new;
​
create table tb_user_new_new as select * from tb_user;
​
select * from tb_user_new_new;

七、视图

视图本质是将select查询语句进行保存，每次进行数据计算时，如果使用相同的sql语句，就不需要再重新写一遍

create view 视图名  as 查询语句
-- 将计算的sql语句保存在视图中
create view sum_view as select sum(if(name is not null,1,0) ) from tb_user;
​
-- 当查询视图时，就会自动执行视图中的sql语句
select * from sum_view;

mysql中也是可以使用视图

-- （1）修改表字段注解和表注解
use hive3;
alter table COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;
alter table TABLE_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
​
-- （2）修改分区字段注解
alter table PARTITION_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8 ;
alter table PARTITION_KEYS modify column PKEY_COMMENT varchar(4000) character set utf8;
​
-- （3）修改索引注解
alter table INDEX_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;

八、数据压缩和存储格式

8-1 数据压缩

hive的表的行数据是以文件方式存在hdfs

优点：

减少存储磁盘空间，降低单节点的磁盘IO。

由于压缩后的数据占用的带宽更少，因此可以加快数据在Hadoop集群流动的速度，减少网络传输带宽。

缺点：

需要花费额外的时间/CPU做压缩和解压缩计算。

压缩格式压缩格式所在的类
Zliborg.apache.hadoop.io.compress.DefaultCodec
Gziporg.apache.hadoop.io.compress.GzipCodec
Bzip2org.apache.hadoop.io.compress.BZip2Codec
Lzocom.hadoop.compression.lzo.LzoCodec
Lz4org.apache.hadoop.io.compress.Lz4Codec
Snappyorg.apache.hadoop.io.compress.SnappyCodec

默认文件的压缩方式是Zlib，可以在建表的时候指定表数据按照那种压缩方式存储数据，zlib压缩的占用空间少，但是消耗的时间

实际开发建议使用Snappy 压缩空间和速度比较均衡

8-2 存储格式

表数据存储方式有两种

一个行存储 一个列存储

逻辑表中的数据，最终需要落到磁盘上，以文件的形式存储，有两种常见的存储形式。行式存储和列式存储。

Hive支持的存储数的格式主要有：TEXTFILE（行式存储） 、SEQUENCEFILE(行式存储)、ORC（列式存储）、PARQUET（列式存储）。

默认的存储格式是TEXTFILE（行式存储)

列存储的数据会转为二进制存储，所以文件打开后乱码

STORED AS orc tblproperties ("orc.compress"="SNAPPY");
create table tb_visit_new(
    id int,
    name string,
    url string
)  -- stored as指定orc（列存储）存储方式  tblproperties("orc.compress"="SNAPPY") 指定压缩方式
    stored as orc tblproperties("orc.compress"="SNAPPY");
​
insert into tb_visit_new select * from tb_visit;
​
​
select * from tb_visit_new;

九、hive指令行

使用hive的shell指令，进行hive操作

将写好的sql文件上传服务器，使用hive指令运行

 

使用hive -f 执行sql文件，每次产生新数据后不需要重写sql语句，只需要执行sql文件

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

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

2024团体程序设计天梯赛L1-101 别再来这么多猫娘了！

2024团体程序设计天梯赛L1-101 别再来这么多猫娘了！

题目链接L1-101 别再来这么多猫娘了&#xff01; #include<iostream> #include<stdio.h> #include<string.h> #include<string> #include<algorithm> using namespace std; string s[105], text; int n, k, ans, a[5005];int main() { // ios::s…
阅读更多...
【项目】基于JDBC+MySQL的Java教务管理系统（附源码+论文说明）

【项目】基于JDBC+MySQL的Java教务管理系统（附源码+论文说明）

摘要 随着信息技术的不断发展&#xff0c;教育管理也在向数字化、智能化方向迈进。Java作为一种广泛应用于企业级应用开发的编程语言&#xff0c;与数据库技术的结合更是为教务管理系统的开发提供了强大的支持。 本文将介绍基于JDBC&#xff08;Java Database Connectivity&a…
阅读更多...
短视频不够500有效粉丝怎么涨？如何涨有效粉丝？巨量千川投流怎么涨粉的？

短视频不够500有效粉丝怎么涨？如何涨有效粉丝？巨量千川投流怎么涨粉的？

近期&#xff0c;一些使用抖音橱窗功能的用户遇到了一个问题&#xff0c;他们在挂橱窗时会出现“有效粉丝不足500”无法正常挂橱窗的提示。这个问题是由于抖音平台改变了规则所致。现在&#xff0c;只有通过观看视频而非刷关注的粉丝才被算作有效粉丝&#xff0c;其他的粉丝都将…
阅读更多...
39. UE5 RPG角色释放技能时转向目标方向

39. UE5 RPG角色释放技能时转向目标方向

在上一篇&#xff0c;我们实现了火球术可以向目标方向发射&#xff0c;并且还可以按住Shift选择方向进行攻击。技能的问题解决&#xff0c;现在人物释放技能时&#xff0c;无法朝向目标方向&#xff0c;接下来我们解决人物的问题。 实现思路&#xff1a; 我们将使用一个官方的…
阅读更多...
（CVPR，2023）SAN：用于开放词汇语义分割的边缘适配网络

（CVPR，2023）SAN：用于开放词汇语义分割的边缘适配网络

文章目录 相关论文相关资料摘要引言方法对视觉 token 的特征融合使用注意力偏差进行掩码识别分割图像生成 实验 相关论文 &#xff08;CVPR&#xff0c;2024&#xff09;SED&#xff1a;一个用于开放词汇语义分割的简单编解码器 &#xff08;CVPR&#xff0c;2024&#xff09;…
阅读更多...
python int占几个字节

python int占几个字节

《深入理解计算机系统》这本书上面提到了在32位机器和64机器中int类型都占用4个字节。《The C Programming language》这本书&#xff0c;里面有一句话是这样的&#xff1a;Each compiler is free to choose appropriate sizes for its own hardware, subject only to the rest…
阅读更多...
https协议的加密方式详解

https协议的加密方式详解

各位大佬能多多点赞关注评论收藏&#xff0c;球球各位大佬们了&#xff01;&#xff01; &#xff01; 目录 1.为什么要加密&#xff1f; 2.如何加密 1.密钥&#xff08;yue,第四声&#xff09; 2.对称加密 3.非对称加密 4.公证机构 3.总结 1.为什么要加密&#xff1f;…
阅读更多...
systemverilog中位的选择

systemverilog中位的选择

常用的变量类型就是 reg 和 wire &#xff0c;这两种类型可以定义 一位的变量&#xff0c;也可以定义多位&#xff0c;其中 1 bit 的变量称为 标量(scalar)&#xff0c;多 bit 的变量称为 向量(vector)&#xff0c;如下所示&#xff1a; wire o_nor; // singl…
阅读更多...
【树莓派】如何刷个系统给树莓派4B，如何ssh登陆到树莓派

【树莓派】如何刷个系统给树莓派4B，如何ssh登陆到树莓派

文章目录 下载树莓派镜像下载烧写软件烧写编辑设置连接树莓派4B重启ssh查看树莓派IPssh远程连接问询、帮助 下载树莓派镜像 https://www.raspberrypi.com/software/operating-systems/#raspberry-pi-os-64-bit 下载烧写软件 https://www.raspberrypi.com/software/ 烧写 编辑…
阅读更多...
7. Spring Boot 创建与使用

7. Spring Boot 创建与使用

经过前面的六篇文章&#xff0c;Spring Framework的知识终于大致讲完了&#xff0c;但是Spring AOP还没提到&#xff0c;个人认为Spring AOP更适合放在Spring MVC之后再讲解&#xff0c;而讲解Spring MVC前先学习Spring Boot的目的也是为了在学习Spring MVC的时候直接使用Sprin…
阅读更多...
EelasticSearch使用

EelasticSearch使用

1. Easy-ES介绍 Easy-Es 2. 导入依赖包 <dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId><exclusions>//排除框架中原有的依赖包<exclusion><groupId>org.elast…
阅读更多...
Vue3 + Js + Element-Plus + VueX后台管理系统通用解决方案

Vue3 + Js + Element-Plus + VueX后台管理系统通用解决方案

前言 本文是作为学习总结而写的一篇文章&#xff0c;也是方便以后有相关需求&#xff0c;可以直接拿来用&#xff0c;也算是记录吧&#xff0c;文中有一些文件的引入&#xff0c;没给出来&#xff0c;完整项目地址&#xff08;后续代码仓库放这里&#xff09; 1、layout解决方…
阅读更多...
kaggle 纽约预测出租车价格 得分 5.34072

kaggle 纽约预测出租车价格 得分 5.34072

流程 导入所要使用的包引入kaggle的数据集csv文件查看数据集有无空值填充这些空值提取特征分离训练集和测试集调用模型 数据资源获取 数据资源获取 导入需要的包 import numpy as np import pandas as pd import matplotlib.pyplot as plt import seaborn as sns引入kaggl…
阅读更多...
基于CH32V103的多功能推杆设计

基于CH32V103的多功能推杆设计

一、项目简介 “创意源于生活&#xff0c;工具始于懒惰。” 整体造型外观参考了最近比较火的夫妻游戏《双人成行》第一关里面那个吸尘器的推杆开关&#xff0c;结构中采用阻尼器/滚珠轴承等器件&#xff0c;使其非常具有质感和手感。功能上我构思不能只有电脑开关这么简单地一…
阅读更多...
高架学习笔记之软件架构风格

高架学习笔记之软件架构风格

目录 零、什么是软件架构风格 一、常见的软件架构风格 二、数据流风格 2.1. 批处理风格 2.2. 管道-过滤器风格 三、调用/返回风格 3.1. 主/子程序风格 3.2. 面向对象风格 3.3. 层次型风格 3.4. 客户端/服务器风格 3.4.1. 两层C/S体系结构 3.4.2. 三层C/S体系结构 …
阅读更多...
Redis报错：CROSSSLOT Keys in request don‘t hash to the same slot的解决方案

Redis报错：CROSSSLOT Keys in request don‘t hash to the same slot的解决方案

最近&#xff0c;项目上线的时候&#xff0c;出现了一个Redis的报错&#xff1a;CROSSSLOT Keys in request dont hash to the same slot&#xff0c;这个在内网环境下无法复现&#xff0c;因为正式环境的Redis是cluster集群模式&#xff0c;而我们内网环境是单机模式。(后面我…
阅读更多...
k8s安装，linux-ubuntu上面kubernetes详细安装过程

k8s安装，linux-ubuntu上面kubernetes详细安装过程

官方文档&#xff1a;https://kubernetes.io/zh-cn/docs/setup/production-environment/container-runtimes/ 环境配置 该部分每个主机都要执行 如果你确定不需要某个特定设置&#xff0c;则可以跳过它。 设置root登录 sudo passwd root sudo vim /etc/ssh/sshd_config Perm…
阅读更多...
深入OceanBase内部机制：资源隔离实现的方式总结

深入OceanBase内部机制：资源隔离实现的方式总结

码到三十五 &#xff1a; 个人主页 心中有诗画&#xff0c;指尖舞代码&#xff0c;目光览世界&#xff0c;步履越千山&#xff0c;人间尽值得 ! 目录 1. 为何HTAP需要资源隔离2. OceanBase的资源隔离机制概述租户间资源隔离租户内资源隔离物理资源隔离大查询请求的隔离优先级…
阅读更多...
排序 “贰” 之选择排序

排序 “贰” 之选择排序

目录 ​编辑 1. 选择排序基本思想 2. 直接选择排序 2.1 实现步骤 2.2 代码示例 2.3 直接选择排序的特性总结 3. 堆排序 3.1 实现步骤 3.2 代码示例 3.3 堆排序的特性总结 1. 选择排序基本思想 每一次从待排序的数据元素中选出最小&#xff08;或最大&#xff09;的一个…
阅读更多...
【剪映专业版】13快速为视频配好音：清晰、无噪声、对齐

【剪映专业版】13快速为视频配好音：清晰、无噪声、对齐

视频课程&#xff1a;B站有知公开课【剪映电脑版教程】 使用场景&#xff1a;视频无声音或者视频有声音但是需要更改声音 时间指示器在哪里&#xff0c;就从哪里开始 红色按钮&#xff1a;开始录音 声音波纹&#xff1a;蓝色最佳&#xff0c;黄色或红色声音太大&#xff0c;…
阅读更多...
最新文章

Copyright @ 2022~2023