工作时，数据统计分析、挖掘的时候用到很多Hive方面的内容，就做了一个完整的整理文档。

·


hive


是基于Hadoop

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

·

Hive


定义了


简单的类


SQL


查询语言，称为


HQL


，它允许熟悉


SQL


的用户查询数据。

·

Hive


将用户的


HiveQL


语句通过解释器转换为


MapReduce


作业提交到


Hadoop


集群上，


Hadoop


监控作业执行过程，然后返回作业执行结果给用户。


Hive


并非为联机事务处理而设计，


Hive


并不提供实时的查询和基于行级的数据更新操作


。


Hive


的最佳使用场合是大数据集的批处理作业，例如，网络日志分析。

Tip1： Hadoop

是一个开源框架来存储和处理大型数据在分布式环境中。它包含两个模块，一个是MapReduce，另外一个是Hadoop分布式文件系统（HDFS）


。

•




MapReduce


：



一种并行编程模型在大型集群普通硬件可用于处理大型结构化，半结构化和非结构化数据。

•



HDFS


：



Hadoop


分布式文件系统是


Hadoop


的框架的一部分，用于存储和处理数据集。它提供了一个容错文件系统在普通硬件上运行。

Hadoop


生态系统包含了用于协助


Hadoop


的不同的子项目（工具）模块，如


Sqoop, Pig


和


Hive


。

•



Sqoop:



它是用来在


HDFS


和


RDBMS


之间来回导入和导出数据。

•



Pig:





它是用于开发


MapReduce


操作的脚本程序语言的平台。

•



Hive:





它是用来

开发




SQL


类型脚本



用于做


MapReduce


操作的平台。

注：有多种方法来执行


MapReduce


作业



：

•


传统的方法是使用


Java MapReduce


程序结构化，半结构化和非结构化数据。

•


针对


MapReduce


的脚本的方式，使用


Pig


来处理结构化和半结构化数据。

•


Hive


查询语言（


HiveQL


或


HQL


）采用


Hive


为


MapReduce


的处理结构化数据。

1


、用户接口

用户接口主要有三个：


CLI


，


Client


和


WUI


。其中最常用的是


CLI


，


Cli


启动的时候，会同时启动一个


Hive


副本。


Client


是


Hive


的客户端，用户连接至


Hive Server


。在启动


Client


模式的时候，需要指出


Hive Server


所在节点，并且在该节点启动


Hive Server


。


WUI


是通过浏览器访问


Hive


。

2


、元数据存储

Hive


将元数据存储在数据库中，如


mysql


、


derby


。


Hive


中的元数据包括


表的名字，表的列和分区及其属性，表的属性（是否为外部表等）


，表的数据所在目录等。

3


、解释器、编译器、优化器、执行器

解释器、编译器、优化器完成


HQL


查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在


HDFS


中，并在随后由


MapReduce


调用执行。

4


、


Hadoop

Hive


的数据存储在


HDFS


中，大部分的查询由


MapReduce


完成。

首先


Hive


没有专门的数据存储格式，也没有为数据建立索引，用户可以非常自由的组织


Hive


中的表，只需要在创建表的时候告诉


Hive


数据中的列分隔符和行分隔符，


Hive


就可以解析数据。

其次，


Hive


中所有的数据都存储在


HDFS


中


，


Hive


中包含以下数据模型：


表


(Table)


，外部表


(External Table)


，分区


(Partition)


，桶


(Bucket)


。

Hive


中的


Table


和数据库中的


Table


在概念上是类似的，每一个


Table


在


Hive


中都有一个相应的目录存储数据。例如，一个表


pvs


，它在


HDFS


中的路径为：


/wh/pvs


，其中，


wh


是在


hive-site.xml


中由


${hive.metastore.warehouse.dir}


指定的数据仓库的目录，所有的


Table


数据（不包括


External Table


）都保存在这个目录中。

Partition


对应于数据库中的


Partition


列的密集索引，但是


Hive


中


Partition


的组织方式和数据库中的很不相同。在


Hive


中，表中的一个


Partition


对应于表下的一个目录，所有的


Partition


的数据都存储在对应的目录中。例如：


pvs


表中包含


ds


和


city


两个


Partition


