CREATE EXTERNAL FORMAT ​

Create an external file format that you can reference when you load a table from external storage. This format defines the type of source file and a set of load configuration options.

CREATE EXTERNAL FORMAT [ IF NOT EXISTS ] name
TYPE file_type
WITH (option value [,option value] ...)

IF NOT EXISTS

Create the object if it does not already exist. If it does exist, do not create it and do not return an error.

TYPE

Specify the source file type, which defines the formatting style of the incoming data (how the source files were formatted by the export or unload tool that produced them). For flat files, the type defines how field delimiters are protected in the data.

• CSV: Delimiters in field values were protected by wrapping the field values in quotes. For example: "2012, Mini Cooper S, ALL4"
• TEXT: Delimiters in field values were protected by preceding them with a backslash escape character. For example: 2012\, Mini Cooper S\, ALL4
• BCP: Delimiters in fields were not protected (for compatibility with the Microsoft SQL Server bcp tool). For example: 2012, Mini Cooper S, ALL4
• PARQUET: a structured, columnar storage format (binary).

The default type is CSV.

TYPE is equivalent to the ybload --format option.

WITH

Each name-value pair must consist of a valid load option and a valid setting for that option. For example:

with (skip_blank_lines true, delimiter '|')

Note that these options do not exactly match the ybload client tool options, and not all of those options are supported. Do not use the -- prefix for options in the WITH clause. See External Format Load Options.

You must have the correct privileges to run this command. See ON EXTERNAL object.

Examples ​

Create an external format for CSV files with two load options:

premdb=> create external format premdbcsv 
type csv with (delimiter ',', skip_blank_lines 'true'); 
CREATE EXTERNAL FORMAT

Create an external format for parquet data with two load options:

premdb=> create external format premdbparquet 
type parquet 
with (ignore_unsupported_schema 'true', serialize_nested_as_json 'true');
CREATE EXTERNAL FORMAT