snowflake-cloud-data-platform - 如何获取雪花数据库中所有 View 的定义？

Question

请求是从生产环境中获取所有 View 的定义并刷新下层环境。我认为，循环中的 GET_DDL 可以满足需要，但不确定如何实现它。请指教。

Answer 1

假设 VIEW_DEFITION 是不够的:

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA != 'INFORMATION_SCHEMA';

和GET_DDL是必需的:

DECLARE
CUR CURSOR FOR SELECT CONCAT_WS('.',TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME) AS name
               FROM INFORMATION_SCHEMA.VIEWS
               WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA');
BEGIN
  CREATE OR REPLACE TEMPORARY TABLE temp_view_defs(view_name TEXT, definition TEXT);

  FOR rec IN CUR DO   
    EXECUTE IMMEDIATE REPLACE('INSERT INTO temp_view_defs(view_name, definition)
                        SELECT ''<view_name>'', GET_DDL(''TABLE'', ''<view_name>'')'
                        ,'<view_name>'
                        ,rec.name);
 END FOR;

 LET rs RESULTSET := (SELECT * FROM temp_view_defs);

 RETURN TABLE(rs);
END;

示例输出: