Hive-QL中的查询

Posted by RussXia on April 25, 2019

Join

在大多数SQL中,会先执行join操作,然后再将结果通过where语句进行过滤。

例如,select s.ymd,s.symbol,s.price_close,d.dividend from stocks s left join dividends d on s.ymd=d.ymd and s.symbol=d.symbol where s.symbol='APPL' and s.`exchange`='NASDAQ' and d.`exchange`='NASDAQ';,会先进行join操作,在到达where语句时,d.exchange字段绝大部分为NULL,此时回去过滤掉这批数据。

Hive中left semi join 和in、not in、exists等子查询

关于in和exists,官方wiki:https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SubQueries#LanguageManualSubQueries-SubqueriesintheWHEREClause

Hive在0.12版本后就支持了from条件中子查询,例如

SELECT t3.col
FROM (
  SELECT a+b AS col
  FROM t1
  UNION ALL
  SELECT c+d AS col
  FROM t2
) t3

0.13版本后就支持了where条件中的子查询,前提是对于in、not in这样的子查询(也叫做’不相关子查询’,因为不会引用到父查询的列),查询结果必须被当作是个常量。

SELECT *
FROM A
WHERE A.a IN (SELECT foo FROM B);s

也同样支持 EXISTS 和 NOT EXISTS 类型的子查询

SELECT A
FROM T1
WHERE EXISTS (SELECT B FROM T2 WHERE T1.X = T2.Y)

但是存在以下几点限制:

  • 子查询支持在表达式的右边
  • IN/NOT IN 子查询只支持单列
  • EXISTS/NOT EXISTS 必须要有一个或多个相关的predicates(判断?关联?)
  • 引用父查询只在where 的子查询中支持。

Hive中关于left semi join的wiki资料:https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Joins

自从hive 0.13支持了in/exists等操作后,就不那么需要left semi join了。LEFT SEMI JOIN必须保证,右边的表只有在join条件上(on 语句)可以被引用,不能出现在where语句和select语句中

order by、sort by、distirbute by、cluster by

Hive中的order by和其他SQL方言中定义的一样。会对查询结果集进行一个全局排序。这也就是说会有一个所有数据都通过一个reducer进行处理的过程。

sort by只会在每个reducer中对数据进行排序,也就是说这是一个局部排序的过程。这可以保证每个reducer的输出数据都是有序的(局部有序,全局无序)。

distribute by控制map的输出在reducer中如何划分。默认情况下,MapReduce框架会根据map输入的键计算相应的hash值,然后按照hash值将key-value均匀的分发到多个reducer中。distribute by可以保证对应的记录会分发到同一个reducer中处理。

如果在一个HQL中,distribute by的字段和sort by的字段完全相同,而且采用升序排列(asc,默认的排序方式),这种情况下 distribute a,b sort by a asc,b asc 就可以缩写成 cluster by a,b