尚硅谷大数据技术之Hive作者：尚硅谷大数据研发部讲师：WHAlex思维导图

曾经的曾经

2023-05-02

浏览量: 33

尚硅谷大数据技术

Hive

技术开发

尚硅谷大数据技术之Hive作者：尚硅谷大数据研发部讲师：WHAlex

树图思维导图提供《尚硅谷大数据技术之Hive作者：尚硅谷大数据研发部讲师：WHAlex》在线思维导图免费制作，点击“编辑”按钮，可对《尚硅谷大数据技术之Hive作者：尚硅谷大数据研发部讲师：WHAlex》进行在线思维导图编辑，本思维导图属于思维导图模板主题，文件编号是：f3991891b201487c9203dfcdbfa623ea

思维导图大纲

一 Hive基本概念

1.1 什么是Hive

Hive：由Facebook开源用于解决海量结构化日志的数据统计。

Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类SQL查询功能。

本质是：将HQL转化成MapReduce程序

1）Hive处理的数据存储在HDFS

2）Hive分析数据底层的实现是MapReduce

3）执行程序运行在YARN上

1.2 Hive的优缺点

1.2.1 优点

1）操作接口采用类SQL语法，提供快速开发的能力（简单、容易上手）

2）避免了去写MapReduce，减少开发人员的学习成本。

3）Hive的执行延迟比较高，因此Hive常用于数据分析，对实时性要求不高的场合；

4）Hive优势在于处理大数据，对于处理小数据没有优势

5）Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。

1.2.2 缺点

1）Hive的HQL表达能力有限

（1）迭代式算法无法表达

（2）数据挖掘方面不擅长

2）Hive的效率比较低

（1）Hive自动生成的MapReduce作业，通常情况下不够智能化

（2）Hive调优比较困难，粒度较粗

1.3 Hive架构原理

Hive架构.png

如图中所示，Hive通过给用户提供的一系列交互接口，接收到用户的指令(SQL)，使用自己的Driver，结合元数据(MetaStore)，将这些指令翻译成MapReduce，提交到Hadoop中执行，最后，将执行返回的结果输出到用户交互接口。

1）用户接口：Client

CLI（hive shell）、JDBC/ODBC(java访问hive)、WEBUI（浏览器访问hive）

2）元数据：Metastore

元数据包括：表名、表所属的数据库（默认是default）、表的拥有者、列/分区字段、表的类型（是否是外部表）、表的数据所在目录等；

默认存储在自带的derby数据库中，推荐使用MySQL存储Metastore

3）Hadoop

使用HDFS进行存储，使用MapReduce进行计算。

4）驱动器：Driver

（1）解析器（SQL Parser）：将SQL字符串转换成抽象语法树AST，这一步一般都用第三方工具库完成，比如antlr；对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误。

（2）编译器（Physical Plan）：将AST编译生成逻辑执行计划。

（3）优化器（Query Optimizer）：对逻辑执行计划进行优化。

（4）执行器（Execution）：把逻辑执行计划转换成可以运行的物理计划。对于Hive来说，就是MR/Spark。

1.4 Hive和数据库比较

由于 Hive 采用了类似SQL 的查询语言 HQL(Hive Query Language)，因此很容易将 Hive 理解为数据库。其实从结构上来看，Hive 和数据库除了拥有类似的查询语言，再无类似之处。本文将从多个方面来阐述 Hive 和数据库的差异。数据库可以用在 Online 的应用中，但是Hive 是为数据仓库而设计的，清楚这一点，有助于从应用角度理解 Hive 的特性。

1.4.1 查询语言

由于SQL被广泛的应用在数据仓库中，因此，专门针对Hive的特性设计了类SQL的查询语言HQL。熟悉SQL开发的开发者可以很方便的使用Hive进行开发。

1.4.2 数据存储位置

Hive 是建立在 Hadoop 之上的，所有 Hive 的数据都是存储在 HDFS 中的。而数据库则可以将数据保存在块设备或者本地文件系统中。

1.4.3 数据更新

由于Hive是针对数据仓库应用设计的，而数据仓库的内容是读多写少的。因此，Hive中不支持对数据的改写和添加，所有的数据都是在加载的时候确定好的。而数据库中的数据通常是需要经常进行修改的，因此可以使用 INSERT INTO … VALUES 添加数据，使用 UPDATE … SET修改数据。

