LOAD TABLE

LOAD TABLE table_name
[ SOURCE FIELDS (col1 type [, col2 type ] [, ...]) ]
[ FROM ('string1' [,'string2'] [, ...]) ]
EXTERNAL LOCATION location_name 
[ EXTERNAL FORMAT { format_name | 
  (TYPE file_type WITH (option value [, option value ] [, ...])) } ]
[ WITH (option value [, option value ] [, ...]) ]

table_name

Name of a regular user table that is the target for the load. The source files will be read from external storage and written to the table. To run LOAD TABLE commands, you must have BULK LOAD privilege on the database and INSERT privilege on the target table.

SOURCE FIELDS

Specify all of the fields found in the source files and their data types. These fields map to the columns in the target table that you want to load. You must specify all of the fields that appear in the source files, in order, but you can load a table that only contains a subset of those fields. For example, you can load a table with four columns from a source file that has five or more fields. You do not have to specify the same order of columns in the CREATE TABLE statement. The load operation can detect which columns correspond to which source fields by name and data type.

In this example, the source file contains five fields:

...
source fields(seasonid smallint,
  matchday timestamp,
  htid smallint,
  atid smallint,
  moment varchar(5))
...

You can load a table that has one or more columns that correspond to these fields. See also LOAD TABLE Examples.

Conversely, you can also load a table that has columns that do not have corresponding fields in the source file if DEFAULT column constraints are specified in the CREATE TABLE statement. For example, you can load a two-column table from a source file that has only one field. In this case, SOURCE FIELDS identifies the one field that exists in the source file; the second column in the table is loaded with the specified default value. For example:

create table t1 (c1 int, c2 int not null default 1);
...
load table t1 
source fields (c1 int)
...

Note: The SOURCE FIELDS option is not available in the Load Data Assistant.

FROM('string1','string2', ...)

Specify one or more character strings that represent a list of the S3 source files or a "prefix" that identifies those files. These files will be loaded from the specified external location, which is a bucket name.

If you do not specify a FROM list, the load operation will scan the entire bucket and load any files that have a schema that matches the columns in the target table. Therefore, it is recommended that you specify a FROM list or prefix.

If a string exactly matches the key of a specific file stored in the bucket, only that S3 file will be loaded. Otherwise, the string is checked for matches against the beginning part of the key of any objects in the bucket. All of the files that match will be loaded.

For example, the following commands all produce the same results, loading the same two files:

premdb=# load table match from ('/premdb/match.csv','/premdb/matchcopy.csv') external location premdbs3data;
INSERT 0 17212

premdb=# load table match from ('/premdb/match') external location premdbs3data;
INSERT 0 17212

Note: Wildcard patterns are not supported for S3 source files. For example, you cannot use the following FROM clause to load files that have a prefix of match:

from ('/premdb/mat*.csv')

Instead you could use:

from ('/premdb/mat')

EXTERNAL LOCATION

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

EXTERNAL FORMAT

Name of an external format object. See CREATE EXTERNAL FORMAT. Alternatively, you can define the format in the TYPE clause. You can also omit the EXTERNAL FORMAT clause completely and use the default format settings for the table.

TYPE...WITH

Specify the source file type: CSV, TEXT, PARQUET, or BCP. 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 load 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 (skip_blank_lines true, delimiter '|'))

See External Format Load Options.

WITH

The WITH clause at the end of the LOAD TABLE statement defines general load-processing options. Each name-value pair must consist of a valid option (in this context) and a valid setting for that option. These are separate and distinct from the formatting load options defined by the external format object or in the TYPE WITH() list.

For example:

with (source_compression 'gz') 

See LOAD TABLE Options.

load table match 
from ('/premdb/match.csv') 
external location premdbs3data;
INSERT 0 8606

load table newmatchstats 
from ('/premdb/newmat') 
external location premdbs3data;
INSERT 0 24785280

load table newmatchstats 
from ('/premdb/newmatchstats25mil.gz') 
external location premdbs3data 
with(source_compression 'gz');
INSERT 0 24785280

load table newmatchstats 
from ('/premdb/newmatchstats') 
external location premdbs3data 
with(source_compression 'gz');
ERROR:  [bulksvc-load] Unable to open channel for s3://ybpremdb/premdb/newmatchstats25mil.csv CAUSED BY: Input is not in the .gz format

load table newmatchstats 
from ('/premdb/newmatchstats25mil.csv') 
external location premdbs3data 
external format premdbs3format;

load table newmatchstats 
from ('/premdb/newmatchstats25mil.csv') 
external location premdbs3data 
external format (type csv with(skip_blank_lines true, delimiter ','));

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

delete from matchday;
DELETE  24785280

load table matchday
source fields(seasonid smallint,
  matchday timestamp,
  htid smallint,
  atid smallint,
  moment varchar)
from('/premdb/newmatch')
external location premdbs3data;
INSERT  24785280

create table t1(c1 int, c2 int);

load table t1 
source fields(c3 int, c2 int, c1 int)
...

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 one exists, for the external location object specified in the LOAD TABLE command