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 50,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.

• Identifying by Name:

  FIELDS IGNORED BY NAME(Category, Description)
  

• Identifying by Position:

  FIELDS IGNORED BY POSITION(3, 4)
  

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.

• Identifying by Name:

  FIELDS MAPPED BY NAME(ID, Name, Stock)
  

• Identifying by Position:

  FIELDS MAPPED BY POSITION(1, 2, 5)
  

Specify the error suppression policy for inserting duplicate primary key values into a table with a primary key. If the specified table does not have a primary key or the UPDATE_ON_EXISTING_PK option is used, then this options has no effect.

The default is FALSE.

Whether to do a full ingest of the data or perform a dry run or type inference instead.

The default mode is FULL.

Retrieve this many records at a time from the remote database. Lowering this number will help tables with large record sizes fit into available memory during ingest.

The default is 50,000.

Run the single given statement before the initial load is performed. Also run it before each subsequent reload, if SUBSCRIBE is TRUE.

For example, to set the time zone to UTC before running each load, use:

JDBC_SESSION_INIT_STATEMENT = 'SET TIME ZONE ''UTC'''

Whether to start a Kafka subscription at the earliest or latest offset, for data sources configured for Kafka. The default is earliest.

The number of remote query partitions to assign each Kinetica worker process. The queries assigned to a worker process will be executed by the tasks allotted to the process.

To decrease memory pressure, increase the number of splits per rank.

The default is 8 splits per rank.

The number of tasks to use on each Kinetica worker process to process remote queries. The tasks assigned to a worker process will execute any remote query partitions assigned to it.

To decrease memory pressure, decrease the number of tasks per rank.

The default is 8 tasks per rank.

When an error is encountered loading a record, handle it using either 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.

The default interval is 60 seconds. This option is only applicable when SUBSCRIBE is TRUE.

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')

For a JDBC query change data capture loading scheme, the remote query column that will be used to determine whether a record is new and should be loaded or not. This column should have an ever-increasing value and be of an integral or date/timestamp type. Often, this column will be a sequence-based ID or create/modify timestamp.

This option is only applicable when SUBSCRIBE is TRUE.

Whether to not distribute the retrieval of remote data and issue queries for blocks of data at time in parallel.

The default is FALSE.

Ordering expression to use in partitioning remote data for retrieval. The remote data will be ordered according to this expression and then retrieved in sequential blocks from the first record. This is potentially less performant than using REMOTE_QUERY_PARTITION_COLUMN.

If REMOTE_QUERY_NO_SPLIT is TRUE, a valid REMOTE_QUERY_PARTITION_COLUMN is specified, or the column given is invalid, this option is ignored.

Column to use to partition remote data for retrieval. The column must be numeric and should be relatively evenly distributed so that queries using values of this column to partition data will retrieve relatively consistently-sized result sets.

If REMOTE_QUERY_NO_SPLIT is TRUE or the column given is invalid, this option is ignored.

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 is FALSE.

Specify the string truncation policy for inserting text into VARCHAR columns that are not large enough to hold the entire text value.

The default is FALSE.

Specify whether to truncate the target table before beginning the load or subscription.

The default is FALSE (don't truncate the target table).

When making a type inference of the data values in order to define column types for the target table, use one of the following modes.

The default mode is SPEED.

Specify the record collision policy for inserting into a table with a primary key. If the specified table does not have a primary key, then this options has no effect.

The default 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.