，则对应于


ds = 20090801, ctry = US


的


HDFS


子目录为：


/wh/pvs/ds=20090801/ctry=US


；对应于


ds = 20090801, ctry = CA


的


HDFS


子目录为；


/wh/pvs/ds=20090801/ctry=CA

Buckets


对指定列计算


hash


，根据


hash


值切分数据，目的是为了并行，每一个


Bucket


对应一个文件。将


user


列分散至


32


个


bucket


，首先对


user


列的值计算


hash


，对应


hash


值为


0


的


HDFS


目录为：


/wh/pvs/ds=20090801/ctry=US/part-00000


；


hash


值为


20


的


HDFS


目录为：


/wh/pvs/ds=20090801/ctry=US/part-00020

External Table


指向已经在


HDFS


中存在的数据，可以创建


Partition


。它和


Table


在元数据的组织上是相同的，而实际数据的存储则有较大的差异。

Table


的创建过程和数据加载过程（这两个过程可以在同一个语句中完成），在加载数据的过程中，实际数据会被移动到数据仓库目录中；之后对数据对访问将会直接在数据仓库目录中完成。删除表时，表中的数据和元数据将会被同时删除。

CREATE DATABASE


语句

创建数据库是用来创建数据库在


Hive


中语句。在


Hive


数据库是一个命名空间或表的集合。此语法声明如下：

CREATEDATABASE


|


SCHEMA


[


IF NOT EXISTS


]


<


database name


>

IF NOT EXISTS


是一个可选子句，通知用户已经存在相同名称的数据库。

可以使用


SCHEMA


在


DATABASE


的这个命令。下面的查询执行创建一个名为


userdb


数据库：

hive


>


CREATE DATABASE


[


IF NOT EXISTS


]


userdb


;

或，


hive


>


CREATE SCHEMA userdb


;

以下的查询用于验证数据库列表：

hive


>


SHOW DATABASES


;

default

userdb

JDBC


程序

在


JDBC


程序（


HiveCreateDb.java


文件）来创建数据库如下。

import


java


.


sql


.


SQLException


;

import


java


.


sql


.


Connection


;

import


java


.


sql


.


ResultSet


;

import


java


.


sql


.


Statement


;

import


java


.


sql


.


DriverManager


;

public


class


HiveCreateDb


{

private


static


String


driverName


=


“org.apache.hadoop.hive.jdbc.HiveDriver”


;

public


static


void


main


(


String


[]


args


)


throws


SQLException


{

// Register driver and createdriver instance

Class


.


forName


(


driverName


);

// get connection

Connection


con


=


DriverManager


.


getConnection


(


“jdbc:hive://localhost:10000/default”


,


“”


,


“”


);

Statement


stmt


=


con


.


createStatement


();

stmt


.


executeQuery


(


“CREATEDATABASE userdb”


);

System


.


out


.


println


(“


Database


userdbcreated successfully


.”);

con


.


close


();

}

}

JDBC


：数据库连接，一个JAVA API

可访问任何类型表列数据，用于连接到数据库；创建SQL或MySQL语句；在数据库中执行SQL或MySQL查询；查看和修改数据库中的数据记录。

DROP DATABASE


语句

DROP DATABASE


是删除所有的表并删除数据库的语句。它的语法如下：

DROP DATABASEStatementDROP (DATABASE|SCHEMA) [IF EXISTS] database_name

[RESTRICT|CASCADE];

删除名称为userb

的数据库；

hive> DROP DATABASE IF EXISTS userdb;

使用


CASCADE


查询删除数据库。这意味着要全部删除相应的表在删除数据库之前；

hive> DROP DATABASE IF EXISTS userdb CASCADE;

使用


SCHEMA


查询删除数据库；

hive> DROP SCHEMA userdb;

另外也可通过JDBC

程序删除数据库。

Create Table


是用于在


Hive


中创建表的语句。语法和示例如下：

语法：

CREATE


[


TEMPORARY


]


[


EXTERNAL


]


TABLE


[


IF NOTEXISTS


]


[


db_name


.]


table_name

[(


col_namedata_type


[


COMMENT col_comment


],


…)]

[


COMMENTtable_comment


]

[


ROWFORMAT row_format


]

