在对象存储中读取和写入文本数据

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存储桶的写权限

  1. 在S3上为PXF示例文件创建一个文件夹。 例如, 假设您对名为 BUCKET 的S3存储桶具有写权限：

    $ aws s3 mb s3://BUCKET/pxf_examples

  2. 在本地创建一个名为 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

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

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

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

  4. 验证文件现在位于S3中:

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

  5. 启动 psql 子系统:

    $ psql -d postgres

  6. 使用 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',');

  7. 查询外部表:

    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)

  8. 创建另一个引用 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存储桶的写权限

  1. 创建第二个带分隔符的纯文本文件：

    $ vi /tmp/pxf_s3_multi.txt

  2. 将以下数据复制/黏贴到 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

    注意使用冒号 : 分隔三个字段。 另外还要注意第一个字段(地址)周围的引号。 此字段包含嵌入式换行符，用于将街道地址与城市和州分开。

  3. 将文本文件复制到S3：

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

  4. 使用 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 的替代语法。

  5. 查询 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)

写入文本数据

“<objstore>: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的数据。

  1. 使用上述的数据模式创建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逗号字符 ,

  2. 通过在 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 );

  3. (可选) 将您在 示例: 从S3读取文本数据 创建的 pxf_s3_textsimple 表中的数据插入到 pxf_s3_writetbl_1 ：

    postgres=# INSERT INTO pxf_s3_writetbl_1 SELECT * FROM pxf_s3_textsimple;

  4. 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 默认分隔符。

  5. 查询可读外部表：

    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 表的完整内容(如果执行了可选步骤)

  6. 创建第二个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=':');

  7. 通过直接写入 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 );

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