深入学习《Programing Hive》：HiveQL查询(2)

        浮点比较陷阱
        在WHERE查询条件中：在比较不同类型的数值(如FLOAT vs DOUBLE)时，会引发浮点比较陷阱。
        看下面的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
        

       
        LIKE和RLIKE
        LIKE标识模糊查询：

              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.
        

        RLIKE是使用正则表达式：

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

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

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

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

        如果不使用HAVING，那么就要使用子查询：

              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
        

        JION
        Hive支持典型的SQL JION连接，但只支持等值连接。
        Hive中的内连接、左外连接、右外连接和全外连接和标准的SQL中的连接有相同的概念；但是在Hive中没有IN、EXISTS关键字的使用，取代这两个关键字的功能的是LEFT SEMI-JION，也是对Inner JION的性能上的加强优化。
        LEFT SEMI-JION
        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 子句或其他地方过滤都不行。

        JION优化
        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)、Map-Side Jion：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;
        

    
        ORDER BY和SORT BY
        ORDER BY和SQL方言中的order by 语句很像，会对所有的结果集做整体的排序操作，这就意味着所有的数据是通过一个Reducer处理的；对处理非常大的数据及来说，这会执行非常长的处理时间。
        Hive提供了一种局部排序的功能——SORT BY，只对每个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;