深入学习《Programing Hive》：HiveQL查询(2)-优快云博客

本文深入探讨Hive SQL中的高级特性，包括浮点数比较陷阱及其解决方案、模糊查询与正则表达式查询、分组与过滤技巧、连接类型及优化方法、排序策略等，帮助读者掌握高效查询的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

[b]浮点比较陷阱[/b]
在[b]WHERE[/b]查询条件中：在比较不同类型的数值(如FLOAT vs DOUBLE)时，会引发[b]浮点比较陷阱[/b]。
看下面的HiveQL语句，本来只想要查询Federal Taxes > 0.2，但是返回结果如下：


              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('Federal Taxes') == 0.2)的结果也返回了？！这就是浮点比较陷阱引发的。
那么如何来避免这个陷阱呢？那就要将0.2强制转型了：


              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

[b]LIKE和RLIKE[/b]
[b]LIKE[/b]标识模糊查询：


              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.

[b]RLIKE[/b]是使用正则表达式：


              hive> SELECT name,address.street FROM employees
                  > WHERE address.street RLIKE '.*(Chicago|Ontario).*';
              Mary Smith   100 Ontario St.
              Todd Jones   200 Chicago Ave.

[b]GROUP BY[/b]
Hive的GROUP BY语句和传统的SQL相同，经常要和聚合函数一块儿使用，我们再看一下[b]stocks[/b]表的定义：


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

示例如下：


              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
              ...

[b]HAVING[/b]
Hive中的HAVING关键字和传统SQL中的概念相同，是对分组后的结果再次过滤。使用HAVING可以避免GROUP BY后的子查询：


              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
              ...

如果不使用[b]HAVING[/b]，那么就要使用子查询：


              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

[size=large][b]JION[/b][/size]
Hive支持典型的SQL JION连接，但[b]只支持等值连接[/b]。
Hive中的内连接、左外连接、右外连接和全外连接和标准的SQL中的连接有相同的概念；但是在Hive中没有[b]IN、EXISTS[/b]关键字的使用，取代这两个关键字的功能的是LEFT SEMI-JION，也是对Inner JION的性能上的加强优化。
[b]LEFT SEMI-JION[/b]
LEFT SEMI-JION和MySQL方言中的IN... EXISTS...结构做同样的事情：


              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
              ...

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

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


              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)处理：


              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字句中的jion keys都相同时，Hive会把多个table的jion连接编译为一个MapReduce Job，否则一次jion连接会编译成一个job。
3)、[b]Map-Side Jion：[/b]Hive提供Map端的jion连接操作，默认情况下是不支持该操作的，用户必须强制告知查询优化器做Map端的Jion连接操作，这可以避免对结果进行大规模的笛卡尔集的操作，也可以减少数据的I/O流量：


              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连接操作之前，必须要设置一些参数：


              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;

[size=large][b]ORDER BY和SORT BY[/b][/size]
[b]ORDER BY[/b]和SQL方言中的order by 语句很像，会对所有的结果集做整体的排序操作，这就意味着所有的数据是通过一个Reducer处理的；对处理非常大的数据及来说，这会执行非常长的处理时间。
Hive提供了一种局部排序的功能——[b]SORT BY[/b]，只对每个Reducer处理的局部数据排序，也是一个本地排序，这也就是说每个Reducer处理后的数据是排序的，但对整体而言是无序的。


              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;