Hive基础总结

Hive

Hive中文手册

思考：

• Hive的架构原理
• Hive和数据库的比较
• 几种访问方式的不同

1.1 什么是Hive

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

Hive本质是将HQL转换成MapReduce

• Hive处理的数据存储在HDFS
• Hive分析数据底层的实现是Mapeduce（也可以配置在Spark或者Tez）
• 执行程序运行在Yran上

1.2 Hive的架构

元数据：Metastore

• 元数据包括：数据库（默认是default）、表名、表的拥有者、列/分区字段、表的类型（是否是外部表）、表的数据所在目录等。
• 默认存储在自带的derby数据库中，由于derby数据库只支持单客户端访问，生产环境中为了多人开发，推荐使用MySQL存储Metastore。

SQL Parser 解析器：将SQL语句翻译成MR的任务

Physical Plan 编译器：将SQL真正的编译成MR的流程

Query Optimizer 优化器：自动优化写的SQL语句（在做连接时，左/右表为大表时，所需的执行时间是不一样的）

Execution 执行器：执行最终任务

3）驱动器：Driver

（1）解析器（SQLParser）：将SQL字符串转换成抽象语法树（AST）

（2）语义分析（Semantic Analyzer）：将AST进一步划分为QeuryBlock

（3）逻辑计划生成器（Logical Plan Gen）：将语法树生成逻辑计划

（4）逻辑优化器（Logical Optimizer）：对逻辑计划进行优化

（5）物理计划生成器（Physical Plan Gen）：根据优化后的逻辑计划生成物理计划

（6）物理优化器（Physical Optimizer）：对物理计划进行优化

（7）执行器（Execution）：执行该计划，得到查询结果并返回给客户端

1.3 Hive和数据库比较

相同点：Hive有类似的SQL语法——HQL

不同点：

• 数据更新：Hive是读多写少，不建议对数据的改写，所有的数据都是在加载的时候确定好的。而数据库通常是需要经常进行修改的。
• 执行延迟：Hive的执行时间会比较长，当数据量大的时候，Hive的并行计算才能体现出优势。
• 数据规模：Hive是建立在集群上并可以利用MR进行并行计算，所以Hive可以支持大规模的运算。

1.4 为什么推荐使用MySQL存储Metastore

当在一个窗口启动Hive客户端并在另一个窗口开启Hive时，会报如下错误

Caused by: ERROR XSDB6: Another instance of Derby may have already booted the database /opt/module/hive/metastore_db.
        at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
        at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
        at org.apache.derby.impl.store.raw.data.BaseDataFileFactory.privGetJBMSLockOnDB(Unknown Source)
        at org.apache.derby.impl.store.raw.data.BaseDataFileFactory.run(Unknown Source)
...

原因在于Hive默认使用的元数据库为derby。derby数据库的特点是同一时间只允许一个客户端访问。如果多个Hive客户端同时访问，就会报错。由于在企业开发中，都是多人协作开发，需要多客户端同时访问Hive，怎么解决呢？我们可以将Hive的元数据改为用MySQL存储，MySQL支持多客户端同时访问。

配置步骤：

1. 在MySQL中chain关键Hive元数据库——create database metastore;
2. 将MySQL的JDBC驱动拷贝到Hive的lib目录下
3. 在$HIVE_HOME/conf目录下新建hive-site.xml文件，并添加MySQL中的jdbc连接的URL、Driver、Username、Password和Hive默认在HDFS的工作目录属性

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>

<configuration>
    <!-- jdbc连接的URL -->
    <property>
        <name>javax.jdo.option.ConnectionURL</name>
        <value>jdbc:mysql://hadoop102:3306/metastore?useSSL=false</value>
    </property>
    
    <!-- jdbc连接的Driver-->
    <property>
        <name>javax.jdo.option.ConnectionDriverName</name>
        <value>com.mysql.jdbc.Driver</value>
    </property>
    
	<!-- jdbc连接的username-->
    <property>
        <name>javax.jdo.option.ConnectionUserName</name>
        <value>root</value>
    </property>

    <!-- jdbc连接的password -->
    <property>
        <name>javax.jdo.option.ConnectionPassword</name>
        <value>123456</value>
    </property>

    <!-- Hive默认在HDFS的工作目录 -->
    <property>
        <name>hive.metastore.warehouse.dir</name>
        <value>/user/hive/warehouse</value>
    </property>
</configuration>

4. 初始化Hive元数据库（修改为采用MySQL存储元数据）
   • bin/schematool -dbType mysql -initSchema -verbose

2.1 本地启动Hive

[hadoop:hive]$ bin/hive

2.2 远程访问Hive

2.2.1 使用元数据服务访问Hive

要想远程/第三方访问Hive，有两种方法，第一种通过Hive的元数据服务访问

• 在之前的hive-site.xml文件上，添加如下内容

<!-- 指定metastore服务的地址 -->
<property>
	<name>hive.metastore.uris</name>
	<value>thrift://hadoop102:9083</value>
</property>

