Hive入门学习

Apache Hive

1、Hive简介

1.1、什么是Hive

hive是基于Hadoop的一个数据仓库工具，用来进行数据提取、转化、加载，这是一种可以存储、查询和分析存储在Hadoop中的大规模数据的机制。hive数据仓库工具能将结构化的数据文件映射为一张数据库表，并提供SQL查询功能，能将SQL语句转变成MapReduce任务来执行。
主要用途：用来做离线数据分析，比直接用MR开发效率更高。

1.2、Hive的好处

现在人工成本和学习成本高。对于一个IT人员SQL是一个基本。而且Hive的语法采用的是类SQL，这样就提高了快速开发的能力。避免了去写MR,同时，对于扩展功能以及项目迭代都较为方便。

2、Hive与Hadoop的关系

Hive利用HDFS存储数据，利用HiveSQL执行MapReduce查询分析数据。

3、Hive与传统数据库对比

hive因为是基于Hadoop的HDFS存储，所以注定了是离线的分析数据。但是我们又是使用HSQL来执行。所以肯定很多人会想他与传统的数据库是什么关系或者异同。
查询语言：HQL 传统数据库是SQL
数据存储：HDFS

	Hive	传统数据库
查询语言	HQL	SQL
数据存储	HDFS	Local FS
执行	MR	Excutor
执行延迟	高	低
处理数据量	大	小
索引	无	有复杂的索引

4 、Hive 数据模型

Hive中数据都存储于HDFS上，无特定格式。我们在创建表时需要特定的分隔符，就可以映射成功。
Hive模型：
db: 在HDFS上有一个专门的目录文件夹。hive.metastore.warehouse.dir
table: 其实就在HDFS上的house目录的下一级。
external table: 在HDFS任意目录下。但是需要在创表时指定。
partition: 分区，主要是在table目录的下一个目录。
bucket: 分桶，主要是在table同一级目录，被hash为多个文件。

5、Hive 基本操作

1、DDL 操作

1.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]
注释：

1. EXTERNAL:可以让创建的表为外部表，同时搭配LOCATION一起使用指定数据的路径。
2. PARTITIONED BY ：指定分区，注意不能是创建表字段内的字段。
3. CLUSTERED BY：分桶字段，是为创建表字段内的字段。可以减低join笛卡尔积。
4. SORTED BY:字段排序
5. ROW FORMAT：默认分隔符是‘\001’，我们也可以自己指定分隔符。如：‘\t’，’,'等。
6. STORED AS：如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。

注意：
1、内部表与外部表：创建内部表时会将数据移动到数据仓库指定的路径，创建外部表只需一个路径，不会对数据的位置做改变。删除表的时候，，内部表的元数据和数据会被一起删除，而外部表只是删除元数据不删除数据。
2、LIKE : 它和传统数据库的like不同，Hive的like是用来复制现有的表结构不复制数据。

create table a_table like b_table;

3、分隔符的指定：如果我们在创建表的时候没有指定分隔符，加载数据的时候就需要指定为‘\001’。如果不指定分隔符，加载的数据全部为‘null’。
4、分区表：普通的hiveSQL查询会全表扫描，有时候我们只需要扫描部分，这就是分区表的作用。分区表在Hive中他是每个分区都是一个文件夹，数据都存放在文件夹目录里。只表示分区（不区分大小写）。
5、分桶表：他其实是对表的更加细致化的分。他是对某一列进行细分。采用的是hash除以桶数取余。主要有两个好处：减少join的笛卡尔积；提高查询效率。

1.2、修改表

（1）增加分区：

ALTER TABLE a_table ADD PARTITION (dt='20200401') location
'/user/hadoop/warehouse/a_table/dt=20200401'; //一次添加一个分区

ALTER TABLE b_table ADD PARTITION (dt='2020-03-31', country='cn') location
'/path/to/cn/part200331' PARTITION (dt='2008-04-01', country='cn') location
'/path/to/cn/part200401'; //一次添加多个分区

（2）删除分区：

