Loading Data

Copy-paste examples of loading data with SQL

LOAD INTO

Single & Multiple Files

Single File
1
2
LOAD DATA INTO example.product
FROM FILE PATHS 'data/products.csv'
Multiple Files by Wildcard
1
2
LOAD DATA INTO example.product
FROM FILE PATHS 'data/products*csv'
Multiple Files by List
1
2
LOAD DATA INTO example.product
FROM FILE PATHS 'data/products.csv', 'data/products.kh.csv'

Data Sources

Batch Load
1
2
LOAD DATA INTO example.product
FROM FILE PATHS 'data/products.csv'
Streaming Load
1
2
3
LOAD DATA INTO example.orders
FORMAT JSON
WITH OPTIONS (DATA SOURCE = 'kafka_ds', SUBSCRIBE = true)

Text Delimited Files

CSV File
1
2
LOAD DATA INTO example.product
FROM FILE PATHS 'data/products.nh.csv'
No Header
1
2
3
LOAD DATA INTO example.product
FROM FILE PATHS 'data/products.nh.csv'
FORMAT TEXT (INCLUDES HEADER = false)
Columns by Name
1
2
3
LOAD DATA INTO example.product_name_stock
FROM FILE PATHS 'data/products.title-case.csv'
WITH OPTIONS (FIELDS MAPPED BY NAME(ID, Name, Stock))
Delimited Text Option
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
LOAD DATA INTO example.product
FROM FILE PATHS 'data/products.ssv'
FORMAT TEXT
(
  COMMENT = '--',
  DELIMITER = ';',
  ESCAPE = '`',
  INCLUDES HEADER = true,
  NULL = '<null>',
  QUOTE = ''''
)
Forced Format
1
2
3
LOAD DATA INTO example.product
FROM FILE PATHS 'data/products.dat'
FORMAT TEXT

JSON/GeoJSON Files

JSON File
1
2
LOAD DATA INTO example.product
FROM FILE PATHS 'data/products.json'
GeoJSON File
1
2
LOAD DATA INTO example.events
FROM FILE PATHS 'data/events.json'
Properties by Name
1
2
3
LOAD DATA INTO example.product_name_stock
FROM FILE PATHS 'data/products.json'
WITH OPTIONS (FIELDS MAPPED BY NAME(ID, Name, Stock))
Forced Format
1
2
3
LOAD DATA INTO example.product
FROM FILE PATHS 'data/products.dat'
FORMAT JSON

Parquet Files

Parquet File
1
2
LOAD DATA INTO example.ext_employee
FROM FILE PATHS 'data/employee.parquet'
Properties By Name
1
2
3
LOAD DATA INTO example.product_name_stock
FROM FILE PATHS 'data/products.parquet'
WITH OPTIONS (FIELDS MAPPED BY NAME(ID, Name, Stock))
Forced Format
1
2
3
CREATE EXTERNAL TABLE example.ext_employee
FILE PATHS 'data/employee.dat'
FORMAT PARQUET

Shapefile Files

Shapefile File
1
2
LOAD DATA INTO example.events
FROM FILE PATHS 'data/events.shp'
Properties by Name
1
2
3
LOAD DATA INTO example.events
FROM FILE PATHS 'data/events.shp'
WITH OPTIONS (FIELDS MAPPED BY NAME(ID, type))

External Tables

Refresh on Start
1
2
3
4
5
6
7
CREATE EXTERNAL TABLE example.ext_product
FILE PATHS 'products.csv'
WITH OPTIONS
(
    DATA SOURCE = 'product_ds',
    REFRESH ON START = true
)
Change Data Capture Subscription
1
2
3
4
5
6
7
8
CREATE EXTERNAL TABLE example.ext_product
FILE PATHS 'products.csv'
WITH OPTIONS
(
   DATA SOURCE = 'product_ds',
   SUBSCRIBE = true,
   POLL_INTERVAL = '60'
)