导出数据到VOLUME - COPY INTO VOLUME

目标：将一张表或一个查询结果导出为文件到 Volume 的指定路径下。

语法

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 ]

参数说明

formatTypeOptions
- COMPRESSION：可选参数。指定压缩格式，默认为不压缩。支持 GZIP、ZSTD、DEFLATE 压缩。例如：
```
COMPRESSION = 'GZIP'
```
  COMPRESSION = 'GZIP'
copyOptions
- filename_prefix = '<prefex_name>'：可选参数。设置文件前缀。例如：
```
filename_prefix = 'my_prefix_'
```
  filename_prefix = 'my_prefix_'
- filename_suffix = '<suffix>'：可选参数。设置文件后缀。例如：
```
filename_suffix = '.data'
```
  filename_suffix = '.data'
- include_job_id = 'TRUE' | 'FALSE'：可选参数。设置文件名是否包含作业 ID，不设置时默认为不包含。例如：
```
include_job_id = 'TRUE'
```
  include_job_id = 'TRUE'

使用示例

导出表数据到 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

* 导出查询结果到 Volume ```SQL -- copy from query COPY INTO VOLUME my_external_vol SUBDIRECTORY 'dau_unload/' FROM (SELECT * FROM DAU limit 5) FILE_FORMAT = (type = CSV);

导出时设置文件格式

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

* 导出时设置任务参数 ```SQL -- 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';

约束与限制

要求 JDBC 驱动版本为 1.3.5 及以上。

联系我们