BigQuery中DETERMINISTIC_DECRYPT_STRING解密列报错求助

可能的原因及解决步骤 #

1. 列中存在无效的Base64值 #

硬编码的加密值是合法的Base64字符串，但ssn列可能包含空值、截断字符串或非Base64字符，导致FROM_BASE64解析失败，进而触发解密错误。

先筛选出无效值：

SELECT ssn
FROM `project.dataset.table`
WHERE NOT REGEXP_CONTAINS(ssn, r'^[A-Za-z0-9+/]+={0,2}$') OR ssn IS NULL

对这些无效值进行清理（比如删除、补全或标记）后再尝试解密。

2. 加密值格式与硬编码值不一致 #

确认ssn列中的加密值和你测试用的硬编码值使用了完全相同的加密规则：

• 加密时是否添加了额外的前缀/后缀？
• 确定性加密的上下文参数（第三个空字符串参数）是否和加密时一致？
• 加密使用的密钥集是否和解密用的完全匹配（包括typeUrl、value等字段）？

3. 处理空值或异常值 #

如果列中存在空值，直接传入解密函数会报错。可以通过条件判断跳过无效值：

DECLARE KEYSET STRING;
SET KEYSET = '{"primaryKeyId":1,"key":[{"keyData":{"typeUrl":"a","value":"a","keyMaterialType":"SYMMETRIC"},"status":"ENABLED","keyId":1,"outputPrefixType":"TINK"}]}';

SELECT 
  ssn,
  CASE 
    WHEN ssn IS NOT NULL AND REGEXP_CONTAINS(ssn, r'^[A-Za-z0-9+/]+={0,2}$') 
    THEN DETERMINISTIC_DECRYPT_STRING(KEYS.KEYSET_FROM_JSON(KEYSET), FROM_BASE64(ssn), "") 
    ELSE NULL 
  END AS d_ssn 
FROM `project.dataset.table`;

4. 验证单条列值的解密有效性 #

从ssn列取一条值，硬编码到查询中测试，确认该值是否能正常解密：

DECLARE KEYSET STRING;
SET KEYSET = '{"primaryKeyId":1,"key":[{"keyData":{"typeUrl":"a","value":"a","keyMaterialType":"SYMMETRIC"},"status":"ENABLED","keyId":1,"outputPrefixType":"TINK"}]}';

SELECT 
  DETERMINISTIC_DECRYPT_STRING(KEYS.KEYSET_FROM_JSON(KEYSET), FROM_BASE64("具体的ssn列值"), "") 

如果这条测试失败，说明该列值本身加密规则不匹配；如果成功，说明列中存在部分无效值导致批量解密失败。

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