Hive原理与Hive建表实例（内部表与外部表，静态与动态分区表，分桶）_完成外部表、静态分区表、动态分区表、分桶表创建实验,执行 select 查询语句

1.  Hive的原理

HiveQL语句会转化成MapReduce，提交任务到Hadoop中，用HDFS存储数据，MapReduce查询数据。

1.  用户接口主有三个：CLI、JDBC/ODBC和WebGUI。

   CLI为shell命令行；JDBC/ODBC是Hive的JAVA实现，与传统数据库JDBC类似；WebGUI是通过浏览器访问Hive。

2.  元数据存储：Hive 将元数据存储在数据库mysql , derby中。

   Hive 中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等。

3.  Hive的数据存储

Hive中所有的数据都存储在 HDFS 中，没有专门的数据存储格式（可支持Text，SequenceFile，ParquetFile，RCFILE等）

Hive 中包含以下数据模型：DB、Table，External Table，Partition，Bucket

• db：在hdfs中表现为${hive.metastore.warehouse.dir}目录下一个文件夹
• table：在hdfs中表现所属db目录下一个文件夹
• external table：与table类似，不过其数据存放位置可以在任意指定路径
• partition：在hdfs中表现为table目录下的子目录
• bucket：在hdfs中表现为同一个表目录下根据hash散列之后的多个文件

Hive的数据类型

基础数据类型包括：TINYINT, SMALLINT, INT, BIGINT, BOOLEAN, FLOAT, DOUBLE, STRING, BINARY, TIMESTAMP, DECIMAL, CHAR, VARCHAR, DATE。

复杂类型包括： ARRAY, MAP, STRUCT, UNION。

CREATE TABLE employees (
    name STRING,
    salary FLOAT,
    subordinates ARRAY<STRING>,
    deductions MAP<STRING, FLOAT>,
    address STRUCT<street:STRING, city:STRING, state:STRING, zip:INT>
) PARTITIONED BY (country STRING, state STRING);

内部表也称为managed_table，默认存储在/user/hive/warehouse下，也可以通过location指定（一般不指定），删除表时，会删除表数据以及元数据；
外部表称之为external_table，在创建表时可以自己指定目录位置location（一般都要指定）；删除表时，只会删除元数据不会删除表数据。

2.  创建内部表mytable

create table if not exists mytable(sid int, sname string)   \
row format delimited fields terminated by '\005'    \
stored as textfile;

查看HDFS： http://100.168.1.182:50070/explorer.html#/user/hive/warehouse/mytable，有/user/hive/warehouse/mytable目录

创建同另一张表格式一样的表，但数据为空

create table if not exists mytable_2
Like mytable;

2.  创建外部表stocks

我的本地文件 /home/hadoop/Data/stocks/stocks.txt ， 如下：

1,RMB,2018-08-01,1,1,1,
2,HKD,2018-08-02,1.18,1.19,1.20,
3,USD,2018-08-03,0.14,0.15,0.16,
4,GBP,2018-08-04,0.11,0.12,0.13,
5,TWD,2018-08-05,4.48,4.49,4.50,
6,AUD,2018-08-06,0.19,0.20,0.21,

上传文件

hadoop fs -mkdir /user/hive/external/data
hadoop fs -mkdir /user/hive/external/data/stocks
hadoop fs -put stocks.txt /user/hive/external/data/stocks/
 

这是HDFS中有这份数据，此时该创建外部表指向这份数据。

create external table if not exists stocks(
stock_id  int,
money string,
ymd  string,
price_open  float,
price_mid  float,
price_close  float)
row format delimited  fields  terminated by ','
location '/user/hive/external/data/stocks';

刚开始我用exchange   string， 一直报错，出现NoViableAltException问题

NoViableAltException(109@[2238:1: columnNameTypeOrPKOrFK : ( ( foreignKeyWithName ) | ( primaryKeyWithName ) | ( primaryKeyWithoutName ) | ( foreignKeyWithoutName ) | ( columnNameType ) );])
。。。。。。
FAILED: ParseException line 1:58 cannot recognize input near 'exchange' 'STRING' ',' in column name or primary key or foreign key

后来我将exchange   string改成money string,  就可以了，在数据库中 exchange是更改的意思，不要使用！！

hive> select * from stocks;
OK
1	RMB	2018-08-01	1.0	1.0	1.0
2	HKD	2018-08-02	1.18	1.19	1.2
3	USD	2018-08-03	0.14	0.15	0.16
4	GBP	2018-08-04	0.11	0.12	0.13
5	TWD	2018-08-05	4.48	4.49	4.5
6	AUD	2018-08-06	0.19	0.2	0.21
Time taken: 1.366 seconds, Fetched: 6 row(s)
 

3.  分区表

分区代表了数据的仓库，也就是文件夹目录。每个文件夹下面可以放不同的数据文件。通过文件夹可以查询里面存放的文件。但文件夹本身和数据的内容毫无关系。

本地有 /home/hadoop/Data/order.txt 如下：

