如何使用OSSForeignTable导入和导出数据_云原生数据仓库AnalyticDB(AnalyticDB)-阿里云帮助中心

AnalyticDB PostgreSQL版支持通过OSS Foreign Table（简称OSS FDW）导入OSS数据到本地表和导出本地数据到OSS。

支持的文件格式

OSS FDW目前仅支持导入和导出如下格式的数据文件：

支持导入和导出CSV、TEXT格式的非压缩文本文件。
支持导入和导出CSV、TEXT格式的GZIP压缩文件。
支持导入和导出ORC格式的二进制文件。
关于ORC与AnalyticDB PostgreSQL版的数据类型的区别，请参见ORC文件数据类型对照表。

创建OSS Server、OSS User Mapping和OSS FDW

使用OSS FDW前您需要提前创建OSS Server、OSS User Mapping和OSS FDW。

创建OSS Server的具体方法，请参见创建OSS Server。
创建OSS User Mappin的具体方法，请参见创建OSS User Mapping。
创建OSS FDW的具体方法，请参见创建OSS FDW。

导入OSS数据到本地表

上传准备工作中的示例文件example.csv到OSS，具体操作，请参见控制台上传文件。
说明
- 建议数据文件的编码和数据库编码保持一致，减少编码转换，提高效率。数据库编码默认UTF-8。
- AnalyticDB PostgreSQL版的每个数据分区（Segment）将按轮询方式并行对OSS上的数据文件进行读取。
- 对于CSV和TEXT文件，AnalyticDB PostgreSQL版支持多文件并行读取，默认并行数为4。文件数量建议为数据节点数（Segment节点数量 X 单个Segment核数）的整倍数，从而提升读取效率。如何拆分源文件，请参见切分大文件。
连接AnalyticDB PostgreSQL版数据库，连接方式，请参见客户端连接。

创建OSS Server，示例如下：

CREATE SERVER oss_serv
    FOREIGN DATA WRAPPER oss_fdw
    OPTIONS (
        endpoint 'oss-cn-********.aliyuncs.com',
        bucket 'adb-pg'
  );

创建访问OSS Server的用户，示例如下：

CREATE USER MAPPING FOR PUBLIC
    SERVER oss_serv
    OPTIONS (
        id 'LTAI5t7Ge***************',
        key 'FikziJd2La*******************'
    );

创建OSS FDW，示例如下：

CREATE FOREIGN TABLE ossexample (
    date text,
    time text,
    open float,
    high float,
    low float,
    volume int
) SERVER oss_serv OPTIONS (dir 'oss_adb/', format 'csv');

导入OSS数据，您可以通过INSERT语句或CREATE TABLE AS语句导入数据，具体方法如下：
- INSERT
  1. 使用INSERT语句导入数据需要在本地创建一张结构一致的表，建表示例如下：
```
CREATE TABLE adbexample (
    date text,
    time text,
    open float,
    high float,
    low float,
    volume int
) WITH (APPENDONLY=TRUE, ORIENTATION=COLUMN, COMPRESSTYPE=ZSTD, COMPRESSLEVEL=5);
```
  2. 执行INSERT语句将外表ossexample的数据导入到本地表adbexample，示例如下：
```
INSERT INTO adbexample SELECT * FROM ossexample;
```
- CREATE TABLE AS
  执行CREATE TABLE AS语句新建一张本地表adbexample并导入外表ossexample的数据，示例如下：
```
CREATE TABLE adbexample AS SELECT * FROM ossexample DISTRIBUTED BY (volume);
```

导出本地数据到OSS

创建一个OSS FDW，文件格式为CSV，导出目录为tt_csv。

CREATE FOREIGN TABLE foreign_x (i int, j int)
SERVER oss_serv
OPTIONS (format 'csv', dir 'tt_csv/');

使用INSERT INTO语句将本地数据导出到OSS。
```
INSERT INTO foreign_x SELECT * FROM local_x;
```

导出文件命名规则

导出时，多个计算节点会并行将数据写出到相同的目录下，OSS外表导出文件名格式如下：

{tablename | prefix } _{timestamp}_{random_key}_{seg}{segment_id}_{fileno}.{ext}[.gz]

参数说明如下：

参数	说明
`{tablename \| prefix }`	导出前缀。指定路径的方式不同，前缀信息不同： prefix方式：以`prefix`指定的信息为前缀。 dir方式：以OSS外表名称为默认前缀。
`{timestamp}`	导出时的时间戳，格式如`YYYYMMDDHH24MISS`。
`{random_key}`	随机键值。
`{seg}{segment_id}`	由“seg”和“Segment节点编号”组成。例如“seg1”表明该文件由Segment 1导出。
`{fileno}`	文件段序号，从0开始。
`{ext}`	导出的文件格式。 CSV文件格式：显示为`.csv`。 TEXT文件格式：显示为`.txt`。 ORC文件格式：显示为`.orc`。
`[.gz]`	表示导出文件为GZIP压缩文件。

示例如下：

导出文件格式为CSV，并进行GZIP压缩，使用dir方式指定路径：

CREATE FOREIGN TABLE fdw_t_out_1(a int)
SERVER oss_serv
OPTIONS (format 'csv', filetype 'gzip', dir 'test/');

文件名称如下：

fdw_t_out_1_20200805110207_1718599661_seg-1_0.csv.gz

导出文件格式为ORC，使用prefix方式指定前缀路径：

CREATE FOREIGN TABLE fdw_t_out_2(a int)
SERVER oss_serv
OPTIONS (format 'orc', prefix 'test/my_orc_test');