使用gpfdist和gpload转换外部数据

使用gpfdist和gpload转换外部数据

gpfdist并行文件服务器允许用户设置转换，使Greenplum数据库外部表能够以CREATE EXTERNAL TABLE命令的FORMAT子句不支持的格式读取和写入文件。input转换以外部数据格式读取文件，并以CSV或外部表的FORMAT子句中指定的其他文本格式将行输出到gpfdist。output转换以文本格式从gpfdist接收行，并将它们转换为外部数据格式。

Note: gpfdist和gpload仅与发布它们的Greenplum数据库主要版本兼容。例如，与Greenplum Database 4.x一起安装的gpfdist工具不能与Greenplum Database 5.x或6.x一起使用。

本节介绍了设置数据转换的任务，以使gpfdist使用Greenplum数据库不支持的格式读取或写入外部数据文件。

关于gpfdist转换
确定转换方案
编写转换
编写gpfdist配置文件
传输数据
配置文件格式
XML转换示例

Parent topic: 装载和卸载数据

关于gpfdist转换

要设置数据格式转换，用户需要提供一个可执行命令，该命令可以被gpfdist以包含数据的文件名调用。例如，用户可以编写一个shell脚本，该脚本在XML文件上运行XSLT转换，以输出具有以竖线(|)字符分隔的列和使用换行符分隔的行的行。

转换是在以命令行方式传递给gpfdist的YAML格式的配置文件中配置的。

如果要将外部数据加载到Greenplum数据库的表中，可以使用gpload工具自动执行创建外部表的任务，运行gpfdist并将转换后的数据加载到数据库表中。

从数据库中访问外部XML文件中的数据是常见的需要转换的示例。下图展示了对ETL服务器上的XML文件执行转换的gpfdist。

Figure 1. 使用XML转换的外部表

以下是为外部数据文件设置gpfdist转换的高级步骤。该过程用XML示例说明。

Determine the transformation schema.
Write a transformation.
Write the gpfdist configuration file.
Transfer the data.

确定转换方案

要为转换项目做准备：

确定该项目的目标，例如索引数据、分析数据、组合数据等等。
检查源文件并标记文件结构和元素名称。
选择要导入的元素并且决定是否需要其他适当的限制。

例如，下面的XML文件prices.xml是一个简短的包含价格记录的文件。每个价格记录包含两个字段：项目编号和价格。

<?xml version="1.0" encoding="ISO-8859-1" ?>
<prices>
  <pricerecord>
    <itemnumber>708421</itemnumber>
    <price>19.99</price>
  </pricerecord>
  <pricerecord>
    <itemnumber>708466</itemnumber>
    <price>59.25</price>
  </pricerecord>
  <pricerecord>
    <itemnumber>711121</itemnumber>
    <price>24.99</price>
  </pricerecord>
</prices>

目的是把所有的数据导入到一个具有一个整数列itemnumber和一个小数列price的Greenplum数据库表中。

编写转换

转换指定从数据中抽取什么。用户可以使用适合其项目的任何创作环境和语言。对于XML转换，基于项目的目标和范围选择一种如XSLT、Joost（STX）、Java、Python或者Perl的技术。

在price的例子中，下一步是转换XML数据成一个简单的分隔成两列的格式。

708421|19.99
708466|59.25
711121|24.99

下面的称为input_transform.stx的STX转换完成了这种数据转换。

<?xml version="1.0"?>
<stx:transform version="1.0"
   xmlns:stx="http://stx.sourceforge.net/2002/ns"
   pass-through="none">
  <!-- declare variables -->
  <stx:variable name="itemnumber"/>
  <stx:variable name="price"/>
  <!-- match and output prices as columns delimited by | -->
  <stx:template match="/prices/pricerecord">
    <stx:process-children/>
    <stx:value-of select="$itemnumber"/>    
<stx:text>|</stx:text>
    <stx:value-of select="$price"/>      <stx:text>
