查询数据
这一节讨论如何使用SELECT语句的各种形式从Hive中检索数据。
排序和聚集
在Hive中可以使用标准的ORDER BY子句对数据进行排序。但这里有一个潜在的 不利因素。ORDER BY能够预期产生完全排序的结果，但是它是通过只用一个 reducer来做到这一点的。所以对于大规模的数据集，它的效率非常低。
在很多情况下，并不需要结果是全局排序的。此时，可以换用Hive的非标准的扩 展SORT BY。SORT BY为毎个reducer产生一个排序文件。
在有些情况下，你需要控制某个特定行应该到哪个reducer,通常是为了进行后续 的聚集操作。这就是Hive的DISTRIBUTE BY子句所做的事情。下面的例子根据 年份和气温对气象数据集进行排序，以确保证所有具有相同年份的行最终都在同一个reducer分区中:
hive> FROM records2SELECT year, temperatureDISTRIBUTE BY year
>SORT BY year ASC, temperature DESC;1949 1111949 781950 221950 01950- 11
同一个年份的气温已经在同一文件中分好组并且(以降序)排好序。后续的査询可以利用这 一点。
如果SORT BY和DISTRIBUTE BY中所用的列相同，可以缩写为CLUSTER BY以 便同时指定两者所用的列。
MapReduce 脚本
使用 Hadoop Streaming、TRANSFORM、MAP、REDUCE 子句这样的方法，便可 以在Hive中调用外部脚本。假设我们像例12-1那样，用一个脚本来过滤不符合某 个条件的行(删除低质量的气温读数)。
例12-1.过滤低质量气象记录的Python脚本
#!/usr/bin/env pythonimport re import sysfor line in sys.stdin:(year, temp, q) = line.strip().split() if (temp != ”9999" and re.match("[01459]", q)): print ”％s\t%s” % (year, temp)
我们可以像下面这样使用这个脚本：
hive> ADD FILE /path/to/is_good_quality.py; hive> FROM records2 >SELECT TRANSFORH(year, temperature, quality) >USING 9is_good_quality.py' >AS year, temperature;1949 1111949 781950 0 1950 221950- 11
在运行查询之前，我们需要在Hive中注册脚本。通过这一操作，Hive知道需要把 脚本文件传输到Hadoop集群上
查询本身把year, temperature和quality这些字段以制表符分隔的行的形式 流式传递给脚本is_good_qmdity.py,并把制表符分隔的输出解析为year和temperature字段，最终形成查询的输出。
这一示例并不使用reducer。如果要用查询的嵌套形式，我们可以指定map和 reduce函数。这一次我们用MAP和REDUCE关键字。但在这两个地方用SELECTTRANSFORM也能达到同样的效果。max_temperature_reduce.py脚本的内容参见例2-11；
FROM (FROM records2MAP year, temperature, quality USING 'is_good_quality.py' AS year, temperature) map_output REDUCE year, temperatureUSING 'max_temperature_reducG.py' AS year, temperature;
连接
使用Hive和直接使用MapReduce相比，好处在于它简化了常用操作。想想在 MapReduce中实现“连接”(join)幻要做的事情，在 Hive中进行连接操作就能充分体现这个好处。
内连接
内连接是最简单的一种连接。输入表之间的每次匹配都会在输出表里生成一行。让 我们来考虑两个演示用的小表：sales列出了人名及其所购商品的ID;things列 出商品的ID和名称：
hive> SELECT * FROM sales;Joe 2Hank 4 Ali 0Eve 3Hank 2hive> SELECT * FROM things;2 Tie 4 Coat 3 Hat1 Scarf
我们可以像下面这样对两个表进行内连接：
hive> SELECT sales.*, things.* >FROM sales J0IN things ON (sales.id = things.id);Joe 2 2 TieHank 2 2 TieEve 3 3 HatHank 4 4 Coat
FROM子句中的表(sales)和JOIN子句中的表(things)用ON子句中的谓词进行连 接。Hive只支持等值连接(equijoin)，这意味着在连接谓词中只能使用等号。在这 个示例中，等值条件是两个表的id列必须相同。
在Hive中，可以在连接谓词中使用AND关键字分隔的一系列表达式来连接多个 列。还可以在查询中使用多个〕JOIN...ON..子句来连接多个表。扭^会智能地以最 少MapReduce作业数来执行连接。
单个的连接用一个MapReduce作业实现。但是，如果多个连接的连接条件中使用 了相同的列，那么平均每个连接可以至少用一个MapReduce作业来实现。你可以 在查询前使用EXPLAIN关键字来查看Hive将为某个查询使用多少个MapReduce 作业：
EXPLAINSELECT sales.*, things.*FROM sales JOIN things ON (sales.id = things.id);
EXPLAIN的输出中有很多查询执行计划的详细信息，包括抽象语法树、Hive执行 各阶段之间的依赖图以及每个阶段的信息。一个阶段可能是MapReduce作业文件移 动这样的操作。如果要查看更详细的信息，可以在查询前使用EXPLAINEXTENDED。
Hive目前使用基于规则的查询优化器来确定查询是如何执行的。但在将来，Hive 很有可能会增加一个基于代价的优化器。
外连接
外连接可以让你找到连接表中不能匹配的数据行。在前面的示例里，我们在进行内 连接时，Ali那一行没有出现在输出中。因为她所购商品的ID没有在things表中 出现。如果我们把连接的类型改为LEFT OUTER JOIN,查询会返回左侧表(sales) 中的每一个数据行，即使有些行无法与这个表所要连接的表(things)中的任何数据 行对应：
hive> SELECT sales.*, things.*FROM sales LEFT OUTER JOIN things ON (sales.id = things.id);Ali 0 NULL NULLJoe 2 2 TieHank 2 2 TieEve 3 3 HatHank 4 4 Coat
注意，此时返回了Ali所在的数据行，但因为这一行无匹配，所以things表的对应列为空值NULL。
Hive也支持“右外连接”（right outer join),即和左连接相比交换两个表的角色。在这里，things表中的所有商品，即使没有任何人购买它们（围巾），也会返回：
hive> SELECT sales.*, things.*FROM sales RIGHT OUTER JOIN things ON (sales.id = things.id);NULL NULL 1 ScarfJoe 2 2 TieHank 2 2 TieEve 3 3 HatHank 4 4 Coat
半连接
Hive(在本书写作时)并不支持IN子査询，但可以使用LEFT SEMI JOIN来达到相 同的效果。
考虑如下IN子查询，它能够査找things表中在sales表中出现过的所有商品：
SELECT *FROM thingsWHERE things.id IN (SELECT id from sales);
我们可以像下面这样重写这个查询：
hive> SELECT *FROM things LEFT SEMI JOlN sales ON (sales.id = things.id);2 Tie3 Hat4 Coat
写LEFT SEMI JOIN查询时必须遵循一个限制：右表(sales)只能在ON子句中出 现。例如，我们不能在SELECT表达式中引用右表。
map连接
如果有一个连接表小到足以放入内存，Hive就可以把较小的表放入每个mapper 的内存来执行连接操作。如果要指定使用map连接，需要在SQL中使用C语言风 格的注释，从而给出提示：
SELECT /*+ MAPJ0IN(things) */ sales.*, things.*FROM sales JOIN things ON (sales.id = things.id);
执行这个査询不使用reducer,因此这个查询对RIGHT或FULL OUTER JOIN无 效，因为只有在对所有输入上进行聚集(reduce)的步骤才能检测到哪个数据行无 法匹配。 Map连接可以利用分桶的表，因为作用于桶的mapper加载右侧表中对应的桶即可执行连接。这时使用的语法和前面提到的在内存中进行 连接是一样的，只不过还需要用下面的语法启用优化选项：
SET hive.optimize.bucketmapjoin=true;
子查询
子查询是内嵌在另一个SQL语句中的SELECT语句。Hive对子査询的支持很有 限。它只允许子查询出现在SELECT语句的FROM子句中。
下面的查询可以找到每年每个气象站最高气温的均值：
SELECT station, year, AVG(max_temperature)FROM (SELECT station, year, MAX(temperature) AS max_temperatureFROM records2WHERE temperature != 9999AND (quality = 0 OR quality = 1 OR quality = 4 OR quality = 5 OR qualityGROUP BY station, year)mtGROUP BY station, year
这里的子查询用于计算毎个气象站/日期组合中的最高气温，然后外层查询使用 AVG聚集函数计算这些最髙读数的均值。
外层查询像访问表那样访问子査询的结果，这是为什么必须为子查询赋予一个别名(mt)的原因。子查询中的列必须有唯一的名称，以便外层查询可以引用这些列。
视图
视图是一种用SELECT语句定义的“虚表”（virtual table)。视图可以用来以一种不 同于磁盘实际存储形式把数据呈现给用户。现有表中的数据常常需要以一种特殊的 方式进行简化和聚集以便于后期处理。视图也可以用来限制用户，使其只能访问授 权可以看到的表的子集。
在Hive中，创建视图时并不把视图“物化”(materialize)存储到磁盘上。相反，视 图的SELECT语句只是在执行引用视图的语句时才执行。如果一个视图要对“基 表”（base table)进行大规模的变换，或视图的查询会频繁执行，可能需要新建一个 表，并把视图的内容存储到新表中，以此手工来物化它。
我们可以用视图重写前一节中的查询，它用于查找每年各个气象站气温最大值的均 值。首先，让我们为有效记录(即有特定quality值的记录)创建一个视图：
CREATE VIEW valid_records AS SELECT *FROM records2WHERE temperature != 9999AND (quality = 0 OR quality = 1 OR quality = 4 OR quality = 5 OR quality = 9);
创建视图时并不执行查询，查询只是存储在metastore中。SHOW TABLES命令的 输出结果里包括视图。可以使用DESCRIBE EXTENDED view_name命令来查看某 个视图的详细信息，包括用于定义它的那个查询。
接下来，让我们为每个观测站每年的最高气温创建第二个视图。这个视图基于 valid_record 视图：
CREATE VIEW max_temperatures (station, year, max_temperature)ASSELECT station, year, MAX(temperature) FROM valid_records GROUP BY station, year;
在这个视图定义中，我们显式地列出了列的名称。我们这么做是因为最高气温列是 一个聚集表达式，如果我们不指明，Hive会自己创建一个别名(例如_c2)。我们 也可以在SELECT语句中使用AS子句来为列命名。
有了这两个视图，现在我们就可以执行查询了：
SELECT station, year, AVG(max_temperature)FROM max_temperatures GROUP BY station, year;
这个查询的结果和前面使用子查询的查询是一样的。特别的，Hive为它们所使用 的 MapReduce作业的个数也是一样的：都是两个，GROUP BY使用一个。从 这个例子可以看到，Hive可以把使用视图的査询组织成一系列作业，效果与不使 用视图的查询一样。换句话说，Hive在执行时，不会在不必要的情况下物化视 图。
Hive中的视图是只读的，所以无法通过视图向基表加载或插入数据。