[


STOREDAS file_format


]

示例：



需使用


CREATE TABLE


语句创建一个名为


employee


表。


employee


表中的字段和数据类型如下：

下面的数据是一个注释，行格式字段，如字段终止符，行终止符，并保存的文件类型。

COMMENT


‘


Employee


details


’

FIELDSTERMINATED BY


‘


\t


’

LINES TERMINATEDBY


‘


\n


’

STORED IN TEXTFILE

下面的查询创建使用上述数据的表名为


employee


。

hive


>


CREATE TABLE IF NOT EXISTS employee


(


eid


int


,


name


String


,

>


salary


String


,


destination


String


)

>


COMMENT


‘


Employee


details


’

>


ROWFORMAT DELIMITED

>


FIELDSTERMINATED BY


‘


\t


’

>


LINESTERMINATED BY


‘


\n


’

>


STOREDAS TEXTFILE


;

如果添加选项


IF NOT EXISTS


，


Hive


忽略大小写，万一表已经存在的声明。

成功创建表后，能看到以下回应：

OK

Time


taken


:


5.905


seconds

hive


>

LOAD DATA


语句

一般来说，在


SQL


创建表后，可以使用


INSERT


语句插入数据。但在


Hive


中，可使用


LOAD DATA


语句插入数据。

同时将数据插入到


Hive


，最好是使用


LOAD DATA


来存储大量记录。有两种方法用来加载数据：一种是从本地文件系统，另一种是从


Hadoop


文件系统。

加载数据：

LOAD DATA


[


LOCAL


]


INPATH


‘filepath’


[


OVERWRITE


]


INTOTABLE tablename

[


PARTITION


(


partcol1


=


val1


,


partcol2


=


val2


…)]

•


LOCAL


是标识符指定本地路径。它是可选的。

•


OVERWRITE


是可选的，覆盖表中的数据。

•


PARTITION


这是可选的

例如；插入


在


/home/user


目录中名为


sample.txt


文件的数据。

hive


>


LOADDATA LOCAL INPATH


‘/home/user/sample.txt’

>


OVERWRITE INTO TABLE employee


;

修改表的属性，如，修改表名，修改列名，添加列，删除或替换列。

Alter Table


语句

语法：



声明接受任意属性，我们希望在一个表中修改以下语法。

ALTER TABLE name RENAME TOnew_name

ALTER TABLE name ADDCOLUMNS


(


col_spec


[,


col_spec


…])

ALTER TABLE name DROP


[


COLUMN


]


column_name

ALTER TABLE name CHANGEcolumn_name new_name new_type

ALTER TABLE name REPLACECOLUMNS


(


col_spec


[,


col_spec


…])

Rename To…


查询重命名表，



把


employee


修改为


emp

hive


>


ALTER TABLE employee RENAME TO emp


;

Change …


修改列名和列数据类型：

下表为例，显示


employee


表中字段要被更改（粗体）。

字段名	从数据类型转换	更改字段名称	转换为数据类型
eid	int	eid	int
name	String	ename	String
salary	Float	salary	Double
designation	String	designation	String

语句；

hive


>


ALTER TABLE employee CHANGE name ename


String


;

hive


>


ALTER TABLE employeeCHANGE salary salary


Double


;

添加列语句

下面的查询增加了一个列名


dept


在


employee


表。

hive


>


ALTER TABLE employee ADD COLUMNS


(

>


dept STRING COMMENT


‘Department name’


);

REPLACE


语句

以下从


employee


表中查询使用


empid


代替


eid


列，


name


代替


ename


列

hive


>


ALTER TABLE employee REPLACE COLUMNS


(

>


eid INT empid


Int


,

>


ename STRING name


String


);

Drop Table


语句

语法：

DROP TABLE


[


IFEXISTS


]


table_name


;

以下查询删除一个名为


employee


的表


:

hive


>


DROP TABLE IF EXISTSemployee


;

查询验证表的列表

hive


>


SHOW TABLES


;

emp

ok

Time


taken


:


2.1


seconds

分区含义；组织表到分区。它是将一个表到基于分区列，如日期，城市和部门的值相关方式。使用分区，很容易对数据进行部分查询。

