Snowflake SQL存储过程DL_FILE_NAME参数化问题求助

我编写了Snowflake存储过程TRANSFORM.SP_WMS_CDC_TDHIST_STAGE_LOAD_COPY，尝试对DL_FILE_NAME进行参数化。存储过程能正常创建，但调用时传入的文件名无法替换到COPY语句的路径中，无法正确加载指定文件。

原存储过程代码 #

CREATE OR REPLACE PROCEDURE TRANSFORM.SP_WMS_CDC_TDHIST_STAGE_LOAD_COPY(DL_FILE_NAME VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
    TRUNCATE TABLE STAGING.TDHIST_STG;

    COPY INTO STAGING.TDHIST_STG FROM (
      SELECT
        $1:TDPNT,
        $1:TDCUS,
        $1:TDTIC,
        $1:TDTSK,
        $1:TDOLC,
        $1:TDSEQ,
        $1:TDSL1,
        $1:TDCAS,
        $1:TDQTY,
        $1:TDCUB,
        $1:TDPRD,
        $1:TDBCD,
        $1:TDLIN,
        $1:TDMOP,
        $1:TDMMOP,
        $1:TDTYP,
        $1:TDSDT,
        $1:TDPOI,
        $1:TDSTP,
        $1:TDDCK,
        $1:TDSTS,
        $1:TDRCD,
        $1:TDDCD,
        $1:TDUSR,
        $1:TDCHK,
        $1:TDCSTS,
        $1:TDLDR,
        $1:TDDOR,
        $1:TDLOT,
        $1:TDLTX,
        $1:TDLIC,
        $1:TDPID,
        $1:TDLCE,
        $1:TDLDT,
        $1:TDLTM,
        $1:TDNCE,
        $1:TDNDT,
        $1:TDNTM,
        $1:TDQCE,
        $1:TDQDT,
        $1:TDQTM,
        $1:TDUSR1,
        $1:TDUSR2,
        $1:TDUSR3,
        $1:TDUSR4,
        $1:TDTIME,
        $1:TDMDTE,
        $1:TDMPGM,
        $1:SOURCEDBMS,
        $1:DTL__CAPXRESTART1,
        $1:DTL__CAPXRESTART2,
        $1:DTL__CAPXUOW,
        $1:DTL__CAPXUSER,
        $1:DTL__CAPXTIMESTAMP,
        $1:DTL__CAPXACTION,
        $1:DTL__CAPXCASDELIND,
        $1:DTL__CAPXRRN,
        $1:CDC_INS_DT,
        $1:CDC_UPD_DT,
        $1:CDC_INS_USR,
        $1:CDC_UPD_USR,
        $1:DL_INS_DT,
        $1:DL_INS_USR,
        CONVERT_TIMEZONE('America/Los_Angeles', 'America/New_York', CURRENT_TIMESTAMP::TIMESTAMP_NTZ) AS INSERT_TIMESTAMP,
        NULL,
        'ADF_USER',
        NULL,
        'WMS'
    FROM @STAGING.sf_dl_wms_parquet_stage/CDC_TDHIST/:DL_FILE_NAME
);

    RETURN 'DONE';
END;
$$;

原调用语句 #

CALL TRANSFORM.SP_WMS_CDC_TDHIST_STAGE_LOAD_COPY(
    'CDC_TDHIST_IncrementalLoad_2023-10-24.snappy.parquet'
);

问题原因与解决方案 #

SQL语言的Snowflake存储过程中，无法直接在COPY语句的Stage路径里使用:参数名进行变量替换，必须通过动态SQL拼接生成完整的COPY语句，再用EXECUTE IMMEDIATE执行。

正确的存储过程代码 #

CREATE OR REPLACE PROCEDURE TRANSFORM.SP_WMS_CDC_TDHIST_STAGE_LOAD_COPY(DL_FILE_NAME VARCHAR)
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
    copy_sql STRING;
BEGIN
    TRUNCATE TABLE STAGING.TDHIST_STG;

    -- 动态拼接COPY语句，将参数插入到Stage路径中
    copy_sql := 'COPY INTO STAGING.TDHIST_STG FROM (
      SELECT
        $1:TDPNT,
        $1:TDCUS,
        $1:TDTIC,
        $1:TDTSK,
        $1:TDOLC,
        $1:TDSEQ,
        $1:TDSL1,
        $1:TDCAS,
        $1:TDQTY,
        $1:TDCUB,
        $1:TDPRD,
        $1:TDBCD,
        $1:TDLIN,
        $1:TDMOP,
        $1:TDMMOP,
        $1:TDTYP,
        $1:TDSDT,
        $1:TDPOI,
        $1:TDSTP,
        $1:TDDCK,
        $1:TDSTS,
        $1:TDRCD,
        $1:TDDCD,
        $1:TDUSR,
        $1:TDCHK,
        $1:TDCSTS,
        $1:TDLDR,
        $1:TDDOR,
        $1:TDLOT,
        $1:TDLTX,
        $1:TDLIC,
        $1:TDPID,
        $1:TDLCE,
        $1:TDLDT,
        $1:TDLTM,
        $1:TDNCE,
        $1:TDNDT,
        $1:TDNTM,
        $1:TDQCE,
        $1:TDQDT,
        $1:TDQTM,
        $1:TDUSR1,
        $1:TDUSR2,
        $1:TDUSR3,
        $1:TDUSR4,
        $1:TDTIME,
        $1:TDMDTE,
        $1:TDMPGM,
        $1:SOURCEDBMS,
        $1:DTL__CAPXRESTART1,
        $1:DTL__CAPXRESTART2,
        $1:DTL__CAPXUOW,
        $1:DTL__CAPXUSER,
        $1:DTL__CAPXTIMESTAMP,
        $1:DTL__CAPXACTION,
        $1:DTL__CAPXCASDELIND,
        $1:DTL__CAPXRRN,
        $1:CDC_INS_DT,
        $1:CDC_UPD_DT,
        $1:CDC_INS_USR,
        $1:CDC_UPD_USR,
        $1:DL_INS_DT,
        $1:DL_INS_USR,
        CONVERT_TIMEZONE(''America/Los_Angeles'', ''America/New_York'', CURRENT_TIMESTAMP::TIMESTAMP_NTZ) AS INSERT_TIMESTAMP,
        NULL,
        ''ADF_USER'',
        NULL,
        ''WMS''
    FROM @STAGING.sf_dl_wms_parquet_stage/CDC_TDHIST/' || DL_FILE_NAME || '
);';

    -- 执行动态生成的SQL语句
    EXECUTE IMMEDIATE copy_sql;

    RETURN 'DONE';
END;
$$;

关键修改说明 #

• 声明copy_sql变量存储动态生成的SQL语句
• 用字符串拼接将DL_FILE_NAME参数插入到Stage路径中，注意原SQL中的单引号需要转义（写成两个单引号''）
• 使用EXECUTE IMMEDIATE执行动态生成的COPY语句

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