一、Hive概述
1.1 简介
- Hive是一个数据仓库软件
- Hive主要使用HQL（类sql）来分析已经存储在分布式设备(HDFS)上的数据
- Hive的本质是将用户编写的HQL，转换为MR/spark程序，对数据进行分析
- Hive分析的数据必须是结构化的数据，在分析之前，用户需要对数据创建表结构
- Hive的表结构(shema)存储在关系型数据库中，数据是存储在HDFS上，二者通过表进行映射
- Hive基于OLAP设计，基于OLAP设计的软件，一般重分析，延时高！
1.2 安装
①保证环境变量中有JAVA_HOME
②基于HADOOP工作，保证环境变量中有HADOOP_HOME
③在环境变量中配置HIVE_HOME，默认hive在启动时，会读取HIVE_HOME/conf中的配置文件
1.3 配置
①自定义hive的一些属性，需要在conf/hive-site.xml文件，配置自定义的属性
参数读取的顺序:hadoop中的8个配置文件------>hive-default.xml------>hive-site.xml----->hive --hiveconf x=y
②修改hive输出日志的位置：修改conf/hive-log4j-properties文件
1.4 元数据存储
①hive默认将元数据存储在derby中
②derby数据库适合在一些JAVA开发的程序中作为内嵌的数据库，但是derby不支持多个实例同时访问一个库！
将hive的元数据存储位置修改为存放在mysql中，需要注意：
①元数据存储的数据库的编码必须为latin1
②mysql的 binlog_format=mixded|row
1.5 hive的交互参数
-d: 定义一个变量名=变量值，在hivecli中可以使用${变量名}来读取变量值
-e: 执行一个引号引起来的sql语句，执行后返回结果，退出cli，用在脚本中！
-f: 执行一个sql文件，执行后返回结果，退出cli，用在脚本中！
-S: 静默模式，不输出和结果无关的多余信息
–hiveconf 属性名=属性值: 在hivecli启动之前定义一个属性名
1.6 在hive中如何查看变量
set: 查看当前cli所有的变量
set 变量名: 查看某个指定变量的值
set 变量名=变量值: 修改某个变量的值
1.7 连接hive的方式
①使用hivecli
②可以使用JDBC方式连接hive
a)启动JDBC的服务端----hiveserver2
b)使用JDBC方式的客户端来访问
beeline !connect jdbc:hive2://hiveserver2服务运行的主机名:10000
使用其他JDBC客户端
1.8 hive中常用的命令
在hive中使用hdfs: dfs 命令
在hive中使用shell命令： ! shell命令
1.9 数据类型
①在hive中最常用的数据类型就是String
②基本数据类型中，低精度的类型和高精度的类型进行计算时，hive会自动进行隐式转换，将低精度转为高精度！
(1)boolean类型无法进行隐式转换
(2)使用cast(值 as 类型)进行强制转换
③集合类型：
(1)Array: 数组，使用下标定位数组中的元素
(2)Map: key-value，使用key获取value的值
(3)Struct: 通过struct.属性名获取属性对应的值
1.10 数据的格式
①hive只能分析结构化的数据！在使用hive之前，需要对数据进行ETL，转为结构化的数据
②在建表时，需要指定正确的分隔符，不然hive在读取每行数据时，无法将字段和数据进行映射
1.11 hive的本质
①在hive中创建的表，库都在hdfs上有相应的路径！
②表中的数据，是以文件的形式在HDFS对应的目录中存放！
③在建表和建库后，会在Mysql中生成对应的shema信息！
1. TBLS: 存放表的元数据
2. DBS: 库的元数据
3. COLUMNS_V2: 列的元数据
4. PARTITIONS
二、库的增删改查操作
2.1 增
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment] -- 库的注释说明
[LOCATION hdfs_path] -- 库在hdfs上的路径
[WITH DBPROPERTIES (property_name=property_value, ...)]; -- 库的属性
create database if not exists mydb2
comment 'this is my db'
location 'hdfs://hadoop101:9000/mydb2'
with dbproperties('ownner'='jack','tel'='12345','department'='IT');
2.2 删
drop database 库名 -- 只能删除空库
drop database 库名 cascade -- 删除非空库
2.3 改
use 库名 -- 切换库
-- 同名的属性值会覆盖，之前没有的属性会新增
dbproperties: alter database mydb2 set dbproperties('ownner'='tom','empid'='10001');
2.4 查
show databases -- 查看当前所有的库
show tables in database -- 查看库中所有的表
desc database 库名 -- 查看库的描述信息
desc database extended 库名 -- 查看库的详细描述信息
三、表的增删改查操作
3.1 增
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(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 row_format] -- 表中数据每行的格式，定义数据字段的分隔符，集合元素的分隔符等
[STORED AS file_format] -- 表中的数据要以哪种文件格式来存储，默认为TEXTFILE（文本文件）可以设置为SequnceFile或 Paquret,ORC等
[LOCATION hdfs_path] -- 表在hdfs上的位置
- 建表时，不带EXTERNAL，创建的表是一个MANAGED_TABLE(管理表，内部表)
- 建表时，带EXTERNAL，创建的表是一个外部表
建表样例：
①原始数据格式
songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing
②期望转换的JSON格式
{
"name": "songsong",
"friends": ["bingbing" , "lili"] , //列表Array,
"children": { //键值Map,
"xiao song": 18 ,
"xiaoxiao song": 19
}
"address": { //结构Struct,
"street": "hui long guan" ,
"city": "beijing"
}
}
③建表语句
-- Map和Struct的区别：Struct中属性名是不变的，Map中key可以变化的！
-- 注意：在一个表中，array每个元素之间的分隔符，Map每个Entry之间的分隔符，struct每个属性之间的分隔符，都需要一致
create table people(name string,friends array<string>,
children map<string,int>,
address struct<street:string,city:string>)
row format delimited fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n';
外部表和内部表的区别是：
1. 内部表(管理表)在执行删除操作时，会将表的元数据(schema)和表位置的数据一起删除！
- 外部表在执行删除表操作时，只删除表的元数据(schema)
在企业中，创建的都是外部表，在hive中表是廉价的，数据是珍贵的
建表语句执行时：
1. hive会在hdfs生成表的路径；
- hive还会向MySQl的metastore库中掺入两条表的信息(元数据)
管理表和外部表之间的转换：
-- 将表改为外部表
alter table p1 set tblproperties('EXTERNAL'='TRUE');
-- 将表改为管理表
alter table p1 set tblproperties('EXTERNAL'='FALSE');
注意：在hive中语句中不区分大小写，但是在参数中严格区分大小写！
其他建表方式：
-- 只复制表结构，新表分区列会复制，但是分区信息不复制(show partition看不到)
create table person1 like person;
-- 执行查询语句查询的结果，按照顺序作为新表的普通列，不能创建分区表，复制后分区列为普通列
create table 表名 as select 语句
3.2 删
drop table 表名 -- 删除表
truncate table 表名 -- 清空管理表，只清空数据，报错 Cannot truncate non-managed table 表名.
3.3 改
-- 改表的属性
alter table 表名 set tblproperties(属性名=属性值)
-- 对列进行调整：
-- (1)改列名或列类型
alter table 表名 change [column] 旧列名 新列名 新列类型 [comment 新列的注释]
[FIRST|AFTER column_name] -- 调整列的顺序
-- (2)添加列和重置列
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
3.4 查
desc 表名： 查看表的描述
desc formatted 表名： 查看表的详细描述
四、分区表
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
- 分区表
在建表时，指定了PARTITIONED BY ，这个表称为分区表 - 分区概念
- MR: 在MapTask输出key-value时，为每个key-value计算一个区号，同一个分区的数据，会被同一个reduceTask处理，这个分区的数据，最终生成一个结果文件，通过分区，将MapTask输出的key-value经过reduce后，分散到多个不同的结果文件中
- Hive: 将表中的数据，分散到表目录下的多个子目录(分区目录)中
- 分区意义
分区的目的是为了让数据，分散到多个子目录中，在执行查询时，可以只选择查询某些子目录中的数据，加快查询效率！- 只有分区表才有子目录(分区目录)
- 分区目录的名称由两部分确定： 分区列列名=分区列列值
- 将输入导入到指定的分区之后，数据会附加上分区列的信息
- 分区的最终目的是在查询时，使用分区列进行过滤
- 如果表是一个分区表，数据必须存在在最后一级分区目录下
4.1 创建分区表
create external table if not exists default.deptpart1(
deptno int,
dname string,
loc int
)
PARTITIONED BY(area string)
row format delimited fields terminated by '\t';
多级分区表，有多个分区字段
create external table if not exists default.deptpart2(
deptno int,
dname string,
loc int
)
PARTITIONED BY(area string, province string)
row format delimited fields terminated by '\t';
指定分区表数据仓库存储的位置
create external table if not exists default.deptpart3(
deptno int,
dname string,
loc int
)
PARTITIONED BY(area string)
row format delimited fields terminated by '\t'
location 'hdfs://hadoop101:9000/deptpart3';
4.2 表分区的查询
show partitions 表名
4.3 创建分区
创建分区表的实质：
- 在hdfs上生成分区路径
- 在mysql中metastore.partitions表中生成分区的元数据
-- 方式1：
alter table 表名 add partition(分区字段名=分区字段值);
- 直接使用load命令向分区加载数据，如果分区不存在，load时自动帮我们生成分区
-- 方式2：
-- load data:表示加载数据
-- local:表示从本地加载数据到hive表；否则从HDFS加载数据到hive表
-- inpath:表示加载数据的路径
-- overwrite:表示覆盖表中已有数据，否则表示追加
-- into table:表示加载到哪张表
-- student:表示具体的表
-- partition:表示上传到指定分区
load data [local] inpath '/opt/module/datas/student.txt' [overwrite] into table student [partition (partcol1=val1,…)];
-- 样例
load data local inpath '/opt/module/datas/dept.txt' into table dept_partition2 partition(month='201709',day='10');
- 如果数据已经按照规范的格式(如果有分区，要提前建好分区目录)，上传到了HDFS，可以使用修复分区命令自动生成分区的元数据
-- 方式3：
msck repair table 表名;
注意事项：
①如果表是个分区表，在导入数据时，必须指定向哪个分区目录导入数据
②如果表是多级分区表，在导入数据时，数据必须位于最后一级分区的目录
五、分桶表操作
分桶在向一个表插入数据时，数据将分散到多个文件中，这个操作称为分桶操作。
分桶的目的将数据分散到多个文件中，分散后可以使用抽样查询，查询感兴趣的样本。
如何进行分桶操作呢？
-
首先需要创建一个分桶表，在创建表时，需要指定分桶的字段，可以选择指定是否在分桶时排序及排序的字段，必须指定要分N个桶。
-
默认分桶开关是关闭的，需要打开分桶开关
set hive.enforce.bucketing=true;
默认排序开关也是关闭的，需要打开排序开关
set hive.enforce.sorting=true; -
分桶必须经过MR，才可以完成，且只能使用Insert 的方式向表导入数据
5.1 建表
create table stu_buck(id int, name string)
clustered by(id)
SORTED BY (id desc)
into 4 buckets
row format delimited fields terminated by '\t';
准备一张临时表
create table stu_buck_tmp(id int, name string)
row format delimited fields terminated by '\t';
-- load data 到 stu_buck_tmp
5.2 导入数据
因为load实际上是hdfs dfs -put操作，无法满足分桶表导入数据。
而向分桶表导入数据时，必须运行MR/Spark程序，才能实现分桶操作。
借助 insert into 表名 select 语句完成
导入数据之前
- 需要打开强制分桶开关： set hive.enforce.bucketing=true;
- 需要打开强制排序开关： set hive.enforce.sorting=true;
insert into table stu_buck select * from stu_buck_tmp
5.3 抽样查询
格式：select * from 分桶表 tablesample(bucket x out of y on 分桶表分桶字段);
要求：
①抽样查询的表必须是分桶表！
②bucket x out of y on 分桶表分桶字段
假设当前表一共分了z(假设是4)个桶
x: 从当前表的第几桶开始抽样
0<x<=y
y: z/y 代表一共抽多少桶！
要求y必须是z的因子或倍数！
怎么抽： 从第x桶开始抽样，每间隔y桶抽一桶，知道抽满 z/y桶
bucket 1 out of 2 on id： 从第1桶(0号桶)开始抽，抽第x+y*(n-1)，一共抽2桶 ： 0号桶,2号桶
select * from stu_buck tablesample(bucket 1 out of 2 on id)
bucket 1 out of 1 on id： 从第1桶(0号桶)开始抽，抽第x+y*(n-1)，一共抽4桶 ： 0号桶,2号桶,1号桶,3号桶
bucket 2 out of 4 on id： 从第2桶(1号桶)开始抽，一共抽1桶 ： 1号桶
bucket 2 out of 8 on id： 从第2桶(1号桶)开始抽，一共抽0.5桶 ： 1号桶的一半
六、DML导入
6.1 load
作用将数据直接加载到表目录中
load data [local] inpath 'xx' into table 表名 partition()
local: 如果导入的文件在本地文件系统，需要加上local，使用put将本地上传到hdfs，不加local默认导入的文件是在hdfs，使用mv将源文件移动到目标目录
6.2 insert
insert方式运行MR程序，通过程序将数据输出到表目录
在某些场景，必须使用insert方式来导入数据：
①向分桶表插入数据
②如果指定表中的数据，不是以纯文本形式存储，需要使用insert方式导入
语法：
insert into|overwrite table 表名 select xxx | values(),(),()
insert into: 向表中追加新的数据
insert overwrite： 先清空表中所有的数据，再向表中添加新的数据
多插入模式(从一张源表查询，向多个目标表插入)
from 源表
insert xxxx 目标表 select xxx
insert xxxx 目标表 select xxx
insert xxxx 目标表 select xxx
举例：
from deptpart2
insert into table deptpart1 partition(area='huaxi') select deptno,dname,loc
insert into table deptpart1 partition(area='huaxinan') select deptno,dname,loc
6.3 location
- 在建表时，指定表的location为数据存放的目录
hive (default)> create table if not exists student5(
id int, name string
)
row format delimited fields terminated by '\t'
location '/user/hive/warehouse/student5';
- 上传数据到hdfs上
hive (default)> dfs -put /opt/module/datas/student.txt /user/hive/warehouse/student5;
- 查询数据
hive (default)> select * from student5;
6.4 查询语句中创建表并加载数据（As Select）
根据查询结果创建表（查询的结果会添加到新创建的表中）
create table if not exists student3
as select id, name from student;
6.5 import
不仅可以导入数据还可以顺便导入元数据(表结构)。import只能导入export输出的内容！
IMPORT [[EXTERNAL] TABLE 表名(新表或已经存在的表) [PARTITION (part_column="value"[, ...])]]
FROM 'source_path'
[LOCATION 'import_target_path']
- 如果向一个新表中导入数据，hive会根据要导入表的元数据自动创建表
- 如果向一个已经存在的表导入数据，在导入之前会先检查表的结构和属性是否一致，只有在表的结构和属性一致时，才会执行导入
- 不管表是否为空，要导入的分区必须是不存在的
- external table不移动数据，而管理表会copy数据到/user/hive/warehouse下，数据都可以查到
-- 示例
import external table importtable1 from '/export1'
七、DML导出
7.1 insert(常用)
将一条sql运算的结果，插入到指定的路径
语法：
insert overwrite [local] directory '/opt/module/datas/export/student' -- 没有local就是HDFS
row format delimited fields terminated by '\t' -- 将查询的结果，按照\t的格式导出
select * from student;
7.2 Hadoop命令导出到本地
hive (default)> dfs -get /user/hive/warehouse/student/month=201709/000000_0 /opt/module/hive/datas/export/student3.txt;
7.3 Hive Shell 命令导出
基本语法：（hive -f/-e 执行语句或者脚本 > file）
hive -e 'select * from default.student;' > /opt/module/datas/export/student4.txt;
7.4 export
- 既能导出数据，还可以导出元数据(表结构)
- export会在hdfs的导出目录中，生成数据和元数据
- 导出的元数据是和RDMS无关
- 如果是分区表，可以选择将分区表的部分分区进行导出
EXPORT TABLE tablename [PARTITION (part_column="value"[, ...])]
TO 'export_target_path' [ FOR replication('eventid') ]
7.5 Sqoop导出
八、查询
查询语法：
[WITH CommonTableExpression (, CommonTableExpression)*] (Note: Only available starting with Hive 0.13.0)
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[
CLUSTER BY col_list | [DISTRIBUTE BY col_list]
[SORT BY col_list]
]
[LIMIT number]
8.1 排序
Hive的本质是MR，MR中的排序分类
- 全排序：结果只有一个(只有一个分区)，所有的数据整体有序
- 部分排序：结果有多个(有多个分区)，每个分区内部有序
- 二次排序：在排序时，比较的条件有多个
排序和分区的区别是什么？
-
排序： 在reduce之前就已经排好序了，排序是shuffle阶段的主要工作
-
分区：使用Partitioner来进行分区，当reduceTaskNum>1，设置用户自己定
义的分区器，默认使用HashParitioner，HashParitioner只根据key的hashcode来分区
各类排序关键词的含义：
当reduceTaskNum>1(set mapreduce.job.reduces=3)，设置用户自己定义的分区器，如果没有使用HashParitioner
- ORDER BY col_list ： 全排序
- SORT BY col_list ： 部分排序，设置reduceTaskNum>1(set mapreduce.job.reduces=3)， 只写sort by是随
机分区。如果希望自定定义使用哪个字段分区，需要使用DISTRIBUTE BY - DISTRIBUTE BY col_list：指定按照哪个字段分区，结合sort by 使用，结合ORDER BY没有意义
- CLUSTER BY col_list ： 如果分区的字段和排序的字段一致，可以简写为CLUSTER BY 。例如：DISTRIBUTE BY sal sort by sal asc 等价于 CLUSTER BY sal，但是CLUSTER BY后不能写排序方式，只能按照asc排序
要求： CLUSTER BY 后不能写排序方式，只能按照asc排序！
8.2 函数介绍
-
查看函数
show functions; -
查看函数的使用
desc function year; -- desc function 函数名称; desc function extended year; -- 查看year函数的详细解释; -
函数的分类
按照函数的来源可以分为：
- 系统函数，自带函数可以直接使用
- 用户自定义函数
- 遵守hive函数类的要求，自定义一个函数类
- 打包函数，放入到hive的lib目录下，或在HIVE_HOME/auxlib，auxlib用来存放hive可以加载的第三方jar包的目录
- 创建一个函数，让这个函数和之前编写的类关联，函数有库的概念
- 使用函数
函数按照特征分：
- UDF： 用户定义的函数。特点是一进一出。输入单个参数，返回单个结果。例如cast(‘a’ as int) 返回 null
- UDTF: 用户定义的表生成函数。特点是一进多出。传入一个参数(集合类型)，返回一个结果集
- UDAF： 用户定义的聚集函数。特点是多进一出。 传入一列多行的数据，返回一个结果(一列一行) ，count,avg,sum
8.3 常用时间函数
-
常用日期函数
hive默认解析的日期格式是： yyyy-MM-dd HH:mm:ss 或 yyyy-MM-dd- unix_timestamp:返回当前或指定时间的时间戳
select unix_timestamp(); -- 1603508397 select unix_timestamp('2019-01-01 01:01:01'); -- 1546275661 select unix_timestamp('2019_01_01 01-01-01', 'yyyy_MM_dd HH-mm-ss'); -- 1546275661 -
from_unixtime：将时间戳转为日期格式
select from_unixtime(0); +----------------------+--+ | _c0 | +----------------------+--+ | 1970-01-01 08:00:00 | +----------------------+--+ -
current_date：当前日期
select current_date() ; +-------------+--+ | _c0 | +-------------+--+ | 2020-10-25 | +-------------+--+ -
current_timestamp：当前的日期加时间
select current_timestamp(); +--------------------------+--+ | _c0 | +--------------------------+--+ | 2020-10-25 09:48:13.412 | +--------------------------+--+ -
to_date：抽取日期部分
select to_date('2020-10-25 08:08:08'); +-------------+--+ | _c0 | +-------------+--+ | 2020-10-25 | +-------------+--+ -
year：获取年
-
month：获取月
-
day：获取日
-
hour：获取时
-
minute：获取分
-
second：获取秒
-
weekofyear：当前时间是一年中的第几周
-
dayofmonth：当前时间是一个月中的第几天
-
months_between： 两个日期间的月份，前-后
select months_between('2019-12-30 12:59:59', '2019-10-01'); +-------------+--+ | _c0 | +-------------+--+ | 2.95295662 | +-------------+--+ -
add_months：日期加减月
select add_months('2019-12-30 12:59:59', 1); +-------------+--+ | _c0 | +-------------+--+ | 2020-01-30 | +-------------+--+ -
datediff：两个日期相差的天数，前-后
select datediff('2019-02-28 12:59:59', '2019-03-05 12:59:59'); +------+--+ | _c0 | +------+--+ | -5 | +------+--+ -
date_add：日期加天数
-
date_sub：日期减天数
-
last_day：日期的当月的最后一天
8.4 常用取整函数
- round： 四舍五入
- ceil： 向上取整
- floor： 向下取整
8.5 常用字符串操作函数
-
upper： 转大写
-
lower： 转小写
-
length： 长度
-
trim： 前后去空格
-
lpad： 向左补齐，到指定长度
-
rpad： 向右补齐，到指定长度
-
regexp_replace：使用正则表达式匹配目标字符串，匹配成功后替换
SELECT regexp_replace('100-200', '(\d+)', 'num')='num-num -- 例如: SELECT regexp_replace('100-200', '1', 'a'); -- a00-200
8.6 集合操作
- size： 集合（仅限map和list）中元素的个数
- map_keys： 返回map中的key
- map_values: 返回map中的value
- array_contains: 判断array中是否包含某个元素
- sort_array： 将array中的元素排序
8.7 NVL函数
NVL( string1, replace_with)：判断string1是否为null，如果为null，使用replace_with替换null，否则不做操作。
在以下场景使用：
- 将NULL替换为默认值
- 运行avg()求平均值，如果有10行数据，8个为NULL，其他两个数据为10、20，计算平均值时会自动忽略NULL，计算的avg：(10+20)/2=15，结果错误。正确做法应该是将NULL替换为0，然后再avg计算。
SELECT avg(nvl(comm, 0)) from emp;
8.8 concat和concat_ws函数
concat: 字符串拼接。可以在参数中传入多个string类型的字符串，一旦有一个参数为null，返回Null。
SELECT CONCAT('a', 'b', 'c');
+------+--+
| _c0 |
+------+--+
| abc |
+------+--+
SELECT CONCAT('a', 'b', NULL);
+-------+--+
| _c0 |
+-------+--+
| NULL |
+-------+--+
concat_ws: 使用指定的分隔符完成字符串拼接，concat_ws(分隔符,[string | array]+)
select concat_ws('.', 'wwww', array('facebook', 'com'));
+--------------------+--+
| _c0 |
+--------------------+--+
| wwww.facebook.com |
+--------------------+--+
8.9 if判断子句
if(判断表达式,值1(表达式为true),值2(表达式为false))，类似于三元表达式。
原始数据
| name | dept_id | sex |
|---|---|---|
| 悟空 | A | 男 |
| 大海 | A | 男 |
| 宋宋 | B | 男 |
| 凤姐 | A | 女 |
| 婷姐 | B | 女 |
| 婷婷 | B | 女 |
求出不同部门男女各多少人
SELECT dept_id,
sum( if(sex='男', 1, 0) ) male_count,
sum( if(sex='女', 1, 0) ) male_count
from emp_sex
GROUP BY dept_id;
+----------+-------------+-------------+--+
| dept_id | male_count | male_count |
+----------+-------------+-------------+--+
| A | 2 | 1 |
| B | 1 | 2 |
+----------+-------------+-------------+--+
8.10 行转列
COLLECT_LIST(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行不去重汇总，产生array类型字段。
COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。
| name | constellation | blood_type |
|---|---|---|
| 孙悟空 | 白羊座 | A |
| 大海 | 射手座 | A |
| 宋宋 | 白羊座 | B |
| 猪八戒 | 白羊座 | A |
| 凤姐 | 射手座 | A |
把星座和血型一样的人归类到一起，期望结果：
射手座,A 大海|凤姐
白羊座,A 孙悟空|猪八戒
白羊座,B 宋宋
select collect_list(constellation), blood_type from person_info group by blood_type;
+----------------------------+-------------+--+
| _c0 | blood_type |
+----------------------------+-------------+--+
| ["白羊座","射手座","白羊座","射手座"] | A |
| ["白羊座"] | B |
+----------------------------+-------------+--+
select collect_set(constellation), blood_type from person_info group by blood_type;
+----------------+-------------+--+
| _c0 | blood_type |
+----------------+-------------+--+
| ["白羊座","射手座"] | A |
| ["白羊座"] | B |
+----------------+-------------+--+
SELECT concat_ws(',',constellation, blood_type), concat_ws('|', collect_set(name)) FROM person_info GROUP BY constellation, blood_type;
+--------+----------+--+
| _c0 | _c1 |
+--------+----------+--+
| 射手座,A | 大海|凤姐 |
| 白羊座,A | 孙悟空|猪八戒 |
| 白羊座,B | 宋宋 |
+--------+----------+--+
8.11 列转行
EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行
参数只能是array或map
- 将array类型参数转为1列N行
- 将map类型参数转为2列N行
explode函数属于UDTF，UDTF在使用时，不能和其他表达式一起出现在select子句后，只能单独出现在select子句后！
LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
| movie | category |
|---|---|
| 《疑犯追踪》 | 悬疑,动作,科幻,剧情 |
| 《Lie to me》 | 悬疑,警匪,动作,心理,剧情 |
| 《战狼2》 | 战争,动作,灾难 |
-------------不允许----------
select movie,explode(category)
from movie_info;
--------不符合要求--------------
select movie,col1
from movie_info
join
(select explode(category) col1
from movie_info) tmp;
处理：
- 先explode
- 需要将炸裂后的1列N行，在逻辑上依然视作1列1行，实际是1列N行，和movie进行笛卡尔集。这个操作在hive中称为侧写(Lateral view)
Lateral view explode() 临时表名 as 临时列名
**练习1：**将电影分类中的数组数据展开。结果如下：
select movie, category_name from movie_info lateral view explode(category) table_tmp as category_name;
《疑犯追踪》 悬疑
《疑犯追踪》 动作
《疑犯追踪》 科幻
《疑犯追踪》 剧情
《Lie to me》 悬疑
《Lie to me》 警匪
《Lie to me》 动作
《Lie to me》 心理
《Lie to me》 剧情
《战狼2》 战争
《战狼2》 动作
《战狼2》 灾难
**练习2：**将所有的数组拆分开
select * from person_info2;
+-------------------------+-----------------------+-------------------------+--+
| person_info2.names | person_info2.tags | person_info2.hobbys |
+-------------------------+-----------------------+-------------------------+--+
| ["jack","tom","jerry"] | ["阳光男孩","肌肉男孩","直男"] | ["晒太阳","健身","说多喝热水"] |
| ["marry","nancy"] | ["阳光女孩","肌肉女孩","直女"] | ["晒太阳","健身","看有内涵的段子"] |
+-------------------------+-----------------------+-------------------------+--+
select name_ex, tags_ex, hobbys_ex from person_info2
lateral view explode(names) tmp1 as name_ex
lateral view explode(tags) tmp1 as tags_ex
lateral view explode(hobbys) tmp1 as hobbys_ex;
8.12 窗口函数
oracle,sqlserver都提供了窗口函数，但是在mysql5.5和5.6都没有提供窗口函数
窗口函数：窗口+函数
窗口： 函数运行时计算的数据集的范围
函数： 运行的函数
仅仅支持以下函数：
Windowing functions：
LEAD:
LEAD (scalar_expression [,offset] [,default])： 返回当前行以下N行的指定列的列值！
如果找不到，就采用默认值
LAG:
LAG (scalar_expression [,offset] [,default])： 返回当前行以上N行的指定列的列值！
如果找不到，就采用默认值
FIRST_VALUE:
FIRST_VALUE(列名,[false(默认)])： 返回当前窗口指定列的第一个值，
第二个参数如果为true,代表加入第一个值为null，跳过空值，继续寻找！
LAST_VALUE:
LAST_VALUE(列名,[false(默认)])： 返回当前窗口指定列的最后一个值，
第二个参数如果为true,代表加入第一个值为null，跳过空值，继续寻找！
统计类的函数(一般都需要结合over使用)： min,max,avg,sum,count
排名分析：
RANK
ROW_NUMBER
DENSE_RANK
CUME_DIST
PERCENT_RANK
NTILE
注意：不是所有的函数在运行都是可以通过改变窗口的大小，来控制计算的数据集的范围
所有的排名函数和LAG,LEAD，支持使用over()，但是在over()中不能定义 window_clause
窗口函数和分组有什么区别？
①如果是分组操作，select后只能写分组后的字段
②如果是窗口函数，窗口函数是在指定的窗口内，对每条记录都执行一次函数
③如果是分组操作，有去重效果，而partition不去重！
格式：函数 over( partition by 字段 ,order by 字段 window_clause )
窗口的大小可以通过window_clause来指定：
(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
特殊情况：
①在over()中既没有出现windows_clause，也没有出现order by，窗口默认为rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING
②在over()中(没有出现windows_clause)，指定了order by，窗口默认为rows between UNBOUNDED PRECEDING and CURRENT ROW
+----------------+---------------------+----------------+--+
| business.name | business.orderdate | business.cost |
+----------------+---------------------+----------------+--+
| 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 |
+----------------+---------------------+----------------+--+
-
查询在2017年4月份购买过的顾客及总人数
-- 先筛选出2017年4月份购买过的顾客 SELECT * FROM business where year(orderdate) = '2017' AND month(orderdate) = '04'; +----------------+---------------------+----------------+--+ | business.name | business.orderdate | business.cost | +----------------+---------------------+----------------+--+ | jack | 2017-04-06 | 42 | | mart | 2017-04-08 | 62 | | mart | 2017-04-09 | 68 | | mart | 2017-04-11 | 75 | | mart | 2017-04-13 | 94 | +----------------+---------------------+----------------+--+ -- 查询在2017年4月份购买过的顾客及总人数，使用窗口函数，前无边界，后无边界 SELECT name, count(*) over(rows between unbounded preceding and unbounded following) from business where year(orderdate) = '2017' AND month(orderdate) = '04' GROUP BY name; -- 等价于 如果不出现ORDER BY和window clause SELECT name, COUNT(1) over() FROM business WHERE year(orderdate) = '2017' AND month(orderdate) = '04' GROUP BY name; +-------+-----------------+--+ | name | count_window_0 | +-------+-----------------+--+ | mart | 2 | | jack | 2 | +-------+-----------------+--+ -
查询顾客的购买明细及月购买总额
select name, substring(orderdate, 1, 7), sum(cost) over(partition by name, substring(orderdate, 1, 7) rows between unbounded preceding and unbounded following) from business; +-------+----------+---------------+--+ | name | _c1 | sum_window_0 | +-------+----------+---------------+--+ | jack | 2017-01 | 111 | | jack | 2017-01 | 111 | | jack | 2017-01 | 111 | | jack | 2017-02 | 23 | | jack | 2017-04 | 42 | | mart | 2017-04 | 299 | | mart | 2017-04 | 299 | | mart | 2017-04 | 299 | | mart | 2017-04 | 299 | | neil | 2017-05 | 12 | | neil | 2017-06 | 80 | | tony | 2017-01 | 94 | | tony | 2017-01 | 94 | | tony | 2017-01 | 94 | +-------+----------+---------------+--+ -- 等价于 select name, substring(orderdate, 1, 7), sum(cost) over(partition by name, substring(orderdate, 1, 7)) from business; -
上述的场景,要将cost按照日期进行累加
-- 在over()中(没有出现windows_clause)，指定了order by，窗口默认为rows between UNBOUNDED PRECEDING and CURRENT ROW SELECT name, orderdate,cost,sum(cost) over(partition by name order by orderdate) from business; +-------+-------------+-------+---------------+--+ | name | orderdate | cost | sum_window_0 | +-------+-------------+-------+---------------+--+ | jack | 2017-01-01 | 10 | 10 | | jack | 2017-01-05 | 46 | 56 | | jack | 2017-01-08 | 55 | 111 | | jack | 2017-02-03 | 23 | 134 | | jack | 2017-04-06 | 42 | 176 | | mart | 2017-04-08 | 62 | 62 | | mart | 2017-04-09 | 68 | 130 | | mart | 2017-04-11 | 75 | 205 | | mart | 2017-04-13 | 94 | 299 | | neil | 2017-05-10 | 12 | 12 | | neil | 2017-06-12 | 80 | 92 | | tony | 2017-01-02 | 15 | 15 | | tony | 2017-01-04 | 29 | 44 | | tony | 2017-01-07 | 50 | 94 | +-------+-------------+-------+---------------+--+ -
查询顾客的购买明细及上次的购买时间
-- LAG (scalar_expression [,offset] [,default])： 返回当前行以上N行的指定列的列值，如果找不到，就采用默认值 SELECT name, orderdate,cost,LAG(orderdate, 1, null) over(partition by name order by orderdate) from business; +-------+-------------+-------+---------------+--+ | name | orderdate | cost | lag_window_0 | +-------+-------------+-------+---------------+--+ | jack | 2017-01-01 | 10 | NULL | | jack | 2017-01-05 | 46 | 2017-01-01 | | jack | 2017-01-08 | 55 | 2017-01-05 | | jack | 2017-02-03 | 23 | 2017-01-08 | | jack | 2017-04-06 | 42 | 2017-02-03 | | mart | 2017-04-08 | 62 | NULL | | mart | 2017-04-09 | 68 | 2017-04-08 | | mart | 2017-04-11 | 75 | 2017-04-09 | | mart | 2017-04-13 | 94 | 2017-04-11 | | neil | 2017-05-10 | 12 | NULL | | neil | 2017-06-12 | 80 | 2017-05-10 | | tony | 2017-01-02 | 15 | NULL | | tony | 2017-01-04 | 29 | 2017-01-02 | | tony | 2017-01-07 | 50 | 2017-01-04 | +-------+-------------+-------+---------------+--+ -
查询顾客的购买明细及下次的购买时间
-- LEAD (scalar_expression [,offset] [,default])： 返回当前行以下N行的指定列的列值，如果找不到，就采用默认值 SELECT name, orderdate,cost,LEAD(orderdate, 1, null) over(partition by name order by orderdate) from business; +-------+-------------+-------+----------------+--+ | name | orderdate | cost | lead_window_0 | +-------+-------------+-------+----------------+--+ | jack | 2017-01-01 | 10 | 2017-01-05 | | jack | 2017-01-05 | 46 | 2017-01-08 | | jack | 2017-01-08 | 55 | 2017-02-03 | | jack | 2017-02-03 | 23 | 2017-04-06 | | jack | 2017-04-06 | 42 | NULL | | mart | 2017-04-08 | 62 | 2017-04-09 | | mart | 2017-04-09 | 68 | 2017-04-11 | | mart | 2017-04-11 | 75 | 2017-04-13 | | mart | 2017-04-13 | 94 | NULL | | neil | 2017-05-10 | 12 | 2017-06-12 | | neil | 2017-06-12 | 80 | NULL | | tony | 2017-01-02 | 15 | 2017-01-04 | | tony | 2017-01-04 | 29 | 2017-01-07 | | tony | 2017-01-07 | 50 | NULL | +-------+-------------+-------+----------------+--+ -
查询顾客的购买明细及顾客本月第一次购买的时间
-- FIRST_VALUE(列名,[false(默认)])：返回当前窗口指定列的第一个值，第二个参数如果为true,代表加入第一个值为null，跳过空值，继续寻找！ SELECT name, orderdate,cost,FIRST_VALUE(orderdate, false) over(partition by name, substring(orderdate, 1, 7) order by orderdate) from business; +-------+-------------+-------+-----------------------+--+ | name | orderdate | cost | first_value_window_0 | +-------+-------------+-------+-----------------------+--+ | jack | 2017-01-01 | 10 | 2017-01-01 | | jack | 2017-01-05 | 46 | 2017-01-01 | | jack | 2017-01-08 | 55 | 2017-01-01 | | jack | 2017-02-03 | 23 | 2017-02-03 | | jack | 2017-04-06 | 42 | 2017-04-06 | | mart | 2017-04-08 | 62 | 2017-04-08 | | mart | 2017-04-09 | 68 | 2017-04-08 | | mart | 2017-04-11 | 75 | 2017-04-08 | | mart | 2017-04-13 | 94 | 2017-04-08 | | neil | 2017-05-10 | 12 | 2017-05-10 | | neil | 2017-06-12 | 80 | 2017-06-12 | | tony | 2017-01-02 | 15 | 2017-01-02 | | tony | 2017-01-04 | 29 | 2017-01-02 | | tony | 2017-01-07 | 50 | 2017-01-02 | +-------+-------------+-------+-----------------------+--+ -
查询顾客的购买明细及顾客本月最后一次购买的时间
-- LAST_VALUE(列名,[false(默认)])： 返回当前窗口指定列的最后一个值，第二个参数如果为true,代表加入第一个值为null，跳过空值，继续寻找！ SELECT name, orderdate,cost,LAST_VALUE(orderdate, false) over(partition by name, substring(orderdate, 1, 7) order by orderdate rows between UNBOUNDED PRECEDING and UNBOUNDED following) from business; +-------+-------------+-------+----------------------+--+ | name | orderdate | cost | last_value_window_0 | +-------+-------------+-------+----------------------+--+ | jack | 2017-01-01 | 10 | 2017-01-08 | | jack | 2017-01-05 | 46 | 2017-01-08 | | jack | 2017-01-08 | 55 | 2017-01-08 | | jack | 2017-02-03 | 23 | 2017-02-03 | | jack | 2017-04-06 | 42 | 2017-04-06 | | mart | 2017-04-08 | 62 | 2017-04-13 | | mart | 2017-04-09 | 68 | 2017-04-13 | | mart | 2017-04-11 | 75 | 2017-04-13 | | mart | 2017-04-13 | 94 | 2017-04-13 | | neil | 2017-05-10 | 12 | 2017-05-10 | | neil | 2017-06-12 | 80 | 2017-06-12 | | tony | 2017-01-02 | 15 | 2017-01-07 | | tony | 2017-01-04 | 29 | 2017-01-07 | | tony | 2017-01-07 | 50 | 2017-01-07 | +-------+-------------+-------+----------------------+--+ -
查询顾客的购买明细和顾客最近3次的花费
-- 最近三次: 当前和之前两次 select name, orderdate, cost, sum(cost) over(partition by name order by orderdate rows between 2 preceding and current row) from business; +-------+-------------+-------+---------------+--+ | name | orderdate | cost | sum_window_0 | +-------+-------------+-------+---------------+--+ | jack | 2017-01-01 | 10 | 10 | | jack | 2017-01-05 | 46 | 56 | | jack | 2017-01-08 | 55 | 111 | | jack | 2017-02-03 | 23 | 124 | | jack | 2017-04-06 | 42 | 120 | | mart | 2017-04-08 | 62 | 62 | | mart | 2017-04-09 | 68 | 130 | | mart | 2017-04-11 | 75 | 205 | | mart | 2017-04-13 | 94 | 237 | | neil | 2017-05-10 | 12 | 12 | | neil | 2017-06-12 | 80 | 92 | | tony | 2017-01-02 | 15 | 15 | | tony | 2017-01-04 | 29 | 44 | | tony | 2017-01-07 | 50 | 94 | +-------+-------------+-------+---------------+--+ -- 最近三次: 当前+前一次+后一次 select name, orderdate, cost, sum(cost) over(partition by name order by orderdate rows between 1 preceding and 1 following) from business; +-------+-------------+-------+---------------+--+ | name | orderdate | cost | sum_window_0 | +-------+-------------+-------+---------------+--+ | jack | 2017-01-01 | 10 | 56 | | jack | 2017-01-05 | 46 | 111 | | jack | 2017-01-08 | 55 | 124 | | jack | 2017-02-03 | 23 | 120 | | jack | 2017-04-06 | 42 | 65 | | mart | 2017-04-08 | 62 | 130 | | mart | 2017-04-09 | 68 | 205 | | mart | 2017-04-11 | 75 | 237 | | mart | 2017-04-13 | 94 | 169 | | neil | 2017-05-10 | 12 | 92 | | neil | 2017-06-12 | 80 | 92 | | tony | 2017-01-02 | 15 | 44 | | tony | 2017-01-04 | 29 | 94 | | tony | 2017-01-07 | 50 | 79 | +-------+-------------+-------+---------------+--+ -- 或者使用lead+lag实现 select name, orderdate, cost, cost + lag(cost, 1, 0) over(partition by name order by orderdate) + lead(cost, 1, 0) over(partition by name order by orderdate) from business; +-------+-------------+-------+------+--+ | name | orderdate | cost | _c3 | +-------+-------------+-------+------+--+ | jack | 2017-01-01 | 10 | 56 | | jack | 2017-01-05 | 46 | 111 | | jack | 2017-01-08 | 55 | 124 | | jack | 2017-02-03 | 23 | 120 | | jack | 2017-04-06 | 42 | 65 | | mart | 2017-04-08 | 62 | 130 | | mart | 2017-04-09 | 68 | 205 | | mart | 2017-04-11 | 75 | 237 | | mart | 2017-04-13 | 94 | 169 | | neil | 2017-05-10 | 12 | 92 | | neil | 2017-06-12 | 80 | 92 | | tony | 2017-01-02 | 15 | 44 | | tony | 2017-01-04 | 29 | 94 | | tony | 2017-01-07 | 50 | 79 | +-------+-------------+-------+------+--+ -
查询前20%时间的订单信息
SELECT * FROM ( select name, orderdate, cost, cume_dist() over(order by orderdate) cdnum from business) tmp where tmp.cdnum <= 0.2; +-----------+----------------+-----------+----------------------+--+ | tmp.name | tmp.orderdate | tmp.cost | tmp.cdnum | +-----------+----------------+-----------+----------------------+--+ | jack | 2017-01-01 | 10 | 0.07142857142857142 | | tony | 2017-01-02 | 15 | 0.14285714285714285 | +-----------+----------------+-----------+----------------------+--+ -- 如果是非精确排名，可以使用NTILE(5)
8.13 排名函数
注意：排名函数跟over()，但是不能定义window_clause，在计算名次前，需要先排序
RANK: 允许并列，一旦有并列跳号
ROW_NUMBER: 行号, 连续的，每个号之间差1
DENSE_RANK： 允许并列，一旦有并列不跳号
CUME_DIST： 从排序后的第一行到当前值之间数据 占整个数据集的百分比！
PERCENT_RANK： rank-1/ 总数据量-1
NTILE(x): 将数据集均分到X个组中，返回每条记录所在的组号
select *, rank() over(partition by subject order by score desc) from score;
+-------------+----------------+--------------+----------------+--+
| score.name | score.subject | score.score | rank_window_0 |
+-------------+----------------+--------------+----------------+--+
| 孙悟空 | 数学 | 95 | 1 |
| 宋宋 | 数学 | 86 | 2 |
| 婷婷 | 数学 | 85 | 3 |
| 大海 | 数学 | 56 | 4 |
| 宋宋 | 英语 | 84 | 1 |
| 大海 | 英语 | 84 | 1 |
| 婷婷 | 英语 | 78 | 3 |
| 孙悟空 | 英语 | 68 | 4 |
| 大海 | 语文 | 94 | 1 |
| 孙悟空 | 语文 | 87 | 2 |
| 婷婷 | 语文 | 65 | 3 |
| 宋宋 | 语文 | 64 | 4 |
+-------------+----------------+--------------+----------------+--+
- 给每个学生的总分进行排名
select name, sumscore, rank() over(order by sumscore desc)
from
(select name, sum(score) sumscore
from score
group by name) tmp;
+-------+-----------+----------------+--+
| name | sumscore | rank_window_0 |
+-------+-----------+----------------+--+
| 孙悟空 | 250 | 1 |
| 宋宋 | 234 | 2 |
| 大海 | 234 | 2 |
| 婷婷 | 228 | 4 |
+-------+-----------+----------------+--+
- 求每个学生的成绩明细及给每个学生的总分和总分排名
select *, DENSE_RANK() over(order by tmp.sumscore DESC) FROM
(select name, subject, score, sum(score) over(partition by name) sumscore
from score) tmp;
+-----------+--------------+------------+---------------+----------------------+--+
| tmp.name | tmp.subject | tmp.score | tmp.sumscore | dense_rank_window_0 |
+-----------+--------------+------------+---------------+----------------------+--+
| 孙悟空 | 语文 | 87 | 250 | 1 |
| 孙悟空 | 数学 | 95 | 250 | 1 |
| 孙悟空 | 英语 | 68 | 250 | 1 |
| 宋宋 | 语文 | 64 | 234 | 2 |
| 宋宋 | 数学 | 86 | 234 | 2 |
| 宋宋 | 英语 | 84 | 234 | 2 |
| 大海 | 语文 | 94 | 234 | 2 |
| 大海 | 数学 | 56 | 234 | 2 |
| 大海 | 英语 | 84 | 234 | 2 |
| 婷婷 | 语文 | 65 | 228 | 3 |
| 婷婷 | 数学 | 85 | 228 | 3 |
| 婷婷 | 英语 | 78 | 228 | 3 |
+-----------+--------------+------------+---------------+----------------------+--+
-
只查询每个科目的成绩的前2名
select * from (SELECT *, rank() over(partition by subject order by score desc) rn from score) tmp where tmp.rn <= 2; +-----------+--------------+------------+---------+--+ | tmp.name | tmp.subject | tmp.score | tmp.rn | +-----------+--------------+------------+---------+--+ | 孙悟空 | 数学 | 95 | 1 | | 宋宋 | 数学 | 86 | 2 | | 宋宋 | 英语 | 84 | 1 | | 大海 | 英语 | 84 | 1 | | 大海 | 语文 | 94 | 1 | | 孙悟空 | 语文 | 87 | 2 | +-----------+--------------+------------+---------+--+ -
查询学生成绩明细，并显示科目的最高分
SELECT *, MAX(score) over(partition by subject order by score desc)
FROM score;
+-------------+----------------+--------------+---------------+--+
| score.name | score.subject | score.score | max_window_0 |
+-------------+----------------+--------------+---------------+--+
| 孙悟空 | 数学 | 95 | 95 |
| 宋宋 | 数学 | 86 | 95 |
| 婷婷 | 数学 | 85 | 95 |
| 大海 | 数学 | 56 | 95 |
| 宋宋 | 英语 | 84 | 84 |
| 大海 | 英语 | 84 | 84 |
| 婷婷 | 英语 | 78 | 84 |
| 孙悟空 | 英语 | 68 | 84 |
| 大海 | 语文 | 94 | 94 |
| 孙悟空 | 语文 | 87 | 94 |
| 婷婷 | 语文 | 65 | 94 |
| 宋宋 | 语文 | 64 | 94 |
+-------------+----------------+--------------+---------------+--+
-- 或者使用FIRST_VALUE
SELECT *, FIRST_VALUE(score) over(partition by subject order by score desc)
FROM score;
蚂蚁金服种树题
plant_carbon.plant_id | plant_carbon.plant_name | plant_carbon.low_carbon
user_low_carbon.user_id | user_low_carbon.data_dt | user_low_carbon.low_carbon
1.蚂蚁森林植物申领统计
问题1：假设2017年1月1日开始记录低碳数据（user_low_carbon），
假设2017年10月1日之前满足申领条件的用户都申领了一颗p004-胡杨，
剩余的能量全部用来领取“p002-沙柳”。
统计在10月1日累计申领“p002-沙柳”排名前10的用户信息；以及他比后一名多领了几颗沙柳。
①统计在2017-01-01到2017-10-01日用户获取的总积分
SELECT user_id, sum(low_carbon) AS sumCarbon FROM user_low_carbon WHERE regexp_replace(data_dt, '/', '-') between '2017-1-1' and '2017-10-1' GROUP BY user_id
-- t1
②统计胡杨和沙柳单价
-- 胡杨单价：
SELECT low_carbon huyangCarbon from plant_carbon where plant_id = 'p004'; -- t2
-- 沙柳单价：
SELECT low_carbon shaliuCarbon from plant_carbon where plant_id = 'p002'; -- t3
③计算每个用户领取了多少个沙柳
SELECT user_id, floor((sumCarbon - huyangCarbon) / shaliuCarbon) AS shaliuCount
FROM t1 join t2 join t3
order by shaliuCount desc
LIMIT 11; -- t4
④统计前10名用户比后一名多领了几颗沙柳
SELECT user_id, shaliuCount, rank() over(order by shaliuCount desc),
(shaliuCount - LEAD(shaliuCount, 1, 0)) over(order by shaliuCount desc)
FROM t4
⑤组合SQL
SELECT user_id, shaliuCount, rank() over(order by shaliuCount desc),
shaliuCount - LEAD(shaliuCount, 1, 0) over(order by shaliuCount desc)
FROM
(SELECT user_id, floor((sumCarbon - huyangCarbon) / shaliuCarbon) AS shaliuCount
FROM
(SELECT user_id, sum(low_carbon) AS sumCarbon FROM user_low_carbon WHERE regexp_replace(data_dt, '/', '-') between '2017-1-1' and '2017-10-1' GROUP BY user_id) t1 join (SELECT low_carbon huyangCarbon from plant_carbon where plant_id = 'p004') t2
join (SELECT low_carbon shaliuCarbon from plant_carbon where plant_id = 'p002') t3
order by shaliuCount desc
LIMIT 11
) t4;
+----------+--------------+----------------+------+--+
| user_id | shaliucount | rank_window_0 | _c3 |
+----------+--------------+----------------+------+--+
| u_007 | 66 | 1 | 3 |
| u_013 | 63 | 2 | 10 |
| u_008 | 53 | 3 | 7 |
| u_005 | 46 | 4 | 1 |
| u_010 | 45 | 5 | 1 |
| u_014 | 44 | 6 | 5 |
| u_011 | 39 | 7 | 2 |
| u_009 | 37 | 8 | 5 |
| u_006 | 32 | 9 | 9 |
| u_002 | 23 | 10 | 1 |
| u_004 | 22 | 11 | 22 |
+----------+--------------+----------------+------+--+
问题2：查询user_low_carbon表中每日流水记录，条件为：
用户在2017年，连续三天（或以上）的天数里，
每天减少碳排放（low_carbon）都超过100g的用户低碳流水。
需要查询返回满足以上条件的user_low_carbon表中的记录流水。
plant_carbon.plant_id | plant_carbon.plant_name | plant_carbon.low_carbon
user_low_carbon.user_id | user_low_carbon.data_dt | user_low_carbon.low_carbon
解法1：
①过滤2017年的数据，统计每个用户每天共收集了多少碳
select user_id, regexp_replace(data_dt, '/', '-') dt, sum(low_carbon) carbonPerDay
from user_low_carbon
where year(regexp_replace(data_dt, '/', '-')) = 2017
GROUP BY user_id, data_dt
having carbonPerDay >= 100 -- t1
②过滤符合连续3天的条件
如何判断当前记录复合连续三天的条件？
a)如果当前日期位于连续三天中的第一天，使用当前日期减去 当前日期后一天的日期，差值一定为-1
使用当前日期减去 当前日期后二天的日期，差值一定为-2
b)如果当前日期位于连续三天中的第二天，使用当前日期减去 当前日期前一天的日期，差值一定为1
使用当前日期减去 当前日期后一天的日期，差值一定为-1
c)如果当前日期位于连续三天中的第三天，使用当前日期减去 当前日期前一天的日期，差值一定为1
使用当前日期减去 当前日期前二天的日期，差值一定为2
满足a,b,c其中之一，当前日期就符合要求
求当前日期和当前之前，前1，2天和后1，2天日期的差值
select user_id,dt,carbonPerDay,
datediff(dt,lag(dt,1,'1970-1-1') over(partition by user_id order by dt)) pre1diff,
datediff(dt,lag(dt,2,'1970-1-1') over(partition by user_id order by dt)) pre2diff,
datediff(dt,lead(dt,1,'1970-1-1') over(partition by user_id order by dt)) after1diff,
datediff(dt,lead(dt,2,'1970-1-1') over(partition by user_id order by dt)) after2diff
from t1 -- t2
③过滤数据
select user_id,regexp_replace(dt,'-','/') newdt,carbonPerDay
from t2
where (after1diff=-1 and after2diff=-2) or (pre1diff=1 and after1diff=-1) or (pre1diff=1 and pre2diff=2) -- t3
④关联原表，求出每日的流水
select u.*
from t3 join user_low_carbon u
on t3.user_id=u.user_id and t3.newdt=u.data_dt
⑤最终的SQL
select u.*
from
(select user_id,regexp_replace(dt,'-','/') newdt,carbonPerDay
from
(select user_id,dt,carbonPerDay,
datediff(dt,lag(dt,1,'1970-1-1') over(partition by user_id order by dt)) pre1diff,
datediff(dt,lag(dt,2,'1970-1-1') over(partition by user_id order by dt)) pre2diff,
datediff(dt,lead(dt,1,'1970-1-1') over(partition by user_id order by dt)) after1diff,
datediff(dt,lead(dt,2,'1970-1-1') over(partition by user_id order by dt)) after2diff
from (select user_id,regexp_replace(data_dt,'/','-') dt,sum(low_carbon) carbonPerDay
from user_low_carbon
where year(regexp_replace(data_dt,'/','-'))=2017
group by user_id,data_dt
having carbonPerDay >= 100)t1 )t2
where (after1diff=-1 and after2diff=-2) or (pre1diff=1 and after1diff=-1) or (pre1diff=1 and pre2diff=2))t3 join user_low_carbon u
on t3.user_id=u.user_id and t3.newdt=u.data_dt
解法2：
如何判断当前数据是连续的？
当前有A,B两列，A列的起始值从a开始，B列的起始值从b开始
- 假设A列每次递增X，B列每次递增Y
- 如果A列和B列都是连续递增，A列和B列之间的差值，总是相差(x-y)
- 如果X=Y，A列和B列之间的差值，总是相差0
A B
1. a b a-b
2. a+X b+Y (a-b)+(x-y)
3. a+2X b+2Y (a-b)+2(x-y)
4. a+3X b+3Y
n. a+(n-1)X b+(n-1)Y
判断日期是连续的？ 连续的日期，每行之间的差值为1
- 连续的日期每次递增1，再提供一个参考列，这个参考列每次也是递增1
- dt，从2017-1-1开始递增，每次递增1
- B列，从1开始递增，每次递增1
- 如果dt列和B列都是连续的
- 此时 dt列-B列=每行的差值，每行的差值之间的差值，一定等于0，每行之间的差值相等
dt 列B diff
2017-1-1 1 2016-12-31
2017-1-3 2 2017-1-1
2017-1-5 3 2017-1-2
2017-1-6 4 2017-1-2
2017-1-7 5 2017-1-2
2017-1-8 6 2017-1-2
2017-1-12 7 2017-1-5
2017-1-13 8 2017-1-5
2017-1-15 9 2017-1-6
2017-1-16 10 2017-1-6
2017-1-17 11 2017-1-6
-- 判断连续
select user_id, dt, carbonPerDay,
date_sub(dt, row_number() over(partition by user_id order by dt) ) diff
from t1 -- t2
-- 判断连续的天数超过3天
select user_id, dt, carbonPerDay, diff, count(*) over(partition by user_id,diff) diffcount
from t2 -- t3
-- 过滤超过3天的数据
select user_id, dt
from t3
where diffcount >= 3 -- t4
-- 关联原表求出结果
select user_id, dt
from
(
select user_id, dt, carbonPerDay, diff, count(*) over(partition by user_id,diff) diffcount
from
(
select user_id, dt, carbonPerDay,
date_sub(dt, row_number() over(partition by user_id order by dt) ) diff
from (
select user_id, regexp_replace(data_dt, '/', '-') dt, sum(low_carbon) carbonPerDay
from user_low_carbon
where year(regexp_replace(data_dt, '/', '-')) = 2017
GROUP BY user_id, data_dt
having carbonPerDay >= 100 ) t1
) t2
) t3
where diffcount >= 3 order by user_id, dt asc;
九、自定义函数
9.1 编写自定义的函数
①引入依赖
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>1.2.1</version>
</dependency>
②自定义UDF函数，继承UDF类
③提供evaluate()，可以提供多个重载的此方法，但是方法名是固定的
④evaluate()不能返回void，但是可以返回null!
9.2 打包
上传到hiveserver2所在机器
9.3 安装
在HIVE_HOME/auxlib 目录下存放jar包
9.4 创建函数
注意：用户自定义的函数，是有库的范围，指定库下创建的函数，只在当前库有效。
create [temporary] function 函数名 as 自定义的函数的全类名
-- 样例
create temporary function show_name as 'cn.itcats.hive.udf.MyFunction';
创建temporary函数，不会在hive元数据库FUNCS记录，而非temporary函数，在mysql元数据库记录以下信息：
| FUNC_ID | CLASS_NAME | CREATE_TIME | DB_ID | FUNC_NAME | FUNC_TYPE | OWNER_NAME | OWNER_TYPE |
|---|---|---|---|---|---|---|---|
| 1 | cn.itcats.hive.udf.MyFunction | 1604329265 | 6 | show_name | 1 | USER |
show_name这个函数只能在DB_ID=6的库使用
十、企业级优化
10.1 Fetch抓取
Fetch抓取是指，Hive中对某些情况的查询可以不必使用MapReduce计算。
-- 例如
SELECT * FROM employees;
在这种情况下，Hive可以简单地读取employee对应的存储目录下的文件，然后输出查询结果到控制台。
在hive-default.xml.template文件中hive.fetch.task.conversion默认是more，老版本hive默认是minimal，该
属性修改为more以后，在全局查找、字段查找、limit查找等都不走mapreduce。
10.2 本地模式
大多数的Hadoop Job是需要Hadoop提供的完整的可扩展性来处理大数据集。不过，有时Hive的输入数据量是非常小的。在这种情况下，为查询触发执行任务消耗的时间可能会比实际job的执行时间要多的多。对于大多数这种情况，Hive可以通过本地模式在单台机器上处理所有的任务。对于小数据集，执行时间可以明显被缩短。
用户可以通过设置hive.exec.mode.local.auto的值为true，来让Hive在适当的时候自动启动这个优化。
-- 开启本地mr
set hive.exec.mode.local.auto=true;
-- 设置local mr的最大输入数据量，当输入数据量小于这个值时采用local mr的方式，默认为134217728，即128M
set hive.exec.mode.local.auto.inputbytes.max=50000000;
-- 设置local mr的最大输入文件个数，当输入文件个数小于这个值时采用local mr的方式，默认为4
set hive.exec.mode.local.auto.input.files.max=10;
10.3 执行计划(Explain)
1．基本语法
EXPLAIN [EXTENDED | DEPENDENCY | AUTHORIZATION] query
2．案例实操
（1）查看下面这条语句的执行计划
hive (default)> explain select * from emp;
hive (default)> explain select deptno, avg(sal) avg_sal from emp group by deptno;
（2）查看详细执行计划
hive (default)> explain extended select * from emp;
hive (default)> explain extended select deptno, avg(sal) avg_sal from emp group by deptno;
10.4 推测执行
在分布式集群环境下，因为程序Bug（包括Hadoop本身的bug），负载不均衡或者资源分布不均等原因，会造成同一个作业的多个任务之间运行速度不一致，有些任务的运行速度可能明显慢于其他任务（比如一个作业的某个任务进度只有50%，而其他所有任务已经运行完毕），则这些任务会拖慢作业的整体执行进度。为了避免这种情况发生，Hadoop采用了推测执行（Speculative Execution）机制，它根据一定的法则推测出“拖后腿”的任务，并为这样的任务启动一个备份任务，让该任务与原始任务同时处理同一份数据，并最终选用最先成功运行完成任务的计算结果作为最终结果。设置开启推测执行参数：Hadoop的mapred-site.xml文件中进行配置
<property>
<name>mapreduce.map.speculative</name>
<value>true</value>
</property>
<property>
<name>mapreduce.reduce.speculative</name>
<value>true</value>
</property>
不过hive本身也提供了配置项来控制reduce-side的推测执行：
<property>
<name>hive.mapred.reduce.tasks.speculative.execution</name>
<value>true</value>
</property>
10.5 JVM重用
JVM重用是Hadoop调优参数的内容，其对Hive的性能具有非常大的影响，特别是对于很难避免小文件的场景或task特别多的场景，这类场景大多数执行时间都很短。
Hadoop的默认配置通常是使用派生JVM来执行map和Reduce任务的。这时JVM的启动过程可能会造成相当大的开销，尤其是执行的job包含有成百上千task任务的情况。JVM重用可以使得JVM实例在同一个job中重新使用N次。N的值可以在Hadoop的mapred-site.xml文件中进行配置。通常在10-20之间，具体多少需要根据具体业务场景测试得出。
<property>
<name>mapreduce.job.jvm.numtasks</name>
<value>10</value>
</property>
这个功能的缺点是，开启JVM重用将一直占用使用到的task插槽，以便进行重用，直到任务完成后才能释放。如果某个“不平衡的”job中有某几个reduce task执行的时间要比其他reduce task消耗的时间多的多的话，那么保留的插槽就会一直空闲着却无法被其他的job使用，直到所有的task都结束了才会释放。
10.6 严格模式
Hive提供了一个严格模式，可以防止用户执行那些可能意想不到的不好的影响的查询。通过设置属性hive.mapred.mode值为默认是非严格模式nonstrict 。开启严格模式需要修改hive.mapred.mode值为strict，开启严格模式可以禁止3种类型的查询。
<property>
<name>hive.mapred.mode</name>
<value>strict</value>
</property>
-
对于分区表，除非where语句中含有分区字段过滤条件来限制范围，否则不允许执行。换句话说，就是用户不允许扫描所有分区。进行这个限制的原因是，通常分区表都拥有非常大的数据集，而且数据增加迅速。没有进行分区限制的查询可能会消耗令人不可接受的巨大资源来处理这个表。
-
对于使用了order by语句的查询，要求必须使用limit语句。因为order by为了执行排序过程会将所有的结果数据分发到同一个Reducer中进行处理，强制要求用户增加这个LIMIT语句可以防止Reducer额外执行很长一段时间。
-
限制笛卡尔积的查询。对关系型数据库非常了解的用户可能期望在执行JOIN查询的时候不使用ON语句而是使用where语句，这样关系数据库的执行优化器就可以高效地将WHERE语句转化成那个ON语句。不幸的是，Hive并不会执行这种优化，因此，如果表足够大，那么这个查询就会出现不可控的情况。
10.7 并行执行
Hive会将一个查询转化成一个或者多个阶段。这样的阶段可以是MapReduce阶段、抽样阶段、合并阶段、limit阶段，或者Hive执行过程中可能需要的其他阶段。默认情况下，Hive一次只会执行一个阶段。不过，某个特定的job可能包含众多的阶段，而这些阶段可能并非完全互相依赖的，也就是说有些阶段是可以并行执行的，这样可能使得整个job的执行时间缩短。不过，如果有更多的阶段可以并行执行，那么job可能就越快完成。
通过设置参数hive.exec.parallel值为true，就可以开启并发执行。不过，在共享集群中，需要注意下，如果job中并行阶段增多，那么集群利用率就会增加。
-- 打开任务并行执行
set hive.exec.parallel=true;
-- 同一个sql允许最大并行度，默认为8。
set hive.exec.parallel.thread.number=16;
当然，得是在系统资源比较空闲的时候才有优势，否则，没资源，并行也起不来。
10.8 数据倾斜
10.8.1 合理设置Map数
(1) 通常情况下，作业会通过input的目录产生一个或者多个map任务。
主要的决定因素有：input的文件总个数，input的文件大小，集群设置的文件块大小。
(2) 是不是map数越多越好？
答案是否定的。如果一个任务有很多小文件（远远小于块大小128m），则每个小文件也会被当做一个块，用一个map任务来完成，而一个map任务启动和初始化的时间远远大于逻辑处理的时间，就会造成很大的资源浪费。而且，同时可执行的map数是受限的。
(3) 是不是保证每个map处理接近128M的文件块，就高枕无忧了？
答案也是不一定。比如有一个127m的文件，正常会用一个map去完成，但这个文件只有一个或者两个小字段，却有几千万的记录，如果map处理的逻辑比较复杂，用一个map任务去做，肯定也比较耗时。
问题：
-
ORC是否可以切片？
-
ORC不管是否用压缩，都可以切片，即使使用snappy压缩，也可切
如果使用的是TextInputFormat，TextInputFormat根据文件的后缀判断是否是一个压缩格式，只要不是压缩格式，都可切！
如果是压缩格式，再判断是否使用的是可切的压缩格式类型！如果表在创建时，使用store as orc,此时这个表的输入格式会使用OrcInputFormat！
OrcInputFormat.getSplits()方法中，文件是可以切片的，即使使用snappy压缩，也可切
-
-
Parquet是否切片？
- Parquet文件不使用LZO压缩，可以切片。
- Parquet如果使用了LZO压缩，必须创建index后才可切片。
如果表在创建时，使用store as Parquet,此时这个表的输入格式会使用ParquetInputFormat。 ParquetInputFormat继承了FileInputFormat，但并没有重写isSplitable()方法FileInputFormat.isSplitable(){return true}; Parquet文件格式在切片时，也可以切 Parquet+LZO格式的文件，在切片时是可以切，但是通常我们还会为此文件创建Index! 创建索引的目的是，在读入文件时，使用LZO合理的切片策略，而不是默认的切片策略 因为如果表的存储为Parquet+LZO，此时表的输入格式已经不能设置为ParquetInputFormat，而需要设置为 LZOInputFormat！
10.8.2 小文件进行合并
在map执行前合并小文件，减少map数。CombineHiveInputFormat具有对小文件进行合并的功能（系统默认的格式），HiveInputFormat没有对小文件合并功能。
set hive.input.format = org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
10.8.3 复杂文件增加Map数
当input的文件都很大，任务逻辑复杂，map执行非常慢的时候，可以考虑增加Map数，来使得每个map处理的数据量减少，从而提高任务的执行效率。
根据computeSliteSize(Math.max(minSize,Math.min(maxSize,blocksize)))=blocksize=128M公式，调整maxSize最大值。让maxSize最大值低于blocksize就可以增加map的个数。
案例实操：
-- 1．执行查询
hive (default)> select count(*) from emp;
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
-- 2．设置最大切片值为100个字节
hive (default)> set mapreduce.input.fileinputformat.split.maxsize=100;
hive (default)> select count(*) from emp;
Hadoop job information for Stage-1: number of mappers: 6; number of reducers: 1
10.8.4 合理设置Reduce数
- 调整reduce个数方法一
- 每个Reduce处理的数据量默认是256MB
hive.exec.reducers.bytes.per.reducer=256000000
- 每个任务最大的reduce数，默认为1009
hive.exec.reducers.max=1009
- 计算reducer数的公式
N=min(参数2，总输入数据量/参数1)
- 调整reduce个数方法二
在hadoop的mapred-default.xml文件中设置每个job的Reduce个数
set mapreduce.job.reduces = 15;
- reduce个数并不是越多越好
-
过多的启动和初始化reduce也会消耗时间和资源；
-
另外，有多少个reduce，就会有多少个输出文件，如果生成了很多个小文件，那么如果这些小文件作为下一个任务的输入，则也会出现小文件过多的问题；
在设置reduce个数的时候也需要考虑这两个原则：
- 处理大数据量利用合适的reduce数
- 使单个reduce任务处理数据量大小要合适；
10.9 表的优化
10.9.1 小表、大表join
将key相对分散，并且数据量小的表放在join的左边，这样可以有效减少内存溢出错误发生的几率；再进一步，可以使用map join让小的维度表（1000条以下的记录条数）先进内存。在map端完成reduce。
实际测试发现：新版的hive已经对小表join大表和大表join小表进行了优化。小表放在左边和右边已经没有明显区别。
10.9.2 大表join大表
- 空KEY过滤【NULL数据不需要】
如果A表中有大量c字段为null的数据。如果不对null值处理，此时，会产生数据倾斜
-- 情形一：A left join B on A.c = b.c
-- 假如不需要id为null的数据！此时可以将A表中id为null的字段提前过滤，减少MR在执行时，输入的数据量！
-- 解决：将null值过滤，过滤后再执行Join!
-- 测试不过滤空id
insert overwrite table jointable2
select n.* from nullidtable n left join ori o on n.id = o.id; -- Time taken: 18.743 seconds
-- 测试过滤空id
insert overwrite table jointable2
select n.* from (select * from nullidtable where id is not null ) n left join ori o on n.id = o.id; -- Time taken: 7.537 seconds
- 空key转换【NULL数据需要】
有时虽然某个key为空对应的数据很多，但是相应的数据不是异常数据，必须要包含在join的结果中，此时我们可以表a中key为空的字段赋一个随机的值，使得数据随机均匀地分不到不同的reducer上
情形二： A表中c字段为null的数据也需要，不能过滤，如何解决数据倾斜？
注意：①可以将null替换为一个不影响执行结果的随机值！
②注意转换后类型匹配的问题
insert overwrite local directory '/home/atguigu/joinresult'
select n.* from nullidtable n full join ori o on
case when n.id is null then -floor(rand()*100) else n.id end = o.id;
10.9.3 MapJoin
如果不指定MapJoin或者不符合MapJoin的条件，那么Hive解析器会将Join操作转换成Common Join，即：在Reduce阶段完成join。容易发生数据倾斜。可以用MapJoin把小表全部加载到内存在map端进行join，避免reducer处理。
- 开启MapJoin参数设置
（1）设置自动选择Mapjoin
set hive.auto.convert.join = true; -- 默认为true
- 大表小表的阈值设置（默认25M一下认为是小表）：
set hive.mapjoin.smalltable.filesize = 25000000;
10.9.4 group by
默认情况下，Map阶段同一Key数据分发给一个reduce，当一个key数据过大时就倾斜了。
并不是所有的聚合操作都需要在Reduce端完成，很多聚合操作都可以先在Map端进行部分聚合，最后在Reduce端得出最终结果。
1．开启Map端聚合参数设置
（1）是否在Map端进行聚合，默认为True
hive.map.aggr = true -- Combiner
（2）在Map端进行聚合操作的条目数目
hive.groupby.mapaggr.checkinterval = 100000 -- (1)和(2)配合使用
（3）有数据倾斜的时候进行负载均衡（默认是false）
hive.groupby.skewindata = true
当选项设定为 true，生成的查询计划会有两个MR Job。第一个MR Job中，Map的输出结果会随机分布到Reduce中，每个Reduce做部分聚合操作，并输出结果，这样处理的结果是相同的Group By Key有可能被分发到不同的Reduce中，从而达到负载均衡的目的；第二个MR Job再根据预处理的数据结果按照Group By Key分布到Reduce中（这个过程可以保证相同的Group By Key被分布到同一个Reduce中），最后完成最终的聚合操作。
10.9.5 Count(Distinct) 去重统计
数据量小的时候无所谓，数据量大的情况下，由于COUNT DISTINCT操作需要用一个Reduce Task来完成，这一个Reduce需要处理的数据量太大，就会导致整个Job很难完成，一般COUNT DISTINCT使用先GROUP BY再COUNT的方式替换：
select count(distinct id) from bigtable;
select count(id) from (select id from bigtable group by id) a; -- 优化
10.9.6 行列过滤
列处理：在SELECT中，只拿需要的列，如果有，尽量使用分区过滤，少用SELECT *。
行处理：在分区剪裁中，当使用外关联时，如果将副表的过滤条件写在Where后面，那么就会先全表关联，之后再过滤，比如：
- 测试先关联两张表，再用where条件过滤
select o.id from bigtable b
join ori o on o.id = b.id
where o.id <= 10; -- Time taken: 34.406 seconds, Fetched: 100 row(s)
- 通过子查询后，再关联表
select b.id from bigtable b
join (select id from ori where id <= 10 ) o on b.id = o.id; -- Time taken: 30.058 seconds, Fetched: 100 row(s)
10.9.6 动态分区调整
关系型数据库中，对分区表Insert数据时候，数据库自动会根据分区字段的值，将数据插入到相应的分区中，Hive中也提供了类似的机制，即动态分区(Dynamic Partition)，只不过，使用Hive的动态分区，需要进行相应的配置。
-
静态分区：load data local inpath ‘xx’ into table xxx partition(分区列名=分区列值)
在导入数据时，分区的名称已经确定 -
动态分区：在导入数据时，根据数据某一列字段的值是什么，就自动创建分区，动态分区需要在动态分区非严格模式才能运行动态分区
需要在动态分区非严格模式才能运行set hive.exec.dynamic.partition.mode=nonstrict;动态分区只能使用insert方式导入数据，注意字段的顺序问题，分区列必须位于最后一个字段
1. 开启动态分区参数设置
（1）开启动态分区功能（默认true，开启）
hive.exec.dynamic.partition=true
（2）设置为非严格模式（动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区。）
hive.exec.dynamic.partition.mode=nonstrict
（3）在所有执行MR的节点上，最大一共可以创建多少个动态分区。
hive.exec.max.dynamic.partitions=1000
（4）在每个执行MR的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即day字段有365个值，那么该参数就需要设置成大于365，如果使用默认值100，则会报错。
hive.exec.max.dynamic.partitions.pernode=100
（5）整个MR Job中，最大可以创建多少个HDFS文件。
hive.exec.max.created.files=100000
（6）当有空分区生成时，是否抛出异常。一般不需要设置。
hive.error.on.empty.partition=false
2. 案例实操
需求：将ori中的数据按照时间(如：20111230000008)，插入到目标表ori_partitioned_target的相应分区中
（1）创建分区表
create table ori_partitioned(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string)
partitioned by (p_time bigint)
row format delimited fields terminated by '\t';
（2）加载数据到分区表中
load data local inpath '/home/atguigu/ds1' into table ori_partitioned partition(p_time='20111230000010') ;
load data local inpath '/home/atguigu/ds2' into table ori_partitioned partition(p_time='20111230000011') ;
（3）创建目标分区表
create table ori_partitioned_target(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string)
PARTITIONED BY (p_time STRING)
row format delimited fields terminated by '\t';
（4）设置动态分区
set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = nonstrict;
set hive.exec.max.dynamic.partitions = 1000;
set hive.exec.max.dynamic.partitions.pernode = 100;
set hive.exec.max.created.files = 100000;
set hive.error.on.empty.partition = false;
insert overwrite table ori_partitioned_target partition (p_time)
select id, time, uid, keyword, url_rank, click_num, click_url, p_time from ori_partitioned;
（5）查看目标分区表的分区情况
show partitions ori_partitioned_target;
扫一扫
471
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