表或分区是细分成桶，以提供额外的结构，可以使用更高效的查询的数据。桶的工作是基于表的一些列的散列函数值。

例如，一个


Tab1


表包含雇员数据，如


id, name, dept


和


yoj (


即加盟年份


)


，如果用年份分区雇员数据并将其存储在一个单独的文件，它减少了查询处理时间。下例表示分区的文件和数据；

employee


数据表中，

/tab1/employeedata/file1

id, name, dept, yoj

1, gopal, TP, 2012

2, kiran, HR, 2012

3, kaleel,SC, 2013

4, Prasanth, SC, 2013

上面的数据被划分成使用年两个文件。

/tab1/employeedata/2012/file2

1, gopal, TP, 2012

2, kiran, HR, 2012

/tab1/employeedata/2013/file3

3, kaleel,SC, 2013

4, Prasanth, SC,2013

添加分区

可以通过添加分区表改变所述表。假设我们有一个表叫


employee


，拥有如


Id, Name, Salary,Designation, Dept,


和


yoj


等字段。

语法：

ALTER TABLE table_name ADD


[


IF NOT EXISTS


]


PARTITION partition_spec

[


LOCATION


‘location1’


]


partition_spec


[


LOCATION


‘location2’


]


…;

partition_spec


:

:


(


p_column


=


p_col_value


,


p_column


=


p_col_value


,


…)

以下查询用于将分区添加到


employee


表。

hive


>


ALTER TABLE employee

>


ADD PARTITION


(


year


=’


2013


’)

>


location


‘/2013/part2013’


;

重命名分区

此命令的语法如下。

ALTER TABLE table_namePARTITION partition_spec RENAME TO PARTITION partition_spec


;

hive


>


ALTER TABLE employee PARTITION


(


year


=’


1203


’)

>


RENAME TO PARTITION


(


Yoj


=’


1203


’);

删除分区

下面语法用于删除分区：

ALTER TABLE table_name DROP


[


IF EXISTS


]


PARTITION partition_spec


,


PARTITIONpartition_spec


,…;

以下查询是用来删除分区：

hive


>


ALTER TABLE employee DROP


[


IF EXISTS


]

>


PARTITION


(


year


=’


1203


’);

在


Hive


有四种类型的运算符：

•


关系运算符

•


算术运算符

•


逻辑运算符

•


复杂运算符

关系运算符；



用来比较两个操作数

下表描述


Hive


中可用的关系运算符：

运算符	操作	描述
A = B	所有基本类型	如果表达 A 等于表达 B ，结果 TRUE ，否则 FALSE 。
A != B	所有基本类型	如果 A 不等于表达式 B 表达返回 TRUE ，否则 FALSE 。
A < B	所有基本类型	TRUE ，如果表达式 A 小于表达式 B ，否则 FALSE 。
A <= B	所有基本类型	TRUE ，如果表达式 A 小于或等于表达式 B ，否则 FALSE 。
A > B	所有基本类型	TRUE ，如果表达式 A 大于表达式 B ，否则 FALSE 。
A >= B	所有基本类型	TRUE ，如果表达式 A 大于或等于表达式 B ，否则 FALSE 。
A IS NULL	所有类型	TRUE ，如果表达式的计算结果为 NULL ，否则 FALSE 。
A IS NOT NULL	所有类型	FALSE ，如果表达式 A 的计算结果为 NULL ，否则 TRUE 。
A LIKE B	字符串	TRUE ，如果字符串模式 A 匹配到 B ，否则 FALSE 。
A RLIKE B	字符串	NULL ，如果 A 或 B 为 NULL ； TRUE ，如果 A 任何子字符串匹配 Java 正则表达式 B ；否则 FALSE 。
A REGEXP B	字符串	等同于 RLIKE.

示例

假设


employee


表由字段：


Id, Name,Salary, Designation,


和


Dept


组成，如下图所示。生成一个查询检索员工详细信息


– ID


为


1205


。

+—–+————–+——–+———————–+—

| Id  | Name        | Salary | Designation           |Dept |

+—–+————–+————————————+—

|1201 |Gopal        | 45000  | Technical manager     | TP  |

|1202 |Manisha      | 45000  | Proofreader           | PR   |