1	2017-06-20 00:01:02.123456789
2	2017-09-21 00:03:04.234567890
3	2018-02-22 05:06:07.345678901
4	2018-05-23 06:07:08.456789012
5	2018-07-24 07:08:09.567890123
6	2018-08-25 08:09:10.678901234

创建静态分区表

create table order_par(order_number string, event_time string)
partitioned by (event_month string)
row format delimited fields terminated by '\t';

加载数据到Hive分区表中：

方法一： 通过load方式加载
load data local inpath  "/home/hadoop/Data/order.txt" overwrite into table order_par  
partition (event_month='2017-09');
 
方法二： 查询装入
insert overwrite table order_par partition(event_month='2017-09')
select order_number,event_time from order_par where event_month='2018-05';

方法三：  手工创建hdfs目录和上传文件，从而达到添加分区的目的
静态分区表如果手工创建对应的hdfs目录上传文件，分区表中无法查到该分区信息，需要刷新，这种添加分区的途径是不合法

手动在HDFS上创建了分区目录，并手动上传了数据文件，之后：
select * from order_par where event_month='2018-05';
 
此时是查不到该分区的， 修复表信息之后可以查询
MSCK REPAIR TABLE order_par;

查看数据文件：select * from order_par ;     或者     select * from order_par where event_month='2017-09';

hive> select * from order_par where event_month='2017-09';
OK
1	2017-06-20 00:01:02.123456789	2017-09
2	2017-09-21 00:03:04.234567890	2017-09
3	2018-02-22 05:06:07.345678901	2017-09
4	2018-05-23 06:07:08.456789012	2017-09
5	2018-07-24 07:08:09.567890123	2017-09
6	2018-08-25 08:09:10.678901234	2017-09
Time taken: 0.572 seconds, Fetched: 6 row(s)

查看HDFS 中的数据文件：    /user/hive/warehouse/srt.db/order_par/event_month=2017-09/order.txt

hadoop@Master:~/Data$ hadoop fs -cat /user/hive/warehouse/srt.db/order_par/event_month=2017-09/order.txt
1	2017-06-20 00:01:02.123456789
2	2017-09-21 00:03:04.234567890
3	2018-02-22 05:06:07.345678901
4	2018-05-23 06:07:08.456789012
5	2018-07-24 07:08:09.567890123
6	2018-08-25 08:09:10.678901234

使用where子句，过滤分区字段，遍历某个分区； 以上两个SQL可以查到列event_month信息，而使用hdfs dfs -cat看不到该列，说明Hive分区表的分区列是伪列

添加分区，两个分区有相同的数据

添加新的分区：
alter table order_par add partition(event_month='2018-05');
 
加载数据：
load data local inpath "/home/hadoop/Data/order.txt" overwrite into table order_par  
partition(event_month='2018-05');
 
查看分区记录：
hive> select * from order_par where event_month='2018-05';
OK
1	2017-06-20 00:01:02.123456789	2018-05
2	2017-09-21 00:03:04.234567890	2018-05
3	2018-02-22 05:06:07.345678901	2018-05
4	2018-05-23 06:07:08.456789012	2018-05
5	2018-07-24 07:08:09.567890123	2018-05
6	2018-08-25 08:09:10.678901234	2018-05
Time taken: 0.153 seconds, Fetched: 6 row(s)
 
查看表的分区数：
hive> show partitions order_par;
OK
event_month=2017-09
event_month=2018-05
Time taken: 0.067 seconds, Fetched: 1 row(s)
 
两个分区的数据合并
select * from order_par where event_month='2018-05'  
union 
select * from order_par where event_month='2017-09';
 
查看合并之后的结果：
hive> select * from order_par;
OK
1	2017-06-20 00:01:02.123456789	2017-09
2	2017-09-21 00:03:04.234567890	2017-09
3	2018-02-22 05:06:07.345678901	2017-09
4	2018-05-23 06:07:08.456789012	2017-09
5	2018-07-24 07:08:09.567890123	2017-09
6	2018-08-25 08:09:10.678901234	2017-09
1	2017-06-20 00:01:02.123456789	2018-05
2	2017-09-21 00:03:04.234567890	2018-05
3	2018-02-22 05:06:07.345678901	2018-05
4	2018-05-23 06:07:08.456789012	2018-05
5	2018-07-24 07:08:09.567890123	2018-05
6	2018-08-25 08:09:10.678901234	2018-05
Time taken: 0.12 seconds, Fetched: 12 row(s)
 
删除分区：
alter table order_par drop partition(event_month='2018-05');

动态分区：分区的值是非确定的，由输入数据来确定

如果用上述的静态分区，插入的时候必须首先要知道有什么分区类型，而且每个分区写一个load data，太烦人。使用动态分区可解决以上问题，其可以根据查询得到的数据动态分配到分区里。其实动态分区就是不指定分区目录，由系统自己选择。

首先Hive 有一张表 person_par，如下：

