【Apache Hive】-EW帮帮网

一、Hive简介

官网：https://hive.apache.org

1、Hive是什么？

Apache Hive 是一款建立在Hadoop之上的开源数据仓库系统，可以将存储在Hadoop文件中的结构化、半结构化数据文件映射为一张数据库表，基于表提供了一种类似SQL的查询模型，称为Hive查询语言（HQL），用于访问和分析存储在Hadoop文件中的大型数据集。
Hive 利用HDFS存储数据，利用MapReduce查询分析数据。
Hive 核心是将HQL转换为MapReduce程序，然后将程序提交到Hadoop集群执行。
Hive 的最大的魅力在于用户专注于编写HQL，Hive帮您转换成为MapReduce程序完成对数据的分析。
Hive 适用于离线批处理。
Hive 由Facebook实现并开源。

2、核心特性

类 SQL 接口（HiveQL）：降低 Hadoop 使用门槛，允许熟悉 SQL 的用户直接编写查询。
多种执行引擎：默认使用 MapReduce，但支持更高效的 Tez 和 Spark 以提高性能。
元数据管理：通过 Metastore（通常使用 MySQL、PostgreSQL 等关系数据库）存储表结构、分区等元数据。
数据存储优化：
- 分区：按目录划分数据（如按日期），加速查询。
- 分桶：按哈希值分文件存储，提升采样和连接效率。
- 存储格式：支持列式存储（ORC、Parquet），压缩率高，查询更快。
表类型：
- 内部表（Managed）：数据由 Hive 管理，删除表时数据一并删除。
- 外部表（External）：数据存于 HDFS，删除表仅移除元数据。
- 扩展性：支持用户自定义函数（UDF）、SerDe（序列化/反序列化）处理复杂数据格式。

3、架构组件

在这里插入图片描述

3.1 用户接口

客户端接口： CLI、JDBC/ODBC、WebGUI。其中，CLI(command line interface)为shell命令行；Hive中的Thrift服务器允许外部客户端通过网络与Hive进行交互，类似于JDBC或ODBC协议。WebGUI是通过浏览器访问Hive。

Hive提供了 Hive shell、ThriftServer等服务进程向用户提供操作接口

3.2 元数据存储

Metastore：集中管理元数据，支持多用户协作。

通常是存储在关系数据库如 mysql/derby中。Hive 中的元数据包括表的名字、表的列和分区及其属性、表的属性（是否为外部表等）、表的数据所在目录等。

3.3 Driver 驱动程序

包括语法解析器、计划编译器、优化器、执行器

完成 HQL 査询语句从词法分析、语法分析、编译、优化以及査询计划的生成。生成的查询计划存储在 HDFS 中，并在随后有执行引擎调用执行。这部分内容不是具体的服务进程，而是封装在Hive所依赖的Jar文件即Java代码中

3.4 执行引擎

执行引擎：将逻辑计划转为物理任务（如 MapReduce DAG）。

Hive本身并不直接处理数据文件。而是通过执行引擎处理。当下Hive支持MapReduce、Tez、Spark3种执行引擎。

4、Hive Metadata

元数据（Metadata），又称中介数据、中继数据，为描述数据的数据（data about data），主要是描述数据属性（property）的信息，用来支持如指示存储位置、历史数据、资源查找、文件记录等功能。

Hive Metadata 即 Hive 的元数据。包含用 Hive 创建的 database、table、表的位置、类型、属性，字段顺序类型等元信息。元数据存储在关系型数据库中。如 hive 内置的 Derby、或者第三方如 MySQL 等。

5、Hive Metastore

Metastore即元数据服务。Metastore服务的作用是管理metadata元数据，对外暴露服务地址，让各种客户端通过连接metastore服务，由metastore再去连接MySQL数据库来存取元数据。

有了metastore服务，就可以有多个客户端同时连接，而且这些客户端不需要知道MySQL数据库的用户名和密码，只需要连接metastore 服务即可。某种程度上也保证了hive元数据的安全。

Metastore 配置模式：

服务配置模式	内嵌模式	本地模式	远程模式
Metastore单独配置、启动	否	否	是
Metadata存储介质	Derby	MySQL	MySQL

5.1 内嵌模式

内嵌模式（Embedded Metastore）是 metastore 默认部署模式。此种模式下，元数据存储在内置的 Derby 数据库，并且 Derby 数据库和 metastore 服务都嵌入在主 HiveServer 进程中，当启动 HiveServer 进程时，Derby 和 metastore 都会启动。不需要额外起 Metastore 服务。但是一次只能支持一个活动用户，适用于测试体验，不适用于生产环境。
在这里插入图片描述

5.2 本地模式

本地模式（Local Metastore）下，Metastore 服务与主 HiveServer 进程在同一进程中运行，但是存储元数据的数据库在单独的进程中运行，并且可以在单独的主机上。metastore 服务将通过 JDBC 与 metastore 数据库进行通信。

本地模式采用外部数据库来存储元数据，推荐使用 MySQL。hive 根据 hive.metastore.uris 参数值来判断，如果为空，则为本地模式。

缺点是：每启动一次 hive 服务，都内置启动了一个 metastore。

在这里插入图片描述

5.3 远程模式

远程模式（Remote Metastore）下，Metastore 服务在其自己的单独 JVM 上运行，而不在 HiveServer 的 JVM 中运行。如果其他进程希望与 Metastore 服务器通信，则可以使用 Thrift Network API 进行通信。

