Untitled
unknown
plain_text
2 months ago
1.5 kB
2
Indexable
USE SCHEMA {{ SNOWFLAKE_DATABASE_NAME }}.{{ EXTERNAL_TABLES_SCHEMA }}; CREATE OR REPLACE PROCEDURE create_tasks_for_iceberg_refresh(database_name STRING) RETURNS STRING LANGUAGE SQL AS $$ DECLARE tbl_name STRING; task_name STRING; task_create_command STRING; task_resume_command STRING; res RESULTSET; BEGIN res := ( SELECT table_name FROM {{ SNOWFLAKE_DATABASE_NAME }}.information_schema.tables WHERE table_schema = '{{ EXTERNAL_TABLES_SCHEMA }}' AND is_iceberg = 'YES' ); FOR tbl IN res DO task_name := 'refresh_iceberg_metadata_' || tbl.table_name; task_create_command := ' CREATE OR REPLACE TASK ' || task_name || ' WAREHOUSE = LOAD_WH SCHEDULE = ''USING CRON */5 * * * * UTC'' AS BEGIN ALTER ICEBERG TABLE {{ SNOWFLAKE_DATABASE_NAME }}.{{ EXTERNAL_TABLES_SCHEMA }}.' || tbl.table_name || ' REFRESH; END;'; task_resume_command := ' ALTER TASK {{ SNOWFLAKE_DATABASE_NAME }}.{{ EXTERNAL_TABLES_SCHEMA }}.' || task_name || ' RESUME;'; EXECUTE IMMEDIATE task_create_command; EXECUTE IMMEDIATE task_resume_command; END FOR; RETURN 'Tasks created successfully for all tables in {{ SNOWFLAKE_DATABASE_NAME }}.'; END; $$; CALL create_tasks_for_iceberg_refresh('{{ SNOWFLAKE_DATABASE_NAME }}');
Editor is loading...
Leave a Comment