七，Hive篇，分区表和分区表详细篇

凡梦_leo

已于 2024-07-29 20:43:18 修改

阅读量1.4k

点赞数 39

分类专栏： Hadoop 文章标签： hive hadoop 数据仓库数据库大数据 python sql

于 2024-07-29 20:39:23 首次发布

本文链接：https://blog.csdn.net/qq_55006020/article/details/140756563

版权

Hadoop 专栏收录该内容

10 篇文章 1 订阅

订阅专栏

学习：知识的初次邂逅
复习：知识的温故知新

一，分区表

1，分区表的优势：

分区表在Hive中具有多个显著的优势，这些优势主要体现在查询性能提升、数据管理便捷性、以及数据生命周期管理的支持等方面。以下是分区表优势的详细阐述：

1. 查询性能提升

减少数据扫描量：分区表通过将数据按照某个或多个列的值划分为不同的分区，每个分区存储为独立的目录。在查询时，Hive可以仅扫描与查询条件相匹配的分区，而无需扫描整个表，从而大大减少了数据扫描量。
提高查询效率：由于分区表减少了数据扫描量，因此查询所需的时间也相应减少，提高了查询效率。这对于处理大规模数据集尤为重要。

2. 数据管理便捷性

更好的数据组织：分区表允许用户根据业务需求将数据划分为更小的数据集，每个分区都对应着HDFS上的一个独立目录。这种组织方式使得数据更加有序，便于管理和维护。
灵活的分区策略：用户可以根据需要选择分区列和分区策略，如按时间、地区、业务类型等进行分区。这种灵活性使得分区表能够适用于多种不同的业务场景。
支持动态分区：Hive还支持动态分区功能，即在插入数据时自动根据分区字段的值将数据分配到相应的分区中。这进一步简化了数据管理的复杂性。

3. 数据生命周期管理的支持

数据备份与归档：通过分区表，用户可以更方便地根据数据的生命周期进行备份和归档操作。例如，可以定期将旧数据分区迁移到冷存储中，以节省存储空间并提高查询效率。
数据删除与清理：当数据不再需要时，用户可以通过删除整个分区来快速清理数据，而无需逐条删除记录。这大大提高了数据清理的效率。

4. 其他优势

支持复杂查询：分区表还支持复杂的查询操作，如聚合、排序和过滤等。由于分区表减少了数据扫描量，这些操作的执行速度也会相应提高。
与Hadoop生态系统集成：Hive作为Hadoop生态系统的一部分，分区表可以与其他Hadoop组件（如HDFS、YARN、MapReduce等）无缝集成，实现数据的高效处理和分析。

分区表在Hive中具有查询性能提升、数据管理便捷性、以及数据生命周期管理的支持等多方面的优势。这也是我们使用分区表的原因所在;

2，创建分区表

准备数据

-- 0 准备数据
-- vim /root/orders_data.csv
-- 1001,2024-07-01,2001,产品A,5,100
-- 1002,2024-07-01,2002,产品B,3,150
-- 1003,2024-07-02,2001,产品A,2,100
-- 1004,2024-07-02,2003,产品C,7,200
-- 1005,2024-07-03,2002,产品B,4,150

把准备好的数据放入到linux中

创建普通表作为分区表的数据源

