全部产品
Search

搜索本产品

    云原生数据仓库AnalyticDB：使用COPY或UNLOAD命令导入或导出数据到OSS

    文档中心

    云原生数据仓库AnalyticDB：使用COPY或UNLOAD命令导入或导出数据到OSS

    更新时间：Jul 05, 2023

    AnalyticDB PostgreSQL版支持COPY和UNLOAD命令，COPY表示从外表导入数据到本地表，UNLOAD表示从本地表导出数据到外表。

    COPY和UNLOAD都是基于OSS Foreign Table来完成数据导入导出的，OSS Foreign Table的详细内容请参见使用 OSS Foreign Table 访问 OSS 数据。

    COPY

    语法

    COPY <table_name> 
[ <column_list> ]
FROM <data_source>
ACCESS_KEY_ID '<access_key_id>'
SECRET_ACCESS_KEY '<secret_access_key>'
[ [ FORMAT ] [ AS ] <data_format> ]
[ MANIFEST ]
[ option '<value>' [ ... ] ]

    参数

    参数

    是否必填

    说明

    table_name

    是

    需要导入数据的本地表名，必须是已经存在的本地表。

    column_list

    否

    需要写入的目标列列表。若不使用，则默认写入所有列。

    data_source

    是

    OSS路径，如oss://<bucket_name>/path_prefix。

    access_key_id

    是

    阿里云账号或者具备OSS访问权限的RAM用户的AccessKey ID。

    如何获取AccessKey ID，请参见创建AccessKey。

    secret_access_key

    是

    阿里云账号或者具备OSS访问权限的RAM用户的AccessKey Secret。

    如何获取AccessKey Secret，请参见创建AccessKey。

    [ FORMAT ] [ AS ] <data_format>

    否

    若不使用，则默认FORMAT AS CSV。

    data_format可以是BINARY，CSV，JSON，JSONLINE，ORC，PARQUET，TEXT（FORMAT与AS可缺省，即FORMAT AS CSV与FORMAT CSV、CSV等价）。

    MANIFEST

    否

    表示data_source是Manifest清单文件。Manifest清单文件必须是JSON格式，由以下元素构成：

    • entries：数组，表示清单内具体的OSS文件列表，可以位于不同的Bucket或拥有不同的路径前缀，但需要能够使用相同的AccessKey ID或AccessKey Secret访问。示例如下：

      {  "entries": [  
    {"url": "oss://adbpg-regress/local_t/_seg2_0.csv", "mandatory":true},
    {"url": "oss://adbpg-regress/local_t/_seg1_0.csv", "mandatory":true},
    {"url": "oss://adbpg-regress/local_t/_seg0_0.csv", "mandatory":true}, 
    {"url": "oss://adbpg-regress-2/local_t/_seg1_0.csv", "mandatory":true}, 
    {"url": "oss://adbpg-regress-2/local_t/_seg2_0.csv", "mandatory":true}, 
    {"url": "oss://adbpg-regress-2/local_t/_seg0_0.csv", "mandatory":true} 
   ]
}

    • url：一个OSS文件的完整路径。

    • mandatory：OSS文件不存在时是否报错。

    [ option '<value>' [ ... ] ]

    否

    选项列表，以key value的形式输入。选项说明见下表。

    选项列表。

    选项

    类型

    是否必选

    备注

    ENDPOINT

    字符串

    是

    指定OSS的Endpoint。如何获取OSS的Endpoint，请参见访问域名和数据中心。

    FDW

    字符串

    是

    指定OSS FDW插件名字。COPY命令在创建临时Server时需要用到。

    其他所有创建外表时用到的option，如FORMAT，FILETYPE，DELIMITER，ESCAPE等。

    不涉及

    不涉及

    创建临时外表时用到的option，详细内容请参见使用 OSS Foreign Table 访问 OSS 数据。

    示例

    示例1

    1. 创建本地表。

       CREATE TABLE local_t2 (a int, b float8, c text);

    2. 使用COPY命令导入数据，只写入a和c两列，b列全部写入NULL。

      COPY local_t2 (a, c)
FROM 'oss://adbpg-regress/local_t/'
ACCESS_KEY_ID 'LTAI5tDfyHVmxCf66Un****'
SECRET_ACCESS_KEY 'TNPPxOFY4xx7CZGjVgSsVBtIs****'
FORMAT AS CSV
ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com'
FDW 'oss_fdw';

    3. 查询表中的数据。

      SELECT * FROM local_t2 LIMIT 10;

      返回信息如下。

       a  | b |                c