|1203 |Masthanvali  | 40000  | Technical writer      | TP  |

|1204 |Krian        | 40000  | Hr Admin              | HR   |

|1205 |Kranthi      | 30000  | Op Admin              | Admin|

+—–+————–+——–+———————–+—

以下查询执行检索使用上述表中的雇员的详细信息：

hive


>


SELECT


*


FROM employee WHERE


Id


=


1205


;

成功执行的查询，有以下结果：

+—–+———–+———–+———————-+

| ID  | Name     | Salary    | Designation          | Dept  |

+—–+—————+——-+———————-+

|1205 | Kranthi   | 30000    | Op Admin             | Admin |

+—–+———–+———–+———————-+

下面的查询执行以检索薪水大于或等于


40000


卢比的雇员的详细信息。

hive


>


SELECT


*


FROM employee WHERE


Salary


>=


40000


;

成功执行的查询，有以下回应：

+—–+————+——–+————————+——+

| ID  | Name      | Salary | Designation           | Dept |

+—–+————+——–+————————+——+

|1201 |Gopal      | 45000  | Technical manager      | TP  |

|1202 | Manisha    | 45000 | Proofreader            | PR

|1203 |Masthanvali| 40000  | Technicalwriter       | TP   |

|1204 |Krian      | 40000  | Hr Admin               | HR   |

+—–+————+——–+————————+——+

算术运算符：



支持的操作数各种常见的算术运算。返回数字类型。下表描述了在


Hive


中可用的算术运算符：

运算符	操作	描述
A + B	所有数字类型	A 加 B 的结果
A – B	所有数字类型	A 减去 B 的结果
A * B	所有数字类型	A 乘以 B 的结果
A / B	所有数字类型	A 除以 B 的结果
A % B	所有数字类型	A 除以 B. 产生的余数
A & B	所有数字类型	A 和 B 的按位与结果
A | B	所有数字类型	A 和 B 的按位或结果
A ^ B	所有数字类型	A 和 B 的按位异或结果
~A	所有数字类型	A 按位非的结果

示例

下面的查询相加两个数字，


20


和


30


。

hive


>


SELECT


20


+


30


ADD FROM temp


;

在成功执行查询后，有以下回应：

+——–+

|   ADD  |

+——–+

|   50   |

+——–+

逻辑运算符

运算符是逻辑表达式。所有这些返回


TRUE


或


FALSE


。

运算符	操作	描述
A AND B	boolean	TRUE ，如果 A 和 B 都是 TRUE ，否则 FALSE 。
A && B	boolean	类似于 A AND B.
A OR B	boolean	TRUE ，如果 A 或 B 或两者都是 TRUE ，否则 FALSE 。
A || B	boolean	类似于 A OR B.
NOT A	boolean	TRUE ，如果 A 是 FALSE ，否则 FALSE 。
!A	boolean	类似于 NOT A.

示例

下面的查询用于检索部门是


TP


并且工资超过


40000


卢比的员工详细信息。

hive


>


SELECT


*


FROM employee WHERE


Salary


>


40000


&&


Dept


=


TP


;

成功执行查询后，能看到以下回应：

+——+————–+———–+——————–+——-+

| ID   | Name         | Salary    | Designation      | Dept  |

+——+————–+———–+——————+——-+

|1201  | Gopal       | 45000     | Technical manager|TP    |

+——+————–+———–+——————+——-+

复杂的运算符

这些运算符提供一个表达式来接入复杂类型的元素。

运算符	操作	描述
A[n]	A 是一个数组， n 是一个 int	它返回数组 A 的第 n 个元素，第一个元素的索引 0 。
M[key]	M 是一个 Map<K, V> 并 key 的类型为 K	它返回对应于映射中关键字的值。
S.x	S 是一个结构	它返回 S 的 s 字段

Hive


支持以下内置函数：

示例；

round()


函数

hive


>


SELECT round


(


2.6


)


from


temp


;

成功执行的查询，能看到以下回应：

2.0

floor()


函数

hive


>


SELECT floor


(


2.6


)


from


temp


;

2


．


0

聚合函数

Hive


支持以下内置聚合函数。这些函数的用法类似


SQL


聚合函数。