1.4.4 索引

Hive在加载数据的过程中不会对数据进行任何处理，甚至不会对数据进行扫描，因此也没有对数据中的某些Key建立索引。Hive要访问数据中满足条件的特定值时，需要暴力扫描整个数据，因此访问延迟较高。由于 MapReduce 的引入， Hive 可以并行访问数据，因此即使没有索引，对于大数据量的访问，Hive 仍然可以体现出优势。数据库中，通常会针对一个或者几个列建立索引，因此对于少量的特定条件的数据的访问，数据库可以有很高的效率，较低的延迟。由于数据的访问延迟较高，决定了 Hive 不适合在线数据查询。

1.4.5 执行

Hive中大多数查询的执行是通过 Hadoop 提供的 MapReduce 来实现的。而数据库通常有自己的执行引擎。

1.4.6 执行延迟

Hive 在查询数据的时候，由于没有索引，需要扫描整个表，因此延迟较高。另外一个导致 Hive 执行延迟高的因素是 MapReduce框架。由于MapReduce 本身具有较高的延迟，因此在利用MapReduce 执行Hive查询时，也会有较高的延迟。相对的，数据库的执行延迟较低。当然，这个低是有条件的，即数据规模较小，当数据规模大到超过数据库的处理能力的时候，Hive的并行计算显然能体现出优势。

1.4.7 可扩展性

由于Hive是建立在Hadoop之上的，因此Hive的可扩展性是和Hadoop的可扩展性是一致的（世界上最大的Hadoop 集群在 Yahoo!，2009年的规模在4000 台节点左右）。而数据库由于 ACID 语义的严格限制，扩展行非常有限。目前最先进的并行数据库 Oracle 在理论上的扩展能力也只有100台左右。

1.4.8 数据规模

由于Hive建立在集群上并可以利用MapReduce进行并行计算，因此可以支持很大规模的数据；对应的，数据库可以支持的数据规模较小。

二 Hive安装环境准备

2.1 Hive安装地址说明

1）Hive官网地址：

http://hive.apache.org/

2）文档查看地址：

https://cwiki.apache.org/confluence/display/Hive/GettingStarted

3）下载地址：

http://archive.apache.org/dist/hive/

4）github地址：

https://github.com/apache/hive

2.2 Hive安装部署

1）Hive安装及配置

（1）把apache-hive-1.2.1-bin.tar.gz上传到linux的/opt/software目录下

（2）解压apache-hive-1.2.1-bin.tar.gz到/opt/module/目录下面

（3）修改apache-hive-1.2.1-bin.tar.gz的名称为hive

（4）修改/opt/module/hive/conf目录下的hive-env.sh.template名称为hive-env.sh

（5）配置hive-env.sh文件

（a）配置HADOOP_HOME路径

（b）配置HIVE_CONF_DIR路径

2）Hadoop集群配置

（1）必须启动hdfs和yarn

（2）在HDFS上创建/tmp和/user/hive/warehouse两个目录并修改他们的同组权限可写

3）Hive基本操作

（1）启动hive

（2）查看数据库

（3）打开默认数据库

（4）显示default数据库中的表

（5）创建一张表

（6）显示数据库中有几张表

（7）查看表的结构

（8）向表中插入数据

（9）查询表中数据

（10）退出hive

2.3 将本地文件导入Hive案例

需求：将本地/opt/module/datas/student.txt这个目录下的数据导入到hive的student(id int, name string)表中。

1）数据准备：在/opt/module/datas/student.txt这个目录下准备数据

（1）在/opt/module/目录下创建datas

（2）在/opt/module/datas/目录下创建student.txt文件并添加数据

2）hive实际操作

（1）启动hive

（2）显示数据库

（3）使用default数据库

（4）显示default数据库中的表

（5）删除已创建的student表

（6）创建student表, 并声明文件分隔符’\t’

（7）加载/opt/module/datas/student.txt 文件到student数据库表中。

（8）Hive查询结果

3）遇到的问题

再打开一个客户端窗口启动hive，会产生java.sql.SQLException异常。

原因是，Metastore默认存储在自带的derby数据库中，推荐使用MySQL存储Metastore;

2.4 MySql安装

2.4.1 安装包准备

1）查看mysql是否安装，如果安装了，卸载mysql

（1）查看

rpm -qa|grep mysql

（2）卸载