----+---+----------------------------------
 12 |   | a24cba6ebdc5e0c485cd88ef60b72fea
 15 |   | c4d3028f5205fab98e5f43c7945db4ba
 20 |   | 769884311db01f400e21a903a3f1cb50
 26 |   | 7d12c981d262e0067ea1a04368f32f2a
 30 |   | 4e64bda52d54d263d16f42771b1d0225
 35 |   | b70c976d4c04568bd497b42a7d2e451d
 40 |   | d07ce2948b8618b47c351b6e222182f6
 46 |   | c2234393f878f5557776b7e778299564
 47 |   | cde904b2331fa274cd8d9266aa858342
 50 |   | 1235b900fb644bb36440a274314e4b6b
(10 rows)

    4. 可以看到从外表导入的a和c列，与源数据表local_t的a和c列数据相同。

      • SELECT sum(hashtext(t.a::text)) AS col_a_hash, sum(hashtext(t.c::text)) AS col_c_hash FROM local_t2 t;

        返回信息如下。

         col_a_hash  | col_c_hash
-------------+-------------
 23725368368 | 13447976580
(1 row)
      • SELECT sum(hashtext(t.a::text)) AS col_a_hash, sum(hashtext(t.c::text)) AS col_c_hash FROM local_t t;

        返回信息如下。

         col_a_hash  | col_c_hash
-------------+-------------
 23725368368 | 13447976580
(1 row)

    5. 导入其他格式示例。

      • 导入ORC格式数据：

        COPY tt
FROM 'oss://adbpg-regress/q_oss_orc_list/'
ACCESS_KEY_ID 'LTAI5tDfyHVmxCf66Un****'
SECRET_ACCESS_KEY 'TNPPxOFY4xx7CZGjVgSsVBtIs****'
FORMAT AS ORC
ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com'
FDW 'oss_fdw';

      • 导入PARQUET格式数据：

        COPY tp
FROM  'oss://adbpg-regress/test_parquet/'
ACCESS_KEY_ID 'LTAI5tDfyHVmxCf66Un****'
SECRET_ACCESS_KEY 'TNPPxOFY4xx7CZGjVgSsVBtIs****'
FORMAT AS PARQUET
ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com'
FDW 'oss_fdw';

    示例2

    1. 创建本地表。

       CREATE TABLE local_manifest (a int, c text);

    2. 创建Manifest文件，其中OSS文件列表可位于不同的Bucket。

      {
   "entries": [
      {"url": "oss://adbpg-regress/local_t/_20210114103840_83f407434beccbd4eb2a0ce45ef39568_1450404435_seg2_0.csv", "mandatory":true},
      {"url": "oss://adbpg-regress/local_t/_20210114103840_83f407434beccbd4eb2a0ce45ef39568_1856683967_seg1_0.csv", "mandatory":true},
      {"url": "oss://adbpg-regress/local_t/_20210114103840_83f407434beccbd4eb2a0ce45ef39568_1880804901_seg0_0.csv", "mandatory":true},
      {"url": "oss://adbpg-regress-2/local_t/_20210114103849_67100080728ef95228e662bc02cb99d1_1008521914_seg1_0.csv", "mandatory":true},
      {"url": "oss://adbpg-regress-2/local_t/_20210114103849_67100080728ef95228e662bc02cb99d1_1234881553_seg2_0.csv", "mandatory":true},
      {"url": "oss://adbpg-regress-2/local_t/_20210114103849_67100080728ef95228e662bc02cb99d1_1711667760_seg0_0.csv", "mandatory":true}
   ]
}

    3. 使用COPY命令从Manifest清单中导入本地表。

      COPY local_manifest
FROM 'oss://adbpg-regress-2/unload_manifest/t_manifest'
ACCESS_KEY_ID 'LTAI5tDfyHVmxCf66Un****'
SECRET_ACCESS_KEY 'TNPPxOFY4xx7CZGjVgSsVBtIs****'
FORMAT AS CSV
MANIFEST          -- 表示从Manifest文件中导入。
ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com'
FDW 'oss_fdw';

    示例3

    COPY导入OSS数据时，可能会存在异常的数据行（无法正常COPY导入）。当遇到这种情况时，可以通过额外的option选项设置实现容错。

    • log_errors：表示是否记录错误行信息。

    • segment_reject_limit：segment_reject_limit '10' 表示最多容忍10行，大于等于10行时报错退出；segment_reject_limit '10%' 表示当前的错误总行数/当前总共已处理的行 >= 10% 时，报错退出。

    1. 创建本地表。

      CREATE TABLE sales(id integer, value float8, x text) DISTRIBUTED BY (id);

    2. 使用COPY导入OSS数据文件，文件中有3行数据存在编码问题。

      COPY sales
