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 [mode] 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 mode

Specify the usage of this format. This guarantees the options are valid at the time of performing the load / unload operation.

• load : to be used in LOAD TABLE commands
• unload : to be used in UNLOAD TABLE commands

The default mode is load if unspecified.

TYPE file_type

Specify the file type, which defines the formatting style of the data files. 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.

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/ybunload 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 and External Format Unload 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

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

premdb=> create external format unloadcsv
type unload csv with (delimiter ',', linesep '\n');
CREATE EXTERNAL FORMAT