Step 1. Create File Format Objects¶
When you load data files from an S3 bucket into a table, you must describe the format of the file and specify how the data in the file should be interpreted and processed. For example, if you are loading pipe-delimited data from a CSV file, you must specify that the file uses the CSV format with pipe symbols as delimiters.
When you execute the COPY INTO <table> command, you specify this format information. You can
either specify this information as options in the command (e.g.
TYPE = CSV
, FIELD_DELIMITER = '|'
, etc.) or you can specify a
file format object that contains this format information. You can create a named file
format object using the CREATE FILE FORMAT command.
In this step, you create file format objects describing the data format of the sample CSV and JSON data provided for this tutorial.
Creating a File Format Object for CSV Data¶
Execute the CREATE FILE FORMAT command
to create the mycsvformat
file format.
CREATE OR REPLACE FILE FORMAT mycsvformat
TYPE = 'CSV'
FIELD_DELIMITER = '|'
SKIP_HEADER = 1;
Where:
TYPE = 'CSV'
indicates the source file format type. CSV is the default file format type.FIELD_DELIMITER = '|'
indicates the ‘|’ character is a field separator. The default value is ‘,’.SKIP_HEADER = 1
indicates the source file includes one header line. The COPY command skips these header lines when loading data. The default value is 0.
Creating a File Format Object for JSON Data¶
Execute the CREATE FILE FORMAT command to create
the myjsonformat
file format.
CREATE OR REPLACE FILE FORMAT myjsonformat TYPE = 'JSON' STRIP_OUTER_ARRAY = TRUE;
Where:
TYPE = 'JSON'
indicates the source file format type.STRIP_OUTER_ARRAY = TRUE
directs the COPY command to exclude the root brackets ([]) when loading data to the table.