Hive学习笔记

  第一次写博客，如果有哪些不对或是写错的地方，还请告知，谢谢！

1、什么是Hive？Hive能做什么事情？

    首先，Hive是基于Hadoop构建的一套数据仓库分析系统，它提供了丰富的SQL查询方式来分析存储在Hadoop分布式文件系统中的数据，其次Hive可以将结构化的数据文件映射为一张数据库表，并提供完整的SQL去查询分析，可以讲SQL入局转换为MapReduce的任务进行运行，通过自己的SQL去查询分析需要的内容，这套SQL被称为Hive QL。这样做的好处就是使不熟悉MapReduce的用户很方便的利用SQL语言进行查询，汇总和分析数据。

2、Hive表。

2.1、Hive数据表分类：内部表和外部表。

2.1.1、内部表：Hive创建并通过load data inpath 进数据库的表，这种表可以理解为数据和表结构都保存在一起的数据表。当通过drop table tableName删除元数据中表结构的同时，表中的据也同样会从HDFS中被删除。创建内部表使用create table命令，与mysql创建表命令一样，其中，if not exists是指如果tableName这张表不存在，就创建，否则不创建，例如：

Create table tableName if not exists tableName(

Id int,

Name string

);

2.1.2、外部表：在表结构创建以前，数据已经保存在HDFS上，通过创建表结构，将数据格式化到表的结构里，当进行drop table tableName操作的时候，Hive仅仅删除元数据的表结构，而不删除HDFS上的文件。

创建外部表使用external关键字，例如：

Create external table tableName if not exists tableName(

id int,

name string

);

2.2、Hive表操作。

2.2.1、Hive创建表时，需要注意指定分隔符（例如：两个字段之间使用空格“ ”或是制表符“\t”来分隔，那么在建表时需要怎么去指定？）

create table tableName(id int,name string) row format delimited fields terminated by "\t";

以上红色部分是指定分隔符的语法，蓝色部分则是指定的分隔符，这里使用"\t"(制表符) 来表示。

2.2.2、Hive加载本地文件到表结构，例(使用上面创建好的表)：

假设我’/root/test/table1’的内容为（id和name中间用制表符隔开）：

1  tom

2  rocky

3  abby

load data local inpath ‘/root/test/table1’ into table tableName;

Hive加载数据的时候使用load data inpath后跟路径来加载，但是如果加载的是本地文件需在load data后跟关键字local来说明加载的路径为本地路径。完成后使用select * from tableName查看。

2.2.3、以上2.2.1和2.2.2可合并为一步：

create external table if not exists tableName(

id string,

name string

) row format delimited fields terminated by '\t'

location 'hdfs:///tmp/Hive-test';

在创建表的同时直接加载数据时，这里的路径一定是一个文件夹，不能是一个文件。

2.2.4、Hive加载Avro数据到表结构，例：

create external table if not exists tableName

row format serde 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'

stored as

inputformat  'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'

outputformat 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'

location '/tmp/test/input/'

tblproperties(

             'avro.schema.literal'='{

             "type": "record",

             "name": "Test",

             "namespace": "com.captech.avro",

             "fields": [

                    { "name":"id",  "type":"string"},

                    { "name":"name",     "type":"string"}

             ]            }'

);

在使用Avro数据格式的时候，确保要在$HIVE_HOME/lib目录下放入以下四个工具包：avro-1.7.1.jar、avro-tools-1.7.4.jar、 jackson-core-asl-1.8.8.jar、jackson-mapper-asl-1.8.8.jar。当然，你也可以把这几个包存在别的路径下面，但是你需要把这四个包放在CLASSPATH中。

其中，row format serde、inputformat和outputformat的值是固定的，location是数据的路径，tblproperties的值为Avro数据格式的定义。

以上Hive QL语句中的tblproperties到结束可以替换为：

