> ## Documentation Index
> Fetch the complete documentation index at: https://docs.kinetica.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Loading Data

> Copy-paste examples of loading data with SQL

## LOAD INTO

### Single & Multiple KiFS Files

<CodeGroup>
  ```sql Single File theme={null}
  LOAD DATA INTO example.product
  FROM FILE PATHS 'kifs://data/products.csv'
  ```

  ```sql Multiple Files by List theme={null}
  LOAD DATA INTO example.product
  FROM FILE PATHS 'kifs://data/products.csv', 'kifs://data/products.kh.csv'
  ```
</CodeGroup>

### Data Sources

#### File/Queue-Based

<CodeGroup>
  ```sql One-Time Load theme={null}
  LOAD DATA INTO example.product
  FROM FILE PATHS 'products.csv'
  WITH OPTIONS (DATA SOURCE = 'example.product_ds')
  ```

  ```sql Change Data Capture theme={null}
  -- 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
  )
  ```

  ```sql Streaming theme={null}
  LOAD DATA INTO example.orders
  FORMAT JSON
  WITH OPTIONS (DATA SOURCE = 'kafka_ds', SUBSCRIBE = TRUE)
  ```

  ```sql Streaming w/ Schema Registry theme={null}
  LOAD DATA INTO example.orders
  FORMAT AVRO
  WITH OPTIONS
  (
  	DATA SOURCE = 'kafka_ds',
  	SCHEMA_REGISTRY_SCHEMA_NAME = 'order_schema',
  	SUBSCRIBE = TRUE
  )
  ```
</CodeGroup>

#### Query-Based

<CodeGroup>
  ```sql One-Time Load theme={null}
  LOAD INTO example.employee_dept2
  FROM REMOTE QUERY 'SELECT * FROM example.employee WHERE dept_id = 2'
  WITH OPTIONS (DATA SOURCE = 'example.jdbc_ds')
  ```

  ```sql Change Data Capture theme={null}
  -- 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'
  )
  ```

  ```sql Init Options theme={null}
  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'''
  )
  ```

  ```sql Sizing Options theme={null}
  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
  )
  ```
</CodeGroup>

### File Types

#### Delimited Text Files

<CodeGroup>
  ```sql CSV theme={null}
  LOAD DATA INTO example.product
  FROM FILE PATHS 'kifs://data/products.csv'
  ```

  ```sql Forced Format theme={null}
  LOAD DATA INTO example.product
  FROM FILE PATHS 'kifs://data/products.dat'
  FORMAT TEXT
  ```

  ```sql No Header theme={null}
  LOAD DATA INTO example.product
  FROM FILE PATHS 'kifs://data/products.nh.csv'
  FORMAT TEXT (INCLUDES HEADER = FALSE)
  ```

  ```sql Columns Mapped by Name theme={null}
  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))
  ```

  ```sql Columns Ignored by Position theme={null}
  LOAD DATA INTO example.product_name_stock
  FROM FILE PATHS 'kifs://data/products.title-case.csv'
  WITH OPTIONS (FIELDS IGNORED BY POSITION(2, 4))
  ```

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

#### JSON/GeoJSON Files

<CodeGroup>
  ```sql JSON File theme={null}
  LOAD DATA INTO example.product
  FROM FILE PATHS 'kifs://data/products.json'
  ```

  ```sql GeoJSON File theme={null}
  LOAD DATA INTO example.events
  FROM FILE PATHS 'kifs://data/events.json'
  ```

  ```sql Forced Format theme={null}
  LOAD DATA INTO example.product
  FROM FILE PATHS 'kifs://data/products.js'
  FORMAT JSON
  ```

  ```sql Properties Mapped by Name theme={null}
  LOAD DATA INTO example.product_name_stock
  FROM FILE PATHS 'kifs://data/products.json'
  WITH OPTIONS (FIELDS MAPPED BY NAME(id, name, stock))
  ```

  ```sql Properties Ignored by Name theme={null}
  LOAD DATA INTO example.product_name_stock
  FROM FILE PATHS 'kifs://data/products.json'
  WITH OPTIONS (FIELDS IGNORED BY NAME(category, description))
  ```
</CodeGroup>

#### Parquet Files

<CodeGroup>
  ```sql Implicit Format theme={null}
  LOAD DATA INTO example.ext_employee
  FROM FILE PATHS 'kifs://data/employee.parquet'
  ```

  ```sql Forced Format theme={null}
  CREATE EXTERNAL TABLE example.ext_employee
  FILE PATHS 'kifs://data/employee.pq'
  FORMAT PARQUET
  ```

  ```sql Properties Mapped By Name theme={null}
  LOAD DATA INTO example.product_name_stock
  FROM FILE PATHS 'kifs://data/products.parquet'
  WITH OPTIONS (FIELDS MAPPED BY NAME(id, name, stock))
  ```

  ```sql Properties Ignored By Name theme={null}
  LOAD DATA INTO example.product_name_stock
  FROM FILE PATHS 'kifs://data/products.parquet'
  WITH OPTIONS (FIELDS IGNORED BY NAME(category, description))
  ```
