响应式网站 手机站,本地部署 wordpress,wordpress在线不能播放视频,哪家建设公司宣传搞得好文章作者邮箱#xff1a;yugongshiyesina.cn 地址#xff1a;广东惠州 ▲ 本章节目的
⚪ 掌握HIve的基本SQL语句和注意问题#xff1b;
⚪ 掌握HIve的表结构#xff1b;
⚪ 掌握HIve的数据类型#xff1b;
⚪ 掌握HIve的基础函数和窗口函数#xff1b; …文章作者邮箱yugongshiyesina.cn 地址广东惠州 ▲ 本章节目的
⚪ 掌握HIve的基本SQL语句和注意问题
⚪ 掌握HIve的表结构
⚪ 掌握HIve的数据类型
⚪ 掌握HIve的基础函数和窗口函数 一、基本SQL
1. SQL的执行方式
1. 通过hive -e的方式来执行指定的SQL例如hive -e create database demo;。
2. 通过hive -f的方式来执行指定的SQL脚本例如hive -f test.sql。
3. 进入Hive的命令行里来执行指定的SQL。
2. 注意问题
1. 如果不指定那么Hive默认将数据放在HDFS的/user/hive/warehouse目录下。
2. 在Hive中每一个database都对应了一个单独的目录。
3. 在Hive启动的时候自带一个default库。如果在建表的时候没有指定那么默认也是将表放在default库下。
4. alter database可以修改指定库的属性但是不能修改库的库名以及存储位置。
5. 在Hive中没有主键的概念不支持主键。
6. 在Hive中每一个table也对应了一个单独的目录。
7. 在Hive中需要在建表的时候指定字段之间的间隔符号。
8. insert into表示向表中追加数据insert overwrite表示将表中的清空之后再添加当前的数据(覆盖)。
9. 需要注意的是Hive中的数据会以文件的形式落地到HDFS上在Hive的默认文件格式(textfile - 文本)下不支持修改(update和delete)操作。如果需要Hive支持修改操作那么需要在建表的时候指定文件格式为orc格式。但是在实际开发中因为一般利用Hive存储历史数据所以很少或者根本不对Hive中的数据进行修改因此一般不适用orc格式。另外orc格式虽然支持update和delete操作但是效率非常低。
3. 基本SQL SQL 解释 create database demo; 创建demo库 create database if not exists demo4; 如果demo4库不存在则创建 create database demo5 location /demo5; 创建demo5库同时指定存储位置 show databases; 查看所有的库 show databases like demo*; 查看demo开头的库 desc database demo; 描述demo库 desc database extended demo; 描述demo库的详细信息 use demo; 使用demo库 alter database demo set dbproperties (date2020-12-25); 修改demo库的属性 drop database demo5; 删除demo5库 drop database if exists demo4; 如果demo4库存在则删除 drop database demo3 cascade; 强制删除demo3库及其中的表 create table person (id int, name string, age int); 建立person表包含idnameage三个字段 insert into table person values(1, colin, 19); 插入数据 select * from person; 查询数据 load data local inpath /home/hivedemo/person.txt into table person; 从本地加载文件到Hive表中 drop table person; 删除表 create table person (id int, name string, age int) row format delimited fields terminated by ; 建表指定字段之间的间隔符号为空格 create table p2 like person; 创建和person表结构一致的p2表 describe p2; 或者 desc p2; 描述p2 show tables; 查看所有的表 insert into table p2 select * from person where age 18; 从person中查询数据将age18的数据放到p2表中 create table if not exists p3 like person; 如果p3表不存在则创建和person结构一致的p3表 from person insert overwrite table p2 select * where age 18 insert into table p3 select * where id 5; 从person表中查询数据然后将查询出来的age18的数据覆盖到p2表中同时将id5的数据追加到p3表中 create table if not exists p4 as select * from person where age 18; 创建p4表同时在建表的时候将person表中age18的数据放进去 insert overwrite local directory /home/hivedata row format delimited fields terminated by \t select * from person where age 18; 将person表中age18的数据查询出来放到本地磁盘的/home/hivedata目录下 insert overwrite directory /person row format delimited fields terminated by , select * from person where id 6; 将person表中id6的数据查询出来放到HDFS的地址路径下 alter table person rename to p1; 重命名表
二、基本表结构
1. 内部表和外部表
1. 如果在Hive中手动建表手动添加数据(包括insert和load方式)那么这种表称之为内部表。
2. 如果在Hive中手动建表来管理HDFS上已经存在的数据那么这种表称之为外部表。
3. 相对而言在工程或者项目建立的初期一般会建立外部表来管理HDFS上已经存在的数据但是当外部表建立之后不代表数据能够直接处理使用还需要对数据进行抽取整理等清洗操作来建立能够实际使用的内部表。
4. 建立外部表
create external table orders(orderid int, orderdate string, productid int, num int) row format delimited fields terminated by location /orders;
5. 查看表的详细信息
desc extended orders;
#或者
desc formatted orders;
#在其中寻找Table Type:EXTERNAL_TABLE
6. 在Hive中删除内部表的时候这个表对应的目录也会从HDFS上删除删除外部表的时候只是删除了元数据而这个表对应的目录没有从HDFS上移除。
2. 分区表
1. 分区表的作用通常是对数据来进行分类。
2. 建立分区表
create table cities (id int, name string) partitioned by (province string) row format delimited fields terminated by ;
3. 加载数据
load data local inpath /home/hivedemo/hebei.txt into table cities partition(provincehebei);
load data local inpath /home/hivedemo/jiangsu.txt into table cities partition(provincejiangsu);
4. 在Hive中每一个分区对应一个单独的目录。
5. 如果在查询数据的时候指定了分区字段那么分区表的查询效率就会高于未分区的表如果在查询数据的时候进行了跨分区查询那么此时未分区表的查询效率就要高于分区表。
6. 删除分区
alter table cities drop partition(province henan);
7. 手动添加分区
alter table cities add partition (provincehenan) location /user/hive/warehouse/demo.db/cities/provincehenan;
8. 修复表
msck repair table cities;
9. 修改分区名
alter table cities partition(province__HIVE_DEFAULT_PARTITION__) rename to partition (provincesichuan);
10. 在Hive的分区表中分区字段在原始数据中并不存在而是在加载数据的时候来手动指定。如果分区字段在原始数据中存在那么需要考虑动态分区。
11. 动态分区 - 将未分区表中的数据查询出来放到分区表中
#建立临时表去管理原始数据
create table cities_tmp (tid int, tprovince string, tcity_name string) row format delimited fields terminated by ;
#将数据加载到临时表中
load data local inpath /home/hivedemo/cities.txt into table cities_tmp;
#关闭严格模式开启动态分区
set hive.exec.dynamic.partition.modenonstrict;
#将未分区表中的数据查询出来放到分区表中
insert into table cities partition(province) select tid, tcity_name, tprovince from cities_tmp distribute by tprovince;
12. 在Hive中也允许对多字段来进行分区。此时前一个字段形成的目录会包含后一个字段形成的目录。多字段分区通常用于对数据来进行多级分类例如省市县学生的年级和班级商品的分类等。
#原始数据
1 1 1 tom
1 1 2 sam
1 1 3 bob
1 1 4 alex
1 2 1 bruce
1 2 2 cindy
1 2 3 jack
1 2 4 john
2 1 1 tex
2 1 2 helen
2 1 3 charles
2 1 4 frank
2 2 1 david
2 2 2 simon
2 2 3 lucy
2 2 4 lily
#建立临时表来管理数据
create table students_tmp (grade int, class int, id int, name string) row format delimited fields terminated by ;
#加载数据
load data local inpath /home/hivedemo/students.txt into table students_tmp;
#根据年纪和班级将学生区分开 - 建立分区表
create table students(id int, name string) partitioned by (grade int, class int) row format delimited fields terminated by ;
#动态分区
insert into students partition(grade, class) select id, name, grade, class from students_tmp distribute by grade, class;
3. 分桶表
1. 分桶表的作用是对数据进行抽样。
2. 在实际生产过程中当数据量比较大且又希望对数据进行快速分析获取分析结果并且还能够容忍一定程度上的分析误差的时候可以考虑对数据进行抽样处理。需要注意的是在抽样的时候抽样字段和要分析的字段之间不能又关联性。例如年龄和身高之间就是又关联性的姓名和身高之间是没有关联性的。
3. 在Hive中分桶机制默认是不开启的首先需要开启分桶机制。
set hive.enforce.bucketing true;
4. 建立分桶表 - 桶数指定的越多执行分桶的时候耗费的资源越多。
create table students_bucket(id int, name string) clustered by (name) into 4 buckets row format delimited fields terminated by \t;
5. 在Hive中向分桶表中添加数据的时候只能使用insert方式而不能使用load方式使用load方式不会对数据进行分桶。
insert overwrite table students_bucket select id, name from students;
6. 每一个桶都会对应一个单独的结果文件。
7. 对数据进行抽样。
select * from students_bucket tablesample (bucket 1 out of 2 on name);
8. bucket x out of yx表示从第一个桶的第几行开始抽y表示抽样步长即每几行抽一次。
三、数据类型
1. 概述
1. 在Hive中提供了非常丰富的数据类型大概可以分为两类基本类型和复杂类型。
2. 基本类型包含tinyintsmallintintbigintfloatdoublebooleanstringtimestampbinary。
3. 复杂类型包含arraymap和struct。
2. 复杂类型
1. array类型数组类型对应了Java中的数组或者集合(List和Set)类型。
#原始数据
1 bob,alex lucy,lily,jack
2 tom,sam,smith rose,john
3 peter,bruce david,kathy
4 helen,eden,iran cindy,grace,mike
#建表语句
create table battles (id int, groupa arraystring, groupb arraystring) row format delimited fields terminated by collection items terminated by ,;
#加载数据
load data local inpath /home/hivedemo/battles.txt into table battles;
#非空查询
select groupa[2] from battles where groupa[2] is not null;
2. map类型映射类型对应了Java中的映射(Map)类型。
#原始数据
1 tom,87
2 job,69
3 alex,72
4 david,85
5 zoo,90
#建表语句
create table scores(id int, score mapstring, int) row format delimited fields terminated by map keys terminated by ,;
#加载数据
load data local inpath /home/hivedemo/scores.txt into table scores;
#非空查询
select score[david] from scores where score[david] is not null;
3. struct类型结构体类型对应了Java中的对象在使用的时候会将数据封装成一个类似于json串的结构。
#原始数据
1 tom,19,male sam,20,male
2 lily,18,female lucy,18,female
3 charles,19,male mark,21,male
4 joan,18,female james,20,male
5 linda,19,female matin,19,male
#建表语句
create table infos(groupid int, membera structname:string, age:int, gender:string, memberb structname:string, age:int, gender:string) row format delimited fields terminated by collection items terminated by ,;
#加载数据
load data local inpath /home/hivedemo/infos.txt into table infos;
#获取指定属性的值
select membera.name from infos;
四、函数
1. 概述
1. 在Hive中提供了非常丰富的运算符和函数以方便用户对数据进行处理。
2. 如果Hive中原生提供的函数不能适应当前场景还可以考虑自定函数。
3. 在Hive中所有的函数都不能直接使用必须结合select形成语句才能使用。
4. 查看Hive中的所有函数
show functions;
5. 描述函数的信息
desc function count;
2. 案例
1. 拼接多个字符串并且在拼接的时候指定字符串之间的间隔符号。
#原始数据
mail qq com
music qq com
news baidu com
mail 163 com
#建表语句
create table webs(app string, company string, kind string) row format delimited fields terminated by ;
#加载数据
load data local inpath /home/hivedemo/webs.txt into table webs;
#字符串拼接
select concat(app, ., company, ., kind) from webs;
select concat_ws(., app, company, kind) from webs;
select concat_ws(., *) from webs;
2. 给定一个字符串表示日期从日期中将年份提取出来。
#方式一切分
select cast(split(2020-12-25, -)[0] as int);
#方式二提取
select year(2020-12-25);
3. year函数在使用的时候要求年月日之间必须用-来拼接如果用其他符号拼接那么就无法提取。
#方式一切分
select cast(split(2020/12/25, /)[0] as int);
#方式二替换之后再进行提取
select year(regexp_replace(2020/12/25, /, -));
4. 提取邮箱后缀
#方式一切分
select split(tomtedu.cn, )[1];
#方式二正则提取
select regexp_extract(tomtedu.cn, (.)(.), 2);
3. nvl函数
1. nvl(s1, s2)如果s1的值不为null那么返回s1的值如果s1的值为null那么返回s2的值如果s1和s2的值都为null那么返回null。
2. 案例
#原始数据
1 Bill 1000
2 vincent 800
3 William 500
4 Henry
5 Betty 500
6 Fred 300
7 Karl
8 Lee 400
9 Thomas 600
10 Shirley 900
#建表语句
create table rewards(id int, name string, reward double) row format delimited fields terminated by ;
#加载数据
load data local inpath /home/hivedemo/rewards.txt into table rewards;
#计算平均每一个员工收到了多少的奖金 - 聚合函数在计算的时候如果碰到null会自动忽略
select avg(nvl(reward, 0)) from rewards;
4. case when函数
1. case when函数类似于Java中的switch-case结构。
2. 案例
#原始数据
1 财务 bill 男
2 技术 charles 男
3 技术 lucy 女
4 技术 lily 女
5 财务 helen 女
6 财务 jack 男
7 财务 john 男
8 技术 alex 男
9 技术 cindy 女
10 技术 david 男
#建表语句
create table employers(id int, department string, name string, gender string) row format delimited fields terminated by ;
#加载数据
load data local inpath /home/hivedemo/employers.txt into table employers;
#统计每一个部门的总人数
select department, count(*) from employers group by department;
#统计每一个部门男女生的总人数
select department, sum(case gender when 男 then 1 else 0 end) as sum_male, sum(case gender when 女 then 1 else 0 end) as sum_female from employers group by department;
5. explode
1. explode(array/map)函数在使用的时候会传入的数组中的每一个元素给单独提取出来形成单独的一行或者会将映射中的键值对给拆分开形成多行数据。
2. 案例单词统计
#原始数据
hello tom hello bob david joy hello
hello rose joy hello rose
jerry hello tom hello joy
hello rose joy tom hello david
#建表语句
create external table words(warr arraystring) row format delimited collection items terminated by location /words;
select w, count(w) from (select explode(warr) w from words)ws group by w;
6. 列转行
1. 所谓的列转行是将一列的数据进行拆分拆分成多行的数据。
2. 案例一
#原始数据
悬崖之上 剧情/动作/悬疑
秘密访客 悬疑/惊悚
扫黑·决战 剧情/动作/犯罪
真·三国无双 动作/奇幻/古装
#建表语句
create table movies(name string, kinds arraystring) row format delimited fields terminated by collection items terminated by/;
#加载数据
load data local inpath /home/hivedemo/movies.txt into table movies;
#列转行 - 炸裂
select name, kind from movies lateral view explode(kinds) k as kind;
#查询所有的悬疑片
select * from (select name, kind from movies lateral view explode(kinds) k as kind)tmp where kind 悬疑;
3. 案例二
#原始数据
Kevin 活泼/开朗 打篮球/看电影
Lisa 大方/活泼 看电影/听音乐
Carl 活泼/幽默 听音乐/打篮球
Joy 大方/诙谐 打游戏/看电影
#建表语句
create table friends (name string, characters arraystring, hobbies arraystring) row format delimited fields terminated by collection items terminated by /;
#加载数据
load data local inpath /home/hivedemo/friends.txt into table friends;
#炸裂
select name, c, h from friends lateral view explode(characters) c_tmp as c lateral view explode(hobbies) h_tmp as h;
#获取性格活泼喜欢听音乐的人
select * from (select name, c, h from friends lateral view explode(characters) c_tmp as c lateral view explode(hobbies) h_tmp as h) tmp where c 活泼 and h 听音乐;
7. 行转列
1. 行转列指的是将多行的数据拼接之后放到一列上。
2. 案例一
#原始数据
夺命手术 剧情
夺命手术 悬疑
夺命手术 惊悚
夺命手术 犯罪
白夜行 爱情
白夜行 悬疑
如月疑云 喜剧
如月疑云 悬疑
无罪之最 剧情
无罪之最 悬疑
无罪之最 惊悚
无罪之最 犯罪
#建表语句
create table movies (name string, kind string) row format delimited fields terminated by ;
#加载数据
load data local inpath /home/hivedemo/movies.txt into table movies;
#行转列
select name, concat_ws(/, collect_set(kind)) from movies group by name; 3. 案例二
#原始数据
1 1 burt
1 2 james
1 3 fred
1 4 bruce
1 1 carol
1 2 taylor
1 3 evan
1 4 grace
1 1 richard
1 2 adam
1 3 ben
1 4 ross
1 1 charles
1 2 cody
1 3 wendy
1 4 david
#建表语句
create table students(grade int, class int, name string) row format delimited fields terminated by ;
#加载数据
load data local inpath /home/hivedemo/students.txt into table students;
#行转列
select grade, class, concat_ws(,, collect_list(name)) from students group by grade, class;
8. 分类
1. 在Hive中对常见的函数进行了分类分为了3类
a. UDF(User Defined Function)用户定义函数。特点是一进一出指的是输入一行数据会获取到一行结果例如yearlengthconcatconcat_wssplit等。
b. UDAF(User Defined Aggregation Function)用户定义聚合函数。特点是多进一出指的是输入多行数据获取到一行结果例如countsumavgmaxmincollect_setcollect_list等。
c. UDTF(User Defined Table-generation Function)用户定义表生成函数。特点是一进多出指的是输入一行数据获取到多行结果例如explode。UDTF在使用的时候必须紧贴select语句例如select explode(XXX) from xxx;并且注意UDTF在结合select使用的时候在表输中只能单独存在例如select name, explode(xxx) from xxx;或者select explode(xxx), name from xxx是不允许的。
2. 在Hive中相对使用比较多的函数大部分都是UDF函数。
9. 自定义UDF
1. 在Hive中如果Hive原生提供的函数不能够处理当前的数据那么Hive允许用户自定义函数。
2. 在Hive1.X和Hive2.X需要定义类继承UDF类但是在Hive3.X中UDF类已过时所以需要定义类继承GenericUDF类。
3. 定义完类之后需要将这个类打成jar包放到Linux上上传之后需要在Hive中来添加当前的jar包。
add jar /home/hivedemo/hive-1.0-SNAPSHOT.jar;
4. 创建临时函数。
create temporary function indexof as cn.tedu.hive.udf.AuthUDF;
10. 自定义UDTF
1. 在Hive中自定义UDTF过程和自定义UDF过程有些类似不同的地方在于需要定义一个类继承GenericUDTF。
2. 定义完类之后同样需要将这个类打成jar包放到Linux上之后同样需要在Hive中添加当前的jar包同样需要创建临时函数。
五、窗口函数
1. over
1. 窗口函数的作用是用于限制要处理的数据的量。
2. 语法
分析函数 over(partition by 列名 order by 列名 rows between 起始位置 and 结束位置)
a. 分析函数一般包括聚合函数、排序函数等例如
Ⅰ. count(col)统计指定列的总的个数。
Ⅱ. sum(col)针对指定列进行求和。
Ⅲ. avg(col)针对指定列进行求平均。
Ⅳ. max(col)获取指定列的最大值。
Ⅴ. min(col)获取指定列的最小值。
Ⅵ. lag(col, n)获取当前行的第前n行数据。例如lag(col, 1)表示获取上一行数据。再例如如果当前是第5行数据那么lag(col, 3)表示获取第2行数据。
Ⅶ. lead(col, n)获取当前的第后n行数据。例如lead(col, 1)表示获取下一行数据。再例如如果当前是第5行数据那么lead(col, 3)表示获取第8行数据。
Ⅷ. ntile(n)要求数据必须排序数据排序之后会平均的放到n个桶中。Hive自动的给每一个桶进行编号编号是从1开始。每一个桶中的数据都会携带当前桶的编号。如果数据不能平均放到每一个桶中那么会优先将排在前面的数据放到小的桶中去并且每一个桶中的数据行数之差不能超过1行。
Ⅸ. row_number()在数据排序之后对数据进行自增的编号且编号不重复。
Ⅹ. rank()在数据排序之后会对数据进行自增的编号如果值相同那么编号会重复且产生空位。
ⅩⅠ. dense_rank()在数据排序之后会对数据进行自增的编号如果值相同那么编号会重复且不产生空位。
b. partition by对数据进行分区实际上就是对数据进行分类
c. order by对数据进行排序desc表示降序asc表示升序
d. rows between表示指定数据的处理范围
3. 指定范围
a. preceding往前
b. following往后
c. current row当前行
d. unbounded无边界
Ⅰ. unbounded preceding从第一行开始
Ⅱ. unbounded following到最后一行结束
2. 案例
1. 案例一
#原始数据
jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94
#建表语句
create table orders (name string, orderdate string, cost double) row format delimited fields terminated by ,;
#加载数据
load data local inpath /home/hivedemo/orders.txt into table orders;
#需求一查询2017年4月份消费的顾客名单以及2017年4月份产生的总的消费人次
select distinct name, count(*) over() from orders where year(orderdate) 2017 and month(orderdate) 4;
#需求二获取每一个顾客的消费明细以及每一位顾客的月度消费总额
select *, sum(cost) over(partition by name, month(orderdate)) from orders;
#需求三获取每一个顾客的消费明细以及到当前日期为止的累计消费
select *, sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) from orders;
#需求四查询顾客的消费明细以及每一次消费之后上一次的消费时间
select *, lag(orderdate, 1) over(partition by name order by orderdate) from orders;
#需求五获取最早的20%的顾客的消费名单 - 首先需要先将数据按照消费日期来进行排序其次需要将按照排序之后的结果将数据放到5个桶中获取第一个桶的数据
select * from (select *, ntile(5) over(order by orderdate) as bucket_id from orders)tmp where bucket_id 1;
2. 案例二
#原始数据
Charles Chinese 87
Charles Math 95
Charles English 68
Lily Chinese 94
Lily Math 56
Lily English 84
William Chinese 64
William Math 86
William English 84
Vincent Chinese 65
Vincent Math 85
Vincent English 78
#建表语句
create table scores (name string, subject string, score int) row format delimited fields terminated by \t;
#加载数据
load data local inpath /home/hivedemo/scores.txt into table scores;
#需求一按学科对每一个学生的成绩进行降序排序
#顺次排序
select *, row_number() over(partition by subject order by score desc) from scores;
#并列排序
select *, rank() over(partition by subject order by score desc) from scores;
#非空位并列排序
select *, dense_rank() over(partition by subject order by score desc) from scores;
#需求二按学科获取每一个学科考试成绩前三名的学生
select * from (select *, rank() over(partition by subject order by score desc) as sortid from scores)tmp where sortid 3;
