目录
1. Hive基础
1. Hive基础认识
hive是一个分析框架，用于解决海量结构化日志的数据统计工具。
给予hadoop的数据仓库工具。将结构化的数据映射为一张表，并且用sql查询。
本质是将hql提交给yarn，转换为mapreduce任务。
- hive处理的数据存储在hdfs上。
- hive分析数据底层是mr程序。
- 执行的程序在yarn上， 进行资源分配。
hive是不存数据的。原数据是从hdfs上读取，元数据是从mysql中读取。
1.1.1 优缺点
优点：
(1) 类sql语法， 易上手
(2) 避免写mr
(3) 延迟高，实时性不高，适用于离线任务
(4) 优势在于处理大数据
(5) 支持自定义函数
缺点：
(1) 迭代计算无法表达，迭代计算是在一个计算结果的基础上再进行计算。 MR有什么局限性，hive也会存在。mr任务是一个个串联起来跑的。
(2) 数据挖掘并不是擅长。比如， 啤酒 + 尿布 是属于挖掘
(3) 效率低， 调优困难。
1.2 Hive架构
（1） Meta store ：用于存储表和文件的对应关系， 默认的是durby（德比）数据库，但是存在问题，一般换成mysql。
元数据包括:表名、表所属的数据库(默认是 default)、表的拥有者、列/分区字段、表 的类型(是否是外部表)、表的数据所在目录等。
（2） 客户端 ：CLI(hive shell)、JDBC/ODBC(在java代码中 访问 hive， 要有jdbc得到驱动)、WEBUI(浏览器访问 hive)
（3） 核心部分：
核心部分是上图中黄色框3。
整个过程是将hql转换为mr任务，提交给yarn执行。
（3.1） 解析器(SQL Parser):将 SQL 字符串转换成抽象语法树 AST，这一步一般都用 第三方工具库完成，比如 antlr;对 AST 进行语法分析，比如表是否存在、字段是否存 在、SQL 语义是否有误。
（3.2）编译器(Physical Plan):将 AST 编译生成逻辑执行计划，简而言之就是翻译。
（3.3）优化器(Query Optimizer):对逻辑执行计划进行优化。
（3.4）执行器(Execution):把逻辑执行计划转换成可以运行的物理计划。对于 Hive 来 说，就是 MR/Spark。
（3.5）Driver部分是驱动，连接hive与hdfs。
（4）hadoop部分 ：使用 HDFS 进行存储，使用 MapReduce 进行计算。
（5）
meta stroe存的是元数据hdfs存的是原数据 元数据和原数据是不同的
hive是hadoop的客户端
hive的元数据都是第三方存， 数据是hdfs存， 只是个客户端。
1.3 Hive任务提交流程
执行的顺序是:
(1) 在客户端（框2）提交hql，之后， 先经过解析器，校验表存不存在，字段对不对等信息；
（2）再之后经过编译器，转换为mr任务；
（3）再经过优化器进行优化；
（4）执行任务，提交给yarn；
（5）最后执行任务的时候，先从mysql中加载元数据（框1），根据元数据拼接起来的路径，到hdfs中访问实际的数据；
1.4 Hive与数据库的对比
1.存储位置：
1.1 数据库的数据是存在设备或者本地的；hive是建立在hadoop之上的，存在hdfs上。
2. 数据更新：
hive更多是查询；数据库是增删改查；
索引： 数据库是有索引，加速查询的；hive是无索引
3. 执行： hive是mr来实现的；数据通常有自己执行引擎；
4. 执行延迟： hive延迟高；数据库延迟小数据量延迟比较底 。主要是数据规模的问题
5. 可扩展性： hive因为是基于hadoop，存储能力，计算能力都能扩展；数据库可扩展的较少；
1.5 Hive 的元数据库
一般不会使用Hive自带的德比数据库，更换为mysql。
在在替换为mysql数据库后，会有个metastore库，库下有个TBLS的表， 这个表中存的是hive中创建的表的字段信息，所属数据库信息。并且根据所属数据库信息字段，从DBS表中，可以查到该表的hdfs 路径。
另外有4张是以PARTITION开头的表， 是和分区有关的信息。
1.6 hive加载数据文件
从文件系统中加载数据
方式一：
在hive客户端执行 ， 既可以在hive的自带客户端执行，也能在hue这样的界面客户端执行
load data local inpath “路径” into table 表名
load data local inpath “路径” overwrite into table 表名
加上overwrite会覆盖原有的，不加会在原数据后面继续添加数据
如果数据是在hdfs上， 则不用加local，后面跟的路径是数据再hdfs上的路径；数据要是在本地就需要加local，并且数据是在本地的。
方式二：
通过hadoop执行
hadoop fs -put 文件名 hdfs路径
hdfs路径是表所在的路径
hadoop fs -put name.txt /user/hive/warhouse/stu
load语句从本地加载数据，是将数据文件复制的，但是如果是load hdfs路径上的数据， 是移动数据。这个移动数据，并非是真的移动了数据文件，实际数据还是hdfs上的DN文件夹路径下没有变；变的只是对应的NameNode上元数据。
建表语句：
create table 表名(
id int comment "ids",
name string comment "名字"
)
row formate delimited fields terminated by '\t';
***注意后面有分号***
1.7 Hive常用交互命令
hive -help 查看帮助
hive -e 后面加sql语句
hive -e “select * from table_name”
hive -f 后面跟sql文件， 可以直接执行sql文件
hive -f /data/demo.hql
hive -f /data/demo.hql > /data/log/result.txt 把查询结果追加到文件中
1.8 Hive数据类型
以下内容介绍的是hive的数据类型，以及类型的转换。
hive的数据类型分为基本类型和集合类型。
1.8.1 基本类型
下图是基本数据类型：
对于 Hive 的 String 类型相当于数据库的 varchar 类型，该类型是一个可变的字符串，不 过它不能声明其中最多能存储多少个字符，理论上它可以存储 2GB 的字符数。
1.8.2 集合类型
下图是集合数据类型：
Hive 有三种复杂数据类型 ARRAY、MAP 和 STRUCT。ARRAY 和 MAP 与 Java 中的 Array 和 Map 类似，而 STRUCT 与 C 语言中的 Struct 类似，它封装了一个命名字段集合， 复杂数据类型允许任意层次的嵌套。
集合类型使用的时候并不多。
create table test(
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';
在建表的时候，设定好字段的类型，以及数据类型的分隔符，在load 数据到表里的时候，会自动的将数据解析成指定的数据格式。
在查询的时候，查询数组类型的字段，使用列名[索引值]的方式获取；
1.8.3 Hive类型转换
Hive 的原子数据类型是可以进行隐式转换的，类似于 Java 的类型转换，例如某表达式 使用 INT 类型，TINYINT 会自动转换为 INT 类型，但是 Hive 不会进行反向转化，例如， 某表达式使用 TINYINT 类型，INT 不会自动转换为 TINYINT 类型，它会返回错误，除非使 用 CAST 操作。
简而言之是，低精度的可以自动的往高精度转，但是高精度的不能自动的转为低精度，因为会损失精度，但是可以强制转换。
-
隐式类型转换规则如下
(1)任何整数类型都可以隐式地转换为一个范围更广的类型，如 TINYINT 可以转换
成 INT，INT 可以转换成 BIGINT。
(2)所有整数类型、FLOAT 和 STRING 类型都可以隐式地转换成 DOUBLE。
(3)TINYINT、SMALLINT、INT 都可以转换为 FLOAT。
(4)BOOLEAN 类型不可以转换为任何其它的类型。 -
可以使用 CAST 操作显示进行数据类型转换
例如 CAST(‘1’ AS INT)将把字符串’1’ 转换成整数 1;如果强制类型转换失败，如执行
CAST(‘X’ AS INT)，表达式返回空值 NULL。
cast('1' as int) # 会转换成功
cast('qqqqq' as int) # 这样会转换失败， 返
回的是NULL
2. DDL数据定义
关于库， 表的增删改查
2.1 数据库操作
在创建的数据库的时候可以指定数据库在hdfs上的路径的位置， 默认是在根目录下。
-- 创建数据库
create database db_hive2 location '/db_hive2.db';
-- 显示数据库信息
desc database db_hive;
-- 删除数据库
drop database db_hive;
2.2 表操作
2.2.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]
[LOCATION hdfs_path]
实例：
CREATE EXTERNAL TABLE hive_ods.tmp_20200427_7(
a1 string COMMENT 'a1',
a2 string COMMENT 'a2',
a3 string COMMENT 'a3'
)
COMMENT 'test'
ROW FORMAT DELIMITED fields terminated by ','
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
'hdfs://bdp/dba/hive_ods.db/tmp_20200427_7'
可以通过show create table hive_ods; 查看建表语句， 下面会显示一些默认的信息。
2.2.2 建表字段说明
(1)CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常;用户可以用 IFNOTEXISTS 选项来忽略这个异常。
(2)EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际 数据的路径(LOCATION)，Hive 创建内部表时，会将数据移动到数据仓库指向的路 径;若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的 时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
(3)COMMENT:为表和列添加注释。
(4)PARTITIONED BY 创建分区表
(5)CLUSTERED BY 创建分桶表
(6)SORTED BY 不常用
(7)ROW FORMAT
DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value,
property_name=property_value, …)]
用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户 还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。
SerDe 是 Serialize/Deserilize 的简称，目的是用于序列化和反序列化。
(8)STORED AS 指定存储文件类型
常用的存储文件类型:SEQUENCEFILE(二进制序列文件)、TEXTFILE(文本)、 RCFILE(列式存储格式文件)
如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩， 使用 STORED AS SEQUENCEFILE。
(9)LOCATION :指定表在 HDFS 上的存储位置。
(10)LIKE 允许用户复制现有的表结构，但是不复制数据。
2.3 管理表（内部表）
默认创建的表都是所谓的管理表，有时也被称为内部表。因为这种表，Hive 会(或多 或少地)控制着数据的生命周期。Hive 默认情况下会将这些表的数据存储在由配置项 hive.metastore.warehouse.dir(例如，/user/hive/warehouse)所定义的目录的子目录下。当 我 们 删除一个管理表时，Hive 也会删除这个表中数据。管理表不适合和其他工具共享数据。
简而言之，内部表删除数据的时候是元数据和原数据一起删除的；外部表删除的时候是只删除mysq中的元数据，不删除hdfs上的原数据。
2.4 外部表
2.4.1 创建外部表
因为表是外部表，所以 Hive 并非认为其完全拥有这份数据。删除该表并不会删除掉这 份数据，不过描述表的元数据信息会被删除掉。
创建外部表语句：
-- 加上 external 关键字
create external table if not exists default.dept( deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';
desc formatted table_name 查看表的基本信息， 其中就能看到到底是内部还是外部表。基本新建的都是外部表， 因为为了数据安全； 只有临时的的才会建内部表。
2.5 内部表和外部表转换
将内部表转为外部表
alter table student2 set tblproperties(‘EXTERNAL’=‘TRUE’);
2.6 分区表
分区表实际上就是对应一个 HDFS 文件系统上的独立的文件夹，该文件夹下是该分区 所有的数据文件。Hive 中的分区就是分目录，把一个大的数据集根据业务需要分割成小的 数据集。在查询时通过 WHERE 子句中的表达式选择查询所需要的指定的分区，这样的查 询效率会提高很多。
2.6.1 分区表的操作
-- 创建分区表语句
create table dept_partition( deptno int, dname string, loc string
)
partitioned by (month string)
row format delimited fields terminated by '\t';
-- 将数据加载到分区中
load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201708');
-- 增加分区
alter table dept_partition add partition(month='201706') ;
-- 创建多个分区
alter table dept_partition add partition(month='201705') partition(month='201704');
--删除分区
alter table dept_partition drop partition (month='201704');
-- 删除多个区分
alter table dept_partition drop partition (month='201705'), partition (month='201706');
--查看分区表有多少分区
show partitions dept_partition;
--查看分区表结构
desc formatted dept_partition; 或者 desc dept_partition;
2.6.2 创建二级分区表
-- 创建二级分区表
create table dept_partition2( deptno int, dname string, loc string
)
partitioned by (month string, day string)
row format delimited fields terminated by '\t';
--加载数据导二级分区表中，即再添加一个分区字段
load data local inpath '/opt/module/datas/dept.txt' into table
default.dept_partition2 partition(month='201709', day='13');
2.6.3 分区表注意事项
把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式 ：
（1）上传数据后修复
创建分区目录dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201709/day=12;
将数据添加到分区目录中
dfs -put /opt/module/datas/dept.txt /user/hive/warehouse/dept_partition2/month=201709/day=12;
截止到这一步是差不多数据的；
执行修复命令
msck repair table dept_partition2;
之后就能查询了
(2) 上传数据后添加分区
创建分区目录
dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201709/day=12;
将数据添加到分区目录中
dfs -put /opt/module/datas/dept.txt /user/hive/warehouse/dept_partition2/month=201709/day=12;
添加分区
alter table dept_partition2 add partition(month=‘201709’,
（3） 创建文件夹后 load 数据到分区
创建分区目录
dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201709/day=10;
加载数据
load data local inpath ‘/opt/module/datas/dept.txt’ into table dept_partition2 partition(month=‘201709’,day=‘10’);
这样的方式貌似只能再sever节点上执行或者客户端执行
2.7 修改表的语句
（1）重命名表: ALTER TABLE table_name RENAME TO new_table_name
（2）修改列: alter table dept_partition change column deptdesc desc int;
（3）替换列: alter table dept_partition replace columns(deptno string, dname
（4）增加列: alter table dept_partition add columns(deptdesc string);
（5）删除表
3. DML操作
3.1. 数据导入
从文件系统中加载数据
方式一：
在hive客户端执行 ， 既可以在hive的自带客户端执行，也能在hue这样的界面客户端执行
load data local inpath “路径” into table 表名
load data local inpath “路径” overwrite into table 表名
加上overwrite会覆盖原有的，不加会在原数据后面继续添加数据
如果数据是在hdfs上， 则不用加local，后面跟的路径是数据再hdfs上的路径；数据要是在本地就需要加local，并且数据是在本地的。
方式二：
通过hadoop执行
hadoop fs -put 文件名 hdfs路径
hdfs路径是表所在的路径
hadoop fs -put name.txt /user/hive/warhouse/stu
将数据从hdfs导入到表中，用load 语句
3.2 数据插入
用的很多
从一个表select出数据再insert到另一个表中
insert into table 表名 partition={} # 不覆盖
select *
from 表名
insert overwrite table # 覆盖 没有into
select *
from 表名
3.3 查询语句中创建表并加载数据路径 as select
即在创建表的时候， 同时加载出数据。 大致是这样的:
create table aa(name string) as select * from aa;
3.4 with关键字
with关键词用来定义临时表：
-- table_name_0 在同脚本下就能直接用查询的结果
with table_name_0 as(
select *
from table_name_1
)
3.5 import 数据导指定的hive表中
这个方式不常用，比较麻烦。需要export之后再 再用import数据导入
注意： 先用export导出后， 再将数据导入
export和import 在生产中很少用
3.6 insert语句将数据导出为文件
insert overwrite local directory
'/opt/module/datas/export/student1'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from student;
将student表中的数据导出到本地路径上的文件中。
去掉local 后是将数据导入到hdfs路径上
27 28 29 没看
下面是30
3.7 Export 导出到 HDFS 上
这个方式不常用，比较麻烦。需要export之后再 再用import数据导入
注意： 先用export导出后， 再将数据导入
export和import 在生产中很少用
3.8 Hive Shell 命令导出数据
hive -e 'select * from default.student;' > /opt/module/datas/export/student4.txt;
将查询的结果用sh命令写入文件。
3.9 清空表 truncate
truncate 是要删除数据的，但是只能删除管理表，不能删除外部表中的数据 。
字段等元数据是保留的， 是删除数据的。
truncate table 表名
4. 查询
4.1 基础查询
查询全表或者特定列
4.2 列别名
列别名 as 关键词 或者as也可以不写。
- 重命名一个列，
- 便于计算
- 紧跟列名 也可以在列名和列别名之间加关键词 as
4.3 算数运算符
日志的分析中后面的几个用的少。
4.4 常用函数
count, max, min, avg , sum
这些都是聚合函数，需要先分组，再使用聚合函数；会触发mr任务，因为需要计算 。
4.5 where
谓词下推： 优先条件查询
1．使用 WHERE 子句，将不满足条件的行过滤掉
2． WHERE 子句紧随 FROM 子句
4.5.1 比较运算符 between, in, is null
select 'a'='a' 返回 true
select 'a'='b' 返回 false
select 'a'=null 返回 NULL
select null=null 返回 NULL -- 因为空值不存在相等的情况，所以为null
select null <=> null 返回 true-- 这样的情况返回是true， <=> 称作指向符
select 'a' <=> null 返回false
select 'a' between 'a' and 'c' 返回true
4.6 like 和 rlike 模糊查询
（1）使用 % 是匹配另个或者多个任意字符；
（2）使用 _ 代表一个字符 ；
select 'a' like 'a_' 返回 false
select 'a' like 'a%' 返回 true
rlike是hive的扩展功能，可以使用正则匹配条件
比如：
select 'a' rlike '[a]' 返回 true 判断是否包含a
和like中的 % 一样的作用
select 'a' like 'a%'
4.7 逻辑运算符
and , or , not
4.8 分组
4.8.1 group by
group by 通常和聚合函数一起使用，按照一个或者多个队列结果进行分组， 然后对每个组执行聚合操作。
group by 聚合的意思是： 找出该行所有对应的项， 放到一个新的列中：
a b c
12-25 type1 111
12-25 type1 222
12-25 type2 111
12-25 type2 322
针对a b列聚合：
得出的结果是：
列1 列2
12-25，type1 111，222
12-25，type2 111，322
针对c列聚合：
得出的结果是：
列1 lie2
111 12-25，type1；12-25，type2
222 12-25，type1
322 12-25，type2
4.8.2 having
where针对是的列发挥作用，传数据；
having是针对查询结果中的列发挥作用。
就是不能在where中使用聚会函数。
where在group by 之前，但是having中就能使用聚会函数，having在group by之后。
having 与 where 不同点：
(1)where 针对表中的列发挥作用，查询数据;having 针对查询结果中的列发挥作用， 筛选数据。
(2)where 后面不能写聚合函数，而 having 后面可以使用聚合函数。
(3)having 只用于 group by 分组统计语句。
-- having 是在group by 之后，所以sum函数执行的结果是可以通过having筛选条件，但是在where后面就不行。
select deptno, avg(sal) avg_sal
from emp group
by deptno having
avg_sal > 2000;
4.9 join
- hive 的join语句仅仅支持等值连接， 不支持非等值连接；mysql中允许使用非等值连接比较多
- join的关联的时候不能使用or连接，只能用and 。
- 在join的时候会可能会产生笛卡尔积的情况：
select *
from
(
select id, name
from a
) aa
join
(
select id, sex
from b
) bb
on aa.id = bb.id
-- 这里能写，等于号， on aa.id = bb.id 但是hive不支持不等于的，!=, <>
select *
from
table1 t1
join
table2 t2
on t1.id = t2.id
在join的时候可以使用表的别名：
- 简化查询
- 使用表名前缀可以提高效率
内连接用join，
左连接用left join
右连接用 right join
满外连接 full join， 也叫圈外连接
笛卡尔连接 cross join
多表连接
笛卡尔积会在下面的条件产生：
- 省略连接条件
- 连接条件无效
select *
from
table1 t1
join
table2 t2
on t1.id = t2.id
join
join的效率并不高，但是为什么还要使用join呢？
因为在hive中，表要进行维度抽取， 这样更灵活，建的是宽表。
hive存储资源多，但是计算资源有限。
join 的时候后面的on 关联条件中，不能出现 or 连接词， 可以出现and，mysql是支持中间的or连接的。
4.10 排序
4.10.1 order by
order by 默认是从小到大，升序， 加上desc 是降序 。
order by 是全局排序， 只有一个reducer。尽管在执行的脚本的时候可以设定reducer的个数，但是设置之后还是一个reducer。
多列排序：order by后面多了列名
-- 可以按照指定的别名列排序，可以看出order by是在select 之后执行；
select ename, sal*2 twosal
from emp
order by twosal;
-- 多个列排名
select ename, deptno, sal
from emp
order by deptno, sal ;
4.10.2 每个mapreduce内部的 sort by
reduces默认是-1， 设置reduces的个数是根据数据量来的
set maprecude,job,reduces=3 可以这样设置reduce的个数
这样查出来的会按照哈希算法分成3个区，分区规则是随机算法， 如果指定分区字段的话
set mapreduces.job.recude=3
select *
from tablename
sort by sal
-- 这样会吧排序的结果分为3个文件，每个文件里面的内容都是按照sal排序的，
-- 但是分为3个文件的是依据随机算法的。
-- 因为没有指定字段来分割这个3个文件， 可以指定distribute by 指定字段
set mapreduces.job.recude=3
select *
from tablename
distribute by deptno
sort by sal
-- distribute by写在sort by前面
-- 一般想对分区内排序要使用sort by 和distribute by结合使用
4.10.3 sort by 和order by的区别
Hive基于Hadoop的mapreduce来执行分布式程序的，和普通单机程序不同的一个特点就是最终的数据会产生多个子文件，每个reducer节点都会处理partition给自己的那份数据产生结果文件，这导致了在Hadoop环境下很难对数据进行全局排序，如果在Hadoop上进行order by全排序，会导致所有的数据集中在一台reducer节点上，然后进行排序，这样很可能会超过单个节点的磁盘和内存存储能力导致任务失败。
一种替代的方案则是放弃全局有序，而是分组有序。比如，一共有1千万条数据，其中有一个字段叫产品线，一个字段叫点击量。如果想对点击量排序，如果使用order by，则会进行全局排序，可能导致执行任务的reduce节点内存不足。 于是， 可以先按照产品线分组，每组会分布到不同的reduce节点上运行，在该节点上的数据，是有序的。
--使用order by会引发全局排序
select * from baidu_click order by click desc;
--使用distribute和sort进行分组排序
select * from baidu_click distribute by product_line sort by click desc;
distribute by + sort by就是该替代方案，被distribute by设定的字段为KEY，数据会被HASH分发到不同的reducer机器上，然后sort by会对同一个reducer机器上的每组数据进行局部排序。 至于会产生几个分区文件，貌似是根据分区个数。
distribute+sort的结果是按组有序而全局无序的，输入数据经过了以下两个步骤的处理：
- 根据KEY字段被HASH，相同组的数据被分发到相同的reducer节点；
- 对每个组内部做排序
在进行分组排序之后， 如果结果还是想要一个全局排序的结果，那就取出每组的前部分数据，再进行order by。
参考地址： order by 和sort by区别
4.10.4 cluster by
当distribute by和sort by的字段相同的时候， 可以使用cluster by 。但是不能指定升降序， 默认只能是升序。
select *
from tablename
cluster by deptno
如果使用的sort by设置的reduce是1个，那结果和order by一样
- order by 全局排序 1个reduce
- sort by 可以设置reduce个数， 和distribute by 连用
- cluster by 如果使用sort by和distribute by 的排序字段一致的时候，使用clustre by
建表语句里面的partition 都是加partitioned by的， 查询语句里面是没有加ed
4.11 分桶表
4.11.1 分桶表的定义
分区表是将数据放在不同的路径下， 文件夹的名字是分区名；
分桶表是将数据都放在同一个文件夹中，但是多个文件存放；是按照字段的规律将数据分开，分桶表跟mr的partition有些像。
分桶表的建表语句：
create tablename(
id int,
name string
)
clustered by(id)
into 4 buckets -- 分几个桶， 即走4个recude， 输出4个文件
row formate del.......
由此可以看出， 在创建分区表的时候是partitioned by(pt) pt是新字段，
但是分桶表的是一个已经存在的字段；
4.11.2 分桶抽样查询
暂时略过
4.12 常用函数
4.12.1 空值赋值
nvl函数：nvl(列名1, replace_with): 如果列名1为空值， 则replace_with的内容代替，此处可以为一个固定的值， 也可以是别的列名。
select nvl(name1, name2)
from table_name
或
select nvl(name1, 'zhangsan')
from table_name
-- 如果name1 为NULL， 则只用name2替代，如果name2还是NULL， 则就是空；
-- hive不支持这样写， 只能写2个参数
select nvl(name1, name2，name3) from table_name
4.12.2 时间函数
4.12.2.1 时间格式化
select date_format('2019-12-29', 'yyyy-MM')
date_format是只接受yyyy-MM-dd 格式的日期
4.12.2.2 时间相加减
select date_add('2019-12-29', 1)
select date_add('2019-12-29', -1)
select date_sub('2019-12-29', 1)
select date_sub('2019-12-29', -1)
select datediff('2019-12-29', '2019-12-20')
是前面的减去后面的， 9， 反过来是-9
4.12.3 替换函数
select regexp_replace('2019/12/29', '/', '-')
select datediff(regexp_replace('2019/12/29', '/', '-'), regexp_replace('2019/12/28', '/', '-'))
4.12.4 case when
select dept_id,
sum(case sex when '男' then 1 else 0 end) male,
sum(case sex when '女' then 1 else 0 end) female
from table_name
group by dept_id
或者
select dept_id,
sum(if(sex='男',1,0)) male,
sum(if(sex='女',1,0)) female
from table_name
group by dept_id
总结：
如果分支少的时候可以用if代替了；如果多的时候可以用case when
4.12.5 if函数
参考上面的sql代码
if(sex='男',1,0)
if函数三个参数：
第一个是布尔类型表达式；
第二个是正确的返回结果；
第三个是错误的返回结果
4.12.6 行列互转
相关函数介绍：
4.12.6.1 concat函数
concat(): 字符串拼接函数
select concat('1', '323','dsadf')
select concat(cast(model_id as string), public_type, '222')
from table_name
where pt='2019-12-25'
limit 10
-- model_id 原为int， 使用cast强制转换为string， 才能使用concat拼接；
4.12.6.2 concat_ws函数
concat_ws(): 字符串拼接函数 是concat的特殊形式;
是把一行中的多个字段连接起来
select concat_ws('-', 'a', 'b', 'c') -- 结果为 'a-b-c'
select concat_ws('-', cast(model_id as string), public_type, public_author)
from table_name
where pt='2019-12-25'
limit 10
concat_ws: 第一个参数是连接符，后面的都是需要连接的字段
4.12.6.3 collect_set 和 collect_list
collect_set: 是将多行转换为一行；
collect_set是聚合函数， 需要使用group by
id name
1 a
2 b
3 c
4 d
5 e
6 f
--对id列使用collect_set方法，则会得出 这样的结果[1,2,3,4,5,6]
-- collect_set方法只接受1个参数；
select collect_set(id), collect_set(name)
from table_name
-- 得出结果是：
_col _col2
[1,2,3,4,5,6] [a,b,c,d,e,f]
collect_list和collect_set使用方法是一致的， 区别在于set和list
select concat_ws('-', collect_set(cast(public_type as string))),
concat_ws('__',collect_set(cast(model_id as string)))
from table_name
where pt='2019-12-25'
limit 10
先将字段model_id强制转为string类型；
使用collect_set方法每一行的id取出来放到一个set中，即转为一行中；
再使用concat_ws方法拼接成为一个字符串；
select t.a, collect_list(b)
from
(
select concat_ws(',', released_time,public_type) a, model_id b
from table_name
where pt='2019-12-25'
limit 10
) t
4.12.7 列转行
explode()： 将hive的一列中的复杂的array，或者map拆开分为多个行
lateral view: 侧写
(1) 用法： LATERAL VIEW UDTF_FUNCATION_NAME(列名) tableAlias AS columnAli
(2) 解释：用于和 split, explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据，在此
基础上可以对拆分后的数据进行聚合。
UDTF_FUNCATION_NAME： 需要使用的udtf函数的名
列名： 函数所涉及的列名， 比如本示例中使用udtf函数explode，列名需要指定为炸裂的列名
tableAlias ： 临时定义的表的别名，只是作为一个虚拟表，其余得到地方也不会用到
columnAli ：列的别名
UDTF是一进多出。
最终要得出下面的结果：
列名1 列名2
疑犯追踪 悬疑
疑犯追踪 动作
疑犯追踪 科幻
疑犯追踪 剧情
lie to me 悬疑
lie to me 警匪
.......
.....
...
..
.
select movie,
category_name
from table_name
lateral view explode(category) tabel_tmp as category_name
tabel_tmp是表的别名
category_name是列名
4.12.8 窗口函数
窗口函数文章地址： 窗口函数
4.12.9 排名函数
排名函数文章地址： 排名函数
5025
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