FROM 'oss://adbpg-const/error_sales/'
ACCESS_KEY_ID 'LTAI5tDfyHVmxCf66Un****'
SECRET_ACCESS_KEY 'TNPPxOFY4xx7CZGjVgSsVBtIs****'
FORMAT AS csv
log_errors 'true'
segment_reject_limit '10'
endpoint 'oss-cn-hangzhou-internal.aliyuncs.com'
FDW 'oss_fdw';
NOTICE:  found 3 data formatting errors (3 or more input rows), rejected related input data
COPY FOREIGN TABLE

    3. 查看具体的错误行信息。

      SELECT * FROM gp_read_error_log('<COPY的目标表名>');

      例如，查看sales表的错误行信息。

      SELECT * FROM gp_read_error_log('sales');

      返回信息如下。

                  cmdtime            |                    relname                     |        filename         | linenum | bytenum |                          errmsg                           | rawdata | rawbytes
-------------------------------+------------------------------------------------+-------------------------+---------+---------+-----------------------------------------------------------+---------+----------
 2021-02-08 14:24:04.225238+08 | adbpgforeigntabletmp_20210208142403_1936866966 | error_sales/sales.2.csv |       2 |         | invalid byte sequence for encoding "UTF8": 0xed 0xab 0xad |         | \x
 2021-02-08 14:24:04.225238+08 | adbpgforeigntabletmp_20210208142403_1936866966 | error_sales/sales.2.csv |       3 |         | invalid byte sequence for encoding "UTF8": 0xed 0xab 0xad |         | \x
 2021-02-08 14:24:04.225269+08 | adbpgforeigntabletmp_20210208142403_1936866966 | error_sales/sales.3.csv |       2 |         | invalid byte sequence for encoding "UTF8": 0xed 0xab 0xad |         | \x
(3 rows)
      说明

      追加保存的错误行日志，需要占用一定的存储空间。删除错误行日志语法为：SELECT gp_truncate_error_log('<table_name>') 。

    UNLOAD

    注意事项

    当您导出CSV文件时，可能出现option因关键字冲突而发生语法错误，此时需要将option选项用双引号引用，并写成小写字母形式。需要进行特殊处理的options如下：delimiter、quote、null、header 、escape、encoding。示例如下：

    UNLOAD ('SELECT * FROM test') 
TO 'oss://adbpg-regress/local_t/'
ACCESS_KEY_ID 'LTAI5tDfyHVmxCf66Un****'
SECRET_ACCESS_KEY 'TNPPxOFY4xx7CZGjVgSsVBtIs****'
FORMAT csv
"delimiter" '|'
"quote" '"'
"null" ''
"header" 'true'
"escape" 'E'
"encoding" 'utf-8'
FDW 'oss_fdw'
ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com';

    语法

    UNLOAD ('<select_statement>')
TO <destination_url>
ACCESS_KEY_ID '<access_key_id>'
SECRET_ACCESS_KEY '<secret_access_key>'
[ [ FORMAT ] [ AS ] <data_format> ]
[ MANIFEST [ '<manifest_url>' ] ]
[ PARALLEL [ { ON | TRUE } | { OFF | FALSE } ] ]
[ option '<value>' [ ... ] ]

    参数

    参数

    是否必填

    说明

    select_statement

    是

    一个SELECT查询语句，查询的结果数据会被写到OSS。

    destination_url

    是

    OSS路径，如oss://<bucket_name>/path_prefix。

    access_key_id

    是

    阿里云账号或者具备OSS访问权限的RAM用户的AccessKey ID。

    如何获取AccessKey ID，请参见创建AccessKey。

    secret_access_key

    是

    阿里云账号或者具备OSS访问权限的RAM用户的AccessKey Secret。

    如何获取AccessKey ID，请参见创建AccessKey。

    [ FORMAT ] [ AS ] <data_format>

    否

    若不使用，则默认FORMAT AS CSV。

    data_format可以是CSV，ORC，TEXT（FORMAT与AS可缺省，即FORMAT AS CSV与FORMAT CSV、CSV等价）。

    MANIFEST

    否

    表示导出时需要生成导出清单文件。manifest_url可以指定与数据文件不同Bucket的OSS完整路径，且路径需要以manifest为后缀。不指定manifest_url时，表示清单文件与导出的数据文件有相同的路径前缀。

    说明

    如果manifest_url指定的文件已经存在，则需要在[ option [ value ] [ ... ] ]选项列表中指定allowoverwrite选项为true， 表示覆盖原Manifest文件。

    PARALLEL

    否

    是否多Segment并行导出。默认多节点并行导出，每个节点生成独立的导出文件。值设置为OFF或FALSE时，表示关闭并行，导出数据大小不超过8 GB时，仅导出一个文件。

    [ option '<value>' [ ... ] ]

    否

    选项列表，以key value的形式输入。选项说明见下表。

    选项列表。

    选项

    类型

    是否必选

    备注

    ENDPOINT

    字符串

    是

    指定OSS的Endpoint。如何获取OSS的Endpoint，请参见访问域名和数据中心。

    FDW

    字符串

    是

    指定OSS FDW插件名字。COPY命令在创建临时Server时需要用到。

    其他所有创建外表时用到的option，如FORMAT，FILETYPE，DELIMITER，ESCAPE等。

    不涉及

    不涉及

    创建临时外表时用到的option，详细内容请参见使用 OSS Foreign Table 访问 OSS 数据。

    示例

    示例1

    1. 创建本地表并写入测试数据。

      2. CREATE TABLE local_t (a int, b float8, c text);
