第一章：上次课回顾
- 零基础班第十五课 - Hive DDL详解：
https://blog.csdn.net/zhikanjiani/article/details/89075266
重点是：
1、Hive的数据库、表的创建、修改、删除相关；
2、创建数据库、表都是可以指定location的，也可以不指定的；
3、default库对应的hdfs路径在哪，自己创建的数据库的路劲在哪？
1、default库对应的hdfs的路径如下：
hive (default)> desc database default;
OK
default Default Hive database hdfs://localhost:9000/user/hive/warehouse public ROLE
Time taken: 0.102 seconds, Fetched: 1 row(s)
2、自己创建的ruozeg6数据库的hdfs的路径：
hive (default)> desc database ruozeg6;
OK
ruozeg6 hdfs://hadoop004:9000/user/hive/warehouse/ruozeg6.db hadoop USER
Time taken: 0.432 seconds, Fetched: 1 row(s)
3、自己创建的数据库也可以不使用/user/hive/warehouse默认路径，而是自己指定路径，如下：
hive (default)> desc database hive2;
OK
hive2 hdfs://hadoop004:9000/d6/hive/directory hadoop USER
Time taken: 0.108 seconds, Fetched: 1 row(s)
内部表和外部表的区别一定要搞懂
- 还简单讲了Hive中使用load加载数据
DML：
在Hive中需要注意的一点：
- 在关系型数据库中是通过insert的方式写进去的；
- 但是在大数据场景中，使用insert的场景是不多的；
- 大部分加载数据都是采用load的方式加载到表中去（把一批文件load到表中去），实际就是把数据加载到hdfs上，因为meta信息已经有了；所以就能直接进行查询。
第二章：Hive DML语法
官网上显示：
DML (load/insert/update/delete/merge, import/export, explain plan)
2.1 ：LOAD的用法
语法：
-
LOAD DATA [LOCAL] INPATH ‘filepath’ [OVERWRITE] INTO TABLE tablename [PARTITION]
-
解释：local 指的是本地路径，有local默认是本地linux，不带local的话它会去hdfs的路径上找；overwrite指的是表内容是否覆盖。
开始进行测试：
1、在本地路径：/home/hadoop/data 下vi一个dept.txt
[hadoop@hadoop004 data]$ pwd
/home/hadoop/data
[hadoop@hadoop004 data]$ cat dept.txt
10 accouting newwork
20 restart china
30 sales japan
2、 此时在hive中创建一张表：
create table dept(
deptno string,
dname string,
location string
)row format delimited fields terminated by ‘\t’;
- 此时表创建完成，暂时为空表。
3、使用load加载dept.txt文件内容至dept表
命令：
- load data local inpath ‘/home/hadoop/data/dept.txt’ overwrite into table dept;
hive> load data local inpath '/home/hadoop/data/dept.txt' overwrite into table dept;
Loading data to table g6.dept
[Warning] could not update stats.
OK
Time taken: 25.649 seconds
hive> select * from dept;
OK
10 accouting newwork
20 restart china
30 sales japan
Time taken: 0.545 seconds, Fetched: 3 row(s)
2.2、测试有无overwrite的区别
测试有无overwrite的区别
hive> load data local inpath '/home/hadoop/data/dept.txt' into table dept;
hive> select * from dept;
OK
10 accouting newwork
20 restart china
30 sales japan
10 accouting newwork
20 restart china
30 sales japan
Time taken: 0.087 seconds, Fetched: 6 row(s)
官网中对有无overwrite的描述：
-
If the OVERWRITE keyboards is used then the contents of the target table (or partition) will be deleted and replaced by the files referred to by filepath; otherwise the files referred by filepath will be added to the table.
-
无overwrite是追加，有overwrite是覆盖
2.3、测试有无local的区别
- 目前我们使用的是local指定到linux本地路径，假设我们不使用local。
1、把data目录下的dept.txt文件上传到hdfs文件系统上：
[hadoop@hadoop004 data]$ hdfs dfs -mkdir -p /hive/dept
[hadoop@hadoop004 data]$ hdfs dfs -put dept.txt /hive/dept/
[hadoop@hadoop004 data]$ hdfs dfs -ls /hive/dept
Found 1 items
-rw-r--r-- 1 hadoop supergroup 53 2019-04-18 23:59 /hive/dept/dept.txt
2、把hdfs上的文件加载到dept表中（此处选择的是overwrite）
- load data inpath ‘/hive/dept/dept.txt’ overwrite into table dept;
3、我们再去hdfs路径上/hive/dept/dept.txt上查看：
[hadoop@hadoop004 data]$ hdfs dfs -ls /hive/dept
19/09/10 07:24:24 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
- 查看到该文件夹下为空，第2步中的load操作就相当于是把这份数据移走了。
同时需要注意：
- ‘/hive/data/dept.txt’ 这个hdfs路径可以补全 ==> hdfs://hadoop004:9000/hive/dept/dept.txt，因为我们是在系统里面配置的，所以头部没有 ‘hdfs://hadoop004:9000’ 也能识别。
内部表与外部表区别？
https://blog.csdn.net/zhikanjiani/article/details/94492610
2.4、Hive中insert的使用
insert语法如下：
1、insert语句的底层是要跑MapReduce作业的.
INSERT OVERWRITE LOCAL DIRECTORY '/home/hadoop/tmp/d6/'
row format delimited fields terminated by ',' //输出的结果以逗号为分割
select deptnu,dname from dept; //跟上的SQL语句
2、在/home/hadoop/tmp/d6目录下，打印输出文件，得到结果
[hadoop@hadoop004 d6]$ pwd
/home/hadoop/tmp/d6
[hadoop@hadoop004 d6]$ ll
total 4
-rw-r--r--. 1 hadoop hadoop 33 Apr 19 00:25 000000_0
[hadoop@hadoop004 d6]$ cat 000000_0
10,accouting
20,restart
30,sales
3、2的结果就等价于
create table xxx as select deptno,dname from dept;
insert是在hive0.14版本中出现的，语法上是支持的，但是生产上是拒绝使用的。
生产举例：
-
PK哥公司1minute几百G数据，插入会产生很多小文件，需要保证每一次数据处理的幂等性（不管跑多少次都要保证结果为真）；insert的前提肯定是没有的数据才需要insert。
大数据忌讳：像webUI界面一样，点击按钮去跑MapReduce作业，要看数据量以及资源量 -
hive中delete删除数据，首先要去找这个数据在哪再去删除，所以性能是非常低的。
幂等性：
- 数据丢了每一个批次重跑就行（就是不论运行了多少次，每一次运行的结果都是一样的）
- 对于一张表来说，我们load一次就够了，load是支持目录的；一个分区就是一个目录，一个分区不会与文件个数有关系，它是到文件夹的，不会关心文件夹下的个数有多少？
- 只要一load，元数据就会关联上；在Hive中，有数据进来（有可能是使用spark清洗、外部数据进来的）
图解：
1、源数据是我们要处理的数据也就是source，假设数据已经进来了在hdfs上，由于日志可能是乱七八糟的，所以要对这份数据做一个清洗（ip解析）；表创建的时候指定分隔符：假设是"\t"，这中间可能就会以逗号分隔开。
2、涉及到ip解析（省份、运营商、地市）<== 肯定要做一个etl
3、etl output出来的文件字段信息必然是和a表一致的，每个字段都要能对应上，etl输出的肯定是存放在hdfs上的；肯定是创建的external表，把hdfs path指向hdfs上的存储
4、a表肯定是要采用列式存储，外部表的典型场景
5、我们要在a表中insert数据，那肯定是源端数据中是没有的，是数据采集端的问题。
web的交互方式遇到的问题：
-
在页面上写一个按钮，按钮去触发Hive、MapReduce作业（数据多久跑完根本不可控），只能拿SQL的语法来套；insert、update、delete都是在大数据中禁用的。
-
假设我们要统计每个省份的topN，我们肯定要把它的结果Insert到B表中去。
-
离线的作业跑20个小时都正常，Pk哥公司一个作业跑2分钟；要想跑的快，加资源，一个作业动辄就上T的内存。
-
删除、更新都要到文件中去找出这个数据在哪，所以这个性能是很差的。
Hadoop体系：Sqoop
Spark体系：Spark SQL
Linux中的Hive体系
2.5、Hive在生产上的使用
1、hive -e
1、hive -e "select * from dept;" 联想到脚本
2、vi a.sh
#！/bin/bash
hive -e "use g6;select * from dept" 选择数据库，再把需要统计的东西写进Hive -e
3、执行这个脚本
./a.sh
2、hive -f
1、编辑一个名称为test.sql的脚本文件
vi test.sql
如下两行是脚本文件中的内容：
use g6;
select * from dept limit 3; 这是一个脚本
2、自己找一个目录编辑即可
[hadoop@hadoop004 d6]$ ll
total 12
-rw-r--r--. 1 hadoop hadoop 33 Apr 19 00:25 000000_0
-rwxrwxr-x. 1 hadoop hadoop 49 Apr 19 00:47 a.sh
-rw-rw-r--. 1 hadoop hadoop 35 Apr 19 00:54 test.sql
[hadoop@hadoop004 d6]$ pwd
/home/hadoop/tmp/d6
3、找到路径，开始执行test.sql
[hadoop@hadoop004 d6]$ hive -f test.sql
Logging initialized using configuration in jar:file:/home/hadoop/app/hive-1.1.0-cdh5.7.0/lib/hive-common-1.1.0-cdh5.7.0.jar!/hive-log4j.properties
OK
Time taken: 0.699 seconds
OK
10 accouting newwork
20 restart china
30 sales japan
Time taken: 0.955 seconds, Fetched: 3 row(s)
hive界面下的清屏操作：！clear; + 回车
第三章：Hive中的SQL语法
-
在Hive中，where、>、<、=、limit、between也是支持的between XXX and XXX、
-
数据使用的是emp表
1、select * from emp limit 5;
- 输出emp表中的前5行
2、select * from emp where sal between 800 and 1500;
- 输出emp表中满足（金额在800到1500）这个区间的所有信息
3、select * from emp where Ename in (‘SMITH’,‘WARD’);
- 我们在3中测试的时候使用了where Ename in，使用desc emp; 查看表结构信息字段名称是ename，测试的时候发现它自动的对字段名称的大小进行了转换。
验证这一猜想：
大小写做了转换去元数据中查看，进入到mysql，columns_v2查看表字段都是小写；
1、mysql -uroot -p 进入数据库
2、use ruoze_g6；这个创建的数据库：问？还记得这个数据库是在哪儿创建的么，路径：/home/hadoop/app/hive/conf 下的hive-site.xml文件进行编辑。
3、show tables；
4、select * from tbls \G; 查看所有表格信息，根据表的id去找字段
5、select * from COLUMNS_V2 limit 5; 所有的列的表。判定肯定把Ename转换为小写了；
如果列名错误的话他会提示找不到字段报错。具体展示如下几张图：
6、select ename from emp where sal is null;
3.1 最基础的SQL进行需求分析
聚合函数：max min sum count avg 多进一出
需求一：统计这张表中最高工资、最低工资、工资总和、工资数、平均工资
-
select max(sal),min(sal),sum(sal),count(sal),avg(sal) from emp;
-
解析：该SQL会去跑MapReduce.
需求二：求每个部门的平均工资
拿到每个部门的数据，在此基础上求平均工资：
- select deptno,avg(sal) from emp group by deptno;
注意点：出现在select中的字段要么出现在group by中，要么出现在聚合函数中；
groupby前后只能对应一个字段。
错误点：
hive (ruozeg6)> select ename,deptno,avg(sal) from emp group by deptno;
FAILED: SemanticException [Error 10025]: Line 1:7 Expression not in GROUP BY key 'ename'
需求三：每个部门下平均薪资>2000
- select deptno,avg(sal) as avg_sal from emp group by deptno where avg_sal > 1000；
注意：此段是错误的，使用group by配合having使用，正确的SQL如下：
- select deptno,avg(sal) as avg_sal from emp group by deptno having avg_sal > 1000；
扩充：groupBy能不能和where搭配使用？
需求四：薪资在某个区间输出高或低
- case when then 在报表系统中比较常用
select ename,sal,
case
when sal >1 and sal <1000 then 'lower' //工资在（1，1000）中，你的工资太低了
when sal >=1000 and sal <2000 then 'middle' //工资在[1000,2000)中，你的工资中等
when sal >=2000 and sal <7000 then 'high' //工资在[2000,7000)中，你的工资很高了
else 'highest' end //其余的是最高的，然后结束
from emp;
了解下用法：它是不走Map Reduce的
3.2 常用内置、外置函数
1、函数 built-in 内置
2、upper lower（外置函数）也叫自定义函数
3、如何知道Hive中内置的函数
- operators and UDFs(user-defined functions)
- 网址：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
4、我们下次要学东西不要直接百度，直接去官网上查看，官网上查看内置函数：最准确的应该是在源码中的体现，源码中怎么找内置函数：
5、在Hive客户端上展示所有的函数
1、Hive下展示所有函数：
hive > show functions
2、查看abs函数的信息：
hive (ruozeg6)> desc function abs; //查看abs函数的信息。
OK
abs(x) - returns the absolute value of x
Time taken: 0.144 seconds, Fetched: 1 row(s)
3、查看abs函数的详细信息，包括案例
hive (ruozeg6)> desc function extended abs; 查看ABS函数的详细信息，包括有案例。
OK
abs(x) - returns the absolute value of x
Example:
> SELECT abs(0) FROM src LIMIT 1;
0
> SELECT abs(-5) FROM src LIMIT 1;
5
Time taken: 0.068 seconds, Fetched: 6 row(s)
绝对值函数：
hive> select abs(-20) from math;
OK
20
Time taken: 2.171 seconds, Fetched: 1 row(s)
hive> desc function extended abs;
OK
abs(x) - returns the absolute value of x
Example:
> SELECT abs(0) FROM src LIMIT 1;
0
> SELECT abs(-5) FROM src LIMIT 1;
5
Time taken: 0.019 seconds, Fetched: 6 row(s)
3.3、使用内置函数进行大小写、时间函数的转换
- select ename,lower(ename) from emp;
3.3.1、第一种：时间函数
1、获取当前时间精确到天数
- select current_date from emp;
hive (ruozeg6)> select current_date from dual;
OK
2019-09-11
Time taken: 0.072 seconds, Fetched: 1 row(s)
2、获取到当前时间精确到秒
- select current_timestamp from dual;
hive (ruozeg6)> select current_timestamp from dual;
OK
2019-09-11 20:55:35.867
3、在工作中经常用的是一个时间戳，日志进来很多都是这种格式。
- select unix_timestamp() from dual;
hive (ruozeg6)> select unix_timestamp() from dual;
OK
1568206640
百度时间戳转换：2019-09-11 20:57:20
日志进来的不同时间格式：
1、将时间转换为时间戳形式：
- select unix_timestamp(“2019-04-19 05:49:26”) from dual;
hive (ruozeg6)> select unix_timestamp("2019-04-19 05:49:26") from dual;
OK
1555624166
3、另外一种时间格式（yyyyMMddHHmmss）转换为时间戳：
- select unix_timestamp(“20190419054926”,“yyyyMMddHHmmss”) from dual;
hive (ruozeg6)> select unix_timestamp("20190419054926","yyyyMMddHHmmss") from dual;
OK
1555624166
4、to_date函数的使用：
- select to_date(“2019-04-19 05:49:26”) from dual;
hive (ruozeg6)> select to_date("2019-04-19 05:49:26") from dual;
OK
2019-04-19
- 返回值是data类型
小结：
我们为什么要转换成字符串类型呢，我们按照天、小时的话直接加一个分区，里面的东西就可以直接按照分区进行过滤。
5、分别获取到"2019-04-19 05:49:26"这个信息中的年、月、日、时、分、秒
- select month（“2019-04-19 05:49:26”）from dual；
- 直接替换year–>month–>day–>hour–>minute–>second
hive (ruozeg6)> select year("2019-04-19 05:49:26") from dual;
OK
2019
Time taken: 0.081 seconds, Fetched: 1 row(s)
hive (ruozeg6)> select month("2019-04-19 05:49:26") from dual;
OK
4
Time taken: 0.078 seconds, Fetched: 1 row(s)
hive (ruozeg6)> select day("2019-04-19 05:49:26") from dual;
OK
19
Time taken: 0.084 seconds, Fetched: 1 row(s)
hive (ruozeg6)> select hour("2019-04-19 05:49:26") from dual;
OK
5
Time taken: 0.06 seconds, Fetched: 1 row(s)
hive (ruozeg6)> select minute("2019-04-19 05:49:26") from dual;
OK
49
Time taken: 0.072 seconds, Fetched: 1 row(s)
hive (ruozeg6)> select second("2019-04-19 05:49:26") from dual;
OK
26
Time taken: 0.062 seconds, Fetched: 1 row(s)
6、完成日期的加减（date add）函数：
- 在此基础上使用减法也是一样
- select date_add(“2019-04-19 05:49:26”,10) from dual;
hive (ruozeg6)> select date_add("2019-04-19 05:49:26",10) from dual;
OK
2019-04-29
Time taken: 0.137 seconds, Fetched: 1 row(s)
hive (ruozeg6)> select date_add("2019-04-19 05:49:26",-10) from dual;
OK
2019-04-09
Time taken: 0.078 seconds, Fetched: 1 row(s)
3.3.2、第二种：Mathematical Functions
1、round函数
- 作用：四舍五入
hive (ruozeg6)> select round(5.6) from dual;
OK
6.0
Time taken: 0.084 seconds, Fetched: 1 row(s)
hive (ruozeg6)> select round(5.4) from dual;
OK
5.0
Time taken: 0.06 seconds, Fetched: 1 row(s)
2、ceil函数
- ceil(x) - Find the smallest integer not smaller than x
hive (ruozeg6)> select ceil(5.6) from dual;
OK
6
Time taken: 0.069 seconds, Fetched: 1 row(s)
hive (ruozeg6)> select ceil(5.4) from dual;
OK
6
Time taken: 0.062 seconds, Fetched: 1 row(s)
hive (ruozeg6)> select ceil(6.1) from dual;
OK
7
3、floor函数
- floor(x) - Find the largest integer not greater than x
hive (ruozeg6)> select floor(6.1) from dual;
OK
6
Time taken: 0.056 seconds, Fetched: 1 row(s)
hive (ruozeg6)> select floor(6.6) from dual;
OK
6
Time taken: 0.058 seconds, Fetched: 1 row(s)
4、substr函数
- 作用：截取字符串
1、位置从第一个字符a开始，从第二个字符b开始往后截取。
- select substr(“abcdefg”,2) from dual;
2、从第二个字符b开始向后截取3位
- select substr(“abcdefg”,2,3) from emp;
hive (ruozeg6)> select substr("abcdefg",2) from dual;
OK
bcdefg
Time taken: 0.08 seconds, Fetched: 1 row(s)
hive (ruozeg6)> select substr("abcdefg",2,3) from dual;
OK
bcd
Time taken: 0.092 seconds, Fetched: 1 row(s)
5、concat函数的用法：
1、把如下字符合在一起
- select concat(“ab”,“cd”,“fg”) from dual;
- 输出结果：abcdfg
6、把字符分割开：
- select concat_ws("---------",“tree”,“17”) from dual;
1、查看concat_ws函数定义：
concat_ws(separator, [string | array(string)]+) - returns the concatenation of the strings separated by the separator.
2、测试
hive (ruozeg6)> select concat_ws("----","tree","17") from dual;
OK
tree----17
Time taken: 0.189 seconds, Fetched: 1 row(s)
7、Split函数
场景描述：我们在浏览手机新闻的时候，发送给客户端的是一条ip+port的信息。
- desc function extended split;
- select split(“192.168.1.1:8020,”"\.") from emp;
hive (ruozeg6)> select split("192.168.199.151:8020",".") from dual;
OK
["","","","","","","","","","","","","","","","","","","","",""]
Time taken: 0.062 seconds, Fetched: 1 row(s)
hive (ruozeg6)> select split("192.168.199.151:8020","\\.") from dual;
OK
["192","168","199","151:8020"]
Time taken: 0.046 seconds, Fetched: 1 row(s)
hive (ruozeg6)> select split("192.168.199.151:8020","\\.") from dual;
OK
["192","168","199","151:8020"]
Time taken: 0.07 seconds, Fetched: 1 row(s)
特殊字符要加\进行转义。
问题：转义字符是个什么东西？
3.4、Hive完成Wordcount统计分析
第一步：创建Hive表，准备数据加载进去
-
create table d6_wc（sentence string）； 创建hive表
-
load data local inpath ‘/home/hadoop/data/wc.txt’ into table d6_wc; 把wc.txt文件加载入表。
1、select split（sentence,"\t"）from d6_wc;
- split(str, regex) - Splits str around occurances that match regex
2、select explode(split(sentence,"\t")) from d6_wc;
- explode：分割数组中的元素将其转换成多行
hive (ruozeg6)> select split(sentence,"\t") from d6_wc;
OK
["ruoze","ruoze","ruoze"]
["jepson","jepson"]
["john"]
Time taken: 0.077 seconds, Fetched: 3 row(s)
hive (ruozeg6)> select explode(split(sentence,"\t")) from d6_wc;
OK
ruoze
ruoze
ruoze
jepson
jepson
john
Time taken: 0.069 seconds, Fetched: 6 row(s)
3、SQL统计这些词出现的个数
hive完成wc，split完成分割、explode完成行转列、SQL统计
select word,count(1) as c
from
(select explode(split(sentence,"\t")) as word from d6_wc) as t
group by word //以word进行groupby
order by c desc; //排序
-
Total job = 2，没有order by的话只有一个job
-
必然是两个mapreduce作业，groupBy和orderBy没办法并行执行，order by的执行依赖于group by.
-
生成几个job就对应几个MapReduce作业
结果显示如下：
第四章：本次课程面试题
面试题1：Hive中是否支持insert？
- 支持，从0.14版本开始起Hive是支持insert的，但是生产上是拒绝使用insert的，insert数据就肯定意味着的是那一分钟的数据丢了，数据丢了是前端采集的事情；我们必须要保证每一批数据处理的幂等性（数据不管跑多少次，都要保证每一次的数据都是正确的）。
面试题2：在hive中null、0、" "、’ '的区别，如何处理？
面试题3：hive的行转列、列转行
作业1：求每个部门、工作岗位的最高薪资
作业2：desc function extended abs; 中出现的案例是怎么来的呢？
作业3：Hive求每个月月底的一天？
2428
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
