Hive学习

Hive入门

Hive基础知识

Hive：由Facebook开源用于解决海量结构化日志的数据统计。
Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类SQL查询功能。
本质是：将HQL转化成MapReduce程序
1）Hive处理的数据存储在HDFS
2）Hive分析数据底层的实现是MapReduce
3）执行程序运行在Yarn上

Hive的优缺点

优点：

• 1)操作接口采用类SQL语法，提供快速开发的能力（简单、容易上手）。
• 2)避免了去写MapReduce，减少开发人员的学习成本。
• 3)Hive的执行延迟比较高，因此Hive常用于数据分析，对实时性要求不高的场合。
• 4)Hive优势在于处理大数据，对于处理小数据没有优势，因为Hive的执行延迟比较高。
• 5)Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。

缺点：

• 1．Hive的HQL表达能力有限
  • （1）迭代式算法无法表达
  • （2）数据挖掘方面不擅长
• 2．Hive的效率比较低
  • （1）Hive自动生成的MapReduce作业，通常情况下不够智能化
  • （2）Hive调优比较困难，粒度较粗

Hive架构原理

• 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。

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

Hive和数据库比较

• 查询语言

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

• 数据存储位置

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

• 数据更新

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

• 索引

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

• 执行

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

• 执行延迟

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

• 可扩展性

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

• 数据规模

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

Hive安装

Hive本身存储元数据的数据库是自带的derby数据库，需要改为Sql

常用基础API：

• 显示数据库：show databases;
• 使用default数据库： use default;
• 删除已创建的student表 ：drop table student
• 创建student表, 并声明文件分隔符’\t’：
  • create table（id int,name string） ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’;
• 加载/opt/module/datas/student.txt 文件到student数据库表中：
  • load data local inpath ‘/usr/hiveData/data.txt’ into table student;

HiveJDBC访问

• 启动hiveserver2服务
• 启动beeline
• 连接hiveserver2

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       |
+----------------+--+

Hive常用交互命令

• 1．“-e”不进入hive的交互窗口执行sql语句
• 2．“-f”执行脚本中sql语句

Hive其他命令操作

• 在hive cli命令窗口中如何查看hdfs文件系统：dfs -ls
• 在hive cli命令窗口中如何查看本地文件系统：! ls /opt/module/datas;
• 查看在hive中输入的所有历史命令
  • （1）进入到当前用户的根目录/root或/home/atguigu
  • （2）查看. hivehistory文件

Hive常见属性配置

• 1）Default数据仓库的最原始位置是在hdfs上的：/user/hive/warehouse路径下。
• 2）在仓库目录下，没有对默认的数据库default创建文件夹。如果某张表属于default数据库，直接在数据仓库目录下创建一个文件夹。
• 3）修改default数据仓库原始位置（将hive-default.xml.template如下配置信息拷贝到hive-site.xml文件中）。

<property>
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>
<description>location of default database for the warehouse</description>
</property>

查询后信息显示配置

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

<property>
		<name>hive.cli.print.header</name>
		<value>true</value>
	</property>
	<property>
		<name>hive.cli.print.current.db</name>
		<value>true</value>
	</property>

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存放位置：hive.log.dir=/opt/module/hive/logs

Hive数据类型

基本数据类型

Tips： 对于Hive的String类型相当于数据库的varchar类型，该类型是一个可变的字符串，不过它不能声明其中最多能存储多少个字符，理论上它可以存储2GB的字符数。

数据集合类型

Tips： Hive有三种复杂数据类型ARRAY、MAP 和 STRUCT。ARRAY和MAP与Java中的Array和Map类似，而STRUCT与C语言中的Struct类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套。

类型转化

Hive的原子数据类型是可以进行隐式转换的，类似于Java的类型转换。
例如某表达式使用INT类型，TINYINT会自动转换为INT类型，但是Hive不会进行反向转化。
例如，某表达式使用TINYINT类型，INT不会自动转换为TINYINT类型，它会返回错误，除非使用CAST操作。

1．隐式类型转换规则如下：
（1）任何整数类型都可以隐式地转换为一个范围更广的类型，如TINYINT可以转换成INT，INT可以转换成BIGINT。
（2）所有整数类型、FLOAT和STRING类型都可以隐式地转换成DOUBLE。
（3）TINYINT、SMALLINT、INT都可以转换为FLOAT。
（4）BOOLEAN类型不可以转换为任何其它的类型。

2. 可以使用CAST操作显示进行数据类型转换：
例如CAST(‘1’ AS INT)将把字符串’1’ 转换成整数1；如果强制类型转换失败，如执行CAST(‘X’ AS INT)，表达式返回空值 NULL。

DDL数据定义

创建数据库

• create database if not exist test1 location ‘/db_hive2.db’;

查询数据库

查询数据库

• show database [like “test*”]

查看数据库详细信息

• desc database db_hive;

切换当前数据库

• use db_hive;

修改数据库

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

删除数据库

• 删除空数据库：drop database if exists db_hive2;
• 强制删除数据库：drop database db_hive cascade;

创建表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
[(col_name data_type [COMMENT col_comment], ...)] 
[COMMENT table_comment] 
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 
[CLUSTERED BY (col_name, col_name, ...) 
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 
[ROW FORMAT row_format] 
[STORED AS file_format] 
[LOCATION hdfs_path]

字段解释说明
（1）CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。
（2）EXTERNAL关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
（3）COMMENT：为表和列添加注释。
（4）PARTITIONED BY创建分区表
（5）CLUSTERED BY创建分桶表
（6）SORTED BY不常用
（7）ROW FORMAT DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char][MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, …)]
用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED，将会使用自带的SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive通过SerDe确定表的具体的列的数据。
SerDe是Serialize/Deserilize的简称，目的是用于序列化和反序列化。
（8）STORED AS指定存储文件类型
常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）
如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。
（9）LOCATION ：指定表在HDFS上的存储位置。
（10）LIKE允许用户复制现有的表结构，但是不复制数据。

管理表（内部表）

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

外部表

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

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

管理表与外部表的互相转换

alter table student2 set tblproperties(‘EXTERNAL’=‘TRUE’);

alter table student2 set tblproperties(‘EXTERNAL’=‘FALSE’);

分区表

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

分区表基本操作：

• 创建分区表语法：

hive (default)> create table dept_partition(
deptno int, dname string, loc string
)
partitioned by (month string)
row format delimited fields terminated by '\t';

• 加载数据到分区表

hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table 
default.dept_partition partition(month='201709');
hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table 
default.dept_partition partition(month='201708');
hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table 
default.dept_partition partition(month='201707’);

• 查询分区表中数据

select * from dept_partition where month='201709'
//单分区查询不用union关键字
    union
    select * from dept_partition where month='201708'
    union
    select * from dept_partition where month='201707';

• 增加分区

hive (default)> alter table dept_partition add partition(month='201706') ;

• 删除分区

alter table dept_partition drop 
partition (month='201705'), 
partition (month='201706');

• 查看分区表有多少分区

show partitions dept_partition;

分区表注意事项

• 创建二级分区表

hive (default)> create table dept_partition2(
               deptno int, dname string, loc string
               )
               partitioned by (month string, day string)
               row format delimited fields terminated by '\t';

DML数据操作

数据导入

向表中装载数据（Load）

• 语法
  hive> load data [local] inpath ‘/opt/module/datas/student.txt’ overwrite | into table student [partition (partcol1=val1,…)];
  （1）load data:表示加载数据
  （2）local:表示从本地加载数据到hive表；否则从HDFS加载数据到hive表
  （3）inpath:表示加载数据的路径
  （4）overwrite:表示覆盖表中已有数据，否则表示追加
  （5）into table:表示加载到哪张表
  （6）student:表示具体的表
  （7）partition:表示上传到指定分区

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

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

create table if not exists student3 as select id, name from student;

Import数据到指定Hive表中

hive (default)> import table student2 partition(month='201709') from
 '/user/hive/warehouse/export/student';

数据导出

insert导出

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

 insert overwrite local directory '/opt/module/datas/export/student' 
 select * from student;

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

insert overwrite local directory '/opt/module/datas/export/student1'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' select * from student;

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

insert overwrite directory '/user/atguigu/student2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
select * from student;

Hadoop命令导出到本地

dfs -get /user/hive/warehouse/student/month=201709/000000_0
/opt/module/datas/export/student3.txt;

Hive Shell 命令导出

 bin/hive -e 'select * from default.student;' 
 > /opt/module/datas/export/student4.txt;

Export导出到HDFS上

export table default.student to
'/user/hive/warehouse/export/student';

清除表中数据

注意：Truncate只能删除管理表，不能删除外部表中数据
hive (default)> truncate table student;

查询

基本查询

注意：
（1）SQL 语言大小写不敏感。
（2）SQL 可以写在一行或者多行
（3）关键字不能被缩写也不能分行
（4）各子句一般要分行写。
（5）使用缩进提高语句的可读性。

列别名

1．重命名一个列
2．便于计算
3．紧跟列名，也可以在列名和别名之间加入关键字‘AS’

算术运算符

常用函数

Limit语句

典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。
hive (default)> select * from emp limit 5;

Where语句

1．使用WHERE子句，将不满足条件的行过滤掉
2．WHERE子句紧随FROM子句

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

Like和RLike

1）使用LIKE运算选择类似的值
2）选择条件可以包含字符或数字:

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

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

逻辑运算符

分组

Group By语句

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

• （1）计算emp表每个部门的平均工资
  hive (default)> select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;
• （2）计算emp每个部门中每个岗位的最高薪水
  hive (default)> select t.deptno, t.job, max(t.sal) max_sal from emp t group by t.deptno, t.job;

Having语句

1．having与where不同点
（1）where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据。
（2）where后面不能写分组函数，而having后面可以使用分组函数。
（3）having只用于group by分组统计语句。
2．案例实操

求每个部门的平均工资
hive (default)> select deptno, avg(sal) from emp group by deptno;
 求每个部门的平均薪水大于2000的部门
hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno having
 avg_sal > 2000;

Join语句

等值Join

Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接。
案例实操
（1）根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称；
hive (default)> select e.empno, e.ename, d.deptno, d.dname from emp e join dept d
on e.deptno = d.deptno;

表的别名

1．好处
（1）使用别名可以简化查询。
（2）使用表名前缀可以提高执行效率。
2．案例实操

合并员工表和部门表
hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno
 = d.deptno;

内连接

内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。

hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno
 = d.deptno;

左外连接

左外连接：JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。

hive (default)> select e.empno, e.ename, d.deptno 
from emp e left join dept d on e.deptno = d.deptno;

右外连接

右外连接：JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。

hive (default)> select e.empno, e.ename, d.deptno 
from emp e right join dept d on e.deptno = d.deptno;

满外连接

满外连接：将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。

hive (default)> select e.empno, e.ename, d.deptno 
from emp e full join dept d on e.deptno = d.deptno;

多表连接

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

hive (default)>SELECT e.ename, d.deptno, l. loc_name
FROM   emp e 
JOIN   dept d
ON     d.deptno = e.deptno 
JOIN   location l
ON     d.loc = l.loc;

大多数情况下，Hive会对每对JOIN连接对象启动一个MapReduce任务。本例中会首先启动一个MapReduce job对表e和表d进行连接操作，然后会再启动一个MapReduce job将第一个MapReduce job的输出和表l;进行连接操作。
注意：为什么不是表d和表l先进行连接操作呢？这是因为Hive总是按照从左到右的顺序执行的。

排序

全局排序（Order By）

Order By：全局排序，一个Reducer

• 1．使用 ORDER BY 子句排序
  • ASC（ascend）: 升序（默认）
  • DESC（descend）: 降序
• 2．ORDER BY 子句在SELECT语句的结尾
• 3．案例实操
• （1）查询员工信息按工资升序排列

hive (default)> select * from emp order by sal;

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

hive (default)> select * from emp order by sal desc;

按照别名排序

• 按照员工薪水的2倍排序

hive (default)> select ename, sal*2 twosal from emp order by twosal;

多个列排序

按照部门和工资升序排序

hive (default)> select ename, deptno, sal from emp order by deptno, sal ;

每个MapReduce内部排序（Sort By）

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

• 1．设置reduce个数

hive (default)> set mapreduce.job.reduces=3;

• 2．查看设置reduce个数

hive (default)> set mapreduce.job.reduces;

• 3．根据部门编号降序查看员工信息

hive (default)> select * from emp sort by empno desc;

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

hive (default)> insert overwrite local directory '/opt/module/datas/sortby-result'
 select * from emp sort by deptno desc;

分区排序（Distribute By）

Distribute By：类似MR中partition，进行分区，结合sort by使用。
注意，Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。

对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。

案例实操：

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

hive (default)> set mapreduce.job.reduces=3;
hive (default)> insert overwrite local directory '/opt/module/datas/distribute-result' select * from emp distribute by deptno sort by empno desc;

Cluster By

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

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

• 1）以下两种写法等价

hive (default)> select * from emp cluster by deptno;
hive (default)> select * from emp distribute by deptno sort by deptno;

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

分桶及抽样查询

• 分桶表是对数据文件本身进行切分，而且分区表是对存储路径的一种切分

分桶表数据存储

• 需要设置属性：set hive.enforce.bucketing=true;
• 创建分桶表：

create table stu_buck(id int, name string)
clustered by(id) 
into 4 buckets
row format delimited fields terminated by '\t';

• 从其他表中导入数据或者通过load方法导入数据

分桶抽样查询

对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive可以通过对表进行抽样来满足这个需求。

• 抽样语句

select * from stu_buck tablesample(bucket 1 out of 4 on id);

注：tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y) 。