注意：主机名需要改为metastore服务所在节点，端口号无需修改，metastore服务的默认端口就是9083。

当配置文件写好时，之前的启动方式就不行了

[hadoop:hive]$ bin/hive
hive> show tables;
FAILED: HiveException java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient

此时操作会报错，需要更改以下的启动方式

1. 通过在第一个窗口中，开启元数据服务(开启后，窗口不要动)

   [hadoop:hive]$ bin/hive --service metastore
   

2. 开启另一个窗口

   [hadoop:hive]$ bin/hive
   hive> show tables;
   OK
   test
   

将hive元数据作为前台进程启动，即关闭程序窗口，服务随之停止（不推荐使用）

2.2.2 使用JDBC方式访问Hive

（1）Hadoop端配置

hivesever2的模拟用户功能，依赖于Hadoop提供的proxy user（代理用户功能），只有Hadoop中的代理用户才能模拟其他用户的身份访问Hadoop集群。因此，需要将hiveserver2的启动用户设置为Hadoop的代理用户，配置方式如下：

• 在Hadoop的core-site.xml文件添加如下信息，然后记得分发三台机器

<!--配置所有节点的atguigu用户都可作为代理用户-->
<property>
    <name>hadoop.proxyuser.atguigu.hosts</name>
    <value>*</value>
</property>

<!--配置atguigu用户能够代理的用户组为任意组-->
<property>
    <name>hadoop.proxyuser.atguigu.groups</name>
    <value>*</value>
</property>

<!--配置atguigu用户能够代理的用户为任意用户-->
<property>
    <name>hadoop.proxyuser.atguigu.users</name>
    <value>*</value>
</property>

（2）Hive端配置

1. 在hive-site.xml文件添加如下信息

   <!-- 指定hiveserver2连接的host -->
   <property>
   	<name>hive.server2.thrift.bind.host</name>
   	<value>hadoop102</value>
   </property>
   
   <!-- 指定hiveserver2连接的端口号 -->
   <property>
   	<name>hive.server2.thrift.port</name>
   	<value>10000</value>
   </property>
   

2. 启动方式：

   1. 启动hiveserver2

      bin/hive --service hiveserver2
      

   2. 使用命令行客户端beeline进行远程访问

      bin/beeline -u jdbc:hive2://hadoop102:10000 -n atguigu
      

      -u 表示url

      -n后面接的是用户名，用户名跟上面配置的core-site.xml文件有关

用户模拟功能图解(不设置参数，默认开启)：

2.3 Hive使用小技巧

• “-e”不进入hive的交互窗口执行hql语句

  [atguigu@hadoop102 hive]$ bin/hive -e "select id from student;"
  

• “-f”执行脚本中的hql语句

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

     [atguigu@hadoop102 hive]$ mkdir datas
     [atguigu@hadoop102 datas]$ vim hivef.sql
     

  2. 文件中写入正确的hql语句

     select * from student;
     

  3. 执行文件中的hql语句

     [atguigu@hadoop102 hive]$ bin/hive -f /opt/module/hive/datas/hivef.sql
     

  4. 执行文件中的hql语句并将结果写入文件中

     [atguigu@hadoop102 hive]$ bin/hive -f /opt/module/hive/datas/hivef.sql  > /opt/module/hive/datas/hive_result.txt
     

3 Hive数据类型

3.1 基本数据类型

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

3.2 集合数据类型

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

取值

• array：arr[0]
• map：map[key]
• struct：struct.key

3.3 类型转换

• 隐式转换

  • 与Java中的默认类型转换机制一样

• 显示转换

  • 可以借助cast函数完成显示的类型转换

  -- 语法
  cast(expr as <type>)
  -- 案例
  hive (default)> select '1' + 2, cast('1' as int) + 2;
  _c0	   _c1
  3.0	    3
  

4 DDL数据定义

4.1 数据库

• 创建数据库

语法：

CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

案例：

-- 不指定路径
create database db_hive1;
-- 指定路径
create database db_hive2 location '/db_hive2';
-- 创建库分区
create database db_hive3 with dbproperties('create_date'='2022-11-18')

• 查询数据库

1）展示所有数据库

语法：

SHOW DATABASES [LIKE 'identifier_with_wildcards'];

2）查看数据库信息

语法：

DESCRIBE DATABASE [EXTENDED] db_name;

EXTENDED：表示查看更多信息，如hdfs地址、分区....

• 修改数据库

用户可以使用alter database命令修改数据库某些信息，其中能够修改的信息包括dbproperties、location、owner user。需要注意的是：修改数据库location，不会改变当前已有表的路径信息，而只是改变后续创建的新表的默认的父目录。

语法：

--修改dbproperties
ALTER DATABASE database_name SET DBPROPERTIES (property_name=property_value, ...);

--修改location
ALTER DATABASE database_name SET LOCATION hdfs_path;

--修改owner user
ALTER DATABASE database_name SET OWNER USER user_name;

• 删除数据库

语法：

DROP DATABASE [IF EXISTS] database_name [RESTRICT|CASCADE];

