INSERT INTO ... SELECT ... FROM FILE

Kinetica can load data from text-based data files that are local to the client into existing tables using a modified INSERT INTO ... SELECT statement.

This command is only available through KiSQL or database clients configured with the Kinetica ODBC/JDBC driver.

When reading data from a file, Kinetica assumes the following format, able to be overridden using command options:

If there is a header row, it is either:

a simple list of comma-delimited column names:
id,category,name,description,stock

the Kinetica standard format:

id|int|data,category|string|data|char16,name|string|data|char32,description|string|data|char128|nullable,stock|int|data|nullable

The data fields are comma-delimited
Strings are optionally enclosed in double-quotes; double-quotes must be used when the data contains commas; two consecutive double-quotes are used as an escape code for double-quoted string data containing double-quotes; e.g.: "This string contains a "" quote mark and a "","" double-quoted comma." This would be loaded into a single string column in Kinetica as: This string contains a " quote mark and a "," double-quoted comma.

INSERT INTO ... SELECT ... FROM FILE Syntax

INSERT INTO [<schema name>.]<table name> [( <column list> )]
SELECT <field list>
FROM FILE."<file name | file name expression>"
[ <option list> ]

Parameters

Name of the schema containing the table into which data will be inserted

Name of the table into which data will be inserted

Optional list of target table columns into which data will be inserted; if not specified, all target table columns will be assumed. Whether specified or implied, this list needs to match the fields specified in <field list> in number, order, & type.

List of the source data file field names from which data will be extracted; use * to extract all columns (this wildcard must be used when a source file has no header which defines field names). This list needs to match all the target table columns (or those specified in the <column list> option) in number, order, & type.

Name of the source data file or set of source data files from which records will be extracted; wildcards (*) can be used to specify a group of files

Optional list of whitespace-separated processing options

Options

The following options can be specified when loading data from files. When reading from multiple files (using wildcards when specifying the file names), options specific to the source file will be applied to each file being read.

BATCH_SIZE=<n>

Use an ingest batch size of n records.The default batch size is 10,000.

COMMENT='<string>'

Treat lines in the source file that begin with string as comments and skip.The default comment marker is #.

DELIMITER='<char>'

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.

ESCAPE='<char>'

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:

Source Data String	Representation when Loaded into the Database
`<char>a`	ASCII bell
`<char>b`	ASCII backspace
`<char>f`	ASCII form feed
`<char>n`	ASCII line feed
`<char>r`	ASCII carriage return
`<char>t`	ASCII horizontal tab
`<char>v`	ASCII vertical tab

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.

HEADER=<Y|N>

Declare that the source file has or does not have a header. If not given, that determination will be intuited.

INITIAL_CLEAR

Truncate the target table before loading data.The default is to not truncate the target table.

LIMIT=<n>

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.

NULL='<string>'

Treat string as the indicator of a null source field value.The default is \N.

ON_ERROR=<mode>

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

Mode	Description
`PERMISSIVE`	If an error is encountered parsing a source record, attempt to insert as much of the valid fields from the record as possible.
`SKIP`	If an error is encountered parsing a source record, skip the record.
`ABORT`	If an error is encountered parsing a source record, stop the data load process. Primary key collisions are considered abortable errors in this mode.

The default mode is SKIP.

QUOTE='<char>'

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=<n>

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.

Option Characters

For DELIMITER, ESCAPE, & QUOTE, any single character can be used, or any one of the following escaped characters:

Escaped Char	Corresponding Source File Character
`''`	Single quote
`\\`	Backslash
`\a`	ASCII bell
`\b`	ASCII backspace
`\f`	ASCII form feed
`\t`	ASCII horizontal tab
`\v`	ASCII vertical tab

For instance, if two single quotes ('') are specified for a QUOTE character, the parser will interpret single quotes in the source file as quoting characters; specifying \t for DELIMITER will cause the parser to interpret ASCII horizontal tab characters in the source file as delimiter characters.

Examples

File Paths

Single File

To load a CSV file of product data located at ../data/products.csv into a table named product:

INSERT INTO ... SELECT ... FROM FILE (Single File) Example

INSERT INTO example.product
SELECT *
FROM FILE."../data/products.csv"

Multiple Files

To load all files whose names begin with products and end in a csv extension, located under ../data, into a table named product:

INSERT INTO ... SELECT ... FROM FILE (Multiple File) Example

INSERT INTO example.product
SELECT *
FROM FILE."../data/products*csv"

Options

Column/Field Names

To load specific fields from a product data file located at ../data/products.csv into specific target table columns:

INSERT INTO ... SELECT ... FROM FILE (Column/Field Names) Example

INSERT INTO example.product (id, stock, category, name)
SELECT ID, Stock, Category, Name
FROM FILE."../data/products.csv"

When specifying source data file field names, it is not required that target table column names also be specified, or vice versa. The only requirement is that the set of source data file fields selected align with the target table columns into which data will be inserted.

Load Options

To load data from a product data file with the following options:

file located at ../data/products.csv
only records 4 through 10 will be loaded
any error will cause the insert to fail
the target table will be truncated before loading
records will be inserted into the table in groups of 2

INSERT INTO ... SELECT ... FROM FILE with Load Options Example

INSERT INTO example.product
SELECT *
FROM FILE."../data/products.csv"
BATCH_SIZE=2
INITIAL_CLEAR
LIMIT=7
ON_ERROR=abort
SKIP=3

To load data from a product data file with the following options:

file located at ../data/products.ssv
the file has a header
the field delimiter is a semicolon
data requiring quotes will have single quotes as the quoting character
the escape character is the backtick
the data file represents null values as <null>
data file comments are on lines starting with --

INTO ... SELECT ... FROM FILE with Parse Options Example

INSERT INTO example.product
SELECT *
FROM FILE."../data/products.ssv"
COMMENT='--'
DELIMITER=';'
ESCAPE='`'
HEADER=Y
NULL='<null>'
QUOTE='\''

Considerations

Multi-Head

If Multi-Head Ingest has been enabled on the database server, file loading through ODBC/JDBC will automatically leverage it, when applicable.

Blank Lines

A blank line in a source data file will be treated as a valid record and will be inserted into the target table as a record with all null fields. If blank lines should not be considered as data, they should be prefixed with the comment marker in order to be skipped by the loading process.

GAdmin

While GAdmin does not support this command, it does support importing data from client-local files directly.

/execute/sql endpoint

While SQL file ingestion is available to ODBC/JDBC clients; it is not supported within the /execute/sql endpoint, either as a REST call directly or as an API call (like the Python API’s execute_sql() function).

​Parameters

​Options

​Option Characters

​Examples

​File Paths

​Single File

​Multiple Files

​Options

​Column/Field Names

​Load Options

​Considerations

Parameters

Options

Option Characters

Examples

File Paths

Single File

Multiple Files

Options

Column/Field Names

Load Options

Considerations