使用PXF访问Azure，Google云端存储，Minio和S3对象存储 - 读取和写入文本数据 - 《Greenplum数据库 v6.0 中文文档》

先决条件
读取文本数据
- 示例: 从S3读取文本数据
读取含有双引号引起来的换行符的文本数据
- 示例: 从S3中读取多行文本数据
写入文本数据
- 示例: 将文本数据写入S3
  - 步骤

PXF 对象存储连接器支持普通分隔和逗号分隔的值文本数据。本节介绍如何使用PXF访问对象存储中的文本数据，包括如何创建、查询以及将数据写入引用对象存储中文件的外部表。

注意: 从对象存储中访问文本数据和访问HDFS中的文本数据非常相似。

先决条件

在尝试从对象存储中读取数据或将数据写入对象存储之前，请确保已满足PXF对象存储先决条件

读取文本数据

当您从对象存储中读取每行都是一条记录的分隔的纯文本或.csv数据时，请使用 <objstore>:text 配置文件。 PXF 支持以下 <objstore> 配置文件前缀：

对象存储	配置前缀
Azure Blob Storage	wasbs
Azure Data Lake	adl
Google Cloud Storage	gs
Minio	s3
S3	s3

以下语法创建一个引用对象存储中示例文本文件的Greenplum数据库可读外部表：

CREATE EXTERNAL TABLE <table_name> 
    ( <column_name> <data_type> [, ...] | LIKE <other_table> )
LOCATION ('pxf://<path-to-file>?PROFILE=<objstore>:text[&SERVER=<server_name>][&<custom-option>=<value>[...]]')
FORMAT '[TEXT|CSV]' (delimiter[=|<space>][E]'<delim_value>');

CREATE EXTERNAL TABLE 命令中使用的特定关键字和值见下表中描述。

关键字	值
<path‑to‑file>	S3 对象存储中文件或目录的绝对路径
PROFILE=<objstore>:text	`PROFILE` 关键字必须定义为特定的对象存储。例如: `s3:text`
SERVER=<server_name>	PXF用于访问数据的命名服务器配置。可选的; 如果未指定，PXF将使用`default`服务器。
FORMAT	当 <path-to-file> 引用分隔的纯文本数据时，请使用 `FORMAT` `‘TEXT’` 。当 <path-to-file> 引用逗号分隔的值数据时，请使用 `FORMAT` `‘CSV’`
delimiter	数据中的分隔符。对于`FORMAT` `‘CSV’`，默认的<delim_value> 是逗号`,`。当分隔符为转义序列时，在<delim_value> 前面加上`E`。示例：`(delimiter=E’\t’)`, `(delimiter ‘:’)`。

如果要访问S3对象存储库：

您可以通过CREATE EXTERNAL TABLE命令中的自定义选项提供S3凭据，如使用DDL覆盖S3服务器配置中所述。
如果您正在从S3中读取CSV格式的数据，则可以指示PXF使用S3 Select Amazon服务检索数据。有关用于此目的的PXF自定义选项的更多信息，请参考使用Amazon S3选择服务。

示例: 从S3读取文本数据

执行以下步骤创建示例文本文件，将文件复制到S3, 然后使用 s3:text 配置文件创建两个PXF 外部表来查询数据。

要运行此示例，您必须：

在系统上安装了AWS CLI工具
知道您的AWS访问ID和密钥
拥有对S3存储桶的写权限

在S3上为PXF示例文件创建一个文件夹。例如, 假设您对名为 BUCKET 的S3存储桶具有写权限：
```
$ aws s3 mb s3://BUCKET/pxf_examples
```

在本地创建一个名为 pxf_s3_simple.txt 的分隔的纯文本数据文件:

$ echo 'Prague,Jan,101,4875.33
Rome,Mar,87,1557.39
Bangalore,May,317,8936.99
Beijing,Jul,411,11600.67' > /tmp/pxf_s3_simple.txt

请注意使用逗号 , 分隔四个字段

将数据文件复制到您在步骤1创建的S3 目录中：

$ aws s3 cp /tmp/pxf_s3_simple.txt s3://BUCKET/pxf_examples/

验证文件现在位于S3中:

$ aws s3 ls s3://BUCKET/pxf_examples/pxf_s3_simple.txt

启动 psql 子系统:
```
$ psql -d postgres
```

使用 PXF s3:text 配置文件创建Greenplum数据库外部表，该表引用您刚刚创建并添加到S3中的 pxf_s3_simple.txt 文件。例如, 假设您的服务名为 s3srvcfg：