RESTRICT：严格模式，若数据库不为空，则会删除失败，默认为该模式。CASCADE：级联模式，若数据库不为空，则会将库中的表一并删除。

4.2 表

1. 普通创建表

语法：

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]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]
[TBLPROPERTIES (property_name=property_value, ...)]

关键字说明：

• TEMPORARY

  • 临时表，该表只在当前会话可见，会话结束，表会被删除。

• EXTERNAL（重点）

  • 外部表，不写默认为内部表。外部表表示Hive只接管元数据，当在Hive客户端中删除表时，HDFS上的数据不会相应删除。内部表意味着Hive完全接管该表，删除该表时，HDFS上的数据也会相应的删除。

• data_type（重点）

  • 设置字段的数据类型

• PARTITIONED BY（重点）

  • 创建分区表

• CLUSTERED BY ... SORTED BY...INTO ... BUCKETS（重点）

  • 创建分桶表

• ROW FORMAT（重点）

  • 指定SERDE，SERDE是Serializer and Deserializer的简写。Hive使用SERDE序列化和反序列化每行数据。语法说明如下：

  语法一：DELIMITED 关键字表示对文件中的每一个字段按照分隔符进行分割，其会使用默认的SERDE对每行数据进行序列化和反序列化。

  ROW FORAMT DELIMITED 
  [FIELDS TERMINATED BY char] 
  [COLLECTION ITEMS TERMINATED BY char] 
  [MAP KEYS TERMINATED BY char] 
  [LINES TERMINATED BY char] 
  [NULL DEFINED AS char]
  

  Ø fields terminated by ：列分隔符

  Ø collection items terminated by ： map、struct和array中每个元素之间的分隔符

  Ø map keys terminated by ：map中的key与value的分隔符

  Ø lines terminated by ：行分隔符

  语法二：SERDE 关键字可用于指定其他内置的SERDE 或者用户自定义的SERDE。列如JSON SERDE，可用于处理JSON 字符串。

  ROW FORMAT SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value,property_name=property_value, ...)]
  

• STORED AS（重点）

  • 指定文件格式，常用的文件格式有，textfile（默认值），sequence file，orc file、parquet file等等。

• LOCATION

  • 指定表所对应的HDFS路径，若不指定路径，其默认值为

    ${hive.metastore.warehouse.dir}/db_name.db/table_name

• TBLPROPERTIES

  • 用于配置表的一些KV键值对参数

2. 查看表

   • 展示所有表

   语法：

   SHOW TABLES [IN database_name] LIKE ['identifier_with_wildcards']；
   

   like通配表达式说明：*表示任意个任意字符，|表示或的关系。

   案例：

   show tables like 'stu*';
   

   • 查看表信息

   语法：

   DESCRIBE [EXTENDED | FORMATTED] [db_name.]table_name
   

   EXTENDED：展示详细信息

   FORMATTED：对详细信息进行格式化的展示

3. 修改表

   • 重命名表

   语法：

   ALTER TABLE table_name RENAME TO new_table_name
   

   • 修改列信息

   语法：

   ​ 增加列：该语句允许用户增加新的列，新增列的位置位于末尾。

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

   ​ 更新列：该语句允许用户修改指定列的列名、数据类型、注释信息以及在表中的位置。

   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 REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
   

4. 删除表

语法：

DROP TABLE [IF EXISTS] table_name;

5. 清空表

语法：

TRUNCATE [TABLE] table_name

truncate只能清空管理表，不能删除外部表中数据

5 DML数据操作

5.1 Load

Load语句可将文件导入到Hive表中。

语法：

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)];

• local：表示从本地加载数据到Hive表；否则从HDFS加载数据到Hive表。
• overwrite：表示覆盖表中已有数据，否则表示追加。
• partition：表示上传到指定分区，若目标是分区表，需指定分区。

5.2 Insert

1. 将查询结果插入表中

语法：

INSERT (INTO | OVERWRITE) TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement;

2. 将给定Values插入表中

语法：

INSERT (INTO | OVERWRITE) TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES values_row [, values_row ...]

3. 将查询结果写入目标路径

语法：

INSERT OVERWRITE [LOCAL] DIRECTORY directory
  [ROW FORMAT row_format] [STORED AS file_format] select_statement;

5.3 Export&Import

​ Export导出语句可将表的数据和元数据信息一并到处的HDFS路径，Import可将Export导出的内容导入Hive，表的数据和元数据信息都会恢复。Export和Import可用于两个Hive实例之间的数据迁移。

语法：

--导出
EXPORT TABLE tablename TO 'export_target_path'

--导入
IMPORT [EXTERNAL] TABLE new_or_original_tablename FROM 'source_path' [LOCATION 'import_target_path']

案例：

--导出
hive>
export table default.student to '/user/hive/warehouse/export/student';

--导入
hive>
import table student2 from '/user/hive/warehouse/export/student';

6 查询

官网地址

