Loading Data

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

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

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

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

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

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

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

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

LOAD INTO example.ts
FROM REMOTE QUERY 'SELECT ts FROM example.ts_source'
WITH OPTIONS
(
	DATA SOURCE = 'example.jdbc_ds',
	JDBC_SESSION_INIT_STATEMENT = 'SET TIME ZONE ''EST'''
)

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

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

LOAD DATA INTO example.product
FROM FILE PATHS 'kifs://data/products.dat'
FORMAT TEXT

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

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

LOAD DATA INTO example.product_name_stock
FROM FILE PATHS 'kifs://data/products.title-case.csv'
WITH OPTIONS (FIELDS IGNORED BY POSITION(2, 4))

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

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

LOAD DATA INTO example.events
FROM FILE PATHS 'kifs://data/events.json'

LOAD DATA INTO example.product
FROM FILE PATHS 'kifs://data/products.js'
FORMAT JSON

LOAD DATA INTO example.product_name_stock
FROM FILE PATHS 'kifs://data/products.json'
WITH OPTIONS (FIELDS MAPPED BY NAME(id, name, stock))

LOAD DATA INTO example.product_name_stock
FROM FILE PATHS 'kifs://data/products.json'
WITH OPTIONS (FIELDS IGNORED BY NAME(category, description))

LOAD DATA INTO example.ext_employee
FROM FILE PATHS 'kifs://data/employee.parquet'

CREATE EXTERNAL TABLE example.ext_employee
FILE PATHS 'kifs://data/employee.pq'
FORMAT PARQUET

LOAD DATA INTO example.product_name_stock
FROM FILE PATHS 'kifs://data/products.parquet'
WITH OPTIONS (FIELDS MAPPED BY NAME(id, name, stock))

LOAD DATA INTO example.product_name_stock
FROM FILE PATHS 'kifs://data/products.parquet'
WITH OPTIONS (FIELDS IGNORED BY NAME(category, description))

LOAD DATA INTO example.events
FROM FILE PATHS 'shapefile/events.shp'
WITH OPTIONS (DATA SOURCE = 'example.events_ds')

LOAD DATA INTO example.events
FROM FILE PATHS 'kifs://data/events.shp'
FORMAT SHAPEFILE

LOAD DATA INTO example.events
FROM FILE PATHS 'kifs://data/events.shp'
FORMAT SHAPEFILE
WITH OPTIONS (FIELDS MAPPED BY NAME(ID, kiGeometry))

LOAD DATA INTO example.events
FROM FILE PATHS 'kifs://data/events.shp'
FORMAT SHAPEFILE
WITH OPTIONS (FIELDS IGNORED BY NAME(type))

LOAD INTO example.product
FROM FILE PATHS 'kifs://data/products.avro'
FORMAT AVRO

LOAD INTO example.product
FROM FILE PATHS 'kifs://data/products.schemaless.avro'
FORMAT AVRO
WITH OPTIONS (
    AVRO_SCHEMA = '{"type":"record","name":"Product","namespace":"example","fields":[{"name":"id","type":"int"},{"name":"category","type":"string"},{"name":"name","type":"string"},{"name":"description","type":["null","string"],"default":null},{"name":"stock","type":["null","int"],"default":null}]}'
)

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

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

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

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

LOAD INTO example.employee_local
FROM REMOTE QUERY 'SELECT * FROM example.employee'
WITH OPTIONS
(
	DATA SOURCE = 'example.jdbc_ds',
	REMOTE_QUERY_PARTITION_COLUMN = 'id'
)

LOAD INTO example.employee_local
FROM REMOTE QUERY 'SELECT * FROM example.employee'
WITH OPTIONS
(
	DATA SOURCE = 'example.jdbc_ds',
	REMOTE_QUERY_ORDER_BY = 'hire_date, dept_id'
)

LOAD INTO example.employee_local
FROM REMOTE QUERY 'SELECT * FROM example.employee'
WITH OPTIONS
(
	DATA SOURCE = 'example.jdbc_ds',
	REMOTE_QUERY_NO_SPLIT = true
)

LOAD INTO example.employee_local
FROM REMOTE QUERY 'SELECT * FROM example.employee'
WITH OPTIONS
(
	DATA SOURCE = 'example.jdbc_ds',
	NUM_SPLITS_PER_RANK = 16,
	NUM_TASKS_PER_RANK = 4
)

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

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

LOAD DATA INTO example.product_load_options
FROM FILE PATHS 'kifs://data/products.csv'
WITH OPTIONS (BATCH SIZE = 20000)

CREATE EXTERNAL TABLE example.ext_product
FILE PATHS 'products.csv'
WITH OPTIONS (DATA SOURCE = 'example.product_ds', REFRESH ON START = TRUE)

CREATE EXTERNAL TABLE example.ext_order
FILE PATHS 'orders/'
WITH OPTIONS (DATA SOURCE = 'example.order_ds', SUBSCRIBE = TRUE)

CREATE EXTERNAL TABLE example.ext_employee_dept2
REMOTE QUERY 'SELECT * FROM example.employee WHERE dept_id = 2'
WITH OPTIONS (DATA SOURCE = 'example.jdbc_ds')

-- Load new orders for product 42 continuously into a table
--   order_id is an ever-increasing sequence allotted to each new order
CREATE EXTERNAL TABLE example.ext_order_product42
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'
)