远程模式下，需要配置 hive.metastore.uris 参数来指定 metastore 服务运行的机器 ip 和端口，并且需要单独手动启动 metastore 服务。元数据也采用外部数据库来存储元数据，推荐使用 MySQL。

在生产环境中，建议用远程模式来配置 Hive Metastore。在这种情况下，其他依赖 hive 的软件都可以通过 Metastore 访问 hive。由于还可以完全屏蔽数据库层，因此这也带来了更好的可管理性/安全性。

在这里插入图片描述

二、部署Hive

1、环境准备

安装Hadoop：

【】

修改Hadoop配置文件 core-site.xml

<!-- 指定 hadoop用户可以代理所有主机的用户 -->
<property>
    <name>hadoop.proxyuser.hadoop.hosts</name>
    <value>*</value>
</property>
<!-- 指定 hadoop用户可以代理所有用户组的用户 -->
<property>
    <name>hadoop.proxyuser.hadoop.groups</name>
    <value>*</value>
</property>

安装MySQL
- 【Linux】MySQL部署
- 下载 com.mysql / mysql-connector-j
  - MySQL Connector/J (Archived Versions)
  - 阿里云Maven仓库

2、安装Hive

# 下载
wget https://dlcdn.apache.org/hive/hive-4.0.1/apache-hive-4.0.1-bin.tar.gz

# 解压
tar -zxvf apache-hive-4.0.1-bin.tar.gz -C /app

# 将mysql驱动挪进lib目录
mv mysql-connector-j-8.4.0.jar /app/apache-hive-4.0.1-bin/lib/

# 登陆mysql，创建hive数据库
mysql -uroot -p
mysql> create database hive charset utf8;
mysl> quit;

# 进入hive目录
cd /app/apache-hive-4.0.1-bin

# 修改配置文件
tee conf/hive-env.sh << 'EOF'
export HADOOP_HOME=/app/hadoop-3.4.0
export HIVE_CONF_DIR=/app/apache-hive-4.0.1-bin/conf
export HIVE_AUX_JARS_PATH=/app/apache-hive-4.0.1-bin/lib
EOF
tee conf/hive-site.xml << 'EOF'
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
    <!-- 指定 Hive 元数据存储的 JDBC 连接 URL。这里使用的是 MySQL 数据库 -->
    <property>
        <name>javax.jdo.option.ConnectionURL</name>
        <value>jdbc:mysql://rl-node1:3306/hive?createDatabaseIfNotExist=true&amp;useSSL=false&amp;allowPublicKeyRetrieval=true&amp;useUnicode=true&amp;characterEncoding=UTF-8</value>
    </property>
 	<!-- 指定 JDBC 连接的驱动类名。 低版本驱动类名：com.mysql.jdbc.Driver -->
    <property>
        <name>javax.jdo.option.ConnectionDriverName</name>
        <value>com.mysql.cj.jdbc.Driver</value>
    </property>
    <!-- 指定连接数据库的用户名。 -->
    <property>
        <name>javax.jdo.option.ConnectionUserName</name>
        <value>test</value>
    </property>
    <!-- 指定连接数据库的密码 -->
    <property>
        <name>javax.jdo.option.ConnectionPassword</name>
        <value>+Test123</value>
    </property>
    <!-- 指定 HiveServer2 Thrift 服务的绑定主机名。这是 HiveServer2 服务监听的地址。 -->
    <property>
        <name>hive.server2.thrift.bind.host</name>
        <value>rl-node1</value>
    </property>
    <!-- 指定 Hive Metastore 服务的 URI。这是客户端连接到 Metastore 服务时使用的地址和端口。 -->
    <property>
        <name>hive.metastore.uris</name>
        <value>thrift://rl-node1:9083</value>
    </property>
    <!-- 禁用元数据存储的事件通知 API 的授权。这通常用于测试环境或当你不希望使用安全认证时。  -->
    <property>
        <name>hive.metastore.event.db.notification.api.auth</name>
        <value>false</value>
    </property>
    <!-- 禁用元数据存储模式（schema）的验证。在升级 Hive 或更改元数据存储模式时，可能需要暂时禁用此验证。但在生产环境中，建议启用它以确保模式的一致性。 -->
    <property>
        <name>hive.metastore.schema.verification</name>
        <value>false</value>
    </property>
</configuration>
EOF

# 移除SLF4J依赖，避免与Hadoop重复导入依赖致使依赖冲突
mv lib/log4j-slf4j-impl-2.18.0.jar lib/log4j-slf4j-impl-2.18.0.jar.bak

# hive数据库初始化
sh bin/schematool -initSchema -dbType mysql -verbos

# 创建日志目录
mkdir logs

# 启动hive元数据管理服务，注意要先启动hadoop、mysql
nohup bin/hive --service metastore >> logs/metastore.log 2>1 &

3、Hive 脚本

3.1 bin目录下的脚本文件

