Hive三种建表语句详解与使用技巧_全面掌握Hive建表方法

Hive 是基于 Hadoop 的数据仓库工具，支持使用 SQL 查询存储在 HDFS 上的大规模数据。在 Hive 中，建表语句是数据建模和查询的基础。以下是 Hive 中三种常见的建表语句的详解与使用技巧：

1. 内部表（Managed Table）

特点

• 数据由 Hive 管理，表删除时，HDFS 上的数据也会被删除。
• 默认存储位置为 Hive 仓库目录（hive.metastore.warehouse.dir）。
• 适用于需要完全由 Hive 管控的数据。

建表语句

CREATE TABLE table_name (
    column1_name column1_type,
    column2_name column2_type,
    ...
)
[ROW FORMAT DELIMITED
 FIELDS TERMINATED BY ','
 LINES TERMINATED BY '\n']
[STORED AS file_format];

示例

CREATE TABLE employee (
    id INT,
    name STRING,
    age INT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

使用技巧

• 适用场景：适合临时表或需要频繁删除和重建的表。
• 优化建议：
  • 使用分区表（Partitioned Table）优化查询性能，例如按日期分区。
  • 结合桶表（Bucketed Table）提高数据采样效率。

2. 外部表（External Table）

特点

• 数据由用户管理，表删除时，HDFS 上的数据不会被删除。
• 适用于需要与外部系统（如 HBase、Kafka）或已有 HDFS 数据集成的场景。
• 支持动态加载数据，无需重新加载表。

建表语句

CREATE EXTERNAL TABLE table_name (
    column1_name column1_type,
    column2_name column2_type,
    ...
)
[ROW FORMAT DELIMITED
 FIELDS TERMINATED BY ','
 LINES TERMINATED BY '\n']
[STORED AS file_format]
LOCATION 'hdfs_path';

示例

CREATE EXTERNAL TABLE logs (
    log_id INT,
    log_message STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE
LOCATION '/user/hive/external/logs';

使用技巧

• 适用场景：适合存储原始日志数据、备份数据或与其他系统共享的数据。
• 优化建议：
  • 使用 LOCATION 指定数据路径，避免数据丢失。
  • 配合 ALTER TABLE 动态添加分区，减少数据移动。

3. 分区表（Partitioned Table）

特点

• 数据按分区存储，每个分区对应一个目录。
• 查询时可通过分区裁剪（Partition Pruning）减少扫描数据量，提高性能。
• 支持内部表和外部表。

建表语句

CREATE TABLE table_name (
    column1_name column1_type,
    column2_name column2_type,
    ...
)
PARTITIONED BY (partition_column_name partition_column_type, ...)
[ROW FORMAT DELIMITED
 FIELDS TERMINATED BY ','
 LINES TERMINATED BY '\n']
[STORED AS file_format];

示例

CREATE TABLE sales (
    product_id INT,
    amount DOUBLE
)
PARTITIONED BY (sale_date STRING)
STORED AS PARQUET;

使用技巧

• 适用场景：适合按时间、地区等维度频繁查询的数据。
• 优化建议：
  • 分区字段选择高基数字段（如日期、用户 ID）。
  • 使用动态分区插入数据时，设置 hive.exec.dynamic.partition.mode=nonstrict。
  • 结合桶表进一步优化查询性能。

对比与选择

| 特性 | 内部表 | 外部表 | 分区表 |
|------------------|-----------------------------|-----------------------------|-----------------------------|
| 数据管理 | Hive 管理 | 用户管理 | Hive 管理（分区目录） |
| 数据删除 | 表删除时数据被删除 | 表删除时数据保留 | 表删除时分区数据被删除 |
| 适用场景 | 临时表、中间结果 | 原始数据、外部系统集成 | 大规模数据按维度查询 |
| 性能优化 | 无 | 无 | 分区裁剪、减少扫描量 |

综合使用技巧

1. 分区与桶表结合：
   • 分区表按日期或地区分区，桶表按用户 ID 或哈希值分桶，进一步提升查询效率。
2. 存储格式选择：
   • 使用 ORC 或 Parquet 格式存储数据，支持压缩和列式存储，减少 I/O 开销。
3. 动态分区优化：
   • 启用动态分区插入时，设置 hive.exec.max.dynamic.partitions 和 hive.exec.max.dynamic.partitions.pernode 参数，避免分区过多导致任务失败。
4. 表属性配置：
   • 使用 TBLPROPERTIES 设置表属性，例如 orc.compress=SNAPPY 或 parquet.compression=SNAPPY。

• 内部表：适合临时数据和完全由 Hive 管控的场景。
• 外部表：适合与外部系统集成或需要保留原始数据的场景。
• 分区表：适合大规模数据按维度查询的场景，可显著提升性能。

根据业务需求选择合适的建表方式，并结合分区、桶表和存储格式优化，可以充分发挥 Hive 的数据处理能力。

// 来源：https://www.nzw6.com