Export Data to VOLUME - COPY INTO VOLUME

Objective: Export a table or a query result to a specified path in the Volume

Syntax

COPY INTO { VOLUME external_volume_name | TABLE VOLUME table_name  | USER VOLUME }  
SUBDIRECTORY '<path>'
FROM { [<namespace>.]<table_name> |(<query>)}
FILE_FORMAT = ( TYPE = { CSV|TEXT|PARQUET }  [ formatTypeOptions ]  ) 
[ copyOptions ]

Parameter Description

• formatTypeOptions

  • COMPRESSION: Optional parameter. Specifies the compression format, default is no compression. Supports GZIP, ZSTD, DEFLATE compression, for example: COMPRESSION = 'GZIP'

• copyOptions

  • filename_prefix = '<prefix_name>'. Optional parameter. Sets the file prefix, for example: filename_prefix = 'my_prefix_'
  • filename_suffix = '<suffix>'. Optional parameter. Sets the file suffix, for example: filename_suffix = '.data'
  • include_job_id = 'TRUE' | 'FALSE'. Optional parameter. Sets whether the job ID is included in the file name, default is not to include the job ID if not set. For example: include_job_id = 'TRUE'

Usage Example

• Export table data to Volume

-- Unload to external volume
COPY INTO VOLUME my_external_vol SUBDIRECTORY 'dau_unload/' 
FROM TABLE dau
FILE_FORMAT = (type = CSV);

-- Unload to table volume
COPY INTO TABLE VOLUME dau SUBDIRECTORY 'dau_unload/' 
FROM TABLE dau
FILE_FORMAT = (type = CSV);

-- Unload to user volume
COPY INTO USER VOLUME SUBDIRECTORY 'dau_unload/' 
FROM TABLE dau
FILE_FORMAT = (TYPE = CSV )


SHOW VOLUME DIRECTORY my_external_vol;

relative_path                                   url                                                                size last_modified_time  
----------------------------------------------- ------------------------------------------------------------------ ---- ------------------- 
dau_unload/part00001.csv                           oss://your-bucket/dau_unload/part00001.csv                           75   2024-05-29 17:03:25

• Export Query Results to Volume

-- copy from query
COPY INTO VOLUME my_external_vol SUBDIRECTORY 'dau_unload/' 
FROM (SELECT * FROM DAU limit 5)
FILE_FORMAT = (type = CSV);

• Set the export file format during export

-- copy from table to external volume
COPY INTO VOLUME my_external_vol SUBDIRECTORY 'dau_unload/' 
FROM TABLE dau
FILE_FORMAT = (type = CSV);

-- COPY_OPTION: Unload and compress with gzip
COPY INTO VOLUME my_external_vol SUBDIRECTORY 'dau_unload/' 
FROM (SELECT * FROM DAU limit 5)
FILE_FORMAT = (TYPE = CSV COMPRESSION = 'GZIP') ;

COPY INTO VOLUME my_external_vol SUBDIRECTORY 'dau/' 
FROM TABLE dau
FILE_FORMAT = (type = PARQUET COMPRESSION = 'GZIP');

• Set export task parameters during export

-- COPY_OPTION: Unload and add prefix to file names
COPY INTO VOLUME my_external_vol SUBDIRECTORY 'dau_unload/' 
FROM TABLE dau
FILE_FORMAT = (TYPE = CSV) 
FILENAME_PREFIX = 'my_prefix_';

-- COPY_OPTION: Unload and add suffix to file names
COPY INTO VOLUME my_external_vol SUBDIRECTORY 'dau_unload/' 
FROM TABLE dau
FILE_FORMAT = (TYPE = CSV) 
FILENAME_PREFIX = '.data';

-- COPY_OPTION: Unload and add job id to file names
COPY INTO VOLUME my_external_vol SUBDIRECTORY 'dau_unload/' 
FROM TABLE dau
FILE_FORMAT =  (TYPE = CSV )
INCLUDE_JOB_ID = 'TRUE';

Constraints and Limitations

• Requires JDBC driver version 1.3.5 or above.
• Direct export to object storage location is not currently supported (can be exported to the corresponding object storage location with the help of Volume object).