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

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=':')