时间戳

首页 > 解决方案 > Snowflake 中的 Varbinary 字节

snowflake-cloud-data-platform - Snowflake 中的 Varbinary 字节

问题描述

如果在 mssql 中执行以下查询，我将得到以下输出：查询：

select SubString(0x003800010102000500000000,1, 2) as A
,SubString(0x003800010102000500000000, 6, 1) as B
,CAST(CAST(SubString(0x003800010102000500000000, 9, cast(SubString(0x003800010102000500000000, 
 6, 1)As TinyInt)) As VarChar) As Float) as D

读取格式：0x 00 38 00 01 01 02 00 05 00 00 00 00

输出：ABD 0x0038 0x02 0

上面的子字符串函数为每个指定的索引值占用两个字节，不包括 mssql 中的前两个字节“0x”。

现在我正在尝试使用雪花来实现相同的输出。有人可以帮忙吗，因为我很难通过创建一个函数来理解将字节分成两部分。

CREATE OR REPLACE FUNCTION getFloat1 (p1 varchar) RETURNS Float as $$
Select Case
    WHEN concat(substr(p1::varchar,1, 2),substr(p1::varchar,5, 4)) <> '0x3E00'
        then 0::float
       ELSE 1::float
        //Else substr(p1::varchar, 9, substr(p1::varchar, 6, 1)):: float End as test1 $$ ;

标签： snowflake-cloud-data-platform

解决方案

Snowflake 没有二进制文字，因此没有任何符号会像0xSQL Server 中的符号那样自动将值视为二进制。您始终必须将值强制转换为 BINARY 数据类型才能将该值视为二进制。

此外，SQL Server 和 Snowflake 之间的 BINARY 数据类型处理存在一些差异：

  • SUBSTRING在 Snowflake 中只能处理一个字符串
    • ...然后的第二个参数SUBSTRING必须是作为十六进制字符串的字符数，而不是作为二进制的字节数
  • Snowflake 支持十六进制字符串作为二进制的表示，但十六进制字符串不能包含0x前缀
  • Snowflake 无法将二进制转换为数字，但可以使用 'X' 格式字符串转换十六进制字符串TO_NUMBER

基于上述差异，下面是一个与 SQL Server 查询实现相同结果的示例查询：

select
    substring('003800010102000500000000', 1, 4)::binary A,
    substring('003800010102000500000000', 11, 2)::binary B,
    to_number(
      substring(
        '003800010102000500000000', 
        17,
        to_number(substring('003800010102000500000000', 11, 2), 'XX')*2
      ),
      'XXXX'
    )::float D
;

它返回与您的查询相同的以下结果：

/*
A    B    D
0038 02   0
*/

解释：

由于 Snowflake 没有二进制文字并且 SUBSTRING 仅支持字符串 (VARCHAR)，因此任何二进制操作都必须使用 VARCHAR 十六进制字符串来完成。

所以，在查询中，第一个SUBSTRING从 1 开始，提取 4 个字符，因为 1 个字节由 2 个十六进制字符组成，那么提取 2 个字节相当于提取 4 个十六进制字符。

第二个SUBSTRING从 11 开始，因为从第 6 个字节开始意味着忽略 5 个字节（= 10 个十六进制字符）并从下一个十六进制字符开始，即第 6 个字节的第一个十六进制字符（10 + 1 = 11）。

第三个SUBSTRING和第二个一样，从第 9 个字节开始意味着忽略 8 个字节（= 16 个十六进制字符），从后面的十六进制字符开始（16 + 1 = 17）。

此外，要将十六进制字符串转换为数字数据类型，请使用cast 函数X的第二个“格式”参数中的TO_NUMBER字符将字符串解析为十六进制字符的集合。单个X字符对应于要解析的字符串中的单个十六进制字符。这就是为什么我曾经'XX'解析一个字节（2 个十六进制字符）并用来'XXXX'解析 2 个字节（4 个十六进制字符）的原因。

推荐阅读