查询语法：

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM table_reference       -- 从什么表查
  [WHERE where_condition]   -- 过滤
  [GROUP BY col_list]        -- 分组查询
   [HAVING col_list]          -- 分组后过滤
  [ORDER BY col_list]        -- 排序
  [CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
  ]
 [LIMIT number]                -- 限制输出的行数

...

Join连接的hive sql执行过程

• full join

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

MySQL中没有full join

• 多表连接

案例：

select 
    e.ename, 
    d.dname, 
    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进行连接操作。

• 笛卡尔积

案例：

select 
    empno, 
    dname 
from emp, dept;

6.1 每个Reduce内部排序（Sort By）

每一个hive sql语句都可以看作是MR，排序操作发生在Reduce中。

​ Sort By：对于大规模的数据集order by的效率非常低。在很多情况下，并不需要全局排序，此时可以使用Sort by。

​ Sort by为每个reduce产生一个排序文件。每个Reduce内部进行排序，对全局结果集来说不是排序。

1. 设置reduce个数

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

2. 根据部门编号查看员工信息

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

hive sql 执行过程

因为设置了3个Reduce，所以最后会生成3个结果文件。

在这3个结果文件内部，deptno是按降序排序的。但是对于全局，这3个结果文件是无序的。

这3个结果文件内的数据的分配默认是随机的。

为什么是随机？因为防止出现数据倾斜

6.2 分区（Distribute By）

​ Distribute By：在有些情况下，我们需要控制某个特定行应该到哪个Reducer，通常是为了进行后续的聚集操作。distribute by子句可以做这件事。distribute by类似MapReduce中partition（自定义分区），进行分区，结合sort by使用。

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

案例：

• 先按照部门编号区分，再按照员工编号薪资降序排序，即：只关心部门内的员工号有序，无需使用order by进行全局排序

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

注意：

• 因为distribute by的分区规则是根据分区字段的hash码与reduce的个数进行相除后，所以余数相同的分到一个区。
• Hive要求distribute by语句要写在sort by语句之前。
• 演示完以后mapreduce.job.reduces的值要设置回-1，否则下面分区or分桶表load跑MapReduce的时候会报错。

hive sql 执行过程

6.3 Cluster By

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

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

以下两条SQL等价：

-- 使用cluster by
hive (default)> 
select 
    * 
from emp 
cluster by deptno;

-- 使用distribute by + sort by
hive (default)> 
select 
    * 
from emp 
distribute by deptno;

6.4 四种 By 总结

• Order By
  • 全局排序，只有一个Reducer，一旦数据多，是直接跑不动的
• Sort By
  • 区内排序，往往sort by 结合distribute by 使用，实现分区排序
• Distribute By
  • 按字段的hash值与reduce的个数进行相除后的余数进行分区
• Cluster by
  • 当Distribute By 和 Sort By 结合使用时的排序字段相同，可替换成Cluster by使用

7 分区表

分区表实际上就是对应一个HDFS文件系统上的独立文件夹，该文件夹下是该分区所有的数据文件。

Hive中的分区就是分目录。把一个大的数据集根据业务需要分割成小的数据集。在查询时通过where表达式选择锁需要的指定的分区，避免全表扫描，这样的查询效率会提高很多。

7.1 分区表基本语法

创建分区表实例：

create table dept_partition
(
    deptno int,    --部门编号
    dname  string, --部门名称
    loc    string  --部门位置
)
partitioned by (year string) --设置分区字段
row format delimited fields terminated by '\t';

建表语句中的分区关键字是partitioned，不是partition

设置的分区字段不能是表中已有的字段，可以把分区字段理解为表的伪列

加载数据

load

-- 加载数据时，需要使用 partition 指定该数据所属的分区
load data local inpath '/home/tengyer/data/dept_2020.log' into table dept_partition partition(year='2020');
load data local inpath '/home/tengyer/data/dept_2021.log' into table dept_partition partition(year='2021');
load data local inpath '/home/tengyer/data/dept_2022.log' into table dept_partition partition(year='2022');

注意：此处的关键字是partition ，和创建表时的partitioned不一样

加载完数据后，在 hdfs 上查看该表时，会发现该表目录/user/hive/warehouse/dept_partition 下面创建了三个文件夹：year=2020、year=2021、year=2022，分别存储该分区内的数据文件。

如果查询时where条件里指定了分区条件，那么就可以避免全表扫描，只在扫描该分区内的数据文件即可，可以提高查询速度。

insert

将year='2020'分区的数据插入到year='2019'分区，可执行如下转载语句

insert overwrite table dept_partition partition (year = '2019')
select deptno, dname, loc
from dept_partition
where year = '2020';

分区表查询

-- 创建表时，创建了三个字段：deptNo、dname、loc，还有一个分区字段 year
-- 使用select查询时，year也会被当做一个字段查询出来，
select deptno ,dname ,loc ,year from dept_partition 

-- 分区字段也可以像普通字段一样进行where
select * from dept_partition where year='2020';

查看表的所有分区信息

show partitions dept_partition;

增加分区

• 创建单个分区

alter table dept_partition add partition(year='2018');

• 同时创建多个分区（分区之间不能有逗号）

