Unloading TSV File
Unloading TSV File
Syntax:
COPY INTO { internalStage | externalStage | externalLocation }
FROM { [<database_name>.]<table_name> | ( <query> ) }
FILE_FORMAT = (
TYPE = TSV,
RECORD_DELIMITER = '<character>',
FIELD_DELIMITER = '<character>',
COMPRESSION = gzip,
OUTPUT_HEADER = true -- Unload with header
)
[MAX_FILE_SIZE = <num>]
[DETAILED_OUTPUT = true | false]
- More TSV options refer to TSV File Format Options
- Unloading into multiple files use the MAX_FILE_SIZE Copy Option
- More details about the syntax can be found in COPY INTO location
Tutorial
Step 1. Create an External Stage
CREATE STAGE tsv_unload_stage
URL = 's3://unload/tsv/'
CONNECTION = (
ACCESS_KEY_ID = '<your-access-key-id>'
SECRET_ACCESS_KEY = '<your-secret-access-key>'
);
Step 2. Create Custom TSV File Format
CREATE FILE FORMAT tsv_unload_format
TYPE = TSV,
COMPRESSION = gzip; -- Unload with gzip compression
Step 3. Unload into TSV File
COPY INTO @tsv_unload_stage
FROM (
SELECT *
FROM generate_series(1, 100)
)
FILE_FORMAT = (FORMAT_NAME = 'tsv_unload_format')
DETAILED_OUTPUT = true;
Result:
┌──────────────────────────────────────────────────────────────────────────────────────────┐