y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。例如，table总共分了4份，当y=2时，抽取(4/2=)2个bucket的数据，当y=8时，抽取(4/8=)1/2个bucket的数据。
x表示从哪个bucket开始抽取，如果需要取多个分区，以后的分区号为当前分区号加上y。例如，table总bucket数为4，tablesample(bucket 1 out of 2)，表示总共抽取（4/2=）2个bucket的数据，抽取第1(x)个和第3(x+y)个bucket的数据。

注意：x的值必须小于等于y的值，否则
FAILED: SemanticException [Error 10061]: Numerator should not be bigger than denominator in sample clause for table stu_buck

其他常用查询函数

空字段赋值

函数说明：
NVL：给值为NULL的数据赋值，它的格式是NVL( string1, replace_with)。它的功能是如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值，如果两个参数都为NULL ，则返回NULL。

CASE WHEN

• 语法：case sex when ‘男’ then 1 else 0 end

行转列

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

列转行

• 函数说明

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

• LATERAL VIEW

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

• 案例：
  
  

窗口函数

1．相关函数说明

• OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化
• CURRENT ROW：当前行
• n PRECEDING：往前n行数据
• n FOLLOWING：往后n行数据
• UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点
• LAG(col,n)：往前第n行数据
• LEAD(col,n)：往后第n行数据
• NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。

Rank

rank函数是用来排名的函数，将一个范围内的排名存在新列中
函数说明

• RANK() 排序相同时会重复，总数不会变
• DENSE_RANK() 排序相同时会重复，总数会减少
• ROW_NUMBER() 会根据顺序计算

函数

系统内置函数

自定义函数

• 1）Hive 自带了一些函数，比如：max/min等，但是数量有限，自己可以通过自定义UDF来方便的扩展。
• 2）当Hive提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数
  • （UDF：user-defined function）。
• 3）根据用户自定义函数类别分为以下三种：
  • （1）UDF（User-Defined-Function）
    一进一出
  • （2）UDAF（User-Defined Aggregation Function）
    聚集函数，多进一出，类似于：count/max/min
  • （3）UDTF（User-Defined Table-Generating Functions）
    一进多出，如lateral view explore()