tblproperties(

‘avro.schema.url’=’hdfs:///test/ex.avsc’);

但前提是需要在hdfs:///test/下提前定义好需要的avro结构体到ex.avsc

2.2.5、Hive加载OrcFile数据到表结构，例：

create table tableName (

id int,

name string

) stored as orc location 'hdfs:///tmp/Hive-test';

需要注意的是以上标红部分，需要指定一下是哪种数据格式，这里指定的是orc，则表示文件格式为OrcFile。

SequenceFile和TextFile也类似。

2.3、Hive的partition

2.3.1、Hive partition概念，在Hive Select查询中一般会扫描整个表内容，会消耗很多时间做没必要的工作。有时候只需要扫描表中关心的一部分数据，因此建表时引入了partition概念。

2.3.2、分区表指的是在创建表时指定的partition的分区空间。

2.3.3、如果需要创建有分区的表，需要在create表的时候调用可选参数partitioned by。

2.3.4、分区建表分为2种，一种是单分区，也就是说在表文件夹目录下只有一级文件夹目录。另外一种是多分区，表文件夹下出现多文件夹嵌套模式。

单分区建表语句：create table tableName (id int, name string) partitioned by (dt string);单分区表，按天分区，在表结构中存在id，name，dt三列。

b、双分区建表语句：create table day_hour_table (id int, name string) partitioned by (dt string, hour string);双分区表，按天和小时分区，在表结构中新增加了dt和hour两列。

2.3.5、在已创建表的基础上添加表分区语法：

alert table tableName add partition (dt='2014-11-18', hour='14') location '/tmp/test' partition (dt='2014-11-18', hour='15') location '/tmp/test2'

2.3.6、删除分区语法，用户可以用 alert table tableName drop partition来删除分区。分区的元数据和数据将被一并删除。例：

alert table tableName drop partition (dt='2014-11-18', hour='09');

2.3.6、数据加载进分区表中语法：

load date [local] inpath 'filepath' [overwrite] into table tablename [partition (partcol1=val1, partcol2=val2 ...)]

例：

load date inpath '/user/pv.txt' into table tableName partition(dt='2014-11-18', hour='08');

load date local inpath '/user/hua/*' into table tableName partition(dt='2014-11-18');

当数据被加载至表中时，不会对数据进行任何转换。Load操作只是将数据复制至Hive表对应的位置。数据加载时在表下自动创建一个目录，文件存放在该分区下。

2.3.7、基于分区的查询的语句：

select tableName.* from tableName where tableName.dt>= '2014-11-18';

2.3.8、查看分区语句：

show partitions tableName;

2.4、Hive bucket（桶）

2.4.1、桶的概念

对于每一个表（table）或者分区， Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive也是针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

2.4.2、为什么使用桶

(1)获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接 （Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大减少JOIN的数据量。

(2)使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。

2.4.3、创建带桶的表：

create table tableName(id int,name string) clustered by (id) sorted by(name) into 4 buckets row format delimited fields terminated by '\t' stored as textfile; 

首先，我们来看如何告诉Hive—个表应该被划分成桶。我们使用clustered by子句来指定划分桶所用的列和要划分的桶的个数： 
create table tableName (id int)，name string) clustered by (id) into 4 buckets;

在这里，我们使用ID来确定如何划分桶(Hive使用对值进行哈希并将结果除 以桶的个数取余数。这样，任何一桶里都会有一个随机的用户集合。

对于map端连接的情况，两个表以相同方式划分桶。处理左边表内某个桶的 mapper知道右边表内相匹配的行在对应的桶内。因此，mapper只需要获取那个桶 (这只是右边表内存储数据的一小部分)即可进行连接。这一优化方法并不一定要求两个表必须桶的个数相同，两个表的桶个数是倍数关系也可以。

桶中的数据可以根据一个或多个列另外进行排序。由于这样对每个桶的连接变成了高效的归并排序(merge-sort), 因此可以进一步提升map端连接的效率。以下语法声明一个表使其使用排序桶： 
create table tableName(id int, name string) clustered by (id) sorted by (id asc) into 4 buckets; 

如果需要让Hive自动来进行划分桶的操作，可设置如下属性：

set hive.enforce.bucketing = true;  

2.4.4、往表中插入数据：

insert overwriter table tableName select * from tableName; 

2.4.5、对桶中的数据进行采样：

select * from tableName tablesample (bucket 1 out of 4 on id); 

桶的个数从1开始计数。因此，前面的查询从4个桶的第一个中获取所有的用户。 对于一个大规模的、均匀分布的数据集，这会返回表中约四分之一的数据行。我们 也可以用其他比例对若干个桶进行取样(因为取样并不是一个精确的操作，因此这个 比例不一定要是桶数的整数倍)。例如，下面的查询返回一半的桶：

select * from tableName tablesample(bucket 1 out of 2 on id)； 

tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y)
y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。例如，table总共分了64份，当y=32时，抽取(64/32=)2个bucket的数据，当y=128时，抽取(64/128=)1/2个bucket的数据。x表示从哪个bucket开始抽取。例如，table总bucket数为32，tablesample(bucket 3 out of 16)，表示总共抽取（32/16=）2个bucket的数据，分别为第3个bucket和第（3+16=）19个bucket的数据。