beeline：这是一个基于 JDBC 的 Hive 客户端，用于连接和执行 Hive 查询。它提供了比传统 Hive CLI 更丰富的功能和更好的性能。
hive：这是 Hive 的命令行接口（CLI）的启动脚本。它允许用户直接在命令行中运行 HiveQL 命令。然而，请注意，随着 Hive 的发展，Hive CLI 已经被认为是不推荐使用的，并且可能会在未来的版本中被移除。目前，推荐使用 Beeline 替代 Hive CLI。
hiveserver2：这是启动 HiveServer2 服务的脚本。HiveServer2 是一个基于 Thrift 的服务器，它允许远程客户端通过 JDBC 或 ODBC 连接到 Hive 并执行查询。HiveServer2 提供了比 HiveServer（Hive 1.x 中的版本）更好的性能和安全性。
init-hive-dfs.sh：这个脚本用于初始化 Hive 在 HDFS 上的目录结构。在首次使用 Hive 之前，您需要运行此脚本来创建必要的目录和文件。这些目录和文件用于存储 Hive 的元数据、临时文件等。
replstats.sh：这个脚本与 Hive 的复制（Replication）功能相关。它可能用于查看或管理复制过程中的统计信息。然而，具体的用途可能会根据 Hive 的版本和配置而有所不同。
hive-config.sh：这个脚本可能用于设置 Hive 的环境变量和配置。它可能被其他脚本（如 hive 或 hiveserver2）调用，以确保 Hive 在正确的环境中运行。然而，具体的用途和内容可能会根据 Hive 的版本和安装方式而有所不同。
hplsql：HPL/SQL 是一个用于 Hive 的 PL/SQL 兼容层，允许用户使用类似 PL/SQL 的语法来编写 HiveQL 查询。然而，这个脚本可能是一个链接到 HPL/SQL 工具的启动脚本，或者是 HPL/SQL 发行版中与 Hive 集成的一部分。请注意，HPL/SQL 可能不是 Apache Hive 官方项目的一部分，而是一个第三方工具。
metatool：这个脚本用于管理 Hive 的元数据。它可以用于执行与 Hive 元数据库相关的各种任务，如检查元数据库的完整性、升级元数据库架构等。
schematool：这个脚本用于管理 Hive 的数据库架构。它可以用于创建、升级或验证 Hive 的数据库架构，确保它与当前安装的 Hive 版本兼容。
ext：包含一些额外的工具或脚本，这些工具或脚本是 Hive 发行版的一部分，但不是核心功能所必需的。

3.2 HiveServer、HiveServer2 服务

HiveServer、HiveServer2 都是 Hive 自带的两种服务，允许客户端在不启动 CLI（命令行）的情况下对 Hive 中的数据进行操作，且两个都允许远程客户端使用多种编程语言如 java，python 等向 hive 提交请求，取回结果。

但是，HiveServer 不能处理多于一个客户端的并发请求。因此在 Hive-0.11.0 版本中重写了 HiveServer 代码得到了 HiveServer2，进而解决了该问题。HiveServer 已经被废弃。HiveServer2 支持多客户端的并发和身份认证，旨在为开放 API 客户端如 JDBC、ODBC 提供更好的支持。

HiveServer2 通过 Metastore 服务读写元数据。所以在远程模式下，启动 HiveServer2 之前必须先首先启动 metastore 服务。

远程模式下，Beeline 客户端只能通过 HiveServer2 服务访问 Hive。而 bin/hive 是通过 Metastore 服务访问的。具体关系如下：

在这里插入图片描述

启动hiverserver2服务（两种方式）:

nohup bin/hiveserver2 >> logs/hiveserver2.log 2>1 &

nohup bin/hive --service hiveserver2 >> logs/hiveserver2.log 2>1 &

可能存在的问题：使用jps 或者ps 可以查到 hiveserver2 服务进程，但是用ss或者netstat查不到监听信息，排错步骤：

# 打开hive日志记录
sed 's|property\.hive\.log\.dir *= *.*|property.hive.log.dir = /app/apache-hive-4.0.1-bin/logs|' conf/hive-log4j2.properties.template > conf/hive-log4j2.properties

# 关闭hiverserver2重启
ps -ef | grep hiveserver2 | grep -v grep | awk '{print $2}' | xargs kill -9
nohup bin/hiveserver2 >> logs/hiveserver2.log 2>1 &

# 查看日志
less logs/hive.log

报错信息如下：

Caused by: org.apache.hadoop.ipc.RemoteException: Permission denied: user=appuser, access=WRITE, inode="/":hadoop:supergroup:drwxr-xr-x

这个错误信息表明在尝试写入 HDFS（Hadoop Distributed File System）的根目录 / 时，用户 appuser 没有足够的权限。

为了解决这个问题，您可以考虑以下几个选项：

更改 HDFS 目录的权限

通常不推荐更改 HDFS 根目录的权限，因为这可能会影响整个集群的安全性。但是，如果您确实需要 appuser 在特定目录下写入文件，可以考虑为该目录设置更宽松的权限（请注意，这可能会带来安全风险）。

# 创建仓库目录
hadoop fs -mkdir -p /user/hive/warehouse

# 设置目录权限（允许 appuser 读写）
hadoop fs -chmod -R 777 /user/hive/warehouse   # 简化测试权限
hadoop fs -chown -R appuser:supergroup /user/hive/warehouse  # 设置所有者和组

# 验证目录权限
hadoop fs -ls /user/hive

