hive 数据查询

QQ交流群：335671559

一、SELECT ... FROM ...子句

hive> SELECT name, salary FROM employees;

hive> SELECT e.name, e.salary FROM employees e;

第一种方式是我们普遍使用的，第二种方式使用了别名，这在JOIN查询时非常必要

    1.1  查询数组结果，subordinates 数组类型

    hive> SELECT name, subordinates FROM employees;

John Doe ["Mary Smith","Todd Jones"]

Mary Smith ["Bill King"]

Todd Jones []

    查询数组中的某一个值

    hive> SELECT name, subordinates[0] FROM employees;

John Doe Mary Smith

Mary Smith Bill King

    1.2  查询map类型

    hive> SELECT name, deductions FROM employees;

John Doe {"Federal Taxes":0.2,"State Taxes":0.05,"Insurance":0.1}

Mary Smith {"Federal Taxes":0.2,"State Taxes":0.05,"Insurance":0.1}

    使用key值查询map中的值
    hive> SELECT name, deductions["State Taxes"] FROM employees;

        John Doe 0.05

    1.3  查询结构体类型

    hive> SELECT name, address FROM employees;

John Doe {"street":"1 Michigan Ave.","city":"Chicago","state":"IL","zip":60600}

Mary Smith {"street":"100 Ontario St.","city":"Chicago","state":"IL","zip":60601}

    使用圆点（dot）查询结构体中的值

    hive> SELECT name, address.city FROM employees;

John Doe Chicago

Mary Smith Chicago

    1.4  使用正则表达式查询列

    hive> SELECT symbol, `price.*` FROM stocks;

AAPL 195.69 197.88 194.0 194.12 194.12

AAPL 192.63 196.0 190.85 195.46 195.46

    本查询匹配的是symbol列和以'price'开头的列， `price.*` 使用的是反引号

    1.5  使用列值进行计算

    hive> SELECT upper(name), salary, deductions["Federal Taxes"],

        > round(salary * (1 - deductions["Federal Taxes"])) FROM employees;

        可以使用hive列进行算术操作，使用数字类型进行运算时会出现类型的转换，一般是小类型向大类型转换。在定义列类型时，如果比较在乎溢出问        题，可以尽量使用范围较大的类型，缺点是占用内存较多。

二、where 字句   

     2.1 在比较浮点数时，有一个问题要注意，举例如下：

    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的情况。这个问题的原因就是浮点型数字在进行类型转换后，出现的BUG。原始数据中deductions这个map容器，对应的值是float类型，执行deductions['Federal Taxes'] > 0.2操作时，由于0.2默认是double类型，所以需要进行类型转换。0.2在float类型下实际值是0.2000001，在double类型下实际值是0.200000000001，因为double是8个字节，所以有更多的标识位。当float类型的0.2转化为double类型时，值为0.200000100000，所以会比0.200000000001大,最终出现以上查询结果。

    改进做法：

    （1）定义表字段时，使用范围最大的类型，这里使用double，这样可以避免这种结果。但是占用内存较大

    （2）在查询条件中，进行类型转换，比如deductions['Federal Taxes'] > cast(0.2 AS FLOAT)

    （3）hive中不支持“0.2f”这种写法，多以不能直接比较

    2.2 having 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;

    2.3 Inner JOIN  内连接

hive> SELECT a.ymd, a.price_close, b.price_close

> FROM stocks a JOIN stocks b ON a.ymd = b.ymd

> WHERE a.symbol = 'AAPL' AND b.symbol = 'IBM';

hive做内连接时不支持以下操作：

SELECT a.ymd, a.price_close, b.price_close

FROM stocks a JOIN stocks b

ON a.ymd <= b.ymd

WHERE a.symbol = 'AAPL' AND b.symbol = 'IBM';

SELECT a.ymd, a.price_close, b.price_close

FROM stocks a JOIN stocks b

ON a.ymd = b.ymd  OR a.symbol=b.symbol