rpm -e --nodeps mysql-libs-5.1.73-7.el6.x86_64

2）解压mysql-libs.zip文件到当前目录

3）进入到mysql-libs文件夹下，并设置当前用户执行权限

2.4.2 安装MySql服务器

1）安装mysql服务端

[root@hadoop102 mysql-libs]# rpm -ivh MySQL-server-5.6.24-1.el6.x86_64.rpm

2）查看产生的随机密码

[root@hadoop102 mysql-libs]# cat /root/.mysql_secret

OEXaQuS8IWkG19Xs

3）查看mysql状态

[root@hadoop102 mysql-libs]# service mysql status

4）启动mysql

[root@hadoop102 mysql-libs]# service mysql start

2.4.3 安装MySql客户端

1）安装mysql客户端

[root@hadoop102 mysql-libs]# rpm -ivh MySQL-client-5.6.24-1.el6.x86_64.rpm

2）链接mysql

[root@hadoop102 mysql-libs]# mysql -uroot -pOEXaQuS8IWkG19Xs

3）修改密码

mysql>SET PASSWORD=PASSWORD('000000');

4）退出mysql

mysql>exit

2.4.4 MySql中user表中主机配置

配置只要是root用户+密码，在任何主机上都能登录MySQL数据库。

1）进入mysql

2）显示数据库

3）使用mysql数据库

4）展示mysql数据库中的所有表

5）展示user表的结构

6）查询user表

7）修改user表，把Host表内容修改为%

8）删除root用户的其他host

9）刷新

mysql>flush privileges;

10）退出

2.5 Hive元数据配置到MySql

2.5.1 驱动拷贝

1）在/opt/software/mysql-libs目录下解压mysql-connector-java-5.1.27.tar.gz驱动包

2）拷贝/opt/software/mysql-libs/mysql-connector-java-5.1.27目录下的mysql-connector-java-5.1.27-bin.jar到/opt/module/hive/lib/

2.5.2 配置Metastore到MySql

1）在/opt/module/hive/conf目录下创建一个hive-site.xml

2）根据官方文档配置参数，拷贝数据到hive-site.xml文件中。

https://cwiki.apache.org/confluence/display/Hive/AdminManual+MetastoreAdmin

具体参数配置

3）配置完毕后，如果启动hive异常，可以重新启动虚拟机。（重启后，别忘了启动hadoop集群）

2.5.3 多窗口启动Hive测试

1）先启动MySQL

2）再次打开多个窗口，分别启动hive

3）启动hive后，回到MySQL窗口查看数据库，显示增加了metastore数据库

2.6 Hive常用交互命令

[atguigu@hadoop102 hive]$ bin/hive -help

1）“-e”不进入hive的交互窗口执行sql语句

2）“-f”执行脚本中sql语句

（1）在/opt/module/datas目录下创建hivef.sql文件

（2）执行文件中的sql语句

（3）执行文件中的sql语句并将结果写入文件中

2.7 Hive其他命令操作

1）退出hive窗口：

hive(default)>exit;

hive(default)>quit;

2）在hive cli命令窗口中如何查看hdfs文件系统

hive(default)>dfs -ls /;

3）在hive cli命令窗口中如何查看hdfs本地系统

hive(default)>! ls /opt/module/datas;

4）查看在hive中输入的所有历史命令

2.8 Hive常见属性配置

2.8.1 Hive数据仓库位置配置

（1）Default数据仓库的最原始位置是在hdfs上的：/user/hive/warehouse路径下

（2）在仓库目录下，没有对默认的数据库default创建文件夹。如果某张表属于default数据库，直接在数据仓库目录下创建一个文件夹。

（3）修改default数据仓库原始位置（将hive-default.xml.template如下配置信息拷贝到hive-site.xml文件中）

配置同组用户有执行权限

bin/hdfs dfs -chmod g+w /user/hive/warehouse

2.8.2 显示当前数据库，以及查询表的头信息配置

1）在hive-site.xml文件中添加如下配置信息，就可以实现显示当前数据库，以及查询表的头信息配置。

2）重新启动hive，对比配置前后差异

2.8.3 Hive运行日志信息配置

1）Hive的log默认存放在/tmp/atguigu/hive.log目录下（当前用户名下）。

2）修改hive的log存放日志到/opt/module/hive/logs

（1）修改/opt/module/hive/conf/hive-log4j.properties.template文件名称为hive-log4j.properties

