如何在未知表结构的情况下编写UDTF（用户定义表函数）

Snowflake的UDTF确实要求提前固定返回表的列结构，无法直接实现返回动态列的UDTF。以下是几种绕开该限制的可行方案，适配不同使用场景：

方案1：返回JSON字符串，配合内置函数动态解析为表 #

先实现一个返回JSON格式结果的UDF（而非UDTF），再用Snowflake内置函数将JSON转换为表结构，无需提前知晓列名。

步骤1：创建执行动态SQL并返回JSON的UDF #

CREATE OR REPLACE FUNCTION RUN_DYNAMIC_SQL(sql_str VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS $$
    // 执行传入的SQL语句
    const stmt = snowflake.createStatement({sqlText: SQL_STR});
    const rs = stmt.execute();
    
    // 获取结果集列名
    const cols = [];
    for(let i = 1; i <= rs.getColumnCount(); i++){
        cols.push(rs.getColumnName(i));
    }
    
    // 将结果集转换为JSON数组
    const result = [];
    while(rs.next()){
        const row = {};
        cols.forEach((col, idx) => {
            row[col] = rs.getColumnValue(idx + 1);
        });
        result.push(row);
    }
    
    return JSON.stringify(result);
$$;

步骤2：解析JSON为表结构 #

如果需要手动指定列：

SELECT 
    obj.value:id::INT AS id,
    obj.value:name::VARCHAR AS name,
    obj.value:create_time::TIMESTAMP AS create_time
FROM 
    TABLE(FLATTEN(PARSE_JSON(RUN_DYNAMIC_SQL('SELECT * FROM test_table')))) obj;

如果需要自动适配所有列，可通过动态SQL生成查询语句（需结合存储过程封装）：

-- 示例存储过程：自动生成解析JSON的查询语句
CREATE OR REPLACE PROCEDURE QUERY_DYNAMIC_RESULT(sql_str VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS $$
    // 获取JSON结果并解析列名
    const jsonResult = RUN_DYNAMIC_SQL(SQL_STR);
    const firstRow = JSON.parse(jsonResult)[0];
    const cols = Object.keys(firstRow);
    
    // 构造查询字段列表
    const selectCols = cols.map(col => `obj.value:${col}::${typeof firstRow[col]}`).join(', ');
    
    // 生成并执行最终查询
    const query = `SELECT ${selectCols} FROM TABLE(FLATTEN(PARSE_JSON('${jsonResult}'))) obj`;
    const stmt = snowflake.createStatement({sqlText: query});
    stmt.execute();
    
    return query;
$$;

调用后直接查看结果即可：

CALL QUERY_DYNAMIC_RESULT('SELECT * FROM test_table');

方案2：用存储过程生成临时表，直接查询临时表 #

这种方案更直观，适合需要多次复用动态查询结果的场景：

创建存储过程 #

CREATE OR REPLACE PROCEDURE RUN_SQL_TO_TEMP(sql_str VARCHAR, temp_table_name VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS $$
    // 清理已存在的临时表
    const dropStmt = snowflake.createStatement({sqlText: `DROP TABLE IF EXISTS ${TEMP_TABLE_NAME}`});
    dropStmt.execute();
    
    // 执行动态SQL并创建临时表
    const createStmt = snowflake.createStatement({sqlText: `CREATE OR REPLACE TEMPORARY TABLE ${TEMP_TABLE_NAME} AS ${SQL_STR}`});
    createStmt.execute();
    
    return `临时表 ${TEMP_TABLE_NAME} 已创建`;
$$;

使用方式 #

-- 调用存储过程生成临时表
CALL RUN_SQL_TO_TEMP('SELECT * FROM test_table', 'my_temp_table');

-- 直接查询临时表
SELECT * FROM my_temp_table;

方案3：结合RESULT_SCAN查询动态SQL结果 #

利用Snowflake的RESULT_SCAN函数，直接读取最近执行的动态SQL结果：

创建存储过程获取查询ID #

CREATE OR REPLACE PROCEDURE EXECUTE_DYNAMIC_SQL(sql_str VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS $$
    const stmt = snowflake.createStatement({sqlText: SQL_STR});
    stmt.execute();
    // 返回查询ID供RESULT_SCAN使用
    return stmt.getQueryId();
$$;

使用方式 #

-- 执行动态SQL并获取查询ID
CALL EXECUTE_DYNAMIC_SQL('SELECT * FROM test_table');

-- 替换为返回的查询ID，查询结果
SELECT * FROM TABLE(RESULT_SCAN('01234567-89ab-cdef-ghij-klmnopqrstuv'));

总结 #

直接实现返回动态列的UDTF在Snowflake中目前无法做到，因为UDTF的返回结构必须预定义。上述方案可根据场景选择：

• 若需嵌入SQL用表函数形式，优先选择JSON返回+动态解析的方案；
• 若允许分步骤操作，临时表方案更易用且性能更优；
• RESULT_SCAN适合临时查看单次动态查询的结果。

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