Hive
1. Hive架构
1）用户接口：Client
2）元数据：Metastore （hive和HDFS之间的映射关系），包括：表名、表所属的数据库、字段等
· 元数据将hive中的表和HDFS中的路径做一个映射。
· 默认存储在自带的 derby 数据库（derby默认只能单用户）中，推荐使用 MySQL 存储 Metastore；
3）Hadoop
使用 HDFS 进行存储，使用 MapReduce 进行计算。
4）驱动器：Driver ★
（1）解析器：将 SQL 转换成MR的任务需要的对应的元数据等准备工作；
（2）编译器：进一步生成逻辑执行计划（MR任务的执行流程）。
（3）优化器：对逻辑执行计划进行优化。如果直接根据SQL生成执行计划的化，效率可能不高。
（4）执行器：真正触发MR任务的执行。把逻辑执行计划转换成可以运行的物理计划。对于 Hive 来 说，就是 MR/Spark。
2. Hive运行机制
用户输入SQL语句给hive：
- hive根据MySQL中的映射信息（元数据）找到对应的HDFS的路径上的数据，
- hive经过Driver(解析器、编译器、优化器、执行器)，将这些指令翻译成 MR程序，提交到 Yarn上执行；
最后返回结果。
3. Hive与传统数据库的区别
Hive和数据库除了拥有类似的查询语言外，无其他相似
存储位置：Hive数据存储在HDFS上。数据库保存在块设备或本地文件系统
数据操作：Hive一般不改、删（数据量太大，只做增、查），而数据库通常需要经常修改；
执行引擎：Hive通过MapReduce来实现。数据库用自己的执行引擎
执行速度：Hive执行延迟高，但它数据规模远超过数据库处理能力时，Hive的并行计算能力就体现优势了。数据库执行延迟较低
数据规模：hive大规模的数据计算。数据库能支持的数据规模较小
4. 为什么元数据放在MySQL ？
因为默认derby中时只能打开一个hive客户端，而放在MySQL可以同时打开多个客户端。
5. MANAGED内部表和EXTERNAL外部表的区别
管理：外部表数据由HDFS管理；内部表数据由hive自身管理
存储：外部表数据存储位置由自己指定（没有指定location则在默认地址下新建）；内部表数据存储在hive.metastore.warehouse.dir（默认在/uer/hive/warehouse）
创建：被external修饰的就是外部表；没被修饰【默认】是内部表
删除：删除内部表会删除元数据和存储数据；删除外部表仅仅删除元数据，HDFS路径下的文件依然还在，即外部表更安全；
场景：
- 如果一个表不止hive使用，被共享的情况下用外部表，外部表用的的较多；
- 临时表，中间表用内部表；
- 当前这份数据是否具有绝对的控制权或者是否需要对数据进行修改操作，如果有就建立内部表、如果没有就建立EXTERNAL外部表；
可以修改内部表为外部表：
alter table 表名 set tblproperties ('EXTERNAL'='TRUE'); // true即外部表，false为内部表
6. 导入数据的方式
-
向表中装载数据（load）
load data local inpath ‘test.txt’ into table test04;
如果是HDFS上就不加local -
通过查询语句向表中插入数据（Insert）
insert into /overwrite table student select * from emp； -
将sql写入脚本文件，在hive外部运行脚本（-e 命令行 -f 文件）
bin/hive -e “select …”
bin/hive -f bjpowernode.sql
7. 数据导出的方式
-
将查询的结果导出到本地
insert overwrite local directory '/opt/module/hive/data/export/student ’ select * from student; -
将查询的结果导出到HDFS
insert overwrite directory ‘/test5’ ----------- // HDFS上就不写local！
row format delimited
fields terminated by ‘\t’
select * from teacher; -
用HDFS命令导出到本地
dfs -get /user/hive/warehouse/student/student.txt
/opt/module/data/export/student3.txt;
8. 分区内排序
1. sort by 区内排序
有些情况下，我们需要控制某个特定行应该到哪个 reducer，通常是为了进行后续的聚集操作；
一般和distribute by 连用！ 先distribute再sort！
一般sort by 会指定分区规则 ! 如果有多个reduce而 没有分区规则就随机分区了！（为了防止数据倾斜）
2. 分区（Distribute By）
一般和sort by 连用！
distribute by 类似 MR 中 partition；
例：
set mapreduce.job.reduces=3; 设置reducer 数
select deptno,ename , sal from emp distribute by deptno sort by sal; -----// 即按照deptno分区，在区内按照sal区内排序；
正好10 20 30三种deptno，对应3个reuducer ！
9. 分区表
引入：
由于Hive没有索引，查询时全表扫描效率低；
通过分区可以直接查询目录而不用全表扫描，提高效率；
概念：
分区表实际上就是对应一个 HDFS 文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。
Hive 中的分区就是分目录文件， 每一个分区就是一个文件；
注意：分区字段不能是表中已经存在的字段 ！ 可以将分区字段看作表的伪列。
1. 静态分区
分区字段是写死的，每次插入数据要指定分区字段的值！
-
创建分区表：
create table dept01( deptno int, dname string, loc string) partitioned by (day string) ------// ① 建表时加入分区字段 -
添加数据到分区表：（需要指定分区字段以及字段值，所以是静态）
load data local inpath ‘dept1.txt’ into table dept01 partition(day =‘2020-10-25’) ; -
查询： 限定分区字段 ！提高效率 ！
select * from dept01 where day =‘2020-10-25’;
查询时，分区字段当作普通字段使用即可。 -
增加分区（使用现有的分区字段）
alter table dept01 add partition(day=‘2020-10-27’);
2. 动态分区
数据库自动会根据分区字段的值，将数据插入到相应的分区中 ！
注意：分区字段不能是表中已经存在的数据 ！
-
创建分区表： 与静态分区相同
-
开启动态分区+开启非严格模式：
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict; -
传入数据： 不需要指定字段和字段的值
insert into deptnum partition(deptno) select dname,loc, deptno from dept;
动态分区字段就在select语句中！select的最后一个字段是动态分区字段！
partition（deptno）可省略；
其他语法：
-
删除单个分区：alter table dept01 drop partition (day=‘2020-10-27’);
-
删除多个分区：alter table dept01 drop partition (day=‘2020-10-28’) , partition(day=‘2020-10-30’);
-
查看分区情况：show partitions dept01;
-
查看分区字段：desc formatted dept01;
10. 分桶表
10.1 什么是分桶表 ？
分区提供一个隔离数据和优化查询的便利方式；分桶是将数据集分解成更容易管理的若干部分的另一个技术。
主要用于分文件，建表的时候，指定按照哪些字段执行分桶操作，并可以设置需要分多少个桶，
【当插入数据的时候】，会执行MR分区的操作，会将数据分散在各个桶当中，
默认分区方案： key的hashCode和ReduceTask的数量取模；
10.2 分区和分桶的区别 ？
- 分区提供一个隔离数据和优化查询的便利方式；分桶是将数据集分解成更容易管理的若干部分的另一个技术。
- 分区针对的是数据的存储路径；分桶针对的是数据文件；
- 分桶按照哈希码取模分割，相对均匀；而分区是按照字段的值来分割，容易造成数据倾斜；
- 分桶和分区互不干扰，可以把分区表进一步分桶；
10.3 分桶表的作用 ？
1）数据采样
案例1：如数据质量抽样校验工作（一般会判断各个字段的结构信息是否完整）；
案例2：在进行数据分析时，一天需要编写很多SQL，但是每编写一条SQL，都需要对SQL进行校验，
如果面对完整的数据集合做校验，会导致校验时间过长，影响进度，所以可以采样出一部分；
2）提升查询的效率（多表效率提升更明显）
1.假设是分桶表，则会将SQL中的分桶表字段进行分桶操作，得到桶的编号，根据编号到对应桶中去查询数据！
这样就能避免扫描全表！从而提升效率；---------单表操作，用分区表也能实现；
2.利用分桶来进行多表join的优化！ ※
① 小表join大表
使用map join（Block Cache缓存技术，和环形缓冲区无关），在进行join时，将小表放在每个读取大表的MapTask的内存上，让mapTask每读取一次大表都和内存中小表的数据进行join，将join的结果输出到reduce端，从而实现在map端即reduce之前完成join的操作！
如何开启：两项配置 ①开启map -join ②设置小表的阀值 ，默认25mb
如果在reduce端join的问题：①reduce端压力较大，效率低 ②可能会出现数据倾斜；
②中表join大表；bucket map join
中型表：小表的3~10倍大，视为中型表；
解决方案：
① 提前过滤，过滤成小表就能用map-join了
② join的字段有大量的null值，可以尝试添加随机数（保证各个reduce接收的数据量差不多，减少数据倾斜）
③基于分桶表的 bucket map join ，即两个表都以桶为单位拆分，让每个桶的数据分别去进行两个表的map-join，即中型表的桶就是map-join的小表，大表的桶和小表的桶对应进行join，比普通mapjoin效率更高；
bucket map join的条件：
③大表join大表；SMB map join
整体思想和普通的bucket map join类似，将两个表按照桶进行拆分，不过此时两个表的桶的数量相同，所以一个桶对应一个桶，每个桶达到map join的条件，一个桶放在一个maptask的内存中；
① 提前过滤，减少join的数量，提升效率
② join的字段有大量的null值，可以尝试添加随机数（保证各reduce接收的数据量接近，从而减少数据倾斜）
③ SMB（sort merge Buckect Map join）
smb map join 条件：
①开启参数
②两个表的bucket桶数量要相等！ 而不是整数倍！
③bucket列=join列=sort列
分桶表的使用
1）创建分桶表：
create table buck(id int, name string)
clustered by(id)
into 4 buckets //分到4个桶里！
row format delimited
fields terminated by ‘\t’;
分桶规则：
对分桶字段的值进行哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中；
2）向分桶表添加数据
- 创建一张和分桶表一样的临时表tmp，唯一区别是这个表不是一个分桶表！
- 将数据加载到临时表tmp
- 通过
insert into 分桶表...select...… 导入分桶表
11. 函数
UDF ：（一进一出）
UDAF ： user defined aggregation function 聚合函数（多进一出）
UDTF ：炸裂函数（一进多出） 比如wordcount，将一行数据切分成多行
11.1 常见函数
nvl： MySQL中的ifnull（x,y）
concat (str1,str2,…) ： str既可以是基本数据类型，也可以是字段！ UDF函数，一进一出；
concat_ws(separator, str1, str2,…)： str为string，多个字段 或者 可以接收 string的数组！ 不能是int ！UDF函数
多行转一行的函数：concat_ws、collect_set、collect_list
一行转多行的函数：explode
产生数组（UDAF、UDF）：
-
collect_set(col)： （去重） UDAF 聚合函数！ 可以和group by连用！多进一出
例：
select collect_set(job) from emp;
例：
select collect_set(ename) from emp group by deptno；
-
collect_list(col)： （不去重） UDAF 聚合函数；
例：
select collect_set(job) from emp;
-
split(str, regex) UDF , str为字符串，regex为分隔符。 切割成数组（java中的split( ) 也是切为数组）。（数组是一行！）
.
接收数组（UDTF、UDF）
-
EXPLODE（col）， UDTF 炸裂函数， 一进多出；将 一列中的 Array 数组或者 Map 数组结构一行拆分成多行；
-
concat_ws(separator, str1, str2,…)： str为string，多个字段 或者 可以接收 string的数组！ 不能是int ！UDF函数；
11.2 窗口函数 ※ ※ ※
OVER()： 指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。partition决定开窗范围。
和group by 比较：
over是对每一行都开窗计算，每一行都有自己的组；
group by 的每个组中只能有一行输出，如count、avg、sum等；而窗口函数的每个partition可以有多行输出！
用法：
-
over() 的前面要使用以下函数（会增加一列），或者聚合函数；
lag (col, n, default_val)：往前第 n 行数据 //default_val可写可不写，若该行没有数据时，则为default_val
lead (col, n, default_val)：往后第 n 行数据 //default_val可以是字段
ntile (n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从 1 开始，对于每一行，NTILE 返回此行所属的组的编号。 ntile(n) over (…) 后面加别名，以方便使用分组编号的字段名
rank()： 相同时重复，总数不变 ，如1 1 3 （常用）
Dense_Rank()： 相同时重复，总数变少，如1 1 2
Row_number()：根据顺序记数，相同的不重复，如 1 2 3 -
over()内参数：分区、排序、指定窗口范围； 若over()即表示开窗范围为整个表；
例：
partition by name order by orderdate rows between unbounded preceding and current row（默认第一行到当前行） -
window specifiction 限定窗口行数：
CURRENT ROW：当前行
n PRECEDING： 往前 n 行数据
n FOLLOWING：往后 n 行数据
UNBOUNDED：起点(没有边界，走到走不动为止)
UNBOUNDED PRECEDING 表示从前面的起点，
UNBOUNDED FOLLOWING 表示到后面的终点
例1：查询在 2017 年 4 月份购买过的顾客名字及总人数；
如果用group by :
- select name,count(*) from business
where substring(orderdate,1,7) = ‘2017-04’ group by name;
count算的是同一个name出现的时间次数，即购买的次数，只输出两行；
- 窗口函数：
select name,count(*) over () from business where substring(orderdate,1 ,7) = ‘2017-04’ ; // 全局开窗，用count统计所有人
例2：查询顾客的购买明细及每个顾客的购买总额
- select name, orderdate, cost, sum(cost) over( partition by name) from business;
// 限定开窗范围为name即每个顾客
需求升级：查询顾客的购买明细及每个顾客每月的购买总额
- select name, orderdate, cost, sum(cost) over( partition by name , month(orderdate) ) from business; // 联合开窗！ 开窗范围是每人、每个月
新需求：每个用户按照月份进行累加 ？ ※ ※ ※
- select name, orderdate, cost, sum(cost) over ( partition by name order by month(orderdate) ) from business;
默认是 rows between unbounded preceding and current row ，即从第一行到当前行
例3：将每个顾客的 cost 按照日期进行累加？ ※ ※ ※
注意：
sum(cost) over (partition by name) -----按 name 分组，组内数据相加
sum(cost) over (partition by name order by orderdate) ----按 name 分组，范围是每个人，【组内】按照时间进行累加
- select name,orderdate,cost, sum(cost) over ( partition by name order by orderdate ) from business;
等价于：
sum(cost) over ( partition by name order by orderdate rows between unbounded preceding and current row ) from business;
改写：
-
sum(cost) over (partition by name order by orderdate rows between 1 PRECEDING and current row)
前面一行和当前行做聚合 -
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and 1 FOLLOWING )
每个开窗范围内，前一行到当前行的后面一行一起做聚合（共三行！） -
sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING )
【每个开窗范围内】，当前行及后面所有行做聚合 -
sum(cost) over(partition by name order by orderdate rows between unbounded preceding and 1 preceding )
【每个开窗范围内】，从起点到上一行， 但是第一行没有上一行。
-
sum(cost) over ( order by orderdate rows between unbounded preceding and 1 preceding )
不看人（没有partition by name），只按日期来累加。
没有partition by就是全局 开窗！
例4：查看顾客上次的购买时间
- select name, orderdate, cost,
lag( orderdate, 1) over (partition by name order by orderdate) from business; // 获取前面一行
lag(orderdate, 1)为往前一行，而第一行往前没有 ，所以是 null；
加上lag第三个参数，可以指定没有的时候使用默认值：
select name, orderdate, cost,
lag( orderdate, 1 ,‘11111’ ) over (partition by name order by orderdate) from business;
也可以将后面一行往前放：
select name, orderdate, cost,
lead( orderdate, 1 ,‘11111’ ) over (partition by name order by orderdate) from business;
例5：查询前 20%时间的订单信息
- select name,orderdate,cost, ntile(5) over(order by orderdate) as groups from business ; ----- t1
子查询求前20% ：
select name,orderdate, cost from t1 where groups =1 ;
分成五个组， 第一个组即20% ！
如果求中间的40%到60%，则让 where group =3 即可。
例6：查询用户在页面停留的时间 ※
1.获取用户本次和上次的时间差值
select name,orderdate, datediff(orderdate, lag(orderdate,1，0) over(partition by name order by orderdate) ) as diff from tablename;------------q1
2.将所有时间差值加起来即为总停留时间；
select sum( NVL(diff,0) ) from q1 group by name；
例7：求出每门学科前三名的学生？
假设有数据：
-
select name,subject,score, r from
( select * , rank() over(partition by subject order by score desc ) as r from score ) as q
where rr<=3;注：对rank 的结果有限制一定要用子查询 ！
扫一扫
474
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
