Hive加载数据与数据null值处理

背景：load数据文件的数据流转的雏形通常是：
业务库 -> 数据文件 -> load进hive -> ods层
这里会面临最基础的两个问题：

  1. 通常我们需要一个跳板层，即将数据文件Load进stage层(text)，然后通过查询加载进ODS层(ORC)；
  2. 数据文件生成时，如果直接使用命令行的形式，字段null值将被直接赋值为’NULL’字符串；

RC File 和 ORC File 的区别

RC File（Record Columnar File）和ORC File（Optimized Row Columnar File）都是Hive中的列式存储格式，它们都旨在提高查询性能和降低存储成本，但有一些区别：

1. 存储方式不同：

  • RC File是将每个记录作为一个行存储，但每列都单独存储，因此数据被列分隔。列的数据被压缩在每个数据块内，可以根据需要使用Zlib、Snappy或LZO等算法进行压缩。
  • ORC File是将数据按行组织并存储在行组中，每个行组可以包含数千行记录，而每列单独存储，列的数据按列分隔存储。列数据被压缩在行组内，可以使用Snappy、Zlib、LZO、LZ4等算法进行压缩。

2. 压缩率和压缩效率不同：

  • ORC比RC更优秀的原因之一是，它可以基于数据类型和压缩算法选择更适合的压缩算法，以实现更好的压缩率和压缩效率。ORC压缩方式可以在每列和每行组上分别选择，根据实际数据的分布情况来选择更好的压缩算法。

3. 某些情况下ORC File查询效率更高，如以下情况：

  • 数据压缩：由于ORC File支持列存储和列压缩，可以在读取数据时减少磁盘I/O和网络带宽占用，因此在数据量较大或者需要跨网络传输时，ORC File的查询速度更快。
  • 列过滤：ORC File支持基于列的过滤，即在读取数据时，可以只读取查询中涉及的列，而无需读取所有的列数据。这可以进一步提高查询性能，特别是当数据包含多个列或者某些列数据比其他列数据更大时。
  • 列式存储：ORC File支持列存储，将相同的数据类型的数据放在一起存储，使得在查询中需要的列能够快速访问，而无需扫描整个行。

但是在某些情况下，RC File的查询效率又可能比ORC File更高。例如，如果数据量较小或者需要频繁地更新或删除数据，那么RC File可能比ORC File更适合。在选择文件格式时，需要根据实际情况和使用场景进行综合考虑。

4. 适用场景不同：

  • RC文件通常适用于OLTP（联机事务处理）类型的工作负载，因为它们通常需要执行较少的全表扫描，并且需要高度压缩，以减少I/O和网络负载。
  • ORC文件通常适用于OLAP（联机分析处理）类型的工作负载，因为它们通常需要执行大量的全表扫描和分析查询，因此需要更高的查询性能和更高的压缩比率，以便更快地检索和分析大型数据集。

RC File的建表语句示例

CREATE TABLE hive_table_rc
(
   id int comment '编号',
   name string comment '名称',
   money decimal(10, 2) comment '金钱'
) comment 'hive中rc格式的建表语句'
   STORED AS RCFILE;

ORC File的建表语句示例

CREATE TABLE hive_table_orc
(
   id int comment '编号',
   name string comment '名称',
   money decimal(10, 2) comment '金钱'
) comment 'hive中orc格式的建表语句'
   STORED AS ORC;

可以创建发现不同格式的表只是修改指定类型即可。在实际使用中，我们通常会在创建表时指定该表更多的属性，这可以解决如上述提到的null值问题，会在下文中提到。

关于两个基础问题

使用命令，将数据从业务库写出到数据文件

  1. 首先，我们使用cmd的方式将数据写入到数据文件
# 请注意，两个##之间是要替换的内容，如##port## -> 3306
mysql -h##hostname or IP address## -P##port## -u##username## -p##password## "##select * from table_test##" > ##table_test##
  1. 查看这个文件，会发现如果某个字段为null，该列将被写为’NULL’字符串
cat ##table_test##
  1. 此时有两种办法解决，第一种是使用sed命令，直接将所有的NULL替换掉，如下
sed -i 's/NULL//g' ##table_test##

但这种方式容易改变到其他字符串中真的携带了NULL的内容，所以更推荐第二种方式，如下

mysql -h##hostname or IP address## -P##port## -u##username## -p##password## "##select ifnull(name, '') from table_test##" > ##table_test##

这种方式将null转为’'空字符串，这期间还可以加入replace函数，替换CHAR(10)和CHAR(13)，避免换行符引起的数据错位。

将文件加载到HIVE

  1. 上述第3步中，我们已经将null转为’‘字符串，那么在创建HIVE表时(也可以修改已创建过的表)，我们需要一些额外的配置，使’'默认为null，我们的建表语句如下
CREATE TABLE my_table
(
    id    int comment '编号',
    name  string comment '名称',
    money decimal(10, 2) comment '金钱'
)
    ROW FORMAT DELIMITED
        FIELDS TERMINATED BY ','
        LINES TERMINATED BY '\n'
        NULL DEFINED AS ''
    STORED  AS TEXTFILE
    TBLPROPERTIES ("serialization.null.format"='');

NULL DEFINED AS '‘将空字符串视为NULL值，‘serialization.null.format’=’'则用于指定NULL值在数据文件中的表示方式。通过这种方式，我们可以同时使用NULL DEFINED AS '‘和serialization.null.format=’'来将NULL值和空字符串表示在Hive中的一致性。
也可以修改已存在的表，如下

alter table my_table set serdeproperties('serialization.null.format' = '');
  1. 使用load语句，将数据加载进hive表中
-- 注意，使用了overwrite时是覆盖加载数据
LOAD DATA INPATH 'table_test' OVERWRITE INTO TABLE my_table;