Loading Data

• Loading server-accessible data sets via SQL (or the /execute/sql endpoint call) that either:
  • can be reached by Kinetica via an external data source object
  • have been uploaded to Kinetica via SQL or API
• Loading client-side files via ODBC/JDBC

​

LOAD INTO

<COPY | LOAD> [DATA] INTO [<schema name>.]<table name>
<
   FROM REMOTE QUERY '<source data query>'
   |
   FROM FILE PATHS <file paths>
      [FORMAT <[DELIMITED] TEXT [(<delimited text options>)] | AVRO | JSON | PARQUET | SHAPEFILE>]
>
[WITH OPTIONS (<load option name> = '<load option value>'[,...])]
[<table property clause>]

• local files uploaded into KiFS
• a data source configured to allow access to:
  • remote files on Azure, GCS, HDFS, or S3
  • remote messages on a Kafka topic
  • a remote database queryable via JDBC

​

Parameters

​

Delimited Text Options

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

​

Delimited Text Option Characters

​

Load Options

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

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

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

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

SHARD KEY = ('id')

​

Table Property Clause

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

USING TABLE PROPERTIES
(
    <table property> = <value>,
    ...
    <table property> = <value>
)

​

Examples

​

File Paths

Single File

LOAD DATA INTO example.product
FROM FILE PATHS 'kifs://data/products.csv'

Multiple Files

LOAD DATA INTO example.product
FROM FILE PATHS 'kifs://data/products.csv', 'kifs://data/products.kh.csv'

​

File Formats

Single File - No Header

LOAD DATA INTO example.product
FROM FILE PATHS 'kifs://data/products.nh.csv'
FORMAT TEXT (INCLUDES HEADER = FALSE)

Delimited Text Options

• data file named products.ssv located in the KiFS directory data
• 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 --
• when parse errors are encountered, the record will be skipped

LOAD DATA INTO example.product
FROM FILE PATHS 'kifs://data/products.ssv'
FORMAT TEXT
(
	COMMENT = '--',
	DELIMITER = ';',
	ESCAPE = '`',
	INCLUDES HEADER = TRUE,
	NULL = '<null>',
	QUOTE = ''''
)
WITH OPTIONS (ON ERROR = SKIP)

Kinetica Headers

• data file named products.kh.csv located in the KiFS directory data
• data file contains a Kinetica header
• target table named product_create_on_load
• target table will be created if it doesn’t exist

LOAD DATA INTO example.product_create_on_load
FROM FILE PATHS 'kifs://data/products.kh.csv'

Parquet File

LOAD DATA INTO example.employee_2000
FROM FILE PATHS 'kifs://data/employee.parquet'
FORMAT PARQUET

Kafka

• Kafka remote source is represented by the kafka_ds data source
• data will be continuously streamed from the Kafka source
• target table named orders

LOAD DATA INTO example.orders
FORMAT JSON
WITH OPTIONS (DATA SOURCE = 'kafka_ds', SUBSCRIBE = TRUE)

​

Table Options

Primary/Shard Keys

• data file named employee.csv located in the KiFS directory data
• data file does not contain a Kinetica header
• target table named example.employee
• target table will be created if it doesn’t exist
• target table will have a composite primary key on the id & dept_id columns and a shard key on id column, if it is created by this load command

LOAD INTO example.employee
FROM FILE PATHS 'kifs://data/employee.csv'
WITH OPTIONS
(
    PRIMARY KEY = (id, dept_id),
    SHARD KEY = (id)
)

Creation Options

• data file named products.kh.csv located in the KiFS directory data
• data file does not contain a Kinetica header
• target table named product_create_options
• target table will be created if it doesn’t exist
• target table will have a chunk size of 1,000,000 records, if it is created by this load command
• target table will have a TTL of 5 minutes, and will be dropped if unused for any 5-minute span, if it is created by this load command

LOAD DATA INTO example.product_create_options
FROM FILE PATHS 'kifs://data/products.csv'
USING TABLE PROPERTIES (CHUNK SIZE = 1000000, TTL = 5)

​

Column Options

Column/Field Names

• data file named products.title-case.csv located in the KiFS directory data
• data file contains a header with fields named ID, Category, Name, Description, & Stock
• target table named product_name_stock
• target columns named id, name, & stock
• only load fields ID, Name, & Stock into columns id, name, & stock
• skip records that fail to load successfully

LOAD DATA INTO example.product_name_stock
FROM FILE PATHS 'kifs://data/products.title-case.csv'
WITH OPTIONS (ON ERROR = SKIP, FIELDS MAPPED BY NAME(ID, Name, Stock))

Date/Time Column Types

LOAD INTO example.employee
FROM FILE PATHS 'kifs://data/employee.csv'
WITH OPTIONS
(
	COLUMN FORMATS = '
	{
		"hire_date": {"date": "YYYY-MM-DD"}
	}'
)

​

Dry Run

LOAD DATA INTO example.product
FROM FILE PATHS 'kifs://data/products.csv'
WITH OPTIONS (INGESTION MODE = DRY RUN)

​

Data Sources

File-Based

LOAD DATA INTO example.product
FROM FILE PATHS 'products.csv'
WITH OPTIONS (DATA SOURCE = 'example.product_ds')

Query-Based

LOAD INTO example.employee_dept2
FROM REMOTE QUERY 'SELECT * FROM example.employee WHERE dept_id = 2'
WITH OPTIONS (DATA SOURCE = 'example.jdbc_ds')

​

Change Data Capture

File-Based

• data pulled through a data source, example.order_ds
• data files contained with an orders directory
• initially, all files in the directory will be loaded; subsequently, only those files that have been updated since the last check will be reloaded
• files will be polled for updates every 5 minutes
• target table named example.orders
• target table will be created if it doesn’t exist

-- Load files in the orders directory with timestamps newer than the time of the
--   previous poll interval; poll every 5 minutes
LOAD DATA INTO example.orders
FROM FILE PATHS 'orders/'
WITH OPTIONS
(
	DATA SOURCE = 'example.order_ds',
	SUBSCRIBE = TRUE,
	POLL_INTERVAL = 300
)

Query-Based

• data pulled through a data source, example.jdbc_ds
• data contained with an example.orders table, where only orders for product with ID 42 will be loaded into the target table
• initially, all orders will be loaded; subsequently, only those orders with an order_id column value higher than the highest one on the previous poll cycle will be loaded
• remote table will be polled for updates every 60 seconds
• target table named example.order_product42
• target table will be created if it doesn’t exist

-- Load new orders for product 42 continuously into a table
--   order_id is an ever-increasing sequence allotted to each new order
LOAD INTO example.order_product42
FROM REMOTE QUERY 'SELECT * FROM example.orders WHERE product_id = 42'
WITH OPTIONS
(
	DATA SOURCE = 'example.jdbc_ds',
	SUBSCRIBE = TRUE,
	REMOTE_QUERY_INCREASING_COLUMN = 'order_id'
)

​

INSERT INTO … SELECT … FROM FILE

• 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 [<schema name>.]<table name> [( <column list> )]
SELECT <field list>
FROM FILE."<file name | file name expression>"
[ <option list> ]

​

Parameters

​

Options

​

Option Characters

​

Examples

​

File Paths

Single File

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

Multiple Files

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

​

Options

Column/Field Names

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

Load 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 example.product
SELECT *
FROM FILE."../data/products.csv"
BATCH_SIZE=2
INITIAL_CLEAR
LIMIT=7
ON_ERROR=abort
SKIP=3

• 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 --

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

​

Considerations

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

• 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.

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

• 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).