ADB PG 中可以使用COPY/UNLOAD命令,用于便捷地从外表导入数据到本地表(COPY)和从本地表导出数据到外表(UNLOAD)。COPY和UNLOAD都是基于ADB PG映射OSS外表(请参见使用 OSS Foreign Table 访问 OSS 数据)完成数据导出。

UNLOAD

语法
UNLOAD ('select-statement')
TO destination_url
ACCESS_KEY_ID '<access-key-id>'
SECRET_ACCESS_KEY '<secret-access-key>'
[ [ FORMAT ] [ AS ] data_format ] 
[ [option value]  ...  ]
  • select-statement:一个 select 查询语句,查询的结果数据会被写到OSS。
  • destination_url:表示OSS路径的字符串,如oss://bucket-name/path-prefix
  • <access-key-id>: 表示OSS账号ID。
  • <secret-access-key>: 表示OSS账号KEY。
  • [ FORMAT ] [ AS ] data_format: 表示导出的数据格式。可选,默认格式为CSV。 data_format 可以是CSV, ORC, TEXT。
  • [ option [ value ] [, ... ] ] :选项列表, 以key value的形式输入。见下表:
    选项 类型 单位 是否必选 默认值 备注
    endpoint 字符串 指定oss的endpoint
    fdw 字符串 指定oss fdw插件名字。COPY命令在创建临时Server时需要用到。
    其他所有创建外表时用到的option,如format, filetype, delimiter,escape等 剩下的option是创建临时外表时用到的option,请参见使用 OSS Foreign Table 访问 OSS 数据
示例
  1. 创建本地表并写入测试数据。
    postgres=# create table local_t (a int, b float8, c text);
    postgres=# insert into local_t select r, random() * 1000, md5(random()::text) from generate_series(1,1000)r;
    INSERT 0 1000
    postgres=# select * from local_t limit 5;
     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)
  2. 使用 UNLOAD,导出指定列数据到OSS。
    -- 使用ULOAD将local_t的a,c两列导出到“oss://adbpg-regress/local_t/”路径
    UNLOAD ('select a, c from local_t') TO 'oss://adbpg-regress/local_t/'
    ACCESS_KEY_ID 'id'
    SECRET_ACCESS_KEY 'key'
    FORMAT AS CSV
    ENDPOINT 'oss-endpoint'
    FDW 'oss_fdw';
  3. 可以看到OSS对应路径下已经写入csv文件。
    $ ossutil ls oss://adbpg-regress/local_t/
    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
    查看文件数据,只写出了a,c 两列的数据。
    $ head -n 10 adbpgforeigntabletmp_20200907164801_1354519958_20200907164801_652261618_seg2_0.csv
    7,1225341d0d367a69b1b345536b21ef73
    19,424a7a5c36066842f4de8c8a8341fc89
    27,c214432e9928e4a6f7bef7bd815424c0
    29,ade5d636e2b5d2a606a02e79255da4bd
    37,85660e60ede47b68493f6295620db568
    77,e1be448ba2b08f0a2ca05b7ed812abfd
    80,5e85d597a3b0f2f9736a728724a0f9e0
    92,dc23f76f0b1446504b8f1c2274521d2f
    94,50304822488d55a500e3a71bcf40890f
    97,e970fde8cd0df9c6b610925a488f6042

COPY

语法
COPY table_name [ ( column_name [, ...] ) ]
FROM data_source
ACCESS_KEY_ID '<access-key-id>'
SECRET_ACCESS_KEY '<secret-access-key>'
[ [ FORMAT ] [ AS ] data_format ] 
[ [ option value]  ...  ]
  • table_name:需要导入数据的本地表名,必须是已经存在的本地表。
  • data_source:表示OSS路径的字符串,如oss://bucket_name/path_prefix
  • <access-key-id>:表示OSS账号ID。
  • <secret-access-key>:表示OSS账号KEY。
  • [ FORMAT ] [ AS ] data_format:表示按照data_format格式解析读取OSS文件。可选,默认为CSV格式。data_format 可以是CSV, JSON, JSONLINE, ORC, PARQUET, TEXT。
  • [ option [ value ] [, ... ] ] 选项列表, 以key value的形式输入。见下表:
    选项 类型 单位 是否必选 默认值 备注
    endpoint 字符串 指定oss的endpoint
    fdw 字符串 指定oss fdw插件名字。COPY命令在创建临时Server时需要用到。
    其他所有创建外表时用到的option,如format, filetype, delimiter,escape等 剩下的option是创建临时外表时用到的option,请参见使用 OSS Foreign Table 访问 OSS 数据
使用示例
  1. 创建本地表。
    create table local_t2 (a int, b float8, c text);
  2. 使用COPY命令从oss://adbpg-regress/local_t/导入a,c两列。由于未指定b列,则b列默认为NULL。
    postgres=# COPY local_t2 (a, c)
    FROM 'oss://adbpg-regress/local_t/'
    ACCESS_KEY_ID 'id'
    SECRET_ACCESS_KEY 'key'
    FORMAT AS CSV
    ENDPOINT 'oss_endpoint'
    FDW 'oss_fdw';
    COPY FOREIGN TABLE
    
    postgres=# 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)
  3. 导入其他格式示例。
    -- 导入ORC格式数据
    COPY tt
    FROM 'oss://adbpg-regress/q_oss_orc_list/'
    ACCESS_KEY_ID 'id'
    SECRET_ACCESS_KEY 'key'
    FORMAT AS ORC
    ENDPOINT 'oss-cn-hangzhou-zmf.aliyuncs.com'
    FDW 'oss_fdw';
    
    -- 导入parquet格式数据
    COPY tp
    FROM  'oss://adbpg-regress/test_parquet/'
    ACCESS_KEY_ID 'id'
    SECRET_ACCESS_KEY 'key'
    FORMAT AS PARQUET
    ENDPOINT 'oss-cn-hangzhou-zmf.aliyuncs.com'
    FDW 'oss_fdw';