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