实验六：熟悉Hive的基本操作
由于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. 创建一个内部表 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 ',';
图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;
图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;
图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;
图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';
图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;
图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;
图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;
图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，问题得以解决