Hive 基础
Facebook为了解决海量日志数据的分析而开发了hive，后来开源给了Apache基金会组织。 hive是一种用SQL语句来协助读写、管理存储在HDFS上的大数据集的数据仓库软件。
Hive 特点
1 是基于 Hadoop 的一个数据仓库工具；
2 Hive 最大的特点是将 Hive SQL语句转换为 MapReduce、Tez 或者 spark 等任务执行,使得大数据分析更容易。
3 可以将结构化的数据映射为一张数据库表,库和表的元数据信息一般存在关系型数据库上（比如MySQL）；
4 底层数据是存储在 HDFS 上，hive本身并不提供数据的存储功能；
5 数据存储方面：他能够存储很大的数据集，并且对数据完整性、格式要求并不严格；
6 数据处理方面：不适用于实时计算和响应，使用于离线分析。
为什么使用Hive
1 直接使用 MapReduce、Tez、Spark学习成本太高，因为需要了解底层具体执行引擎的处理逻辑，而且需要一定的编码基础；而Hive提供直接使用类sql语言即可进行数据查询和处理的平台或接口，只要使用者熟悉sql语言即可；
2 MapReduce、Tez、Spark实现复杂查询逻辑开发难度大，因为需要自己写代码实现整个处理逻辑以及完成对数据处理过程的优化，而hive将很多数据统计逻辑封装成了可直接使用的窗口函数，且支持自定义窗口函数来进行扩展，而且hive有逻辑和物理优化器，会对执行逻辑进行自动优化。
Hive 架构
Hive的内部架构总共分为四大部分：
1 用户接口层（cli、JDBC/ODBC、Web UI）
(1) cli （Command Line Interface），shell终端命令行，通过命令行与hive进行交互；
(2) JDBC/ODBC，是 Hive 的基于 JDBC 操作提供的客户端，用户（开发员，运维人员）通过客户端连接至 Hive server 服务；
(3) Web UI，通过浏览器访问hive。
2 元数据存储系统
(1) 元数据 ，通俗的讲，就是存储在 Hive 中的数据的描述信息；
(2) Hive 中的元数据通常包括：表的名字，表的列和分区及其属性，表的属性（内部表和 外部表），表中数据所在的目录；
(3) Metastore 默认存在自带的 Derby 数据库或者我们自己创建的 MySQL 库中；
(4) Hive 和 MySQL或Derby 之间通过 MetaStore 服务交互。
3 Thrift Server-跨语言服务
Hive集成了Thrift Server，让用户可以使用多种不同语言来操作hive。
4 Driver(Compiler/Optimizer/Executor)
Driver完成HQL查询语句的词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在HDFS上，并由MapReduce调用执行。
整个过程的执行步骤如下：
(1) 解释器完成词法、语法和语义的分析以及中间代码生成，最终转换成抽象语法树；
(2) 编译器将语法树编译为逻辑执行计划；
(3) 逻辑层优化器对逻辑执行计划进行优化，由于Hive最终生成的MapReduce任务中，而Map阶段和Reduce阶段均由OperatorTree组成，所以大部分逻辑层优化器通过变换OperatorTree，合并操作符，达到减少MapReduce Job和减少shuffle数据量的目的；
(4) 物理层优化器进行MapReduce任务的变换，生成最终的物理执行计划；
(5) 执行器调用底层的运行框架执行最终的物理执行计划。
Hive 数据组织
1 Hive 的存储结构包括 数据库、表、视图、分区和表数据 等。数据库，表，分区等都对应HDFS上的一个目录。表数据对应 HDFS 对应目录下的文件。
2 Hive 中包含以下数据模型：
database ：在 HDFS 中表现为${hive.metastore.warehouse.dir}或者指定的目录下的一个文件夹； table ：在 HDFS 中表现为某个 database 目录下一个文件夹； external table ：与 table 类似，在 HDFS 中也表现为某个 database 目录下一个文件夹； partition ：在 HDFS 中表现为 table 目录下的子目录； bucket ：在 HDFS 中表现为同一个表目录或者分区目录下根据某个字段的值进行 hash 散列之后的多个文件； view ：与传统数据库类似，只读，基于基本表创建。
3 Hive 中的表分为内部表、外部表、分区表和 Bucket 表。
内部表和外部表的区别：
1.内部表数据由Hive自身管理，外部表数据由HDFS管理；
2.删除内部表会直接删除元数据（metadata）及存储数据；删除外部表仅仅会删除元数据，HDFS上的文件并不会被删除。
分区表和分桶表的区别：
1.分区表，Hive 数据表可以根据某些字段进行分区操作，细化数据管理，让部分查询更快；
2.分桶表：表和分区也可以进一步被划分为桶，分桶表中的数据是按照某些分桶字段进行 hash 散列形成的多个文件。
Hive 数据类型
1 Hive的内置数据类型可以分为两大类
① 基础数据类型包括：TINYINT、SMALLINT、INT、BIGINT、BOOLEAN、FLOAT、DOUBLE、STRING、BINARY、TIMESTAMP、DECIMAL、CHAR、VARCHAR、DATE。
② 复杂类型包括ARRAY、MAP、STRUCT、UNION，这些复杂类型是由基础类型组成的。
数据类型 所占字节 开始支持版本 TINYINT 1byte： -128 ~ 127 SMALLINT 2byte：-32,768 ~ 32,767 INT 4byte：-2,147,483,648 ~ 2,147,483,647 BIGINT 8byte：-9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807 BOOLEAN FLOAT 4byte单精度 DOUBLE 8byte双精度 STRING BINARY 从Hive0.8.0开始支持 TIMESTAMP 从Hive0.8.0开始支持 DECIMAL 从Hive0.11.0开始支持 CHAR 从Hive0.13.0开始支持 VARCHAR 从Hive0.12.0开始支持 DATE 从Hive0.12.0开始支持
2 Hive表结构中的数据类型与MySQL对应列有如下关系
MySQL(bigint) --> Hive(bigint) MySQL(tinyint) --> Hive(tinyint) MySQL(int) --> Hive(int) MySQL(double) --> Hive(double) MySQL(bit) --> Hive(boolean) MySQL(varchar) --> Hive(string) MySQL(decimal) --> Hive(decimal) MySQL(date/timestamp) --> Hive(date) MySQL(timestamp) --> Hive(timestamp) 注： Mysql[decimal(12,2)]-->Hive[decimal]会导致导入后小数丢失
3 数据类型转化关系表
Hive DDL操作
1 存储格式：
压缩表和非压缩表
- textfile是Hive建表时默认使用的存储格式，数据不做压缩，本质上textfile就是以文本的形式将数据存放在hdfs中。
- orcfile 列式存储格式，查询更快。
2 表类型：
2.1 分区表和非分区表，分桶表
一个表可以拥有一个或者多个分区，每个分区以文件夹的形式单独存在表文件夹的目录下
2.2 外部表和内部表
- 管理表 默认创建的表都是管理表，也被称为内部表，删除管理表时，Hive 也会删除这个表中数据
- 外部表 删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉
3 hive -f/-e操作
hive -f 后面指定的是一个文件，然后文件里面直接写sql
hive -e 后面是直接用双引号拼接hivesql，然后就可以执行命令
4 建库
4.1 创建库
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name [COMMENT database_comment] //关于数据块的描述 [LOCATION hdfs_path] //指定数据库在HDFS上的存储位置 [WITH DBPROPERTIES (property_name=property_value, ...)]; //指定数据块属性
4.2 查看有哪些数据库
dfs -ls /hive/warehouse/; show databases;
4.3 显示数据库的详细属性信息
desc database [extended] dbname;
4.4 查看正在使用哪个库
select current_database();
4.5 查看创建库的详细语句
show create database t3;
4.6 删库（默认情况下，hive 不允许删除包含表的数据库， 使用 cascade 关键字）
drop database if exists dbname; drop database if exists dbname cascade;
4.7 切换库
use database_name;
5 建表
5.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 TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXIST 选项来忽略这个异常 •EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION） •LIKE 允许用户复制现有的表结构，但是不复制数据 •COMMENT可以为表与字段增加描述 •PARTITIONED BY 指定分区 •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 确定表的具体的列的数据。 •STORED AS SEQUENCEFILE //序列化文件 | TEXTFILE //普通的文本文件格式 | RCFILE //行列存储相结合的文件 | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname //自定义文件格式 如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCE 。 •LOCATION指定表在HDFS的存储路径
5.2 创建内部表
create table student( id int, name string ) row format delimited fields terminated by "\001" stored as textfile;
5.3 创建外部表
create external table student_ext( id int, name string ) row format delimited fields terminated by "," location "/hive/student";
5.4 创建分区外部表
如果不指定数据库，hive会把表创建在default数据库下。分区表现为这张表的数据存储目录下的一个子目录，如果是分区表。那么数据文件一定要存储在某个分区中，而不能直接存储在表中。
create external table tmp.table_add_column_test( a string comment '原始数据1', b string comment '原始数据2' ) comment '表注释' partitioned by (dt string) row format delimited fields terminated by '\001' stored as textfile;
5.5 创建分桶外部表
create external table student_bck( id int, name string ) clustered by (id) sorted by (id asc, name desc) into 4 buckets row format delimited fields terminated by "\001" location "/hive/student_bck";
5.6 使用CTAS创建表（从一个查询SQL的结果来创建一个表进行存储）
create table student_ctas as select * from student where id < 95012;
5.7 复制表结构（加external关键字，复制成外部表）
create external table student_copy like student;
5.8 查看当前使用的数据库中有哪些表
dfs -ls /hive/warehouse/edw.db; show tables;
5.9 查看非当前使用的数据库中有哪些表
show tables in myhive;
5.10 查看数据库中以xxx开头的表
show tables like 'tablename*';
5.11 查看表的信息
desc tablename;
5.12 查看表的详细信息
desc extended tablename;（格式不友好）
desc formatted student;（格式友好）
5.13 查看分区信息
show partitions tablename;
5.14 查看表的详细建表语句
show create table tablename;
6 修改表
6.1 内部表和外部表转换
内部转外部：alter table tableA set TBLPROPERTIES('EXTERNAL'='true'); 外部转内部：alter table tableA set TBLPROPERTIES('EXTERNAL'='false');
6.2 修改表名（需先把表改为内部表然后在改名，外部表改名后相应路径不变，会导致数据问题）
alter table tablenamerename to new_tablename;
6.3 修改字段类型（注意hive字段类型转换规则，范围小的数据类型可以转换为范围更大的类型，但不能逆向转，比如 int可以转换为string,但string无法转为int）
alter table new_tablename change name new_name string;
6.4 增加字段（添加列默认在列的末尾添加新列，但在分区列之前。默认模式为RESTRICT（即不修改元数据），cascade则同步修改元数据，这样才会重新刷新数据时添加的字段才会有值，不然刷新数据新添加的字段以前的数据都为null）
alter table tmp.tablename add columns ( last_lesson_teacher_no string comment '编号', teaching_department string comment '教学部', assistant_job_number string comment '工号' ) cascade;
6.5 对字段排序（hive可以对已存在的表字段进行排序，但HDFS文件内容并没有变化,内部也不行）
alter table tmp.table_add_column_test change column added_column added_column string after a cascade;
6.6 删除一个字段
不支持
6.7 替换所有字段
alter table new_student replace columns (id int, name string, address string);
6.8 增加分区（未分区表增加分区时，只能删除重建表结构，然后增加分区，把数据移到分区目录下，记得把表转为外部表；分区表增加分区时，直接增加分区即可）
增加一个分区：
alter table ods.tablename add partition(dt='20190101');
增加多个分区：
alter table student_ptn add partition(city="chongqing2") partition(city="chongqing3") partition(city="chongqing4");
6.9 修改分区（修改已经指定好的分区的数据存储目录，此时原先的分区文件夹仍存在，但是在往分区添加数据时，只会添加到新的分区目录）
alter table student_ptn partition (city='beijing') set location '/student_ptn_beijing';
6.10 删除分区（如果是外表,需要删除hdfs目录数据）
alter table ods.sc_career_window drop partition(dt='20190101');
7 删除表
7.1 删除表(如果是外表,需要删除hdfs目录数据 dfs -rm -r /hive/warehouse/ods.db/tablename)
drop table tablename;
7.2 清空表
truncate table student_ptn; insert overwrite table t_table1 select * from t_table1 where 1=0;
7.3 删除符合条件的数据（查询出数据重新插入表或者新表，分区表也可以）
insert overwrite table t_table1 select * from t_table1 where XXXX;
8 修复表
8.1 删除表结构后在重建表，可以对表进行数据修复(如果是未分区表转分区表需先把数据转到分区中再修复)
msck repair table ods.tablename;
Hive DML操作
1 Load
load data [local] inpath 'filepath' [overwrite] into table tablename [partition (partcol1=val1, partcol2=val2 ...)] load data inpath '/p/view_user_uuid/*' into table table partition (dt='2019-04-28');
注意：
① load 命令会将 filepath 中的文件复制到目标文件系统中。目标文件系统由表的位置属性决定。被复制的数据文件移动到表的数据对应的位置。
② 如果没有指定 LOCAL 关键字，filepath 指向的是一个完整的 URI，hive会直接使用这个 URI。
③ 如果使用了 overwrite 关键字，则目标表（或者分区）中的内容会被删除，然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。
④ 如果目标表（分区）已经有一个文件，并且文件名和 filepath 中的文件名冲突，那么现有的文件会被新文件所替代。
2 Insert
Hive 中 insert 主要是结合 select 查询语句使用，将查询结果插入到表中。
注意：
① 需要保证查询结果列的数目和需要插入数据表格的列数目一致.
② 如果查询出来的数据类型和插入表格对应的列数据类型不一致，将会进行转换，但是不能保证转换一定成功，转换失败的数据将会为 NULL。
③ 可以将一个表查询出来的数据插入到原表中, 结果相当于自我复制了一份数据。
2.1 覆盖(当select为空时，不覆盖以前数据) insert overwrite table tmp.trial_lesson_success partition(dt='20190419') 2.2 新增 insert into table tmp.trial_lesson_success partition(dt='20190419') 2.3 保存到本地和HDFS insert overwrite local directory '/root/123456' select * from t_p; insert overwrite directory '/aaa/test' select * from t_p;(谨慎使用 insert overwrite directory '/hive/warehouse/'这一条sql就能干崩掉hive）
Hive DQL操作
1 Select
select [all | distinct] select_expr, select_expr, ... from table_reference join table_other on expr [where where_condition] [group by col_list [having condition]] [cluster by col_list | [distribute by col_list] [sort by| order by col_list] ] [limit number] 1.1 order by 会对输入做全局排序，因此只有一个 reducer，会导致当输入规模较大时，需要较长的计算时间。 1.2 sort by 不是全局排序，其在数据进入 reducer 前完成排序。因此，如果用 sort by 进行排序，并且设置 mapred.reduce.tasks>1，则 sort by 只保证每个 reducer 的输出有序，不保证全局有序。 1.3 distribute by(字段)根据指定字段将数据分到不同的 reducer，分发算法是 hash 散列。 1.4 Cluster by(字段) 除了具有 Distribute by 的功能外，还会对该字段进行排序。 1.5 如果 distribute 和 sort 的字段是同一个时，此时，cluster by = distribute by + sort by
2 Join
Hive 中除了支持和传统数据库中一样的内关联、左关联、右关联、全关联，还支持 left semi join 和 cross join，但这两种 JOIN 类型也可以用前面的代替。
Hive 支持等值连接 （a.id = b.id ）, 不支持非等值( (a.id>b.id) ) 的连接，因为非等值连接非常难转化到 map/reduce 任务。另外，Hive 支持多 2 个以上表之间的 join。
2.1 join 时，每次 map/reduce 任务的逻辑 reducer 会缓存 join 序列中除了最后一个表的所有表的记录，再通过最后一个表将结果序列化到文件系统。这一实现有助于在 reduce 端减少内存的使用量。实践中，应该把最大的那个表写在最后（否则会因为缓存浪费大量内存）。 2.2 left ，right 和 full outer 关键字用于处理 join 中空记录的情况 select a.val, b.val from a left outer join b on (a.key=b.key)对应所有 a 表中的记录都有一条记录输出。输出的结果应该是 a.val, b.val，当a.key=b.key 时，而当 b.key 中找不到等值的 a.key 记录时也会输出:a.val, null所以 a 表中的所有记录都被保留了；“a right outer join b”会保留所有 b 表的记录。 2.3 join 发生在 where 子句 之前 如果你想限制 join 的输出，应该在 where 子句中写过滤条件——或是在 join 子句中写。 select a.val, b.val from a left outer join b on (a.key=b.key) where a.ds='2009-07-07' and b.ds='2009-07-07' 这会 join a 表到 b 表（outer join），列出 a.val 和 b.val 的记录。where 从句中可以使用其他列作为过滤条件。但是，如前所述，如果 b 表中找不到对应 a 表的记录，b 表的所有列都会列出null，包括 ds 列。也就是说，join 会过滤 b 表中不能找到匹配 a 表 join key 的所有记录。这样的话，left outer 就使得查询结果与 where 子句无关了。解决的办法是在 outer join 时使用以下语法： select a.val, b.val from a left outer join b on (a.key=b.key and b.ds='2009-07-07' and a.ds='2009-07-07') 2.4 hive中的特别join select * from a left semi join b on a.id = b.id; 等同于 select a.id,a.name from a where a.id in (select b.id from b); 在hive中效率极低 select a.id,a.name from a join b on (a.id = b.id); select * from a inner join b on a.id=b.id;
3 查看函数功能
desc function extended datediff;
动态分区
1 静态分区、动态分区、单分区和复合分区
hive分区可以方便快速定位，查找( 设置分区，可以直接定位到hdfs上相应的文件目录下，避免全表扫描)。
hive分区可以分为静态分区、动态分区，另外静动态分区又都可以分为复合分区和单分区表。
2 Hive分区的概念与传统关系型数据库分区不同
传统数据库的分区方式：就oracle而言，分区独立存在于段里，里面存储真实的数据，在数据进行插入的时候自动分配分区。
Hive的分区方式：由于Hive实际是存储在HDFS上的抽象，Hive的一个分区名对应一个目录名，子分区名就是子目录名，并不是一个实际字段。所以我们在插入数据的时候指定分区，就是新建一个目录或者子目录，或者在原来目录的基础上来添加数据。对于hive分区而言，可以分为静态分区和动态分区这两个类。
3 静态分区表
新建表时定义的分区顺序，决定了文件目录顺序（谁是父目录谁是子目录），正因为有了这个层级关系，当我们查询所有year=1024的时候，2014以下的所有日期下的数据都会被查出来。如果只查询月份分区，但父目录都有该日期的数据，那么Hive会对输入路径进行修剪，从而只扫描日期分区，性别分区不作过滤（即查询结果包含了所有性别）。
4 动态分区表
在使用静态分区的时候，我们首先要知道有什么分区类型，然后每个分区来进行数据的加载，这个操作过程比较麻烦；而动态分区不会有这些不必要的操作，动态分区可以根据查询得到的数据动态地分配到分区中去，动态分区与静态分区最大的区别是不指定分区目录，由系统自己进行过选择。
动态分区模式可以分为严格模式(strict)和非严格模式(non-strict),二者的区别是：严格模式在进行插入的时候至少指定一个静态分区，而非严格模式在进行插入的时候可以不指定静态分区
5 设置动态分区相关的参数
set hive.exec.dynamic.partition=true //使用动态分区 set hive.exec.dynamic.partition.mode=nonstrick;//无限制模式，如果模式是strict，则必须有一个静态分区，且放在最前面 set hive.exec.max.dynamic.partitions.pernode=10000;//每个节点生成动态分区的最大个数 set hive.exec.max.dynamic.partitions=100000;//生成动态分区的最大个数 set hive.exec.max.created.files=150000;//一个任务最多可以创建的文件数目 set dfs.datanode.max.xcievers=8192;//限定一次最多打开的文件数 set hive.merge.mapfiles=true; //map端的结果进行合并 set mapred.reduce.tasks =20000; //设置reduce task个数
原文链接：
1. 大数据利器hive https://mp.weixin.qq.com/s/ZhW4n7hldadiFjCGHsz88A
