hive语法和常用函数

Hive是一个基于Hadoop分布式系统上的数据仓库，最早是由Facebook公司开发的，Hive极大的推进了Hadoopecosystem在数据仓库方面上的发展。

Facebook的分析人员中很多工程师比较擅长而SQL而不善于开发MapReduce程序，为此开发出Hive，并对比较熟悉SQL的工程师提供了一套新的SQL-like方言——HiveQL。

HiveSQL方言特别和MySQL方言很像，并提供了HiveQL的编程接口。HiveQL语句最终被Hive解析器引擎解析为MarReduce程序，作为job提交给JobTracker运行。这对MapReduce框架是一个很有力的支持。

Hive是一个数据仓库，它提供了数据仓库的部分功能：数据ETL(抽取、转换、加载)工具，数据存储管理，大数据集的查询和分析能力。

由于Hive是Hadoop上的数据仓库，因此Hive也具有高延迟、批处理的的特性，即使处理很小的数据也会有比较高的延迟。故此，Hive的性能就和居于传统数据库的数据仓库的性能不能比较了。

Hive不提供数据排序和查询的cache功能，不提供索引功能，不提供在线事物，也不提供实时的查询功能，更不提供实时的记录更性的功能，但是，Hive能很好地处理在不变的超大数据集上的批量的分析处理功能。Hive是基于hadoop平台的，故有很好的扩展性(可以自适应机器和数据量的动态变化)，高延展性(自定义函数)，良好的容错性，低约束的数据输入格式。

下面我们来看一下Hive的架构和执行流程以及编译流程：

用户提交的HiveQL语句最终被编译为MapReduce程序作为Job提交给Hadoop执行。

Hive的数据类型

Hive的基本数据类型有：TINYINT，SAMLLINT，INT，BIGINT，BOOLEAN，FLOAT，DOUBLE，STRING，TIMESTAMP(V0.8.0+)和BINARY(V0.8.0+)。

Hive的集合类型有：STRUCT，MAP和ARRAY。

Hive主要有四种数据模型(即表)：(内部)表、外部表、分区表和桶表。

表的元数据保存传统的数据库的表中，当前hive只支持Derby和MySQL数据库。

内部表：

Hive中的表和传统数据库中的表在概念上是类似的，Hive的每个表都有自己的存储目录，除了外部表外，所有的表数据都存放在配置在hive-site.xml文件的${hive.metastore.warehouse.dir}/table_name目录下。

Java代码

CREATE

TABLE

IF

NOT

EXISTS

