一、学习内容框架
大数据技术解决两个问题：
1.海量数据的存储：Hadoop的HDFS
2.海量数据的计算：Hadoop的Mapreduce
存在的问题：数据存放在hdfs上，怎么去读取和写入？
而且Mapreduce编程的非常的困难，怎么样让更多的人使用大数据去计算与分析？
Hive【数据仓库】解决上面的两个问题：
：使用Hive把数据存储在Hdfs上，编写Sql【Hql】就可以在hdfs上进行数据的存储与计算查询
1.1 什么是Hive
Apache Hive是一款建立在Hadoop之上的开源数据仓库系统，可以将存储在Hadoop文件中的结构化数据映射为一张数据库表，提供了类似SQL的查询模型，称为Hive查询语言（HQL），用于访问和分析存储在Hadoop文件中的大型数据集。
Hive核心是将HQL转换为MapReduce程序，然后将程序提交到Hadoop群集执行，就可以在hdfs上进行数据的在存储与计算了。
2.2 启动Hive的环境
# 以下的操作全在master上操作
# 启动hdfs
start-dfs.sh
# 启动yarn
start-yarn.sh
# 启动hive的元数据服务
hive --service metastore &
# 启动Hive的远程访问服务
hive --service hiveserver2 &
# 在master上操作
beeline -u jdbc:hive2://master:10000 -n root
2.3启动Hive的控制端
1.第一代客户端【不推荐】
hive
2.beeline客户端 【第二代的最新客户端，其实就是远程访问登录】
# 在master上操作
beeline -u jdbc:hive2://master:10000 -n root
# 如果master节点出现以下日志，表示hive启动成功
Beeline version 2.3.4 by Apache Hive
0: jdbc:hive2://master:10000>
3.使用IDEA做为客户端
配置IDEA
打开IDEA,创建项目
配置hive驱动
方法：将hive-jdbc-jar包配置到IDEA中
步骤：打开 右侧Database 窗口
创建 Driver
步骤：选中 Driver ，并点击
下载 Driver 所需依赖 1.去自己的主机上/opt/apps/hive/jdbc 目录中下载hive-jdbc-3.1.2-standalone.jar的 jar 包
注意：默认下载到电脑的桌面上的fsdownload文件夹
2.把hive-jdbc-3.1.2-standalone.jar复制到IDEA中，并连接
在IDEA中粘贴jar包
3.在IDEA中连接jar包
如下图所示
4.开启连接
选择下图的4，创建数据源，选择Create Data Source
连接
创建文件，并执行SQL语句
本例中文件取名为hive.sql
创建会话窗口
执行show databases命令进行测试
或者右侧选择命令执行
运行结果：
常见命令解释
二、Hive的DDL语言
1.1 创建数据库
示例1：创建一个数据库：itheima
create database if not exists itheima ;
【重点归纳】
Hive数据库与HDFS目录：Hive在创建数据库时会在HDFS的
/user/hive/warehouse目录下创建相应的文件夹来存储该数据库的表数据；如果只是删除HDFS上的数据库目录并不会影响Hive的元数据。192.168.36.100:9870
删除和重新创建数据库：Hive的元数据和HDFS的物理存储是分开的，因此在HDFS中删除数据库目录后，Hive元数据仍认为数据库存在，导致无法用同名重新创建数据库，而且使用Show databases还能看到数据库存在。
如果要完全删除Hive数据库：需使用
DROP DATABASE 数据库名 CASCADE命令，同时清理元数据和物理存储。避免在HDFS上直接删除数据库的目录
示例2：创建数据库itcast，并将其数据存储在 HDFS的 /hive_db/create_db/itcast.db 目录中。
CREATE DATABASE IF NOT EXISTS itcast
LOCATION '/hive_db/create_db/itcast.db'
-- 注意：在hive中不能创建有同名的数据库，如果创建不成功，检查一下hive中是否有同名的数据库
使用默认 LOCATION 时的行为:
当未指定
LOCATION，Hive 会使用默认的数据仓库路径（通常是/user/hive/warehouse），并通常会在这个路径下立即创建一个以数据库名命名的目录。
1.2 查看数据库
Hive中查询数据库的语法格式如下：
SHOW DATABASES [LIKE '数据库名'];
-- 字段说明：
[ ]表示可选字段
show databases 显示所有数据库
LIKE '数据库名' 可选，LIKE子句用于模糊查询，[可以使用通配符]
_代表任意一个字符
%代表任意字符
-- 示例1
-- 查询Hive中所有数据库
SHOW DATABASES
-- 查询Hive中数据库名称首字母是“i”的数据库。
SHOW DATABASES LIKE 'i*'；
有一个例外就是default数据库中的表，default数据库没有自己的目录，所以是直接放在/user/hive/warehouse下面的
1.3 查询数据库的信息
Hive中的DESCRIBE DATABASE语句用于显示Hive中数据库的名称、注释（如果已设置）及其在文件系统上的位置等信息。
DESC DATABASE [EXTENDED] db_name;
-- EXTENDED：用于显示更多信息
-- 查询Hive数据库itcast的信息 DESC DATABASE itheima; -- 查询Hive数据库itcast的详细信息（除了基础信息外，还显示如数据库的元数据、创建时间和其他数据库属性。） DESC DATABASE EXTENDED itheima;
1.4 选择数据库
Hive中的USE DATABASE语句用于选择特定的数据库，切换当前会话使用哪一个数据库进行操作。
-- 语法: USE database_name; -- 将当前使用的数据库切换至数据库itcast USE itcast; -- 查看当前所在的数据库 select current_database();
1.5 删除数据库
Hive中的DROP DATABASE语句用于删除（删除）数据库。
默认行为是RESTRICT，这意味着仅在数据库为空时才删除它。要删除带有表的数据库，我们可以使用CASCADE
DROP DATABASE IF EXISTS database_name CASCADE; -- DROP (DATABASE|SCHEMA)：表示删除数据库的语句； -- IF EXISTS：可选，用于判断数据库是否存在； -- database_name：用于指定数据库名称； -- CASCADE：可选，表示数据库中存在表时也强制删除数据库。
案例—王者荣耀
文件archer.txt中记录了手游《王者荣耀》射手的相关信息，内容如下所示，其中字段之间分隔符为制表符\t,要求在Hive中建表映射成功该文件。
1. 在master的路径 /opt下创建data目录
mkdir -p /opt/data
2.创建archer.txt文件，并上传到/opt/data目录下
1 后羿 5986 1784 396 336 remotely archer 2 马可波罗 5584 200 362 344 remotely archer 3 鲁班七号 5989 1756 400 323 remotely archer 4 李元芳 5725 1770 396 340 remotely archer 5 孙尚香 6014 1756 411 346 remotely archer 6 黄忠 5898 1784 403 319 remotely archer 7 狄仁杰 5710 1770 376 338 remotely archer 8 虞姬 5669 1770 407 329 remotely archer 9 成吉思汗 5799 1742 394 329 remotely archer 10 百里守约 5611 1784 410 329 remotely archer assassin
字段含义：
id、name（英雄名称）、hp_max（最大生命）、mp_max（最大法力）、attack_max（最高物攻）、defense_max（最大物防）、attack_range（攻击范围）、role_main（主要定位）、role_assist（次要定位）。
分析一下：字段都是基本类型，字段的顺序需要注意一下。字段之间的分隔符是制表符，需要使用row format语法进行指定。
3. 创建数据库与数据表：
-- 打开IDEA，连接Hive -- 删除数据库 drop database if exists honor_of_kings cascade; -- 创建数据库并切换使用 create database if not exists honor_of_kings; -- 选择数据库 use honor_of_kings; -- 查看当前数据库 select current_database(); -- 创建数据表 create table if not exists honor_of_kings.t_archer( id int comment "ID", name string comment "英雄名称", hp_max int comment "最大生命", mp_max int comment "最大法力", attack_max int comment "最高物攻", defense_max int comment "最大物防", attack_range string comment "攻击范围", role_main string comment "主要定位", role_assist string comment "次要定位" ) row format delimited fields terminated by "\t" STORED AS TEXTFILE; -- 查看数据表的内容 select * from t_archer; -- 查看数据表的信息 desc extended t_archer; -- 查看数据表详细信息【元数据】 desc formatted t_archer;
4.archer.txt文件上传到t_archer表文件夹下。
-- 上传本地数据到hive的表（其实就是在hdfs中，hive把数据表是映射在了HDFS） -- 语法：hdfs dfs -put 本地文件 hdfs的路径 -- 4.1 上传本地数据到hive的表 LOAD DATA LOCAL INPATH '/opt/data/archer.txt' INTO TABLE honor_of_kings.t_archer; -- 4.2 在HDFS的web UI 去查看数据是否上传到了t_archer目录中 master:9870 -- 4.3 在master上使用hdfs dfs命令查看数据 hdfs dfs -ls -R / -- 4.4 在master的主机上运行命令查看数据 hdfs dfs -cat /user/hive/warehouse/honor_of_kings.db/t_archer/*
5.在IDEA执行SQL查询操作，可以看出数据已经映射成功。
select * from t_archer;
想一想：Hive这种能力是不是比mysql一条一条insert插入数据方便多了？
三、Hive DDL建表高阶
1. Hive内、外部表
1.1 什么是内部表
Hive内部表，也叫管理表，创建表时默认创建的就是内部表。Hive对内部表拥有完全的控制权，包括数据和表结构的管理。
存储与管理
存储位置：内部表的数据存储在Hadoop分布式文件系统（HDFS）的指定目录中。
数据格式：数据以Hadoop的文件格式存储，确保与Hadoop生态系统的兼容性。
数据管理：Hive完全管理内部表的数据。这包括数据的添加、删除和修改等操作。用户通过Hive提供的命令进行这些操作，而无需直接操作底层HDFS。
数据操作
数据检索：使用Hive的SELECT语句可以从内部表中查询数据。Hive负责从HDFS读取数据并返回查询结果。
数据维护：与外部表相比，用户可以通过Hive命令直接对表中的数据进行修改、添加或删除。
生命周期
创建与删除：当删除内部表时，Hive不仅删除表的元数据，还删除存储在HDFS上的源数据。这是内部表与外部表的关键区别，外部表删除时不会删除数据。
基本操作
创建数据库
drop database if exists hive_test; create database if not exists hive_test;
选择数据库
use hive_test;
创建内部表student
create table student( num int, name string, sex string, age int, dept string) row format delimited fields terminated by ',';
查看student表的元数据描述信息
DESC FORMATTED student;
1.2 什么是外部表
Hive外部表允许定义表结构，但不管理数据本身。这些表使用EXTERNAL关键字创建，主要用于访问存储在HDFS或其他外部存储系统中的数据。Hive负责管理外部表的元数据，而数据的实际存储和管理由外部系统处理。
存储与管理
存储位置：外部表的数据存储在指定的外部位置，可以是HDFS或其他外部存储系统。
数据格式：数据的格式由存储在外部系统中的实际数据决定，Hive适应这些格式以正确解析数据。
数据管理：数据的管理（添加、删除和修改）由外部存储系统负责，Hive不对外部表中的数据进行管理。
数据操作
数据检索：使用Hive的SELECT语句可以查询外部表数据，Hive从外部存储系统读取并返回数据。
数据维护：与内部表不同，Hive不能直接对外部表中的数据进行修改、添加或删除操作。数据维护必须通过外部存储系统进行。
生命周期
创建：创建外部表时，使用
LOCATION参数指定数据的存储位置。Hive初始化表结构的元数据，而不影响实际数据。删除：删除外部表仅移除Hive中的元数据，实际存储在外部系统的数据保持不变，仍可在Hive之外访问。
基本操作
创建外部表student_ext
create external table student_ext( num int, name string, sex string, age int, dept string) row format delimited fields terminated by ',' location '/stu';
备注：外部表本身只是在Hive中定义的一个表结构，它并不包含任何实际的数据。只有在外部表所指定的HDFS路径下放置了相应的数据文件后，才能通过HDFS Web UI来查看这些文件。
查看student_ext表的元数据描述信息
DESC FORMATTED student_ext;
1.3 案例演示
下面我们将用一个关于NBA球员分析的数据进行区别内部表和外部表。
-- 删除数据库 drop database if exists nba; -- 创建数据库nba create database if not exists nba; -- 选择数据库 use nba; -- 检查目前所在的数据库 select current_database();
1.创建内部表t1
-- 删除内部表t1 drop table if exists t1; -- 创建内部表t1 create table if not exists t1( name string comment '名字', pos string comment '位置', height double comment '身高', weight int comment '体重', age int comment '年龄', nba_age int comment '球龄', nums int comment '出场次数', avgtime double comment '平均时间', attack double comment '进攻能力', defend int comment '防守能力', allstar string comment '是否进入全明星', income bigint comment '薪资' ) comment "NBA球员分析" row format delimited fields terminated by ','; -- 查看表t1的元数据信息 desc formatted t1;
type标明MANAGED_TABLE说明这个表是受监管的。也就是内部表。
2.创建外部表t2
-- 删除外部表t2 drop table if exists t2; -- 创建外部表t2 create external table t2( name string comment '名字', pos string comment '位置', height double comment '身高', weight int comment '体重', age int comment '年龄', nba_age int comment '球龄', nums int comment '出场次数', avgtime double comment '平均时间', attack double comment '进攻能力', defend int comment '防守能力', allstar string comment '是否进入全明星', income bigint comment '薪资' ) comment "NBA球员分析" row format delimited fields terminated by ','; -- 查看表结构 desc formatted t2;
type标明EXTERNAL_TABLE说明这个表是外部表。
3. 将数据导入到两个表之间然后删除
nba.txt可以在excel中制作，导出为带逗号分隔的csv文件，然后使用notepad++转换一下格式，否则在idea下运行脚本会出现中文乱码。 转换方法：notepad++ --> 编辑 --> 档案格式转换 --> 转为unix
-- 上传数据nba.txt到master节点的 /opt/data目录中 -- 数据载入t1,t2 【local表示从本地加载】 load data local inpath '/opt/data/nba.txt' into table t1; load data local inpath '/opt/data/nba.txt' into table t2; -- 查看数据表 select * from t1; select * from t2;
4. 查看HDFS端口9870：删除前
5.查看HDFS上存放的数据
[root@master ~]# hdfs dfs -cat /user/hive/warehouse/nba.db/t1/* [root@master ~]# hdfs dfs -cat /user/hive/warehouse/nba.db/t2/*
6. 删除t1和t2表
drop table if exists t1; drop table if exists t2;
查看删除后的结果：
HDFS上只有t2的数据还在，但是它的元数据已被删除，这里显示表找不到。
数据表t1是内部表：故删除表时，数据也会被删除（适用于Hive完全管理的数据）。
数据表t2是外部表：故删除表时，仅删除元数据，数据仍然存在（适用于已有数据或共享数据）。
1.4 内部表、外部表差异
无论内部表还是外部表，Hive都在Hive Metastore中管理表定义及其分区信息。
删除内部表，会从Metastore中删除表元数据，还会从HDFS中删除其所有数据/文件。
删除外部表，只会从Metastore中删除表的元数据，并保持HDFS位置中的实际数据不变。
实验结论
表类型 数据存放位置 删除表时数据是否删除 适用场景 内部表 (Managed Table) /user/hive/warehouse/数据库名.db/表名/数据和表都会删除 Hive全权管理的表，适用于ETL 外部表 (External Table) 用户指定的HDFS路径 仅删除表的元数据，数据仍然保留 适用于已有数据或多个工具共享
重点总结：
内部表：Hive完全管理，删除表时数据也删除。
外部表：用户管理数据，删除表时数据不会删除。
1.5 内部表、外部表的创建实验
当需要通过Hive完全管理控制表的整个生命周期时，请使用内部表。
当文件已经存在或位于远程位置时，请使用外部表，因为即使删除表，文件也会被保留。
准备数据
在/opt/data目录下准备数据，创建dept.txt文件并添加数据：
[root@master data]# vim dept.txt 100,数学系,2100 200,物理系,2200 300,化学系,2300 400,新闻系,2400 500,软件系,2500
练习1：内部表创建
/* 创建内部表 */ --（1）启动Hive -- 复制dept.txt到master节点下的/opt/data目录下 --（2）显示数据仓库 show databases; --（3）创建一个名为hivedwh的数据仓库，并存放在默认位置： drop database if exists hivedwh cascade; create database if not exists hivedwh; --（4）切换到hivedwh数据仓库 use hivedwh; select current_database(); --（5）创建内部表dept_int create table if not exists dept_int( deptno int, dname string, buildingsno int) row format delimited fields terminated by ','; --（6）查看创建的表 show tables; --（7）向内部表中导入数据 load data local inpath '/opt/data/dept.txt' into table dept_int; --（8）简单查询 select * from dept_int; --（9）查看表的元数据信息 desc formatted dept_int; -- (10)master主机上运行HDFS命令查看HDFS上的数据 /* [root@master ~]# hdfs dfs -ls -R /user/hive/warehouse/hivedwh.db/ [root@master ~]# hdfs dfs -cat /user/hive/warehouse/hivedwh.db/dept_int/dept.txt */ -- (11)删除数据表dept drop table if exists dept_int; -- 查看数据表【发现dept表已经不存在】 show tables ; -- 在HDFS上查看dept数据表【目录】已不存在，因为是内部表，所以元数据和数据都被删除
练习2：外部表创建
/* 创建外部表 */ --（1）创建外部表dept_ext create external table if not exists dept_ext( deptno int, dname string, buildingsno int) row format delimited fields terminated by ','; --（2）查看创建的表 show tables; --（3）向外部表中导入数据 load data local inpath '/opt/data/dept.txt' into table dept_ext; --（4）简单查询 select * from dept_ext; --（5）查看表的元数据信息 desc formatted dept_ext; --(6)在master主机上运行HDFS命令查看HDFS上的数据 /* [root@master ~]# hdfs dfs -ls -R /user/hive/warehouse/hivedwh.db/ [root@master ~]# hdfs dfs -cat /user/hive/warehouse/hivedwh.db/dept_ext/dept.txt */ --(7)删除数据表dept drop table if exists dept_ext; -- 查看数据表【发现dept表已经不存在】 show tables ; -- 在HDFS上查看dept数据表【目录】仍然存在，因为是外部表，所以没有被删除
在创建Hive外部表时，定义的字段与dept.txt中的数据有几个关键性的关系和要求：
数据类型匹配：在创建表时定义的字段类型必须与
dept.txt中相应数据的实际类型相匹配。例如，如果dept.txt中的deptno是整数，那么在Hive表中deptno字段应定义为INT类型。列的顺序：Hive表中字段的顺序应与
dept.txt数据文件中每行数据的列的顺序一致。列的数量：Hive表定义中的字段数量应该与
dept.txt数据文件中每行的列数相匹配。如果数据文件中的列比Hive表中定义的字段多，那么多出的列将被忽略。如果数据文件中的列比Hive表中定义的字段少，那么缺失的列将被视为null。字段分隔符：Hive表定义中指定的字段分隔符（如上例中的空格
'\t'）必须与dept.txt数据文件中实际使用的字段分隔符相匹配。如果分隔符不匹配，Hive将无法正确解析字段。
1.6 向Hive表中添加数据的方法
在Hive中，有两种主流的数据添加方法：LOAD DATA和hdfs dfs -put。这两种方法有以下主要区别：
1. 语法差异：
LOAD DATA命令：
LOCAL这是一个可选关键字，用于指定加载的数据文件位于本地文件系统。如果没有指定
LOCAL，那么默认情况下Hive会从HDFS上加载数据。INPATH 'filepath'：这里的
'filepath'需要替换为您的数据文件路径。这个路径应该是完整的路径，指向包含要加载数据的文件。如果使用LOCAL关键字，这个路径应该是本地文件系统的路径；如果没有使用LOCAL，则应该是HDFS的路径。[OVERWRITE]：这也是一个可选关键字。如果指定了
OVERWRITE，Hive会在加载数据前删除表中现有的所有数据。如果不使用OVERWRITE，新数据将会被追加到表中，不会影响表中现有的数据。INTO TABLE tablename：这里的
tablename是目标Hive表的名称，即您希望将数据加载到哪个表中作用：将数据从文件系统（本地或HDFS）直接加载到Hive表中。
语法：
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablenameHDFS DFS -PUT命令：
/path/to/localfile.txt为本地文件路径。/path/on/hdfs/为HDFS目标路径。用于上传文件到HDFS。
语法：
hdfs dfs -put /path/to/localfile.txt /path/on/hdfs/
2. 使用场景：
LOAD DATA 主要用于从本地文件系统或HDFS向Hive表加载数据。
HDFS DFS -PUT 主要用于将本地文件上传到HDFS。
3. 性能和可扩展性：
LOAD DATA
支持自动并行加载，利用MapReduce框架。
根据表的分区和分桶设置自动调整并行度和资源分配。
适用于高效地加载数据到Hive表。
HDFS DFS -PUT
不支持在Hive自动并行加载。
需要手动并行上传或使用其他工具。
更适合于简单的数据文件上传到HDFS。
根据需求和数据量大小选择合适的方法对于提高效率至关重要。LOAD DATA命令适合高效地将数据加载到Hive表中，而HDFS DFS -PUT命令更适合于上传文件到HDFS。
HDFS dfs -put怎么才能向Hive表中传输数据？
步骤：先本地导入数据，然后创建外部表关联
创建外部表，并指定加载数据的HDFS的路径。
# 创建外部表 CREATE EXTERNAL TABLE IF NOT EXISTS dept_ext_put( deptno INT, dname STRING, buildingsno INT ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/user/hive/warehouse/hivedwh.db/dept_ext_put'; # 注意：此时/user/hive/warehouse/hivedwh.db/dept_ext2/路径下并没有dept.txt文件 Hive 外部表的数据是存储在 HDFS 或其他支持的文件系统中，而不是由 Hive 直接管理。 这意味着，您可以在任何时间点将数据文件放入外部表指定的位置，Hive 会在查询时读取这个位置的数据。
使用HDFS DFS -PUT命令把表上传到建表时指定的HDFS的路径
[root@master ~]# hdfs dfs -put /opt/data/dept.txt /user/hive/warehouse/hivedwh.db/dept_ext_put/
2. Hive分区表
Hive 分区的核心概念与实践指南：
什么是 Hive 分区？
在 Hive 中，分区是一种将大数据集分割为更小、更易管理的数据集的策略。这种分割是通过创建不同的目录来实现的，每个目录代表一个数据集的子集。
分区表的物理存储：
物理上，每个 Hive 分区表对应 HDFS 上的一个特定目录。
这个目录包含了属于该分区的所有数据文件。
在 Hive 的结构中，意味着每个分区都与表下的一个子目录相对应，且该子目录内包含了所有分区数据。
sales_partitioned/ └── 2023-01/ │ ├── data_file_1.csv │ └── data_file_2.csv ├── 2023-02/ │ ├── data_file_3.csv │ └── data_file_4.csv ├── 2023-03/ │ ├── data_file_5.csv │ └── data_file_6.csv └── ...
分区字段的角色：
分区字段不是表中的真实数据字段，它只是用于管理数据的“标识”。
分区字段的值不会存储在数据文件中，而是体现在文件路径上。
为什么要使用分区表？
提高查询效率：查询时只扫描相关分区，而不是整张表。
减少数据扫描：分区裁剪让 Hive 跳过无关数据，加快查询速度。
管理更方便：数据按时间、地区等分类，结构清晰，易维护。
优化存储：分区减少单个文件大小，提高 HDFS 读写性能。
支持并行处理：不同分区的数据可分布存储，提升计算速度。
2.1 分区表的引入、产生背景
现有6份数据文件，分别记录了《王者荣耀》中6种位置的英雄相关信息。
要求：建立数据表t_all_hero，把6份文件同时映射加载。
-- 任务 -- 删除数据库 drop database if exists honor_of_kings cascade ; -- 创建数据库 create database if not exists honor_of_kings; -- 选择数据库 use honor_of_kings; -- 查看当前数据库 select current_database(); -- 创建数据表 create table if not exists t_all_hero( id int, name string, hp_max int, mp_max int, attack_max int, defense_max int, attack_range string, role_main string, role_assist string ) row format delimited fields terminated by "\t"; -- 查看数据表 show tables ; -- 上传6个数据文件到master:/opt/data -- 加载数据到数据表 LOAD DATA LOCAL INPATH '/opt/data/archer.txt' INTO TABLE t_all_hero; LOAD DATA LOCAL INPATH '/opt/data/assassin.txt' INTO TABLE t_all_hero; LOAD DATA LOCAL INPATH '/opt/data/mage.txt' INTO TABLE t_all_hero; LOAD DATA LOCAL INPATH '/opt/data/support.txt' INTO TABLE t_all_hero; LOAD DATA LOCAL INPATH '/opt/data/tank.txt' INTO TABLE t_all_hero; LOAD DATA LOCAL INPATH '/opt/data/warrior.txt' INTO TABLE t_all_hero;
查看HDFS 的web UI
-- 全表查询 select * from t_all_hero; -- 查询role_main主要定位是射手archer并且hp_max最大生命大于6000的数量 select count(*) from t_all_hero where role_main='archer' and hp_max>6000;
思考一下：where语句的背后需要进行全表扫描才能过滤出结果，对于hive来说需要扫描表下面的每一个文件。如果数据文件特别多的话，效率很慢也没必要。本需求中，只需要扫描archer.txt文件即可，如何优化可以加快查询，减少全表扫描呢？
2.2 分区表的概念、创建
当Hive表对应的数据量大、文件多时，为了避免查询时全表扫描数据，Hive支持根据用户指定的字段进行分区，分区的字段可以是日期、地域、种类等具有标识意义的字段。比如把一整年的数据根据月份划分12个月（12个分区），后续就可以查询指定月份分区的数据，尽可能避免了全表扫描查询。
分区表建表语法：
CREATE TABLE table_name ( column1 data_type, column2 data_type) PARTITIONED BY (partition1 data_type, partition2 data_type,......);
需求：以role角色作为分区字段创建静态分区表和动态分区表
针对《王者荣耀》英雄数据，重新创建一张分区表t_all_hero_part，以role角色作为分区字段。
# 创建分区表 create table t_all_hero_part( id int, name string, hp_max int, mp_max int, attack_max int, defense_max int, attack_range string, role_main string, role_assist string ) partitioned by (role string) row format delimited fields terminated by "\t";
注意：
分区字段不能与表中的普通字段同名，否则会导致表结构冲突。
分区字段本身不会存储在数据文件中，但在查询时，它们会作为虚拟字段出现在表结构中。
2.3 分区表数据加载--静态分区
所谓静态分区是在创建表时就已经定义好的分区，插入数据时需要显式指定分区的值。
语法如下：
load data [local] inpath '文件路径' into table tablename partition(分区字段='分区值'...);
"local": 可选参数，表示从本地文件系统加载数据，如果省略该参数，则表示从 HDFS 加载数据。
"inpath": 必选参数，指定待加载数据的路径，可以是本地文件系统路径或 HDFS 路径。
"into table tablename": 必选参数，指定要将数据加载到的目标表名。
"partition(分区字段='分区值'...)": 可选参数，表示将数据加载到表的特定分区中，如果省略该参数，则表示将数据加载到表的非分区部分。
静态加载数据操作如下：把以下数据文件都上传到本地文件系统上/opt/data目录下
[root@master data]# pwd /opt/data [root@master data]# ll 总用量 44 -rw-r--r--. 1 root root 480 1月 4 12:37 archer.txt -rw-r--r--. 1 root root 292 1月 4 19:15 assassin.txt -rw-r--r--. 1 root root 437 1月 4 13:00 hot_hero_skin_price.txt -rw-r--r--. 1 root root 4237 1月 4 19:49 log.txt -rw-r--r--. 1 root root 883 1月 4 19:15 mage.txt -rw-r--r--. 1 root root 1276 1月 4 17:32 nba.txt -rw-r--r--. 1 root root 289 1月 4 19:15 support.txt -rw-r--r--. 1 root root 446 1月 4 19:15 tank.txt -rw-r--r--. 1 root root 207 1月 4 13:31 team_ace_player.txt -rw-r--r--. 1 root root 837 1月 4 19:15 warrior.txt
静态加载数据
-- 将本地路径为 '/opt/data/archer.txt' 的数据 -- 加载到Hdfs上名为 t_all_hero_part 的表中的 'sheshou' 分区中。 -- 注意：不建议使用中文做分区键 load data local inpath '/opt/data/archer.txt' into table t_all_hero_part partition(role='sheshou'); -- 同理 load data local inpath '/opt/data/assassin.txt' into table t_all_hero_part partition(role='cike'); load data local inpath '/opt/data/mage.txt' into table t_all_hero_part partition(role='fashi'); load data local inpath '/opt/data/support.txt' into table t_all_hero_part partition(role='fuzhu'); load data local inpath '/opt/data/tank.txt' into table t_all_hero_part partition(role='tanke'); load data local inpath '/opt/data/warrior.txt' into table t_all_hero_part partition(role='zhanshi');
# -- 在分区表中查询role_main主要定位是射手archer并且hp_max最大生命大于6000的数量 select count(*) from t_all_hero_part where role_main='archer' and hp_max>6000;
# 在master中使用hdfs命令查看分区表里的内容 hdfs dfs -cat /user/hive/warehouse/honor_of_kings.db/t_all_hero_part/role=sheshou/*
查看WEB端的HDFS
查看表内容
-- 使用select查看数据表 select * from t_all_hero_part;
-- 使用select查看数据表中tanke的数据信息 select * from t_all_hero_part where role='tanke';
2.4 分区表数据加载--动态分区
动态分区基础
动态分区定义：在数据加载时，Hive 自动根据数据的值创建相应的分区，无需手动指定，提升管理效率。
核心实现语法：使用
INSERT ... SELECT语句，其中 最后一列的值作为分区字段，Hive 会自动创建并分配数据到相应的分区。
启用和配置动态分区
在使用动态分区之前，必须在Hive会话中设置以下两个参数：
set hive.exec.dynamic.partition=true; # 启用动态分区 set hive.exec.dynamic.partition.mode=nonstrict; # 设置为非严格模式 或者 set hive.exec.dynamic.partition.mode=strict; # 设置为严格模式
动态分区模式
非严格模式（nonstrict mode）：允许所有分区键动态生成，无需静态指定任何分区键。
严格模式（strict mode）：要求
INSERT语句至少有一个分区键静态指定，防止生成过多分区影响性能。
分区列指定方法
1.直接使用列名：当源表和目标表的分区字段名称相同时，可直接使用列名，无需额外处理。
set hive.exec.dynamic.partition=true; # 启用动态分区 set hive.exec.dynamic.partition.mode=nonstrict; # 设置为非严格模式 INSERT INTO TABLE table_name PARTITION (partition_column) SELECT column1, column2, ..., partition_column FROM source_table;
2.使用
as进行重命名：当源表和目标表的分区字段名称不同时，需要使用AS进行重命名，确保数据正确分配到目标分区。set hive.exec.dynamic.partition=true; # 启用动态分区 set hive.exec.dynamic.partition.mode=nonstrict; # 设置为非严格模式 INSERT INTO TABLE table_name PARTITION (partition_column) SELECT column1, column2, ..., source_partition_column as partition_column FROM source_table;
严格模式下的动态分区
在严格模式下，至少一个分区键需静态指定，这有助于防止创建过多分区，避免性能问题。
set hive.exec.dynamic.partition=true; # 启用动态分区 set hive.exec.dynamic.partition.mode=strict; # 设置为严格模式 INSERT INTO TABLE table_name PARTITION (year=2022, month) SELECT column1, column2, ..., month FROM source_table;
注意事项
在非严格模式下，要注意不要创建大量无意义的分区，以免影响性能。
生产环境建议使用严格模式，确保数据加载的稳定性和查询的准确性。
通过这种方式，我们提供了一个关于Hive动态分区全面且结构化的概览，旨在帮助用户更好地理解和实施动态分区，以提高数据处理效率和准确性。
动态分区实验
创建一张新的分区表
-- 启用动态分区 set hive.exec.dynamic.partition=true; -- 设置为非严格模式 set hive.exec.dynamic.partition.mode=nonstrict; -- 选择数据库 use honor_of_kings; -- 删除数据表 drop table if exists t_all_hero_part_dynamic; -- 创建一张新的分区表t_all_hero_part_dynamic create table t_all_hero_part_dynamic( id int, name string, hp_max int, mp_max int, attack_max int, defense_max int, attack_range string, role_main string, role_assist string ) partitioned by (role string) row format delimited fields terminated by "\t"; -- 查看表结构,会发现最后多了一个分区列role select * from t_all_hero_part_dynamic;
partitioned by (role string)不会在数据表中真正创建role列，而是将role作为分区字段，Hive 通过文件目录结构来管理该分区字段，而不是存储在数据文件中。分区字段是虚拟列，查询时可以像普通列一样使用，但它的值来自数据存储路径，而非数据文件本身。
不能在表结构中重复定义分区列，否则会报
Column repeated in partitioning columns错误。因为 分区字段不能与表中的普通字段重名，否则 Hive 无法区分它们的作用。
1.执行静态分区插入数据
insert into table t_all_hero_part_dynamic partition(role='archer') select * from t_all_hero where role_main = 'archer'; insert into table t_all_hero_part_dynamic partition(role='assassin') select * from t_all_hero where role_main = 'assassin'; insert into table t_all_hero_part_dynamic partition(role='mage') select * from t_all_hero where role_main = 'mage'; insert into table t_all_hero_part_dynamic partition(role='support') select * from t_all_hero where role_main = 'support'; insert into table t_all_hero_part_dynamic partition(role='tank') select * from t_all_hero where role_main = 'tank'; insert into table t_all_hero_part_dynamic partition(role='warrior') select * from t_all_hero where role_main = 'warrior';
2.执行动态分区插入数据
在 Hive 中，INSERT INTO ... PARTITION ... 是一种标准的语法
-- 确保 Hive 允许使用动态分区 SET hive.exec.dynamic.partition = true; -- 启用动态分区 SET hive.exec.dynamic.partition.mode = nonstrict; -- 设置为非严格模式（允许所有分区字段动态生成） -- 将数据从 t_all_hero 表插入到 t_all_hero_part_dynamic 分区表中 INSERT INTO TABLE t_all_hero_part_dynamic PARTITION(role) -- 指定 role 作为分区字段 SELECT tmp.*, -- 选择源表 t_all_hero 的所有字段 tmp.role_main AS role -- 将 role_main 作为 role 分区字段插入 FROM t_all_hero tmp; -- 从 t_all_hero 表获取数据
其中：
PARTITION(role)指定了要插入数据的分区列【分区键】是role，而分区列的值则是从SELECT语句中选择的列role_main中获取的。这种语法是固定的，并且在 Hive 中被广泛使用。动态分区插入时，分区值是根据查询返回字段位置自动推断的。
查看WEB端的HDFS
master:9870 192.168.36.100：9870
# 查看表的全部信息内容 select * from t_all_hero_part_dynamic;
-- 按条件查看archer数据 select * from t_all_hero_part_dynamic where role='archer'
# 使用hdfs的命令行的方式查看 hdfs dfs -cat /user/hive/warehouse/honor_of_kings.db/t_all_hero_part_dynamic/role=archer/000000_0
# 查看分区 show partitions t_all_hero_part;
2.5 分区表的本质
Hive的分区表本质是将数据按照指定的列值进行物理上的分隔存储，以提高查询效率和管理数据的灵活性。
非分区表上看起来和分区表好像没多大变化，只不过多了一个分区字段。实际上在底层管理数据的方式发生了改变。这里直接去HDFS查看区别。
非分区表：t_all_hero
分区表：t_all_hero_part
分区的概念提供了一种将Hive表数据分离为多个文件/目录的方法。
不同分区对应着不同的文件夹，同一分区的数据存储在同一个文件夹下。只需要根据分区值找到对应的文件夹，扫描本分区下的文件即可，避免全表数据扫描。
[root@master data]# hdfs dfs -ls -R /user/hive/warehouse/honor_of_kings.db/t_all_hero_part_dynamic drwxr-xr-x - root supergroup 0 2023-01-04 20:49 /user/hive/warehouse/honor_of_kings.db/t_all_hero_part_dynamic/role=archer -rwxr-xr-x 3 root supergroup 499 2023-01-04 20:49 /user/hive/warehouse/honor_of_kings.db/t_all_hero_part_dynamic/role=archer/000000_0 drwxr-xr-x - root supergroup 0 2023-01-04 20:49 /user/hive/warehouse/honor_of_kings.db/t_all_hero_part_dynamic/role=assassin -rwxr-xr-x 3 root supergroup 297 2023-01-04 20:49 /user/hive/warehouse/honor_of_kings.db/t_all_hero_part_dynamic/role=assassin/000000_0 drwxr-xr-x - root supergroup 0 2023-01-04 20:49 /user/hive/warehouse/honor_of_kings.db/t_all_hero_part_dynamic/role=mage -rwxr-xr-x 3 root supergroup 902 2023-01-04 20:49 /user/hive/warehouse/honor_of_kings.db/t_all_hero_part_dynamic/role=mage/000000_0 drwxr-xr-x - root supergroup 0 2023-01-04 20:49 /user/hive/warehouse/honor_of_kings.db/t_all_hero_part_dynamic/role=support -rwxr-xr-x 3 root supergroup 294 2023-01-04 20:49 /user/hive/warehouse/honor_of_kings.db/t_all_hero_part_dynamic/role=support/000000_0 drwxr-xr-x - root supergroup 0 2023-01-04 20:49 /user/hive/warehouse/honor_of_kings.db/t_all_hero_part_dynamic/role=tank -rwxr-xr-x 3 root supergroup 453 2023-01-04 20:49 /user/hive/warehouse/honor_of_kings.db/t_all_hero_part_dynamic/role=tank/000000_0 drwxr-xr-x - root supergroup 0 2023-01-04 20:49 /user/hive/warehouse/honor_of_kings.db/t_all_hero_part_dynamic/role=warrior -rwxr-xr-x 3 root supergroup 845 2023-01-04 20:49 /user/hive/warehouse/honor_of_kings.db/t_all_hero_part_dynamic/role=warrior/000000_0
2.6 静态分区 vs. 动态分区
📌 分区方式
静态分区：插入数据时，必须手动指定分区值，适用于已知分区值的情况。
动态分区：分区值由数据自动生成，适用于分区值未知或数量较多的情况。
📌 分区表创建
无论静态还是动态分区，建表语句相同，表结构相同，都需要指定分区字段（存储在 HDFS 目录）。
区别在数据插入：静态分区手动指定，动态分区自动生成。
2.7 分区列、分区键和分区值的区别
分区列 (Partition Column)：
定义：分区列是在创建带分区的Hive表时，在
PARTITIONED BY子句中指定的列。作用：它们用于物理地组织表的数据。数据根据这些列的值被分割成不同的分区。
例子：假设有一个按年（
year）和月（month）分区的表，year和month就是分区列。分区键 (Partition Key)：
定义：分区键通常指的是分区列的实际值，用于查询和管理数据时标识特定的分区。
作用：在查询或数据操作时，分区键用于引用或定位特定的分区。
例子：如果你在查询中指定
WHERE year = 2022 AND month = 1，那么year = 2022和month = 1就是分区键。分区值 (Partition Value)：
定义：分区值指的是分区列的具体数值。它是数据在分区中如何被分隔和存储的基础。
作用：分区值用于确定数据存储在哪个分区。在文件系统中，每个分区通常按照分区值命名，如目录
year=2022/month=1。例子：如果你有数据属于2022年1月，则这些数据的分区值是2022（对应于
year分区列）和1（对应于month分区列）
小结
分区列是表的结构的一部分，用于定义如何分区。
分区键在查询或操作数据时使用，指向特定分区的分区列及其值。
分区值是分区列在具体数据行或分区中的值，决定了数据的物理存储位置。
在实际使用中，分区键和分区值的概念经常交织在一起，因为分区键实际上是由分区列的分区值构成的。分区列则是定义这些值如何组织数据的结构元素。
2.8 分区表的使用
分区表的使用重点在于：
一、建表时根据业务场景设置合适的分区字段。比如日期、地域、类别等；
二、查询的时候尽量先使用where进行分区过滤，查询指定分区的数据，避免全表扫描。
比如：查询英雄主要定位是射手并且最大生命大于6000的个数。使用分区表查询和使用非分区表进行查询，SQL如下：
--非分区表 全表扫描过滤查询 select count(*) from t_all_hero where role_main="archer" and hp_max >6000; --分区表 先基于分区过滤 再查询 select count(*) from t_all_hero_part where role="sheshou" and hp_max >6000;
想一想：底层执行性能来说，分区表的优势在哪里？
2.9 分区表的注意事项
一、 分区表不是建表的必要语法规则，是一种优化手段表，可选；
二、分区字段不能是表中已有的字段，不能重复；
三、 分区字段是虚拟字段，其数据并不存储在底层的文件中；
四、 分区字段值的确定来自于用户价值数据手动指定（静态分区）或者根据查询结果位置自动推断（动态分区）
五、 Hive支持多重分区，也就是说在分区的基础上继续分区，划分更加细粒度
2.10 多重分区表
通过建表语句中关于分区的相关语法可以发现，Hive支持多个分区字段：PARTITIONED BY (partition1 data_type, partition2 data_type,….)。
多重分区下，分区之间是一种递进关系，可以理解为在前一个分区的基础上继续分区。从HDFS的角度来看就是文件夹下继续划分子文件夹。
比如：把全国人口数据首先根据省进行分区，然后根据市进行划分，再根据区县再划分，此时就是3分区表。
-- 单分区表，按省份分区 CREATE TABLE t_user_province ( id INT, name STRING, age INT ) PARTITIONED BY (province STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; -- 指定逗号 `,` 作为字段分隔符 -- 双分区表，按省份和市分区 CREATE TABLE t_user_province_city ( id INT, name STRING, age INT ) PARTITIONED BY (province STRING, city STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; -- 指定逗号 `,` 作为字段分隔符 -- 三分区表，按省份、市、县分区 CREATE TABLE t_user_province_city_county ( id INT, name STRING, age INT ) PARTITIONED BY (province STRING, city STRING, county STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; -- 指定逗号 `,` 作为字段分隔符
多分区表的数据插入和查询使用
-- 加载数据到单分区表（按省份分区） LOAD DATA LOCAL INPATH '/opt/data/t_user.csv' INTO TABLE t_user_province PARTITION(province='上海市'); -- 加载数据到多分区表（按省、市、区县分区） LOAD DATA LOCAL INPATH '/opt/data/t_user.csv' INTO TABLE t_user_province_city_county PARTITION(province='浙江省', city='杭州市', county='萧山区'); -- 查询浙江省杭州市的数据 SELECT * FROM t_user_province_city_county WHERE province='浙江省' AND city='杭州市';
3. Hive分桶表
3.1 业务场景
Hive分桶表
Hive 分桶（Bucketing） 是一种 数据存储优化技术，通过将数据按某列的哈希值均匀分布到多个桶（文件）中，提高查询效率。
📌 分区 vs. 分桶
分区表：每个分区在 HDFS 上是一个目录（如
/year=2021/）。分桶表：在分区目录下，数据进一步划分到多个桶（文件），每个桶存储部分数据。
📌 分桶原理
哈希分桶：Hive 对指定列计算哈希值，然后取 桶数的余数 来决定数据存放的桶。
桶号计算：桶号 = 哈希值(分桶列) mod 总桶数
实际例子
按姓名分桶（4个桶）：
计算姓名的ASCII 码总和，对4 取余，决定数据存放的桶：
张三→ 哈希值 % 4 = 0 → 存入桶 0李四→ 哈希值 % 4 = 1 → 存入桶 1王五→ 哈希值 % 4 = 2 → 存入桶 2赵六→ 哈希值 % 4 = 3 → 存入桶 3
分桶可加速查询，适用于大规模数据、JOIN优化等场景。
📌 分桶操作
创建分桶表时，需要指定分桶列和桶的数量。
📌 分桶的优势
查询更快：只扫描相关桶，减少数据读取量。
均衡存储：避免数据倾斜，提升计算效率。
管理方便：简化数据存储和处理，优化大规模数据管理。
3.2 分桶表的概念
分桶表也叫做桶表，源自建表语法中bucket单词。是一种用于优化查询而设计的表类型。该功能可以让数据分解为若干个部分易于管理。
在分桶时，我们要指定根据哪个字段将数据分为几桶（几个部分）。
数据分桶原理:
Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
3.3 分桶表的语法
CREATE TABLE table_name ( col1 data_type, col2 data_type, ... ) CLUSTERED BY (col_name) -- 指定分桶字段 INTO N BUCKETS; -- 指定桶的数量
注意:分桶的字段必须是表中已经存在的字段。
3.4 分桶实战1
分桶是由列的哈希值除以桶的个数来决定每条数据划分在哪个桶中。
# 数据准备（id,name,age） [root@master data]# pwd /opt/data [root@master data]# vim stu_info.txt 1,tom,11 2,cat,22 3,dog,33 4,hive,44 5,hbase,55 6,mr,66 7,alice,77 8,scala,88
-- 创建数据库 drop database if exists bucket; create database if not exists bucket; -- 创建一个普通的表 create table psn ( id int, name string, age int ) row format delimited fields terminated by ',';
-- 将数据load到这张表中 load data local inpath '/opt/data/stu_info.txt' into table psn;
-- 创建分桶表 create table psn_bucket ( id int, name string, age int ) clustered by (age) into 4 buckets row format delimited fields terminated by ',';
-- 将数据insert到表psn_bucket中 -- (注意：这里和分区表插入数据有所区别，分区表需要select 和指定分区，而分桶则不需要) insert into psn_bucket select id,name,age from psn;
在HDFS上查看分桶数据
查询数据
我们在linux中使用Hadoop的命令查看一下（与我们猜想的顺序一致）
[root@master data]# hdfs dfs -cat /user/hive/warehouse/honor_of_kings.db/psn_bucket/* # 查看结果 8,scala,88 4,hive,44 7,alice,77 3,dog,33 6,mr,66 2,cat,22 5,hbase,55 1,tom,11 [root@master data]# hdfs dfs -cat /user/hive/warehouse/test.db/psn_bucket/000000_0 # 查看结果 8,scala,88 4,hive,44
# 这里设置的桶的个数是4 数据按照 年龄age%4的余数进行放桶(文件) 11%4 == 3 -----> 000003_0 22%4 == 2 -----> 000002_0 33%4 == 1 -----> 000001_0 44%4 == 0 -----> 000000_0 55%4 == 3 -----> 000003_0 66%4 == 2 -----> 000002_0 77%4 == 1 -----> 000001_0 88%4 == 0 -----> 000000_0 # ...以此类推
3.5 分桶实战2
现有美国2021-1-28号，各个县county的新冠疫情累计案例信息，包括确诊病例和死亡病例，数据格式如下所示：
2021-01-28,Juneau City and Borough,Alaska,02110,1108,3 2021-01-28,Kenai Peninsula Borough,Alaska,02122,3866,18 2021-01-28,Ketchikan Gateway Borough,Alaska,02130,272,1 2021-01-28,Kodiak Island Borough,Alaska,02150,1021,5 2021-01-28,Kusilvak Census Area,Alaska,02158,1099,3 2021-01-28,Lake and Peninsula Borough,Alaska,02164,5,0 2021-01-28,Matanuska-Susitna Borough,Alaska,02170,7406,27 2021-01-28,Nome Census Area,Alaska,02180,307,0 2021-01-28,North Slope Borough,Alaska,02185,973,3 2021-01-28,Northwest Arctic Borough,Alaska,02188,567,1 2021-01-28,Petersburg Borough,Alaska,02195,43,0
字段含义如下： count_date（统计日期）,county（县）,state（州）,fips（县编码code）,cases（累计确诊病例）,deaths（累计死亡病例）。
根据state州把数据分为5桶，建表语句如下：
-- 创建数据库 drop database if exists itcast cascade; create database if not exists itcast; use itcast; -- 创建一个名为t_usa_covid19_bucket的分桶表，用于存储美国covid-19疫情数据 create table if not exists itcast.t_usa_covid19_bucket( count_date string, -- 日期字段，记录数据的日期 county string, -- 县级行政单位 state string, -- 州级行政单位 fips int, -- fips代码，美国用于区分各县的唯一代码 cases int, -- 确诊病例数 deaths int -- 死亡病例数 ) clustered by(state) -- 通过state字段进行分桶 into 5 buckets; -- 数据分成5个桶，用于更有效的查询和存储
在创建分桶表时，还可以指定分桶内的数据排序规则
-- 根据state州分为5桶 每个桶内根据cases确诊病例数倒序排序 create table if not exists itcast.t_usa_covid19_bucket_sort( count_date string, -- 日期字段，记录数据的日期 county string, -- 县级行政单位 state string, -- 州级行政单位 fips int, -- fips代码，美国用于区分各县的唯一代码 cases int, -- 确诊病例数 deaths int -- 死亡病例数 ) clustered by(state) -- 通过state字段进行分桶 sorted by (cases desc) -- 在每个桶内根据cases字段进行倒序排序 into 5 buckets; -- 数据分成5个桶，用于更有效的查询和存储
3.4 分桶表的数据加载
-- step1:开启分桶的功能 从Hive2.0开始不再需要设置 set hive.enforce.bucketing=true; -- step2:创建普通hive表itcast.t_usa_covid19中，并加载数据 CREATE TABLE if not exists itcast.t_usa_covid19 ( count_date string, county string, state string, fips int, cases int, deaths int ) row format delimited fields terminated by ","; -- 方法一：将源数据上传到HDFS，t_usa_covid19表对应的路径下 hdfs dfs -put /opt/apps/hive/data/us-covid19-counties.dat \ /user/hive/warehouse/itcast.db/t_usa_covid19 -- 方法二：使用LOAD DATA命令将数据从本地文件系统导入到Hive表中 LOAD DATA LOCAL INPATH '/opt/apps/hive/data/us-covid19-counties.dat' INTO TABLE itcast.t_usa_covid19; -- step3:使用insert+select语法将数据加载到分桶表中 insert into t_usa_covid19_bucket select * from t_usa_covid19; -- step4:使用insert+select语法将数据加载到排序的分桶表中 insert into t_usa_covid19_bucket_sort select * from t_usa_covid19;
到HDFS上查看t_usa_covid19_bucket底层数据结构可以发现，数据被分为了5个部分。
并且从结果可以发现，只要hash_function(bucketing_column)一样的，就一定被分到同一个桶中。
3.5 分桶表与分区表的区别
（1）从表现形式上：
分区表是一个目录，分桶表是文件
（2）从创建语句上：
分区表使用
partitioned by子句指定，以指定字段为伪列，需要指定字段类型分桶表由
clustered by子句指定，指定字段为真实字段，需要指定桶的个数
（3）从数量上：
分区表的分区个数可以增长，分桶表一旦指定，不能再增长
（4）从作用上：
分区避免全表扫描，根据分区列查询指定目录提高查询速度
分桶保存分桶查询结果的分桶结构（数据已经按照分桶字段进行了hash散列）
分桶表数据进行抽样和JOIN时可以提高MR程序效率
3.5 分桶表的使用好处
和非分桶表相比，分桶表的使用好处有以下几点：
1、 基于分桶字段查询时，减少全表扫描
-- 基于分桶字段state查询来自于New York州的数据 -- 不再需要进行全表扫描过滤 -- 根据分桶的规则hash_function(New York) mod 5计算出分桶编号 -- 查询指定分桶里面的数据就可以找出结果,此时是分桶扫描而不是全表扫描 select * from t_usa_covid19_bucket where state="New York";
2、 JOIN时可以提高MR程序效率，减少笛卡尔积数量
例如，假设有两个表A和B，都根据相同的列C进行了分桶操作。如果要对这两个表进行JOIN操作，可以将A表和B表中具有相同C列值的桶进行JOIN操作。这样就可以避免对A表和B表所有数据进行笛卡尔积操作，减少JOIN操作的数据量，提高JOIN操作的效率。
3、 分桶表数据进行抽样
当数据量特别大时，对全体数据进行处理存在困难时，抽样就显得尤其重要了。抽样可以从被抽取的数据中估计和推断出整体的特性，是科学实验、质量检验、社会调查普遍采用的一种经济有效的工作和研究方法。
对于分桶表数据进行抽样时，同一个桶内的数据具有相同的值，采样时可以选择每个桶的一部分数据进行采样，这样可以更好地保留数据的特征，提高采样的代表性。这样做可以减少采样误差，提高采样结果的准确性。
为了深入理解Hive中的表结构优化，本实验旨在通过创建和查询三种不同类型的Hive表（普通表、分区表、分区且分桶表）来展示它们在数据存储和查询性能上的区别。以下是详细的实验任务：