alter table dept_partition add partition(year='2016') partition(year='2017');

删除分区

• 删除单个分区

alter table dept_partition drop partition (year='2018');

• 删除多个分区（分区之间必须有逗号）

alter table dept_partition drop partition(year='2016'), partition(year='2017');

7.2 二级分区

​ 思考：如果一天内的日志数据量也很大，如何再将数据拆分?答案是二级分区表，例如可以在按天分区的基础上，再对每天的数据按小时进行分区。

当分区内数据也很大时，需要创建二级分区表：

create table dept_partition2(
    deptNo int,
    dname string,
    loc string
)
partitioned by (year string, month string)   -- 按年、月进行二级分区
row format delimited fields terminated by ',';

加载数据到二级分区表：

load data local inpath '/home/tengyer/data/dept_2020.log' into table dept_partition2 partition(year='2020', month='01');

此时在hdfs产生的目录为：/user/hive/warehouse/dept_partition2/year=2020/month=01

7.3 让分区表和数据产生关联

如果使用hadoop fs -mkdir手动在分区表中创建一个分区文件夹并上传了数据文件，此时使用select语句是查询不到的，因为该分区文件夹在元数据库中并不存在，无法扫描该文件夹。

hadoop fs -mkdir /user/hive/warehouse/dept_partition/year=2023
hadoop fs -put dept_2023.log /user/hive/warehouse/dept_partition/year=2023

此时使用select查询全表时，查询不到year=2023的数据。

如果要让分区表和数据产生关联，可以通过以下几种方式：

方式1：创建文件夹，并上传数据后，执行修复命令

msck repair table dept_partition;

方式2：创建文件夹，并上传数据后，手工创建分区

alter table dept_partition add partition(year='2024');

方式3：创建文件夹后，将数据文件load到分区

-- 使用load命令，即使不创建文件夹，load命令也会自动创建
load data local inpath '/home/tengyer/data/dept_2025.log' into table dept_partition partition(year='2025');

7.4 默认分区

当load数据到分区表时，没有指定分区，则会导入到一个Hive生成的默认分区中。

load data local inpath '/home/tengyer/data/dept_2026.log' into table dept_partition;

此时数据会被导入到一个Hive创建的默认分区中，路径为：/user/hive/warehouse/dept_partition/year=__HIVE_DEFAULT_PARTITION__

7.5 动态分区

关系型数据库中，对分区表insert数据时候，数据库自动会根据分区字段的值，将数据插入到相应的分区中，hive中也提供了类似的机制，即动态分区（Dynamic Partition）。但是使用Hive的动态分区需要进行相应的配置。

开启动态分区参数设置（默认值就是true）：

set hive.exec.dynamic.partition=true;

设置为非严格模式（动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区）

set hive.exec.dynamic.partition.mode=nonstrict;

示例：

创建表：

create table dept_dynamic_partition (
    dname string,
    loc string
)
partitioned by (deptNo int)   -- 分区字段为deptNo
row format delimited fields terminated by ',';

根据插入数据的deptNo自动找到对应分区：

insert into dept_dynamic_partition partition(deptNo)   -- 指定分区字段为deptNo，但是静态指定所属的具体分区
select dname,loc,deptNo from dept -- 将分区字段deptNo放到select的最后面(动态分区的列必须放在最后)

如果不设置非严格模式，那么这句sql会报错。设置了非严格模式后，就可以正常执行了。

表只有2个字段，查询语句有3个，所以会默认最后一个字段为分区

在Hive3中，动态分区还可以进行简写：

insert into dept_dynamic_partition -- 不需要再指定分区字段。这种写法在严格模式下也可以正常执行
select dname,loc,deptNo from dept -- 将分区字段deptNo放到select的最后面

其他参数：

在所有执行MR的节点上，最大一共可以创建多少个动态分区（默认1000）：

set hive.exec.max.dynamic.partitions=1000;

在每个执行MR的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即day字段有365个值，那么该参数就需要设置成大于365（默认值100）：

set hive.exec.max.dynamic.partitions.pernode=100;

整个MR Job中，最大可以创建多少个hdfs文件（默认值100000）：

set hive.exec.max.created.files=100000;

当有空分区生成时，是否抛出异常。一般不需要设置（默认值false）：

set hive.error.on.empty.partition=false;

8 分桶表

分区提供了一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可以形成合理的分区。对于一张表或者分区，Hive可以进一步组织成桶，也就是更为细粒度的数据范围划分。

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

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

分桶表在数据集极大的情况下才会用到，对极大的数据集进行抽样，不常用。

示例：

创建分桶表：

create table stu_buck(
	id int,
	name string
)
clustered by (id)  -- 分桶表的分桶字段必须是表中的已有字段
into 4 buckets    -- 指定桶的数量
row format delimited fields terminated by ',';

分桶表的分桶字段必须是表中的已有字段，分区表则是不能是已有字段

准备数据：

1001,ss1
1002,ss2
1003,ss3
1004,ss4
1005,ss5
1006,ss6
1007,ss7
1008,ss8
1009,ss9
1010,ss10
1011,ss11
1012,ss12
1013,ss13
1014,ss14
1015,ss15
1016,ss16