（2）在hive-log4j.properties文件中修改log存放位置

2.8.4 参数配置方式

1）查看当前所有的配置信息

hive>set;

2）参数的配置三种方式及优先级

（1）配置文件方式

（2）命令行参数方式

[atguigu@hadoop103 hive]$ bin/hive -hiveconf mapred.reduce.tasks=10;

（3）参数声明方式

hive (default)> set mapred.reduce.tasks=100;

上述三种设定方式的优先级依次递增。即配置文件<命令行参数<参数声明。注意某些系统级的参数，例如log4j相关的设定，必须用前两种方式设定，因为那些参数的读取在会话建立以前已经完成了。

三 Hive数据类型

尚硅谷大数据技术之Hive数据类型.doc

四 DDL数据定义

4.1 创建数据库

1）创建一个数据库，数据库在HDFS上的默认存储路径是/user/hive/warehouse/*.db。

2）避免要创建的数据库已经存在错误，增加if not exists判断。（标准写法）

hive (default)> create database if not exists db_hive;

3）创建一个数据库，指定数据库在HDFS上存放的位置

4.2 修改数据库

用户可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。

hive (default)> alter database db_hive set dbproperties('createtime'='20170830');

在mysql中查看修改结果

hive> desc database extended db_hive;

4.3 查询数据库

4.3.1 显示数据库

1）显示数据库

hive> show databases;

2）过滤显示查询的数据库

hive> show databases like 'db_hive*';

4.3.2 查看数据库详情

1）显示数据库信息

2）显示数据库详细信息，extended

4.3.3 使用数据库

hive (default)> use db_hive;

4.4 删除数据库

1）删除空数据库

hive>drop database db_hive2;

2）如果删除的数据库不存在，最好采用 if exists判断数据库是否存在

hive> drop database if exists db_hive2;

3）如果数据库不为空，可以采用cascade命令，强制删除

hive> drop database db_hive cascade;

4.5 创建表

尚硅谷大数据技术之hive创建表.doc

4.5.1 管理表

1）理论

默认创建的表都是所谓的管理表，有时也被称为内部表。因为这种表，Hive会（或多或少地）控制着数据的生命周期。Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如，/user/hive/warehouse)所定义的目录的子目录下。当我们删除一个管理表时，Hive也会删除这个表中数据。管理表不适合和其他工具共享数据。

2）案例实操

（1）普通创建表

（2）根据查询结果创建表（查询的结果会添加到新创建的表中）

（3）根据已经存在的表结构创建表

（4）查询表的类型

4.5.2 外部表

1）理论

因为表是外部表，所以Hive并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。

2）管理表和外部表的使用场景：

每天将收集到的网站日志定期流入HDFS文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过SELECT+INSERT进入内部表。

3）案例实操

分别创建部门和员工外部表，并向表中导入数据。

（1）原始数据

dept.txt

emp.txt

（2）建表语句

创建部门表

创建员工表

（3）查看创建的表

（4）向外部表中导入数据

导入数据

查询结果

（5）查看表格式化数据

4.6 分区表

分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。

4.6.1 分区表基本操作

1）引入分区表（需要根据日期对日志进行管理）

2）创建分区表语法

3）加载数据到分区表中

4）查询分区表中数据

单分区查询

多分区联合查询

5）增加分区

创建单个分区

同时创建多个分区

6）删除分区

删除单个分区

同时删除多个分区

7）查看分区表有多少分区

8）查看分区表结构

4.6.2 分区表注意事项

1）创建二级分区表

2）正常的加载数据

（1）加载数据到二级分区表中

（2）查询分区数据

3）把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式

（1）方式一：上传数据后修复

上传数据

查询数据（查询不到刚上传的数据）

执行修复命令

再次查询数据

（2）方式二：上传数据后添加分区

上传数据

执行添加分区

查询数据

（3）方式三：上传数据后load数据到分区

创建目录

上传数据

查询数据

4.7 修改表

4.7.1 重命名表

（1）语法

ALTER TABLE table_name RENAME TO new_table_name

（2）实操案例

4.7.2 增加、修改和删除表分区

详见4.6.1分区表基本操作。

4.7.3 增加/修改/替换列信息

1）语法

更新列

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

增加和替换列

ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)

注：ADD是代表新增一字段，字段位置在所有列后面(partition列前)，REPLACE则是表示替换表中所有字段。

2）实操案例

（1）查询表结构

（2）添加列

（3）查询表结构

（4）更新列

（5）查询表结构

（6）替换列

（7）查询表结构

4.8 删除表

hive (default)> drop table dept_partition;

五 DML数据操作

5.1 数据导入

5.1.1 向表中装载数据（Load）

1）语法

2）实操案例

（0）创建一张表

（1）加载本地文件到hive

（2）加载HDFS文件到hive中

（3）加载数据覆盖表中已有的数据

5.1.2 通过查询语句向表中插入数据（Insert）

1）创建一张分区表

2）基本插入数据

3）基本模式插入（根据单张表查询结果）

4）多插入模式（根据多张表查询结果）

5.1.3 查询语句中创建表并加载数据（As Select）

详见4.5.1章创建表。

5.1.4 创建表时通过Location指定加载数据路径

1）创建表，并指定在hdfs上的位置

2）上传数据到hdfs上

3）查询数据

5.1.5 Import数据到指定Hive表中

注意：先用export导出后，再将数据导入。

5.2 数据导出

5.2.1 Insert导出

1）将查询的结果导出到本地

2）将查询的结果格式化导出到本地

3）将查询的结果导出到HDFS上(没有local)

5.2.2 Hadoop命令导出到本地

5.2.3 Hive Shell 命令导出

5.2.4 Export导出到HDFS上

5.2.5 Sqoop导出

后续课程专门讲。

5.3 清除表中数据（Truncate）

hive (default)> truncate table student;

注意：Truncate只能删除管理表，不能删除外部表中数据

六查询

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select

6.1 基本查询（Select…From）

6.1.1 全表和特定字段查询

1）全表查询

2）选择特定列查询

注意：

（1）SQL 语言大小写不敏感。

（2）SQL 可以写在一行或者多行

（3）关键字不能被缩写也不能分行

（4）各子句一般要分行写。

（5）使用缩进提高语句的可读性。

6.1.2 列别名

1）重命名一个列。

2）便于计算。

3）紧跟列名，也可以在列名和别名之间加入关键字‘AS’

4）案例实操

（1）查询名称和部门

6.1.3 算术运算符

算术运算符.png

案例实操

查询出所有员工的薪水后加1显示。

6.1.4 常用函数

1）求总行数（count）

2）求工资的最大值（max）

3）求工资的最小值（min）

4）求工资的总和（sum）

5）求工资的平均值（avg）

6.1.5 Limit语句

典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。

hive (default)> select * from emp limit 5;

6.2 Where语句

介绍

1）使用WHERE 子句，将不满足条件的行过滤掉。

2）WHERE 子句紧随 FROM 子句。

3）案例实操

查询出薪水大于1000的所有员工

6.2.1 比较运算符（Between/In/ Is Null）

1）下面表中描述了谓词操作符，这些操作符同样可以用于JOIN…ON和HAVING语句中。

比较运算符（BETWEEN_IN_ISNULL）.png

2）案例实操

（1）查询出薪水等于5000的所有员工

（2）查询工资在500到1000的员工信息

（3）查询comm为空的所有员工信息

（4）查询工资是1500或5000的员工信息

6.2.2 Like和RLike

1）使用LIKE运算选择类似的值

2）选择条件可以包含字符或数字:

% 代表零个或多个字符(任意个字符)。

_ 代表一个字符。

3）RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。

4）案例实操

（1）查找以2开头薪水的员工信息

（2）查找第二个数值为2的薪水的员工信息

（3）查找薪水中含有2的员工信息

6.2.3 逻辑运算符（And/Or/Not）

逻辑运算符（AND_OR_NOT）.png

案例实操

（1）查询薪水大于1000，部门是30

（2）查询薪水大于1000，或者部门是30

（3）查询除了20部门和30部门以外的员工信息

6.3 分组

6.3.1 Group By语句

GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。

案例实操：

（1）计算emp表每个部门的平均工资

（2）计算emp每个部门中每个岗位的最高薪水

6.3.2 Having语句

1）having与where不同点

（1）where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据。

（2）where后面不能写分组函数，而having后面可以使用分组函数。

（3）having只用于group by分组统计语句。

2）案例实操：

（1）求每个部门的平均薪水大于2000的部门

6.4 Join语句

6.4.1 等值Join

Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接。

案例操作

（1）根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门编号；

6.4.2 表的别名

1）好处

（1）使用别名可以简化查询。

（2）使用表名前缀可以提高执行效率。

2）案例实操

合并员工表和部门表

6.4.3 内连接

6.4.4 左外连接

6.4.5 右外连接

6.4.6 满外链接

6.4.7 多表连接

注意：连接 n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件。

0）数据准备

location.txt

1）创建位置表

2）导入数据

3）多表连接查询

6.4.8 笛卡尔积 JOIN

1）笛卡尔集会在下面条件下产生:

（1）省略连接条件

（2）连接条件无效

（3）所有表中的所有行互相连接

2）案例实操

6.5 排序

6.5.1 全局排序（Order By）

Order By：全局排序，一个MapReduce

1）使用 ORDER BY 子句排序

ASC（ascend）: 升序（默认）

DESC（descend）: 降序

2）ORDER BY 子句在SELECT语句的结尾。

3）案例实操

（1）查询员工信息按工资升序排列

（2）查询员工信息按工资降序排列

6.5.2 按照别名排序

按照员工薪水的2倍排序

6.5.3 多个列排序

按照部门和工资升序排序

6.5.4 每个MapReduce内部排序（Sort By）

Sort By：每个MapReduce内部进行排序，对全局结果集来说不是排序

1）设置reduce个数

2）查看设置reduce个数

3）根据部门降序查看员工信息

4）将查询结果导入到文件中（按照部门编号降序排序）

6.5.5 分区排序（Distribute By）

Distribute By：类似MR中partition，进行分区，结合sort by使用。

注意，Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。

案例实操：

（1）先按照部门编号分区，再按照员工编号降序排序。

6.5.6 Cluster By

当distribute by和sorts by字段相同时，可以使用cluster by方式。

cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。

1）以下两种写法等价

select * from emp cluster by deptno;

select * from emp distribute by deptno sort by deptno;

注意：按照部门编号分区，不一定就是固定死的数值，可以是20号和30号部门分到一个分区里面去。

6.6 分桶及抽样查询

6.6.1 分桶表数据存储

分区针对的是数据的存储路径；分桶针对的是数据文件。

分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区，特别是之前所提到过的要确定合适的划分大小这个疑虑。

分桶是将数据集分解成更容易管理的若干部分的另一个技术。

1）先创建分桶表，通过直接导入数据文件的方式

（0）数据准备

student.txt

（1）创建分桶表

（2）查看表结构

（3）导入数据到分桶表中

（4）查看创建的分桶表中是否分成4个桶

分桶表.png

发现并没有分成4个桶。是什么原因呢？

2）创建分桶表时，数据通过子查询的方式导入

（1）先建一个普通的stu表

（2）向普通的stu表中导入数据

（3）清空stu_buck表中数据

（4）导入数据到分桶表，通过子查询的方式

（5）发现还是只有一个分桶

1个分桶.png

（6）需要设置一个属性

（7）查询分桶的数据

6.6.2 分桶抽样查询

6.6.3 数据块抽样

Hive提供了另外一种按照百分比进行抽样的方式，这种事基于行数的，按照输入路径下的数据块百分比进行的抽样。

hive (default)> select * from stu tablesample(0.1 percent);

提示：这种抽样方式不一定适用于所有的文件格式。另外，这种抽样的最小抽样单元是一个HDFS数据块。因此，如果表的数据大小小于普通的块大小128M的话，那么将会返回所有行。

6.7 行转列查询

6.7.1 数据及需求

1）数据准备

2）需求:把星座和血型一样的人归类到一起。

6.7.2 相关函数说明

1）CONCAT(string A/col, string B/col…):返回输入字符串连接后的结果，支持任意个输入字符串;

2）CONCAT_WS(separator, str1, str2,...):它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;

3）COLLECT_SET(col):函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

6.7.3 导入数据及行转列查询

1）创建hive表并导入数据

2）按需求查询数据

6.8 列转行查询

6.8.1 数据及需求

1）数据准备

2）需求：将电影分类中的数组数据展开

6.8.2 相关函数说明

1）LATERAL VIEW: 用法:LATERAL VIEW udtf(expression) tableAlias AS columnAlias 解释:用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

2）EXPLODE(col):将hive一列中复杂的array或者map结构拆分成多行。

6.8.3 导入数据及行转列查询