hive学习笔记
一、Hive基本概念
1.1 hive是什么
hive是基于hadoop的一个数仓分析工具,hive可以将hdfs上存储的结构化的数据,映射成一张表,然后让用户写HQL(类SQL)来分析数据
tel up down
1383838438 1345 1567
1383838439 5345 1567
1383838440 1241 16577
1383838441 3453 15757
1383838434 35355 1567567
按照手机号 分组,统计每个手机号的总流量
select tel,up+down from test;
hive的本质其实就是hadoop的一个客户端,hive底层不存储任何数据,hive表的数据存在hdfs上,hive表的元数据存在关系型数据库中
默认是derby,我们不一般不用默认的derby来存,一般都会修改为mysql。
元数据:描述数据的数据
Hive其实就是将用户写的HQL,给翻译成对应的mr模板,然后执行这些mr程序
hive底层执行引擎其实就是MapReduce,mr运行在yarn上
1.2 hive的优缺点
优点:操作简单,采用类sql的语法分析数据,门槛低,大大的降低了大数据分析的难度,通用性高
缺点:不够灵活,机翻粒度比较粗,调优困难。因为底层执行引擎还是mr,所以延迟较高,不能像关系型数据库那样,立马返回结果
并且底层存储是hdfs,不支持随机写,只能追加,所以hive不支持行级别的更新和删除(delete 和 update)
1.3 hive的架构原理
客户端:命令行客户端,jdbc客户端
数据存储:hdfs
底层执行引擎:mr
元数据库:hive将元数据默认存在derby中,我们一般在安装hive的时候,会修改成mysql
dirver四个器
解析器:将hql语句转换成AST抽象语法树,解析sql是否有误
编译器:将解析后的hql编译成逻辑执行计划,暂时不执行
优化器:对逻辑计划进行优化,调优
执行器:将优化后的逻辑计划执行,其实就是翻译成对应的mr程序,在yarn上运行
1.4 hive和关系型数据库对比
hive不是数据库,不是数据库,不是数据库
hive除了查询语言HQL跟SQL很像之外,别的跟数据库再也没有半点相似可言
数据更新 数据规模 执行延迟 底层引擎 数据存储
二、Hive安装
2.1 hive访问
1)通过hive自带的beeline客户端访问
beeline -u jdbc:hive2://hadoop102:10000 -n logcat
hive脚本访问
hive
2.2 hive交互命令
交互命令使用场景:在shell脚本里面不能人为的进入hive客户端交互写sql,所以要通过hive -e或者-f两个交互参数进行写入
1)hive -e
hive -e "select * from student"
2)hive -f
hive -f stu.sql
2.3 hive参数设置方式
1 通过配置文件设置 (永久生效)
在hive的家目录下面的conf文件夹下的hive-site.xml hive-env.sh hive-log4j2.properties
2 通过命令行参数来设置 (临时生效,只针对当前客户端连接)
hive -hiveconf 参数名=参数值
beeline -u jdbc:hive2://hadoop102:10000 -n logcat -hiveconf 参数名=参数值
3 通过set命令设置(临时生效,只针对当前客户端连接)
我们连接到hive的客户端以后,可以通过set语句来设置参数
查看所有参数设置
set;
查看单个参数的值
set 参数名;
设置单个参数的值
set 参数名=参数值;
参数设置优先级:
hive-default.xml < hive-site.xml < -hiveconf 参数名=参数值 < set 参数名=参数值
三、数据类型
3.1 基本数据类型
HIVE | MySQL | JAVA | 长度 | 例子 |
TINYINT | TINYINT | byte | 1byte有符号整数 | 2 |
SMALINT | SMALINT | short | 2byte有符号整数 | 20 |
INT | INT | int | 4byte有符号整数 | 20 |
BIGINT | BIGINT | long | 8byte有符号整数 | 20 |
BOOLEAN | 无 | boolean | 布尔类型,true或者false | TRUE FALSE |
FLOAT | FLOAT | float | 单精度浮点数 | 3.14159 |
DOUBLE | DOUBLE | double | 双精度浮点数 | 3.14159 |
STRING | VARCHAR | string | 字符系列。可以指定字符集。可以使用单引号或者双引号。 | ‘now is the time’ “for all good men” |
TIMESTAMP | TIMESTAMP | 时间类型 | ||
BINARY | BINARY | 字节数组 |
利用基本数据类型建表测试
create table test(id int,weight double,name string,money bigint);
利用insert语句按照指定的数据类型插入一条数据
insert into test values(1001,75,"zhangsan",1000000000000);
3.2 集合数据类型
数据类型 | 描述 | 语法示例 |
STRUCT | 和c语言中的struct类似,都可以通过“点”符号访问元素内容。例如,如果某个列的数据类型是STRUCT{first STRING, last STRING},那么第1个元素可以通过字段.first来引用。2 | struct() 例如 struct<street:string, city:string> |
MAP | MAP是一组键-值对元组集合,使用数组表示法可以访问数据。例如,如果某个列的数据类型是MAP,其中键->值对是’first’->’John’和’last’->’Doe’,那么可以通过字段名[‘last’]获取最后一个元素 | map() 例如map<string, int> |
ARRAY | 数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素,每个数组元素都有一个编号,编号从零开始。例如,数组值为[‘John’, ‘Doe’],那么第2个元素可以通过数组名[1]进行引用。 | Array() 例如array |
测试数据
songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing_10010
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing_10011
在hive中建表描述上述数据
create table person(
name string,
friends array<string>,
children map<string,int>,
address struct<street:string,city:string,email:int>
)
row format delimited fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n';
加载数据
load data local inpath '/opt/module/hive/datas/person.txt' into table person;
查询数据
select * from person;
查出来songsong 这个人的姓名,第一个朋友,孩子xiaoxiao song的年龄,和他的邮编
select name, friends[0],children['xiaoxiao song'],address.email from person where name = "songsong";
select name, friends[1],children['xiao song'],address.street from person;
3.3 类型转换
1)隐式(自动)类型转换
(1)任何整数类型都可以隐式地转换为一个范围更广的类型,如TINYINT可以转换成INT,INT可以转换成BIGINT。
(2)所有整数类型、FLOAT和STRING类型都可以隐式地转换成DOUBLE。
(3)TINYINT、SMALLINT、INT都可以转换为FLOAT。
(4)BOOLEAN类型不可以转换为任何其它的类型。
2)显示(强制)类型转换
CAST( vlaue AS type)
例如:select * from cast('1' as int) + 2;
四、DDL数据定义语言
4.1 库的DDL
1 创建数据库
CREATE DATABASE [IF NOT EXISTS] database_name --指定数据库名称
[COMMENT database_comment] --指定数据库描述
[LOCATION hdfs_path] --指定创建的数据库在hdfs上存储的路径
[WITH DBPROPERTIES (property_name=property_value, ...)]; --指定库的一些属性
案例实操
create database if not exists db_hive
comment "this is my first db"
with dbproperties ("name"="db_hive","owner"="logcat");create database if not exists db_hive;--创建数据库,并指定在hdfs上的路径
create database if not exists db_hive2
location '/db_hive2';
2 查询数据库
show databases;
3 查看数据库详情
--简单查看
desc database 数据库名;
--详细查看 (详细查看可以看到库的属性信息,简单查看看不到)
desc database extended 数据库名;
4 切换数据库
use 数据库名;
5修改数据库
只能修改数据库的属性信息,别的都无法更改。例如:库名,库的存储位置等元数据信息无法更改
alter database db_hive set dbproperties('createtime'='20200624');
6删除数据库
注意:删除掉数据库以后,hdfs上对应的目录也会删除,谨慎操作。
drop database 数据库名 cascade;--如果数据库不为空,可以在最后加上cascade强制删除
drop database 数据库名 cascade;--为了更严谨,我们可以在删除之前判断数据库是否存在
drop database if exists 数据库名 cascade;
4.2表的DDL
1创建表
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name --指定表名 【external 外部表/内部表】
[(col_name data_type [COMMENT col_comment], ...)] --指定表的列名,列类型 【列描述】
[COMMENT table_comment] --指定表的描述
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] --指定分区表的分区字段(分区字段可以是多个)
[CLUSTERED BY (col_name, col_name, ...) --指定分桶表的分桶字段
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] --指定分桶表桶内排序字段 指定分桶的个数
[ROW FORMAT DELIMITED --指定hive表在hdfs上存储的原始数据的格式[FIELDS TERMINATED BY char] --每行数据中字段的分隔符 ascII码表的第一个字符 ^A [COLLECTION ITEMS TERMINATED BY char] --集合元素分隔符 ascII码表的第二个字符 ^B[MAP KEYS TERMINATED BY char] --map集合中 key 和 value 的分隔符 ascII码表的第三个字符 ^C[LINES TERMINATED BY char] --每行数据的分隔符 默认值:'\n'
]
[STORED AS file_format] --指定hive的数据在hdfs上存储的格式
[LOCATION hdfs_path] --指定hive数据在hdfs上存储的路径 默认值 /user/hive/warehouse/数据库名
[TBLPROPERTIES (property_name=property_value, ...)] --指定表的属性
[AS select_statement] --按照as后面的查询语句的结果来创建表,复制表结构以及表数据
[LIKE table_name] --按照like后面的表结构来创建表,只复制表结构,不复制表数据
2 管理表和外部表
管理表(内部表):hive掌控者这个数据的生命周期,如果删除一个管理表,hdfs上存储的数据也跟着一起删除。所以一般我们创建管理表时,一般不会再location表的存储路径,就默认放在/user/hive/warehouse下
外部表:hive不完全掌控外部表的数据的生命周期,删除外部表,只删除hive表的元数据,不会删除掉hdfs上存储的数据
一般外部表都是先有的hdfs上的数据,然后我们创建一个外部表,手动指定这个外部表的存储路径
3 创建管理表
create table student(
id int,name string
)
row format delimited fields terminated by '\t';create table student2(
id int,name string
);
--根据AS select语句查询结构创建表,复制表结构,复制表数据
create table student3 as select * from student;--根据like 创建表,只复制表结构,不复制表数据
create table student4 like student;
查看表信息
--简单查看表信息
desc 表名;--详细查看表信息
desc formatted 表名;
删除管理表
注意:删除管理表同时会删除hdfs上对应目录的数据,谨慎操作,数据无价
drop table student3;
4 创建外部表
注意:外部表创建时要加external,外部表的好处就是,删除表的时候,只删除表的元数据信息,不删hdfs上存储的数据,更安全。
建表语句
create external table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t'
location '/company/dept';create external table if not exists emp(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int)
row format delimited fields terminated by '\t'
location '/company/emp';
5 外部表和内部表的转换
通过设置表属性“EXTERNAL”来控制表是外部表还是内部表
TRUE 是 外部表 FALSE 是内部表
alter table student set tblproperties('EXTERNAL'='TRUE/FALSE');
6修改表
重名表 注意:重命名表会一起修改hdfs上对应的目录名,前提这个表必须是管理表,并且创建这个管理表时没有自定义location
ALTER TABLE table_name RENAME TO new_table_name
更新列
更新列,列名可以随意修改,列的类型只能小改大,不能大改小(遵循自动转换规则)
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
增加列
ALTER TABLE table_name ADD COLUMNS (col_name data_type [COMMENT col_comment], ...)
替换列
ALTER TABLE table_name REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
7清空表
注意:只能truncate 管理表,外部表不能truncate。truncate的本质其实就是删除hdfs上对应路径的数据。
truncate table 表名;
五 DML数据操作
5.1 导入
5.1.1 load
1 通过load命令加载数据
load data [local] inpath '数据的path' [overwrite] into table student [partition (partcol1=val1,…)];
2 通过本地方式导入
注意:通过本地路径load,本质上其实就是将本地的文件put到hdfs对应的表目录
load data local inpath '/opt/module/hive/datas/student.txt' overwrite into table student;
3通过hdfs路径导入
注意:通过hdfs路径导入,本质是将hdfs上路径的数据剪切到对应的表的hdfs路径
load data inpath '/student.txt' into table student;
4 覆盖导入
注意:加上关键字overwrite 就是覆盖导入,不加的话,默认是追加导入
load data local inpath '/opt/module/hive/datas/student.txt' overwrite into table student;
5.1.2 insert
1) 基本模式插入
into是追加插入,overwrite是覆盖插入,此方式一般没人用
insert into/overwrite table student values(1018,'ss18'),(1019,'ss19');
2)通过查询结果插入
此方式用的比较多,一般都是查询原始表的数据到临时表,注意select之前不能加as,跟创建表时as select区分开
insert into table student2 select * from student where id < 1006;
insert overwrite table student2 select * from student where id < 1006;
注意:通过inset插入数据,数据格式和列的数量要一致才可以。
5.1.3 建表语句后+ as select
注意:建表语句后跟select语句时,as不能省略,跟上面的insert into刚好相反
create table student3 as select * from student;
5.1.4 建表指定location位置
提前把数据传到hdfs上,然后创建表的时候,指定表的位置为数据的路径
注意 location后面一定要给一个目录,不能直接给文件路径。
create table student5(
id int,name string
)
row format delimited fields terminated by '\t'
location '/student';
5.1.5 import导入
注意:使用import导入之前,得使用export导出数据,并且因为export会把数据和元数据一起导出,所以我们使用import导入的时候,表不能存在,否则会元数据冲突,报错。
import table student6 from '/stu';
5.2 数据导出
5.2.1 insert 导出
无格式导出
insert overwrite local directory '/opt/module/hive/datas/export/student'
select * from student;
格式化导出
insert overwrite local directory '/opt/module/hive/datas/export/student2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from student;
注意:因为insert导出,后面只能跟overwrite,所以我们给路径的时候,一定要具体,防止hive误删重要文件、
这个导出路径可以不存在,hive会帮我们创建路径
5.2.2hadoop命令导出
hadoop dfs -get /user/hive/warehouse/student/student.txt
/opt/module/hive/datas/export/student3.txt;
5.2.3hive shell 命令导出
hive -e 'select * from db_hive.student' > /opt/module/hive/datas/stu.txt
5.2.4 export 导出
注意;export导出只能导出到hdfs上,并且会将元数据和数据一起导出。
export table db_hive.student to '/stu';
六 查询DQL
SELECT [ALL | DISTINCT] select_expr, select_expr, ... --指定查询字段FROM table_reference --从哪个表查询[WHERE where_condition] --指定where过滤条件[GROUP BY col_list] --指定分组条件[ORDER BY col_list] --指定排序条件[CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list] --hive排序四个by][LIMIT number] --限制输出结果条数
6.1 基本查询
6.2 分组查询
6.3 join关联查询
6.4 排序
1 全局排序 order by
Order By:全局排序,只有一个Reducer,把所有数据都放在一个分区里面进行排序,这样才能保证输出的结果集是全局有序的。
ASC(ascend): 升序(默认)
DESC(descend): 降序
按照多个列,双重排序
--按照部门降序和工资升序排序
select deptno,sal,empno,ename,job from emp order by deptno DESC ,sal asc ;
2 单个reducer排序 sort by
注意:因为单独使用sort by的话,只能给每个分区内的数据排序,但是不能指定分区内的数据都有哪些,属于随机给数据分配分区
因此没人会单独使用sort by来排序,因为出来的结果集没有意义
3 分区 distribute by
指定分区字段,一般在sort by之前都会加上 distribute by
4 分区排序 Cluster by
cluster by属于 distribute by + sort by的结合,但是前提是 分区字段和排序字段是同一个的时候,才可以替换。并且排序只能升序排,不能再指定desc和asc
select * from emp distribute by deptno sort by deptno;
--上面的sql可以简写成下面的
select * from emp cluster by deptno;
七 分区表 分桶表
7.1分区表
hive存在问题:hive里面没有索引机制,每次查询的时候,hive会暴力扫描整张表。
分区表的本质就是分目录,按照业务需求,把数据分成多个目录存储,然后查询的时候就可以通过where条件指定对应的分区
创建分区表语法
create table dept_partition(
deptno int, dname string, loc string
)
partitioned by (day string)
row format delimited fields terminated by '\t';
分区字段属于分区表的一个伪列,数据里面并没有记录这列的值,分区字段的值体现在分区目录名上面。
往分区表里正常load数据,一定要指定分区
load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table dept_partition partition(day='20200401');
分区表查询数据
select * from dept_partition where day = '20200401';select * from dept_partition where day='20200401'
union
select * from dept_partition where day='20200402'
union
select * from dept_partition where day='20200403';select * from dept_partition where day = '20200401' or day = '20200402';
查看分区表有多少分区
show partitions dept_partition;
删除分区
注意:删除分区会一起删除掉分区内的数据
alter table dept_partition drop partition (day='__HIVE_DEFAULT_PARTITION__');
--删除多个分区 注意:多个分区间必须有逗号,没有会报错
alter table dept_partition drop partition(day='20200405'),partition(day='20200406');
增加分区
alter table dept_partition add partition(day='20200404') ;--增加多个分区 注意:多个分区间不能逗号,有会报错alter table dept_partition add partition(day='20200405') partition(day='20200406');
查看分区表信息
desc formatted dept_partition;
7.2 二级分区表
创建二级分区表
create table dept_partition2(
deptno int, dname string, loc string
)
partitioned by (day string, hour string)
row format delimited fields terminated by '\t';
给二级分区正常加载数据
load data local inpath '/opt/module/hive/datas/dept_20200401.log'
into table dept_partition2
partition(day='20200401',hour='12');
给二级分区增加分区
alter table dept_partition2 add partition(day='20200403',hour='01') partition(day='20200403',hour = '02');
给二级分区删除分区
alter table dept_partition2 drop partition(day='20200403',hour='01'),partition(day='20200403',hour = '02');
分区表和元数据对应三种方式
1 先上传 再修复表
msck repair table dept_partition2;
2 先上传数据,然后手动添加分区
3 直接load load数据的时候直接指定分区字段的值,这个时候不仅会上传数据,还会创建对应的分区
7.3 分桶表
创建分桶表
create table stu_buck(id int, name string)
clustered by(id)
into 4 buckets
row format delimited fields terminated by '\t';
查看分桶表信息
desc formatted stu_buck;
八 函数
查看系统自带函数
show functions;
查看函数具体用法
desc function extended upper;
1 空字段赋值 NVL
将null值转换成我们想要的值
select sal,comm,sal+ NVL(comm,0) money from emp;select ename,job,sal,mgr,comm,NVL(comm,mgr) from emp;
2 CASE WHEN
name | dept_id | sex |
悟空 | A | 男 |
大海 | A | 男 |
宋宋 | B | 男 |
凤姐 | A | 女 |
婷姐 | B | 女 |
婷婷 | B | 女 |
期望结果 求出不同部门男女各多少人
dept_Id 男 女
A 2 1
B 1 2
分析过程
第一步:先求出每个部门有多少人
selectdept_id,sum(1)
from emp_sex
group by dept_id ;
第二步:求出每个部门男女各多少人
selectdept_id,sum(case sex when '男' then 1 else 0 end) man,sum(case sex when '女' then 1 else 0 end) woman
from emp_sex
group by dept_id ;
3 行转列 多行转一列
相关函数
CONCAT(string A/col, string B/col…):返回输入字符串连接后的结果,支持任意个输入字符串;
CONCAT_WS(separator, str1, str2,...):它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL,返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
注意:CONCAT_WS must be "string or array "
COLLECT_SET(col):函数只接受基本数据类型,它的主要作用是将某字段的值进行去重汇总,产生array类型字段。
COLLECT_LIST(col):函数只接受基本数据类型,它的主要作用是将某字段的值进行不去重汇总,产生array类型字段。
原始数据
name | constellation | blood_type |
孙悟空 | 白羊座 | A |
大海 | 射手座 | A |
宋宋 | 白羊座 | B |
猪八戒 | 白羊座 | A |
凤姐 | 射手座 | A |
苍老师 | 白羊座 | B |
需求 把星座和血型一样的人归类到一起
射手座,A 大海|凤姐
白羊座,A 孙悟空|猪八戒
白羊座,B 宋宋|苍老师
实现:
SELECT t1.c_b,concat_ws('|',COLLECT_LIST(t1.name))
FROM
(select name,concat_ws(',',constellation,blood_type) c_b from person_info
) t1
group by t1.c_b;
4 列转行 一列转多行
相关函数
Split(str, separator):将字符串按照后面的分隔符切割,转换成字符array。
EXPLODE(col):将hive一列中复杂的array或者map结构拆分成多行。
LATERAL VIEW
用法:LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释:lateral view用于和split, explode等UDTF一起使用,它能够将一行数据拆成多行数据,在此基础上可以对拆分后的数据进行聚合。
lateral view首先为原始表的每行调用UDTF,UTDF会把一行拆分成一或者多行,lateral view再把结果组合,产生一个支持别名表的虚拟表。
原始数据
movie | category |
《疑犯追踪》 | 悬疑,动作,科幻,剧情 |
《Lie to me》 | 悬疑,警匪,动作,心理,剧情 |
《战狼2》 | 战争,动作,灾难 |
需求
《疑犯追踪》 悬疑
《疑犯追踪》 动作
《疑犯追踪》 科幻
《疑犯追踪》 剧情
《Lie to me》 悬疑
《Lie to me》 警匪
《Lie to me》 动作
《Lie to me》 心理
《Lie to me》 剧情
《战狼2》 战争
《战狼2》 动作
《战狼2》 灾难
实现 第一步,炸开类型
selectexplode(split(category,','))
from movie_info;
实现第二步:尝试使用join进行连接,但是不行,没有办法写join条件,造成了笛卡尔积
SELECT t1.movie,t2.category_name
from
(select movie from movie_info
) t1
left join
(selectexplode(split(category,',')) category_namefrom movie_info
) t2;
实现第三步:
我们的需求是·想让炸开后的临时表和炸开之前表所在行的其他字段进行join
hive帮我们实现了这个需求 lateral view (侧写视图)
selectmovie,category_name
from movie_info
LATERAL VIEW explode(split(category,',')) tmp as category_name;
5 窗口函数
1)定义
窗口函数属于sql中比较高级的函数
mysql从8.0版本才支持窗口函数,我们学的5.6,5.7都有窗口函数
oracle 里面一直支持窗口函数
hive也支持窗口函数
以下函数才是窗口函数
窗口函数:
LEAD LEAD(col,n, default_val):往后第n行数据 col 列名 n 往后第几行 默认为1 默认值 默认null
LAG LAG(col,n,default_val):往前第n行数据 col 列名 n 往前第几行 默认为1 默认值 默认null
FIRST_VALUE 在当前窗口下的第一个值 FIRST_VALUE (col,true/false) 如果设置为true,则跳过空值。
LAST_VALUE 在当前窗口下的最后一个值 LAST_VALUE (col,true/false)如果设置为true,则跳过空值。
标准聚合函数:
COUNT
SUM
MIN
MAX
AVG
分析排名函数
RANK
ROW_NUMBER
DENSE_RANK
NTILE
窗口函数=函数+窗口
窗口:函数在运算时,我们可以指定函数运算的数据范围
2)语法
窗口函数 over([partition by 字段] [order by 字段] [ 窗口语句])
partition by 给查出来的结果集按照某个字段分区,分区以后,开窗的大小最大不会超过分区数据的大小
一旦分区之后,我们必须在单个分区内指定窗口。
order by 给分区内的数据按照某个字段排序
3) 窗口语句
(ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING
两种特殊情况
当指定ORDER BY缺少WINDOW子句时,WINDOW规范默认为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。
如果同时缺少ORDER BY和WINDOW子句,则WINDOW规范默认为ROW BETWEENUND UNBOUNDED PRECEDING和UNBOUNDED FOLLOWING。
以下函数在over()里面只能分区和排序,不能自定义窗口大小了,也就是不能再写window字句
排序分析函数 都不能写 例如: Rank, NTile, DenseRank, CumeDist, PercentRank.
Lead 和 Lag不能写
窗口需求
需求1 查询在2017年4月份购买过的顾客及总人数
select name,count(1) over(rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING)
from business
where month(orderdate) =4
group by name;
由于窗口语句有两种特殊情况,我们这种刚好符合第二种,因此可以省略掉窗口语句
select name,count(1) over()
from business
where month(orderdate) =4
group by name;
需求2 查询顾客的购买明细及月购买总额
SELECT name,orderdate,cost,sum(cost ) over(partition by name,month(orderdate) )
from business;
需求3 上述的场景, 将每个顾客的cost按照日期进行累加
SELECT name,orderdate,cost,sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and CURRENT ROW) cost1,sum(cost) over(partition by name order by orderdate) cost2
from business;
需求4 查询顾客购买明细以及上次的购买时间和下次购买时间
selectname,orderdate,cost,LAG(orderdate,1,'无') over(partition by name order by orderdate) prev_time,LEAD(orderdate,1,'无') over(partition by name order by orderdate) next_time
from business;
需求5 查询顾客每个月第一次的购买时间 和 每个月的最后一次购买时间
注意:LAST_VALUE和FIRST_VALUE 需要自定义windows字句,否则出现错误
selectname,orderdate,cost,FIRST_VALUE(orderdate) over(partition by name,month(orderdate) order by orderdate rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) first_time,LAST_VALUE(orderdate) over(partition by name,month(orderdate) order by orderdate rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) last_time
from business;
需求6 查询前20%时间的订单信息
selectt1.*
FROM
(selectname,orderdate,cost,ntile(5) over(order by orderdate ) nsortfrom business
) t1
where t1.nsort = 1;
6 排名行数
RANK() 排序相同时会重复,会跳号
DENSE_RANK() 排序相同时会重复,不会跳号
ROW_NUMBER() 会根据顺序计算
SELECT name,subject,score,rank() over(PARTITION by subject order by score desc) rp,DENSE_RANK() over(PARTITION by subject order by score desc) drp,ROW_NUMBER() over(PARTITION by subject order by score desc) rowp
from score;
几个关键字总结
1 建表 :PARTITIONED BY(分区表) CLUSTERED BY(分桶表)
2 查询:ORDER BY(全局排序) SORT BY(区内排序)
DITRIBUTE BY(分区) CLUSTER BY(分区排序)
3 窗口函数:PARTITION BY(对数据分区) ORDER BY(排序)
7自定义udf
1 编写代码继承genericUDF类。实现里面的三个方法
2 创建临时函数
添加jar包的类路径给hive,注意是临时生效
add jar /opt/module/hive/datas/myudf.jar;
创建临时函数
create temporary function my_len as "com.logcat.hive.udf.MyStringLength";
删除临时函数
drop temporary function my_len;
注意:临时函数只跟会话有关系,只要会话不断,在当前会话下,任意一个库都可以使用。其他会话全都不能使用。
3 创建永久函数
注意:因为永久函数是永久生效的,我们推出当前会话以后,其他会话也要使用永久函数,因此我们就不能简单的使用add jar来添加hive的类路径了
创建永久函数
注意:此时要使用USING JAR的方式来添加函数的jar包类路径,并且这个路径必须是hdfs路径
create function my_len2 as "com.logcat.hive.udf.MyStringLength" USING JAR 'hdfs://hadoop102:9820/hivejar/myudf.jar';
删除永久函数
drop function my_len2;
注意:永久函数创建的时候,在函数名之前需要自己加上库名,如果不指定库名的话,会默认把当前库的库名给加上。
然后使用永久函数的时候,需要在指定的库里面操作,或者在其他库里面使用的话得加上 库名.函数名
相关文章:
hive学习笔记
一、Hive基本概念1.1 hive是什么hive是基于hadoop的一个数仓分析工具,hive可以将hdfs上存储的结构化的数据,映射成一张表,然后让用户写HQL(类SQL)来分析数据tel up down 1383838438 1345 1567 138383…...
7大体系防作弊,牛客放大招了!严肃笔试客户端上线!
如果问起学生对在线笔试的印象,“不公平”和“不服气”占了半壁江山。学生认为很多企业的在线笔试系统并不完善。原因一,不能有效地规避部分学生的作弊行为;原因二,在线考试系统不稳定,bug频出,导致笔试发挥…...
R语言广义可加模型在空气环境污染方面的应用(1)
粉丝私信我希望复制一篇文章的图片,图片来源于文章:Wu C, Yan Y, Chen X, Gong J, Guo Y, Zhao Y, Yang N, Dai J, Zhang F, Xiang H. Short-term exposure to ambient air pollution and type 2 diabetes mortality: A population-based time series st…...
CSDN 编程竞赛二十九期题解
竞赛总览 CSDN 编程竞赛二十九期:比赛详情 (csdn.net) 竞赛题解 题目1、订班服 小A班级订班服了!可是小A是个小糊涂鬼,整错了好多人的衣服的大小。小A只能自己掏钱包来补钱了。小A想知道自己至少需要买多少件衣服。 #include <cstdio…...
基于STM32采用CS创世 SD NAND(贴片SD卡)完成FATFS文件系统移植与测试
一、前言 在STM32项目开发中,经常会用到存储芯片存储数据。 比如:关机时保存机器运行过程中的状态数据,上电再从存储芯片里读取数据恢复;在存储芯片里也会存放很多资源文件。比如,开机音乐,界面上的菜单图…...
K_A12_007 基于STM32等单片机驱动AS608光学指纹识别模块 OLED0.96显示
K_A12_007 基于STM32等单片机驱动AS608光学指纹识别模块 OLED0.96显示一、资源说明二、基本参数参数引脚说明三、驱动说明对应程序:四、部分代码说明1、接线引脚定义1.1、STC89C52RCAS608光学指纹模块1.2、STM32F103C8T6AS608光学指纹模块五、基础知识学习与相关资料下载六、视…...
map和set介绍及其底层模拟实现
致努力前行的人: 要努力,但不要着急,繁花锦簇,硕果累累都需要过程! 目录 1.关联式容器 2.键值对 3.树形结构的关联式容器 3.1set的介绍 3.2set的使用 3.3multiset的使用 3.4map的使用 3.5multimap的使用 4.常见的面试题…...
实现一个比ant功能更丰富的Modal组件
普通的modal组件如下: 我们写的modal额外支持,后面没有蒙版,并且Modal框能够拖拽 还支持渲染在文档流里,上面的都是fixed布局,我们这个正常渲染到文档下面: render部分 <RenderDialog{...restState}visi…...
2023美赛F题思路数据代码分享
文章目录赛题思路2023年美国大学生数学建模竞赛选题&论文一、关于选题二、关于论文格式三、关于论文提交四、论文提交流程注意不要手滑美赛F题思路数据代码【最新】赛题思路 (赛题出来以后第一时间在CSDN分享) 最新进度在文章最下方卡片,加入获取一手资源 202…...
Flutter如何与Native(Android)进行交互
前言 上一篇文章《Flutter混合开发:Android中如何启动Flutter》中我们介绍了如何在Native(Android项目)中启动Flutter,展示Flutter页面。但是在开发过程中,很多时候并不是简单的展示一个页面即可,还会涉及…...
数据库主从复制和读写分离
主从数据库和数据库集群的一些问题 数据库集群和主从数据库最本质的区别,其实也就是data-sharing和nothing-sharing的区别。集群是共享存储的。主从复制中没有任何共享。每台机器都是独立且完整的系统。 什么是主从复制? 主从复制,是用来建立一个和主数…...
Java并发编程面试题——线程安全(原子性、可见性、有序性)
文章目录一、原子性高频问题1.1 Java中如何实现线程安全?1.2 CAS底层实现1.3 CAS的常见问题1.4 四种引用类型 ThreadLocal的问题?二、可见性高频问题2.1 Java的内存模型2.2 保证可见性的方式2.3 volatile修饰引用数据类型2.4 有了MESI协议,为啥还有vol…...
DialogFragment内存泄露问题能不能一次性改好
孽缘 自DialogFragment在Android3.0之后作为一种特殊的Fragment引入,官方建议使用DialogFragment代替Dialog或者AllertDialog来实现弹框的功能,因为它可以更好的管理Dialog的生命周期以及可以更好复用。 然而建议虽好,实用须谨慎,…...
java学习--多线程
多线程 了解多线程 多线程是指从软件或者硬件上实现多个线程并发执行的技术。 具有多线程能力的计算机因有硬件支持而能够在同一时间执行多个线程,提升性能。 并发和并行 并行:在同一时刻,有多个指令在CPU上同时执行并发࿱…...
90后阿里P7技术专家晒出工资单:狠补了这个,真香...
最近一哥们跟我聊天装逼,说他最近从阿里跳槽了,我问他跳出来拿了多少?哥们表示很得意,说跳槽到新公司一个月后发了工资,月入5万多,表示很满足!这样的高薪资着实让人羡慕,我猜这是税后…...
2023美赛C题:Wordle筛选算法
Wordle 规则介绍 Wordle 每天会更新一个5个字母的单词,在6次尝试中猜出单词就算成功。每个猜测必须是一个有效的单词(不能是不能组成单词的字母排列)。 每次猜测后,字母块的颜色会改变,颜色含义如下: 程…...
SpringBoot 集成 Kafka
SpringBoot 集成 Kafka1 安装 Kafka2 创建 Topic3 Java 创建 Topic4 SpringBoot 项目4.1 pom.xml4.2 application.yml4.3 KafkaApplication.java4.4 CustomizePartitioner.java4.5 KafkaInitialConfig.java4.6 SendMessageController.java5 测试1 安装 Kafka Docker 安装 Kafk…...
OpenCV 图像金字塔算子
本文是OpenCV图像视觉入门之路的第14篇文章,本文详细的介绍了图像金字塔算子的各种操作,例如:高斯金字塔算子 、拉普拉斯金字塔算子等操作。 高斯金字塔中的较高级别(低分辨率)是通过先用高斯核对图像进行卷积再删除偶…...
【自学Linux】Linux一切皆文件
Linux一切皆文件 Linux一切皆文件教程 Linux 中所有内容都是以文件的形式保存和管理的,即一切皆文件,普通文件是文件,目录是文件,硬件设备(键盘、监视器、硬盘、打印机)是文件,就连套接字&…...
CUDA C++扩展的详细描述
CUDA C扩展的详细描述 文章目录CUDA C扩展的详细描述CUDA函数执行空间说明符B.1.1 \_\_global\_\_B.1.2 \_\_device\_\_B.1.3 \_\_host\_\_B.1.4 Undefined behaviorB.1.5 __noinline__ and __forceinline__B.2 Variable Memory Space SpecifiersB.2.1 \_\_device\_\_B.2.2. \_…...
为什么重写equals必须重写hashCode
关于这个问题,看了网上很多答案,感觉都参差不齐,没有答到要点,这次就记录一下! 首先我们为什么要重写equals?这个方法是用来干嘛的? public boolean equals (Object object&#x…...
< 每日小技巧:N个很棒的 Vue 开发技巧, 持续记录ing >
每日小技巧:6 个很棒的 Vue 开发技巧👉 ① Watch 妙用> watch的高级使用> 一个监听器触发多个方法> watch 监听多个变量👉 ② 自定义事件 $emit() 和 事件参数 $event👉 ③ 监听组件生命周期常规写法hook写法ὄ…...
数据结构与算法之二分查找分而治之思想
决定我们成为什么样人的,不是我们的能力,而是我们的选择。——《哈利波特与密室》二分查找是查找算法里面是很优秀的一个算法,特别是在有序的数组中,这种算法思想体现的淋漓尽致。一.题目描述及其要求请实现无重复数字的升序数组的…...
训练自己的中文word2vec(词向量)--skip-gram方法
训练自己的中文word2vec(词向量)–skip-gram方法 什么是词向量 将单词映射/嵌入(Embedding)到一个新的空间,形成词向量,以此来表示词的语义信息,在这个新的空间中,语义相同的单…...
ubuntu系统环境配置和常用软件安装
系统环境 修改文件夹名称为英文 参考链接 export LANGen_US xdg-user-dirs-gtk-update 常用软件安装 常用工具 ping 和ifconfig工具 sudo apt install -y net-tools inetutils-ping 截图软件 sudo apt install -y net-tools inetutils-ping flameshot 录屏 sudo apt-get i…...
【1139. 最大的以 1 为边界的正方形】
来源:力扣(LeetCode) 描述: 给你一个由若干 0 和 1 组成的二维网格 grid,请你找出边界全部由 1 组成的最大 正方形 子网格,并返回该子网格中的元素数量。如果不存在,则返回 0。 示例 1&#…...
windows11安装sqlserver2022报错
window11安装SQL Server 2022 报错 糟糕… 无法安装SQL Server (setup.exe)。此 SQL Server安装程序介质不支持此OS的语言,或没有SQL Server英语版本的安装文件。请使用匹配的特定语言SQL Server介质;或安装两个特定语言MUI,然后通过控制面板的区域设置…...
Python快速上手系列--日志模块--详解篇
前言本篇主要说说日志模块,在写自动化测试框架的时候我们就需要用到这个模块了,方便我们快速的定位错误,了解软件的运行情况,更加顺畅的调试程序。为什么要用到日志模块,直接print不就好了!那得写多少print…...
【THREE.JS学习(1)】绘制一个可以旋转、放缩的立方体
学习新技能,做一下笔记。在使用ThreeJS的时候,首先创建一个场景const scene new THREE.Scene();接着,创建一个相机其中,THREE.PerspectiveCamera()四个参数分别为:1.fov 相机视锥体竖直方向视野…...
数仓实战 - 滴滴出行
项目大致流程: 1、项目业务背景 1.1 目的 本案例将某出行打车的日志数据来进行数据分析,例如:我们需要统计某一天订单量是多少、预约订单与非预约订单的占比是多少、不同时段订单占比等 数据海量 – 大数据 hive比MySQL慢很多 1.2 项目架…...
西安网站建设 招聘/手把手教你优化网站
原文:http://msdn.microsoft.com/zh-cn/library/8xx3tyca.aspx 连接到数据库服务器通常由几个需要很长时间的步骤组成。 必须建立物理通道(例如套接字或命名管道),必须与服务器进行初次握手,必须分析连接字符串信息&…...
做网站上是外部连接怎么改/qq群排名优化
docker容器中的环境变量 docker可以为容器配置环境变量。配置的途径有两种: 在制作镜像时,通过ENV命令为镜像增加环境变量。在容器启动时使用该环境变量。在容器启动时候,通过参数配置环境变量,如果与镜像中有重复的环境变量&…...
网站标题怎么做/提高网站收录的方法
计算机的发展、类型及其应用领域。计算机(computer)是一种能自动、高速进行大量算术运算和逻辑运算的电子设备。 其特点为:速度快、精度高、存储容量大、通用性强、具有逻辑判断和自动控制能力。第一台计算机:ENIAC,美国,1946年 宾…...
海报设计网站官网/南安网站建设
文章转自 http://ju.outofmemory.cn/entry/278349 https://www.jianshu.com/p/102c4df69af9 RequiredDuringSchedulingRequiredDuringExecution :在调度期间要求满足亲和性或者反亲和性规则,如果不能满足规则,则POD不能被调度到对应的主机上…...
坪山住房和建设局网站/外贸平台
题库来源:安全生产模拟考试一点通公众号小程序 安全员-B证(安全员)新版试题参考答案及安全员-B证(安全员)考试试题解析由安全生产模拟考试一点通题库老师及安全员-B证(安全员)操作证已考过的学员汇总,相对有效帮助安全员-B证(安全员)考试总结学员顺利通…...
暴雪国服/说到很多seo人员都转行了
Hadoop生态圈-Hive的自定义函数之UDTF(User-Defined Table-Generating Functions) 作者:尹正杰 版权声明:原创作品,谢绝转载!否则将追究法律责任。...