12.29

实验6

熟悉Hive的基本操作

 

1.实验目的

（1）理解Hive作为数据仓库在Hadoop体系结构中的角色。

（2）熟练使用常用的HiveQL。

2.实验平台

操作系统：Ubuntu18.04（或Ubuntu16.04）。

Hadoop版本：3.1.3。

Hive版本：3.1.2。

JDK版本：1.8。

3.数据集

由《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

解压后可以得到本实验所需的stocks.csv和dividends.csv两个文件。

4.实验步骤

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

表14-11 stocks表结构

col_name	data_type
exchange	string
symbol	string
ymd	string
price_open	float
price_high	float
price_low	float
price_close	float
volume	int
price_adj_close	float

CREATE TABLE 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 ',' STORED

AS TEXTFILE;

 

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

表14-12 dividends表结构

col_name	data_type
ymd	string
dividend	float
exchange	string
symbol	string

CREATE EXTERNAL TABLE

dividends ( ymd STRING, dividend FLOAT )

PARTITIONED BY

(`exchange` STRING, `symbol` STRING)

ROW FORMAT DELIMITED FIELDS TERMINATED BY ','

STORED AS TEXTFILE LOCATION

'/path/to/dividends/partitions';

 

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

LOAD DATA LOCAL INPATH '/path/to/stocks.csv' INTO TABLE stocks;

 

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

表14-13 dividends_unpartitioned表结构

col_name	data_type
ymd	string
dividend	float
exchange	string
symbol	string

CREATE EXTERNAL

 TABLE dividends_unpartitioned

 ( ymd STRING, dividend FLOAT, `exchange` STRING, `symbol` STRING )

ROW FORMAT DELIMITED FIELDS TERMINATED

BY ',' STORED

AS TEXTFILE LOCATION '/usr/hadoop/dividends/';

 

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

在插入数据之前，必须确保启用了Hive的动态分区功能。您可以使用以下命令设置相关参数：

 

0: jdbc:hive2://localhost:10000> SET hive.exec.dynamic.partition=true; 0: jdbc:hive2://localhost:10000> SET hive.exec.dynamic.partition.mode=nonstrict; 0: jdbc:hive2://localhost:10000> SET hive.exec.max.dynamic.partitions.pernode=1000; 0: jdbc:hive2://localhost:10000> SET hive.exec.max.dynamic.partitions=1000; 0: jdbc:hive2://localhost:10000> SET hive.exec.max.created.files=10000; 0: jdbc:hive2://localhost:10000> INSERT INTO TABLE dividends PARTITION (`exchange`, `symbol`) . SELECT ymd, dividend, `exchange`, `symbol` . FROM dividends_unpartitioned;

SELECT * FROM ( SELECT ymd, dividend, `exchange`, `symbol` FROM dividends_unpartitioned ) AS subquery; -- 子查询必须有别名

 

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

 

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

SELECT ymd, -- 交易日期 .

CASE WHEN price_close > price_open

 THEN 'rise' WHEN price_close < price_open

 THEN 'fall' ELSE 'unchange' END AS change -- 涨跌情况

 FROM stocks WHERE symbol = 'AAPL' -- 苹果公司的股票代码

 AND ymd LIKE '2008-10-%'; -- 仅选择2008年10月的数据

 

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

SELECT exchange, -- 交易所

 symbol, -- 股票代码

 ymd, -- 交易日期 price_close,

-- 收盘价 price_open,

 -- 开盘价 (price_close - price_open) AS price_diff --

二者差价 FROM stocks ORDER BY price_diff DESC LIMIT 1;

 

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

SELECT year(ymd) AS year, -- 年份

 AVG(price_adj_close) AS avg_price_adj_close -- 年平均调整后收盘价

 FROM stocks WHERE symbol = 'AAPL' -- 苹果公司的股票代码

 GROUP BY year(ymd) HAVING AVG(price_adj_close) > 50; -- 过滤出年平均调整后收盘价大于50美元的年份

 

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

WITH yearly_avg_prices AS ( SELECT year(ymd) AS year, -- 年份

 symbol, -- 股票代码

AVG(price_adj_close) AS avg_price_adj_close, -- 年平均调整后收盘价

 ROW_NUMBER() OVER (PARTITION BY year(ymd) ORDER BY AVG(price_adj_close) DESC) AS rank -- 排序

FROM stocks GROUP BY year(ymd), symbol ) SELECT year, -- 年份

symbol, -- 股票代码

 avg_price_adj_close -- 年平均调整后收盘价

FROM yearly_avg_prices WHERE rank <= 3 -- 只选择排名前三的公司

 ORDER BY year, rank;

 

5.实验报告

题目：	实验6   熟悉Hive的基本操作	姓名	胡铁丞	日期： 2024-12-9日
实验环境：实验环境：   （1）操作系统：Linux；   （2）Hadoop版本：3.1.0。   （3）虚拟机：VMware。   （4）工具：xshell。   （5）JDK版本：1.8；   （6）Java IDE：IDEA。   （7）Hive：3.1.2
实验内容与完成情况：完成
出现的问题：（1）hive启动报错   （2）运行hive (default)> load data local inpath '/hive_first/stocks.csv' overwrite into table stocks; FAILED: SemanticException Line 1:23 Invalid path ''/hive_first/stocks.csv'': No files matching path file:/hive_first/stocks.csv
解决方案（列出遇到的问题和解决办法，列出没有解决的问题）： （1）启动Hive之前，先启动metastore   （2）改为load data inpath '/hive_first/stocks.csv' overwrite into table stocks;因为stocks.csv存到了hdfs上而不是本地