I need to drop multiple file formats (around 70) of a particular schema in Snowflake. I went through the documentation and also tried some work arounds but couldn't find any way to do it automatically.
Example:
schema - work
file formats - work_123, work_234, preview_567, preview_561, match_897, match_565
This is not fully automated - but writing a stored proc to fully automate it is probably overkill if this is a one off task.
Run show file formats
Construct the drop statements from the results of the previous command:
SELECT 'DROP FILE FORMAT '||"name"||';' from table(result_scan(last_query_id()));
Copy the output from the previous step and run it in your UI
It can be fully automated with Snowflake Scripting:
DECLARE
SCHEMA_NAME STRING := 'WORK';
FORMAT_NAME_PATTERN STRING := '%';
QUERY STRING;
BEGIN
SELECT CONCAT('BEGIN', CHAR(10)
,LISTAGG(CONCAT('DROP FILE FORMAT IF EXISTS '
,FILE_FORMAT_SCHEMA, '.', FILE_FORMAT_NAME, ';', CHAR(10)), '')
,'END')
INTO :QUERY
FROM INFORMATION_SCHEMA.FILE_FORMATS
WHERE FILE_FORMAT_SCHEMA ILIKE :SCHEMA_NAME
AND FILE_FORMAT_NAME ILIKE :FORMAT_NAME_PATTERN ESCAPE '!'
GROUP BY GROUPING SETS(());
QUERY := COALESCE(QUERY, 'SELECT ''No file format found''');
-- Uncomment to run
-- EXECUTE IMMEDIATE :QUERY;
RETURN :QUERY;
END;
For sample data:
CREATE SCHEMA WORK;
CREATE FILE FORMAT work_123 TYPE=csv;
CREATE FILE FORMAT work_232 TYPE=csv;
CREATE FILE FORMAT preview_567 TYPE=JSON;
The query to be executed is:
BEGIN
DROP FILE FORMAT IF EXISTS WORK.PREVIEW_567;
DROP FILE FORMAT IF EXISTS WORK.WORK_123;
DROP FILE FORMAT IF EXISTS WORK.WORK_232;
END
First run:
Subsequent run:
