Exporting Data

Copy-paste examples of exporting data with SQL

EXPORT ... INTO

Sources (Table/Query)

Table
1
2
EXPORT TABLE employee
INTO FILE PATH '/export/employee.csv'
Query
1
2
3
4
5
6
7
EXPORT QUERY
(
    SELECT id, manager_id, first_name, last_name, salary, hire_date
    FROM employee
    WHERE dept_id = 2
)
INTO FILE PATH '/export/employee_dept2.csv'

Data Sinks (File/Table/DML)

File
1
2
3
EXPORT TABLE employee
INTO FILE PATH '/data/employee.csv'
WITH OPTIONS (DATASINK_NAME = 's3_dsink')
Table (via JDBC)
1
2
3
EXPORT TABLE employee
INTO REMOTE TABLE 'example.remote_employee'
WITH OPTIONS (DATASINK_NAME = 'jdbc_dsink')
DML (via JDBC)
1
2
3
EXPORT TABLE employee
INTO REMOTE QUERY 'INSERT INTO example.remote_employee VALUES (?, ?, ?, ?, ?, ?, ?)'
WITH OPTIONS (DATASINK_NAME = 'jdbc_dsink')
Init Options (via JDBC)
1
2
3
4
5
6
7
8
EXPORT TABLE ts_source
INTO REMOTE TABLE 'ts'
WITH OPTIONS
(
    DATASINK_NAME = 'jdbc_dsink',
    JDBC_SESSION_INIT_STATEMENT = 'SET TIMEZONE=''EST''',
    JDBC_CONNECTION_INIT_STATEMENT = 'CREATE TABLE ts (ts DATETIME)'
)

Delimited Text Options

CSV
1
2
EXPORT TABLE product
INTO FILE PATH '/export/products.csv'
Forced Format
1
2
3
EXPORT TABLE product
INTO FILE PATH '/export/products_text.dat'
FORMAT TEXT
No Header
1
2
3
EXPORT TABLE product
INTO FILE PATH '/export/products.csv'
FORMAT TEXT (INCLUDES HEADER = FALSE)
Columns Mapped by Name
1
2
3
EXPORT TABLE product
INTO FILE PATH '/export/products_name.csv'
WITH OPTIONS (FIELDS MAPPED BY NAME(id, name, stock))
Columns Ignored by Position
1
2
3
EXPORT TABLE product
INTO FILE PATH '/export/products_pos.csv'
WITH OPTIONS (FIELDS IGNORED BY POSITION(2, 4))
Parsing Options
1
2
3
4
5
6
7
8
EXPORT TABLE product
INTO FILE PATH '/export/products.ssv'
FORMAT TEXT
(
    DELIMITER = ';',
    INCLUDES HEADER = TRUE,
    NULL = '<null>'
)
GZip Compression
1
2
3
EXPORT TABLE product
INTO FILE PATH '/export/products.csv.gz'
WITH OPTIONS (COMPRESSION_TYPE = 'gzip')

Parquet Files

Parquet Format
1
2
3
EXPORT TABLE product
INTO FILE PATH '/export/products_par.dat'
FORMAT PARQUET
Properties Mapped By Name
1
2
3
4
EXPORT TABLE product
INTO FILE PATH '/export/products_name.parquet'
FORMAT PARQUET
WITH OPTIONS (FIELDS MAPPED BY NAME(id, name, stock))
Properties Ignored By Name
1
2
3
4
EXPORT TABLE product
INTO FILE PATH '/export/products_pos.parquet'
FORMAT PARQUET
WITH OPTIONS (FIELDS IGNORED BY POSITION(category, description))
Snappy Compression (default)
1
2
EXPORT TABLE product
INTO FILE PATH '/export/products.parquet'
GZip Compression
1
2
3
EXPORT TABLE product
INTO FILE PATH '/export/products.gz.parquet'
WITH OPTIONS (COMPRESSION_TYPE = 'gzip')

Naming Options

Single File w/o Code
1
2
3
4
5
-- Creates a file with a name like:
--   export/product.csv
EXPORT TABLE product
INTO FILE PATH '/export/product.csv'
WITH OPTIONS (SINGLE_FILE = 'overwrite')
Single File w/ Code
1
2
3
4
-- Creates a file with a name like:
--   export/product_611460000.csv
EXPORT TABLE product
INTO FILE PATH '/export/product.csv'
Multiple Files w/ Code
1
2
3
4
5
6
-- Creates a set of files with names like:
-- * export/product_611641000.csv
-- * export/product_611642000.csv
EXPORT TABLE product
INTO FILE PATH '/export/product.csv'
WITH OPTIONS (SINGLE_FILE = false)

Sizing Options

Batch (Local File)
1
2
3
EXPORT TABLE product
INTO FILE PATH '/export/products_exp.csv'
WITH OPTIONS (BATCH SIZE = 20000)
Batch (Remote Table)
1
2
3
4
5
6
7
EXPORT TABLE product
INTO REMOTE TABLE 'product_exp'
WITH OPTIONS
(
    DATASINK_NAME = 'jdbc_dsink',
    BATCH SIZE = 20000
)