</CodeGroup>

#### Shapefiles

<CodeGroup>
  ```sql Implicit Format theme={null}
  LOAD DATA INTO example.events
  FROM FILE PATHS 'shapefile/events.shp'
  WITH OPTIONS (DATA SOURCE = 'example.events_ds')
  ```

  ```sql Forced Format theme={null}
  LOAD DATA INTO example.events
  FROM FILE PATHS 'kifs://data/events.shp'
  FORMAT SHAPEFILE
  ```

  ```sql Properties Mapped by Name theme={null}
  LOAD DATA INTO example.events
  FROM FILE PATHS 'kifs://data/events.shp'
  FORMAT SHAPEFILE
  WITH OPTIONS (FIELDS MAPPED BY NAME(ID, kiGeometry))
  ```

  ```sql Properties Ignored by Name theme={null}
  LOAD DATA INTO example.events
  FROM FILE PATHS 'kifs://data/events.shp'
  FORMAT SHAPEFILE
  WITH OPTIONS (FIELDS IGNORED BY NAME(type))
  ```
</CodeGroup>

#### Avro Files

<CodeGroup>
  ```sql Embedded Schema theme={null}
  LOAD DATA INTO example.orders
  FORMAT AVRO
  WITH OPTIONS
  (
  	DATA SOURCE = 'kafka_ds',
  	SUBSCRIBE = TRUE
  )
  ```

  ```sql Schema Registry Lookup theme={null}
  LOAD DATA INTO example.orders
  FORMAT AVRO
  WITH OPTIONS
  (
  	DATA SOURCE = 'kafka_ds',
  	SCHEMA_REGISTRY_SCHEMA_NAME = 'order_schema',
  	SUBSCRIBE = TRUE
  )
  ```
</CodeGroup>

### Primary/Shard Keys

<CodeGroup>
  ```sql Primary Key theme={null}
  LOAD INTO example.employee
  FROM FILE PATHS 'kifs://data/employee.parquet'
  WITH OPTIONS (PRIMARY KEY = (id))
  ```

  ```sql Primary & Shard Key theme={null}
  LOAD INTO example.employee
  FROM FILE PATHS 'kifs://data/employee.csv'
  WITH OPTIONS
  (
      PRIMARY KEY = (id, dept_id),
      SHARD KEY = (id)
  )
  ```
</CodeGroup>

### Query Partitioning Options

<CodeGroup>
  ```sql Partition Column theme={null}
  LOAD INTO example.employee_local
  FROM REMOTE QUERY 'SELECT * FROM example.employee'
  WITH OPTIONS
  (
  	DATA SOURCE = 'example.jdbc_ds',
  	REMOTE_QUERY_PARTITION_COLUMN = 'id'
  )
  ```

  ```sql Ordered Blocks theme={null}
  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'
  )
  ```

  ```sql Disable Partitioning theme={null}
  LOAD INTO example.employee_local
  FROM REMOTE QUERY 'SELECT * FROM example.employee'
  WITH OPTIONS
  (
  	DATA SOURCE = 'example.jdbc_ds',
  	REMOTE_QUERY_NO_SPLIT = true
  )
  ```

  ```sql Rank/Split Processing theme={null}
  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
  )
  ```
</CodeGroup>

### Other Options

<CodeGroup>
  ```sql Column Formats theme={null}
  LOAD INTO example.employee
  FROM FILE PATHS 'kifs://data/employee.csv'
  WITH OPTIONS
  (
  	COLUMN FORMATS = '
  	{
  		"hire_date": {"date": "YYYY-MM-DD"}
  	}'
  )
  ```

  ```sql Table Options theme={null}
  LOAD DATA INTO example.product_create_options
  FROM FILE PATHS 'kifs://data/products.csv'
  USING TABLE PROPERTIES (CHUNK SIZE = 1000000, TTL = 5)
  ```

  ```sql Load Options theme={null}
  LOAD DATA INTO example.product_load_options
  FROM FILE PATHS 'kifs://data/products.csv'
  WITH OPTIONS (BATCH SIZE = 20000)
  ```
</CodeGroup>

## External Tables

<CodeGroup>
  ```sql Refresh on Start theme={null}
  CREATE EXTERNAL TABLE example.ext_product
  FILE PATHS 'products.csv'
  WITH OPTIONS (DATA SOURCE = 'example.product_ds', REFRESH ON START = TRUE)
  ```

  ```sql File Change Data Capture theme={null}
  CREATE EXTERNAL TABLE example.ext_order
  FILE PATHS 'orders/'
  WITH OPTIONS (DATA SOURCE = 'example.order_ds', SUBSCRIBE = TRUE)
  ```

  ```sql JDBC Data Source Remote Query theme={null}
  CREATE EXTERNAL TABLE example.ext_employee_dept2
  REMOTE QUERY 'SELECT * FROM example.employee WHERE dept_id = 2'
  WITH OPTIONS (DATA SOURCE = 'example.jdbc_ds')
  ```

  ```sql Query Change Data Capture theme={null}
  -- 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'
  )
  ```
</CodeGroup>