postgres=# CREATE EXTERNAL TABLE pxf_s3_textsimple(location text, month text, num_orders int, total_sales float8)
            LOCATION ('pxf://BUCKET/pxf_examples/pxf_s3_simple.txt?PROFILE=s3:text&SERVER=s3srvcfg')
          FORMAT 'TEXT' (delimiter=E',');

查询外部表:

postgres=# SELECT * FROM pxf_s3_textsimple;

   location    | month | num_orders | total_sales 
---------------+-------+------------+-------------
 Prague        | Jan   |        101 |     4875.33
 Rome          | Mar   |         87 |     1557.39
 Bangalore     | May   |        317 |     8936.99
 Beijing       | Jul   |        411 |    11600.67
(4 rows)

创建另一个引用 pxf_s3_simple.txt 的外部表，这次指定 CSV FORMAT:

postgres=# CREATE EXTERNAL TABLE pxf_s3_textsimple_csv(location text, month text, num_orders int, total_sales float8)
            LOCATION ('pxf://BUCKET/pxf_examples/pxf_s3_simple.txt?PROFILE=s3:text&SERVER=s3srvcfg')
          FORMAT 'CSV';
postgres=# SELECT * FROM pxf_s3_textsimple_csv;

当您为逗号分隔值数据指定FORMAT 'CSV'时，不需要提供delimiter分隔符选项，因为逗号是默认的分隔符。

读取含有双引号引起来的换行符的文本数据

使用 <objstore>:text:multi 配置文件读取数据中含有引号引起来的换行符，单行或多行的分隔文本数据。以下语法创建一个引用对象存储中此类文本文件的Greenplum数据库可读外部表：

CREATE EXTERNAL TABLE <table_name>
    ( <column_name> <data_type> [, ...] | LIKE <other_table> )
LOCATION ('pxf://<path-to-file>?PROFILE=<objstore>:text:multi[&SERVER=<server_name>][&<custom-option>=<value>[...]]')
FORMAT '[TEXT|CSV]' (delimiter[=|<space>][E]'<delim_value>');

CREATE EXTERNAL TABLE命令中使用的特定关键字和值见下表中描述。

关键字	值
<path‑to‑file>	S3数据存储中的目录或文件的绝对路径
PROFILE=<objstore>:text:multi	`PROFILE` 关键字必须指定为特定的对象存储。例如：`s3:text:multi`
SERVER=<server_name>	PXF用于访问数据的命名服务器配置。可选的; 如果未指定，PXF将使用`default`服务器。
FORMAT	当<path-to-hdfs-file>引用分隔的纯文本数据时，请使用`FORMAT` `‘TEXT’`。当<path-to-hdfs-file>引用逗号分隔值数据时，请使用`FORMAT` `‘CSV’`。
delimiter	数据中的分隔符。对于`FORMAT` `‘CSV’`，默认的<delim_value> 是逗号`,`。当分隔符为转义序列时，在<delim_value> 前面加上`E`。示例：`(delimiter=E’\t’)`, `(delimiter ‘:’)`。

如果要访问S3对象存储，则可以通过CREATE EXTERNAL TABLE命令中的自定义选项提供S3凭据，如使用DDL覆盖S3服务器配置中所述。

示例: 从S3中读取多行文本数据

执行以下步骤创建一个样例文本文件、将文件复制到S3，并使用 PXF s3:text:multi 配置文件创建一个Greenplum数据库可读外部表来查询数据。

要运行此示例，您必须：

在系统上安装了AWS CLI工具
知道您的AWS访问ID和密钥
拥有对S3存储桶的写权限

创建第二个带分隔符的纯文本文件：
```
$ vi /tmp/pxf_s3_multi.txt
```
将以下数据复制/黏贴到 pxf_s3_multi.txt 中：
```
"4627 Star Rd.
San Francisco, CA  94107":Sept:2017
"113 Moon St.
San Diego, CA  92093":Jan:2018
"51 Belt Ct.
Denver, CO  90123":Dec:2016
"93114 Radial Rd.
Chicago, IL  60605":Jul:2017
"7301 Brookview Ave.
Columbus, OH  43213":Dec:2018
```
注意使用冒号 : 分隔三个字段。另外还要注意第一个字段(地址)周围的引号。此字段包含嵌入式换行符，用于将街道地址与城市和州分开。

将文本文件复制到S3：

$ aws s3 cp /tmp/pxf_s3_multi.txt s3://BUCKET/pxf_examples/

使用 s3:text:multi 配置文件创建一个引用S3文件 pxf_s3_multi.txt 的外部表，确保定义 : (冒号) 作为字段分隔符。例如，假设您的服务名为 s3srvcfg ：