hive> select * from person_par;
OK
lily	china	man	2013-03-28
nancy	china	woman	2013-03-28
hanmei	america	man	2013-03-28
jan	china	woman	2013-03-29
mary	america	man	2013-03-29
lilei	china	man	2013-03-29

1. 动态分区的字段，需要写在select语句中所有字段的最后
2. hive需要设置set hive.exec.dynamic.partition=true;(默认值是false，表示是否开启动态分区)
3. [可选]hive需要设置set hive.exec.dynamic.partition.mode=nonstrict;(默认是strict模式，表示至少需要指定一个静态分区；nonstrict模式表示不需要指定静态分区)

设置动态分区
hive> set hive.exec.dynamic.partition=true;
 
创建新表person_par_dnm
hive> create table person_par_dnm ( name  string, nation string) partitioned by (sex  string, dt string)
    > row format delimited fields terminated by ',';
OK
Time taken: 0.334 seconds
 
现在查询分区，并没有
hive> show partitions person_par_dnm;
OK
Time taken: 0.073 seconds
 
从旧表person_par导入数据到新表中person_par_dnm，自动实现分区sex="man",dt，按照最后的dt分区
hive> insert overwrite table person_par_dnm partition(sex="man",dt) select name, nation, dt  from person_par;
 
现在查询分区，有以下分区sex=man/dt=2013-03-28和sex=man/dt=2013-03-29
hive> show partitions person_par_dnm;
OK
sex=man/dt=2013-03-28
sex=man/dt=2013-03-29

查看HDFS上面的目录，有   /user/hive/warehouse/person_par_dnm/sex=man，说明系统按照时间自动分区了

4.  分桶表

Hive采用对列值哈希来组织数据的方式, 称之为分桶, 适合采样和map-join.    使用用户ID来确定如何划分桶(Hive使用对值进行哈希并将结果除 以桶的个数取余数。这样，任何一桶里都会有一个随机的用户集合（PS：其实也能说是随机）

桶则是按照数据内容的某个值进行分桶，把一个大文件散列称为一个个小文件

建立原表person_srt
hive> create table person_srt (srtid int, name string, nation string, sex string, dt string)
    > row format delimited fields terminated by ',';
 
装入数据
hive>load data local inpath '/home/hadoop/Data/person_srt.txt' overwrite into table person_srt;
 
查看数据
hive> select * from person_srt;
OK
1	lily	china	man	2013-03-28
2	nancy	china	woman	2013-03-28
3	hanmei	america	man	2013-03-28
4	jan	china	woman	2013-03-29
5	mary	america	man	2013-03-29
6	lilei	china	man	2013-03-29
 
*****************************************************************************
建立新的分桶表person_srt2，要求：
1.指定根据哪一列来划分桶： clustered by (srtid)
2. 以srtid降序排列：sorted by(srtid desc)
3. 指定划分几个桶：  into 2 buckets
distribute by 类似于mapreduce中分区partition，对数据进行分区，结合sort by进行使用
cluster by   当distribute by和sort by字段相同时 可以用cluster by代替
**********************************************************************************
hive> create table person_srt2( srtid int, name string, nation string, sex string, dt string) 
    > clustered by (srtid) sorted by(srtid desc) into 2 buckets
    > row format delimited fields terminated by ',';
 
设置相关参数
hive> set hive.enforce.bucketing=true;
hive> set mapreduce.job.reduces=2;
 
把旧表person_srt的数据装入分桶表person_srt2中
hive> insert into table person_srt2 select srtid,name,nation,sex,dt from person_srt 
distribute by(srtid) sort by(srtid asc);
 
查询分桶表，以降序排列
hive> select * from person_srt2;
OK
6	lilei	china	man	2013-03-29
4	jan	china	woman	2013-03-29
2	nancy	china	woman	2013-03-28
5	mary	america	man	2013-03-29
3	hanmei	america	man	2013-03-28
1	lily	china	man	2013-03-28
 
对桶中的数据进行采样
从2个桶的第1个中获取所有的用户
hive> select * from person_srt2 tablesample(bucket 1 out of 2);
OK
6	lilei	china	man	2013-03-29
4	jan	china	woman	2013-03-29
2	nancy	china	woman	2013-03-28
从2个桶的第2个中获取所有的用户
hive> select * from person_srt2 tablesample(bucket 2 out of 2);
OK
5	mary	america	man	2013-03-29
3	hanmei	america	man	2013-03-28
1	lily	china	man	2013-03-28

创建分桶表成功，HDFS有如下目录：    /user/hive/warehouse/srt.db/person_srt2

查看HDFS上面的两个文件的数据：

hadoop@Master:~/Data$ hadoop fs -cat /user/hive/warehouse/srt.db/person_srt2/000000_0
6,lilei,china,man,2013-03-29
4,jan,china,woman,2013-03-29
2,nancy,china,woman,2013-03-28
hadoop@Master:~/Data$ hadoop fs -cat /user/hive/warehouse/srt.db/person_srt2/000001_0
5,mary,america,man,2013-03-29
3,hanmei,america,man,2013-03-28
1,lily,china,man,2013-03-28