Hive详解

1 Hive简介                                                          

　　Hadoop项目下包含很多子项目，这些项目很多是围绕hadoop的处理数据的核心基础上的。我们可以简单的看一下

Pig————一种高级数据流语言
Hive————一种类SQL数据仓库基础设施
HBase————一种模仿Google Bigtable的分布式的、面向列的数据库
ZooKeeper————一种用于管理分布式应用之间共享状态的可靠的协同系统
Chukwa————一种用于管理大型分布式系统的数据收集系统

　　hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供完整的sql查询功能，可以将sql语句转换为MapReduce任务进行运行。 其优点是学习成本低，可以通过类SQL语句快速实现简单的MapReduce统计，不必开发专门的MapReduce应用，十分适合数据仓库的统计分析。

　　Hive是建立在 Hadoop 上的数据仓库基础构架。它提供了一系列的工具，可以用来进行数据提取转化加载（ETL），这是一种可以存储、查询和分析存储在 Hadoop 中的大规模数据的机制。Hive 定义了简单的类 SQL 查询语言，称为 HQL，它允许熟悉 SQL 的用户查询数据。同时，这个语言也允许熟悉 MapReduce 开发者的开发自定义的 mapper 和 reducer 来处理内建的 mapper 和 reducer 无法完成的复杂的分析工作。Hive 没有专门的数据格式。 Hive 可以很好的工作在 Thrift 之上，控制分隔符，也允许用户指定数据格式。

　　HiveQL是Hive的类SQL语言，你可以发起一个查询来实现与Hive的交互。例如从user表获取所有活跃用户的查询

select user.* from user where user.active = 1;

　　Hive的设计体现出它是一个管理和查询结构化数据的系统。通过专注结构化数据，Hive可以实现MapReduce一般所不具备的某些优化和可用性功能。它沿用了关系数据库的常见概念，如果表、行、列和schema，以便于学习。

　　通常有几种方法与Hive交互，包括WebGUI和JDBC接口，但是大多数交互倾向利用命令行界面(CLI)。下面是Hive的高层体现结构框图。

2 试试几个命令                                           

示例1 查询的示例

　　在正式讲解HiveQL之前，现在命令行方式下运行几条命令是有好处的。可以感受HiveQL是如何工作的，也可以自己随便探索一下。
可以到美国专利局网站下载试验数据http://www.nber.org/patents/ cite75_99.txt,ASCII CSV格式下的。解压后是个txt文本文件。在Hive中，先定义存储该数据的表。

CREATE TABLE cite(citing INT, cited INT)
    ROW FORMAT DELIMITED
    FIELDS TERMINATED BY ','
    STORED AS TEXTFILE;

　　HiveQL语句以分号结束，如上所述，一条语句可以跨多行，直到分号才结束。上面的命令第一行是创建表及两个字段，其他行告诉数据的存储方式是文本文件以及解析方式是以逗号分隔的字段。

hive> CREATE TABLE cite(citing INT, cited INT)
    > ROW FORMAT DELIMITED
    > FIELDS TERMINATED BY ','
    > STORED AS TEXTFILE;
OK
Time taken: 25.317 seconds

　　由于本人是用比较老的笔记本上装的虚拟机，并且是集群方式运行，所以创建表是比较慢的。。。。。见谅— — 

示例2 查看所有创建的表

hive> SHOW TABLES;
OK
cite
t_afan_test
Time taken: 0.423 seconds

示例3 查看表的结构

hive> describe cite;
OK
citing    int    
cited    int    
Time taken: 0.405 seconds

示例4 加载数据到表中

hive> LOAD DATA LOCAL INPATH '/usr/local/hive/bin/patentData.txt' OVERWRITE INTO TABLE cite;
Copying data from file:/usr/local/hive/bin/patentData.txt
Copying file: file:/usr/local/hive/bin/patentData.txt
Loading data to table default.cite
Deleted hdfs://master:9000/user/hive/warehouse/cite
OK
Time taken: 0.367 seconds

注意加载的文件路径。

　　这告诉Hive从本地文件系统上一个名为patentData.txt的文件中把数据加载到cite表中。在此过程中，本地计算机会把数据上传到HDFS，放在Hive管理的某些目录下。
　　当加载数据时，Hive不会让任何违反schema的数据进入表中，Hive会将这些数据替换为空值，我们可以使用一个简单select语句浏览cite表中的数据。