返回类型	签名	描述
BIGINT	count(*), count(expr),	count(*) – 返回检索行的总数。
DOUBLE	sum(col), sum(DISTINCT col)	返回该组或该组中的列的不同值的分组和所有元素的总和。
DOUBLE	avg(col), avg(DISTINCT col)	返回上述组或该组中的列的不同值的元素的平均值。
DOUBLE	min(col)	返回该组中的列的最小值。
DOUBLE	max(col)	返回该组中的列的最大值。

和


SQL


类似，可根据用户的需求创建视图，将任何结果集数据保存为一个视图。

创建一个视图

可以创建一个视图，在执行


SELECT


语句的时候。语法如下：

CREATE VIEW [IFNOT EXISTS] view_name [(column_name [COMMENT column_comment], …) ]

[COMMENTtable_comment]

AS SELECT …

示例

设


employee


表拥有如下字段：


Id, Name, Salary, Designation


和


Dept


。生成一个查询检索工资超过


30000


卢比的员工详细信息，我们把结果存储在一个名为视图


emp_30000.

+——+————–+———–+——————+——-+

| ID   | Name         | Salary    | Designation      | Dept |

+——+————–+———–+——————+——-+

|1201  | Gopal       | 45000     |Technical manager |TP    |

|1202  | Manisha     | 45000     | Proofreader      | PR   |

|1203  | Masthanvali | 40000     | Technical writer |TP    |

|1204  | Krian       | 40000     | Hr Admin         | HR   |

|1205  | Kranthi     | 30000     | Op Admin         | Admin

+——+————–+———–+——————+——-+

下面使用上述业务情景查询检索员的工详细信息：

hive> CREATE VIEWemp_30000 AS

> SELECT * FROM employee

> WHERE salary>30000;

删除一个视图

语法：


DROP VIEW view_name

创建索引

索引是一个表上的一个特定列的指针。创建索引意味着创建表上的一个特定列的指针。语法如下：

CREATE INDEXindex_name

ON TABLEbase_table_name (col_name, …)

AS’index.handler.class.name’

[WITH DEFERREDREBUILD]

[IDXPROPERTIES(property_name=property_value, …)]

[IN TABLEindex_table_name]

[PARTITIONED BY(col_name, …)]

[

[ ROW FORMAT …] STORED AS …

| STORED BY …

]

[LOCATIONhdfs_path]

[TBLPROPERTIES(…)]

示例，使用之前的字段


Id, Name, Salary, Designation,


和


Dept


创建一个名为


index_salary


的索引，对


employee


表的


salary


列索引。

下面的查询创建一个索引：

hive> CREATE INDEXinedx_salary ON TABLE employee(salary)

> AS’org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler’;

这是一个指向


salary


列。如果列被修改，变更使用的索引值存储。

删除索引

用来删除索引的语法：

DROP INDEX <index_name> ON<table_name>

下面的查询删除名为


index_salary


索引：

hive> DROP INDEXindex_salary ON employee;

SELECT


语句用来从表中检索的数据。


WHERE


子句中的工作原理类似于一个条件。它使用这个条件过滤数据，并返回给出一个有限的结果。内置运算符和函数产生一个表达式，满足该条件并从表中


select


出来。

SELECT


查询的语法；

查询检索薪水大于


30000


的员工信息；

hive> SELECT * FROMemployee WHERE salary>30000;

ORDER BY


子句用于检索基于一列的细节并设置排序结果按升序或降序排列。

ORDER BY


子句的语法；

例如，有员工表；

+——+————–+———-+——————+——–+

| ID   | Name         | Salary   | Designation      | Dept  |

+——+————–+———-+——————+——–+

|1201  | Gopal       | 45000    |Technical manager |TP     |

|1202  | Manisha     | 45000    | Proofreader      | PR    |

|1203  | Masthanvali | 40000    | Technical writer |TP     |

|1204  | Krian       | 40000    | Hr Admin         | HR     |

|1205  | Kranthi     | 30000    | Op Admin         | Admin  |

+——+————–+———-+——————+——–+

hive


>


SELECT


Id


,


Name


,


Dept


FROM employee ORDER BY DEPT


;

得到查询结果

+——+————–+———-+—————–+——–+

