create table company_info(
  personId string, 
  company string,
  money float
)
row format delimited fields terminated by "\t";

load data local inpath '/opt/module/datas/company_info.txt' into table company_info;

1.1.1、order by

hive 中的 order by 语句会对查询结果做一次全局排序，即，所有的 mapper 产生的结果都会交给一个 reducer 去处理，无论数据量大小，job 任务只会启动一个 reducer，如果数据量巨大，则会耗费大量的时间。 尖叫提示：如果在严格模式下，order by 需要指定 limit 数据条数，不然数据量巨大的情况下会造成崩溃无输出结果。涉及属性：set hive.mapred.mode=nonstrict/strict 例如：按照 money 排序的例子

select * from company_info order by money desc;

1.1.2、sort by

hive 中的 sort by 语句会对每一块局部数据进行局部排序，即每一个 reducer 处理的数据都是有序的，但是不能保证全局有序。

1.1.3、distribute by

hive 中的 distribute by 一般要和 sort by 一起使用，即将某一块数据归给(distribute by)某一个 reducer 处理，然后在指定的 reducer 中进行 sort by 排序。 尖叫提示：distribute by 必须写在 sort by 之前。 尖叫提示：涉及属性 mapreduce.job.reduces，hive.exec.reducers.bytes.per.reducer 例如：不同的人（personId）分为不同的组，每组按照 money 排序。

select * from company_info distribute by personId sort by personId asc, money desc;

1.1.4、cluster by

hive 中的 cluster by 在 distribute by 和 sort by 排序字段一致的情况下是等价的。同时，cluster by 指定的列只能是降序，即默认的 descend，而不能是 ascend。例如：写一个等价于 distribute by 与 sort by 的例子。

select * from company_info distribute by personId sort by personId desc;
等价于
select * from compnay_info cluster by personId;

1.2、行转列、列转行（UDAF 与 UDTF）

1.2.1、行转列

1、相关函数说明 1）CONCAT(string A/col, string B/col, …)：返回输入字符串连接后的结果，支持任意个输入字符串。 2）CONCAT_WS(separator, str1, str2,…)：它是一个特殊形式的CONCAT()。第一个参数是剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间。 3）COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。 2、数据准备 person_info.txt

3、需求把星座和血型一样的人归类到一起。结果如下：

射手座,A    大海|凤姐
白羊座,A    孙悟空|猪八戒
白羊座,B    宋宋

分析过程：

4、创建本地person_info.txt，导入数据

[atguigu@hadoop102 datas]$ vim person_info.txt
孙悟空    白羊座 A
大海    射手座 A
宋宋    白羊座 B
猪八戒    白羊座 A
凤姐    射手座 A

5、创建hive表并导入数据

create table person_info(
  name string, 
  constellation string, 
  blood_type string
) 
row format delimited fields terminated by "\t";

load data local inpath '/opt/module/datas/person_info.txt' into table person_info;

6、按需求查询数据

select concat_ws(",", constellation, blood_type) as c_b, name from person_info; t1

+--------+-------+--+
|  c_b   | name  |
+--------+-------+--+
| 白羊座,A  | 孙悟空   |
| 射手座,A  | 大海    |
| 白羊座,B  | 宋宋    |
| 白羊座,A  | 猪八戒   |
| 射手座,A  | 凤姐    |
+--------+-------+--+

--------------------

select 
  t1.c_b, collect_set(t1.name)
from 
  (select concat_ws(",", constellation, blood_type) as c_b, name from person_info) t1
group by
  t1.c_b;

+---------+----------------+--+
| t1.c_b  |      _c1       |
+---------+----------------+--+
| 射手座,A   | ["大海","凤姐"]    |
| 白羊座,A   | ["孙悟空","猪八戒"]  |
| 白羊座,B   | ["宋宋"]         |
+---------+----------------+--+

--------------------

select 
  t1.c_b, concat_ws("|", collect_set(t1.name)) name