hive> select * from cite limit 10;
OK
NULL    NULL
3858241    956203
3858241    1324234
3858241    3398406
3858241    3557384
3858241    3634889
3858242    1515701
3858242    3319261
3858242    3668705
3858242    3707004
Time taken: 0.425 seconds

　　我们将schema定义为两列整数，首行是citing和cited两个值，因此违反了schema，所以变成了空值。
　　统计表的行数，由于原始的文件70多兆，大概有1600万行，本人出于本机性能考虑-  -，只放了1000多行数据。下面是处理过程

hive> select count(1) from cite;
Total MapReduce jobs = 1
Launching Job 1 out of 1
Number of reduce tasks determined at compile time: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapred.reduce.tasks=<number>
Starting Job = job_201307140515_0001, Tracking URL = http://master:50030/jobdetails.jsp?jobid=job_201307140515_0001
Kill Command = /usr/local/hadoop/bin/../bin/hadoop job  -Dmapred.job.tracker=master:9001 -kill job_201307140515_0001
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2013-07-14 06:45:22,599 Stage-1 map = 0%,  reduce = 0%
2013-07-14 06:46:17,100 Stage-1 map = 100%,  reduce = 0%
2013-07-14 06:46:42,596 Stage-1 map = 100%,  reduce = 100%
Ended Job = job_201307140515_0001
MapReduce Jobs Launched: 
Job 0: Map: 1  Reduce: 1   HDFS Read: 17411 HDFS Write: 5 SUCESS
Total MapReduce CPU Time Spent: 0 msec
OK
1027
Time taken: 147.815 seconds

　　虽然是1027行，但是花了将近3分钟，机器性能不太好。
　　通过这些消息，可以知道该查询生成了一个MapReduce作业。Hive之美在于用户根本不需要知道MapReduce的存在。用户所需要关心的仅仅是一种类似于SQL的语言查询数据库。

　　上述查询结果被直接输出在屏幕上，大多数情况下，查询结果应该被存盘，而且通常被放在其他的Hive表中。我们下一个查询会查到每个专利的引用频率。首先生成一个表来存储它的结果：

hive> create table cite_count(cited int, count int);
OK
Time taken: 0.357 seconds

　　我们可以执行一个查询来得到引用频率，这个查询再次使用与SQL相类似的COUTN和GROUP BY功能，再加上insert overwrite table，就可以让hive将结果写入表中。

hive> insert overwrite table cite_count select cited,count(citing) from cite group by cited;
Total MapReduce jobs = 1
Launching Job 1 out of 1
Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapred.reduce.tasks=<number>
Starting Job = job_201307140515_0002, Tracking URL = http://master:50030/jobdetails.jsp?jobid=job_201307140515_0002
Kill Command = /usr/local/hadoop/bin/../bin/hadoop job  -Dmapred.job.tracker=master:9001 -kill job_201307140515_0002
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2013-07-14 06:58:14,823 Stage-1 map = 0%,  reduce = 0%
2013-07-14 06:58:22,874 Stage-1 map = 100%,  reduce = 0%
2013-07-14 06:58:34,933 Stage-1 map = 100%,  reduce = 100%
Ended Job = job_201307140515_0002
Loading data to table default.cite_count
Deleted hdfs://master:9000/user/hive/warehouse/cite_count
Table default.cite_count stats: [num_partitions: 0, num_files: 1, num_rows: 0, total_size: 10188, raw_data_size: 0]
1024 Rows loaded to cite_count
MapReduce Jobs Launched: 
Job 0: Map: 1  Reduce: 1   HDFS Read: 17411 HDFS Write: 10188 SUCESS
Total MapReduce CPU Time Spent: 0 msec
OK
Time taken: 37.106 seconds

　　查询执行告诉我们有1024行被装载到引用频率表中。我们可以执行HiveQL语句浏览这个引用频率表。

hive> select * from cite_count limit 10;
OK
NULL    0
14040    1
16749    1
17445    1
34608    1
55828    1
129311    1
170178    1
205960    1
213525    1
Time taken: 0.182 seconds

示例：当你使用完这个表后，可以使用drop table来删除它。

hive> drop table cite_count;
OK
Time taken: 2.571 seconds

可以使用exit命令来退出Hive会话

3 深入HiveQL                                             

3.1 数据模型                      

