Loading Data

Treat lines in the source file(s) that begin with string as comments and skip.

The default comment marker is #.

Use char as the source file field delimiter.

The default delimiter is a comma, unless a source file has one of these extensions:

• .psv - will cause | to be the delimiter
• .tsv - will cause the tab character to be the delimiter

See Delimited Text Option Characters for allowed characters.

Use char as the source file data escape character. The escape character preceding any other character, in the source data, will be converted into that other character, except in the following special cases:

For instance, if the escape character is \, a \t encountered in the data will be converted to a tab character when stored in the database.

The escape character can be used to escape the quoting character, and will be treated as an escape character whether it is within a quoted field value or not.

There is no default escape character.

Use char as the source file header field name/property delimiter, when the source file header contains both names and properties. This is largely specific to the Kinetica export to delimited text feature, which will, within each field's header, contain the field name and any associated properties, delimited by the pipe | character.

An example Kinetica header in a CSV file:

1

id|int|data,category|string|data|char16,name|string|data|char32

The default is the | (pipe) character. See Delimited Text Option Characters for allowed characters.

Declare that the source file(s) will or will not have a header.

The default is TRUE.

Treat string as the indicator of a null source field value.

The default is the empty string.

Use char as the source file data quoting character, for enclosing field values. Usually used to wrap field values that contain embedded delimiter characters, though any field may be enclosed in quote characters (for clarity, for instance). The quote character must appear as the first and last character of a field value in order to be interpreted as quoting the value. Within a quoted value, embedded quote characters may be escaped by preceding them with another quote character or the escape character specified by ESCAPE, if given.

The default is the " (double-quote) character. See Delimited Text Option Characters for allowed characters.

Name of the table containing records that failed to be loaded into the target table. This bad record table will include the following columns:

Use an ingest batch size of the given number of records.

The default batch size is 200,000.

Use the given type-specific formatting for the given column when parsing source data being loaded into that column. This should be a map of column names to format specifications, where each format specification is map of column type to data format, all formatted as a JSON string.

Supported column types include:

For example, to load dates of the format 2010.10.30 into date column d and times of the 24-hour format 18:36:54.789 into time column t:

{
    "d": {"date": "YYYY.MM.DD"},
    "t": {"time": "HH24:MI:SS.MS"}
}

Use the given formats for source data being loaded into target table columns with the corresponding column types. This should be a map of target column type to source format for data being loaded into columns of that type, formatted as a JSON string.

Supported column properties and source data formats are the same as those listed in the description of the COLUMN FORMATS option.

For example, to make the default format for loading source data dates like 2010.10.30 and 24-hour times like 18:36:54.789:

{
    "date": "YYYY.MM.DD",
    "time": "HH24:MI:SS.MS",
    "datetime": "YYYY.MM.DD HH24:MI:SS.MS"
}

Choose a comma-separated list of fields from the source file(s) to ignore, loading only those fields that are not in the identified list in the order they appear in the file. Fields can be identified by either POSITION or NAME. If ignoring by NAME, the specified names must match the source file field names exactly.

Choose a comma-separated list of fields from the source file(s) to load, in the specified order, identifying fields by either POSITION or NAME. If mapping by NAME, the specified names must match the source file field names exactly.

Whether to do a full ingest of the data or perform a dry run or type inference instead. The default mode is FULL.

When an error is encountered loading a record, handle it using one of the following modes. The default mode is ABORT.

Interval, in seconds, at which a data source is polled for updates. The number of seconds must be passed as a single-quoted string. Default is 60 seconds.

Optional primary key specification clause, where the value is a parenthesized comma-separated list of columns to use as the primary key for the table:

PRIMARY KEY = ('id', 'dept_id')

Optional shard key specification clause, where the value is a parenthesized comma-separated list of columns to use as the shard key for the table:

SHARD KEY = ('id')

Whether to subscribe to the data source specified in the DATA SOURCE option. The default value is FALSE.

Use an ingest batch size of n records.

The default batch size is 10,000.

Treat lines in the source file that begin with string as comments and skip.

The default comment marker is #.

Use char as the source file field delimiter.

The default delimiter is ,, unless the source file has one of these extensions:

• .psv - will cause | to be the delimiter
• .tsv - will cause the tab character to be the delimiter

See Option Characters for allowed characters.

Use char as the source file data escape character. The escape character preceding any other character, in the source data, will be converted into that other character, except in the following special cases:

For instance, if the escape character is \, a \t encountered in the data will be converted to a tab character when stored in the database.

The escape character can be used to escape the quoting character, and will be treated as an escape character whether it is within a quoted field value or not.

There is no default escape character. See Option Characters for allowed characters.

Truncate the target table before loading data.

The default is to not truncate the target table.

Limit the total number of records inserted into the target table to n. If reading from multiple source files, this count is assessed across all files being read. For example, if n is 15 and the three files being read have 10 records each, all of the records from the 1st file and the first 5 records from the 2nd file will be loaded. The remaining records in the 2nd file and all of the records from the 3rd file will be skipped.

The default is no limit.

Treat string as the indicator of a null source field value.

The default is \N.

When an error is encountered loading a record, handle it using using the given <mode>; one of the following:

The default mode is SKIP.

Use char as the source file data quoting character, for enclosing field values. Usually used to wrap field values that contain embedded delimiter characters, though any field may be enclosed in quote characters (for clarity, for instance). The quote character must appear as the first and last character of a field value in order to be interpreted as quoting the value. Within a quoted value, embedded quote characters may be escaped by preceding them with another quote character or the escape character specified by ESCAPE, if given.

The default is the " (double-quote) character. See Option Characters for allowed characters.

Skip the first n source file data lines read, not including header and comment lines. If reading from multiple source files, this count is assessed across all files being read. For example, if n is 15 and the first two files read have 10 records each, the first record to be loaded (not skipped) will be the 6th record of the 2nd file.

The default is to skip no records.