Hadoop学习教程，从入门到精通， Hive数据仓库 — 完整知识点与案例代码(7）
·
Hive数据仓库 — 完整知识点与案例代码
一、数据仓库简介
1.1 什么是数据仓库
数据仓库（Data Warehouse）是一个面向主题的、集成的、相对稳定的、反映历史变化的数据集合，用于支持管理决策。
四大特征：
| 特征 | 说明 |
|---|---|
| 面向主题 | 围绕特定业务主题组织数据（如销售、客户、财务） |
| 集成性 | 将多个异构数据源统一整合 |
| 稳定性 | 数据加载后一般不修改、不删除 |
| 时变性 | 记录历史变化，支持趋势分析 |
1.2 数据仓库系统结构
数据源层 → 数据抽取/转换/加载(ETL) → 数据存储层 → 数据服务层 → 数据应用层
┌─────────────────────────────────────────────────┐
│ 数据应用层 │
│ (报表、BI工具、数据挖掘、即席查询) │
├─────────────────────────────────────────────────┤
│ 数据服务层 │
│ (OLAP、查询引擎、API) │
├─────────────────────────────────────────────────┤
│ 数据存储层 │
│ (数据仓库、数据集市、数据湖) │
├─────────────────────────────────────────────────┤
│ ETL处理层 │
│ (抽取Extract、转换Transform、加载Load) │
├─────────────────────────────────────────────────┤
│ 数据源层 │
│ (关系数据库、日志、文件、API、爬虫数据) │
└─────────────────────────────────────────────────┘
1.3 数据仓库分层
| 层次 | 英文名 | 说明 |
|---|---|---|
| ODS | Operational Data Store | 原始数据层，存放原始数据，保持数据原貌 |
| DWD | Data Warehouse Detail | 明细数据层，对ODS层数据进行清洗、规范化 |
| DWS | Data Warehouse Summary | 汇总数据层，按主题进行轻度汇总 |
| ADS | Application Data Store | 应用数据层，面向具体应用的结果数据 |
1.4 数据仓库模型
星型模型（Star Schema）：
维度表1
\
维度表2 --- 事实表 --- 维度表3
/
维度表4
雪花模型（Snowflake Schema）：
维度表 → 维度子表 → 事实表 ← 维度子表 ← 维度表
二、Hive简介
2.1 Hive与传统关系数据库的区别
| 比较项 | Hive | 传统关系数据库(RDBMS) |
|---|---|---|
| 数据规模 | PB级 | GB~TB级 |
| 查询语言 | HQL (类SQL) | SQL |
| 数据存储 | HDFS/S3等分布式存储 | 本地文件系统 |
| 执行引擎 | MapReduce/Tez/Spark | 自身引擎 |
| 数据写入 | 读多写少，批量加载 | 支持实时读写 |
| 延迟 | 高延迟（分钟~小时级） | 低延迟（毫秒~秒级） |
| 数据更新 | 不支持行级更新 | 支持行级更新和事务 |
| 索引 | 简单索引 | 复杂索引（B+树等） |
| Schema | 读时模式(Schema on Read) | 写时模式(Schema on Write) |
2.2 Hive系统架构
┌──────────────────────────────────────────┐
│ 用户接口层 │
│ (CLI命令行/JDBC/ODBC/Web UI) │
├──────────────────────────────────────────┤
│ 驱动层(Driver) │
│ ┌─────────┬──────────┬──────────┐ │
│ │ 解析器 │ 编译器 │ 优化器 │ │
│ │(Parser) │(Compiler) │(Optimizer)│ │
│ └─────────┴──────────┴──────────┘ │
├──────────────────────────────────────────┤
│ 元数据存储(Metastore) │
│ (MySQL/PostgreSQL/Derby) │
├──────────────────────────────────────────┤
│ 执行引擎 │
│ (MapReduce / Tez / Spark) │
├──────────────────────────────────────────┤
│ 分布式存储 │
│ (HDFS / S3) │
└──────────────────────────────────────────┘
2.3 Hive工作原理
用户输入HQL → 解析器(Parser)生成AST
→ 语义分析生成查询块 → 编译器(Compiler)生成逻辑执行计划
→ 优化器(Optimizer)优化计划 → 生成物理执行计划(MapReduce/Tez任务)
→ 执行引擎执行 → 返回结果
2.4 Hive数据存储模型
Database（数据库）
└── Table（表）
├── 内部表(Managed Table)
├── 外部表(External Table)
├── 分区表(Partitioned Table)
└── 分桶表(Bucketed Table)
存储路径： 数据文件存储在HDFS中，如：/user/hive/warehouse/数据库名.db/表名/
元数据： 存储在外部数据库（如MySQL）中，包括表结构、列信息、分区信息等。
三、Hive的部署
3.1 内嵌模式（Embedded Mode）
元数据存储在Hive自带的Derby数据库中，只允许一个用户连接，适用于测试。
配置文件 hive-site.xml：
<!-- 内嵌模式配置 -->
<configuration>
<!-- 指定元数据存储使用Derby数据库 -->
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:derby:;databaseName=metastore_db;create=true</value>
</property>
<!-- 指定JDBC驱动为Derby -->
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>org.apache.derby.jdbc.EmbeddedDriver</value>
</property>
</configuration>
启动方式：
# 直接启动Hive CLI，自动初始化Derby数据库
hive
3.2 本地模式（Local Mode）
元数据存储在本地MySQL数据库中，支持多用户访问（同一台机器上）。
配置文件 hive-site.xml：
<configuration>
<!-- 指定MySQL作为元数据存储数据库 -->
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost:3306/hive_metastore?createDatabaseIfNotExist=true&useSSL=false</value>
</property>
<!-- 指定MySQL JDBC驱动类名 -->
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.cj.jdbc.Driver</value>
</property>
<!-- MySQL用户名 -->
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
</property>
<!-- MySQL密码 -->
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>123456</value>
</property>
<!-- Hive元数据存储验证方式设为none -->
<property>
<name>hive.metastore.event.db.notification.api.auth</name>
<value>false</value>
</property>
</configuration>
初始化元数据库：
# 初始化MySQL中的Hive元数据库（只需执行一次）
schematool -dbType mysql -initSchema
3.3 远程模式（Remote Mode）
Metastore服务独立部署在一台机器上，其他机器通过Thrift协议远程访问。
服务端 hive-site.xml（Metastore Server所在机器）：
<configuration>
<!-- MySQL连接配置（同本地模式） -->
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://dbhost:3306/hive_metastore?createDatabaseIfNotExist=true</value>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.cj.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>123456</value>
</property>
<!-- 指定Metastore服务绑定的URI -->
<property>
<name>hive.metastore.uris</name>
<value>thrift://metastore-host:9083</value>
</property>
</configuration>
启动Metastore服务：
# 后台启动Metastore服务
hive --service metastore &
客户端 hive-site.xml（其他Hive客户端机器）：
<configuration>
<!-- 指定远程Metastore服务的URI -->
<property>
<name>hive.metastore.uris</name>
<value>thrift://metastore-host:9083</value>
</property>
</configuration>
四、Hive数据类型
4.1 基本数据类型
| 数据类型 | 说明 | 示例 |
|---|---|---|
| TINYINT | 1字节有符号整数 | -128 ~ 127 |
| SMALLINT | 2字节有符号整数 | -32768 ~ 32767 |
| INT | 4字节有符号整数 | 2147483647 |
| BIGINT | 8字节有符号整数 | 9223372036854775807 |
| FLOAT | 4字节单精度浮点数 | 3.14F |
| DOUBLE | 8字节双精度浮点数 | 3.14159 |
| DECIMAL | 任意精度的有符号小数 | DECIMAL(10,2) |
| BOOLEAN | 布尔类型 | TRUE / FALSE |
| STRING | 字符串类型（无长度限制） | ‘hello world’ |
| VARCHAR | 变长字符串（需指定最大长度） | VARCHAR(100) |
| CHAR | 定长字符串 | CHAR(10) |
| TIMESTAMP | 时间戳 | ‘2024-01-01 12:00:00’ |
| DATE | 日期 | ‘2024-01-01’ |
| BINARY | 二进制字节序列 | — |
4.2 复杂数据类型
| 数据类型 | 说明 | 示例 |
|---|---|---|
| ARRAY | 有序同类型元素集合 | ARRAY |
| MAP | 键值对集合，键类型相同，值类型相同 | MAP<string, int> |
| STRUCT | 结构体，包含多个命名字段 | STRUCT<name:string, age:int> |
| UNIONTYPE | 联合类型，可存储多种类型之一 | UNIONTYPE<int, string> |
4.3 数据类型案例代码
-- ============================================================
-- 案例1：创建包含各种基本数据类型的表
-- ============================================================
CREATE TABLE data_type_demo (
id INT COMMENT '用户ID，4字节整数', -- 整数类型
username STRING COMMENT '用户名，字符串类型', -- 字符串类型
age TINYINT COMMENT '年龄，1字节整数', -- 小整数类型
salary DECIMAL(10,2) COMMENT '薪资，精确到小数点后2位', -- 精确小数
height DOUBLE COMMENT '身高，双精度浮点数', -- 浮点类型
is_active BOOLEAN COMMENT '是否激活', -- 布尔类型
birth_date DATE COMMENT '出生日期', -- 日期类型
create_time TIMESTAMP COMMENT '创建时间', -- 时间戳类型
bio VARCHAR(500) COMMENT '个人简介，最长500字符', -- 变长字符串
gender CHAR(1) COMMENT '性别，M/F，固定1字符' -- 定长字符串
)
COMMENT '数据类型演示表' -- 表注释
ROW FORMAT DELIMITED -- 指定行格式为分隔
FIELDS TERMINATED BY '\t' -- 字段间以制表符分隔
LINES TERMINATED BY '\n' -- 行间以换行符分隔
STORED AS TEXTFILE; -- 以文本文件格式存储
-- ============================================================
-- 案例2：创建包含复杂数据类型的表（ARRAY、MAP、STRUCT）
-- ============================================================
CREATE TABLE complex_type_demo (
student_id INT COMMENT '学生ID', -- 学生编号
student_name STRING COMMENT '学生姓名', -- 姓名
-- ARRAY类型：存储学生的所有课程成绩，有序列表
scores ARRAY<DOUBLE> COMMENT '各科成绩列表',
-- MAP类型：存储科目的名称到成绩的映射
course_scores MAP<STRING, DOUBLE> COMMENT '课程名称->成绩映射',
-- STRUCT类型：存储地址信息，包含多个命名字段
address STRUCT<
city:STRING, -- 城市
street:STRING, -- 街道
zip_code:STRING -- 邮编
> COMMENT '家庭地址结构体'
)
COMMENT '复杂数据类型演示表'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t' -- 字段以制表符分隔
COLLECTION ITEMS TERMINATED BY ',' -- 集合元素以逗号分隔
MAP KEYS TERMINATED BY ':' -- MAP的键值对以冒号分隔
LINES TERMINATED BY '\n' -- 行以换行符分隔
STORED AS TEXTFILE; -- 文本文件格式存储
-- ============================================================
-- 案例3：向复杂类型表中插入并查询数据
-- ============================================================
-- 准备测试数据文件 complex_data.txt（每行一条记录）:
-- 1001 张三 90.5,85.0,92.0 数学:90.5,语文:85.0,英语:92.0 北京市,朝阳区建国路,100022
-- 1002 李四 78.0,88.5,95.0 数学:78.0,语文:88.5,英语:95.0 上海市,浦东新区世纪大道,200120
-- 加载数据到表中
LOAD DATA LOCAL INPATH '/home/user/complex_data.txt' -- 本地文件路径
OVERWRITE INTO TABLE complex_type_demo; -- 覆盖写入目标表
-- 查询ARRAY类型：使用下标访问元素（从0开始）
SELECT
student_name, -- 学生姓名
scores[0] AS math_score, -- 第1个元素（数学成绩）
scores[1] AS chinese_score, -- 第2个元素（语文成绩）
SIZE(scores) AS total_subjects -- 数组中元素个数（共多少科）
FROM complex_type_demo;
-- 查询MAP类型：使用键名访问值
SELECT
student_name, -- 学生姓名
course_scores['数学'] AS math, -- 获取键为'数学'的值
course_scores['语文'] AS chinese, -- 获取键为'语文'的值
MAP_KEYS(course_scores) AS all_courses, -- 获取所有键
MAP_VALUES(course_scores) AS all_scores -- 获取所有值
FROM complex_type_demo;
-- 查询STRUCT类型：使用 点号(.) 访问字段
SELECT
student_name, -- 学生姓名
address.city AS city, -- 访问STRUCT中的city字段
address.street AS street, -- 访问STRUCT中的street字段
address.zip_code AS zip -- 访问STRUCT中的zip_code字段
FROM complex_type_demo;
五、数据库操作
5.1 创建数据库
-- ============================================================
-- 案例1：创建数据库的基本语法
-- ============================================================
CREATE DATABASE IF NOT EXISTS company_db -- 如果不存在则创建数据库company_db
COMMENT '公司业务数据库' -- 数据库的注释/描述信息
LOCATION '/user/hive/warehouse/company_db.db' -- 指定数据库在HDFS上的存储路径
WITH DBPROPERTIES ( -- 设置数据库的自定义属性
'creator' = 'admin', -- 创建者
'create_date' = '2024-01-01', -- 创建日期
'purpose' = '存储公司所有业务数据' -- 用途说明
);
-- ============================================================
-- 案例2：创建简单的数据库（使用默认路径）
-- ============================================================
CREATE DATABASE IF NOT EXISTS school_db; -- 最简形式，使用默认存储路径
-- ============================================================
-- 案例3：查看所有数据库
-- ============================================================
SHOW DATABASES; -- 列出所有数据库
-- ============================================================
-- 案例4：使用模糊匹配查找数据库
-- ============================================================
SHOW DATABASES LIKE 'company*'; -- 列出以company开头的数据库（通配符匹配）
SHOW DATABASES LIKE '*_db'; -- 列出以_db结尾的数据库
5.2 查看数据库信息
-- ============================================================
-- 案例1：查看数据库的详细描述信息
-- ============================================================
DESCRIBE DATABASE company_db; -- 显示数据库名称、注释、存储位置
-- ============================================================
-- 案例2：查看数据库的扩展描述信息（含自定义属性）
-- ============================================================
DESCRIBE DATABASE EXTENDED company_db; -- 额外显示DBPROPERTIES中的自定义属性
-- ============================================================
-- 案例3：使用DESC也可以查看
-- ============================================================
DESC DATABASE company_db; -- 等同于DESCRIBE DATABASE
-- ============================================================
-- 案例4：查看当前正在使用的数据库
-- ============================================================
SET hive.cli.print.current.db = true; -- 在CLI提示符中显示当前数据库名
-- 此后命令行提示符会变为 hive (company_db)>
-- ============================================================
-- 案例5：切换/使用数据库
-- ============================================================
USE company_db; -- 切换到company_db数据库
USE default; -- 切换回默认数据库
5.3 修改数据库属性
-- ============================================================
-- 案例1：修改数据库的自定义属性（DBPROPERTIES）
-- ============================================================
ALTER DATABASE company_db -- 修改company_db数据库
SET DBPROPERTIES ( -- 更新自定义属性
'creator' = 'zhangsan', -- 修改创建者
'edit_date' = '2024-06-15', -- 新增修改日期
'purpose' = '公司核心业务数据仓库' -- 修改用途描述
);
-- ============================================================
-- 案例2：修改数据库的所有者（Hive 2.x+ 支持）
-- ============================================================
ALTER DATABASE company_db -- 修改company_db数据库
SET OWNER USER 'lisi'; -- 将数据库所有者修改为lisi
-- ============================================================
-- 案例3：验证修改结果
-- ============================================================
DESCRIBE DATABASE EXTENDED company_db; -- 查看修改后的数据库属性
5.4 删除数据库
-- ============================================================
-- 案例1：删除空数据库
-- ============================================================
DROP DATABASE IF EXISTS school_db; -- 如果数据库存在且为空则删除
-- IF EXISTS：如果不存在不报错
-- ============================================================
-- 案例2：强制删除数据库（包含其中的表一起删除）
-- ============================================================
DROP DATABASE IF EXISTS school_db CASCADE; -- CASCADE：级联删除，数据库中的所有表
-- 和数据一起被删除
-- 生产环境慎用！
-- ============================================================
-- 案例3：查看删除结果
-- ============================================================
SHOW DATABASES LIKE 'school*'; -- 确认数据库已被删除
六、表操作
6.1 创建表
-- ============================================================
-- 案例1：创建内部表（Managed Table）——员工表
-- ============================================================
CREATE TABLE IF NOT EXISTS employees ( -- 如果不存在则创建
emp_id INT COMMENT '员工编号', -- 整数类型，员工ID
emp_name STRING COMMENT '员工姓名', -- 字符串，姓名
emp_age INT COMMENT '员工年龄', -- 整数，年龄
emp_salary DECIMAL(10,2) COMMENT '月薪', -- 精确小数，薪资
hire_date DATE COMMENT '入职日期', -- 日期类型
dept_name STRING COMMENT '所属部门名称' -- 字符串，部门
)
COMMENT '员工信息表（内部表）' -- 表注释
ROW FORMAT DELIMITED -- 行格式：使用分隔符
FIELDS TERMINATED BY '\t' -- 字段之间以Tab键分隔
LINES TERMINATED BY '\n' -- 记录之间以换行符分隔
STORED AS TEXTFILE -- 存储格式：纯文本
LOCATION '/user/hive/warehouse/company_db.db/employees'; -- 数据文件在HDFS上的路径
-- ============================================================
-- 案例2：创建外部表（External Table）—— 日志表
-- ============================================================
-- 外部表删除时只删除元数据，不删除HDFS上的实际数据文件
CREATE EXTERNAL TABLE IF NOT EXISTS access_logs ( -- EXTERNAL关键字指定为外部表
log_id BIGINT COMMENT '日志ID', -- 大整数，日志编号
user_id INT COMMENT '用户ID', -- 整数，用户编号
url STRING COMMENT '访问URL', -- 字符串，请求地址
ip_address STRING COMMENT 'IP地址', -- 字符串，客户端IP
access_time TIMESTAMP COMMENT '访问时间', -- 时间戳
status_code INT COMMENT 'HTTP状态码', -- 整数，响应码
user_agent STRING COMMENT '浏览器标识' -- 字符串，User-Agent
)
COMMENT 'Web访问日志表（外部表）'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|' -- 字段以竖线分隔
LINES TERMINATED BY '\n'
STORED AS TEXTFILE
LOCATION '/data/logs/access/'; -- 指向已有的HDFS数据目录
-- ============================================================
-- 案例3：创建分区表（Partitioned Table）—— 订单表按日期分区
-- ============================================================
CREATE TABLE IF NOT EXISTS orders (
order_id BIGINT COMMENT '订单ID', -- 订单编号
user_id INT COMMENT '用户ID', -- 用户编号
product_id INT COMMENT '商品ID', -- 商品编号
product_name STRING COMMENT '商品名称', -- 商品名
quantity INT COMMENT '购买数量', -- 数量
total_price DECIMAL(10,2) COMMENT '订单总金额', -- 总价
order_status STRING COMMENT '订单状态' -- 状态（待付款/已完成等）
)
COMMENT '订单信息表（按日期分区）'
PARTITIONED BY ( -- 定义分区字段
order_year STRING COMMENT '订单年份分区', -- 一级分区：年份
order_month STRING COMMENT '订单月份分区' -- 二级分区：月份
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS ORC; -- 使用ORC列式存储格式（生产推荐）
-- ============================================================
-- 案例4：创建分桶表（Bucketed Table）
-- ============================================================
CREATE TABLE IF NOT EXISTS user_actions (
action_id BIGINT COMMENT '行为ID',
user_id INT COMMENT '用户ID',
action_type STRING COMMENT '行为类型（click/view/buy）',
action_time TIMESTAMP COMMENT '行为时间',
product_id INT COMMENT '商品ID'
)
COMMENT '用户行为表（分桶表，按user_id分桶）'
CLUSTERED BY (user_id) INTO 32 BUCKETS -- 按user_id哈希分桶，分成32个桶
SORTED BY (action_time) INTO BUCKETS -- 桶内按action_time排序
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS ORC;
-- ============================================================
-- 案例5：使用CTAS创建表（从查询结果创建新表）
-- ============================================================
CREATE TABLE high_salary_employees AS -- 将查询结果创建为新表
SELECT
emp_id, -- 员工ID
emp_name, -- 姓名
emp_salary -- 薪资
FROM employees -- 来源表
WHERE emp_salary > 10000; -- 筛选薪资大于10000的记录
-- ============================================================
-- 案例6：复制表结构（不复制数据）
-- ============================================================
CREATE TABLE IF NOT EXISTS employees_copy -- 创建表副本
LIKE employees; -- 仅复制employees的表结构
-- ============================================================
-- 案例7：创建带Serde的表（处理JSON格式数据）
-- ============================================================
CREATE TABLE IF NOT EXISTS json_events (
event_id STRING COMMENT '事件ID',
event_type STRING COMMENT '事件类型',
event_data STRING COMMENT '事件数据'
)
COMMENT 'JSON格式事件表'
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' -- 使用JSON序列化/反序列化器
STORED AS TEXTFILE;
6.2 查看表
-- ============================================================
-- 案例1：列出当前数据库下的所有表
-- ============================================================
SHOW TABLES; -- 显示当前数据库中的所有表名
-- ============================================================
-- 案例2：使用模糊匹配查找表
-- ============================================================
SHOW TABLES LIKE 'emp*'; -- 列出以emp开头的表
SHOW TABLES LIKE '*order*'; -- 列出包含order的表
-- ============================================================
-- 案例3：查看表的列信息
-- ============================================================
DESCRIBE employees; -- 显示表的列名、数据类型、注释
DESC employees; -- 简写形式，效果相同
-- ============================================================
-- 案例4：查看表的扩展信息（含分区、存储格式等详细信息）
-- ============================================================
DESCRIBE FORMATTED employees; -- 显示格式化的详细表信息
-- 包括：表类型、存储格式、分区信息、
-- 输入/输出格式、数据位置等
-- ============================================================
-- 案例5：查看表的列扩展信息
-- ============================================================
DESCRIBE EXTENDED employees; -- 以非格式化方式显示完整的表元数据
-- ============================================================
-- 案例6：查看表的创建语句
-- ============================================================
SHOW CREATE TABLE employees; -- 输出创建该表的完整DDL语句
-- 可用于复制表结构
-- ============================================================
-- 案例7：查看分区信息
-- ============================================================
SHOW PARTITIONS orders; -- 列出orders表的所有分区
6.3 修改表
-- ============================================================
-- 案例1：重命名表
-- ============================================================
ALTER TABLE employees -- 修改employees表
RENAME TO staff; -- 将表名改为staff
-- 再改回来
ALTER TABLE staff RENAME TO employees; -- 恢复原名
-- ============================================================
-- 案例2：添加新列
-- ============================================================
ALTER TABLE employees -- 修改employees表
ADD COLUMNS ( -- 添加新列
email STRING COMMENT '电子邮箱', -- 新增email列
phone STRING COMMENT '手机号码' -- 新增phone列
);
-- ============================================================
-- 案例3：替换所有列（用新列定义替换现有所有列）
-- ============================================================
ALTER TABLE employees -- 修改employees表
REPLACE COLUMNS ( -- 替换所有列定义（谨慎使用！）
emp_id INT COMMENT '员工编号',
emp_name STRING COMMENT '员工姓名',
emp_age INT COMMENT '年龄',
emp_salary DOUBLE COMMENT '薪资',
hire_date DATE COMMENT '入职日期',
dept_name STRING COMMENT '部门名称',
email STRING COMMENT '邮箱',
phone STRING COMMENT '电话'
);
-- ============================================================
-- 案例4：修改列名和数据类型
-- ============================================================
ALTER TABLE employees -- 修改employees表
CHANGE COLUMN emp_name -- 要修改的列名
employee_name STRING -- 新列名 新数据类型
COMMENT '员工全名'; -- 新注释
-- ============================================================
-- 案例5：修改表属性
-- ============================================================
ALTER TABLE employees -- 修改employees表
SET TBLPROPERTIES ( -- 设置表的自定义属性
'comment' = '公司全体员工信息表', -- 修改表注释
'owner' = 'hr_department', -- 设置所有者
'skip.header.line.count' = '1' -- 跳过CSV文件的第一行（标题行）
);
-- ============================================================
-- 案例6：为内部表设置Serde属性
-- ============================================================
ALTER TABLE employees -- 修改employees表
SET SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' -- 设置序列化类
WITH SERDEPROPERTIES ( -- 设置Serde属性
'field.delim' = ',', -- 字段分隔符改为逗号
'serialization.format' = ',' -- 序列化格式
);
6.4 删除表
-- ============================================================
-- 案例1：删除内部表
-- ============================================================
DROP TABLE IF EXISTS employees_copy; -- 删除内部表
-- 同时删除元数据和HDFS上的数据文件
-- IF EXISTS：如果表不存在也不会报错
-- ============================================================
-- 案例2：删除外部表
-- ============================================================
DROP TABLE IF EXISTS access_logs; -- 删除外部表
-- 仅删除元数据
-- HDFS上的数据文件保留不变
-- ============================================================
-- 案例3：清空表中的所有数据（保留表结构）
-- ============================================================
TRUNCATE TABLE high_salary_employees; -- 清空表数据，表结构保留
-- 仅适用于内部表
-- 不能用于外部表
6.5 修改分区
-- ============================================================
-- 案例1：添加分区
-- ============================================================
ALTER TABLE orders -- 修改orders表
ADD PARTITION ( -- 添加新分区
order_year = '2024', -- 一级分区：年份为2024
order_month = '01' -- 二级分区：月份为01
)
LOCATION '/user/hive/warehouse/company_db.db/orders/order_year=2024/order_month=01';
-- 指定该分区数据在HDFS上的存储路径
-- ============================================================
-- 案例2：添加多个分区
-- ============================================================
ALTER TABLE orders
ADD PARTITION (order_year = '2024', order_month = '02') -- 添加2024年2月分区
PARTITION (order_year = '2024', order_month = '03'); -- 添加2024年3月分区
-- ============================================================
-- 案例3：修改分区的存储位置
-- ============================================================
ALTER TABLE orders -- 修改orders表
PARTITION ( -- 指定要修改的分区
order_year = '2024',
order_month = '01'
)
SET LOCATION '/new/path/orders/2024/01'; -- 将该分区的存储路径改为新路径
-- ============================================================
-- 案例4：删除分区
-- ============================================================
ALTER TABLE orders -- 修改orders表
DROP IF EXISTS PARTITION ( -- 如果分区存在则删除
order_year = '2024',
order_month = '01'
);
-- ============================================================
-- 案例5：修复分区（当HDFS上有数据文件但Metastore中没有对应分区时使用）
-- ============================================================
MSCK REPAIR TABLE orders; -- 扫描HDFS目录，自动发现并注册缺失的分区
-- 常用于手动往HDFS目录上传数据后使用
-- ============================================================
-- 案例6：查看所有分区
-- ============================================================
SHOW PARTITIONS orders; -- 列出orders表的所有分区
-- 查看特定分区
SHOW PARTITIONS orders PARTITION (order_year = '2024'); -- 只列出2024年的分区
七、数据操作
7.1 导入数据
-- ============================================================
-- 案例1：从本地文件系统加载数据到Hive表（覆盖模式）
-- ============================================================
LOAD DATA LOCAL INPATH -- LOCAL关键字：数据源在本地文件系统
'/home/user/data/employees.txt' -- 本地文件的绝对路径
OVERWRITE INTO TABLE employees; -- OVERWRITE：覆盖表中已有数据
-- INTO：追加模式（去掉OVERWRITE即为追加）
-- ============================================================
-- 案例2：从本地文件系统加载数据到Hive表（追加模式）
-- ============================================================
LOAD DATA LOCAL INPATH -- 从本地加载
'/home/user/data/employees_new.txt' -- 本地文件路径
INTO TABLE employees; -- INTO（无OVERWRITE）：追加到表中
-- ============================================================
-- 案例3：从HDFS加载数据到Hive表
-- ============================================================
-- 注意：从HDFS加载数据时，文件会被移动（非复制）到Hive表的目录中
LOAD DATA INPATH -- 无LOCAL关键字：数据源在HDFS上
'/data/input/employees.csv' -- HDFS上的文件路径
OVERWRITE INTO TABLE employees; -- 覆盖写入
-- ============================================================
-- 案例4：使用INSERT INTO追加数据
-- ============================================================
INSERT INTO TABLE employees -- INSERT INTO：追加数据到表中
VALUES
(1001, '张三', 28, 15000.00, '2023-03-15', '技术部'), -- 第1条记录
(1002, '李四', 32, 20000.00, '2022-07-01', '市场部'), -- 第2条记录
(1003, '王五', 25, 12000.00, '2024-01-10', '技术部'); -- 第3条记录
-- ============================================================
-- 案例5：使用INSERT OVERWRITE覆盖数据
-- ============================================================
INSERT OVERWRITE TABLE employees -- INSERT OVERWRITE：覆盖表中全部数据
SELECT
emp_id, -- 员工ID
emp_name, -- 姓名
emp_age, -- 年龄
emp_salary * 1.1 AS emp_salary, -- 薪资上调10%
hire_date, -- 入职日期
dept_name -- 部门名称
FROM temp_employees; -- 从临时表查询数据
-- ============================================================
-- 案例6：使用INSERT INTO通过SELECT语句追加数据
-- ============================================================
INSERT INTO TABLE employees -- 追加数据到employees表
SELECT
emp_id, -- 员工编号
emp_name, -- 姓名
emp_age, -- 年龄
emp_salary, -- 薪资
hire_date, -- 入职日期
dept_name -- 部门
FROM staging_employees -- 从暂存表读取
WHERE dept_name = '技术部'; -- 只导入技术部员工
-- ============================================================
-- 案例7：使用INSERT ... VALUES创建少量测试数据
-- ============================================================
INSERT INTO TABLE employees VALUES
(2001, '赵六', 30, 18000.50, '2023-06-15', '人事部'),
(2002, '钱七', 27, 13500.00, '2024-02-20', '财务部');
7.2 向分区导入数据
-- ============================================================
-- 案例1：静态分区导入——指定具体的分区值
-- ============================================================
INSERT INTO TABLE orders -- 追加数据到orders表
PARTITION ( -- 指定静态分区值
order_year = '2024', -- 分区字段：年份=2024
order_month = '06' -- 分区字段：月份=06
)
SELECT
order_id, -- 订单ID（注意不包含分区列）
user_id, -- 用户ID
product_id, -- 商品ID
product_name, -- 商品名称
quantity, -- 数量
total_price, -- 总价
order_status -- 订单状态
FROM raw_orders -- 来源表
WHERE order_date >= '2024-06-01' -- 筛选2024年6月的订单
AND order_date < '2024-07-01'; -- 且小于2024年7月
-- ============================================================
-- 案例2：动态分区导入——根据查询结果自动确定分区值
-- ============================================================
-- 开启动态分区功能
SET hive.exec.dynamic.partition = true; -- 开启动态分区（默认true）
SET hive.exec.dynamic.partition.mode = nonstrict; -- 设为非严格模式（允许全部动态分区）
SET hive.exec.max.dynamic.partitions = 1000; -- 最大动态分区数
SET hive.exec.max.dynamic.partitions.pernode = 100; -- 每个节点最大分区数
INSERT INTO TABLE orders -- 追加到orders表
PARTITION ( -- 分区字段
order_year, -- 动态分区：年份（从数据中获取值）
order_month -- 动态分区：月份（从数据中获取值）
)
SELECT
order_id, -- 订单ID
user_id, -- 用户ID
product_id, -- 商品ID
product_name, -- 商品名称
quantity, -- 数量
total_price, -- 总价
order_status, -- 订单状态
YEAR(order_date) AS order_year, -- 从order_date提取年份作为分区值
LPAD(MONTH(order_date), 2, '0') AS order_month -- 提取月份，左补零至2位
FROM raw_orders; -- 从原始订单表读取全部数据
-- ============================================================
-- 案例3：混合使用静态分区和动态分区
-- ============================================================
INSERT OVERWRITE TABLE orders -- 覆盖写入orders表
PARTITION ( -- 分区定义
order_year = '2024', -- 静态分区：年份固定为2024
order_month -- 动态分区：月份从数据中获取
)
SELECT
order_id, -- 订单ID
user_id, -- 用户ID
product_id, -- 商品ID
product_name, -- 商品名称
quantity, -- 数量
total_price, -- 总价
order_status, -- 订单状态
LPAD(MONTH(order_date), 2, '0') AS order_month -- 提取月份作为动态分区值
FROM raw_orders -- 来源表
WHERE YEAR(order_date) = 2024; -- 只处理2024年的数据
-- ============================================================
-- 案例4：使用LOAD DATA加载数据到指定分区
-- ============================================================
LOAD DATA LOCAL INPATH -- 从本地文件加载
'/home/user/data/orders_2024_jan.txt' -- 本地文件路径
INTO TABLE orders -- 目标表
PARTITION ( -- 加载到指定分区
order_year = '2024',
order_month = '01'
);
-- ============================================================
-- 案例5：从HDFS加载数据到指定分区
-- ============================================================
LOAD DATA INPATH -- 从HDFS加载
'/data/orders/2024_02.txt' -- HDFS文件路径
INTO TABLE orders -- 目标表
PARTITION ( -- 指定分区
order_year = '2024',
order_month = '02'
);
7.3 查询数据
-- ============================================================
-- 案例1：基本查询——选择所有列
-- ============================================================
SELECT * FROM employees; -- 查询employees表中的所有数据
-- 生产环境慎用，数据量大时会全表扫描
-- ============================================================
-- 案例2：选择特定列
-- ============================================================
SELECT
emp_id, -- 员工编号
emp_name, -- 员工姓名
emp_salary -- 薪资
FROM employees; -- 来源表
-- ============================================================
-- 案例3：使用WHERE子句过滤数据
-- ============================================================
SELECT
emp_id, -- 员工编号
emp_name, -- 员工姓名
emp_salary, -- 薪资
dept_name -- 部门
FROM employees -- 来源表
WHERE emp_salary > 15000 -- 条件1：薪资大于15000
AND dept_name = '技术部'; -- 条件2：部门为技术部
-- ============================================================
-- 案例4：使用LIKE进行模糊查询
-- ============================================================
SELECT
emp_id, -- 员工编号
emp_name -- 员工姓名
FROM employees
WHERE emp_name LIKE '张%'; -- 姓名以"张"开头，%匹配任意多个字符
-- 其他LIKE示例：
-- WHERE emp_name LIKE '_三' -- 姓名是两个字，第二个字是"三"（_匹配单个字符）
-- WHERE emp_name LIKE '%李%' -- 姓名中包含"李"字
-- ============================================================
-- 案例5：使用IN和BETWEEN
-- ============================================================
SELECT emp_id, emp_name, dept_name
FROM employees
WHERE dept_name IN ('技术部', '市场部', '人事部'); -- 部门在指定列表中
SELECT emp_id, emp_name, emp_salary
FROM employees
WHERE emp_salary BETWEEN 10000 AND 20000; -- 薪资在10000到20000之间（含两端）
-- ============================================================
-- 案例6：使用GROUP BY和聚合函数
-- ============================================================
SELECT
dept_name, -- 按部门分组
COUNT(*) AS emp_count, -- 统计每个部门的员工人数
AVG(emp_salary) AS avg_salary, -- 计算平均薪资
MAX(emp_salary) AS max_salary, -- 最高薪资
MIN(emp_salary) AS min_salary, -- 最低薪资
SUM(emp_salary) AS total_salary -- 薪资总额
FROM employees
GROUP BY dept_name -- 按部门名称分组
HAVING COUNT(*) >= 5 -- 只显示员工数>=5的部门（对分组结果过滤）
ORDER BY avg_salary DESC; -- 按平均薪资降序排列
-- ============================================================
-- 案例7：使用ORDER BY排序（全局排序，只使用一个Reducer）
-- ============================================================
SELECT emp_id, emp_name, emp_salary, dept_name
FROM employees
ORDER BY emp_salary DESC -- 按薪资降序排列
LIMIT 10; -- 只取前10条
-- ============================================================
-- 案例8：使用SORT BY排序（每个Reducer内部排序，非全局）
-- ============================================================
SET mapreduce.job.reduces = 3; -- 设置Reducer数量为3
SELECT emp_id, emp_name, emp_salary
FROM employees
SORT BY emp_salary DESC; -- 每个Reducer内部按薪资降序
-- ============================================================
-- 案例9：使用DISTRIBUTE BY和SORT BY组合
-- ============================================================
-- DISTRIBUTE BY：将数据按指定字段分发到不同Reducer
-- SORT BY：每个Reducer内部排序
SELECT emp_id, emp_name, emp_salary, dept_name
FROM employees
DISTRIBUTE BY dept_name -- 按部门分发到不同Reducer
SORT BY emp_salary DESC; -- 每个Reducer内部按薪资降序排序
-- ============================================================
-- 案例10：使用CLUSTER BY（等价于 DISTRIBUTE BY + SORT BY 同一字段升序）
-- ============================================================
SELECT emp_id, emp_name, emp_salary, dept_name
FROM employees
CLUSTER BY dept_name; -- 等价于 DISTRIBUTE BY dept_name SORT BY dept_name ASC
-- ============================================================
-- 案例11：使用LIMIT限制返回行数
-- ============================================================
SELECT * FROM employees LIMIT 5; -- 只返回前5条记录
SELECT * FROM employees LIMIT 5, 10; -- 跳过前5条，返回第6~15条（Hive 2.x+）
-- ============================================================
-- 案例12：使用CASE WHEN条件表达式
-- ============================================================
SELECT
emp_id, -- 员工编号
emp_name, -- 姓名
emp_salary, -- 薪资
CASE -- 条件判断
WHEN emp_salary >= 20000 THEN '高薪' -- 薪资>=20000，标记为'高薪'
WHEN emp_salary >= 15000 THEN '中等' -- 薪资>=15000且<20000，标记为'中等'
WHEN emp_salary >= 10000 THEN '普通' -- 薪资>=10000且<15000，标记为'普通'
ELSE '低薪' -- 其余情况，标记为'低薪'
END AS salary_level -- 新列名为salary_level
FROM employees;
-- ============================================================
-- 案例13：使用窗口函数
-- ============================================================
SELECT
emp_id, -- 员工编号
emp_name, -- 姓名
dept_name, -- 部门
emp_salary, -- 薪资
ROW_NUMBER() OVER ( -- 窗口函数：行号
PARTITION BY dept_name -- 按部门分组
ORDER BY emp_salary DESC -- 组内按薪资降序
) AS rank_in_dept, -- 在本部门的薪资排名
SUM(emp_salary) OVER ( -- 窗口函数：累计求和
PARTITION BY dept_name -- 按部门分组
) AS dept_total_salary -- 本部门薪资总额
FROM employees;
-- ============================================================
-- 案例14：使用子查询
-- ============================================================
-- 查找薪资高于公司平均薪资的员工
SELECT
emp_id, -- 员工编号
emp_name, -- 姓名
emp_salary -- 薪资
FROM employees
WHERE emp_salary > ( -- 子查询：计算公司平均薪资
SELECT AVG(emp_salary) FROM employees -- 内层查询返回平均薪资
);
-- ============================================================
-- 案例15：使用NULL相关函数
-- ============================================================
SELECT
emp_id, -- 员工编号
emp_name, -- 姓名
COALESCE(email, '未填写') AS email, -- 如果email为NULL则显示'未填写'
NVL(phone, '无') AS phone, -- NVL：如果phone为NULL则显示'无'
IF(email IS NULL, '缺失', '完整') AS info_status -- 判断是否为空
FROM employees;
7.4 查询插入
-- ============================================================
-- 案例1：将查询结果插入到另一张表（追加模式）
-- ============================================================
INSERT INTO TABLE tech_employees -- 追加插入到tech_employees表
SELECT
emp_id, -- 员工编号
emp_name, -- 姓名
emp_age, -- 年龄
emp_salary -- 薪资
FROM employees -- 来源表
WHERE dept_name = '技术部'; -- 只选取技术部员工
-- ============================================================
-- 案例2：将查询结果覆盖写入到另一张表
-- ============================================================
INSERT OVERWRITE TABLE tech_employees -- OVERWRITE：覆盖目标表中的现有数据
SELECT
emp_id, -- 员工编号
emp_name, -- 姓名
emp_age, -- 年龄
emp_salary * 1.05 AS emp_salary -- 薪资上浮5%
FROM employees
WHERE dept_name = '技术部';
-- ============================================================
-- 案例3：多表插入——一次查询插入多张表
-- ============================================================
-- Hive支持一次FROM多表插入（Multi-Table Insert）
FROM employees -- 数据来源表
INSERT OVERWRITE TABLE tech_report -- 插入到技术部报表表
SELECT
emp_id, emp_name, emp_salary -- 选择特定列
WHERE dept_name = '技术部' -- 过滤条件：技术部
AND emp_salary > 15000 -- 且薪资>15000
INSERT OVERWRITE TABLE market_report -- 插入到市场部报表表
SELECT
emp_id, emp_name, emp_salary -- 选择特定列
WHERE dept_name = '市场部' -- 过滤条件：市场部
AND emp_salary > 15000; -- 且薪资>15000
-- ============================================================
-- 案例4：将查询结果写入到HDFS目录（导出数据）
-- ============================================================
INSERT OVERWRITE DIRECTORY '/output/emp_report' -- 输出到HDFS目录
ROW FORMAT DELIMITED -- 指定输出格式
FIELDS TERMINATED BY ',' -- 字段以逗号分隔
LINES TERMINATED BY '\n' -- 行以换行符分隔
SELECT
emp_id, -- 员工编号
emp_name, -- 姓名
emp_salary, -- 薪资
dept_name -- 部门
FROM employees
WHERE dept_name = '技术部'; -- 只导出技术部数据
-- ============================================================
-- 案例5：将查询结果导出到本地文件系统
-- ============================================================
INSERT OVERWRITE LOCAL DIRECTORY '/home/user/output/emp_data' -- 输出到本地目录
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t' -- 字段以Tab分隔
SELECT
emp_id, -- 员工编号
emp_name, -- 姓名
emp_salary -- 薪资
FROM employees;
-- ============================================================
-- 案例6：查询结果插入到分区表
-- ============================================================
INSERT INTO TABLE orders -- 追加到orders表
PARTITION (order_year = '2024', order_month = '06') -- 指定静态分区
SELECT
order_id, -- 订单ID
user_id, -- 用户ID
product_id, -- 商品ID
product_name, -- 商品名称
quantity, -- 数量
total_price, -- 总价
order_status -- 订单状态
FROM staging_orders -- 来源暂存表
WHERE order_date LIKE '2024-06%'; -- 筛选2024年6月的数据
-- ============================================================
-- 案例7：使用CTAS一步完成建表+插入
-- ============================================================
CREATE TABLE dept_salary_summary AS -- 创建新表并填充数据
SELECT
dept_name, -- 部门名称
COUNT(*) AS total_employees, -- 员工总数
AVG(emp_salary) AS average_salary, -- 平均薪资
SUM(emp_salary) AS total_salary -- 薪资总额
FROM employees
GROUP BY dept_name; -- 按部门分组统计
7.5 关联查询
-- ============================================================
-- 准备：创建关联查询需要的表和数据
-- ============================================================
-- 员工表（已有）
-- 部门表
CREATE TABLE departments (
dept_id INT COMMENT '部门ID', -- 部门编号
dept_name STRING COMMENT '部门名称', -- 部门名称
dept_loc STRING COMMENT '部门所在地', -- 部门位置
manager_id INT COMMENT '部门经理ID' -- 经理的员工ID
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;
-- 项目表
CREATE TABLE projects (
project_id INT COMMENT '项目ID',
project_name STRING COMMENT '项目名称',
dept_id INT COMMENT '所属部门ID',
budget DECIMAL(12,2) COMMENT '项目预算'
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;
-- ============================================================
-- 案例1：内连接（INNER JOIN）——只返回两表中都匹配的记录
-- ============================================================
SELECT
e.emp_id, -- 员工编号
e.emp_name, -- 员工姓名
e.emp_salary, -- 薪资
d.dept_name, -- 部门名称
d.dept_loc -- 部门所在地
FROM employees e -- 主表（别名e）
INNER JOIN departments d -- 内连接departments表（别名d）
ON e.dept_name = d.dept_name; -- 连接条件：部门名称相同
-- ============================================================
-- 案例2：左外连接（LEFT OUTER JOIN）——返回左表所有记录，右表无匹配则填NULL
-- ============================================================
SELECT
e.emp_id, -- 员工编号
e.emp_name, -- 员工姓名
e.dept_name, -- 员工表中的部门名
d.dept_name AS dept_table_name, -- 部门表中的部门名（可能为NULL）
d.dept_loc -- 部门所在地（无匹配时为NULL）
FROM employees e -- 左表：所有员工都会保留
LEFT OUTER JOIN departments d -- 左外连接
ON e.dept_name = d.dept_name; -- 连接条件
-- ============================================================
-- 案例3：右外连接（RIGHT OUTER JOIN）——返回右表所有记录，左表无匹配则填NULL
-- ============================================================
SELECT
e.emp_id, -- 员工编号（无匹配时为NULL）
e.emp_name, -- 员工姓名（无匹配时为NULL）
d.dept_name, -- 部门名称
d.dept_loc -- 部门所在地
FROM employees e -- 左表
RIGHT OUTER JOIN departments d -- 右外连接：所有部门都会保留
ON e.dept_name = d.dept_name; -- 连接条件
-- ============================================================
-- 案例4：全外连接（FULL OUTER JOIN）——返回两表所有记录，无匹配的填NULL
-- ============================================================
SELECT
e.emp_id, -- 员工编号
e.emp_name, -- 员工姓名
COALESCE(e.dept_name, d.dept_name) AS dept, -- 合并两表的部门名（取非NULL值）
d.dept_loc -- 部门所在地
FROM employees e -- 左表
FULL OUTER JOIN departments d -- 全外连接：两表所有记录都保留
ON e.dept_name = d.dept_name; -- 连接条件
-- ============================================================
-- 案例5：左半连接（LEFT SEMI JOIN）——只返回左表中在右表有匹配的记录
-- 不返回右表的任何列，类似于 EXISTS 子查询
-- ============================================================
SELECT
e.emp_id, -- 员工编号
e.emp_name, -- 员工姓名
e.dept_name -- 部门
FROM employees e -- 左表
LEFT SEMI JOIN departments d -- 左半连接
ON e.dept_name = d.dept_name; -- 连接条件：只判断是否存在匹配
-- ============================================================
-- 案例6：交叉连接（CROSS JOIN / 笛卡尔积）——左表每行 × 右表每行
-- ============================================================
-- 警告：数据量大时会产生海量结果，生产环境慎用！
SELECT
e.emp_name, -- 员工姓名
d.dept_name -- 部门名称
FROM employees e -- 左表
CROSS JOIN departments d; -- 交叉连接：每个员工 × 每个部门
-- ============================================================
-- 案例7：多表关联查询（3表JOIN）
-- ============================================================
SELECT
e.emp_id, -- 员工编号
e.emp_name, -- 员工姓名
d.dept_name, -- 部门名称
d.dept_loc, -- 部门所在地
p.project_name, -- 项目名称
p.budget -- 项目预算
FROM employees e -- 员工表
INNER JOIN departments d -- 第1个连接：员工-部门
ON e.dept_name = d.dept_name -- 连接条件
INNER JOIN projects p -- 第2个连接：部门-项目
ON d.dept_id = p.dept_id; -- 连接条件
-- ============================================================
-- 案例8：自连接（Self Join）——表与自身关联
-- ============================================================
-- 查找同部门中薪资不同的员工对
SELECT
e1.emp_name AS employee_1, -- 员工1的姓名
e2.emp_name AS employee_2, -- 员工2的姓名
e1.dept_name AS department, -- 共同的部门
e1.emp_salary AS salary_1, -- 员工1的薪资
e2.emp_salary AS salary_2 -- 员工2的薪资
FROM employees e1 -- 第1个实例
INNER JOIN employees e2 -- 第2个实例（同一张表）
ON e1.dept_name = e2.dept_name -- 同一个部门
AND e1.emp_id < e2.emp_id; -- 避免重复配对和自配对
-- ============================================================
-- 案例9：使用JOIN的WHERE过滤（注意与ON条件的区别）
-- ============================================================
-- ON条件：在JOIN时过滤，影响连接结果
-- WHERE条件：在JOIN后过滤，对连接结果再筛选
SELECT
e.emp_name, -- 员工姓名
d.dept_name, -- 部门名称
d.dept_loc -- 部门位置
FROM employees e -- 员工表
LEFT OUTER JOIN departments d -- 左外连接
ON e.dept_name = d.dept_name -- ON：连接条件
WHERE d.dept_loc = '北京'; -- WHERE：过滤结果（排除了NULL行，
-- 此时效果等同于INNER JOIN）
-- ============================================================
-- 案例10：使用MAPJOIN提示（小表广播优化）
-- ============================================================
-- 当其中一个表很小时，可以使用MAPJOIN将其加载到内存中广播
SELECT /*+ MAPJOIN(d) */ -- MAPJOIN提示：将d表（小表）加载到内存
e.emp_id, -- 员工编号
e.emp_name, -- 员工姓名
d.dept_name, -- 部门名称
d.dept_loc -- 部门位置
FROM employees e -- 大表
INNER JOIN departments d -- 小表（会被广播到所有Map任务）
ON e.dept_name = d.dept_name; -- 连接条件
-- ============================================================
-- 案例11：使用UNION合并多个查询结果
-- ============================================================
-- UNION DISTINCT：合并并去重（默认）
SELECT emp_id, emp_name, '技术部' AS source
FROM employees
WHERE dept_name = '技术部'
UNION DISTINCT -- 合并两个查询结果并去重
SELECT emp_id, emp_name, '市场部' AS source
FROM employees
WHERE dept_name = '市场部'
ORDER BY emp_id; -- 最终结果按emp_id排序
-- ============================================================
-- 案例12：使用UNION ALL（合并不去重，性能更好）
-- ============================================================
SELECT emp_id, emp_name, emp_salary, '在职' AS status
FROM employees
WHERE is_active = true
UNION ALL -- 合并不去重（保留所有记录）
SELECT emp_id, emp_name, emp_salary, '离职' AS status
FROM former_employees
WHERE leave_date >= '2024-01-01'
ORDER BY emp_id;
八、本章小结
核心知识点总结
┌─────────────────────────────────────────────────────────┐
│ Hive知识体系 │
├──────────────┬──────────────────────────────────────────┤
│ 数据仓库概念 │ 面向主题、集成、稳定、时变 │
│ │ 分层：ODS → DWD → DWS → ADS │
│ │ 模型：星型模型、雪花模型 │
├──────────────┼──────────────────────────────────────────┤
│ Hive本质 │ 基于Hadoop的数据仓库工具 │
│ │ 将HQL转为MapReduce/Tez/Spark任务执行 │
│ │ 读时模式(Schema on Read) │
├──────────────┼──────────────────────────────────────────┤
│ 部署模式 │ 内嵌模式(Derby，单用户) │
│ │ 本地模式(MySQL，多用户) │
│ │ 远程模式(Thrift，分布式) │
├──────────────┼──────────────────────────────────────────┤
│ 数据类型 │ 基本：INT/STRING/DECIMAL/BOOLEAN/DATE... │
│ │ 复杂：ARRAY/MAP/STRUCT/UNIONTYPE │
├──────────────┼──────────────────────────────────────────┤
│ 数据库操作 │ CREATE/SHOW/DESCRIBE/ALTER/DROP DATABASE │
├──────────────┼──────────────────────────────────────────┤
│ 表操作 │ 内部表 vs 外部表(EXTERNAL) │
│ │ 分区表(PARTITIONED BY) │
│ │ 分桶表(CLUSTERED BY) │
│ │ CREATE/SHOW/DESCRIBE/ALTER/DROP TABLE │
├──────────────┼──────────────────────────────────────────┤
│ 数据操作 │ LOAD DATA (本地/HDFS) │
│ │ INSERT INTO/OVERWRITE + SELECT │
│ │ 静态分区 vs 动态分区 │
├──────────────┼──────────────────────────────────────────┤
│ 查询语法 │ SELECT...FROM...WHERE...GROUP BY... │
│ │ HAVING...ORDER BY...LIMIT │
│ │ JOIN: INNER/LEFT/RIGHT/FULL/SEMI/CROSS │
│ │ UNION/UNION ALL │
│ │ 子查询、窗口函数、CASE WHEN │
└──────────────┴──────────────────────────────────────────┘
常用配置参数速查
-- ============================================================
-- Hive常用配置参数（SET命令设置）
-- ============================================================
-- 显示当前数据库名
SET hive.cli.print.current.db = true; -- 在CLI提示符中显示当前数据库
-- 显示查询结果的列头
SET hive.cli.print.header = true; -- 查询结果第一行显示列名
-- 动态分区设置
SET hive.exec.dynamic.partition = true; -- 开启动态分区
SET hive.exec.dynamic.partition.mode = nonstrict; -- 允许全部字段都是动态分区
-- MapJoin优化（小表自动广播）
SET hive.auto.convert.join = true; -- 自动将小表进行MapJoin优化
SET hive.mapjoin.smalltable.filesize = 25000000; -- 小表阈值：25MB
-- 设置执行引擎
SET hive.execution.engine = mr; -- 使用MapReduce引擎
-- SET hive.execution.engine = tez; -- 使用Tez引擎（更快）
-- SET hive.execution.engine = spark; -- 使用Spark引擎
-- 并行执行
SET hive.exec.parallel = true; -- 允许并行执行独立的MapReduce阶段
SET hive.exec.parallel.thread.number = 8; -- 最大并行度
-- 输出压缩
SET hive.exec.compress.output = true; -- 开启输出压缩
SET mapreduce.output.fileoutputformat.compress.codec =
org.apache.hadoop.io.compress.SnappyCodec; -- 使用Snappy压缩
AtomGit 是由开放原子开源基金会联合 CSDN 等生态伙伴共同推出的新一代开源与人工智能协作平台。平台坚持“开放、中立、公益”的理念，把代码托管、模型共享、数据集托管、智能体开发体验和算力服务整合在一起，为开发者提供从开发、训练到部署的一站式体验。
更多推荐
3
0- 0
已为社区贡献8条内容
所有评论(0)