Hive表的高级查询操作

Hive高级查询

1. 前置准备

实验环境

1. Oracle Linux 7.4
2. Java1.8.0_144
3. Hadoop2.7.4
4. Hive2.1.1

实验数据

stocks.csv内容以逗号“,”分隔，依次记录股票代码、股票交易日期、股票开盘价、股票开盘价、股票最低价、股票收盘价、股票交易量和股票成交价。

2. 实验流程

2.1 创建表

依据stocks.csv内容,创建外部表stocks

CREATE EXTERNAL TABLE hql_stocks(
exchanger 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 ',';

加载stocks.csv文件内容至表hql_stocks中

LOAD DATA LOCAL INPATH '/root/experiment/datas/hive/stocks.csv' OVERWRITE INTO TABLE hql_stocks;

查询表hql_stocks中的所有内容

2.2 查询表

2.2.0 HAVING

查询股票交易所NASDAO每支股年销售额的平均收盘价>$10.0的信息。其中平均收盘价的最终显示结果保留2位小数。

SELECT symbol,year(ymd),bround(avg(price_close),2) FROM hql_stocks
WHERE exchanger='NASDAQ'
GROUP BY symbol,year(ymd)
HAVING avg(price_close) >10.0;

可用嵌套SELECT子句，代替HAVING查询。

SELECT s.symbol,s.year,s.avg FROM(
SELECT symbol,year(ymd) AS year,bround(avg(price_close),2) AS avg FROM hql_stocks
WHERE exchanger='NASDAQ'
GROUP BY symbol,year(ymd)) s
WHERE s.avg >10.0;

ORDER BY与SORT BY

为更好地查看排序针对全排序还是Reducer，设置Yarn运行HiveQL。

SET mapreduce.framework.name=yarn;
-- 设置HiveQL语句启动Reducer数量为2
SET mapred.reduce.tasks=2;

使用ORDER BY和SORT BY，查询股票中的日期、股票代码和收盘价，且按日期降序、相同日期下按股票代码升序排序，对比二种排序用法。

2.2.1 ORDER BY

ORDER BY查询，所有数据按年降序排序，同年的数据按收盘价降序排列

SELECT ymd,symbol,price_close
FROM hql_stocks
ORDER BY year(ymd) DESC,price_close ASC;

2.2.2 SORT BY

SORT BY查询，启动2个Reducer，每个Reducer里所有数据按年降序排序，同年的数据按收盘价降序排列

SELECT ymd,symbol,price_close
FROM hql_stocks
SORT BY year(ymd) DESC,price_close ASC;

通过DISTRIBUTE BY和CLUSTER BY二种方法，实现股票信息查询结果按年升序排序，其中确保证所有具有相同年份的记录分发到同一个Reducer中进行处理。

2.2.3 DISTRIBUTE BY

SELECT ymd,symbol,price_close
FROM hql_stocks
DISTRIBUTE BY year(ymd)
SORT BY year(ymd) ASC;

2.2.4 CLUSTER BY

SELECT ymd,symbol,price_close
FROM hql_stocks
CLUSTER BY year(ymd);

2.2.5 JOIN

设置Hive本地执行模式，数据量不大时，可考虑本地执行加快执行速度。

SET mapreduce.framework.name=local;

内连接写法一：求苹果（APPL）公司的股票收盘价与IBM公司收盘价每日价格对比表

SELECT a.ymd,a.price_close,b.price_close
FROM
(SELECT ymd,price_close FROM hql_stocks WHERE symbol='APPL') a,
(SELECT ymd,price_close FROM hql_stocks WHERE symbol='IBM') b
WHERE a.ymd=b.ymd;

内连接写法二，也是推荐写法：求苹果（APPL）公司的股票收盘价与IBM公司收盘价每日价格对比表。

SELECT a.ymd,a.price_close,b.price_close
FROM
(SELECT ymd,price_close FROM hql_stocks WHERE symbol='APPL') a
INNER JOIN
(SELECT ymd,price_close FROM hql_stocks WHERE symbol='IBM') b
ON a.ymd=b.ymd;

左外连接：APPL苹果公司查询一下它的收盘价，同时，如果同期IBM公司有收盘价就显示，没有不显示或者NULL。

SELECT a.ymd,a.symbol,a.price_close,b.symbol,b.price_close
FROM
(SELECT ymd,symbol,price_close FROM hql_stocks WHERE symbol='APPL') a
LEFT OUTER JOIN
(SELECT ymd,symbol,price_close FROM hql_stocks WHERE symbol='IBM') b
ON a.ymd=b.ymd;

右外连接：将上例的LEFT改成RIGHT，结果显示IBM公司的收盘价，同期苹果公司有收盘价就显示，没有不显示或者NULL。

SELECT a.ymd,a.symbol,a.price_close,b.symbol,b.price_close
FROM
(SELECT ymd,symbol,price_close FROM hql_stocks WHERE symbol='APPL') a
RIGHT OUTER JOIN
(SELECT ymd,symbol,price_close FROM hql_stocks WHERE symbol='IBM') b
ON a.ymd=b.ymd;

全外连接：IBM公司和苹果（APPL）公司收盘价内容全部显示出来

SELECT a.ymd,a.symbol,a.price_close,b.symbol,b.price_close
FROM
(SELECT ymd,symbol,price_close FROM hql_stocks WHERE symbol='APPL') a
FULL OUTER JOIN
(SELECT ymd,symbol,price_close FROM hql_stocks WHERE symbol='IBM') b
ON a.ymd=b.ymd;

3. 流程总结

1）通过HAVING与SELECT嵌套查询实现同一功能，理解HAVING用法。

2）通过ORDER BY与SORT BY执行类似语法，对比二种排序的区别。

3）通过DISTRIBUTE BY和CLUSTER BY实现同一功能，理解二者用法。

4）通过JOIN实现内、左外、右外和全连接应用，理解JOIN用法。

注意:
排序列必须出现在SELECT column列表中
为了充分利用所有的Reducer来执行全局排序，可以先使用CLUSTER BY，然后使用ORDER BY

申明:文章仅做记录，涉及侵权内容请联系删除