</stx:text>
  </stx:template>
  <stx:template match="itemnumber">
    <stx:assign name="itemnumber" select="."/>
  </stx:template>
  <stx:template match="price">
    <stx:assign name="price" select="."/>
  </stx:template>
</stx:transform>

此STX转换声明两个临时变量itemnumber 和price，以及以下规则。

当找到满足XPath表达式/prices/pricerecord的元素时，检查子元素并生成包含itemnumber变量值，一个|字符，price变量值和一个新行的输出。
当找到元素时，将该元素的内容存储在变量itemnumber中。
当找到元素后，将该元素的内容存储在变量price中。

编写gpfdist配置文件

gpfdist配置是一个YAML 1.1文档。它指定在装载或者抽取数据时gpfdist用来选择一种转换并应用的规则。

这个gpfdist 配置的例子包含下列项：

定义TRANSFORMATIONS的config.yaml文件
config.yaml文件中引用的input_transform.sh包装器脚本
input_transform.sh中调用的joost转换input_transform.stx

除普通的YAML规则（如文档用三个破折号开始（-—））之外，gpfdist配置必须符合下列限制：

VERSION设置必须出现并且值为1.0.0.1。
TRANSFORMATIONS设置必须出现并且包含一个或者更多个映射。
TRANSFORMATION中的每一个映射必须包含：
- 一个值为’input’或者’output’的TYPE
- 一个表明转换如何运行的COMMAND。
TRANSFORMATION中的每一个映射可以包含可选的CONTENT、SAFE以及STDERR设置。

下面这个称为config.yaml的gpfdist配置适用于prices的例子。每一行开始的缩进是有意义的，它们反映了该说明的层级性。下面例子中的名称prices_input将在后面用SQL创建该表时引用。

---
VERSION: 1.0.0.1
TRANSFORMATIONS:
  prices_input:
    TYPE:     input
    COMMAND:  /bin/bash input_transform.sh %filename%

COMMAND设置使用了一个名为input_transform.sh的包装器脚本，它带有一个%filename%占位符。当gpfdist运行该prices_input转换时，它用/bin/bash调用input_transform.sh ，并且将%filename%占位符替换为要转换的输入文件的路径。名为input_transform.sh的包装器脚本包含调用STX转换并返回其输出的逻辑。

如果使用了Joost，则必须安装Joost STX引擎。

#!/bin/bash
# input_transform.sh - sample input transformation, 
# demonstrating use of Java and Joost STX to convert XML into
# text to load into Greenplum Database.
# java arguments:
#   -jar joost.jar         joost STX engine
#   -nodecl                  don't generate a <?xml?> declaration
#   $1                        filename to process
#   input_transform.stx    the STX transformation
#
# the AWK step eliminates a blank line joost emits at the end
java \
    -jar joost.jar \
    -nodecl \
    $1 \
    input_transform.stx \
 | awk 'NF>0

input_transform.sh文件使用带有AWK解释器的Joost STX引擎。下面的图表展示了gpfdist运行该转换的处理流程。

传输数据

使用基于相应模式的SQL语句创建目标数据库表。

对于保存已加载数据的Greenplum数据库表没有特殊要求。在prices示例中，以下命令创建要加载数据的prices表。

CREATE TABLE prices (
  itemnumber integer,       
  price       decimal        
) 
DISTRIBUTED BY (itemnumber);

接下来，使用其中一种方法用gpfdist转换数据。

GPLOAD只支持输入转换，但是在很多情况下更容易实现。
INSERT INTO SELECT FROM支持输入和输出转换，但是会暴露更多细节。

用GPLOAD转换

Greenplum数据库gpload工具使用gpfdist并行文件服务器和YAML格式的配置文件来编排数据加载操作。gpload自动执行以下任务：

在数据库中创建可读的外部表。
使用包含转换的配置文件启动gpfdist实例。
运行INSERT INTO table_name SELECT FROM external_table以加载数据。
删除外部表定义。

Transforming data with 用gpload 转换数据要求出现在gpload 控制文件的INPUT节中的设置TRANSFORM和TRANSFORM_CONFIG。