ALTER TABLE a_table DROP IF EXISTS PARTITION (dt='2020-04-01');
ALTER TABLE a_table DROP IF EXISTS PARTITION (dt='020-03-31', country='cn');

（3）修改分区：

ALTER TABLE a_table PARTITION (dt='2020-03-31') RENAME TO PARTITION (dt='20200401');

（4）添加列：
a_table(a int,b int, c int);

ALTER TABLE a_table CHANGE a a1 int; //修改 a 字段名

ALTER TABLE a_table CHANGE a a1 STRING AFTER b;//a_table(b int , a1 string , c int)

ALTER TABLE a_table CHANGE b b1 INT FIRST;//a_table(b1 int, a int, c int)

（5）表重命名:

ALTER TABLE a_table RENAME TO new_a_table;

1.3、显示命令

show tables;//当前数据库所有表
show databases;//所有数据库
show partitions table_name;//表分区信息
show functions;//当前支持的方法
desc extended table_name;//查看表信息
describe database database_name;//查看数据库信息

2、DML操作

2.1、Load

	将数据加载到表中，Hive不会进行任何转换。`最多是移动和复制。`

语法：
LOAD DATA [LOCAL] INPATH ‘filepath’ [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 …)]
注释：
1、filepath:
相对路径，例如：demo/data
绝对路径，例如：/user/hive/demo/data
完整 URI， 例如： hdfs://namenode:9000/user/hive/demo/data
2、local:
指定了local代表是linux服务器上查找文件路径。如果没有指定，Hive会使用hadoop配置文件指定的url
3、overwrite:
使用代表目标表内容会被删除。覆盖导入。

2.2、 insert

Hive的insert基本是结合select一起使用因为这是为了执行MR程序。

insert overwrite table stu_buck
select * from student cluster by(Sno);

需要保证查询结果列的数目和需要插入数据表格的列数一致。转换失败将会为null。
Multi Inserts 多重插入:

from a_table
insert overwrite table tablename1 [partition (partcol1=val1,partclo2=val2)]
select_statement1
insert overwrite table tablename2 [partition (partcol1=val1,partclo2=val2)]
select_statement2

Dynamic partition inserts 动态分区插入:
hive.exec.dynamici.partition=true;//开启动态分区，默认：false

insert overwrite table a_table partition (partcol1[=val1], partcol2[=val2] ...)  select ab,c from b_table;

2.3、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、order by :会对数据做全局排序因此只有一个reducer，会导致当输入较大时时间较长。
2、sort by : 不是全局排序，会在reducer前排序。如果用sort by 排序。同时设置了mapred.reduce.task>1 。只能保证一个reduce有序不能保证全局有序。
3、distribute by ：他会根据不同reduce分到不同的数据。是根据hash。
4、cluster by:他和distribute by 有一样的功能但是同时对该字段进行排序。所以，如果 distribute 和 sort 的字段是同一个时，此时， cluster by = distribute by + sort by 如果需要两个字段不同规则进行排序的时候。不能使用cluster by+sort by

3、Hive join

Hive支持传统数据库的所有join。同时还有left semi join和cross join 。

	Hive支持等值连接（a.id=b.id），不支持非等值的连接（a.id>b.id）。

注意：

• Join时把最大的表写在最后。因为Hive执行MR时，reducer会缓存join除了最后一个表的所有表的记录。否则很浪费内存。
• Left，right，full outer 用于处理join空记录的情况。没有关联的是null
• 优化where查询。
  例如：

select a.id,b.id from a left join b on (a.id=b.id) where a.dt='2020-03' and b.dt='2020-03'

优化：

select a.id,b.id from a left join b on (a.id=b.id and  a.dt='2020-03' and b.dt='2020-03')

• Join 是不能交换位置。

select a.id, a.num,b.id,c.id 
	from a join b on (a.id = b.id )
	left join c on (a.id=c.id)

	left和right join，都是左连接的。先join a表到b表，丢弃所有join id 中不匹配的记录，然后用这个的中间结果和c表做join。