INSERT INTO local_t SELECT r, random() * 1000, md5(random()::text) FROM generate_series(1,1000)r;

    2. 查询本地表。

      SELECT * FROM local_t LIMIT 5;

      返回信息如下。

      3.  a  |        b         |                c
----+------------------+----------------------------------
  5 |  550.81393988803 | 8009fa725372e996786849213a695ce0
  6 | 95.8335199393332 | ce7952c6728cdffdee06cc5b502d6457
  9 | 421.379795763642 | d3260ccbf6b9c03f3658d96bb7678b4d
 10 | 362.347379792482 | 2bbbf89d23a2f83b089b589f55b5c4fc
 11 | 800.203878898174 | a52994c5573e6b36d8a1c357bf800ce5
(5 rows)

    3. 使用UNLOAD，导出指定列数据到OSS。

      4. UNLOAD ('select a, c from local_t') TO 'oss://adbpg-regress/local_t/'
ACCESS_KEY_ID 'LTAI5tDfyHVmxCf66Un****'
SECRET_ACCESS_KEY 'TNPPxOFY4xx7CZGjVgSsVBtIs****'
FORMAT AS CSV
ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com'
FDW 'oss_fdw';
NOTICE:  OSS output prefix: "local_t/adbpgforeigntabletmp_20200907164801_1354519958_20200907164801_652261618".
UNLOAD

    4. 查看OSS对应路径下已经写入的CSV文件。

      $ ossutil --config hangzhou-zmf.config ls oss://adbpg-regress/local_t/

      返回信息如下。

      5. LastModifiedTime                   Size(B)  StorageClass   ETAG                                  ObjectName
2020-09-07 16:48:01 +0800 CST        12023      Standard   9F38B5407142C044C1F3555F00000000      oss://adbpg-regress/local_t/adbpgforeigntabletmp_20200907164801_1354519958_20200907164801_652261618_seg0_0.csv
2020-09-07 16:48:01 +0800 CST        12469      Standard   807BA680A0DED49BC1F3555F00000000      oss://adbpg-regress/local_t/adbpgforeigntabletmp_20200907164801_1354519958_20200907164801_652261618_seg1_0.csv
2020-09-07 16:48:01 +0800 CST        12401      Standard   3524F68F628CEB64C1F3555F00000000      oss://adbpg-regress/local_t/adbpgforeigntabletmp_20200907164801_1354519958_20200907164801_652261618_seg2_0.csv
Object Number is: 3

0.153414(s) elapsed

    5. 查看文件数据，只写出了a和c两列的数据。

      $ head -n 10 adbpgforeigntabletmp_20200907164801_1354519958_20200907164801_652261618_seg2_0.csv

      返回信息如下。

      6. 7,1225341d0d367a69b1b345536b21ef73
19,424a7a5c36066842f4de8c8a8341fc89
27,c214432e9928e4a6f7bef7bd815424c0
29,ade5d636e2b5d2a606a02e79255da4bd
37,85660e60ede47b68493f6295620db568
77,e1be448ba2b08f0a2ca05b7ed812abfd
80,5e85d597a3b0f2f9736a728724a0f9e0
92,dc23f76f0b1446504b8f1c2274521d2f
94,50304822488d55a500e3a71bcf40890f
97,e970fde8cd0df9c6b610925a488f6042

    示例2

    1. 使用UNLOAD导出时自动生成Manifest文件（Manifest文件与数据文件有相同的路径前缀）。

      2. UNLOAD ('select * from local_t') TO 'oss://adbpg-regress/local_t/'