WHERE a.symbol = 'AAPL' ;

           在hive的ON字句不支持‘<=’和‘OR’这种条件连接符（放大程序结果），支持‘AND’操作符， 这主要是因为很难通过mapreduce实现这个操作。PIG通    过向量积特征，可是实现这个操作，但也非本地化操作。

    2.4 join 优化策略

       （1） hive假设在join查询语句中的最后一个表是最大的，所以试着缓存其它几个表，然后通过流读取最后的表。所以应该结构化join查询，把最大的表放在最后面。幸运的是，hive有一个“hint”机制，它会告诉优化器哪一个表应该通过流读取，而不需要我们手动设置最后一个表

        （2）使用外连接时，一定要注意where字句的查询条件，因为hive先执行ON字句。然后执行WHERE字句，所以如果WHERE字句使用不当会过滤掉一些正确的数据，如下：

hive> SELECT s.ymd, s.symbol, s.price_close, d.dividend

> FROM stocks s LEFT OUTER JOIN dividends d ON s.ymd = d.ymd AND s.symbol = d.symbol

> WHERE s.symbol = 'AAPL'

> AND s.exchange = 'NASDAQ' AND d.exchange = 'NASDAQ';

修改如下：

        hive> SELECT s.ymd, s.symbol, s.price_close, d.dividend

> FROM stocks s LEFT OUTER JOIN dividends d ON s.ymd = d.ymd AND s.symbol = d.symbol

> WHERE s.symbol = 'AAPL' AND s.exchange = 'NASDAQ';

        （3）当使用join字句时，分区过滤器在外连接（Outer join）ON子句中不起作用，但是在内连接时还是可以使用的。

        注：分区过滤器是指以分区字段作为判断条件，比如表A,有分区字段a，当A参加表外连接时，ON子句中有A.a=‘XXX’时，是不起作用的

    2.X  ORDER BY 、SORT BY、DISTRIBUTE BY与CLUSTER BY

        ORDER BY

        SELECT s.ymd, s.symbol, s.price_close

FROM stocks s

ORDER BY s.ymd ASC, s.symbol DESC;

        在hive中ORDER BY 和其它SQL中的定义是一样的，对全局的数据集合进行排序，但是体现在mapreduce中就是只有一个reduce输出，如果是大数据集这将会用掉很长的时间。

        SORT BY

        SELECT s.ymd, s.symbol, s.price_close

FROM stocks s

SORT BY s.ymd ASC, s.symbol DESC;

        SORT BY是针对mapreduce模式的特殊排序方式，这种排序的特色是每个reduce的输出数据都是有序的，但是全局数据不一定有序。当只有一个reduce时，这种排序方式与ORDER BY方式结果是相同的。

        DISTRIBUTE BY（分散，分布）

        hive> SELECT s.ymd, s.symbol, s.price_close

> FROM stocks s

> DISTRIBUTE BY s.symbol

> SORT BY s.symbol ASC, s.ymd ASC;

        这种排序方式是针对 ORDER BY 这种排序方式不足的地方进行的弥补。在hadoop中，通过mapreduce进行处理数据时，每条数据（key:value）是根据key值的hash值确定分发到哪一个reduce进行处理，所以只有相同key值的记录才可以出现在同一个reduce上。这里用了同样的原理，通过某一个列值，把具有相同值的记录分散到同一个reduce，那么再结合 SORT BY，则具有相同的某一列值的记录都会出现在同一个reduce的输出中 ，且是按某字段有序的。如上例：

    具有相同  s.symbol 值的记录都分发到相同的reduce中，且具有相同  s.symbol值的记录是按 s.ymd有序的。

        CLUSTER BY

hive> SELECT s.ymd, s.symbol, s.price_close

> FROM stocks s

> CLUSTER BY s.symbol;

从名字可以看出，就是把有相同值的数据聚集在一起

    2.XX  Casting 类型转换

SELECT name, salary FROM employees    

WHERE cast(salary AS FLOAT) < 100000.0;

类型转换，主要是针对数字类型cast(value AS TYPE).

针对二进制类型数据进行转换

SELECT (2.0*cast(cast(b as string) as double)) from src;

        当你确定二进制数据表示的是数字类型时，可以先把数据转化为字符串，然后把字符串转化为数字

待续。。。。