Snowflake技术问题：Snowpipe处理空文件时如何提取文件名

问题根源 #

零字节文件没有数据行，导致COPY命令的SELECT子句无法生成记录，自然无法提取METADATA$FILENAME。以下是几种实用解决方案：

方案一：存储过程统一处理空文件与非空文件（推荐） #

创建存储过程批量筛选空文件并插入记录，同时处理非空文件的COPY操作：

CREATE OR REPLACE PROCEDURE DEV.LOAD_FILES_WITH_EMPTY_HANDLING()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    empty_files CURSOR FOR
        SELECT RELATIVE_PATH, METADATA$FILENAME
        FROM @DEV.STAGE.DEV_STG/input/
        WHERE METADATA$FILE_SIZE = 0
          AND RELATIVE_PATH LIKE 'File_DLYERR_%';
    v_filename VARCHAR;
BEGIN
    -- 插入空文件记录到目标表
    FOR file IN empty_files DO
        v_filename := SPLIT_PART(file.METADATA$FILENAME, '/', 4);
        INSERT INTO schema.TMP_table (MESSAGE, FILE_NAME, LOAD_TS)
        VALUES (NULL, v_filename, CURRENT_TIMESTAMP::TIMESTAMP_NTZ);
        -- 可选：移除已处理的空文件，避免重复加载
        REMOVE @DEV.STAGE.DEV_STG/input/ || file.RELATIVE_PATH;
    END FOR;

    -- 处理非空文件的COPY逻辑
    COPY INTO schema.TMP_table FROM (
        SELECT 
          $1::VARIANT AS MESSAGE,
          SPLIT_PART(METADATA$FILENAME,'/',4) AS FILE_NAME,
          CURRENT_TIMESTAMP::TIMESTAMP_NTZ AS LOAD_TS
        FROM @DEV.STAGE.DEV_STG/input/  
    )
    PATTERN = 'File_DLYERR_.*'
    ON_ERROR = 'CONTINUE';

    RETURN '空文件与非空文件处理完成';
END;
$$;

可以通过**Snowflake任务（Task）**定期调用该存储过程，或结合S3事件通知触发存储过程替代原Snowpipe的auto_ingest逻辑。

方案二：从COPY_HISTORY视图提取跳过的空文件 #

Snowflake会在INFORMATION_SCHEMA.COPY_HISTORY中记录被跳过的零字节文件，可定期查询该视图补全记录：

-- 提取最近1小时内被跳过的空文件，插入目标表
INSERT INTO schema.TMP_table (MESSAGE, FILE_NAME, LOAD_TS)
SELECT 
    NULL AS MESSAGE,
    SPLIT_PART(ERROR_MESSAGE, '''', 2) AS FILE_NAME,
    CURRENT_TIMESTAMP::TIMESTAMP_NTZ AS LOAD_TS
FROM INFORMATION_SCHEMA.COPY_HISTORY
WHERE TABLE_NAME = 'TMP_TABLE'
  AND STATUS = 'LOADED'
  AND ERROR_MESSAGE LIKE '%Zero-byte file%'
  AND START_TIME >= DATEADD(HOUR, -1, CURRENT_TIMESTAMP);

注意：需根据实际错误信息格式调整SPLIT_PART的参数，确保正确提取文件名。

方案三：COPY语句中合并空文件处理逻辑 #

通过UNION ALL分别处理空文件与非空文件，强制为空文件生成一条记录：

CREATE OR REPLACE PIPE DEV.schema.load_pipe auto_ingest = true  
AS
COPY INTO schema.TMP_table FROM (
    -- 处理非空文件
    SELECT 
      $1::VARIANT AS MESSAGE,
      SPLIT_PART(METADATA$FILENAME,'/',4) AS FILE_NAME,
      CURRENT_TIMESTAMP::TIMESTAMP_NTZ AS LOAD_TS
    FROM @DEV.STAGE.DEV_STG/input/  
    WHERE METADATA$FILE_SIZE > 0

    UNION ALL

    -- 处理空文件
    SELECT 
      NULL AS MESSAGE,
      SPLIT_PART(METADATA$FILENAME,'/',4) AS FILE_NAME,
      CURRENT_TIMESTAMP::TIMESTAMP_NTZ AS LOAD_TS
    FROM @DEV.STAGE.DEV_STG/input/  
    WHERE METADATA$FILE_SIZE = 0
)
PATTERN = 'File_DLYERR_.*'
ON_ERROR = 'CONTINUE';

注意：该方法依赖Snowflake对空文件的SELECT返回行，部分场景下可能不生效，建议优先使用方案一。

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