• 4）官方文档地址
  https://cwiki.apache.org/confluence/display/Hive/HivePlugins
• 5）编程步骤：
  • （1）继承org.apache.hadoop.hive.ql.UDF
  • （2）需要实现evaluate函数；evaluate函数支持重载；
  • （3）在hive的命令行窗口创建函数
    a）添加jar
    add jar linux_jar_path
    b）创建function，
    create [temporary] function [dbname.]function_name AS class_name;
  • （4）在hive的命令行窗口删除函数
    Drop [temporary] function [if exists] [dbname.]function_name;
• 6）注意事项
  • （1）UDF必须要有返回类型，可以返回null，但是返回类型不能为void；

压缩和存储

开启Map输出阶段压缩

开启Reduce输出阶段压缩

文件存储格式

列式存储和行式存储

• 如图所示左边为逻辑表，右边第一个为行式存储，第二个为列式存储。

行存储的特点：
查询满足条件的一整行数据的时候，列存储则需要去每个聚集的字段找到对应的每个列的值，行存储只需要找到其中一个值，其余的值都在相邻地方，所以此时行存储查询的速度更快。

列存储的特点：
因为每个字段的数据聚集存储，在查询只需要少数几个字段的时候，能大大减少读取的数据量；每个字段的数据类型一定是相同的，列式存储可以针对性的设计更好的设计压缩算法。

• TEXTFILE和SEQUENCEFILE的存储格式都是基于行存储的；

• ORC和PARQUET是基于列式存储的。

TextFile

默认格式，数据不做压缩，磁盘开销大，数据解析开销大。可结合Gzip、Bzip2使用，但使用Gzip这种方式，hive不会对数据进行切分，从而无法对数据进行并行操作。

Orc格式

Orc格式是由一个或多个stripe组成的，每个250MB，有点类似row Group，每个Stripe中有IndexData部分，RowData部分，Stripe Footer部分组成。

1）Index Data：一个轻量级的index，默认是每隔1W行做一个索引。这里做的索引应该只是记录某行的各字段在Row Data中的offset。
2）Row Data：存的是具体的数据，先取部分行，然后对这些行按列进行存储。对每个列进行了编码，分成多个Stream来存储。
3）Stripe Footer：存的是各个Stream的类型，长度等信息。

每个文件有一个File Footer，这里面存的是每个Stripe的行数，每个Column的数据类型信息等；每个文件的尾部是一个PostScript，这里面记录了整个文件的压缩类型以及FileFooter的长度信息等。在读取文件时，会seek到文件尾部读PostScript，从里面解析到File Footer长度，再读FileFooter，从里面解析到各个Stripe信息，再读各个Stripe，即从后往前读。

Parquet格式

Parquet文件是以二进制方式存储的，所以是不可以直接读取的，文件中包括该文件的数据和元数据，因此Parquet格式文件是自解析的。

企业级调优

Fetch抓取

本地模式

表的优化

大表Join大表

空KEY过滤：

hive (default)> insert overwrite table jointable 
select n.* 
from (select * from nullidtable where id is not null ) n  
left join 
ori o on n.id = o.id;

空key转换：
有时虽然某个key为空对应的数据很多，但是相应的数据不是异常数据，必须要包含在join的结果中，此时我们可以表a中key为空的字段赋一个随机的值，使得数据随机均匀地分不到不同的reducer上。

insert overwrite table jointable
select n.* from nullidtable n full join ori o on 
case when n.id is null then concat('hive', rand()) else n.id end = o.id;

MapJoin

Group By

Count(Distinct) 去重统计

数据量小的时候无所谓，数据量大的情况下，由于COUNT DISTINCT操作需要用一个Reduce Task来完成，这一个Reduce需要处理的数据量太大，就会导致整个Job很难完成，一般COUNT DISTINCT使用先GROUP BY再COUNT的方式替换：

小文件进行合并

JVM重用

推测执行