如何在Hive中内联转换Review数据为二进制以存储进度？

一、内联转二进制的可行性 #

没问题，Hive原生支持BINARY数据类型，你可以将Rating和Combination这类自定义类型序列化为字节数组，存入Hive的BINARY字段；读取时再反序列化为原类型，就能实现进度的保存与加载。

二、Hive BinaryWriter的使用方法 #

Hive的BinaryWriter（对应包org.apache.hadoop.hive.serde2.binary.BinaryWriter）可以帮你将自定义类型序列化为符合Hive规范的二进制流，以下是具体实现：

1. 序列化自定义类型 #

假设你的Rating包含score（整数）和comment（字符串），Combination包含id（字符串）和items（字符串列表），序列化代码如下：

import org.apache.hadoop.hive.serde2.binary.BinaryWriter;
import java.io.ByteArrayOutputStream;
import java.io.DataOutputStream;
import java.io.IOException;
import java.util.List;

// 序列化Rating为字节数组
public byte[] serializeRating(Rating rating) throws IOException {
    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    try (DataOutputStream dos = new DataOutputStream(baos)) {
        BinaryWriter writer = new BinaryWriter(dos);
        writer.writeInt(rating.getScore());
        writer.writeString(rating.getComment());
        dos.flush();
        return baos.toByteArray();
    }
}

// 序列化Combination为字节数组
public byte[] serializeCombination(Combination combination) throws IOException {
    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    try (DataOutputStream dos = new DataOutputStream(baos)) {
        BinaryWriter writer = new BinaryWriter(dos);
        writer.writeString(combination.getId());
        writer.writeInt(combination.getItems().size());
        for (String item : combination.getItems()) {
            writer.writeString(item);
        }
        dos.flush();
        return baos.toByteArray();
    }
}

2. 写入Hive表 #

先创建包含BINARY字段的Hive表：

CREATE TABLE reviews (
    id INT,
    user_id STRING,
    rating BINARY,
    combination BINARY,
    create_time TIMESTAMP
) STORED AS ORC;

然后通过JDBC或Spark等工具，将序列化后的字节数组写入对应字段，示例JDBC插入逻辑：

String insertSql = "INSERT INTO reviews VALUES (?, ?, ?, ?, ?)";
try (PreparedStatement pstmt = connection.prepareStatement(insertSql)) {
    pstmt.setInt(1, review.getId());
    pstmt.setString(2, review.getUserId());
    pstmt.setBytes(3, serializeRating(review.getRating()));
    pstmt.setBytes(4, serializeCombination(review.getCombination()));
    pstmt.setTimestamp(5, review.getCreateTime());
    pstmt.executeUpdate();
}

3. 读取并反序列化 #

读取时需要用BinaryReader对应反序列化，保持字段顺序和序列化一致：

import org.apache.hadoop.hive.serde2.binary.BinaryReader;
import java.io.ByteArrayInputStream;
import java.io.DataInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

// 反序列化Rating
public Rating deserializeRating(byte[] bytes) throws IOException {
    try (ByteArrayInputStream bais = new ByteArrayInputStream(bytes);
         DataInputStream dis = new DataInputStream(bais)) {
        BinaryReader reader = new BinaryReader(dis);
        Rating rating = new Rating();
        rating.setScore(reader.readInt());
        rating.setComment(reader.readString());
        return rating;
    }
}

// 反序列化Combination
public Combination deserializeCombination(byte[] bytes) throws IOException {
    try (ByteArrayInputStream bais = new ByteArrayInputStream(bytes);
         DataInputStream dis = new DataInputStream(bais)) {
        BinaryReader reader = new BinaryReader(dis);
        Combination combination = new Combination();
        combination.setId(reader.readString());
        int itemCount = reader.readInt();
        List<String> items = new ArrayList<>();
        for (int i = 0; i < itemCount; i++) {
            items.add(reader.readString());
        }
        combination.setItems(items);
        return combination;
    }
}

三、避免自定义类的替代方案 #

如果不想依赖自定义类，更简单的方式是拆分自定义类型为Hive原生支持的字段：

• 将Rating拆分为rating_score INT和rating_comment STRING
• 将Combination拆分为combination_id STRING和combination_items ARRAY<STRING>

这种方式无需序列化/反序列化，直接用Hive原生类型存储，维护成本更低，也能满足进度保存与加载的需求。

内容的提问来源于stack exchange，提问作者My Name