CODEWITHSUNDEEP
snowflake-cloud-data-platformsnowflake-schema
Het Naik
Het Naik

Reputation: 53

Dropping multiple file formats in Snowflake at once

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

Upvotes: 1

Views: 253

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175566

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:

enter image description here

Subsequent run:

enter image description here

Upvotes: 2

NickW
NickW

Reputation: 9768

This is not fully automated - but writing a stored proc to fully automate it is probably overkill if this is a one off task.

  1. Run show file formats

  2. Construct the drop statements from the results of the previous command:

    SELECT 'DROP FILE FORMAT '||"name"||';' from table(result_scan(last_query_id()));

  3. Copy the output from the previous step and run it in your UI

Upvotes: 2

Related Questions