Hive数仓操作（十七）

一、Hive的存储

一、Hive 四种存储格式

在 Hive 中，支持四种主要的数据存储格式，每种格式有其特点和适用场景，不过一般只会使用`Text` 和 `ORC` ：

1. `Text`

说明：Hive 的默认存储格式。
存储方式：行存储。
优点：简单易用，可以通过 LOAD DATA 直接加载数据。
缺点：占用空间较大，读取和解析速度较慢。

2. Sequence

说明：以序列格式存储数据。
存储方式：行存储，使用键值对（key-value）方式存储数据。
优点：在某些情况下支持高效的压缩。
缺点：占用空间比 Text 格式要大。

3. RC (Record Columnar)

说明：由 Facebook 创建的一种列存储格式。
存储方式：列存储，采用懒加载存储和管理数据。
优点：对每一行的数据进行单独压缩；查询时只读取需要的数据，提高查询速度。
缺点：相较于 Text 格式，导入数据不够方便。

4. `ORC` (Optimized Row Columnar)

说明：在公司工作中使用最广泛的存储格式，是 RC 的优化版本。
存储方式：列存储，具备懒加载特点。
优点：优化了文件的压缩和存储，查询性能非常高。
缺点：同样不支持直接使用 LOAD DATA 导入。

注意事项

对于 Sequence、RC 和 ORC 格式，不能直接使用 LOAD DATA 命令导入数据。需要先将数据导入到一个 Text 格式的表中，再使用 INSERT OVERWRITE TABLE 的方式将数据复制到目标表中。

二、Hive 行列存储

在这里插入图片描述

1. 行存储的特点（TEXT）

查询性能：
- 当查询需要满足条件的一整行数据时，行存储具有优势。只需找到一个值，其余的值都存储在相邻的位置，可以快速访问。

2. 列存储的特点（ORC）

数据读取效率：
- 在查询仅需要少数几个字段时，列存储能显著减少读取的数据量，因为每个字段的数据聚集存储。
数据类型一致性：
- 每个字段的数据类型相同，这使得列式存储能够针对性地设计更高效的压缩算法，优化存储空间。

三、Hive 压缩格式

1. TEXTFILE

压缩算法：可使用 Gzip、Bzip2 等压缩算法。
适用场景：适合需要频繁导入和导出的小数据量表格，压缩时一般使用 Gzip 在上传HDFS前压缩。

2. ORC

默认压缩：ORC 格式通常使用 Zlib 压缩。如果使用其他压缩格式，可能会导致 ORC 文件实际存储空间增大。
适用场景：适合处理非常大的数据集。ORC 格式的读取速度通常较快，默认使用 Zlib 进行压缩，效果优于 Snappy，一般建表时进行压缩。

压缩示例

以下是一个使用 ORC 格式创建 Hive 表的示例：

CREATE TABLE IF NOT EXISTS emp_orc_ys (
    empno INT,
    ename STRING,
    job STRING,
    mgr INT,
    hiredate STRING,
    sal FLOAT,
    comm FLOAT,
    deptno INT
)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'  -- 使用制表符作为字段分隔符
STORED AS ORC
TBLPROPERTIES ("orc.compress" = "SNAPPY");  -- 改变默认压缩方式，使用 Snappy 压缩

四、Hive 建表手册

创建表的基本语法

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name (
    col1 type1,
    col2 type2,
    col3 type3,
    ...
    colN typeN
)
[PARTITIONED BY (part_col1 part_type1, part_col2 part_type2, ...)]
[CLUSTERED BY (col_name1, col_name2, ...)] 
[ROW FORMAT DELIMITED row_format]
[ROW FORMAT SERDE 'serde_name'] 
[FIELDS TERMINATED BY 'delimiter'] -- 指定字段分隔符
[COLLECTION ITEMS TERMINATED BY 'delimiter'] -- 用于数组、映射等
[MAP KEYS TERMINATED BY 'delimiter'] -- 用于映射
[STORED AS file_format]
[LOCATION 'hdfs_path']
[TBLPROPERTIES (compress)];

参数说明

EXTERNAL：可选，表示创建外部表。数据存储在外部位置，删除表时不会删除数据。
IF NOT EXISTS：可选，若表已经存在则不执行创建。
db_name.：可选，指定数据库名称。
table_name：表的名称。
col1, col2, … colN：列的名称和数据类型。
PARTITIONED BY：用于指定分区列及其数据类型。
CLUSTERED BY：指定分桶列，通常和分桶数量一起使用。
ROW FORMAT DELIMITED：指定行格式，通常用于定义分隔符等。
FIELDS TERMINATED BY：指定字段分隔符，例如 FIELDS TERMINATED BY ',' 表示使用逗号作为分隔符。
COLLECTION ITEMS TERMINATED BY：如果表中包含数组或映射，指定集合项的分隔符。
MAP KEYS TERMINATED BY：指定映射键的分隔符。
ROW FORMAT SERDE：可以使用自定义的序列化和反序列化方法。
STORED AS：指定存储文件的格式（加 TEXTFILE、ORC、SEQUENCEFILE等）。
LOCATION：指定表在 HDFS 上的存储路径。
TBLPROPERTIES ：指定表在 HDFS 上的压缩方式。

示例

以下是多元复杂建表示例，创建一个包含数组和映射的 Hive 表：

CREATE EXTERNAL TABLE IF NOT EXISTS my_database.my_table (
    id INT,
    name STRING,
    age INT,
    scores ARRAY<INT>,  -- 数组类型
    attributes MAP<STRING, STRING>  -- 映射类型
)
PARTITIONED BY (country STRING)
CLUSTERED BY (id) INTO 10 BUCKETS
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '|'
MAP KEYS TERMINATED BY ':'
STORED AS ORC
LOCATION 'hdfs://hdfs_path/my_table/'
TBLPROPERTIES ("orc.compress" = "SNAPPY");