postgres=# CREATE EXTERNAL TABLE pxf_s3_textmulti(address text, month text, year int)
            LOCATION ('pxf://BUCKET/pxf_examples/pxf_s3_multi.txt?PROFILE=s3:text:multi&SERVER=s3srvcfg')
          FORMAT 'CSV' (delimiter ':');

注意指定 delimiter 的替代语法。

查询 pxf_s3_textmulti 表:

postgres=# SELECT * FROM pxf_s3_textmulti;

         address          | month | year 
--------------------------+-------+------
 4627 Star Rd.            | Sept  | 2017
 San Francisco, CA  94107           
 113 Moon St.             | Jan   | 2018
 San Diego, CA  92093               
 51 Belt Ct.              | Dec   | 2016
 Denver, CO  90123                  
 93114 Radial Rd.         | Jul   | 2017
 Chicago, IL  60605                 
 7301 Brookview Ave.      | Dec   | 2018
 Columbus, OH  43213                
(5 rows)

写入文本数据

“:text” 配置文件支持将单行纯文本文件写入对象存储中。使用PXF创建可写外部表时，请指定目录名称。当您将记录写入可写外部表中，您插入的数据块将写入指定目录中的一个或多个文件中。

注意：使用可写配置文件创建的外部表仅支持 INSERT 操作。如果要查询插入的数据，则比如单独创建一个引用该目录的可读外部表。

使用以下语法创建一个引用对象存储目录的Greenplum数据库可写外部表：

CREATE WRITABLE EXTERNAL TABLE <table_name> 
    ( <column_name> <data_type> [, ...] | LIKE <other_table> )