有关在gpload控制文件中这些设置的语法和布置，请见Greenplum数据库参考指南。

TRANSFORM_CONFIG指定gpfdist配置文件的名称。
The TRANSFORM设置表示TRANSFORM_CONFIG中提到的文件中描述的转换名称。

---
VERSION: 1.0.0.1
DATABASE: ops
USER: gpadmin
GPLOAD:
  INPUT:
    - TRANSFORM_CONFIG: config.yaml
    - TRANSFORM: prices_input
    - SOURCE:
        FILE: prices.xml

转换名称必须出现在两个位置：在gpfdist配置文件的TRANSFORM设置中以及在TRANSFORM_CONFIG节提到的文件的TRANSFORMATIONS节中。

在gpload控制文件中，可选的参数MAX_LINE_LENGTH指定被传递给gpload的XML转换数据中一行的最大长度。

下面的图表展示了gpload控制文件、gpfdist配置文件以及XML数据文件之间的关系。

用INSERT INTO SELECT FROM转换

使用此加载方法，用户可以执行gpload自动执行的每个任务。用户启动gpfdist，创建一个外部表，加载数据，然后通过删除表并停止gpfdist来清理。

在CREATE EXTERNAL TABLE定义的LOCATION子句中指定转换。例如，在下面的命令中转换被显示为粗体（使用命令gpfdist -c config.yaml先运行gpfdist）。

CREATE READABLE EXTERNAL TABLE prices_readable (LIKE prices)
   LOCATION ('gpfdist://hostname:8080/prices.xml#transform=prices_input')
   FORMAT 'TEXT' (DELIMITER '|')
   LOG ERRORS SEGMENT REJECT LIMIT 10;

在上面的命令中，把hostname改成实际的主机名。prices_input来自于gpfdist配置文件。

下面的查询装载数据到prices表中。

INSERT INTO prices SELECT * FROM prices_readable;

配置文件格式

gpfdist配置文件使用YAML 1.1文档格式并且实现了一种定义转换参数的方案。配置文件必须是一个合法的YAML文档。

gpfdist程序会按照顺序处理该文档并且使用缩进（空格）来判断文档的层次以及小节之间的关系。空白的使用很重要。不要使用空白来进行格式化也不要用制表符。

下面是一个配置文件的基本结构。

---
VERSION:   1.0.0.1
TRANSFORMATIONS: 
  transformation_name1:
    TYPE:      input | output
    COMMAND:   command
    CONTENT:   data | paths
    SAFE:      posix-regex
    STDERR:    server | console
  transformation_name2:
    TYPE:      input | output
    COMMAND:   command 
...

VERSION

必需。gpfdist配置文件方案的版本。当前版本是1.0.0.1。

TRANSFORMATIONS

必需。开始转换说明小节。一个配置文件必须有至少一个转换。当gpfdist收到一个转换请求时，它会在这个小节查找具有匹配的转换名称的项。

TYPE

必须。指定转换的方向。值是input或者output。值为 input 或者 output.

input：gpfdist把转换处理的标准输出当做是要载入到Greenplum数据库的一个记录流。
output : gpfdist 把转换处理的标准输入当作是来自于Greenplum数据库的一个记录流并且写出到适当的输出。

COMMAND

必须。指定将被gpfdist执行来做转换的命令。

对于输入转换，gpfdist调用CONTENT设置中指定的命令。该命令应该会以适当的方式打开底层文件并且为每一行产生一个TEXT行以载入到Greenplum数据库。输入转换决定整个内容是应该被转换为一个行还是多个行。

对于输出转换，gpfdist调用在CONTENT设置中指定的命令。输出命令应该会以适当的方式打开底层文件并且向其中写入。输出转换决定转换好的输出的最终安置。

CONTENT

可选。值是data 和 paths。默认值是data。

当CONTENT指定data时，COMMAND小节中的文本%filename%会被替换为要读写的文件路径。
当CONTENT指定paths时，COMMAND小节中的文本%filename%会被替换为包含要读写的文件列表的临时文件路径。