students(user_no

INT,name

STRING,sex

STRING,

grade

STRING

COMMOT

'班级'）COMMONT

'学生表'

ROW

FORMAT

DELIMITED

FIELDS

TERMINATED

BY

','

STORE

AS

TEXTFILE;

外部表：

外部表指向已经存在在HadoopHDFS上的数据，除了在删除外部表时只删除元数据而不会删除表数据外，其他和内部表很像。

Java代码

CREATE

EXTERNAL

TABLE

IF

NOT

EXISTS

students(user_no

INT,name

STRING,sex

STRING,

class

STRING

COMMOT

'班级'）COMMONT

'学生表'

ROW

FORMAT

DELIMITED

FIELDS

TERMINATED

BY

','

STORE

AS

SEQUENCEFILE

LOCATION

'/usr/test/data/students.txt';

分区表：

分区表的每一个分区都对应数据库中相应分区列的一个索引，但是其组织方式和传统的关系型数据库不同。在Hive中，分区表的每一个分区都对应表下的一个目录，所有的分区的数据都存储在对应的目录中。

比如说，分区表partitinTable有包含nation(国家)、ds(日期)和city(城市)3个分区，其中nation=china，ds=20130506，city=Shanghai则对应HDFS上的目录为：

/datawarehouse/partitinTable/nation=china/city=Shanghai/ds=20130506/。

Java代码

CREATE

TABLE

IF

NOT

EXISTS

students(user_no

INT,name

STRING,sex

STRING,

class

STRING

COMMOT

'班级'）COMMONT

'学生表'

PARTITIONED

BY

(ds

STRING,country

STRING)

ROW

FORMAT

DELIMITED

FIELDS

TERMINATED

BY

','

STORE

AS

SEQUENCEFILE;

分区中定义的变量名不能和表中的列相同。

桶区表：

桶表就是对指定列进行哈希(hash)计算，然后会根据hash值进行切分数据，将具有不同hash值的数据写到每个桶对应的文件中。

Java代码

CREATE

TABLE

IF

NOT

EXISTS

students(user_no

INT,name

STRING,sex

STRING,

class

STRING

COMMOT

'班级',score

SMALLINT

COMMOT

'总分'）COMMONT

'学生表'

PARTITIONED

BY

(ds

STRING,country

STRING)

CLUSTERED

BY(user_no)

SORTED

BY(score)

INTO

32

BUCKETS

ROW

FORMAT

DELIMITED

FIELDS

TERMINATED

BY

','

STORE

AS

SEQUENCEFILE;

内部表和外部表的主要区别:

1)、内部表创建要2步：表创建和数据加载，这两个过程可以同步执行。在数据加载的过程中，数据数据会移动到数据仓库的目录中；外部表的创建只需要一个步骤，表创建数据加载同时完成，表数据不会移动。

2)、删除内部表时，会将表数据和表的元数据一同删除；而删除外部表时，紧删除表的元数据而不会删除表数据。

在上一节《深入学习《ProgramingHive》：Hive的数据模型(表)》中，已经学习过表的定义，接下来接着学习Hive的DDL操作。

复制表结构：Java代码

CREATE

TABLE

IF

NOT

EXISTS

students2

LIKE

students;

查看表结构：Java代码

DESCRIBE

TABLE

students;

删除表：Java代码

DROP

TABLE

students;

创建视图：

Hive中的视图(view)的概念和传统数据库中的表是相同的，是只读的，目前还不支持不支持物化视图。

如果在创建试图后，再将基本表中被视图引用的列修改，那么修改后的数据列将不会体现在视图中；如果基本表被删除或以不兼容的方式被修改，则查询该视图时会失败。

Java代码

CREATE

VIEW

IF

NOT

EXISTS

view_students

(user_no,name,score)

AS

SELECT

user_no,name,score

FROM

students;

删除视图：Java代码

DROP

VIEW

view_students;

定制表存储方式：Java代码

CREATE

TABLE

IF

NOT

EXISTS

employees(

no

STRING,

name

STRING,

salary

FLOAT,

subordinates

ARRAY<STRING>,

deductions

MAP<STRING,FLOAT>,

address

STRUCT<street:STRING,district:STRING,city:STRING,province:STRING,ZIP:STRING>

)

ROW

FORMAT

DELIMITED

FIELDS

TERMINATED

BY

'\001'

COLLECTIN

ITEMS

TERMINATED

BY

'\002'

MAP

KEYS

TERMINATED

'\003'

LINES

TERMINATED

BY

'\n'

STORED

AS

INPUTFORMAT

'org.linkedin.haivvreo.AvroContainerInputFormat'

OUTPUTFORMAT

'org.linkedin.haivvreo.AvroContainerOutputFormat';

修改表结构：

1）、重命名表Java代码

ALTER

TABLE

table_name

RENAME

TO

new_table_name;

只修改了表名，不会改变表结构和数据存放的位置。

2）、修改列名、类型、位置及注释Java代码

CREATE

TABLE

IF

NOT

EXISTS

test(a

INT,b

STRING,c

FLOAT);//创建测试表

//修改列名

TABLE

test

CHANGE

a

a2

INT;

/*将a列的名字改为a2，并将其数据类型改为STRING，然后将之放在b列之后；修改

*

后的表结构为：b

INT,a2

STRING,c

FLOAT

*/

ALTER

TABLE

test

CHANGE

a

a2

STRING

AFTER

b;

/*

*

将b列的名字改为b2，并将其数据类型由FLOAT改为DOUBLE，然后将之

*

放在第一列；修改后的表结构为：

*

b2

DOUBLE,a

STRING,c

FLOAT

*/

ALTER

TABLE

test

CHANGE

b

b2

DOUBLE

FIRST;

列的改变只会修改Hive的元数据，而不改变实际的数据。

用户应该确保元数据的定义和实际的数据结构保持一致。

3）、增加新列Java代码

ALTER

TABLE

test

ADD

COLOMNS(d

INT,e

STRING);

4）、改变表的数据存储格式Java代码

ALTER

TABLE

test

SET

FILEFORMAT

SEQUENCEFILE;

5）、添加分区PartitionJava代码

ALTER

TABLE

students

ADD

PARTITION(ds

=

'2013-05-07',country

=

'china')

LOCATION

'/usr/test/data/test.txt';

6）、删除分区PartitionJava代码

ALTER

TABLE

students

DROP

PARTITION(ds

=

'2013-05-07',country

=

'china');

7）、创建函数Java代码

CREATE

TEMPORARY

FIUNCTION

aFunc

as

class_name;

8）、删除函数Java代码

DROP

TEMPORARY

FIUNCTION

aFunc;

9）、显示表Java代码

SHOW

TABLES

students;

9）、显示分区Java代码

SHOW

PARTITIONS

students;

本节继续讨论HiveQL，Hive查询语言，如何向Hive表中添加数据，操纵数据和从表中将数据提取到文件系统。

之前已经学习过Hive表的创建，本节主要关注向表中填充数据，使表中有数据可供查询。

先行创建本节要使用的表employees：Java代码

CREATE

TABLE

employees

(

name

STRING,

salary

FLOAT,

subordinates

ARRAY<STRING>

COMMENT

'下属',

deductions

MAP<STRING,FLOAT>

COMMENT

'扣费',

address

STRUT<street:STRING,city:STRING,state:STRING,zip:INT>

)

PARTITIONED

BY(country

STRING,state

STRING);

向管理表(非外部表)中加载数据

由于Hive没有行级的insert,update和delete操纵，向表中加载数据的唯一方法就是“批量”加载数据。如下示例，想一个分区表中加载一批数据：Java代码

LOAD

DATA

LOCAL

INPATH

'${env:HOME}/calafornia-employees'

OVERWRITE

INTO

TABLE

employees

PARTITION

(country

=

'US',

state

=

'CA');

在加载数据之前首先要要保证已经创建有符合条件的分区。在这个示例中，数据被加载后再Hive中的目录为：

hdfs://master_server/user/hive/warehouse/mydb.db/employees/country=US/state=CA

这个示例需要注意几点：

关键字OVERWRITE：加上该词说明如果之前已经箱盖分区中加载过数据的话，则之前的数据会首先被“清洗掉”，然后才加载新数据；如果没有加关键字OVERWRITE的话，如：Java代码

LOAD

DATA

LOCAL

INPATH

'${env:HOME}/calafornia-employees'

INTO

TABLE

employees

PARTITION

(country

=

'US',

state

=

'CA');

就不会将已有的数据清洗，而是直接在原有数据后边追加新的数据。

关键字LOCAL：表明是从本地文件系统的文件夹中加载数据，如果不加LOCAL关键字，则表明是从HDFS系统的文件夹中加载数据：Java代码

LOAD

DATA

INPATH

'${env:HOME}/calafornia-employees'

INTO

TABLE

employees

PARTITION

(country

=

'US',

state

=

'CA');

另外一点就是，对于要指定要加载的文件的路径，指定一个文件夹是比较符合常规的，这要比每次单独指定文件夹中的一个具体的文件要好，这样Hive会一次把指定文件夹下的所有的位安全都拷贝到Hive仓库中。

Hive在加载数据时并不会管要加载的数据是否和表定义模式相匹配，但是会验证文件格式，比如说，在表中第一的保存的数据文件为SEQUENCEFILE，那么加载后的文件就必须为SEQUENCEFILE文件。

通过HiveQL查询语句添加数据

INSERT语句可以让用户通过一个HiveQLQuery语句向Hive表中插入数据。看下面的一个示例(这里假设已经定义过表staged_employees)：Java代码

INSERT

OVERWRITE

TABLE

employess

PARTTITION

(country

=

'US',state

=

'OR')

SELECT

*

FROM

staged_employees

se

WHERE

ty

=

'US'

AND

se.st

=

'OR';

OVERWRITE关键字，我们已经讨论过。但对于本示例来说，如果去掉OVERWRITE或用INTO替换掉，如：Java代码

INSERT

INTO

TABLE

employess

PARTTITION

(country

=

'US',state

=

'OR')

SELECT

*

FROM

staged_employees

se

WHERE

ty

=

'US'

AND

se.st

=

'OR';

那么Hive就会将数据以“追加”的方式插入数据到employess表。

这个示例在一个场景中特别有用：数据已经被保存在另外一个表中，如Hive的外部表，然后用户想把某些数据做最终的保存到分区表中；或者从原有的数据源表中将数据保存成符合用的要求的不同的数据记录的格式。

然而，如果staged_employees中数据特别大，有可能你需要执行很多次这样的插入查询导入数据的话，比如说美国部分的数据就要执行65次之多。Hive提供了另外一种INSERT语法，是你只扫描一次表就可以将数据插入到对应的分区中：Java代码

ROM

staged_employees

se

INSERT

INTO

TABLE

employess

PARTTITION

(country

=

'US',state

=

'OR')

SELECT

*

WHERE

ty

=

'US'

AND

se.st

=

'OR';

INSERT

INTO

TABLE

employess

PARTTITION

(country

=

'US',state

=

'CA')

SELECT

*

WHERE

ty

=

'US'

AND

se.st

=

'CA';

INSERT

INTO

TABLE

employess

PARTTITION

(country

=

'US',state

=

'IL')

SELECT

*

WHERE

ty

=

'US'

AND

se.st

=

'IL';

...

这种INSERT语法也可以一次插入多个表。

动态分区添加数据

就上例来说，好友一个问题，那就是如果有特别多的分区要创建的话，用户不得不要写许多HiveQL语句！幸运的是，Hive提供一个叫动态分区的功能，可以基于用户的查询参数推断要创建的分区。通过与动态分区相比较，之前我们讨论的分区插入数据被称为静态分区数据导入。

将上面的静态分区作如下修改，就变成动态分区数据导入的示例：Java代码

INSERT

OVERWRITE

TABLE

employees

PARTITION

(country,state)

SELECT

...,ty,se.st

FROM

staged_employees

se

对于本例来说，Hive会通过SELECT语句中最后两列ty和se.st的值来决定employees表中的分区的key，contry和state。Hive在动态分区插入操作中特别强调源表的数据列的值和要插入分区的key的值的位置关系，而不是通过名字匹配，这也是为什么在staged_employees表中给国家和州定义不同的名字的原因。

假设staged_employees有100个国家和州的匹配对儿，那么执行玩这个HiveQL语句后，employees就会有100个国家分区！

用户也可以混合使用动态分区和静态分区数据插入，如下例中，我们是用了一个静态的country(US)和动态的州的值：Java代码

INSERT

OVERWRITE

TABLE

employees

PARTITION

(country

=

'US',state)

SELECT

...,ty,se.st

FROM

staged_employees

se

WHERE

ty

=

'US';

要注意的是，必须要把静态分区的key放在动态分区的key之前。

默认请情况下，动态分区插入的功能是被禁用的，当被激活后，Hive默认会工作在“严格(strict)”模式下。在“严格(strict)”模式下，必须使用静态分区和动态分区混合使用的方式，这主要是避免一些不好的数据查询设计。

要使用动态模式，用户首先要激活动态分区的一些参数设置:Java代码

hive>

set

hibe.exec.dynamic.partition=true;

hive>

set

hibe.exec.dynamic.mode=nonstrict;

hive>

set

hibe.exec.max.dynamic.partitions.pernode=1000;

hive>

INSERT

OVERWRITE

TABLE

employees

PARTITION(country,state)

>

SELECT

...,ty,se.st

FROM

staged_employees

se;

nonstrict可以完全由用户的查询参数来动态创建所有的分区。

在一个HiveQL中创建表和加载数据

在Hive中，用户可以完全使用一条语句创建表并同时加载数据：Java代码

CREATE

TABLE

ca_employees

AS

SELECT

name

salary,address

FROM

employees

WHERE

state

=

'CA';

这种方法特别适用于在一个大表中提取一个子数据集的场景，这种功能不适合外部表，因外外部表是在定义时直接给其制定一个数据文件的路径。

导出数据

之前讲的都是如何将数据加载到Hive表中，那么如何将表中数据导出表呢？用户可以使用INSERT...DIRECTORY...语句导出数据，示例如下：Java代码

INSERT

OVERWRITE

LOCAL

DIRECTORY

'tmp/ca_employees'

SELECT

name,salary,address

FROM

employees

WHERE

state

=

'CA';

其中OVERWRITE和LOCAL的意义同上。

和加载数据一样，用户同样可以在一个语句中将数据导出到多个文件夹：Java代码

FROM

staged_employees

se

INSERT

OVERWRITE

DIRECTORY

'/tmp/or_employees'

SELECT

*

FROM

ty

=

'US'

AND

se.st

='OR'

INSERT

OVERWRITE

DIRECTORY

'/tmp/CA_employees'

SELECT

*

FROM

ty

=

'US'

AND

se.st

='CA'

INSERT

OVERWRITE

DIRECTORY

'/tmp/IL_employees'

SELECT

*

FROM

ty

=

'US'

AND

se.st

='IL'

前几章已经学习过Hive表的定义和数据操纵，本章我们开始学习HiveQL查询。

SELECT...FROM...查询

SELECT在SQL中是一个投影操作。让我们从新来看之前定义过的分区表employees：Java代码

CREATE

TABLE

employees

(

name

STRING,

salary

FLOAT,

subordinates

ARRAY<STRING>

COMMENT

'下属',

deductions

MAP<STRING,FLOAT>

COMMENT

'扣费',

address

STRUT<street:STRING,city:STRING,state:STRING,zip:INT>

)

PARTITIONED

BY(country

STRING,state

STRING);

SELECT查询：Java代码

hive>

SELECT

name,salary

FROM

employees;

John

Doe

100000.0

Mary

Smith

80000.0

Todd

Jones

70000.0

Bill

King

60000.0

用户也可以给FROM之后的表，视图或子查询起一个别名，如：Java代码

hive>

SELECT

,e.salary

FROM

employees

e;

上面两个HiveQL语句是相同的，给表起别名在JOIN操作中特别有用。

下面我们来看如何查询employees表中的集合类型的数据。我们先看一下如何查询ARRAY类型的数据，如employees表的下属“subordinates”Java代码

hive>

SELECT

name,subordinates

FROM

employees;

John

Doe

["Mary

Smith","Todd

Jones"]

Mary

Smith

["Bill

King"]

Todd

Jones

[]

Bill

king

[]

再看MAP类型的查询，如“deductions”：Java代码

hive>

SELECT

name,deductions

FROM

employees;

John

Doe

{"Federal

Taxes":0.2,"State

Taxes":0.05,"Insurance":0.1}

Mary

Smith

{"Federal

Taxes":0.2,"State

Taxes":0.05,"Insurance":0.1}

Todd

Jones

{"Federal

Taxes":0.15,"State

Taxes":0.03,"Insurance":0.1}

Bill

King

{"Federal

Taxes":0.15,"State

Taxes":0.03,"Insurance":0.1}

再看STRUCT类型的查询，如“address”：Java代码

hive>

SELECT

name,address

FROM

employees;

John

Doe

{"Street":"1

Michign

Ave.","city":"Chicago","State":"IL","ZIP":60600}

Mary

Smith

{"Street":"100

Ontario

St.","city":"Chicago","State":"IL","ZIP":60601}

Todd

Jones

{"Street":"200

Chicago

Ave.","city":"Oak

Park","State":"IL","ZIP":60700}

Bill

King

{"Street":"300

Obscure

Dr.","city":"Obscuria","State":"IL","ZIP":60100}

接下来我们再看如何查看集合性属性字段中的数据：Java代码

hive>

SELECT

name,subordinates[0],deductions["State

Taxes"],address.city

FROM

employees;

John

Doe

Mary

Smith

0.05

Chicago

Mary

Smith

Bill

King

0.05

Chicago

Todd

Jones

NULL

0.03

Oak

Park

Bill

King

NULL

0.03

Obscuria

使用正则表达式查询符合条件的列

在Hive查询中，用户可以使用正则表达式查询符合条件的列，下面的实例中就是使用正则表达式的使用用例，可以查询到symbol列和所有以“price”开头的列：Java代码

hive>

SELECT

symbol,'price.*'

FROM

stocks;

AAPL

195.69

197.88

194.0

194.12

194.12

AAPL

192.63

196.0

190.85

195.46

195.46

AAPL

196.73

198.37

191.57

192.05

192.05

AAPL

195.17

200.2

194.42

199.23

199.23

AAPL

195.91

196.32

193.38

195.86

195.86

...

列计算

在HiveQL中，用户不但可以从表中查询某些列，还可以通过函数或数学表达式来计算列的值。例如，我们可以在employees表中查询雇员的姓名，薪水，联邦税百分百及其他列的值：Java代码

hive>

SELECT

upper(name),salary,deductions["Federal

Taxes"],

>

round(salary

*

(1

-

deductions["Federal

Taxes"]))

>

FROM

employees;

JOHN

DOE

100000.0

0.2

80000

MARY

SMITH

80000.0

0.2

64000

TODD

JONES

70000.0

0.15

59500

BILL

KING

60000.0

0.15

51000

Hive是使用JAVA写的开源软件，在函数或数学表达式来计算列的值时类型转型和JAVA的转型相同。

聚合函数

要在HiveQL查询中使用聚合函数，必须先将hive.map.aggr配置参数设置为true，举例如下：Java代码

hive>

SET

hive.map.aggr=true;

hibe>

SELECT

count(*),avg(salary)

FROM

employees;

但是将Java代码

hive.map.aggr

设置为true会占用更多的内存。

LIMIT

一次典型的HiveQL查询可能会返回所有符合条件的数据记录，但是LIMIT关键字可以限制返回的记录的条数：Java代码

hive>

SELECT

upper(name),salary,deductions["Federal

Taxes"],

>

round(salary

*

(1

-

deductions["Federal

Taxes"]))

>

FROM

employees

>

LIMIT

2;

JOHN

DOE

100000.0

0.2

80000

MARY

SMITH

80000.0

0.2

64000

给列奇别名

Java代码

hive>

SELECT

upper(name),salary,deductions["Federal

Taxes"]

AS

>

fed_taxes,round(salary

*

(1

-

deductions["Federal

Taxes"]))

AS

>

salary_minus_fed_taxes

>

FROM

employees

>

LIMIT

2;

JOHN

DOE

100000.0

0.2

80000

MARY

SMITH

80000.0

0.2

64000

子查询

给列起别名特别适合与子查询中的列，让我们将上个查询示例修改为子查询的使用用例：Java代码

hive>

FROM(

>

SELECT

upper(name),salary,deductions["Federal

Taxes"]

AS

>

fed_taxes,round(salary

*

(1

-

deductions["Federal

Taxes"]))

>

AS

salary_minus_fed_taxes

>

FROM

employees

>

)

e

>

SELECT

,e.salary_minus_fed_taxes

>

WHERE

e.salary_minus_fed_taxes

>

70000;

JOHN

DOE

100000.0

0.2

80000

CASE...WHEN...THEN语句

CASE...WHEN...THEN向标准的SQL语句中一样使用在SELECT列中，对某一个列的返回值做判断，示例如下：Java代码

hive>

SELECT

name,salary,

>

CASE

>

WHEN

salary

<

50000.0

THEN

'low'

>

WHEN

salary

>=

50000.0

AND

salary

<

70000.0

THEN

'middle'

>

WHEN

salary

>=

70000.0

AND

salay

<

100000.0

THEN

'high'

ELSE

'very

high'

>

END

AS

bracket

FROM

employees;

John

Doe

100000.0

very

high

Mary

Smith

80000.0

high

Todd

Jones

70000.0

high

Bill

King

60000.0

middle

Boss

Man

200000.0

very

high

Fred

Finance

150000.0

very

high

Stcy

Accountant

60000.0

middle

WHERE过滤条件

SELECT决定返回哪些数据列，而WHERE决定返回那些符合条件的数据：Java代码

hive>

SELECT

name,salary,deductions["Federal

Taxes"],

>

salary

*

(1

-

deductions["Federal

Taxes"])

>

FROM

employees

>

WHERE

round(salary

*

(1

-

deductions["Federal

Taxes"]))

>

>

70000;

ohn

Doe

100000.0

0.2

80000.0

该示例有一个问题，那就是salary*(1-deductions["FederalTaxes"])分别在SELECT部分和WHERE部分都执行了，性能上不是多优化。那么，对salary*(1-deductions["FederalTaxes"])使用别名能否消除这种冲突呢？，不幸的是这是无效的：Java代码

hive>

SELECT

name,salary,deductions["Federal

Taxes"],

>

salary

*

(1

-

deductions["Federal

Taxes"])

AS

>

salary_minus_fed_taxes

>

FROM

employees

>

WHERE

round(salary_minus_fed_taxes)

>

70000;

FAILED:Error

in

semantic

analysis:

Line

4:13

Invalid

table

alias

or

colomn

reference

'salary_minus_fed_taxes':

(possible

colomn

names

are:

name,salary,subordinates,deductions,address)

如错误信息中所说，用户不能在WHERE部分中引用列的别名，那么我们是否可以使用其他办法来消除这种冲突呢？答案是使用子查询：Java代码

hive>

SELECT

e.*

FROM

>

(SELECT

name,salary,deductions["Federal

Taxes"]

AS

ded,

>

salary

*

(1

-

deductions["Federal

Taxes"])

AS

>

salary_minus_fed_taxes

>

FROM

employees)

e

>

WHERE

round(salary_minus_fed_taxes)

>

70000;

浮点比较陷阱

在WHERE查询条件中：在比较不同类型的数值(如FLOATvsDOUBLE)时，会引发浮点比较陷阱。

看下面的HiveQL语句，本来只想要查询FederalTaxes>0.2，但是返回结果如下：Java代码

hive>

SELECT

name,salary,deductions['Federal

Taxes']

>

FROM

employees

WHERE

deductions['Federal

Taxes']

>

0.2;

John

Doe

100000.0

0.2

Mary

Smith

80000.0

0.2

Boss

Man

200000.0

0.3

Fred

Finance

150000.0

0.3

我们发现，为什不符合过滤条件(deductions('FederalTaxes')==0.2)的结果也返回了？！这就是浮点比较陷阱引发的。

那么如何来避免这个陷阱呢？那就要将0.2强制转型了：Java代码

hive>

SELECT

name,salary,deductions['Federal

Taxes']

>

FROM

employees

>

WHERE

deductions['Federal

Taxes']

>

cast(0.2

AS

FLOAT);

Boss

Man

200000.0

0.3

Fred

Finance

150000.0

0.3

LIKE和RLIKE

LIKE标识模糊查询：Java代码

hive>

SELECT

name,address.street

FROM

employees

WHERE

address.street

LIKE

'%Ave.';

John

Doe

1

Michigan

Ave.

Todd

Hones

200

Chicago

Ave.

hive>

SELECT

name,address.street

FROM

employees

WHERE

address.street

LIKE

'%Chi%';

Todd

Hones

200

Chicago

Ave.

RLIKE是使用正则表达式：Java代码

hive>

SELECT

name,address.street

FROM

employees

>

WHERE

address.street

RLIKE

'.*(Chicago|Ontario).*';

Mary

Smith

100

Ontario

St.

Todd

Jones

200

Chicago

Ave.

GROUPBY

Hive的GROUPBY语句和传统的SQL相同，经常要和聚合函数一块儿使用，我们再看一下stocks表的定义：Java代码

CREAT

EXTENAL

TABLE

IF

NOT

EXISTS

stocks(

exchange

STRING,

symbol

STRING,

ymd

STRING,

price_open

FLOAT,

price_high

FLOAT,

price_low

FLOAT,

price_close

FLOAT,

volume

INT,

price_adj_close

FLOAT)

ROW

FORMAT

DELIMITED

FIELDS

TERMINATED

BY

','

LOCATION

'/data/stocks';

示例如下：Java代码

hive>

SELECT

year(ymd),avg(price_close)

FROM

stocks

>

WHERE

exchange

=

'NASDAQ'

AND

symbol

=

'AAPL'

>

GROUP

BY

year(ymd);

1984

25.578625440597534

1985

20.193676221040867

1986

32.46102808021274

...

HAVING

Hive中的HAVING关键字和传统SQL中的概念相同，是对分组后的结果再次过滤。使用HAVING可以避免GROUPBY后的子查询：Java代码

hive>

SELECT

year(ymd),avg(price_close)

FROM

stocks

>

WHERE

exchange

=

'NASDAQ'

AND

symbol

=

'AAPL'

>

GROUP

BY

year(ymd)

>

HAVING

avg(price_close)

>

50.0;

1987

53.88968399108163

1991

52.49553383386182

1992

54.80338610251119

2000

71.74892876261757

...

如果不使用HAVING，那么就要使用子查询：Java代码

hive>

SELECT

s2.year,s2.avg

FROM

>

(SELECT

year(ymd)

AS

year,avg(price_close)

AS

avg

FROM

stocks

>

WHERE

exchange

=

'NASDAQ'

AND

symbol

=

'AAPL'

>

GROUP

BY

year(ymd)

>

)

s2

>

WHERE

s2.avg

>

50.0

JION

Hive支持典型的SQLJION连接，但只支持等值连接。

Hive中的内连接、左外连接、右外连接和全外连接和标准的SQL中的连接有相同的概念；但是在Hive中没有IN、EXISTS关键字的使用，取代这两个关键字的功能的是LEFTSEMI-JION，也是对InnerJION的性能上的加强优化。

LEFTSEMI-JION

LEFTSEMI-JION和MySQL方言中的IN...EXISTS...结构做同样的事情：Java代码

hive>

SELECT

s.ymd,s.symbol,s.price_close

>

FROM

stocks

s

LEFT

SEMI

JION

dividends

d

ON

s.ymd

=

d.ymd

AND

s.symbol

=

s.symbol;

...

1962-11-05

IBM

361.5

1962-08-07

IBM

373.25

1962-05-08

IBM

459.5

...

LEFTSEMIJOIN的限制是，JOIN子句中右边的表只能在ON子句中设置过滤条件，在WHERE子句、SELECT子句或其他地方过滤都不行。

JION优化

1)、将小表放在JION的左边，Hive会将JION左边的小表中的数据缓存起来，然后流式(stream)处理最后的表中的数据，这可以提高HiveQL查询的性能：Java代码

SELECT

s.ymd,s.symbol,s.price_close,d.dividend

FROM

dividend

d

JION

stocks

s

ON

s.ymd

=

d.ymd

AND

s.symbol

=

d.symbol

WHERE

s.symbol

=

'AAPL';

幸运的是，用户不用必须把要流式(stream)处理的表放在JION的右边，Hive提供了一个“hint”机制来告诉查询优化器那个表中的数据需要被流式(stream)处理：Java代码

SELECT

/*+

STREAMTABLE(s)

*/

s.ymd,s.symbol,s.price_close,d.dividend

FROM

stocks

s

JION

dividend

d

ON

s.ymd

=

d.ymd

AND

s.symbol

=

d.symbol

WHERE

s.symbol

=

'AAPL';

2)、在多个表做JION连接时，如果ON字句中的jionkeys都相同时，Hive会把多个table的jion连接编译为一个MapReduceJob，否则一次jion连接会编译成一个job。

3)、Map-SideJion：Hive提供Map端的jion连接操作，默认情况下是不支持该操作的，用户必须强制告知查询优化器做Map端的Jion连接操作，这可以避免对结果进行大规模的笛卡尔集的操作，也可以减少数据的I/O流量：Java代码

SELECT

/*+

MAPJION(s)

*/

s.ymd,s.symbol,s.price_close,d.dividend

FROM

stocks

s

JION

dividend

d

ON

s.ymd

=

d.ymd

AND

s.symbol

=

d.symbol

WHERE

s.symbol

=

'AAPL';

当然做Map端的Jion连接操作之前，必须要设置一些参数：Java代码

hive>

SET

hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;;

hive>

SET

hive.optimize.bucketmapjion=true;

hive>

SET

hive.optimize.bucketmapjion.sortedmerge=true;

ORDERBY和SORTBY

ORDERBY和SQL方言中的orderby语句很像，会对所有的结果集做整体的排序操作，这就意味着所有的数据是通过一个Reducer处理的；对处理非常大的数据及来说，这会执行非常长的处理时间。

Hive提供了一种局部排序的功能——SORTBY，只对每个Reducer处理的局部数据排序，也是一个本地排序，这也就是说每个Reducer处理后的数据是排序的，但对整体而言是无序的。Java代码

SELECT

s.ymd,s.symbol,s.price_close

FROM

stocks

s

ORDER

BY

s.ymd

ASC,s.symbol

DESC;

SELECT

s.ymd,s.symbol,s.price_close

FROM

stocks

s

SORT

BY

s.ymd

ASC,s.symbol

DESC;

DISTRIBUTEBY

我们都知道，MapReduce模型是默认是通过hashPartitioner()函数将key/value的keys的hash值来数据分发到对应的Reducers，DISTRIBUTEBY可以让用户来控制如何将key/value对分发到哪个Reducer。

这在大多数的情况下通常很有用。但是在默写情况下，用户可能需要有自己来决定按某一个字段来将数据分发到Reducers，如下所示：Java代码

hive>

SELECT

s.ymd,s.symbol,s.price_close

FROM

stocks

s

>

DISTRIBUTE

BY

s.symbol

>

SORT

BY

s.symbol

ASC,s.ymd

ASC;

需要注意的是，DISTRIBUTEBY通常要和SORTBY使用，并且DISTRIBUTEBY必须使用在SORTBY之前。

CLUSTERBY

在上个使用DISTRIBUTEBY的示例中，由DISTRIBUTEBY按s.symbol字段将数据分发到相应的Reducers，然后又SORTBY对Reducer最终输出的数据按s.symbol和s.ymd做升序排序。

CLUSTERBY关键字可以起到DISTRIBUTEBY和SORTBY相同的功能：Java代码

hive>

SELECT

s.ymd,s.symbol,s.price_close

FROM

stocks

s

>

CLUSTER

BY

s.symbol；

使用DISTRIBUTEBY和CLUSTERBY都可以起到对Reducers的输出做并行排倒序。

类型转化(Casting)

之前我们已经学习过数据类型转化的示例，如将一个数值转化为FLOAT类型，转换语法为cast(valueASTYPE)，比如：Java代码

SELECT

name,salary

FROM

employees

WHERE

cast(salary

AS

FLOAT)

<

100000.0

如果salary不能转换成FLOAT类型的值的话，Hive会返回NULL。

转换二进制类型的值

从Hive0.80开始，Hive开始支持二进制类型的值的转换。将BINARY的值b转换成STRING：Java代码

SELECT

(2.0

*

cast(cast(b

AS

STRING)

AS

DOUBLE))

FROM

src;

同样也可以讲STRING类型转换成BINARY类型。

采样查询

有些时候，用户并不想对一个大数据集的数据做全部的分析处理，对于这种情况，Hive提供了对buckettable的采样查询功能：Java代码

hive>

SELECT

*

FROM

numbers

TABLESAMPLE(BUCKET

1

OUT

OF

2

ON

number)s;

Hive中的桶下标是从1开始的。

Block数据块数据采样(BlockSampling)

Hive还提供了另外一种采样的语法——Block数据块数据采样，用户可以一个数据块的数据行(rows)采样：Java代码

hive>

SELECT

*

FROM

numbersflat

TABLESAMPLE(0.1

PERCENT)

s;

UNIONALL

UNIONALL可以将两个或多个子查询的结果集合并到一块儿，这就要就要合并的结果集必须要有相同的列数，并且相对应的列要有相匹配的类型：Java代码

SELECT

log.ymd,log.level,log.message

FROM(

SELECT

l1.ymd,l1.level,l1.message,'log1'

AS

source

FROM

log1

l1

UNION

ALL

SELECT

l2.ymd,l3.level,l2.message,'log2'

AS

source

FROM

log2

l2

)

log

SORT

BY

log.ymd

ASC

Hive提供有限的索引功能，这不像传统的关系型数据库那样有“键(key)”的概念，用户可以在某些列上创建索引来加速某些操作，给一个表创建的索引数据被保存在另外的表中。

Hive的索引功能现在还相对较晚，提供的选项还较少。但是，索引被设计为可使用内置的可插拔的java代码来定制，用户可以扩展这个功能来满足自己的需求。

当然不是说有的查询都会受惠于Hive索引。用户可以使用EXPLAIN语法来分析HiveQL语句是否可以使用索引来提升用户查询的性能。像RDBMS中的索引一样，需要评估索引创建的是否合理，毕竟，索引需要更多的磁盘空间，并且创建维护索引也会有一定的代价。

用户必须要权衡从索引得到的好处和代价。

创建索引

现在让我们来为分区表employees创建一个索引。首先，然我们再回顾一下employees东侧表定义：Java代码

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);

接着让我们看看如何为该表的contry分区创建索引：Java代码

CREATE

INDEX

employees_index

ON

TABLE

employees

(country)

AS

'pact.CompactIndexHandler'

WITH

DEFERRED

REBUILD

IDXPROPERTIES

('creator'

=

'me','created_at'

=

'some_time')

IN

TABLE

employees_index_table

PARTITIONED

BY

(country,name)

COMMENT

'Employees

indexed

by

country

and

name.';

在本示例中，在employees上创建了名为employees_index的索引，索引数据存放在employees_index_table索引表中，WITHDEFERREDREBUILD表明创建一个空索引，可以在之后使用如下语句创建索引数据：Java代码

ALTER

INDEX

employees_index

ON

TABLE

employees

PARTITION(country

=

'US')

REBUILD;

PARTITIONEDBY表明只对某个分区创建索引，若没有该选项则表示对所有分区都创建索引，另外要注意的是index的分区索引默认是和表的分区一致的，也不能对视图VIEW创建索引。AS'pact.CompactIndexHandler'表示使用Apache的pact.CompactIndexHandler作为创建索引的handler，当然也可以使用第三方的实现类或其他的实现类。

当然也可以对其他字段创建索引。

Bitmap位图索引

Hivev0.80添加了一个内置的bitmap位图索引。Bitmap位图索引通常适用于只有少数不同值的列创建索引。现在我们修改上一个索引示例为位图索引：Java代码

CREATE

INDEX

employees_index

ON

TABLE

employees

(country)

AS

'BITMAP'

WITH

DEFERRED

REBUILD

IDXPROPERTIES

('creator'

=

'me','created_at'

=

'some_time')

IN

TABLE

employees_index_table

PARTITIONED

BY

(country,name)

COMMENT

'Employees

indexed

by

country

and

name.';

重建索引

如果用户在创建索引时指定WITHDEFERREDREBUILD关键字，那么开始时是一个空索引。我们在任何时候使用ALTERINDEX语句来创建或重建索引：Java代码

ALTER

INDEX

employees_index

ON

TABLE

employees

PARTITION(country

=

'US')

REBUILD;

Hive没有提供一个内置的在数据变更时自动触发创建索引的机制，故用户需要自己通过ALTERINDEX语句来创建索引；另外，重建索引操作是一个原子操作，因此当rebuild失败时，已构建的索引也无法使用。

查看索引

用户可以查看某个表上的所有的索引：Java代码

SHOW

FORMATTED

INDEX

ON

employees;

SHOW

FORMATTED

INDEXES

ON

employees;

删除索引

在删除一个索引的时候，也会同时删除索引数据所在的表，如：Java代码

DROP

INDEX

IF

EXISTS

employees_index

ON

TABLE

employees;

Hive使用的是Hadoop的文件系统和文件格式，比如TEXTFILE，SEQUENCEFILE等。

在Hive中对中间数据或最终数据数据做压缩，是提高数据吞吐量和性能的一种手段。对数据做压缩，可以大量减少磁盘的存储空间，比如基于文本的数据文件，可以将文件压缩40%或更多，同时压缩后的文件在磁盘间传输和I/O也会大大减少；当然压缩和解压缩也会带来额外的CPU开销，但是却可以节省更多的I/O和使用更少的内存开销。

Hadoopjobs作业，往往是I/O密集型的，而非CPU集型的。数据压缩对I/O密集型的作业带来大大的性能提升，但是如果用户的jobs作业时CPU密集型的，那么在使用压缩就会降低性能，这就要用户对作业的类型做判断，权衡是否要对数据做压缩。

选择合适的编解码器

对数据做压缩，可以最大程度的减少文件所需的磁盘空间和网络I/O的开销，但对数据做压缩和解压缩总会增加CPU的开销，故最好对那些I/O密集型的作业使用数据压缩——这样的作业会有富余的CPU资源，或者对那些磁盘空间不富裕的系统。

Hadoop默认支持Gzip和BZip2的解压缩方式，包括原生的linux解压缩库。Snappy压缩在最近的Hive版本中才被添加的压缩算法，如果您使用的是Hive不支持Snappy的话，用户就要手动添加。过去经常使用的是LZO压缩算法。

那么，为什么要使用不同的压缩算法呢？它们都有什么区别呢？BZip2有最高的压缩比但也会带来更高的CPU开销，Gzip较BZip2次之；如果基于磁盘利用率和I/O考虑，这两个压缩算法都是比较有吸引力的算法。

LZO和Snappy算法有更快的解压缩速度，如果在关注数据解压多于磁盘利用率和I/O开销的场景中，它们都是不错的选择。LZO和Snappy在压缩数据上大致相当，但Snappy算法在解压速度上要较LZO更快。

在选择解压缩算法时，另外一个比较重要的考虑就是压缩格式是否是支持可分割的。Hadoop的会将大文件分割成HDFSblock(默认64MB)大小的splits分片，每个分片对应一个Mapper程序。在这几个压缩算法中，只有BZip2和LZO提供block级的压缩，而Gzip和Snappy则不支持。

Snappy压缩算法时首选。

打开Hive(即由mappe产生的)中间数据文件的压缩功能

HiveQL语句最终会被编译成Hadoop的Mapreducejob，开启Hive的中间数据压缩功能，就是在MapReduce的shuffle阶段对mapper产生的中间结果数据压缩。在这个阶段，优先选择一个低CPU开销的算法。

可以通过参数ermediate来开启和禁用该功能，默认情况下该值值为false，将之设置为true为激活中间数据压缩功能：Java代码

<property>

<name>ermediate</name>

<value>true</value>

<description>This

controls

whether

intermediate

files

produced

by

Hive

between

mutiple

map-redece

job

are

compressed.The

compression

codec

and

other

options

are

determined

from

hadoop

config

variable