| ID   | Name         | Salary   | Designation     | Dept  |

+——+————–+———-+—————–+——–+

|1205  | Kranthi     | 30000    | Op Admin        | Admin |

|1204  | Krian       | 40000    | Hr Admin        | HR    |

|1202  | Manisha     | 45000    | Proofreader     | PR    |

|1201  | Gopal       | 45000    |Technical manager|TP     |

|1203  | Masthanvali | 40000    | Technical writer|TP     |

+——+————–+———-+—————–+——–+

GROUP BY


子句用于分类所有记录结果的特定集合列，用来查询一组记录。

GROUP BY


子句的语法：

同样的，上述员工表，

hive


>


SELECT


Dept


,


count


(*)


FROM employee GROUP BYDEPT


;

查询以检索每个部门的员工数量。

+——+————–+

| Dept |Count(*)     |

+——+————–+

|Admin |    1        |

|PR    |   2         |

|TP    |   3         |

+——+————–+

JOIN


子句通过共同值组合来自两个表的特定字段。它是从数据库中的两个或更多的表组合的记录。

语法；

join_table


:

table_reference JOIN table_factor


[


join_condition


]

|


table_reference


{



LEFT


|


RIGHT


|


FULL


}


[


OUTER


]


JOINtable_reference

join_condition

|


table_referenceLEFT SEMI JOIN table_reference join_condition

|


table_referenceCROSS JOIN table_reference


[


join_condition


]

示例，


CUSTOMERS


表

+—-+———-+—–+———–+———-+

| ID | NAME     | AGE | ADDRESS   | SALARY  |

+—-+———-+—–+———–+———-+

| 1  | Ramesh  | 32  | Ahmedabad | 2000.00  |

| 2  | Khilan  | 25  | Delhi     | 1500.00 |

| 3  | kaushik | 23  | Kota      | 2000.00 |

| 4  | Chaitali | 25  | Mumbai   | 6500.00  |

| 5  | Hardik   | 27  |Bhopal    | 8500.00  |

| 6  | Komal   | 22  | MP        | 4500.00  |

| 7  | Muffy   | 24  | Indore    | 10000.00 |

+—-+———-+—–+———–+———-+

另一个


ORDERS


表

+—–+———————+————-+——–+

|OID  | DATE               | CUSTOMER_ID |AMOUNT |

+—–+———————+————-+——–+

| 102 |2009-10-08 00:00:00 |           3 |3000   |

| 100 |2009-10-08 00:00:00 |           3 |1500   |

| 101 |2009-11-20 00:00:00 |           2 |1560   |

| 103 |2008-05-20 00:00:00 |           4 |2060   |

+—–+———————+————-+——–+

其中有不同类型的联接；


JOIN


、


LEFT OUTER JOIN


、


RIGHT OUTER JOIN


、


FULL OUTER JOIN


。

JOIN


：

JOIN


子句用于合并和检索来自多个表中的记录。


JOIN


和


SQL OUTER JOIN


类似。连接条件是使用主键和表的外键。

hive


>


SELECT c


.


ID


,


c


.


NAME


,


c


.


AGE


,


o


.


AMOUNT

>


FROM CUSTOMERS c JOINORDERS o

>


ON


(


c


.


ID


=


o


.


CUSTOMER_ID


);

查询结果；

+—-+———-+—–+——–+

| ID | NAME     | AGE | AMOUNT |

+—-+———-+—–+——–+

| 3  | kaushik | 23  | 3000   |

| 3  | kaushik | 23  | 1500   |

| 2  | Khilan  | 25  | 1560   |

| 4  | Chaitali | 25  | 2060  |

+—-+———-+—–+——–+

LEFT OUTER JOIN

HiveQL LEFT OUTER JOIN


返回所有行左表，即使是在正确的表中没有匹配。这意味着，如果


ON


子句匹配的右表


0


（零）记录，


JOIN


还是返回结果行，但在右表中的每一列为


NULL


。

例如以下


LEFT OUTER JOIN


用法

hive


>


SELECT c


.


ID


,


c


.


NAME


,


o


.


AMOUNT


,


o


.


DATE

>


FROM CUSTOMERS c

>


