如何在Snowflake视图中将两列拼接为带连字符的单列？

问题 #

需要创建一个Snowflake视图，其中TEST_ID列由另外两列的值以OBJECT_ID_1-OBJECT_ID_2的格式拼接而成（例如12345-67890），而非求和。现有表结构如下：

create or replace table database.schema.test_table (
  TEST_ID NUMBER(38,0) NOT NULL,
  TEST_DETAILS VARIANT NOT NULL,
  PRIMARY KEY (TEST_ID)
);

尝试的视图语句（使用CONCAT未成功）：

create or replace view database.schema2.test_view(
  TEST_ID,
  OBJECT_NAME,
  OBJECT_ID_1,
  OBJECT_ID_2,
) as SELECT
    CONCAT(OBJECT_ID_1,OBJECT_ID_2) as TEST_ID,
    TEST_DETAILS:OBJECT_NAME::VARCHAR(16777216) as OBJECT_NAME,
    TEST_DETAILS:OBJECT_ID_1::NUMBER(38,9) as OBJECT_ID_1,
    TEST_DETAILS:OBJECT_ID_2::NUMBER(38,9) as OBJECT_ID_2,
FROM
    database.schema.test_table ;

解决方案 #

直接使用CONCAT处理数字类型列时，Snowflake会默认执行数值求和而非字符串拼接。要实现带分隔符的字符串拼接，需先将数字转换为字符串，再加入分隔符，以下是两种可行方式：

方式1：使用CONCAT函数显式转换类型 #

通过TO_VARCHAR将数字列转为字符串，再拼接分隔符'-'：

CONCAT(TO_VARCHAR(OBJECT_ID_1), '-', TO_VARCHAR(OBJECT_ID_2)) as TEST_ID

方式2：使用字符串拼接运算符|| #

这种写法更简洁，同样需要先转换数字为字符串：

TO_VARCHAR(OBJECT_ID_1) || '-' || TO_VARCHAR(OBJECT_ID_2) as TEST_ID

修改后的完整视图语句 #

create or replace view database.schema2.test_view(
  TEST_ID,
  OBJECT_NAME,
  OBJECT_ID_1,
  OBJECT_ID_2
) as SELECT
    TO_VARCHAR(OBJECT_ID_1) || '-' || TO_VARCHAR(OBJECT_ID_2) as TEST_ID,
    TEST_DETAILS:OBJECT_NAME::VARCHAR(16777216) as OBJECT_NAME,
    TEST_DETAILS:OBJECT_ID_1::NUMBER(38,9) as OBJECT_ID_1,
    TEST_DETAILS:OBJECT_ID_2::NUMBER(38,9) as OBJECT_ID_2
FROM
    database.schema.test_table;

注：原视图定义中列列表末尾的逗号需移除，否则会触发语法报错。

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