将数据导入分桶表：

load data local inpath '/home/tengyer/data/stu.txt' into table stu_buck;

因为我们设置了4个桶，所以导入数据后，会在/user/hive/warehouse/stu_buck下生成4个数据文件来存储这些数据。

分桶的规则：将分桶字段进行hash，然后对桶的个数取余，决定这条数据存放在哪个桶文件中。

因为int的hash值就是本身，所以000000_0文件中存储的是：1001、1005、1009、1013的数据。其他几个文件类推。

分桶表操作需要注意的事项：

• reducer的个数设置为-1，让Job自行决定需要用多少个reduce。或者将reducer的个数设置为大于等于分桶表的桶数

• 导入数据时最好从hdfs中load数据到分桶表中，避免本地数据文件只存在在一个Datanode而其他DataNode读取不到的问题

• 不要使用本地模式

insert方式将数据导入到分桶表：

insert into table stu_buck select * from student;

抽样查询

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

语法：TABLESAMPLE(BUCKET x OUT OF y)

示例：

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

表示将查出来的数据分成4个桶，从第一个桶开始抽取

注意：的值必须小于等于的值，否则会报错。x``y

其中：

• y 表示样本总共要分的份数

• x表示从哪一份开始向后找数据

9 函数

9.1 查询系统函数

查看系统自带的函数

show functions;

显示自带的函数的用法

desc function upper;

详细显示自带的函数的用法

desc function extended upper;

Hive的函数分为以下三类：

• UDF：一行输入对应一行输出，列入：upper这类字段转换
• UDAF：多行输入对应一行输出（聚合函数），例如：max、count
• UDTF：一行输入对应多行输出（炸裂函数），例如：explode将一个Array、Map字段拆分成多行返回

9.2 常用函数

普通类型字段函数

大致同MySQL的各个函数：

-- nvl空值转换
select nvl(null, 'a')

-- case when then else end
select 
	deptno,
    case deptno
        when 10 then 'aaa'
        when 20 then 'bbb'
        else 'ccc'
    end
from dept;

-- 或者
select 
	deptno,
    case 
        when deptno < 30 then 'aaa'
        when deptno < 40 then 'bbb'
        else 'ccc'
    end
from dept

-- concat 拼接字符串
select concat(dname, ',', loc), o.* from dept o;

-- 其他的，upper、lower、max、min、count、avg、abs、length、trim、lpad、rpad等函数同mysql
select lpad('aa', 8, '0'); -- 位数不足8位时，向左边补零 000000aa
select abs(-5); -- 5

-- 取整：
-- ceil：向上取整
-- floor：向下取整
-- round: 四舍五入
select floor(1.25); -- 1

-- cast:类型映射(转换)
select cast(1 as decimal(16,2)); -- 1.00

其他常用函数：

-- if 函数
select 
	deptno,
	-- if(判断条件, 判断为真, 否则)
	if(deptno > 30, 'aaa', 'bbb')
from dept;

-- substring 截取字符串
select substring('123456',1)-- 123456
select substring('123456',2,5)-- 23456

-- concat_ws 拼接字符串：把分隔符提到最前面
-- 等同于 select concat(ename, ',', job, ',',  hiredate) , e.* from emp e;
select concat_ws(',', ename, job, hiredate) , e.* from emp e;
-- concat_ws函数 也可以将ARRAY类型的字段的每个元素使用指定分隔符拼接成字符串
select concat_ws(',', friends), t.* from test3 t;

-- split：将字符串转换成数组
select split(name,'n'), s.* from student1 s;

-- 获取当前时间的时间戳（长整形数字，相对于unix时间的偏移量）
select unix_timestamp();
-- 将日期字符串转换成时间戳的长整型
select unix_timestamp('2020-02-01', 'yyyy-MM-dd');

-- 将相对于unix时间偏移量的长整型数字转换为日期格式
select from_unixtime(1580515200); 
select from_unixtime(1580515200, 'yyyy/MM/dd');

-- 获取当前日期
select current_date();
-- 获取当前时间戳
select current_timestamp();

-- 获取时间戳中的日期
select to_date(current_timestamp());
select to_date('2022-01-01 10:12:00');

-- 也可以通过 year、month、day、hour、minute、second、weekofyear(截至时间，这一年进过了多少周)、dayofmonth 等相关函数获取年月日时分秒
select year('2022-01-01 10:12:00');

-- 两个日期之间的月数
select months_between('2022-01-01 10:12:00','2021-10-05 10:12:00');

-- 几个月后/前
select add_months('2022-01-01 10:12:00', -2);
select add_months('2022-01-01 10:12:00', 2, 'yyyy/MM/dd');

-- 计算两个日期之间相差的天数
select datediff('2022-01-01 10:12:00', '2021-10-24 10:12:00');

-- 日期加上n天
select date_add('2022-01-01 10:12:00', 2);

-- 获取月末的日期
select last_day('2022-01-01 10:12:00');

