Google BigQuery中64位哈希字符串列的最优数据类型选择及转换方法

你给出的64位字符是十六进制编码的SHA256哈希值，最优选择是BigQuery的BYTES类型：

• 原STRING类型存储64个字符需要占用64字节（十六进制字符均为单字节UTF-8字符）
• 转换为BYTES类型后仅占用32字节（每2个十六进制字符对应1字节），存储空间直接减少50%，千亿级表的成本缩减效果非常显著
• 不建议尝试数值类型存储：该哈希长度超出了BigQuery所有原生数值类型的最大存储范围（INT64仅支持8字节，NUMERIC仅支持16字节），无法适配

BigQuery提供原生的FROM_HEX函数实现十六进制字符串到BYTES的转换，根据你的使用场景可以选择两种操作方式：

方案1：整表重写生成新优化表（推荐，性能最高） #

适合可以停机替换或者新建表使用的场景，SQL语句如下：

CREATE OR REPLACE TABLE `你的项目ID.你的数据集名.优化后的表名`
AS
SELECT
  -- 其余原有字段无需修改
  FROM_HEX(原哈希字段名) AS 新哈希字段名,
  * EXCEPT(原哈希字段名)
FROM `你的项目ID.你的数据集名.原表名`;

方案2：原有表新增字段回填 #

适合不能直接替换原表、需要在线升级的场景：

1. 新增BYTES类型的哈希字段

ALTER TABLE `你的项目ID.你的数据集名.原表名`
ADD COLUMN 新哈希字段名 BYTES;

2. 回填新字段数据

UPDATE `你的项目ID.你的数据集名.原表名`
SET 新哈希字段名 = FROM_HEX(原哈希字段名)
WHERE 新哈希字段名 IS NULL;

3. 确认数据无误后，可删除原有STRING类型的哈希字段（可选）

ALTER TABLE `你的项目ID.你的数据集名.原表名`
DROP COLUMN 原哈希字段名;

如果需要用原十六进制值查询匹配，调用TO_HEX函数即可将BYTES类型转回字符串：

SELECT *
FROM `你的项目ID.你的数据集名.优化后的表名`
WHERE TO_HEX(新哈希字段名) = '1f5ec82dff18c01aac6f4c07feaedf5b4ad43fe8815a5da732d5fe445a788f59';

FROM_HEX函数支持大小写十六进制字符，转换前无需对原字段值做大小写统一处理。

内容的提问来源于stack exchange，提问作者Dervin Thunk