UNLOAD TABLE

Prerequisite: This feature is currently in beta. To enable it, set enable_sql_unload to ON.

Bulk unload table data to external storage using a SQL command. This feature is a SQL-native alternative to the ybunload client.

UNLOAD TABLE [ table_name | SELECT query ]
INTO 'file_path'
EXTERNAL LOCATION location_name 
[ EXTERNAL FORMAT { format_name | 
  (TYPE file_type WITH (option value [, option value ] [, ...])) } ]

table_name or SQL view

Name of the table or view to unload. Data will be read from the table and written to external storage. To execute UNLOAD TABLE, you must have SELECT privilege on the specified table and / or view.

query

A valid SELECT statement for unload. You cannot unload the results of other SQL statements, such as INSERT or CREATE TABLE AS (CTAS). SELECT privilege is required on all referenced tables and views in the query.

file_path

Path within the specified external location where output files will be written.

EXTERNAL LOCATION

Name of an external location object. See CREATE EXTERNAL LOCATION.

Note: Only S3-compatible external storage locations are currently supported.

EXTERNAL FORMAT

Name of an external format object. See CREATE EXTERNAL FORMAT. Alternatively, you can define the format inline using the TYPE clause. You may also omit the EXTERNAL FORMAT clause to use the default format configured for the external location.

Note: The External Format specified must have unload mode.

TYPE...WITH

Specify the target file type: CSV or TEXT. You must enclose the TYPE clause inside parentheses, and the following WITH clause inside its own set of parentheses:

(TYPE format_type WITH(...))

The WITH clause that follows TYPE defines formatting unload options, such as the line separator, field delimiter, and field definitions for different data types. Each name-value pair must consist of a valid option (in this context) and a valid setting for that option. For example:

(TYPE csv WITH (delimiter ',', linesep '\n'))

Options

The TYPE...WITH clause in the UNLOAD TABLE statement supports options listed in External Format Unload Options

Order of Precedence for Format Specifications

There are three different ways to specify the format for an UNLOAD TABLE operation. The following order of precedence is enforced:

1. In-line format options, as specified with EXTERNAL FORMAT (TYPE...WITH(…))
2. An in-line format name, as specified with EXTERNAL FORMAT format_name
3. A default format, if defined, for the external location object specified in the UNLOAD TABLE command

Examples

An example of the premdbs3data external location can be found here.

Unload a table to CSV files:

unload table match
into '/premdb/match/'
external location premdbs3data
external format (type csv);

Unload a table to GZ-compressed files:

unload table newmatchstats
into '/premdb/newmatchstats/'
external location premdbs3data
external format (type csv with (compress 'gzip'));

Unload a table and reference an existing external format object:

unload table newmatchstats
into '/premdb/newmatchstats25mil/'
external location premdbs3data
external format premdbs3format;

Unload a table and reference an existing external location that has external format implicitly defined:

unload table newmatchstats
into '/premdb/newmatchstats25mil/'
external location premdbs3data;

Unload data using a select query from an existing table.

create table matchday as
select seasonid, matchday, htid, atid from newmatchstats;
--SELECT  24785280

unload table (select seasonid, matchday from matchday)
into '/premdb/newmatch'
external location premdbs3data;