-- 日期格式化
select date_format('2022-01-01 10:12:00', 'yyyy/MM/dd');

-- 正则表达式匹配替换
-- 语法
regexp_replace(str, regexp, rep)-- str为要匹配的字符串表达式，regexp为具有匹配模式的字符串表达式，rep为替换字符串的字符串表达式
select regexp_replace('2020/10/25', '/', '-')

日期函数

-- 返回当前或指定时间的时间戳 
unix_timestamp();
unix_timestamp("2022-01-01",'yyyy-MM-dd');

-- 将时间戳转为日期格式
from_unixtime(1640995200);

-- 当前日期
current_date();

-- 当前的日期加时间
current_timestamp();

-- 抽取日期部分
to_date('2022-01-01 12:00:00');

-- 获取年月日时分秒
year();
month();
day();
hour();
minute();
second();

-- 当前时间是一年中的第几周
weekofyear();
-- 当前时间是一月中的第几天
dayofmonth();

-- 两个日期之间的月数
months_between('2022-01-01 10:12:00','2021-10-05 10:12:00');

-- 加减月份
add_months('2022-01-01 10:12:00', -2);
add_months('2022-01-01 10:12:00', 2, 'yyyy/MM/dd');

-- 计算两个日期之间相差的天数(前减后)
datediff('2022-01-01 10:12:00', '2021-10-24 10:12:00')

-- 日期加上n天
date_add('2022-01-01 10:12:00', 2);

-- 获取月末的日期
last_day('2022-01-01 10:12:00');

-- 日期格式化
date_format('2022-01-01 10:12:00', 'yyyy/MM/dd');

字符串函数

-- 转大/小写
upper();
lower();

-- 去除字符串前后空格
trim();

-- 向左/右补齐，到指定长度
select lpad('aa', 8, '0'); -- 位数不足8位时，向左边补零 000000aa
rpad();

-- 正则表达式匹配替换
-- 语法
regexp_replace(str, regexp, rep)-- str为要匹配的字符串表达式，regexp为具有匹配模式的字符串表达式，rep为替换字符串的字符串表达式
select regexp_replace('2020/10/25', '/', '-') -- 2020-10-25

集合操作

示例：

-- 获取Array、Map字段中的元素个数
select size(friends),size(children) from test3;

-- 获取map中的所有key
select map_keys(children) from test3;
-- 获取map中的所有value
select map_values(children) from test3;

-- 判断array中是否包含某个元素
select array_contains(friends,'Jack') from test3;

-- 将array中的元素进行排序
select sort_array(friends) from test3;

行转列函数

行转列：将多行数据的某个字段合并成一个值

-- collect_set：将多行数据中的某个字段合并拼接成一个数组（行转列）
-- collect_set只接收基本数据类型，且会将字段的值进行去重，然后产生Array类型的字段
select collect_set(deptno) from dept;

-- 功能大致同collect_set，但是collect_list不会进行去重，collect_set会进行去重
select collect_list(deptno) from dept;

-- concat('str1','str2','str3')：将多个字符拼接起来
select concat('a',',','b',',','c') -- a,b,c
-- concat_ws('regex','str1','str2','str3')：按照将多个str按照regex拼接起来
select concat_ws(',','a','b','c') -- a,b,c

示例：

-- 将相同部门、相同工作的雇员拼接到一起
select
	concat_ws('|',collect_list(ename)),   -- 使用collect_list聚合，然后使用concat_ws将数组元素拼接成字符串
	concat(job,',',deptno)  -- 分组字段job、deptno，将分组字段使用逗号拼接到一起
from emp 
group by 
	job,deptno;

列转行函数

列转行：将一个值拆分成多行进行展示

-- explode：将Array字段或者Map字段拆分成多行（列转行）
-- 将数组元素拆分成多行返回，每行只存储一个元素
select explode(friends) from test3;
-- 将map拆分成多行返回，返回两个字段，一个key字段，一个value字段，每行存储一对key-value
select explode(children) from test3;

-- lateral view ：侧写
-- 直接select explode函数时，不能再同时查询其他字段，否则会报错
-- 如果想要同时关联展示表中的其他字段，可以使用 lateral view 进行侧写
select 
	name,  -- 可以正常查询其他字段
	friends,
	children, 
	perFriend   -- 查询侧写字段（即展开成多行的friends字段），最后效果类似联合查询
from 
	test3
lateral view 
	explode(friends) perFriend as perFriend; -- 通过lateral view 将展开了的friends字段进行侧写，将输出的结果列起别名为perFriend
	
-- 如果是map，会展开成两列，所以侧写时，as语句后要有两个字段名
select 
	name,
	friends,
	children, 
	perChildName,  -- 展开了的chidren中每个key
	perChildAge    -- 展开了的chidren中每个value
from 
	test3
lateral view 
	explode(children) perChildInfo as perChildName,perChildAge  -- map会展开成两列，所以as语句后要有两个字段名

如果采用炸开函数，还行与其表中的其他字段产生关联，就要用lateral view

