文章作者邮箱：yugongshiye@sina.cn              地址：广东惠州

 ▲ 本章节目的

⚪ 掌握HIve的基本SQL语句和注意问题；

⚪ 掌握HIve的表结构；

⚪ 掌握HIve的数据类型；

⚪ 掌握HIve的基础函数和窗口函数；

一、基本SQL

1. SQL的执行方式

1. 通过hive -e的方式来执行指定的SQL，例如hive -e 'create database demo;'。

2. 通过hive -f的方式来执行指定的SQL脚本，例如hive -f test.sql。

3. 进入Hive的命令行里来执行指定的SQL。

2. 注意问题

1. 如果不指定，那么Hive默认将数据放在HDFS的/user/hive/warehouse目录下。

2. 在Hive中，每一个database都对应了一个单独的目录。

3. 在Hive启动的时候，自带一个default库。如果在建表的时候没有指定，那么默认也是将表放在default库下。

4. alter database可以修改指定库的属性，但是不能修改库的库名以及存储位置。

5. 在Hive中，没有主键的概念，不支持主键。

6. 在Hive中，每一个table也对应了一个单独的目录。

7. 在Hive中，需要在建表的时候指定字段之间的间隔符号。

8. insert into表示向表中追加数据；insert overwrite表示将表中的清空之后再添加当前的数据(覆盖)。

9. 需要注意的是，Hive中的数据会以文件的形式落地到HDFS上，在Hive的默认文件格式(textfile - 文本)下，不支持修改(update和delete)操作。如果需要Hive支持修改操作，那么需要在建表的时候指定文件格式为orc格式。但是在实际开发中，因为一般利用Hive存储历史数据，所以很少或者根本不对Hive中的数据进行修改，因此一般不适用orc格式。另外，orc格式虽然支持update和delete操作，但是效率非常低。

3. 基本SQL

SQL	解释
create database demo;	创建demo库
create database if not exists demo4;	如果demo4库不存在，则创建
create database demo5 location '/demo5';	创建demo5库，同时指定存储位置
show databases;	查看所有的库
show databases like 'demo*';	查看demo开头的库
desc database demo;	描述demo库
desc database extended demo;	描述demo库的详细信息
use demo;	使用demo库
alter database demo set dbproperties ('date'='2020-12-25');	修改demo库的属性
drop database demo5;	删除demo5库
drop database if exists demo4;	如果demo4库存在，则删除
drop database demo3 cascade;	强制删除demo3库及其中的表
create table person (id int, name string, age int);	建立person表，包含id，name，age三个字段
insert into table person values(1, 'colin', 19);	插入数据
select * from person;	查询数据
load data local inpath '/home/hivedemo/person.txt' into table person;	从本地加载文件到Hive表中
drop table person;	删除表
create table person (id int, name string, age int) row format delimited fields terminated by ' ';	建表，指定字段之间的间隔符号为空格
create table p2 like person;	创建和person表结构一致的p2表
describe p2; 或者 desc p2;	描述p2
show tables;	查看所有的表
insert into table p2 select * from person where age >= 18;	从person中查询数据，将age>=18的数据放到p2表中
create table if not exists p3 like person;	如果p3表不存在，则创建和person结构一致的p3表
from person insert overwrite table p2 select * where age >= 18 insert into table p3 select * where id < 5;	从person表中查询数据，然后将查询出来的age>=18的数据覆盖到p2表中，同时将id<5的数据追加到p3表中
create table if not exists p4 as select * from person where age < 18;	创建p4表，同时在建表的时候，将person表中age<18的数据放进去
insert overwrite local directory '/home/hivedata' row format delimited fields terminated by '\t' select * from person where age >= 18;	将person表中age>=18的数据查询出来放到本地磁盘的/home/hivedata目录下
insert overwrite directory '/person' row format delimited fields terminated by ',' select * from person where id >= 6;	将person表中id>=6的数据查询出来放到HDFS的地址路径下
alter table person rename to p1;	重命名表

二、基本表结构

1. 内部表和外部表

1. 如果在Hive中手动建表手动添加数据(包括insert和load方式)，那么这种表称之为内部表。

2. 如果在Hive中手动建表来管理HDFS上已经存在的数据，那么这种表称之为外部表。

3. 相对而言，在工程或者项目建立的初期，一般会建立外部表来管理HDFS上已经存在的数据；但是当外部表建立之后，不代表数据能够直接处理使用，还需要对数据进行抽取整理等清洗操作，来建立能够实际使用的内部表。

4. 建立外部表：

create external table orders(orderid int, orderdate string, productid int, num int) row format delimited fields terminated by ' ' location '/orders';

5. 查看表的详细信息：

desc extended orders;

#或者

desc formatted orders;

#在其中寻找Table Type:EXTERNAL_TABLE

6. 在Hive中，删除内部表的时候，这个表对应的目录也会从HDFS上删除；删除外部表的时候，只是删除了元数据，而这个表对应的目录没有从HDFS上移除。

2. 分区表

1. 分区表的作用通常是对数据来进行分类。

2. 建立分区表：

create table cities (id int, name string) partitioned by (province string) row format delimited fields terminated by ' ' ;

3. 加载数据：

load data local inpath '/home/hivedemo/hebei.txt' into table cities partition(province='hebei');

load data local inpath '/home/hivedemo/jiangsu.txt' into table cities partition(province='jiangsu');

4. 在Hive中，每一个分区对应一个单独的目录。

5. 如果在查询数据的时候，指定了分区字段，那么分区表的查询效率就会高于未分区的表；如果在查询数据的时候，进行了跨分区查询，那么此时未分区表的查询效率就要高于分区表。

6. 删除分区：

alter table cities drop partition(province = 'henan');

7. 手动添加分区：

alter table cities add partition (province='henan') location '/user/hive/warehouse/demo.db/cities/province=henan';

8. 修复表：

msck repair table cities;

9. 修改分区名：

alter table cities partition(province='__HIVE_DEFAULT_PARTITION__') rename to partition (province='sichuan');

10. 在Hive的分区表中，分区字段在原始数据中并不存在，而是在加载数据的时候来手动指定。如果分区字段在原始数据中存在，那么需要考虑动态分区。