11.3.3　SQL操作

下面是一个标准的Select语句语法定义：

SELECT[ALL|DISTINCT]select_expr, select_expr，……

FROM table_reference

[WHERE where_condition]

[GROUP BY col_list]

[CLUSTER BY col_list

|[DISTRIBUTE BY col_list][SORT BY col_list]

]

[LIMIT number]

下面对其中重要的定义进行说明。

（1）table_reference

table_reference指明查询的输入，它可以是一个表、一个视图或一个子查询。下面是一个简单的查询，检索所有表t1中的列和行：

SELECT*FROM t1

（2）WHERE

where_condition是一个布尔表达式。比如下面的查询只输出sales表中amount＞10且region属性值为US的记录：

SELECT*FROM sales WHERE amount＞10 AND region="US"

（3）ALL和DISTINCT

ALL和DISTINCT选项可以定义重复的行是否要返回。如果没有定义，那么默认为ALL，即输出所有的匹配记录而不删除重复的记录，代码如下：

hive＞SELECT col1，col2 FROM t1

1 3

1 3

1 4

2 5

hive＞SELECT DISTINCT col1，col2 FROM t1

1 3

1 4

2 5

hive＞SELECT DISTINCT col1 FROM t1

1

2

（4）LIMIT

LIMIT可以控制输出的记录数，随机选取检索结果中的相应数目输出：

SELECT*FROM t1 LIMIT 5

下面代码为输出Top-k, k=5的查询结果：

SET mapred.reduce.tasks=1

SELECT*FROM sales SORT BY amount DESC LIMIT 5

（5）使用正则表达式

SELECT声明可以匹配使用一个正则表达式的列。下面的例子会对sales表中除了ds和hr的所有列进行扫描：

SELECT（ds|hr）?+.+FROM sales

（6）基于分区的查询

通常来说，SELECT查询要扫描全部的表。如果一个表是使用PARTITIONED BY语句产生的，那么查询可以对输入进行“剪枝”，只对表的相关部分进行扫描。Hive现在只对在WHERE中指定的分区断言进行“剪枝”式的扫描。举例来说，如果一个表page_view按照date列的值进行了分区，那么下面的查询可以检索出日期为2010-03-01的行记录：

SELECT page_views.*

FROM page_views

WHERE page_views.date＞='2010-03-01'AND page_views.date＜='2010-03-31'

（7）HAVING

Hive目前不支持HAVING语义，但是可以使用子查询实现，示例如下：

SELECT col1 FROM t1 GROUP BY col1 HAVING SUM（col2）＞10

可以表示为：

SELECT col1 FROM（SELECT col1，SUM（col2）AS col2sum FROM t1 GROUP BY col1）t2

WHERE t2.col2sum＞10

我们可以将查询的结果写入到目录中：

hive＞INSERT OVERWRITE DIRECTORY'/tmp/hdfs_out'SELECT a.*FROM invites a WHERE

a.ds='2009-09-01'；

上面的例子将查询结果写入/tmp/hdfs_out目录中。也可以将查询结果写入本地文件路径，如下所示：

hive＞INSERT OVERWRITE LOCAL DIRECTORY'/tmp/local_out'SELECT a.*FROM pokes a；

其他（例如GROUP BY和JOIN）的作用和SQL相同，就不再赘述，下面是使用的例子，详细信息可以查看http：//wiki.apache.org/hadoop/Hive/LanguageManual。

（8）GROUP BY

hive＞FROM invites a INSERT OVERWRITE TABLE events SELECT a.bar, count（*）WHERE

a.foo＞0 GROUP BY a.bar；

hive＞INSERT OVERWRITE TABLE events SELECT a.bar, count（*）FROM invites a WHERE

a.foo＞0 GROUP BY a.bar；

（9）JOIN

hive＞FROM pokes t1 JOIN invites t2 ON（t1.bar=t2.bar）INSERT OVERWRITE TABLE

events SELECT t1.bar, t1.foo, t2.foo；

（10）多表INSERT

FROM src

INSERT OVERWRITE TABLE dest1 SELECT src.*WHERE src.key＜100

INSERT OVERWRITE TABLE dest2 SELECT src.key, src.value WHERE src.key＞=100 and

src.key＜200

INSERT OVERWRITE TABLE dest3 PARTITION（ds='2010-04-08'，hr='12'）SELECT src.key

WHERE src.key＞=200 and src.key＜300

INSERT OVERWRITE LOCAL DIRECTORY'/tmp/dest4.out'SELECT src.value WHERE src.key

＞=300；

（11）STREAMING

hive＞FROM invites a INSERT OVERWRITE TABLE events SELECT TRANSFORM（a.foo, a.bar）

AS（oof, rab）USING'/bin/cat'WHERE a.ds＞'2010-08-09'；

这个命令会将数据输入给Map操作（通过/bin/cat命令），同样也可以将数据流式输入给Reduce操作。