lateral view 写在from 表名 的后面

9.3 窗口函数

函数说明

over()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。同Oracle的over()。

其中over()函数内可以使用的关键字：

• CURRENT ROW：当前行

• n PRECEDING：往前n行数据

• n FOLLOWING：往后n行数据

• UNBOUNDED：没有边界的

• • UNBOUNDED RRECEDING：从前面的起点
• • UNBOUNDED FOLLOWING：到后面的终点

示例：

查询10号部门的每个雇员，以及总人数：

select 
	ename, 
	
	-- 每个雇员的数据行中，都会显示10号部门的总人数
	count(*) over()   -- 此处count(*)的分组条件是over开窗内的分组条件，因为此处over内容为空，所以就直接统计了总人数
from emp 
where deptno = 10;

查询10号部门的每个雇员，以及和该雇员从事相同工作的总人数：

select 
	ename,
	deptno, 
	job, 
	count(*) over(partition by job) -- 此处在over内增加分组条件 job，就会按job进行分组统计
from emp 
where deptno = 10;

查询每个雇员信息，并且在雇员后面展示该雇员所在部门的薪资累加：同一个部门的1号雇员展示自己的薪资，2号雇员展示1号和2号的累加，3号雇员展示1/2/3雇员的累加，以此类推

select 
	e.*, 
	sum(sal) over(
        partition by deptno 
        order by deptno,empno) -- 加入 order by ，使其按顺序进行累加展示（默认统计的就是从起点数据到当前行）
from emp e 
order by deptno,empno;

-- 或者完整版写法：
select 
	e.*, 
	sum(sal) over(
        partition by deptno 
        order by deptno,empno 
        rows between unbounded preceding and current row) -- 累加时，只累加从起点到当前行的数据，不计算后面的数据
from emp e 
order by deptno,empno

rows between unbounded preceding and current row:

• unbounded preceding：表示起始行
• current row：表示当前行

查询每个雇员信息，并在雇员后展示该雇员所在部门的薪资汇总：汇总该雇员前面1位雇员 (如果存在）+ 自己 + 自己后面1位雇员(如果存在)

select 
	e.*, 
	sum(sal) over(
		partition by deptno 
		order by deptno,empno 
		rows between 1 preceding and 1 following) -- 累加时：从自己的前一行开始，到自己的后一行
from emp e 
order by deptno,empno

常用窗口函数

1）聚合函数

max：最大值。

min：最小值。

sum：求和。

avg：平均值。

count：计数。

2）跨行取值函数

lead(col, n, default_val)：获取col字段的第后n行数据，如果没用返回default_val

lag：获取col字段的第前n行数据，如果没用返回default_val

lag和lead函数不支持自定义窗口。

first_value：获取窗口内某一列的第一个值

last_value：获取窗口内某一列的最后一个值

3）排名函数

rank、dense_rank、row_number

rank 、dense_rank、row_number不支持自定义窗口。

4）标记行数

row_number()：标记窗口中数据的行号

搭配其他函数使用

NTILE(n)：把有序窗口的行分发到指定数量的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。n必须为int类型

示例：

获取同一个部门中，上一个雇员的薪资，如果该雇员为第一个，则返回0

select 
	e.*,
	-- 使用lag获取前1个雇员的薪资，lag(要获取的字段， 往前第几个, 如果不存在则返回的默认值/字段) 
	-- 默认值也可以不设置，如果不设置则不存在时返回值为null
	-- 默认值如果设置的是个字段，则取的是当前行的字段（因为前面那行不存在，所以是直接本身的字段）
	lag(sal, 1, 0) over (partition by deptno order by empno)
from emp e
order by deptno, empno;

将雇员按照雇佣时间分为5组：

select 
	e.*,
	ntile(5) over(order by hiredate)  -- 按照雇佣时间排序，均分为5组（如果不能均分，最后一组的数量会少一些）
from emp e
order by hiredate

窗口内排序

搭配over()使用的排序的相关函数：

• rank()：排序时如果数值相同会重复，总数不变

• DENSE_RANK()：排序时如果数值相同会重复，总数会减少

• ROW_NUMBER()：会根据顺序计算（即最后显示的是行号）

示例：
将每个部门的雇员按薪资从高到低排序：

select 
	e.*,
	rank() over(partition by deptno order by sal desc),  -- 使用 rank() 排序
	dense_rank() over(partition by deptno order by sal desc), -- 使用 dense_rank() 排序
	row_number() over(partition by deptno order by sal desc) -- 使用row_number()排序
from emp e
order by deptno, sal desc

其中deptNo=30的雇员薪资有重复的，三种不同的排序方式的结果为：

SAL	rank	dense_rank	row_number
2850	1	1	1
1600	2	2	2
1500	3	3	3
1250	4	4	4
1250	4	4	5
950	6	5	6

数据中存在两条SAL=1250的数据，rank和dense_rank对这两条数据的排序结果都是4。但是对于下一条数据（SAL=950），rank的排序号是在两个1250后的6，而dense_rank的排序号是连续的5。