原文:http://www.distream.org/?p=393
1 和传统数据库的比较
读时模式vs.写时模式
传统数据库是写时模式(schema on write),即数据在写入数据库时对模式进行检查。Hive在数据加载时不进行验证,而是在查询时进行,是读时模式(schema on read)。写时模式有利于提升查询性能,因为数据库可以对列进行索引,并对数据压缩。但作为权衡,此时加载数据会花更多时间。此外,在很多情况下,加载时模式是未知的,因为查询尚未确定,因此也不能决定使用何种索引。这些情况下,正是Hive发挥其长处的地方。
更新、事务和索引
这三项是传统数据库最重要的特性,但Hive没有计划支持这些。因此Hive被设计成用MapReduce操作HDFS数据,在这种环境下全表扫描是常态操作,而表更新则是通过把数据变换后放入新表实现的。(即不支持记录的更新)对于事务问题,Hive并没有对表的并发访问定义清楚的语义,因此应用程序需要自己实现应用层的并发或加锁机制。同时与HBase的集成也改变这方面的状况,HBase支持行更新和列索引。
2 HiveQL语言
Hive的SQL不完全支持SQL-92标准,但它的特性是来自开发者的需求,有些扩展受MapReduce启发而来的,如多表插入,Transform,Map和Reduce子句。
Explain关键字可以输出很多查询执行计划的详细信息,包括抽象语法树、Hive执行各阶段之间的依赖图以及每个阶段的信息。Hive目前使用基于规则的查询优化器来确定查询是如何执行的,但以后会增加基于代价的优化器。
2.1 基本类型
和传统数据库相比,Hive只支持原子数据类型的很少一部分。Hive并不支持时间相关数据类型。有四种有符号整型:tinyint, smallint, int, bigint,分别对应java的byte, short, int, long。Hive的浮点数类型float和double和java一样。Hive支持Boolean,还有String,但不能声明最多能存储多少个字符(理论上最多2GB),类似其它数据库的varchar。
基本类型之间可以转换,规则如下:任何整数都可以隐式地转换为一个范围更广的类型;任何整数、float和String都能隐式的转换为Double;tinyint, smallint, int可以转换为float。CAST可以执行强制转换,如CAST(‘1’ as int)将得到1。如失败则返回null。
Hive有三种复杂类型:Array, Map和Struct。
Create Table complex {
}
2.2 操作和函数
Hive提供普通SQL的操作,如关系操作,算术操作和逻辑操作。Hive提供几十个内置函数,包括数学和统计函数、字符串函数、日期函数、条件函数、聚集函数和处理XML/JSON的函数。Hive Shell ”describe function <name>”可用来获取函数说明。
3 表
3.1 托管表和外部表
托管表会将数据移入Hive的warehouse目录;外部表则不会。经验法则是,如果所有处理都由Hive完成,应该使用托管表;但如果要用Hive和其它工具来处理同一个数据集,则使用外部表。
create table name();
create external table name2();
3.2 分区和桶
分区(“partition”)是对表进行初略划分的机制(通过特殊的列),而将表或者分区继续分为桶“bucket”则是继续这种优化,两者皆可提高查询的效率。
多个分区将把表分成多个有层次结构的目录,这样查询就可以快速定位到部分目录。桶对应到目录下的有序文件,一个文件就是一个桶。使用桶除了提高查询效率外,还使“取样”更高效。
create table logs(ts bigint, line string) partitioned by (dt string, country string);
当加载数据时,应显示指定分区值:
load data local inpath ‘file1’ into table logs partition (dt=’2010-01-01’, country=’GB’);
桶对应于MapReduce的输出文件分区,和reduce的任务数一致。
create table bucketed_users (id int, name string) clustered by (id) into 4 buckets;
桶中的数据可以根据一个或多个列另外进行排序。
create table bucketed_users (id int, name string) clustered by (id) sorted by (id ASC) into 4 buckets;
TableSample可以对表进行取样:
select * from bucketed_users tablesample(bucket 1 out of 4 on id); // 返回1/4的桶
3.3 存储格式
3.4 导入数据
insert overwrite table target select col1, col2 from source; // select语句的结果覆盖插入target表的数据
insert overwrite table partition (dt=’2010-01-01’) target select col1, col2 from source; //指定分区
insert overwrite table partition (dt) target select col1, col2,dt from source; // 动态分区插入
// 多表插入
from source
insert overwrite table records_by_year select year, count(1) group by year
insert overwrite table good_records_by_year select year, count(1) where quality ==0 group by year;
// CTAS
create table target AS select col1, col2 from source;
// 创建一个类似其它表的空表
create table new_table like existing_table;
3.5 表的修改
alter table source rename to target;
alter table source add columns (col3 string);
// 更多的是修改列名或数据类型
3.6 删除表
drop table target; // 删除表的元数据,如果是托管表还会删除表的数据
如果要删除表的内容,而保留元数据,则删除数据文件: hive>dfs –rmr ‘warehouse/my-table’;
4 查询数据
4.1 排序和聚集
Order by 能够预期产生完全排序的结果,但是它是通过只用一个reduce来做到这点的。所以对于大规模的数据集它的效率非常低。在很多情况下,并不需要全局排序,此时可以换成Hive的非标准扩展sort by。Sort by为每个reducer产生一个排序文件。在有些情况下,你需要控制某个特定行应该到哪个reducer,通常是为了进行后续的聚集操作。Hive的distribute by 子句可以做这件事。
// 根据年份和气温对气象数据进行排序,以确保所有具有相同年份的行最终都在一个reducer分区中
From record2
select year, temperature
distribute by year
sort by year asc, temperature desc;
4.2 Map Reduce脚本
使用Hadoop Streaming, TRANSFORM, MAP, REDUCE子句这样的方法,便可以在Hive中调用外部脚本。
Add File /path/to/is_good_quality.py;
From records2
Select TRANSFORM(year, temperature, quality) // 从record2中对于每行的三个字段作为输入
Using ‘is_good_quality.py’
As year, temperature;
如果要用查询的嵌套形式,我们可以指定map和reduce函数。
From (
Reduce year, temperature
Using ‘max_temperature_reduce.py’
As year, temperature;
4.3 连接
使用Hive和直接使用MapReduce相比,好处是它简化了常用操作。
内连接:Hive只支持等值连接,只允许在from子句中出现一个表,但可以使用多个join…on…子句来连接多个表,Hive会智能地以最小MapReduce作业数来执行连接。
select sales.*, things.* from sales join things on (sales.id = things.id);
不支持:select sales.*, things.* from sales, things where sales.id = things.id;
外连接:外连接可以让你找到连接表中不能匹配的数据行。
select sales.*, things.* from sales left outer join things on (sales.id = things.id);
select sales.*, things.* from sales right outer join things on (sales.id = things.id);
select sales.*, things.* from sales full outer join things on (sales.id = things.id);
select * from things left semi join sales on (sales.id = things.id);
// 类似于in子查询:select * from things where things.id in (select id from sales);
// 写left semi join查询时必须遵循一个限制,右表只能在on子句中出现。
Map连接:如果有一个连接表小到足以放入内存,Hive就可以把较小的表放入每个mapper的内存来执行连接操作。如果要指定map连接,需要在SQL中使用C语言风格的注释:
select sales.*, things.* from sales join things on (sales.id = things.id);
Map连接可以利用分桶的表,因为作用于桶的mapper加载右侧表中对应的桶即可执行连接。启动优化选项: set hive.optimize.bucketmapjoin = true;
4.4 子查询
Hive对子查询的支持很有限,只允许出现在select语句的from中。子查询的列必须有唯一的名称,以便外层查询可以引用这些列。
Select station, year, AVG(max_temperature)
from (
) mt
group by station, year;
4.5 视图
视图是一种用select语句定义的虚表(virtual table),Hive并不把视图物化存储到磁盘上。创建视图时不执行查询,视图的select语句只是在执行引用视图的语句时才执行。如果一个视图要对基表进行大规模的变换,或视图的查询会频繁执行,可能需要新建一个表,并把视图的内容存储到新表中,相当于物化它(create table…as select)。
create view valid_records
as
select * from record2
where temperature != 999 and quality != 0;
5. 用户自定义函数
UDF必须用java语言编写,包括三类:普通UDF,UDAF(用户自定义聚集函数)和UDTF(用户自定义表生成函数),它们接受输入和产生的输出的数据行在数量上不同:
- UDF操作作用于单个数据行,且产生一个数据行输出;
- UDAF接收多个输入数据行,产生一个数据行输出;
- UDTF接收单个数据行,产生多个数据行(一个表)作为输出。