hive mysql性能_Hive 概括（very good）

读时模式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 {

column1 Array,

column2 Map,

column3 Struct

}

2.2 操作和函数

Hive提供普通SQL的操作，如关系操作，算术操作和逻辑操作。Hive提供几十个内置函数，包括数学和统计函数、字符串函数、日期函数、条件函数、聚集函数和处理XML/JSON的函数。Hive

Shell ”describe function ”可用来获取函数说明。

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,

col2from source;

//

创建一个类似其它表的空表

create

table new_table like existing_table;

3.5 表的修改

alter

table source rename

to target;

alter

table sourceadd

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

byyear 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’ // 调用外部python程序

As

year,

temperature; // 输出作为年份和气温值

如果要用查询的嵌套形式，我们可以指定map和reduce函数。

From

(

From record2

Map year,

temperature, quality

Using ‘is_good_quality.py’

As year, temperature )

map_output

Reduce year, temperature

Using ‘max_temperature_reduce.py’

As

year, temperature;

4.3 连接

使用Hive和直接使用MapReduce相比，好处是它简化了常用操作。

内连接：Hive只支持等值连接，只允许在from子句中出现一个表，但可以使用多个join…on…子句来连接多个表，Hive会智能地以最小MapReduce作业数来执行连接。

select sales.*,

things.* fromsales join things on (sales.id

= things.id);

不支持：select sales.*, things.* from sales, things where sales.id =

things.id;

外连接：外连接可以让你找到连接表中不能匹配的数据行。

select sales.*,

things.* fromsales left

outer

join things on (sales.id

= things.id);

select sales.*,

things.* fromsales right

outer

join things on (sales.id

= things.id);

select sales.*,

things.* fromsales full

outer

join things on (sales.id

= things.id);

select * fromthings 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

(

select station, year,

max(temperature) as max_temperature

from record2

where temperature != 999 and

quality != 0

group by station, year

)

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接收单个数据行，产生多个数据行(一个表)作为输出。