drop table if exists tb_orders;   --清空表
CREATE TABLE IF NOT EXISTS tb_orders (
    order_id INT COMMENT '订单ID',
    dt STRING COMMENT '订单日期',
    customer_id INT COMMENT '客户ID',
    product STRING COMMENT '产品名称',
    quantity INT COMMENT '数量',
    price DECIMAL(10,2) COMMENT '单价'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','  --指定分隔符 以文本分割
STORED AS TEXTFILE;   -- 数据目录形式存储


load data local inpath '/root/hive_data/orders_data.csv' into table tb_orders;  --加载数据

select * from tb_orders;  -- 查询有无加载成功

创建分区表

CREATE TABLE IF NOT EXISTS tb_orders_part (
    order_id INT COMMENT '订单ID',
    customer_id INT COMMENT '客户ID',
    product STRING COMMENT '产品名称',
    quantity INT COMMENT '数量',
    price DECIMAL(10,2) COMMENT '单价'
)
partitioned by (dt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

1,静态加载数据

-- 加载数据
-- 第一种方式 静态加载
insert into tb_orders_part partition(dt="2024-07-01")
select order_id,
       customer_id,
       product,
       quantity,
       price
       from tb_orders where dt="2024-07-01";

-- 查询数据
select * from tb_orders_part;

2,动态加载数据

-- 第二种方式 动态加载
create table tb_orders_part_2(
    order_id INT COMMENT '订单ID',
    customer_id INT COMMENT '客户ID',
    product STRING COMMENT '产品名称',
    quantity INT COMMENT '数量',
    price DECIMAL(10,2) COMMENT '单价'
)
partitioned by (dt string)  --指定分区的字段
row format delimited fields terminated by ','  -- 指定分隔符
stored as textfile ;  --指定以目录存储

    -- 动态数据之前 需要关闭严格模式
    set hive.exec.dynamic.partition.mode=nonstrict;
--  动态插入数据
insert into tb_orders_part_2 partition(dt)
select
order_id, customer_id, product, quantity, price,
dt
from tb_orders;

3,多级别分区

多级别分区需要严格模式
多级别分区需要具有级联关系

-- 动态加载 多级别分区
create table tb_orders_part_3(
    order_id INT COMMENT '订单ID',
    customer_id INT COMMENT '客户ID',
    product STRING COMMENT '产品名称',
    quantity INT COMMENT '数量',
    price DECIMAL(10,2) COMMENT '单价'
)
partitioned by (year string,month string,day string)   -- 指定分区的取数
row format delimited fields terminated by ','          -- 指定文本的分隔符
stored as textfile ;    -- 存储到目录上

-- 动态加载 插入数据 插入数据之前要关闭严格模式
set hive.exec.dynamic.partition.mode=nonstrict;

insert into tb_orders_part_3 partition (year,month, day)
select
    order_id,
    customer_id,
    product,
    quantity,
    price,
    substr(dt,0,4)  as   year,
    substr(dt,5,7) as    month,
    substr(dt,8) as   day
from tb_orders;

二，分桶表

1,分桶表概述

分桶表是数据仓库HIVE中的一种特殊的表结构;
他吧数据集中的记录根据某一列或者多列的值进行哈希处理,在模运算之后,然后放进不同的桶中;
每个桶都包含了表中部分数据;
桶的数量在创建表的时候确定;

2,分桶表的作用

提高查询性能; 数据被拆为多个桶,所以每个桶的数据量相对较小,查询只需要根据读取和处理特定的桶,从而减少了io操作和数据传输量,以达到提高查询性能的作用;
支持更精确的数据过滤和聚合; 由于数据被分为了多个桶,所以可以根据桶的数量和分布来跟精确的过滤数据;
优化数据抽样,分桶表支持更高效的数据抽样;

3,分桶表的优势

查询效率高;
更好的数据管理和维护;

4,创建分桶表

1,准备数据

/*
小说表 story.txt
人物表 person.txt
*/
-- 打开虚拟机 找一个目录/root/hive_data
/*
cd /root/hive_data
vim story.txt
vim person.txt

2,分别创建2个不同的基础表

创建小说表


-- 第一步 建立小说表
create table tb_story(
    sid int,
    name string
)
row format delimited fields terminated by ',';  --指定分隔符

-- 1.1插入数据
load data local inpath '/root/hive_data/story.txt' into table tb_story;

-- 1.2查询数据
select sid,name from tb_story;

创建人物表

-- 第二步 创建人物基本表
create table tb_person(
    pid int,
    name string,
    sid int
)
row format delimited fields terminated by ',';

load data local inpath '/root/hive_data/person.txt' into table tb_person;

select pid, name, sid from tb_person;

3,创建小说分桶表

在创建表的时候指定分桶的列和分的桶数量

-- 创建小说分桶表
create table tb_story_bucket(
    sid int,
    name string
)
clustered by (sid) into 3 buckets;
-- row format delimited fields terminated by ',';  --指定文本分隔符


insert into tb_story_bucket select sid,name from tb_story;

select * from tb_story_bucket;

4,创建人物分桶表

指定排序的列 sorted by


-- 创建人物分桶表
drop table tb_person_bucket;
create table tb_person_bucket(
    pid int,
    name string,
    sid int
)
clustered by (sid) sorted by (sid) into 3 buckets ;

insert into tb_person_bucket select pid, name, sid from tb_person;

select * from tb_person_bucket;

5,小说分桶表和人物分桶表的级连查询

-- 人物分桶表和小说分桶表 级练查询
select * from tb_story_bucked_sorted s join tb_person_bucket p where s.sid=p.sid;

三,分区表和分桶表的区别

分区表是分目录

分桶表是分文件

1. 数据组织方式

分区表：分区表是将表中的数据按照某列或某些列的值进行划分，每个分区实际上是一个独立的文件夹（或目录），里面存储了属于该分区的所有数据。分区表的创建需要手动指定分区键，并根据分区键的值来创建和管理分区。
分桶表：分桶表则是将表中的数据按照某列或某些列的哈希值进行划分，将数据分散存储到固定数量的桶中。每个桶包含了表中的一部分数据，且桶的数量在创建表时就已经确定。分桶表的数据组织更加细致，每个桶中的数据都经过哈希处理，以确保数据的均匀分布。

2. 数据管理方式

分区表：分区表的管理相对简单，主要通过添加、删除或修改分区来实现。由于每个分区都是一个独立的文件夹，因此可以很方便地对单个分区进行数据的加载、查询或删除操作。分区表还支持多级分区，可以根据多个字段进行数据的细分。
分桶表：分桶表的管理相对复杂一些，因为桶的数量和分布是由系统自动根据哈希算法确定的。用户无法直接对桶进行操作，但可以通过查询语句来指定要查询的桶或桶的范围。分桶表的优势在于能够提供更高效的查询性能和数据抽样能力。

3. 应用场景

分区表：分区表适用于数据量巨大且需要按照特定列进行数据划分的场景。例如，在日志分析系统中，可以按照日期对日志数据进行分区，以便快速查询特定时间段的日志数据。分区表还可以有效避免全表扫描，提高查询效率。
分桶表：分桶表适用于需要对数据进行更细致划分以提高查询性能和数据抽样能力的场景。例如，在电商数据分析中，可以按照用户ID对订单数据进行分桶，以便快速查询特定用户的订单信息或进行用户行为分析。分桶表还可以提高JOIN操作的效率，因为可以将JOIN操作限制在特定的桶之间进行。