下面是一个COMMAND小节的例子，它展示了被替换掉的文本%filename%。

COMMAND: /bin/bash input_transform.sh %filename%

SAFE

可选。一个POSIX 正则表达式，路径必须匹配它才能被传递给转换。在担心传递给命令的路径被注入或者被不正确解读时指定SAFE。默认对路径没有限制。

STDERR

可选。值是server 和 console。

这个设置指定如何处理来自于转换的标准错误输出。默认值server，指定 gpfdist将捕捉来自于转换的标准错误输出放在一个临时文件中，并且把该文件的前8k字节发送到Greenplum数据库作为一个错误消息。该错误消息将作为一个SQL错误出现。Console指定gpfdist不会重定向或者转换来自于转换的标准错误输出。

XML转换示例

下面的例子展示了对不同类型的XML数据以及STX转换的完整处理。与这些例子相关的文件和详细用法在GitHub仓库github.com://greenplum-db/gpdb gpMgmt/demo/gpfdist_transform目录中。在运行这些例子之前，请阅读开始之前小节中的README文件。README文件解释了如何下载这些例子中用到的示例数据文件。

基于命令的外部Web表

一个shell命令或者脚本的输出可定义基于命令的Web表数据。在CREATE EXTERNAL WEB TABLE的EXECUTE子句中指定该命令。EXECUTE子句会在指定的Master以及（一个或者多个）Segment主机上运行该shell命令或脚本。该命令或脚本必须位于EXECUTE子句中定义的主机上。

默认情况下，当活动Segment有输出行要处理时，该命令运行在Segment主机上。例如，如果每一个Segment主机运行四个有输出行要处理的主Segment实例，该命令在每个Segment主机上会运行四次。用户可以有选择地限制执行Web表命令的Segment实例的数量。ON子句中Web表定义里包括的所有Segment会并行运行该命令。

用户在外部表定义中指定的命令从数据库中执行并且不能访问来自.bashrc 或者 .profile的环境变量。可在EXECUTE子句中设置环境变量。例如：

=# CREATE EXTERNAL WEB TABLE output (output text)
    EXECUTE 'PATH=/home/gpadmin/programs; export PATH; myprogram.sh' 
    FORMAT 'TEXT';

脚本必须对gpadmin用户可执行并且在Master或者Segment主机上位于同一位置。

下列命令定义一个运行脚本的Web表。该脚本在每一台有需要处理输出行的Segment的Segment主机上运行。

=# CREATE EXTERNAL WEB TABLE log_output 
    (linenum int, message text) 
    EXECUTE '/var/load_scripts/get_log_data.sh' ON HOST 
    FORMAT 'TEXT' (DELIMITER '|');

IRS MeF XML 文件（在demo目录中）

这个例子展示了使用一种Joost STX转换装载一份IRS Modernized eFile tax return数据样例。该数据是一种复杂的XML文件形式。

美国国内税务署（IRS）在对XML进行了可观的投资并且指定在其现代化电子文件（MeF）系统中使用它。在MeF中，每一份纳税申报表都是一个具有深层次结构的XML文档，该层次结构仔细地反映了底层纳税代码的特定形式。

XML、XML Schema和样式表在他们的数据表达和业务工作流中扮演了重要角色。实际的XML数据被从一个附加了MIME “transimission file”消息的ZIP文件中抽取。更多有关MeF的信息，请见IRS网站上的Modernized e-File (Overview)。

样例XML文档RET990EZ_2006.xml大约有350KB大小，它有两个元素：

ReturnHeader
ReturnData

元素包含了有关纳税申报表的一般细节，例如纳税人的姓名、纳税年份以及preparer。元素包含了多个小节，它们含有关于纳税申报表及相关计划表的特定细节。

下面是该XML文件的一个被删节过的例子。