LOCATION ('pxf://<path-to-dir>
    ?PROFILE=<objstore>:text[&SERVER=<server_name>][&<custom-option>=<value>[...]]')
FORMAT '[TEXT|CSV]' (delimiter[=|<space>][E]'<delim_value>');
[DISTRIBUTED BY (<column_name> [, ... ] ) | DISTRIBUTED RANDOMLY];

CREATE EXTERNAL TABLE 命令中使用的特定关键字和值见下表中描述。

关键字	值
<path‑to‑dir>	S3数据存储中目录的绝对路径
PROFILE=<objstore>:text	`PROFILE` 关键字必须指定为对象存储。例如： `s3:text`
SERVER=<server_name>	PXF用于访问数据的命名服务器配置。可选的; 如果未指定，PXF将使用`default`服务器。
<custom‑option>=<value>	<custom-option> 选项见下方描述
FORMAT	将分隔的纯文本写入<path-to-dir>时，使用 `FORMAT` `‘TEXT’` 。将逗号分隔值文本写入<path-to-dir>时，使用 `FORMAT` `‘CSV’` 。
delimiter	数据中的分隔符。数据中的分隔符。对于`FORMAT` `‘CSV’`，默认的<delim_value> 是逗号`,`。当分隔符为转义序列时，在<delim_value> 前面加上`E`。示例：`(delimiter=E’\t’)`, `(delimiter ‘:’)`。
DISTRIBUTED BY	如果您计划将现有Greenplum数据库表中的数据加载到可写外部表，考虑在可写外部表上使用与Greenplum表相同的分布策略或<字段名>。这样做可以避免数据加载操作中segment节点间额外的数据移动。.

使用 <objstore>:text 配置文件创建可写外部表时，可以选择使用记录或块压缩。 PXF <objstore>:text 配置文件支持以下压缩编解码器：

org.apache.hadoop.io.compress.DefaultCodec
org.apache.hadoop.io.compress.GzipCodec
org.apache.hadoop.io.compress.BZip2Codec

您可以通过 CREATE EXTERNAL TABLE LOCATION 子句中的自定义选项指定压缩编码器。 <objstore>:text 配置文件支持以下自定义写入选项：

选项	值描述
COMPRESSION_CODEC	压缩编解码器Java类名。如果未提供此选项，Greenplum数据库不会执行压缩编码。支持的压缩编解码器包括： `org.apache.hadoop.io.compress.DefaultCodec` `org.apache.hadoop.io.compress.BZip2Codec` `org.apache.hadoop.io.compress.GzipCodec`
COMPRESSION_TYPE	采用的压缩类型; 支持的值为 `RECORD` (默认) 或 `BLOCK`
THREAD-SAFE	确定表查询是否可以在多线程模式下运行的布尔值。默认为 `TRUE`。将此选项设置为 `FALSE` 以处理单个线程中所有非线程安全操作 (例如，压缩)的请求。

如果要访问S3对象存储，则可以通过CREATE EXTERNAL TABLE命令中的自定义选项提供S3凭据，如使用DDL覆盖S3服务器配置中所述。

示例: 将文本数据写入S3

这个实力引用在示例: 从S3读取文本数据中介绍的数据模式。

列名	数据类型
location	text
month	text
number_of_orders	int
total_sales	float8

此示例还可以选择使用您在该练习中创建的名为 pxf_s3_textsimple 的Greenplum数据库外部表。

步骤

执行以下步骤，使用与上述相同的数据模式创建Greenplum数据库可写外部表，其中之一将使用压缩。你将使用 PXF s3:text 配置文件将数据写入S3。您还将创建一个单独的可读外部表，以读取您写入S3的数据。

使用上述的数据模式创建Greenplum数据库可写外部表。写入S3 目录 BUCKET/pxf_examples/pxfwrite_s3_textsimple1。创建表是指定逗号 , 作为分隔符。例如，假设您的服务名称为 s3srvcfg：

postgres=# CREATE WRITABLE EXTERNAL TABLE pxf_s3_writetbl_1(location text, month text, num_orders int, total_sales float8)
            LOCATION ('pxf://BUCKET/pxf_examples/pxfwrite_s3_textsimple1?PROFILE=s3:text&SERVER=s3srvcfg')
          FORMAT 'TEXT' (delimiter=',');

将 FORMAT 子句 delimiter 值指定为单个ASCII逗号字符 ,

通过在 pxf_s3_writetbl_1 上调用SQL INSERT 命令，将一些单独的记录写入 pxfwrite_s3_textsimple1 S3目录：

postgres=# INSERT INTO pxf_s3_writetbl_1 VALUES ( 'Frankfurt', 'Mar', 777, 3956.98 );
postgres=# INSERT INTO pxf_s3_writetbl_1 VALUES ( 'Cleveland', 'Oct', 3812, 96645.37 );

(可选) 将您在示例: 从S3读取文本数据创建的 pxf_s3_textsimple 表中的数据插入到 pxf_s3_writetbl_1 ：
```
postgres=# INSERT INTO pxf_s3_writetbl_1 SELECT * FROM pxf_s3_textsimple;
```
Greenplum数据库不支持直接查询可写外部表。要查询刚刚添加到S3的数据，必须创建一个引用该S3目录的Greenplum数据库可读外部表：
```
postgres=# CREATE EXTERNAL TABLE pxf_s3_textsimple_r1(location text, month text, num_orders int, total_sales float8)
            LOCATION ('pxf://BUCKET/pxf_examples/pxfwrite_s3_textsimple1?PROFILE=s3:text&SERVER=s3srvcfg')
            FORMAT 'CSV';
```
创建可读外部表时，请指定 'CSV' FORMAT，因为您创建可写外部表时使用逗号 , 作为分隔字符，即 'CSV' FORMAT 默认分隔符。

查询可读外部表：

postgres=# SELECT * FROM pxf_s3_textsimple_r1 ORDER BY total_sales;

 location  | month | num_orders | total_sales 
-----------+-------+------------+-------------
 Rome      | Mar   |         87 |     1557.39
 Frankfurt | Mar   |        777 |     3956.98
 Prague    | Jan   |        101 |     4875.33
 Bangalore | May   |        317 |     8936.99
 Beijing   | Jul   |        411 |    11600.67
 Cleveland | Oct   |       3812 |    96645.37
(6 rows)

pxf_s3_textsimple_r1 表包含了您单独插入的记录以及 pxf_s3_textsimple 表的完整内容(如果执行了可选步骤)

创建第二个Greenplum数据库可写外部表，这次使用Gzip 压缩并使用冒号 : 作为分隔符：

postgres=# CREATE WRITABLE EXTERNAL TABLE pxf_s3_writetbl_2 (location text, month text, num_orders int, total_sales float8)
            LOCATION ('pxf://BUCKET/pxf_examples/pxfwrite_s3_textsimple2?PROFILE=s3:text&SERVER=s3srvcfg&COMPRESSION_CODEC=org.apache.hadoop.io.compress.GzipCodec')
          FORMAT 'TEXT' (delimiter=':');

通过直接写入 pxf_s3_writetbl_2 表将一些记录写入到 pxfwrite_s3_textsimple2 S3目录：

gpadmin=# INSERT INTO pxf_s3_writetbl_2 VALUES ( 'Frankfurt', 'Mar', 777, 3956.98 );
gpadmin=# INSERT INTO pxf_s3_writetbl_2 VALUES ( 'Cleveland', 'Oct', 3812, 96645.37 );

要从新创建的名为 pxfwrite_s3_textsimple2 的S3目录查询数据，您可以如上所述创建一个Greenplum数据库可读外部表，该表引用此S3目录并指定 FORMAT 'CSV' (delimiter=':') 。