LEFT OUTER JOIN ORDERS o

>


ON


(


c


.


ID


=


o


.


CUSTOMER_ID


);

查询结果；

+—-+———-+——–+———————+

| ID | NAME     | AMOUNT | DATE                |

+—-+———-+——–+———————+

| 1  | Ramesh  | NULL   | NULL                |

| 2  | Khilan  | 1560   | 2009-11-20 00:00:00 |

| 3  | kaushik | 3000   | 2009-10-08 00:00:00 |

| 3  | kaushik | 1500   | 2009-10-08 00:00:00 |

| 4  | Chaitali | 2060   | 2008-05-20 00:00:00 |

| 5  | Hardik  | NULL   | NULL                |

| 6  | Komal   | NULL   | NULL                |

| 7  | Muffy   | NULL   | NULL                |

+—-+———-+——–+———————+

RIGHT OUTER JOIN

HiveQL RIGHT OUTER JOIN


返回右边表的所有行，即使有在左表中没有匹配。如果


ON


子句的左表匹配


0


（零）的记录，


JOIN


结果返回一行，但在左表中的每一列为


NULL


。

hive


>


SELECT c


.


ID


,


c


.


NAME


,


o


.


AMOUNT


,


o


.


DATE

>


FROM CUSTOMERS c

>


RIGHT OUTER JOIN ORDERS o

>


ON


(


c


.


ID


=


o


.


CUSTOMER_ID


);

查询结果：

+——+———-+——–+———————+

| ID   | NAME    | AMOUNT | DATE                |

+——+———-+——–+———————+

| 3    | kaushik | 3000   | 2009-10-08 00:00:00 |

| 3    | kaushik | 1500   | 2009-10-08 00:00:00 |

| 2    | Khilan  | 1560   | 2009-11-20 00:00:00 |

| 4    | Chaitali | 2060   | 2008-05-20 00:00:00 |

+——+———-+——–+———————+

FULL OUTER JOIN

HiveQL FULL OUTER JOIN


结合了左边，并且满足


JOIN


条件合适外部表的记录。连接表包含两个表的所有记录，或两侧缺少匹配结果那么使用


NULL


值填补。

+——+———-+——–+———————+

| ID   | NAME    | AMOUNT | DATE                |

+——+———-+——–+———————+

| 1    | Ramesh  | NULL   | NULL                |

| 2    | Khilan  | 1560   | 2009-11-20 00:00:00 |

| 3    | kaushik | 3000   | 2009-10-08 00:00:00 |

| 3    | kaushik | 1500   | 2009-10-08 00:00:00 |

| 4    | Chaitali | 2060   | 2008-05-20 00:00:00 |

| 5    | Hardik  | NULL   | NULL                |

| 6    | Komal   | NULL   | NULL                |

| 7    | Muffy   | NULL   | NULL                |

| 3    | kaushik | 3000   | 2009-10-08 00:00:00 |

| 3    | kaushik | 1500   | 2009-10-08 00:00:00 |

| 2    | Khilan  | 1560   | 2009-11-20 00:00:00 |

| 4    | Chaitali | 2060   | 2008-05-20 00:00:00 |

+——+———-+——–+———————+

1


、


Hive


不支持等值连接

• SQL


中对两表内联可以写成：

•select * from dual a,dual bwhere a.key = b.key;

•Hive


中应为

•select * from dual a joindual b on a.key = b.key;

2


、分号字符

•


分号是


SQL


语句结束标记，在


HiveQL


中也是，但是在


HiveQL


中，对分号的识别没有那么智慧，例如：

•selectconcat(key,concat(‘;’,key)) from dual;

•


但


HiveQL


在解析语句时提示：

FAILED: Parse Error: line 0:-1 mismatched input ‘<EOF>’ expecting ) infunction specification

•


解决的办法是，使用分号的八进制的


ASCII


码进行转义，那么上述语句应写成：

•selectconcat(key,concat(‘\073’,key)) from dual;

3


、


IS[NOT] NULL

•


SQL


中


null


代表空值


,


值得警惕的是


,


在


HiveQL


中


String


类型的字段若是空


(empty)


字符串


,


即长度为


0,


而对它进行


ISNULL


的判断结果是


False.