Snowflake合并S3 Stage Parquet文件时遇文件格式权限/存在性错误

问题背景 #

尝试执行以下MERGE SQL脚本，将Snowflake现有表data_stage.test_temp4与S3 Stage（@dss.test_plus.test_plus_stage/dev/sub_solutions/test/）内的Parquet文件合并：

MERGE INTO  data_stage.test_temp4  ex  USING (
SELECT
$1:device_attr_hk,
$1:applicationruntime_raw,
$1:devicefamily_raw,
$1:deviceprofile_raw,
$1:manufacturer_raw,
$1:model_raw,
$1:application_runtime_os,
$1:device_profile,
$1:device_family,
$1:device_platform,
$1:manufacturer,
$1:model,
$1:device_type,
$1:creation_timestamp,
$1:change_hkey
FROM  @dss.test_plus.test_plus_stage/dev/sub_solutions/test/  (file_format => 'parquet', PATTERN => '.*.parquet')

) ne ON ex.device_attr_hk = ne.device_attr_hk WHEN MATCHED THEN
UPDATE SET device_attr_hk = ne.device_attr_hk, applicationruntime_raw = ne.applicationruntime_raw, devicefamily_raw = ne.devicefamily_raw, deviceprofile_raw = ne.deviceprofile_raw, manufacturer_raw = ne.manufacturer_raw, model_raw = ne.model_raw, application_runtime_os = ne.application_runtime_os, device_profile = ne.device_profile, device_family = ne.device_family, device_platform = ne.device_platform, manufacturer = ne.manufacturer,  model = ne.model,  device_platform = ne.device_platform ,  manufacturer = ne.manufacturer,  model = ne.model,  device_type = ne.device_type,  creation_timestamp = ne.creation_timestamp,  change_hkey = ne.change_hkey WHEN NOT MATCHED THEN
INSERT
(device_attr_hk, applicationruntime_raw, devicefamily_raw, deviceprofile_raw, manufacturer_raw, model_raw, application_runtime_os, device_profile, device_family, device_platform, manufacturer, model, device_type, creation_timestamp, change_hkey) VALUES
(ne.device_attr_hk, ne.applicationruntime_raw, ne.devicefamily_raw, ne.deviceprofile_raw, ne.manufacturer_raw, ne.model_raw, ne.application_runtime_os, ne.device_profile, ne.device_family, ne.device_platform, ne.manufacturer, ne.model, ne.device_type, ne.creation_timestamp, ne.change_hkey);
commit;

执行后报错：

SQL compilation error: File format 'PARQUET' does not exist or not authorized

已知条件：Stage路径下已存在目标文件，且具备该Stage的访问权限。

解决方案 #

1. 修正文件格式引用问题 #

报错核心原因是脚本中引用的'parquet'并非当前上下文存在的自定义文件格式对象。Snowflake提供内置的Parquet格式支持，无需预先创建自定义格式，直接使用TYPE = PARQUET即可替代file_format => 'parquet'。

如果确实需要使用自定义Parquet文件格式，需确保格式存在并使用完全限定名（如file_format => dss.test_plus.my_parquet_format），避免因上下文找不到格式对象而报错。

2. 清理UPDATE语句中的重复字段 #

原脚本的UPDATE部分重复设置了device_platform、manufacturer、model字段，这会导致额外编译错误，需移除重复的赋值语句。

修正后的完整脚本 #

MERGE INTO data_stage.test_temp4 ex 
USING (
    SELECT
        $1:device_attr_hk,
        $1:applicationruntime_raw,
        $1:devicefamily_raw,
        $1:deviceprofile_raw,
        $1:manufacturer_raw,
        $1:model_raw,
        $1:application_runtime_os,
        $1:device_profile,
        $1:device_family,
        $1:device_platform,
        $1:manufacturer,
        $1:model,
        $1:device_type,
        $1:creation_timestamp,
        $1:change_hkey
    FROM @dss.test_plus.test_plus_stage/dev/sub_solutions/test/ (TYPE = PARQUET, PATTERN => '.*.parquet')
) ne ON ex.device_attr_hk = ne.device_attr_hk 
WHEN MATCHED THEN
    UPDATE SET 
        device_attr_hk = ne.device_attr_hk, 
        applicationruntime_raw = ne.applicationruntime_raw, 
        devicefamily_raw = ne.devicefamily_raw, 
        deviceprofile_raw = ne.deviceprofile_raw, 
        manufacturer_raw = ne.manufacturer_raw, 
        model_raw = ne.model_raw, 
        application_runtime_os = ne.application_runtime_os, 
        device_profile = ne.device_profile, 
        device_family = ne.device_family, 
        device_platform = ne.device_platform, 
        manufacturer = ne.manufacturer, 
        model = ne.model, 
        device_type = ne.device_type, 
        creation_timestamp = ne.creation_timestamp, 
        change_hkey = ne.change_hkey 
WHEN NOT MATCHED THEN
    INSERT (
        device_attr_hk, 
        applicationruntime_raw, 
        devicefamily_raw, 
        deviceprofile_raw, 
        manufacturer_raw, 
        model_raw, 
        application_runtime_os, 
        device_profile, 
        device_family, 
        device_platform, 
        manufacturer, 
        model, 
        device_type, 
        creation_timestamp, 
        change_hkey
    ) VALUES (
        ne.device_attr_hk, 
        ne.applicationruntime_raw, 
        ne.devicefamily_raw, 
        ne.deviceprofile_raw, 
        ne.manufacturer_raw, 
        ne.model_raw, 
        ne.application_runtime_os, 
        ne.device_profile, 
        ne.device_family, 
        ne.device_platform, 
        ne.manufacturer, 
        ne.model, 
        ne.device_type, 
        ne.creation_timestamp, 
        ne.change_hkey
    );
COMMIT;

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