使用具有适当权限的用户运行 HiveServer2
- 如果可能，使用 hadoop 用户或属于 supergroup 组的用户来启动 HiveServer2。这通常不是最佳实践，因为它可能绕过正常的权限检查。更好的做法是为 HiveServer2 配置一个具有所需最小权限的专用服务账户。
```
chown -R hadoop:hadoop /app/apache-hive-4.0.1
su - hadoop
```
更改 HiveServer2 的配置以使用不同的 HDFS 目录
- 修改 HiveServer2 的配置，使其使用非根目录的 HDFS 目录作为工作目录或日志目录。这是推荐的做法，因为它既保持了安全性，又避免了权限问题。
- 修改 hive-site.xml
```
<property>
  <name>hive.metastore.warehouse.dir</name>
  <value>/custom/hive/warehouse</value>  
</property>
```
  并确保该路径在 HDFS 中存在且权限正确。
使用 HDFS 的 ACL（访问控制列表）
- 如果您的 Hadoop 版本支持 ACL，可以为特定用户或组设置更细粒度的权限。
检查 Hadoop 的用户模拟配置
- HiveServer2 通常配置为模拟提交查询的用户。确保 Hadoop 的用户模拟配置正确无误，并且 appuser 有权在所需的 HDFS 路径上执行操作。

3.3 bin/hive、bin/beeline

Hive 发展至今，总共历经了两代客户端工具。

第一代客户端（deprecated 不推荐使用）：$HIVE_HOME/bin/hive，是一个 shell Util。主要功能：
- 用于以交互或批处理模式运行 Hive 查询；
- 用于 Hive 相关服务的启动，比如 metastore 服务。
第二代客户端（recommended 推荐使用）：$HIVE_HOME/bin/beeline，是一个 JDBC 客户端，是官方强烈推荐使用的 Hive 命令行工具，和第一代客户端相比，性能加强安全性提高。

Beeline 在嵌入式模式和远程模式下均可工作。在嵌入式模式下，它运行嵌入式 Hive(类似于Hive Client)；而远程模式下 beeline 通过 Thrift 连接到单独的 HiveServer2 服务上，这也是官方推荐在生产环境中使用的模式。

3.4 bin/hive 客户端使用

在 hive 安装包的 bin 目录下，有 hive 提供的第一代客户端 bin/hive。该客户端可以访问 hive的 metastore 服务，从而达到操作 hive 的目的。

如果您是远程模式部署，请手动启动运行 metastore 服务。如果是内嵌模式和本地模式，直接运行 bin/hive，metastore 服务会内嵌一起启动。可以直接在启动 Hive metastore 服务的机器上使用 bin/hive 客户端操作，此时不需要进行任何配置。

# 去掉hive脚本中切换BEELINE命令
sed -i "s|USE_BEELINE_FOR_HIVE_CLI="true"||" bin/hive

# 登陆客户端
bin/hive

# hive 有一个默认数据库default
hive> show databases;
hive> desc database default;

# 注意此时查看hadoop-hdfs，会发现default数据库对应的目录/user/hive/warehouse不存在
hadoop fs -ls /

# 创建数据库
hive> create database my_hive;

# 当你新建一个数据库后，hdfs上才会对应创建出目录
hadoop fs -ls /

如果需要在其他机器上通过 bin/hive 访问 hive metastore 服务，只需要把当前机器上的 hive 安装目录 scp 发送到其他机器上，并在该机器的 hive-site.xml 配置中添加 metastore 服务地址即可。

[root@rl-node1 ~]# scp -r /app/apache-hive-4.0.1-bin rl-node2:$PWD
[root@rl-node1 ~]# nohup /app/apache-hive-4.0.1/bin/hive --service metastore &
[root@rl-node1 ~]# ssh rl-node2
[root@hadoop02 ~]# /app/apache-hive-4.0.1/bin/hive

3.5 bin/beeline 客户端使用

hive 经过发展，推出了第二代客户端 beeline，但是 beeline 客户端不是直接访问 metastore 服务的，而是需要单独启动 hiveserver2 服务。在 hive 安装的服务器上，首先启动 metastore 服务，然后启动 hiveserver2 服务。

# 先启动 metastore 服务
[hadoop@rl-node1 apache-hive-4.0.1-bin]$ nohup bin/hive --service metastore >> logs/metastore.log 2>1 &

# 启动hiveserver2，HiveServer2 的默认配置（包括端口 10000）是直接内置于 Hive 的核心代码中的，可通过配置 hive.server2.thrift.port 修改
[hadoop@rl-node1 apache-hive-4.0.1-bin]$ nohup bin/hive --service hiveserver2 >> logs/hiveserver2.log 2>1 &

# 登陆客户端
[hadoop@rl-node1 apache-hive-4.0.1-bin]$ bin/beeline 
beeline> !connect jdbc:hive2://rl-node1:10000
Connecting to jdbc:hive2://rl-node1:10000
Enter username for jdbc:hive2://hadoop01:10000: hadoop
Enter password for jdbc:hive2://hadoop01:10000: (这里直接回车)

# 查询数据库
0: jdbc:hive2://rl-node1:10000> show databases;

登陆客户端时报错：

25/03/02 11:48:39 [main]: WARN jdbc.HiveConnection: Failed to connect to rl-node1:10000
Error: Could not open client transport with JDBC Uri: jdbc:hive2://rl-node1:10000: Failed to open new session: java.lang.RuntimeException: org.apache.hadoop.ipc.RemoteException(org.apache.hadoop.security.authorize.AuthorizationException): User: hadoop is not allowed to impersonate hadoop (state=08S01,code=0)

