Synapse哈希键转换迁移至Redshift后结果不匹配问题

问题重现 #

在Synapse中使用以下语句生成哈希键：

CONVERT(char(64),HASHBYTES('SHA2_256',UPPER('1' + '|' +  ISNULL(CAST([SourceSystem] AS NVARCHAR(MAX)),'UNKNOWN') + '|' +  ISNULL(CAST([Vendor] AS NVARCHAR(MAX)),'UNKNOWN')  )),2)

在Redshift中尝试对应语句：

sha2(UPPER('1' || '|' ||  NVL(CAST(SourceSystem AS VARCHAR(MAX)),'UNKNOWN') || '|' ||  NVL(CAST(Vendor AS VARCHAR(MAX)),'UNKNOWN')),256)

但生成的哈希值不一致：

• Synapse哈希键：A92F254C476AB0DA1F0232697673B179832E7F6AC5BFCC831A318CE56CD80AB9
• Redshift哈希键：1f70da140d859ac783d4e9003df546673e485a5b391955c84f8f0a54f112a613

核心原因 #

两者哈希值不匹配的关键是字符编码差异：

• Synapse中NVARCHAR类型采用UTF-16LE编码，HASHBYTES函数直接基于该编码的字节流计算哈希。
• Redshift默认VARCHAR采用UTF-8编码，直接计算哈希时的字节流与Synapse完全不同，导致结果不一致。

解决方法 #

修改Redshift语句，将输入字符串转换为UTF-16LE编码后再计算哈希，同时统一输出格式为大写十六进制：

UPPER(ENCODE(SHA2(CONVERT_TO(
    UPPER('1' || '|' || NVL(CAST(SourceSystem AS VARCHAR(MAX)),'UNKNOWN') || '|' || NVL(CAST(Vendor AS VARCHAR(MAX)),'UNKNOWN')),
    'UTF16LE'
), 256), 'hex'))

语句说明 #

1. CONVERT_TO(..., 'UTF16LE')：将字符串转换为UTF-16LE编码的字节数组，与Synapse的NVARCHAR字节流对齐。
2. SHA2(..., 256)：基于UTF-16LE字节流计算SHA2_256哈希。
3. ENCODE(..., 'hex')：将哈希字节转换为十六进制字符串。
4. UPPER(...)：将十六进制字符串转为大写，与Synapse输出格式保持一致。

验证 #

使用相同的SourceSystem和Vendor值测试修改后的Redshift语句，生成的哈希值将与Synapse完全匹配。

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