Stage and Copy - Useful Commands

To show the list of stages: 

• SHOW STAGES;
• DESC STAGE name_of_the_stage;

To show the list of file formats : 

• SHOW FILE FORMATS;
• DESC FILE FORMAT name_of_the_file_format;

Sample file format creation :

CREATE OR REPLACE FILE FORMAT emp_csv_format

  TYPE = 'CSV'

  FIELD_DELIMITER = ','

  SKIP_HEADER = 1

  FIELD_OPTIONALLY_ENCLOSED_BY = '"'

  NULL_IF = ('NULL', 'null');

Sample file format alter :

ALTER FILE FORMAT emp_csv_format

SET FIELD_DELIMITER = '|';

LIST @emp_named_stage;

Sample stage creation :

CREATE OR REPLACE STAGE emp_named_stage

  FILE_FORMAT = emp_csv_format;

Read data from the stage :

SELECT

        METADATA$FILENAME,

        METADATA$FILE_ROW_NUMBER,

        $1 AS column1_name, -- Referencing the first column

        $2 AS column2_name,  -- Referencing the second column

        $3 AS Age,

        $4 AS City,

        $5 AS unknowncolumn

    FROM @emp_named_stage (file_format => emp_csv_format) AS t;

Copy the data from stage to table :

COPY INTO emp_data FROM @emp_named_stage/samplecsv_error.csv ON_ERROR='continue';

Remove the file from stage :

REMOVE @emp_named_stage/samplecsv_error1.csv;

Validation mode :

COPY INTO emp_data

FROM @emp_named_stage/samplecsv_error1.csv

FILE_FORMAT = emp_csv_format

VALIDATION_MODE = 'RETURN_ERRORS';

To check the history of file load :

SELECT * FROM DIRECTORY(@name_of_the_stage);