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;
Copy

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;
Copy

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.

Next: Step 2. Create Stage Objects

Language: English