实验6 熟悉Hive的基本操作

一、实验目的

（1）理解 Hive 作为数据仓库在 Hadoop 体系结构中的角色。
（2）熟练使用常用的 HiveQL。

二、实验平台

• 操作系统：Ubuntu18.04（或Ubuntu16.04）；
• Hadoop版本：3.1.3；
• Hive版本：3.1.2；
• JDK版本：1.8。

三、数据集

准备工作：

• 由《Hive编程指南》(O’Reilly系列，人民邮电出版社)提供，下载地址： https://raw.githubusercontent.com/oreillymedia/programming_hive/master/prog-hive-1st-ed-data.zip
• 备用下载地址： https://www.cocobolo.top/FileServer/prog-hive-1st-ed-data.zip
• 下载慢可参考我上传的资源：林子雨Hive数据集下载

解压后可以得到本实验所需的

stocks.csv

和

dividends.csv

两个文件。

进入你的 Downloads（下载）文件夹，右键解压刚下载的数据压缩包，进入

prog-hive-1st-ed-data

文件夹，右键打开终端：

cd ~/Downloads/prog-hive-1st-ed-data
sudocp ./data/stocks/stocks.csv /usr/local/hive
sudocp ./data/dividends/dividends.csv /usr/local/hive

进入 Hadoop 目录，启动 Hadoop：

cd /usr/local/hadoop
sbin/start-dfs.sh

启动 MySQL：

service mysql start

切换到 Hive 目录下，启动 MySQL 和 Hive：

cd /usr/local/hive
bin/hive

四、实验步骤

（1）创建一个内部表 stocks，字段分隔符为英文逗号，表结构如下所示：

stocks 表结构：
col_namedata_typeexchangestringsymbolstringymdstringprice_openfloatprice_highfloatprice_lowfloatprice_closefloatvolumeintprice_adj_closefloat
代码：

createtableifnotexists 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 fieldsterminatedby',';

查看表：

hive>describe stocks;
OK
exchange                string                                      
symbol                  string                                      
ymd                     string                                      
price_open              float                                       
price_high              float                                       
price_low               float                                       
price_close             float                                       
volume                  int                                         
price_adj_close         floatTime taken: 0.062 seconds, Fetched: 9row(s)
hive>

（2）创建一个外部分区表 dividends（分区字段为 exchange 和 symbol），字段分隔符为英文逗号，表结构如下所示：

dividends 表结构
col_namedata_typeymdstringdividendfloatexchangestringsymbolstring
代码：

create external tableifnotexists dividends
(`ymd` string,`dividend`float)
partitioned by(`exchange` string ,`symbol` string)row format delimited fieldsterminatedby',';

查看表：

hive>describe dividends;
OK
ymd                     string                                      
dividend                float                                       
exchange                string                                      
symbol                  string                                      
          
# Partition Information          # col_name                data_type               comment             
exchange                string                                      
symbol                  string                                      
Time taken: 0.106 seconds, Fetched: 9row(s)
hive>

（3）从 stocks.csv 文件向 stocks 表中导入数据：

代码：

loaddatalocal inpath '/usr/local/hive/stocks.csv' overwrite intotable stocks;

（4） 创建一个未分区的外部表 dividends_unpartitioned，并从 dividends.csv 向其中导入数据，表结构如下所示：

dividends_unpartitioned 表结构
col_namedata_typeymdstringdividendfloatexchangestringsymbolstring
代码：

create external tableifnotexists dividends_unpartitioned
(`exchange` string ,`symbol` string,`ymd` string,`dividend`float)row format delimited fieldsterminatedby',';

导入数据：

loaddatalocal inpath '/usr/local/hive/dividends.csv' overwrite intotable dividends_unpartitioned;

（5）通过对 dividends_unpartitioned 的查询语句，利用 Hive 自动分区特性向分区表 dividends 各个分区中插入对应数据。

代码：

set hive.exec.dynamic.partition=true;set hive.exec.dynamic.partition.mode=nonstrict;set hive.exec.max.dynamic.partitions.pernode=1000;insert overwrite table dividends partition(`exchange`,`symbol`)select`ymd`,`dividend`,`exchange`,`symbol`from dividends_unpartitioned;

（6）查询IBM公司（symbol = IBM）从 2000 年起所有支付股息的交易日（dividends 表中有对应记录）的收盘价（price_close）。

操作语句如下：

select s.ymd,s.symbol,s.price_close
from stocks s 
LEFT SEMI JOIN 
dividends d
ON s.ymd=d.ymd and s.symbol=d.symbol
where s.symbol='IBM'andyear(ymd)>=2000;

输出如下（折叠部分输出）：

2010-02-08    IBM    121.882009-11-06    IBM    123.492009-08-06    IBM    117.38...2000-05-08    IBM    109.752000-02-08    IBM    118.81Time taken: 8.75 seconds, Fetched: 41row(s)

（7）查询苹果公司（symbol = AAPL）2008 年 10 月每个交易日的涨跌情况，涨显示 rise，跌显示 fall，不变显示 unchange。

操作语句如下：

select ymd,casewhen price_close-price_open>0then'rise'when price_close-price_open<0then'fall'else'unchanged'endas situation
from stocks
where symbol='AAPL'and substring(ymd,0,7)='2008-10';

输出如下（折叠部分输出）：

2008-10-31    rise
2008-10-30    rise
...2008-10-02    fall
2008-10-01    fall
Time taken: 0.1 seconds, Fetched: 23row(s)

（8）查询 stocks 表中收盘价（price_close）比开盘价（price_open）高得最多的那条记录的交易所（exchange）、股票代码（symbol）、日期（ymd）、收盘价、开盘价及二者差价。

操作语句如下：

select`exchange`,`symbol`,`ymd`,price_close,price_open,price_close-price_open as`diff`from(select*from stocks
    orderby price_close-price_open desclimit1)t;

输出如下：

NASDAQ    INFY    2000-02-11670.06534.5135.56Time taken: 4.476 seconds, Fetched: 1row(s)

9）从 stocks 表中查询苹果公司（symbol=AAPL）年平均调整后收盘价（price_adj_close）大于 50 美元的年份及年平均调整后收盘价。

操作语句如下：

selectyear(ymd)as`year`,avg(price_adj_close)as avg_price from stocks
where`exchange`='NASDAQ'and symbol='AAPL'groupbyyear(ymd)having avg_price >50;

输出如下：

200670.810637531052552007128.273904230490162008141.97901150548882009146.814127119760662010204.72159912109376Time taken: 2.347 seconds, Fetched: 5row(s)

（10）查询每年年平均调整后收盘价（price_adj_close）前三名的公司的股票代码及年平均调整后收盘价。

操作语句如下：

select t2.`year`,symbol,t2.avg_price
from(select*,row_number()over(partitionby t1.`year`orderby t1.avg_price desc)as`rank`from(selectyear(ymd)as`year`,
            symbol,avg(price_adj_close)as avg_price
        from stocks
        groupbyyear(ymd),symbol
    )t1
)t2
where t2.`rank`<=3;

输出如下（折叠部分输出）：

NULL    stock_symbol    NULL1962    IBM    2.00722221344236341962    GE    0.16876984293025638...2009    GTC    174.116071156093062010    ISRG    319.753601074218752010    AMEN    313.8752010    GTC    214.36719848632814Time taken: 7.715 seconds, Fetched: 140row(s)

五、总结