实验六：熟悉Hive的基本操作_从stocks.csv文件向stocks表中导入数据

由于CSDN上传md文件总是会使图片失效
完整的实验文档地址如下：
https://download.csdn.net/download/qq_36428822/85709631?spm=1001.2014.3001.5501

---

“大数据技术原理与应用”课程实验报告

题目：实验六：熟悉Hive的基本操作 姓名：小猪猪 日期：2022/5/15

1、实验环境：

设备名称 LAPTOP-9KJS8HO6
处理器 Intel® Core™ i5-10300H CPU @ 2.50GHz 2.50 GHz
机带 RAM 16.0 GB (15.8 GB 可用)
主机操作系统 Windows 10 家庭中文版
虚拟机操作系统 ubuntukylin-16.04
Hadoop 版本 3.1.3
JDK 版本 1.8
Java IDE：Eclipse
系统类型 64 位操作系统, 基于 x64 的处理器
笔和触控 没有可用于此显示器的笔或触控输入

2、实验内容与完成情况：

1.创建一个内部表 stocks，字段分隔符为英文逗号，表结构如下所示：
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
Shell命令：

1.	create table if not exists stocks  
2.	(  
3.	`exchange` string,  
4.	`symbol` string,  
5.	`ymd` string,  
6.	`price_open` float,  
7.	`price_high` float,  
8.	`price_low` float,  
9.	`price_close` float,  
10.	`volume` int,  
11.	`price_adj_close` float  
12.	)  
13.	row format delimited fields terminated by ',';  
1
2
3
4
5
6
7
8
9
10
11
12
13

图1. 创建一个内部表 stocks

2.创建一个外部分区表 dividends（分区字段为 exchange 和 symbol），字段分隔符为英文逗号，表结构如下所示：
dividends 表结构
col_name data_type
ymd String
dividend Float
exange String
symbol string

Shell命令：

1.	create external table if not exists dividends  
2.	(  
3.	`ymd` string,  
4.	`dividend` float  
5.	)  
6.	partitioned by(`exchange` string ,`symbol` string)  
7.	row format delimited fields terminated by ',';  
1
2
3
4
5
6
7

图2. 创建一个外部表 dividends

3.从 stocks.csv 文件向 stocks 表中导入数据：
Shell命令：
load data local inpath ‘/usr/local/hive/stocks.csv’ overwrite into table stocks;

4.创建一个未分区的外部表 dividends_unpartitioned，并从 dividends.csv 向其中导入数据，表结构如下所示：
dividends_unpartitioned 表结构
col_name data_type
ymd String
dividend Float
exange String
symbol string
Shell命令：

1.	create external table if not exists dividends_unpartitioned  
2.	(  
3.	`exchange` string ,  
4.	`symbol` string,  
5.	`ymd` string,  
6.	`dividend` float  
7.	)  
8.	row format delimited fields terminated by ',';
load data local inpath '/usr/local/hive/dividends.csv' overwrite into table dividends_unpartitioned;
1
2
3
4
5
6
7
8
9

图3. 创建一个未分区的外部表 dividends_unpartitioned并导入数据

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

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

图4. 向分区表 dividends 各个分区中插入对应数据

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

1.	select s.ymd,s.symbol,s.price_close  
2.	from stocks s   
3.	LEFT SEMI JOIN   
4.	dividends d  
5.	ON s.ymd=d.ymd and s.symbol=d.symbol  
6.	where s.symbol='IBM' and year(ymd)>=2000; 
1
2
3
4
5
6

图5. 查询IBM公司收盘价

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

1.	select ymd,  
2.	case  
3.	    when price_close-price_open>0 then 'rise'  
4.	    when price_close-price_open<0 then 'fall'  
5.	    else 'unchanged'  
6.	end as situation  
7.	from stocks  
8.	where symbol='AAPL' and substring(ymd,0,7)='2008-10';
1
2
3
4
5
6
7
8

图6. 查询苹果公司涨跌情况

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

1.	select `exchange`,`symbol`,`ymd`,price_close,price_open,price_close-price_open as `diff`  
2.	from  
3.	(  
4.	    select *  
5.	    from stocks  
6.	    order by price_close-price_open desc  
7.	    limit 1  
8.	)t;
1
2
3
4
5
6
7
8

图7. 查询 stocks 表中收盘价（price_close）比开盘价（price_open）高得最多的那条记录

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

1.	select  
2.	    year(ymd) as `year`,  
3.	    avg(price_adj_close) as avg_price from stocks  
4.	where `exchange`='NASDAQ' and symbol='AAPL'  
5.	group by year(ymd)  
6.	having avg_price > 50;
1
2
3
4
5
6

图8. 查询平均调整后收盘价

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

1.	select t2.`year`,symbol,t2.avg_price  
2.	from  
3.	(  
4.	    select  
5.	        *,row_number() over(partition by t1.`year` order by t1.avg_price desc) as `rank`  
6.	    from  
7.	    (  
8.	        select  
9.	            year(ymd) as `year`,  
10.	            symbol,  
11.	            avg(price_adj_close) as avg_price  
12.	        from stocks  
13.	        group by year(ymd),symbol  
14.	    )t1  
15.	)t2  
16.	where t2.`rank`<=3;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

图9. 查询收盘价前三名股票公司的年平均收盘价

3、出现的问题：

1、HIVE SLF4J: Class path contains multiple SLF4J bindings. 启动Hive时报错

图10. 启动Hive时报错

2、在hive-3.1.x版本中，使用hive shell时，会发现在查询命令中夹杂大量的日志信息，严重干扰查询结果显示，特别是强迫症患者，不能忍受。通过修改conf下的日志文件，会发现不起任何作用。

图11. 使用hive时产生大量日志信息

3、一开始启动hive1.2.1时，报错提示hadoop的版本无法识别

图12. 启动hive时提示hadoop版本无法识别

4、启动hive3.1.2时，报错Exception in thread “main” java.lang.NoSuchMethodError: com.google.common.base.Preconditio

图13. 启动hive3.1.2时提示异常

4、解决方案：

1、hadoop和hive的jar包重复了，删除其中一个jar包即可。

2、
解决方法：
1.在 Hive 安装目录的 conf 目录下创建出 log4j.properties 日志配置文件，以WARN 级别为例：

2.添加如下内容：

log4j.rootLogger=WARN, CA
log4j.appender.CA=org.apache.log4j.ConsoleAppender
log4j.appender.CA.layout=org.apache.log4j.PatternLayout
log4j.appender.CA.layout.ConversionPattern=%-4r [%t] %-5p %c %x - %m%n

重启hive，世界清净了。

3、我使用的时hadoop3.1.3，而第一次安装的hive是1.2.1，其中有的jar包版本不匹配，最好的办法是安装更新的hive版本，我重装了hive3.1.2，问题解决了。

4、
考虑到此包是guava组件提供，猜测可能是HIVE和HADOOP内置的jar包版本不一致导致，文件包如下：
hive/lib:guava-19.0.jar
hadoop/lib:guava-27.0-jre.jar，此jar包的位置在share/hadoop/common/lib下面
我们删除掉旧版本的19，将新版本拷贝进去，再次运行bin/hive，问题得以解决