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

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

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