Snowflake外部表Variant转Boolean类型报错的解决方法

问题场景 #

基于CSV文件创建外部表时，原字段定义为VARIANT类型，查询结果包含"None"、"True"、"False"值。尝试直接将字段转为BOOLEAN类型时，报错：Failed to cast variant value "None" to BOOLEAN。

原VARIANT类型外部表创建语句：

create or replace external table PAGES
(
near VARIANT as (nullif(value:c1,null)::VARIANT) 
)
with location = @test_stage
file_format = test_file_format
pattern = '.*[.]csv';

select * from PAGES;

查询结果：

"None"
"True"
"False"
"None"

尝试转换为BOOLEAN类型的报错语句：

create or replace external table PAGES
(
near BOOLEAN as (nullif(value:c1,null)::BOOLEAN) 
)
with location = @test_stage
file_format = test_file_format
pattern = '.*[.]csv';

select * from PAGES;

关联的文件格式和Stage定义：

create or replace file format test_file_format type = 'csv' field_delimiter = ','
SKIP_HEADER = 1
FIELD_OPTIONALLY_ENCLOSED_BY = '"' ESCAPE = '\\' 
empty_field_as_null=TRUE;

create or replace stage oncrawl_stage url='s3://unload-dev/'
  file_format = test_file_format
  storage_integration=snowflake_s3_integration;

解决方案 #

Snowflake无法直接将字符串"None"转换为BOOLEAN类型，需先将"None"映射为NULL，再执行类型转换。以下两种方法均可实现：

方法1：使用CASE WHEN显式处理 #

通过CASE语句匹配"None"字符串并转为NULL，其余合法布尔字符串直接转换为BOOLEAN类型：

create or replace external table PAGES
(
near BOOLEAN as (
    CASE value:c1
        WHEN 'None' THEN NULL
        ELSE value:c1::BOOLEAN
    END
)
)
with location = @test_stage
file_format = test_file_format
pattern = '.*[.]csv';

方法2：结合NULLIF与TRY_CAST #

先通过NULLIF将"None"转为NULL，再用TRY_CAST尝试转换剩余值为BOOLEAN（非法值也会返回NULL）：

create or replace external table PAGES
(
near BOOLEAN as (TRY_CAST(nullif(value:c1, 'None') AS BOOLEAN))
)
with location = @test_stage
file_format = test_file_format
pattern = '.*[.]csv';

执行上述任一语句后，查询select * from PAGES将得到预期结果：

NULL
TRUE
FALSE
NULL

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