关注公众号，回复“资料全集”，不定期最新大数据业内资讯。
❤：在这里跟我一起学习技术、职场、人生、原理、健身、摄影、生活等知识吧！
❤： 欢迎点个关注一起学习，进步充实人生。
“ 大数据时代，熟练使用SQL是基础中的基础，而Hive 定义了简单的类 SQL 查询语言，称为 HQL，它允许熟悉 SQL 的用户快速、简便查询海量数据。 ”
01 Hive是什么
Hive是基于hadoop的一个数仓分析工具，hive可以将hdfs上存储的结构化的数据，映射成一张表，然后让用户写HQL(类SQL)来分析数据。
举例：
tel up down
1383838438 1345 1567
1383838439 5345 1567
1383838440 1241 16577
1383838441 3453 15757
1383838434 35355 1567567
按照手机号 分组，统计每个手机号的总流量：
select tel,sum(up+down) from test group by tel;
Hive的本质其实就是hadoop的一个客户端，Hive底层不存储任何数据，Hive表的数据存在hdfs上，Hive表的元数据存在关系型数据库中，默认是derby，但是我们不一般不用默认的derby来存，一般都会修改为mysql，因为这样可以方便多用户同时操作Hive客户端。
元数据：描述数据的数据。
Hive其实就是将用户写的HQL，给翻译成对应的mapreduce模板，然后执行这些mr程序。
Hive底层执行引擎其实就是MapReduce，mr运行在yarn上。
02 hive的优缺点
第二部分的正文内容从这里开始。
优点：操作简单，采用类sql的语法分析数据，门槛低，大大的降低了大数据分析的难度，通用性高。
缺点：不够灵活，机翻粒度比较粗，调优困难。因为底层执行引擎还是mr（调优手段之一就是把引擎更改为spark），所以延迟较高，不能像关系型数据库那样，立马返回结果。
并且底层存储是hdfs，不支持随机写，只能追加，所以hive不支持行级别的更新和删除（delete 和 update），所以一般会执行新表覆盖临时表的操作。
03 hive的架构原理
（常问面试题）
客户端：命令行客户端，jdbc客户端。
数据存储：hdfs
底层执行引擎：mr
元数据库：hive将元数据默认存在derby中，我们一般在安装hive的时候，会修改成mysql。
dirver四个器：
解析器：将hql语句转换成AST抽象语法树，解析sql是否有误
编译器：将解析后的hql编译成逻辑执行计划，暂时不执行
优化器：对逻辑计划进行优化，调优
执行器：将优化后的逻辑计划执行，其实就是翻译成对应的mr程序，在yarn上运行
03 hive的架构原理
hive不是数据库，不是数据库，不是数据库。
hive除了查询语言HQL跟SQL很像之外，别的跟数据库再也没有半点相似可言
数据更新 数据规模 执行延迟 底层引擎 数据存储
04 DDL数据定义语言
4.1 库的DDL
1 创建数据库
CREATE DATABASE [IF NOT EXISTS] database_name --指定数据库名称
[COMMENT database_comment] --指定数据库描述
[LOCATION hdfs_path] --指定创建的数据库在hdfs上存储的路径
[WITH DBPROPERTIES (property_name=property_value, ...)]; --指定库的一些属性
案例实操
create database if not exists db_hive
comment "this is my first db"
with dbproperties ("name"="db_hive","owner"="atguigu");
create database if not exists db_hive;
--创建数据库，并指定在hdfs上的路径
create database if not exists db_hive2
location '/db_hive2';
2 查询数据库
show databases；
3 查看数据库详情
--简单查看
desc database 数据库名;
--详细查看 （详细查看可以看到库的属性信息，简单查看看不到）
desc database extended 数据库名;
4 切换数据库
use 数据库名;
5修改数据库（基本没人用）
用户可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。
alter database db_hive set dbproperties('createtime'='20200624'); --指定表的属性 设置一个创建时间
6删除数据库
注意：删除掉数据库以后，hdfs上对应的目录也会删除，谨慎操作。
drop database 数据库名 cascade;
--如果数据库不为空，可以在最后加上cascade强制删除
drop database 数据库名 cascade;
--为了更严谨，我们可以在删除之前判断数据库是否存在
drop database if exists 数据库名 cascade;
4.2表的DDL
1 创建表 看着多，真正创建时没用几个；一般只有创建外部表才会指定location，内部表指定没有意义
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name --指定表名 【external 外部表/内部表】
[(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 DELIMITED --指定hive表在hdfs上存储的原始数据的格式
[FIELDS TERMINATED BY char] --每行数据中字段的分隔符 ascII码表的第一个字符 ^A
[COLLECTION ITEMS TERMINATED BY char] --集合元素分隔符 ascII码表的第二个字符 ^B
[MAP KEYS TERMINATED BY char] --map集合中 key 和 value 的分隔符 ascII码表的第三个字符 ^C
[LINES TERMINATED BY char] --每行数据的分隔符 默认值：'\\n'
]
[STORED AS file_format] --指定hive的数据在hdfs上存储的格式，可以写一些压缩格式
[LOCATION hdfs_path] --指定hive数据在hdfs上存储的路径 默认值 /user/hive/warehouse/数据库名
[TBLPROPERTIES (property_name=property_value, ...)] --指定表的属性
[AS select_statement] --按照as后面的查询语句的结果来创建表，复制表结构以及表数据
[LIKE table_name] --按照like后面的表结构来创建表，只复制表结构，不复制表数据
字段解释说明：
（1）CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。
（2）EXTERNAL关键字可以让用户创建一个外部表，在建表的同时可以指定一个指向实际数据的路径（LOCATION）。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
（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的简称， hive使用Serde进行行对象的序列与反序列化
（8）STORED AS指定存储文件格式（这个面试被问到过）
存储格式：
- 行存。Textfile
- 列存。Orc、parquet
压缩：lzo和snappy。
常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）
如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。
（9）LOCATION ：指定表在HDFS上的存储位置。
（10）AS：后跟查询语句，根据查询结果创建表。
（11）LIKE允许用户复制现有的表结构，但是不复制数据。
2 内部表和外部表
管理表（内部表）：hive控制着这个数据的生命周期，如果删除一个管理表，hdfs上存储的数据也跟着一起删除。
Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如，/user/hive/warehouse)所定义的目录的子目录下。当我们删除一个内部表时，Hive也会删除这个表中数据。所以一般我们创建管理表时，一般不会指定location表的存储路径。管理表不适合和其他工具共享数据。
外部表：hive不完全掌控外部表的数据的生命周期，删除外部表，只删除hive表的元数据，不会删除掉hdfs上存储的数据
一般外部表都是先有的hdfs上的数据，然后我们创建一个外部表，手动指定这个外部表的存储路径
3 创建内部表
create table student(
id int,name string
)
row format delimited fields terminated by '\\\\t';
create table student2(
id int,name string
);
--根据AS select语句查询结构创建表,复制表结构，复制表数据
create table student3 as select * from student;
--根据like 创建表，只复制表结构，不复制表数据
create table student4 like student;
查看表信息
--简单查看表信息
desc 表名;
--详细查看表信息
desc formatted 表名;
删除内部表
注意：删除管理表同时会删除hdfs上对应目录的数据，谨慎操作，数据无价
drop table student3;
4 创建外部表 只是通过建一个元数据来管理数据，想删删不了
查询语句先到mysql读取元数据信息，然后到hdfs上的目录里看有没有数据。
Hive的工作机制，一个原数据，一个元数据。不管时间先后，两个是独立的。
外部表更安全，内部表用的少，它不安全。删了外部表，还得去hdfs上删除数据。见得绝大部分是外部表。内部表一删，就把真的数据给删除了。
注意：外部表创建时要加external，外部表的好处就是，删除表的时候，只删除描述表的元数据信息，不删hdfs上存储的数据，更安全。
内部表和外部表的使用场景
每天将收集到的网站日志定期流入HDFS文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过SELECT+INSERT进入内部表
建表语句
create external table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\\\\t'
location '/company/dept';
create external table if not exists emp(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int)
row format delimited fields terminated by '\\\\t'
location '/company/emp';
5 外部表和内部表的转换
通过设置表属性“EXTERNAL”来控制表是外部表还是内部表
TRUE 是 外部表 FALSE 是内部表
alter table student set tblproperties('EXTERNAL'='TRUE/FALSE');
6修改表
重名表 注意：重命名表会一起修改hdfs上对应的目录名，前提这个表必须是管理表（内部表），并且创建这个管理表时没有自定义location
ALTER TABLE table_name RENAME TO new_table_name
更新列
更新列，列名可以随意修改，列的类型只能小改大，不能大改小（遵循自动转换规则）
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
增加列
ALTER TABLE table_name ADD COLUMNS (col_name data_type [COMMENT col_comment], ...)
替换列
ALTER TABLE table_name REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
7清空表 表还要，里面数据不要了
注意：只能truncate管理表，外部表不能truncate。truncate的本质其实就是删除hdfs上对应路径的数据。
truncate table 表名;
05 DDL数据定义语言
5.1 导入 5种方式
1 通过load命令装载数据
将数据从文件中导入到表中，用load。
load就是剪切操作，不需要提交到yarn上跑MapReduce任务，很快；并且不涉及到资源分配的队列问题。
load data [local] inpath '数据的path' [overwrite] into table student [partition (partcol1=val1,…)];
（1）load data:表示加载数据
（2）local:表示从本地加载数据到hive表；否则从HDFS加载数据到hive表
（3）inpath:表示加载数据的路径
（4）overwrite:表示覆盖表中已有数据，否则表示追加
（5）into table:表示加载到哪张表
（6）student:表示具体的表
（7）partition:表示上传到指定分区
案例实操：
（0）创建一张表
hive (default)> create table student(id int, name string) row format delimited fields terminated by '\\t';
（1）加载本地的文件到hive 本质上其实就是将本地的文件put剪切到hdfs对应的表目录
hive (default)> load data local inpath '/opt/module/hive/datas/student.txt' into table student;
（2.1）加载HDFS路径中的文件到hive中 本质是将hdfs上路径的数据剪切到对应的表的hdfs路径
上传文件到HDFS
hive (default)> dfs -put /opt/module/hive/datas/student.txt /user/atguigu;
加载HDFS上数据，导入完成后去hdfs查看文件是否还存在
hive (default)> **load data inpath '/user/atguigu/student.txt' into table student;**
（2.2）加载数据覆盖表中已有的数据
注意:加上关键字overwrite 就是覆盖导入，不加的话，默认是追加导入
hive (default)> load data inpath '/user/atguigu/student.txt' overwrite into table student;
2 通过查询语句向表中插入数据insert
此方式一般没人用，因为效率太低
1）创建一张表
hive (default)> create table student2(id int, name string) row format delimited fields terminated by '\\t';
2）基本模式插入数据 insert into 是追加 insert overwrite是覆盖
hive (default)> insert into table student2 values(1,'wangwu'),(2,'zhaoliu');
3）根据查询结果插入数据
hive (default)> insert overwrite table student2
select id, name from student where id < 1006;
insert into：以追加数据的方式插入到表或分区，原有数据不会删除
insert overwrite：会覆盖表中已存在的数据
注意1：insert不支持插入部分字段，并且后边跟select语句时，select之前不能加as，加了as会报错，一定要跟下面的as select区分开。
注意2：通过inset插入数据，数据格式和列的数量要一致才可以。
3 查询语句中创建表并加载数据 as select
注意：建表语句后跟select语句时，as不能省略，跟上面的insert into刚好相反
根据查询结果创建表（查询的结果会添加到新创建的表中）
create table if not exists student3
as select id, name from student;
4 创建表时通过Location指定加载数据路径 (使用很少)
提前把数据传到hdfs上，然后创建表的时候，指定表的位置为数据的路径
注意 location后面一定要给一个目录，不能直接给文件路径，非文件夹不可。
1）上传数据到hdfs上
hive (default)> dfs -mkdir /student;
hive (default)> dfs -put /opt/module/hive/datas/student.txt /student;
2）创建表，并指定在hdfs上的位置
hive (default)> create external table if not exists student5(
id int, name string
)
row format delimited fields terminated by '\\t'
location '/student';
3）查询数据
hive (default)> select * from student5;
create table student5(
id int,name string
)
row format delimited fields terminated by '\\\\t'
location '/student';
5 import导入
注意：使用import导入之前，得使用export导出数据，并且因为export会把数据和元数据一起导出，所以我们使用import导入的时候，表不能存在，否则会元数据冲突，报错。
hive (default)> import table student2(这个表是新创建的表) from
'/user/hive/warehouse/export/student';
5.2 数据导出
5.2.1 insert 导出
1 将查询的结果导出到本地
insert overwrite local directory '/opt/module/hive/datas/export/student'
select * from student;
2 将查询的结果格式化导出到本地
hive(default)>insert overwrite local directory '/opt/module/hive/datas/export/student1'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\\t' select * from student;
注意：insert 导出，导出的目录不用自己提前创建，hive会帮我们自动创建。因为insert导出，后面只能跟overwrite，所以我们给路径的时候，一定要具体，防止hive误删重要文件。这个步骤很重要，切勿大意。
这个导出路径可以不存在，hive会帮我们创建路径
3 将查询的结果导出到HDFS上(没有local)
hive (default)> insert overwrite directory '/user/atguigu/student2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\\t'
select * from student;
5.2.2 Hadoop命令导出到本地
hive (default)> dfs -get /user/hive/warehouse/student/student.txt
/opt/module/hive/datas/export/student3.txt;
5.2.3hive shell 命令导出
基本语法：（hive -f/-e 执行语句或者脚本 > file）
[atguigu@hadoop102 hive]$ bin/hive -e 'select * from default.student;' > /opt/module/hive/datas/export/student4.txt;
5.2.4 Export导出到HDFS上 只能到处到hdfs不能到本地
注意：并且会将元数据和数据一起导出。
hive (default)> export table default.student to '/user/hive/warehouse/export/student';
export和import主要用于两个Hadoop平台集群之间Hive表迁移，不能直接导出的本地。
5.2.5 Sqoop导出 sql + Hadoop
后续介绍什么是sqoop，如何进行导出。
未完待续。。。
