hive内外表、分区表及桶表

最新推荐文章于 2025-06-15 16:53:02 发布

原创最新推荐文章于 2025-06-15 16:53:02 发布 · 584 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#hive #内外表

hive 专栏收录该内容

28 篇文章

订阅专栏

本文详细介绍了Hive中的数据库、表、分区和桶的概念及其应用。对比内部表与外部表的区别，并提供了创建和使用的实例。此外还阐述了如何通过分区和桶提升查询效率。

hive库、表、分区、桶的一些概念：
Databases：数据库，概念等同于关系型数据库的Schema；
Tables：表，概念等同于关系型数据库的表；
Partitions：分区，概念类似于关系型数据库的表分区，便于提高效率；
Buckets (or Clusters)：分桶，同一个分区内的数据还可以细分，将相同的KEY再划分至一个桶中，
这个有点类似于HASH分区，只不过这里是HASH分桶，也有点类似子分区吧，进一步提高效率；

Hive中的表分为内部表(MANAGED_TABLE)和外部表(EXTERNAL_TABLE)。默认为内部表；
区别：内部表DROP时候会删除HDFS上的数据;
外部表DROP时候不会删除HDFS上的数据;
内部表适用场景：适用于Hive中间表、结果表、以及不需要从外部（如本地文件、HDFS）load数据的情况。或者从外部或者
其他表insert到内部表中。
外部表适用场景：源表，需要定期将外部数据映射到表中。
适用场景举例说明：每天将收集到的网站日志定期流入HDFS文本文件，一天一个目录，
在Hive中建立外部表作为源表，通过添加分区的方式，将每天HDFS上的原始日志映射到外部表的天分区中，
在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过SELECT+INSERT进入内部表。

简单内表创建：
create table dd (name string , age string) location '/input/table_data'; 不指定external默认创建内部表
并且会在hdfs上新建一个dd表的数据存放地若不指定location会默认在/user/hive/warehouse/目录对应的库下创建。
load data inpath '/input/data' into table dd; 加载数据到表中。

外部表的简单创建：
create external table aa (name string , age string); 会在/user/hive/warehouse/对应的库下面建一个表目录
load data inpath '/input/edata' into table aa;
删除这个外部表后，
/user/hive/warehouse/对应的库下面建一个表目录下的数据不会删除，但是/input/edata/下的数据在上一步load后已经没有了！
数据的位置发生了变化！本质是load一个hdfs上的数据时会转移数据！

小结hive内外表区别

1、在导入数据到外部表，数据并没有移动到自己的数据仓库目录下(如果指定了location的话)，
也就是说外部表中的数据并不是由它自己来管理的！而内部表则不一样；

2、在删除内部表的时候，Hive将会把属于表的元数据和数据全部删掉；
而删除外部表的时候，Hive仅仅删除外部表的元数据，数据是不会删除的！

3. 在创建内部表或外部表时加上location 的效果是一样的，只不过表目录的位置不同而已，
加上partition用法也一样，只不过表目录下会有分区目录而已，
load data local inpath直接把本地文件系统的数据上传到hdfs上，
有location上传到location指定的位置上，没有的话上传到hive默认配置的数据仓库中。
加入分区的原因：
1、加入分区避免Hive Select查询中扫描整个表内容，会消耗很多时间做没必要的工作。
（例如每一天的日志存放在一个分区中，这样根据特定的日期查询）

2、一个表可以拥有一个或者多个分区，每个分区以文件夹的形式单独存在表文件夹的目录下。

桶表
对于每一个表（table）或者分区， Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。
Hive也是针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

桶是以文件的形式存放在表或者分区的目录下。

把表（或者分区）组织成桶（Bucket）有两个理由：
（1）获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。
具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接（Map-side join）高效的实现。
比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。
那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。
（2）使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，
如果能在数据集的一小部分数据上试运行查询，会带来很多方便。

分区表创建
CREATE EXTERNAL TABLE IF NOT EXISTS data_table(
ROWKEY        STRING,
STATION       INT,
MONTH         INT,
DAY           INT,
HOUR          INT,
MINUTE        INT
)
PARTITIONED BY (YEAR INT)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\n’
STORED AS TEXTFILE;

关键字STORED AS：指定表在HDFS上的文件存储格式，可选的文件存储格式有：
TEXTFILE：文本默认值.SEQUENCEFILE:二进制序列文件。RCFILE、ORC、PARQUET：
几种列式存储格式文件。关键字LOCATION：指定表在HDFS上的存储位置
hiveSQL和mysql语法相像，对于大小写不敏感，包括函数。
CREATE EXTERNAL TABLE test1 (
id                    INT,
ip                    STRING COMMENT '访问者IP',
avg_view_depth        DECIMAL(5,1),
bounce_rate           DECIMAL(6,5)
) COMMENT '表详情注释'
PARTITIONED BY (day STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS textfile
LOCATION 'hdfs://user/hive/warehose/';

关键字EXTERNAL：表示该表为外部表，如果不指定EXTERNAL关键字，则表示内部表 .
关键字COMMENT：为表和列添加注释。
关键字PARTITIONED BY：表示该表为分区表，分区字段为day,类型为string。
关键字ROW FORMAT DELIMITED：指定表的分隔符，通常后面要与以下关键字连用：
FIELDS TERMINATED BY ','：指定每行中字段分隔符为逗号。
LINES TERMINATED BY '\n'：指定行分隔符
COLLECTION ITEMS TERMINATED BY ',' :指定集合中元素之间的分隔符。
MAP KEYS TERMINATED BY ':'：指定数据中Map类型的Key与Value之间的分隔符，