Airflow中调用Snowflake存储过程执行失败求助

问题描述 #

在Snowflake中可正常运行的存储过程dynamic_view_on_table_sp('DIM_LEXIDS_PROVIDER')，放到Airflow DAG中执行时触发以下JavaScript编译错误：

JavaScript compilation error: Uncaught SyntaxError: Invalid or unexpected token in DYNAMIC_VIEW_ON_TABLE_SP at '                      create_statement += "' position 42

尝试在SQL语句不同位置使用反引号和双引号，问题仍未解决。以下是Snowflake中可正常运行的存储过程代码：

create or replace procedure dynamic_view_on_table_sp(TABLEPREFIX VARCHAR)
            returns STRING
            language javascript
            as     
            $$
              var get_tables_name_stmt = "select table_name from information_schema.tables where table_type = 'BASE TABLE' and table_name like UPPER('"+ TABLEPREFIX + "%');"
              var get_tables_name_stmt = snowflake.createStatement({sqlText:get_tables_name_stmt });
              var tables = get_tables_name_stmt.execute();
              var row_count = get_tables_name_stmt.getRowCount();
              var cnt = 0; 
              var create_statement = `create or replace view dim_full_segment_lexids_tables_vw as \n`;
              while (tables.next())  {
                  cnt += 1;
                  var table_name = tables.getColumnValue(1); 
                  create_statement += `select * from ` + table_name 
                  if (cnt < row_count){
                      create_statement += "\n union all \n"
                  }
               }
              var create_view_statement = snowflake.createStatement( {sqlText: create_statement} );
                create_view_statement.execute();
              return create_view_statement.getSqlText();
            $$;

问题原因 #

错误信息里的"是HTML实体转义后的双引号。存储过程代码中用"替代双引号，在Snowflake环境中能正常解析，但Airflow在传递SQL代码时，会把这些"当成字符串的一部分，导致JavaScript语法解析失败，触发Invalid token错误。

解决方案 #

核心修改思路 #

替换代码中所有的"为实际的引号，改用ES6模板字符串（反引号）处理SQL语句，避免引号嵌套和转义冲突。

修改后的存储过程代码 #

create or replace procedure dynamic_view_on_table_sp(TABLEPREFIX VARCHAR)
returns STRING
language javascript
as     
$$
  // 用模板字符串重构SQL查询，直接通过${}拼接变量
  var get_tables_name_stmt = `select table_name from information_schema.tables where table_type = 'BASE TABLE' and table_name like UPPER('${TABLEPREFIX}%');`;
  var get_tables_name_stmt = snowflake.createStatement({sqlText:get_tables_name_stmt });
  var tables = get_tables_name_stmt.execute();
  var row_count = get_tables_name_stmt.getRowCount();
  var cnt = 0; 
  var create_statement = `create or replace view dim_full_segment_lexids_tables_vw as \n`;
  
  while (tables.next())  {
      cnt += 1;
      var table_name = tables.getColumnValue(1); 
      create_statement += `select * from ` + table_name;
      // 用模板字符串处理换行和UNION ALL，避免转义问题
      if (cnt < row_count){
          create_statement += `\n union all \n`;
      }
   }
  
  var create_view_statement = snowflake.createStatement( {sqlText: create_statement} );
  create_view_statement.execute();
  return create_view_statement.getSqlText();
$$;

关键修改点 #

1. 将原本用"拼接的SQL字符串，全部替换为ES6模板字符串（反引号），通过${TABLEPREFIX}直接注入变量，彻底避免引号嵌套转义问题。
2. 替换所有"为模板字符串或双引号，确保JavaScript代码语法合法。
3. 补全语句末尾的分号，保持代码语法规范。

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