<?xml version="1.0" encoding="UTF-8"?> 
<Return returnVersion="2006v2.0"
   xmlns="https://www.irs.gov/efile" 
   xmlns:efile="https://www.irs.gov/efile"
   xsi:schemaLocation="https://www.irs.gov/efile"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> 
   <ReturnHeader binaryAttachmentCount="1">
     <ReturnId>AAAAAAAAAAAAAAAAAAAA</ReturnId>
     <Timestamp>1999-05-30T12:01:01+05:01</Timestamp>
     <ReturnType>990EZ</ReturnType>
     <TaxPeriodBeginDate>2005-01-01</TaxPeriodBeginDate>
     <TaxPeriodEndDate>2005-12-31</TaxPeriodEndDate>
     <Filer>
       <EIN>011248772</EIN>
       ... more data ...
     </Filer>
     <Preparer>
       <Name>Percy Polar</Name>
       ... more data ...
     </Preparer>
     <TaxYear>2005</TaxYear>
   </ReturnHeader>
   ... more data ..

目标是把所有数据导入到一个Greenplum数据库。首先，将该XML文档转换为文本形式，其中的新行已被转义过并且有两个列： ReturnId和末尾的一个用于整个MeF纳税申报表的列。例如：

AAAAAAAAAAAAAAAAAAAA|<Return returnVersion="2006v2.0"...

载入数据到Greenplum数据库。

WITSML™ 文件（在demo目录中）

这个例子展示了使用一种Joost STX转换载入描述一个石油钻塔的样例数据。该数据是一种从energistics.org下载的复杂的XML文件格式。

Wellsite Information Transfer Standard Markup Language (WITSML™)是一种石油工业发起的为技术和软件提供开放的、非所有权的、标准的接口，以便在石油公司、服务公司、钻探承包商和监管代理之间分享信息。更多有关WITSML™的信息请见http://www.energistics.org/。

石油钻塔的信息由一个顶层的元素和多个子元素（例如, ,等）构成。下面从该文件中摘录的片段展示了标签中的信息的类型。

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet href="../stylesheets/rig.xsl" type="text/xsl" media="screen"?>
<rigs 
 xmlns="http://www.energistics.org/schemas/131" 
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
 xsi:schemaLocation="http://www.energistics.org/schemas/131 ../obj_rig.xsd" 
 version="1.3.1.1">
 <documentInfo>
 ... misc data ...
 </documentInfo>
 <rig uidWell="W-12" uidWellbore="B-01" uid="xr31">
     <nameWell>6507/7-A-42</nameWell>
     <nameWellbore>A-42</nameWellbore>
     <name>Deep Drill #5</name>
     <owner>Deep Drilling Co.</owner>
     <typeRig>floater</typeRig>
     <manufacturer>Fitsui Engineering</manufacturer>
     <yearEntService>1980</yearEntService>
     <classRig>ABS Class A1 M CSDU AMS ACCU</classRig>
     <approvals>DNV</approvals>
 ... more data ...

目标是把这个钻塔的信息导入到Greenplum数据库。

样例文档rig.xml的尺寸大约是11KB。输入不包含制表符，因此相关信息可以被转换成用竖线（|）分隔的记录。

W-12|6507/7-A-42|xr31|Deep Drill #5|Deep Drilling Co.|John Doe|John.Doe@example.com|

有这些列：

well_uid text, — e.g. W-12
well_name text, — e.g. 6507/7-A-42
rig_uid text, — e.g. xr31
rig_name text, — e.g. Deep Drill #5
rig_owner text, — e.g. Deep Drilling Co.
rig_contact text, — e.g. John Doe
rig_email text, — e.g. John.Doe@example.com
doc xml

然后，载入该数据到Greenplum数据库中。

使用gpfdist和gpload转换外部数据

使用gpfdist和gpload转换外部数据

关于gpfdist转换

确定转换方案

编写转换

编写gpfdist配置文件

传输数据

用GPLOAD转换

用INSERT INTO SELECT FROM转换

配置文件格式

XML转换示例

基于命令的外部Web表

IRS MeF XML 文件（在demo目录中）

WITSML™ 文件 （在demo目录中）

WITSML™ 文件（在demo目录中）