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）

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

2. 列存储的特点（ORC）

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

三、Hive 压缩格式

1. TEXTFILE

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

2. ORC

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

压缩示例

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

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

四、Hive 建表手册

创建表的基本语法

CREATE[EXTERNAL]TABLE[IFNOTEXISTS][db_name.]table_name (
    col1 type1,
    col2 type2,
    col3 type3,...
    colN typeN
)[PARTITIONED BY(part_col1 part_type1, part_col2 part_type2,...)][CLUSTEREDBY(col_name1, col_name2,...)][ROW FORMAT DELIMITED row_format][ROW FORMAT SERDE 'serde_name'][FIELDSTERMINATEDBY'delimiter']-- 指定字段分隔符[COLLECTION ITEMS TERMINATEDBY'delimiter']-- 用于数组、映射等[MAP KEYSTERMINATEDBY'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 TABLEIFNOTEXISTS my_database.my_table (
    id INT,
    name STRING,
    age INT,
    scores ARRAY<INT>,-- 数组类型
    attributes MAP<STRING, STRING>-- 映射类型)
PARTITIONED BY(country STRING)CLUSTEREDBY(id)INTO10 BUCKETS
ROW FORMAT DELIMITED
FIELDSTERMINATEDBY','
COLLECTION ITEMS TERMINATEDBY'|'
MAP KEYSTERMINATEDBY':'
STORED AS ORC
LOCATION 'hdfs://hdfs_path/my_table/'
TBLPROPERTIES ("orc.compress"="SNAPPY");