注意前边在 hadoop 的 core-site.xml 里边配置完hadoop.proxyuser.hadoop.hosts、group 是不是没有重启HDFS。

三、Hive SQL语言

1、Hive 数据库操作

在Hive中，默认的数据库叫做default，存储数据位置位于HDFS的/user/hive/warehouse下。

用户自己创建的数据库存储位置是/user/hive/warehouse/database_name.db下。

创建数据库
```
create database if not exists study_test
    comment "这是一个学习的测试数据库"
    with dbproperties ('createBy' = 'root');
```
- COMMENT：数据库的注释说明语句
- LOCATION：指定数据库在HDFS存储位置，默认 /user/hive/warehouse/dbname.db
- WITH DBPROPERTIES：用于指定一些数据库的属性配置
删除数据库
```
drop database study_test;
```
- 默认行为是RESTRICT，这意味着仅在数据库为空时才删除它。
- 要删除带有表的数据库（不为空的数据库），我们可以使用CASCADE。
切换数据库
```
use study_test;
```
切换当前会话使用 study_test 数据库进行操作。
查询当前数据库
```
select current_database();
```

数据库修改Location

alter database my_hive set location hdfs_path;

2、Hive 创建表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [database_name.]table_name 
(
  column_name data_type [COMMENT 'column_comment'],
  ...
)
[COMMENT 'table_comment']
[PARTITIONED BY (partition_column data_type, ...)]
[CLUSTERED BY (column_name, ...) [SORTED BY (column_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION 'hdfs_path']
[TBLPROPERTIES (property_name=property_value, ...)];

2.1 关键参数详解

EXTERNAL

内部表（默认）：数据由 Hive 管理，删除表时会同时删除元数据和 HDFS 数据。

外部表（EXTERNAL）：仅管理元数据，删除表时不删除 HDFS 数据。

CREATE EXTERNAL TABLE external_table (id INT) row format delimited fields terminated by '\t' location '\tmp\test';

内部表与外部表转换

alter table t1 set tblproperties('EXTERNAL'=true);
alter table t1 set tblproperties('EXTERNAL'=false);

PARTITIONED BY
- 分区表：根据指定列（如日期、地区）将数据分目录存储，提高查询效率。
```
CREATE TABLE logs (
  log_time STRING,
  message STRING
)
PARTITIONED BY (dt STRING);
```

CLUSTERED BY 和 SORTED BY

分桶表：将数据按哈希分桶存储，优化 JOIN 和采样。

CREATE TABLE user_bucketed (
  user_id INT,
  name STRING
)
CLUSTERED BY (user_id) INTO 4 BUCKETS;

ROW FORMAT
- 定义数据行的格式：
  - DELIMITED：文本文件分隔符（如 CSV）。
  - SERDE：自定义序列化类（如处理 JSON）。
```
CREATE TABLE csv_table (
  id INT,
  name STRING
)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',';
```
STORED AS
- 指定存储格式：
  - TEXTFILE（默认）: 文本文件。
  - PARQUET: 列式存储，适合分析。
  - ORC: 高效压缩列式存储。
  - AVRO: 支持 Schema 演化。
```
CREATE TABLE orc_table (
  id INT,
  name STRING
)
STORED AS ORC;
```
LOCATION
- 指定表数据在 HDFS 上的存储路径（默认使用 hive.metastore.warehouse.dir 配置）。
```
CREATE TABLE custom_location_table (id INT)
LOCATION '/user/hive/custom_path';
```

TBLPROPERTIES

添加表级元数据属性：

CREATE TABLE props_table (id INT)
TBLPROPERTIES ('creator'='hadoop', 'project'='demo');

2.2 完整示例

创建分区外部表（存储为 Parquet）

CREATE EXTERNAL TABLE sales (
  product_id STRING,
  amount DOUBLE
)
PARTITIONED BY (sale_date STRING)
STORED AS PARQUET
LOCATION '/user/hive/sales_data'
TBLPROPERTIES ('parquet.compression'='SNAPPY');

创建分桶表（带排序）

CREATE TABLE user_activity (
  user_id INT,
  action STRING,
  timestamp BIGINT
)
CLUSTERED BY (user_id) SORTED BY (timestamp DESC) INTO 8 BUCKETS
STORED AS ORC;

从查询结果创建表（CTAS）

CREATE TABLE new_table AS 
SELECT * FROM existing_table WHERE condition;

复制表结构

CREATE TABLE cloned_table LIKE existing_table;

2.3 验证表创建

查看表结构：
```
DESCRIBE FORMATTED table_name;
```

检查 HDFS 路径：

hadoop fs -ls /user/hive/warehouse/database_name.db/table_name

2.4 分桶、分库、分表、分区

维度	分区	分桶	分库	分表
层级	物理划分（目录级）	物理划分（文件级）	逻辑隔离（数据库级）	逻辑或物理划分（表级）
核心目的	按列值剪裁数据	均匀分布数据，优化 JOIN	逻辑隔离表	拆分大表
优化目标	查询效率（分区剪枝）	JOIN性能、数据采样	数据管理和权限隔离	单表数据量过大问题
实现方式	按分区键分目录存储	哈希分桶+排序	创建不同数据库	按规则拆分到多个表
自动管理	支持动态分区	需配置哈希规则	完全手动	完全手动
适用场景	时间、地域等维度查询	大表 JOIN、高效采样	多业务线数据隔离	历史数据归档、分片存储
数据冗余	分区列值重复存储	无冗余	无冗余	无冗余
查询优化	分区剪裁减少扫描量	Map-Side JOIN、高效采样	无直接优化	需手动联合查询

3、修改表

常用操作速查表

操作	命令
重命名表	ALTER TABLE old_name RENAME TO new_name
添加列	ALTER TABLE table ADD COLUMNS (col_name data_type)
修改列名/类型	ALTER TABLE table CHANGE COLUMN old_col new_col new_type
添加分区	ALTER TABLE table ADD PARTITION (dt=‘2023-10-01’)
删除分区	ALTER TABLE table DROP PARTITION (dt=‘2023-10-01’)
修改分区	ALTER TABLE table PARTITION (dt=‘2023-10-01’) RENAME TO PARTITION (dt=‘2023-10-02’)
修改表存储格式	ALTER TABLE table SET FILEFORMAT ORC
修改表注释	ALTER TABLE table SET TBLPROPERTIES (‘comment’=‘new comment’)

3、数据导入（LOAD）

Hive 中 LOAD DATA 命令用于将数据文件从本地文件系统（Local FS）或 HDFS 加载到 Hive 表中。

3.1 基本语法

LOAD DATA [LOCAL] INPATH 'source_path' 
[OVERWRITE] INTO TABLE table_name 
[PARTITION (partition_column = value, ...)];

关键参数说明：

LOCAL
- 指定数据源路径为本地文件系统（如 /home/user/data.csv）。
- 若省略 LOCAL，则路径指向 HDFS（如 /user/hive/input/data.csv）。
OVERWRITE
- 覆盖目标表中的现有数据。
- 若省略，则数据会追加到表中（不删除旧数据）。
PARTITION
- 将数据加载到分区表的指定分区（需提前创建分区目录）。

3.2 使用场景与示例

加载本地文件到 Hive 表

-- 从本地加载（追加数据）
LOAD DATA LOCAL INPATH '/home/hadoop/data/employee.csv' 
INTO TABLE employee;

-- 从本地加载（覆盖旧数据）
LOAD DATA LOCAL INPATH '/home/hadoop/data/employee_new.csv' 
OVERWRITE INTO TABLE employee;

加载 HDFS 文件到 Hive 表

-- 从 HDFS 加载（追加数据）
LOAD DATA INPATH '/user/hive/input/employee.csv' 
INTO TABLE employee;

-- 从 HDFS 加载（覆盖旧数据）
LOAD DATA INPATH '/user/hive/input/employee_new.csv' 
OVERWRITE INTO TABLE employee;

加载数据到分区表

-- 加载到静态分区（需手动指定分区值）
LOAD DATA LOCAL INPATH '/home/hadoop/data/sales_2023.csv' 
INTO TABLE sales 
PARTITION (year=2023, month='2023-01');

3.3 注意事项

数据文件与表结构的匹配
- 字段顺序与数量：数据文件的列必须与表定义的列顺序和数量一致。
- 数据格式：确保文件格式（如 CSV、TSV）与表的 ROW FORMAT 配置匹配（如分隔符为逗号或制表符）。
文件路径规则
- 本地文件系统：使用 LOCAL 时，路径需是 Hive 客户端所在机器的本地路径。
- HDFS：不使用 LOCAL 时，路径必须是 HDFS 绝对路径（如 /user/hive/input/data.csv）。
数据移动行为
- HDFS 文件加载：数据文件会被移动到 Hive 表的 HDFS 存储路径（如 /user/hive/warehouse/employee），原路径文件会消失。
- 本地文件加载：文件会被复制到 Hive 表的 HDFS 存储路径，本地原文件保留。
外部表与内部表
- 内部表（Managed Table）：数据由 Hive 管理，删除表时数据会被删除。
- 外部表（External Table）：数据路径由用户指定，删除表时仅删除元数据，保留 HDFS 数据。

3.4 替代方案：直接操作 HDFS

如果数据已在 HDFS 中，可以直接将文件放入表的存储路径（需格式匹配）：

hadoop fs -put /local/data.csv /user/hive/warehouse/employee

4、数据导入（INSERT）

在 Hive 中，使用 INSERT 语句可以将一个表中的数据导入到另一个表中，支持全量复制、条件筛选、数据转换等操作。

4.1 全量复制（覆盖目标表数据）

INSERT OVERWRITE TABLE target_table 
SELECT * FROM source_table;

4.2 全量复制（追加到目标表）

INSERT INTO TABLE target_table 
SELECT * FROM source_table;

4.3 选择性复制（带条件或列映射）

INSERT OVERWRITE TABLE target_table 
SELECT col1, col2 
FROM source_table 
WHERE condition;

4.4 动态分区插入（分区表专用）

INSERT OVERWRITE TABLE partitioned_table 
PARTITION (partition_col) 
SELECT col1, col2, partition_col 
FROM source_table;

5、数据导出（INSERT）

在 Hive 中，使用 SELECT 语句结合数据导出操作可以将查询结果保存到本地文件系统或 HDFS 中，支持多种格式和导出方式。

5.1 导出到 HDFS 目录

-- 覆盖导出（目录原有内容会被删除）
INSERT OVERWRITE DIRECTORY '/user/hive/output/result'
SELECT * FROM table_name WHERE condition;

-- 追加导出（保留原有内容，新增结果）
INSERT INTO DIRECTORY '/user/hive/output/result'
SELECT * FROM table_name WHERE condition;

5.2 导出到本地文件系统

-- 覆盖导出到本地（需添加 LOCAL 关键字）
INSERT OVERWRITE LOCAL DIRECTORY '/home/hadoop/result'
row format delimited fields terminated by '\t' 
SELECT * FROM table_name WHERE condition;

-- 追加导出到本地
INSERT INTO LOCAL DIRECTORY '/home/hadoop/result'
SELECT * FROM table_name WHERE condition;

6、数据导出（hive）

bin/hive -e "select * from my_hive.test;" > data/data1.txt

bin/hive -f export.sql > data/data2.txt

四、hive案例：将结构化文件映射到hive中

结构化txt文件

95001	李勇	男	20	CS
95002	刘晨	女	19	IS
95003	王敏	女	22	MA
95004	张立	男	19	IS

hive中建表

create table study_test.t_student
(
    stu_no int 		comment '学号',
    name   string	comment '姓名',
    gender string	comment '性别',
    age    int		comment '年龄',
    remark string	comment '备注'
) row format delimited
    fields terminated by "\t";	//表示以制表符作为分割数据列的标志

将文件上传到你新建表的目录下面

hadoop fs -put t_student.txt /user/hive/warehouse/study_test.db/t_student

查询数据
```
SELECT * FROM study_test.t_student;
```

五、Apache Hive DML语句与函数使用

1、Hive SQL DML语法之加载数据

在Hive中建表成功之后，就会在HDFS上创建一个与之对应的文件夹，且文件夹名字就是表名；
文件夹父路径是由参数hive.metastore.warehouse.dir控制，默认值是/user/hive/warehouse；
Hive官方推荐使用Load命令将数据加载到表中

新建student_local.txt填充数据

95001,李勇,男,20,CS
95002,刘晨,女,19,IS
95003,王敏,女,22,MA
95004,张立,男,19,IS
95005,刘刚,男,18,MA
95006,孙庆,男,23,CS
95007,易思玲,女,19,MA
95008,李娜,女,18,CS
95009,梦圆圆,女,18,MA
95010,孔小涛,男,19,CS
95011,包小柏,男,18,MA
95012,孙花,女,20,CS
95013,冯伟,男,21,CS
95014,王小丽,女,19,CS
95015,王君,男,18,MA
95016,钱国,男,21,MA
95017,王风娟,女,18,IS
95018,王一,女,19,IS
95019,邢小丽,女,19,IS
95020,赵钱,男,21,IS
95021,周二,男,17,MA
95022,郑明,男,20,MA

新建表

--step1:建表
--建表student_local 用于演示从本地加载数据
create table student_local
(
    num  int,
    name string,
    sex  string,
    age  int,
    dept string
) row format delimited fields terminated by ',';

load数据

LOAD DATA LOCAL INPATH 'D:/hadoop/student_local.txt' INTO TABLE student_local;

2、Hive SQL-DML-Insert插入数据

最常用的配合是把查询返回的结果插入到另一张表中。

insert+select表示：将后面查询返回的结果作为内容插入到指定表中。

需要保证查询结果列的数目和需要插入数据表格的列数目一致。
如果查询出来的数据类型和插入表格对应的列数据类型不一致，将会进行转换，但是不能保证转换一定成功，转换失败的数据将会为NULL。

--创建一张目标表 只有两个字段
create table student_from_insert
(
    sno   int,
    sname string
);
--使用insert+select插入数据到新表中
insert into table student_from_insert select num, name from student_local;

select * from student_from_insert;

3、Hive SQL DML语法之查询数据

distinct去重

--返回所有匹配的行 去除重复的结果
select distinct state from t_usa_covid19;

其他的用法都和mysql很像，大致都是一样的方式。

4、Hive 常用函数入门

Hive的函数分为两大类：内置函数（Built-in Functions）、用户定义函数UDF（User-Defined Functions）：

内置函数可分为：数值类型函数、日期类型函数、字符串类型函数、集合函数、条件函数等；
用户定义函数根据输入输出的行数可分为3类：UDF、UDAF、UDTF。
- 根据函数输入输出的行数：
  - UDF（User-Defined-Function）普通函数，一进一出
  - UDAF（User-Defined Aggregation Function）聚合函数，多进一出
  - UDTF（User-Defined Table-Generating Functions）表生成函数，一进多出

5、Hive 常用的内置函数

函数库：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

（1）String Functions 字符串函数

字符串长度函数：length
字符串反转函数：reverse
字符串连接函数：concat
带分隔符字符串连接函数：concat_ws
字符串截取函数：substr,substring
------------String Functions 字符串函数------------
select length("dsaf");
select reverse("olklf");
select concat("angela","baby");
--带分隔符字符串连接函数：concat_ws(separator, [string | array(string)]+)
select concat_ws('.', 'www', array('gsdffsd', 'cn'));
--字符串截取函数：substr(str, pos[, len]) 或者 substring(str, pos[, len])
select substr("angelababy",-2); --pos是从1开始的索引，如果为负数则倒着数
select substr("angelababy",2,2);
--分割字符串函数: split(str, regex)
select split('apache hive', ' ');

（2）Date Functions 日期函数

----------- Date Functions 日期函数 -----------------
--获取当前日期: current_date
select current_date();
--获取当前UNIX时间戳函数: unix_timestamp
select unix_timestamp();
--日期转UNIX时间戳函数: unix_timestamp
select unix_timestamp("2011-12-07 13:01:03");
--指定格式日期转UNIX时间戳函数: unix_timestamp
select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss');
--UNIX时间戳转日期函数: from_unixtime
select from_unixtime(1618238391);
select from_unixtime(0, 'yyyy-MM-dd HH:mm:ss');
--日期比较函数: datediff 日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'
select datediff('2012-12-08','2012-05-09');
--日期增加函数: date_add
select date_add('2012-02-28',10);
--日期减少函数: date_sub
select date_sub('2012-01-1',10);

（3）Mathematical Functions 数学函数

----Mathematical Functions 数学函数-------------
--取整函数: round 返回double类型的整数值部分 （遵循四舍五入）
select round(3.1415926);
--指定精度取整函数: round(double a, int d) 返回指定精度d的double类型
select round(3.1415926,4);
--取随机数函数: rand 每次执行都不一样 返回一个0到1范围内的随机数
select rand();
--指定种子取随机数函数: rand(int seed) 得到一个稳定的随机数序列
select rand(3);

（4）Conditional Functions 条件函数

--if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
select if(1=2,100,200);
select if(sex ='男','M','W') from student limit 3;
--空值转换函数: nvl(T value, T default_value)
select nvl("allen","itcast");
select nvl(null,"itcast");
--条件转换函数: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end;
select case sex when '男' then 'male' else 'female' end from student limit 3;

六、Hive 应用场景

数据仓库：整合多源数据，执行复杂 ETL 流程。
批处理分析：日志分析、数据聚合、报表生成（如每日/周汇总）。
机器学习数据预处理：清洗、转换大规模训练数据。
交互式查询：结合 LLAP 或 Presto 实现近似实时响应。

七、Hive 优缺点

（1）优点

易用性强，SQL 语法降低分布式计算门槛。
扩展性好，支持 TB/PB 级数据。
灵活的数据模型与存储格式。

（2）局限性

延迟较高，不适合实时场景。
传统版本不支持事务，新版本支持有限 ACID。

八、与其他工具对比

Hive vs. HBase：Hive 为批处理设计；HBase 是 NoSQL 数据库，适合实时读写。
Hive vs. Impala/Presto：后者为交互式查询优化，延迟更低，但 Hive 稳定性更高。
Hive vs. Spark SQL：Spark SQL 支持内存计算，迭代任务更快，但 Hive 生态集成更成熟。

九、演进与生态整合

Hive on Spark：利用 Spark 引擎提升性能。
Hive 3.0+：支持 ACID 事务（INSERT/UPDATE/DELETE），增强实时能力。
与云平台集成：AWS EMR、Azure HDInsight 等均提供托管 Hive 服务。

十、总结

Apache Hive 是大数据生态中不可或缺的工具，尤其适合需要 SQL 兼容性和批处理能力的场景。尽管实时性不足，但其易用性、扩展性及与 Hadoop 生态的深度整合，使其在企业数据仓库和离线分析中持续发挥重要作用。

二十、资料

黑马程序员大数据入门到实战教程，大数据开发必会的Hadoop、Hive，云平台实战项目全套一网打尽
Apache Hive安装部署详细图文教程
Apache Hive详解

【Apache Hive】

一、Hive简介

1、Hive是什么？

2、核心特性

3、架构组件

3.1 用户接口

3.2 元数据存储

3.3 Driver 驱动程序

3.4 执行引擎

4、Hive Metadata

5、Hive Metastore

5.1 内嵌模式

5.2 本地模式

5.3 远程模式

二、部署Hive

1、环境准备

2、安装Hive

3、Hive 脚本

3.1 bin目录下的脚本文件

3.2 HiveServer、HiveServer2 服务

3.3 bin/hive、bin/beeline

3.4 bin/hive 客户端使用

3.5 bin/beeline 客户端使用

三、Hive SQL语言

1、Hive 数据库操作

2、Hive 创建表

2.1 关键参数详解

2.2 完整示例

2.3 验证表创建

2.4 分桶、分库、分表、分区

3、修改表

3、数据导入（LOAD）

3.1 基本语法

3.2 使用场景与示例

3.3 注意事项

3.4 替代方案：直接操作 HDFS

4、数据导入（INSERT）

4.1 全量复制（覆盖目标表数据）

4.2 全量复制（追加到目标表）

4.3 选择性复制（带条件或列映射）

4.4 动态分区插入（分区表专用）

5、数据导出（INSERT）

5.1 导出到 HDFS 目录

5.2 导出到本地文件系统

6、数据导出（hive）

四、hive案例：将结构化文件映射到hive中

五、Apache Hive DML语句与函数使用

1、Hive SQL DML语法之加载数据

2、Hive SQL-DML-Insert插入数据

3、Hive SQL DML语法之查询数据

4、Hive 常用函数入门

5、Hive 常用的内置函数

六、Hive 应用场景

七、Hive 优缺点

八、与其他工具对比

九、演进与生态整合

十、总结

二十、资料

网站公告

今日签到

热门文章

最新发布