from 
  (select concat_ws(",", constellation, blood_type) as c_b, name from person_info) t1
group by
  t1.c_b;

+---------+----------+--+
| t1.c_b  |   name   |
+---------+----------+--+
| 射手座,A   | 大海|凤姐    |
| 白羊座,A   | 孙悟空|猪八戒  |
| 白羊座,B   | 宋宋       |
+---------+----------+--+

1.2.2、列转行

1、函数说明 EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。 LATERAL VIEW 用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias 解释：lateral view 用于和split,explode等UDTF函数一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。 2、数据准备 movie_info.txt

movie           category

《疑犯追踪》    悬疑,动作,科幻,剧情
《Lie to me》   悬疑,警匪,动作,心理,剧情
《战狼2》   战争,动作,灾难

3、需求将电影分类中的数组数据展开。结果如下：

《疑犯追踪》    悬疑
《疑犯追踪》    动作
《疑犯追踪》    科幻
《疑犯追踪》    剧情
《Lie to me》   悬疑
《Lie to me》   警匪
《Lie to me》   动作
《Lie to me》   心理
《Lie to me》   剧情
《战狼2》   战争
《战狼2》   动作
《战狼2》   灾难

4、创建本地movie.txt，导入数据

[atguigu@hadoop102 datas]$ vim movie_info.txt
《疑犯追踪》    悬疑,动作,科幻,剧情
《Lie to me》   悬疑,警匪,动作,心理,剧情
《战狼2》   战争,动作,灾难

5、创建hive表并导入数据

create table movie_info(
  movie string, 
  category array<string>
) 
row format delimited fields terminated by "\t" -- 表中字段与字段之间的分割符是\t
collection items terminated by ","; -- 集合字段中的每个元素之间的分隔符是逗号

load data local inpath "/opt/module/datas/movie_info.txt" into table movie_info;

6、按需求查询数据

select 
  movie
  explode(category)
from
  movie_info;

上面是错误的。假设能执行的话，得到的是笛卡尔积。

小结：像split,explode等UDTF函数，是不能跟原表的字段直接进行查询的，UDTF函数一定要和lateral view联合在一块用。
-----------------------------------------

select
  movie,
  category_name
from 
  movie_info lateral view explode(category) table_tmp as category_name; -- lateral view 对原始表的集合字段进行了侧写，得到侧写表和侧写列(侧写字段)。

1.3、建表时的数组操作

fields terminated by：标识一张表中字段与字段之间的分隔符。 collection items terminated by：标识一个字段(数组字段)中各个子元素(item)的分隔符。注意：若有两个或两个以上的数组字段，那么他们的分隔符都得一样。

1.4、orc 存储

orc 即 Optimized Row Columnar (ORC) file，在 RCFile 的基础上演化而来，可以提供一种高效的方法在 Hive 中存储数据，提升了读、写、处理数据的效率。

1.5、Hive 分桶

为什么要用Hive 分桶？答：分区会产生新的文件和目录，在HDFS系统上NameNOde的压力会增大。 Hive 可以将表或者表的分区进一步组织成桶，以达到： 1、数据取样效率更高 2、数据处理效率更高桶通过对指定列进行哈希来实现，将一个列名下的数据切分为“一组桶”，每个桶都对应了一个该列名下的一个存储文件。

1.5.1、直接分桶

开始操作之前，需要将 hive.enforce.bucketing 属性设置为 true，以标识 Hive 可以识别桶。

create table music(
  id int,
  name string, 
  size float
)
row format delimited fields terminated by "\t"
clustered by (id) into 4 buckets;

该代码的意思是将 music 表按照 id 将数据分成了 4 个桶，插入数据时，会对应 4 个 reduce 操作，输出 4 个文件。 分桶算法：id.hashCode % 4(桶数) Map集合key去重的原理和set集合去重原理：先比较哈希值(本质是比较地址值,hashCode())，再比较所对应的具体值(equals())。 set集合存储数据的本质是使用Map集合来存储的。 Map集合存储数据的本质是使用数组来存储的。数组存储数据的本质是使用索引+值来存储的。