　　我们看的Hive将表作为基本的数据模型。物理上，Hive将表以目录形式放在/user/hive/warehouse下。例如，我们前面生成cite表将放在/user/hive/warehouse/cite目录下。输出结果的cite_count表则被放在/user/hive/warehouse/cite_count目录下。在大多数基本设置中，一个表下面的目录结构部只有一层，表中的数据分散在一个目录下的多个文件中。
　　关系数据库在列上使用索引来加速对这些列的查询。Hive则使用了分区列的概念，根据列的值将表划分为多个分区。分区列是一种划分标准的列，有点像关系型数据库里面的索引。例如，state列将表划分为50个分区，每个州有一个分区。每个州有一个分区。date列是用作日志数据的分区列，每天的数据归属于其自己的分区。Hive将分区列与常规的数据列区别看待，在分区列上执行查询要高效得多。这是因为Hive在物理上将不同的分区存在不同的目录中。例如，假设你有一个名为users的表，包含data和state两个分区列(加上常规的数据列)。Hive将这个表生成如下的目录结构

　　所有加利福尼亚(state=CA)2009年9月1日(date=20090901)的用户数据放在一个目录中，而其他分区的数据放在其他目录中。如果进来一个查询，它请求加利福尼亚州在2009年9月1日的用户数据，Hive仅需处理那个目录下的数据，而不用管其他分区中存储的users表中的数据。对分区列的跨区查询涉及对多个目录的处理，但是Hive仍能避免去扫描users中的所有数据。某种程度上，分区为Hive带来的好处类似于所以对传统关系数据库的作用。但是分区在粒度上远远不及索引。
　　除了分区，Hive数据模型还应用了通的概念，它可以提供对随机样本数据的高效查询。例如，当计算一个列的平均值时，数据的随机样本可以提供一个很好的近似结果。基于对桶列的散列，桶将分区的数据，进一步划分为为特定数目的文件。如果根据users表中的用户id，我们将桶的个数设定为32，那么Hive中的表将会有如下完整文件结构。
桶列结构

　　每个分区有32个桶，通过基于用户id的分桶，Hive会知道part-00000.....part-00031中的每个文件都是一个用户的随机样本。许多汇总统计的计算在样本数据集上依然有相当好的精度。分桶对于加速一些查询特别有用。例如Hive的查询可以用所有分区的part-00000这段数据，它仅为所有数据的1/32.

3.2 表的管理                       

　　我们已经看到如何为专利引用数据集生成一个样本表。现在让我们剖析一个更为复杂的表生成语句。这次生成一个名为page_view的表。

create table page_view(viewTime int, userid bigint,
                        page_url string, referrer_url string, 
                        ip string comment 'ip address of the user')
comment 'this is the page view table'
partioned by (dt string, country string)
clustered by(userid) into 32 buckets
row format delimited
    fields terminated by '	'
    lines terminated by '
'
stored as sequencefile;

　　我们分析这个很长的语句：

create table page_view(viewTime int, userid bigint,
                        page_url string, referrer_url string, 
                        ip string comment 'ip address of the user')

它指定了表名及其schema，包含列名和它们的类型。Hive支持如下的数据类型。

tinyint————单字节整数，1个字节
smallint————双字节整数
int————四字节整数
bigint————八字节整数
double————双精度浮点数
string————字符序列

注意这里没有布尔类型，通常用tinyint来替代。Hive还支持复杂的数据类型，如结构、映射和数组，它们可以嵌套。

注释：
comment 'this is the page view table'
这个是对每一列附加一个描述性注释。上面是描述ip的
指定分区列：
partioned by(dt string, country string)
　　前面已经讨论过，分区列面向查询进行了优化，它们不同于数据列viewTime，userid，page_url，referrer_url和ip。对于特定的行，分区列的值并没有显式地在行中存储，它隐含在目录路径中。但是，分区列和数据列的查询在用法上并无差别。
指定分桶的信息
clustered by (userid) into 32 buckets
clustered by(...)into...buckets语句指定了分桶信息，其中包含参与随机采样的列，以及生成的桶数。桶数的选择依赖于以下标准：
1.每个分区下数据的大小
2.打算使用的样本大小
　　第一个标准很重要，因为在分区被进一步划分为指定格式的桶之后，你并不想让每个桶文件太小而导致hadoop的效率很低。另一方面，桶应该和你所用样本有相同的大小或者比它更小。如果样本大小为用户基数的百分之三(1/32)左右，基于用户将分桶数设为32就是一个很好的选择。
　　与分区不同，在数据被写入表时，Hive不会自动地强制分桶，指定分桶信息仅会告知Hive当数据写入一个表时，你会手动地强制分桶(取样)的标准，而Hive可以在处理查询时利用这一优势。为了强制设置分桶的标准，你需要在填充表时正确地设置reducer的个数。
　　ROW FORMAT子句告诉Hive表中数据按行存储的方式，若无此子句，Hive默认以换行符作为行分隔符，以001(Ctrl+A)的ASCII值作为字段分隔符。我们的子句告诉Hive改为使用制表符字符作为字段分隔符。我们还告诉Hive使用换行符作为行分隔符，但那已经是默认值，我们在这里包括它只是为了演示而已。

row format delimited
        fields terminated by '	'
        lines terminated by '
'

　　最终，最后一个子句告诉Hive用于存储表中数据的文件格式
　　stored as sequencefile
　　目前hive支持两种格式，sequencefile和textfile，序列文件是一种压缩的格式，通常可提供更高的性能。
　　我们可以在create table语句中添加一个external修饰符，这样表被创建为指向一个现有的数据目录。你需要指定此目录的位置。

create table page_view(viewTime int, userid bigint,
                        page_url string, referrer_url string, 
                        ip string comment 'ip address of the user')
location '/path/to/existing/table/in/HDFS';

在生成一个表之后，你可以用describe命令在hive中查询这个表的schema结构
describe page_view;
修改表的名称
alter table page_view rename to pv;
增加新的列
alter table page_view add columns(newcol string);
删除一个分区
alter table page_view drop partion(dt='2009-09-01');
要删除整个表
drop table page_view
查看所有的表
show tables
用正则表达式查看部分表
show tables 'page_.*'

装载数据
load data local inpath 'page_view.txt'
overwrite into table page_view
partion (dt = '2009-09-01', country='US');
当操作来自本地系统文件中的数据时，可以执行Hive CLI中的本地UNIX命令。
获取一个文件的列表
hive>!ls
或检查文件的前几行
hive>!head hive_result
请注意！和；周围的空格不是必要的。主要为了提高可读性。

执行查询
　　大多数情况下HiveQL查询和SQL查询惊人地相似，一个通常的差别为HiveQL查询的结果相对较大。几乎总有一个insert子句，以便告诉Hive把结果存起来，往往存到其他的表中。

insert overwrite table query_result
也会是HDFS的一个目录
insert overwrite dictionary '/hdfs_dir/query_result'
有时还会是一个本地目录
insert overwrite local dictionary '/local_dir/query_result'
也就是：
insert overwrite table query_result
select * 
from page_view
where country = 'US';

组函数：
注意HiveQL使用count(1)代替SQL的count(*)。
像SQL一样，group by子句允许在组上执行聚合查询，此查询将列出每个国家的页面浏览数量：

select country， count(1)
from page_view
group by country

而下面的查询将列出每个国家的唯一用户数

select country， count(distinct userid)
from page_view
group by country

3.3 HiveQL的运算符及聚合函数

3.4 联结查询                      

　　对于用户而言，寻找Pig Latin和HiveQL这种更高级的语言的一个主要动力在于支持联结操作。目前HiveQL只支持等联结。联结查询的示例如下：
有两个表user和page_view

insert overwrite table query_result
select pv.*,u.gender,u.age
from page_view pv join user u on (pv.userid = u.id);

　　在语法上，将关键字join添加到from子句中两个表之间，然后在关键字on后面指定联结的列，若言联结两个以上的表，重复这种模式

insert overwrite table pv_friends
select pv.*,u.gender,u.age,f.friends
from page_view pv join user u on (pv.userid = u.id)
join friend_list f on (u.id = f.uid);

　　我们可以通过修改from子句为任何查询添加采样，该查询会去计算平均浏览时间，除非这个平均值仅取自32桶的一桶数据

select avg(viewTime)
from page_view tablesample(bucket 1 out of 32)
tablesample的一般语法如下
tablesample(bucket x out of y)

查询样本的大小为1/y左右，此外，y为创建时所指定的桶数的倍数或因数。例如，如果我们将y改为16，查询为

select avg(viewTime)
from page_view tablesample(bucket 1 out of 16)

　　那么样本的大小为大约为16个用户取一个，表仍有32个桶，但Hive仅去1到17并一起处理以满足该查询。另一方面，如果取y为64，则Hive会对单个桶中的一半数据执行查询。x的值仅用于选择要使用的桶，在真正随机的情况下，它的取值不会产生什么影响。

　　除了内置的函数外，程序员还可以在Hive中添加UDF来定制处理函数