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"
}

FIELDS MAPPED AS COLUMN(ID, Name, Category, Stock)

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