ACCESS_KEY_ID 'LTAI5tDfyHVmxCf66Un****'
SECRET_ACCESS_KEY 'TNPPxOFY4xx7CZGjVgSsVBtIs****'
FORMAT AS CSV
MANIFEST    -- 表示UNLOAD导出时生成导出清单，清单文件与数据文件有相同的路径前缀。
ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com'
FDW 'oss_fdw';
NOTICE:  OSS output prefix: "local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c".
UNLOAD

    2. 查看导出的文件列表。

      ossutil ls -s oss://adbpg-regress/local_t/

      除了数据文件外，还有一个清单文件，返回信息如下。

      3. oss://adbpg-regress/local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c_162488956_seg1_0.csv
oss://adbpg-regress/local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c_163756258_seg0_0.csv
oss://adbpg-regress/local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c_1741120517_seg2_0.csv
oss://adbpg-regress/local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c_manifest
Object Number is: 4

0.136180(s) elapsed

    3. 查看清单文件内容。

      ossutil cat oss://adbpg-regress/local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c_manifest

      返回信息如下。

      4. {
   "entries": [
      {"url": "oss://adbpg-regress/local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c_162488956_seg1_0.csv"},
      {"url": "oss://adbpg-regress/local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c_163756258_seg0_0.csv"},
      {"url": "oss://adbpg-regress/local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c_1741120517_seg2_0.csv"}
   ]
}

    4. 使用UNLOAD导出时，生成指定Manifest文件（Manifest路径可与数据文件路径不同）。

      5. 说明

      ALLOWOVERWRITE为TRUE时，会覆写已存在的Manifest文件，但不会覆写数据文件，数据文件由客户按需自行删除。

      UNLOAD ('select * from local_t') TO 'oss://adbpg-regress/local_t/'
ACCESS_KEY_ID 'LTAI5tDfyHVmxCf66Un****'
SECRET_ACCESS_KEY 'TNPPxOFY4xx7CZGjVgSsVBtIs****'
FORMAT AS CSV
MANIFEST 'oss://adbpg-regress-2/unload_manifest/t_manifest' -- 表示UNLOAD导出时生成指定路径的导出清单。
ALLOWOVERWRITE 'true' -- 覆写已存在的Manifest文件。
ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com'
FDW 'oss_fdw';
NOTICE:  OSS output prefix: "local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c".
UNLOAD

    5. 查看导出的文件列表。

      ossutil ls -s oss://adbpg-regress/local_t/

      导出路径下只有数据文件，返回信息如下。

      6. oss://adbpg-regress/local_t/_20210114100956_4d3395a9501f6e22da724a2b6df1b6d3_1736161168_seg0_0.csv
oss://adbpg-regress/local_t/_20210114100956_4d3395a9501f6e22da724a2b6df1b6d3_1925769064_seg2_0.csv
oss://adbpg-regress/local_t/_20210114100956_4d3395a9501f6e22da724a2b6df1b6d3_644328153_seg1_0.csv
Object Number is: 3

0.118540(s) elapsed

    6. 查看清单文件内容，清单文件位于另一个Bucket下。

      ossutil cat oss://adbpg-regress-2/unload_manifest/t_manifest

      返回信息如下。

      7. {
   "entries": [
      {"url": "oss://adbpg-regress/local_t/_20210114100956_4d3395a9501f6e22da724a2b6df1b6d3_1736161168_seg0_0.csv"},
      {"url": "oss://adbpg-regress/local_t/_20210114100956_4d3395a9501f6e22da724a2b6df1b6d3_1925769064_seg2_0.csv"},
      {"url": "oss://adbpg-regress/local_t/_20210114100956_4d3395a9501f6e22da724a2b6df1b6d3_644328153_seg1_0.csv"}
   ]
}

    常见问题

    Q：导出CSV文件时，为什么生成了多个CSV文件？

    A：AnalyticDB PostgreSQL版实例使用UNLOAD命令导出数据到OSS时，每个计算节点都会生成一份CSV文件，文件数量与计算节点数量有关。例如您的实例有4个计算节点，那么导出CSV文件时，也会生成4个CSV文件。