1.5.2、在分区中分桶

当数据量过大，需要庞大分区数量时，可以考虑桶，因为分区数量太大的情况可能会导致文件系统(HDFS)挂掉，而且桶比分区有更高的查询效率。数据最终落在哪一个桶里，取决于 clustered by 的那个列的值的 hash 数与桶的个数求余来决定。虽然有一定离散性，但不能保证每个桶中的数据量是一样的。

create table music2(
  id int,
  name string, 
  size float
)
partitioned by(date string)
clustered by(id) sorted by(size) into 4 bucket 
row format delimited fields terminated by "\t";

load data local inpath '/opt/module/datas/music2.txt' into table music2 partition(date='2017-08-30');

二、总结

2.1 启动/停止hadoop集群、zookeeper集群、历史服务器

[atguigu@hadoop102 hadoop-2.7.2]$ sbin/start-dfs.sh -- 启动dfs集群
[atguigu@hadoop103 hadoop-2.7.2]$ sbin/start-yarn.sh -- 启动yarn集群
[atguigu@hadoop102 ~]$ zkstart.sh -- 启动zookeeper集群
[atguigu@hadoop102 hadoop-2.7.2]$ sbin/mr-jobhistory-daemon.sh start historyserver -- 启动历史服务器

[atguigu@hadoop102 hadoop-2.7.2]$ sbin/stop-dfs.sh -- 停止dfs集群
[atguigu@hadoop103 hadoop-2.7.2]$ sbin/stop-yarn.sh -- 停止yarn集群
[atguigu@hadoop102 ~]$ zkstop.sh -- 停止zookeeper集群
[atguigu@hadoop102 hadoop-2.7.2]$ sbin/mr-jobhistory-daemon.sh stop historyserver -- 停止历史服务器

2.2 访问hive的两种方式

方式一：
[atguigu@hadoop102 hive]$ bin/hive -- 启动hive

方式二：
[atguigu@hadoop102 hive]$ bin/hiveserver2
[atguigu@hadoop102 hive]$ bin/beeline（在新的窗口中输入）
Beeline version 1.2.1 by Apache Hive
beeline> !connect jdbc:hive2://hadoop102:10000（回车）
Connecting to jdbc:hive2://hadoop102:10000
Enter username for jdbc:hive2://hadoop102:10000: atguigu（回车）
Enter password for jdbc:hive2://hadoop102:10000: （直接回车）
Connected to: Apache Hive (version 1.2.1)
Driver: Hive JDBC (version 1.2.1)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://hadoop102:10000> show databases;
+----------------+--+
| database_name  |
+----------------+--+
| default        |
| hive_db2       |
+----------------+--+

2.3 CentOS6x与Cenos7x命令的区别

CentOS6x
[atguigu@hadoop102 ~]$ sudo service mysqld restart

CentOS7x
[atguigu@hadoop102 ~]$ sudo systemctl restart mysqld.service

2.4 大数据开发中重用的两种数据格式

xxx.tsv 文件中的字段是以\t来分割的。 xxx.csv 文件中的字段是以逗号(,)来分割的。(comma:逗号)

2.5 UDF、UDAF、UDTF

collect_set(clo) 将多行数据聚合成一列数据，UDAF函数 concat_ws(separator, str1, str2,…) 聚合函数，UDAF函数 split((col),explode(col) 将一列数据拆成多行数据，UDTF函数

2.6 小知识总结

分桶算法：id.hashCode % 4(桶数) Map集合key去重的原理和set集合去重原理：先比较哈希值(本质是比较地址值,hashCode())，再比较所对应的具体值(equals())。　　set集合存储数据的本质是使用Map集合来存储的。　　Map集合存储数据的本质是使用数组来存储的。　　数组存储数据的本质是使用索引+值来存储的。

hadoop

大数据技术之_09_Hive学习_复习与总结

一、知识梳理

1.1、背景表结构