如何将位字符串类型从Postgres移植到Snowflake

Question

Context当前我正在使用salt对Postgresql数据库中的值进行哈希。我正在尝试将相同的功能从Postgres迁移到Snowflake。

我尝试做什么

目前数据正在Postgres中进行散列处理，如：

SELECT ('x' || md5('bla'::TEXT || (SELECT 'saltysalt')))::BIT(64)::BIGINT;

我正在尝试为Snowflake复制相同的程序。我正在努力一步一步地进行，这就是我停止的地方：

-- Not sure how can I apply the BIT(64)[docs: https://www.postgresql.org/docs/8.1/datatype-bit.html] type to Snowflake
SELECT ('x' || md5('bla'::TEXT || (SELECT 'saltysalt')));

我试过使用Snowflake的原生TO_BINARY函数，但我得到了一个错误，比如：

The following string is not a legal base64-encoded value: 'x6d0d80fc2ace4e80'

摘要

如何将我的哈希方法从Postgres移植到Snowflake？Snowflake中的位串类型的等价物是什么？

Answer 1

我不明白为什么在散列后生成十进制，或者为什么使用SELECT作为文本。

正如我所看到的，你的查询可以在Postgres:中写如下

SELECT ('x' || md5('bla' || 'saltysalt'))::BIT(64)::BIGINT;
7858078745369661000 <--- and this is the output

所以你在这里要做的是，在字符串中添加一个salt，计算MD5，将其转换为二进制，只获得前64位(以避免MD5哈希的好处(，然后将其转换成bigint。

我看到了一个问题。BIGINT可以保留8位数字，但它是为存储有符号的值而设计的。另一方面，原始十六进制不是带符号的值，因此转换不正确。让我演示一下：

SELECT md5('bla' || 'saltysalt');
6d0d80fc2ace4e80c47bfd51f39f42f6 <-- output of above command
SELECT ('x' || md5('bla' || 'saltysalt'))::BIT(64)::BIGINT;
7858078745369661000 <-- you save the first 8 digits as bigint
select to_hex(7858078745369661000);
6d0d80fc2ace4e48 <-- when you convert back to hex, you get a different number!!! compare with the first output

如果hex/bigint转换没有问题，那么您的查询可以很容易地在Snowflake:中这样写

SELECT to_number(LEFT(md5('bla' || 'saltysalt'),16),'XXXXXXXXXXXXXXXX');