使用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数据库不支持的格式读取或写入外部数据文件。

Parent topic: 装载和卸载数据

关于gpfdist转换

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

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

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

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

Figure 1. 使用XML转换的外部表
使用gpfdist和gpload转换外部数据 - 图1

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

  1. Determine the transformation schema.
  2. Write a transformation.
  3. Write the gpfdist configuration file.
  4. Transfer the data.

确定转换方案

要为转换项目做准备:

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

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

  1. <?xml version="1.0" encoding="ISO-8859-1" ?>
  2. <prices>
  3. <pricerecord>
  4. <itemnumber>708421</itemnumber>
  5. <price>19.99</price>
  6. </pricerecord>
  7. <pricerecord>
  8. <itemnumber>708466</itemnumber>
  9. <price>59.25</price>
  10. </pricerecord>
  11. <pricerecord>
  12. <itemnumber>711121</itemnumber>
  13. <price>24.99</price>
  14. </pricerecord>
  15. </prices>

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

编写转换

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

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

  1. 708421|19.99
  2. 708466|59.25
  3. 711121|24.99

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

  1. <?xml version="1.0"?>
  2. <stx:transform version="1.0"
  3. xmlns:stx="http://stx.sourceforge.net/2002/ns"
  4. pass-through="none">
  5. <!-- declare variables -->
  6. <stx:variable name="itemnumber"/>
  7. <stx:variable name="price"/>
  8. <!-- match and output prices as columns delimited by | -->
  9. <stx:template match="/prices/pricerecord">
  10. <stx:process-children/>
  11. <stx:value-of select="$itemnumber"/>
  12. <stx:text>|</stx:text>
  13. <stx:value-of select="$price"/> <stx:text>
  14. </stx:text>
  15. </stx:template>
  16. <stx:template match="itemnumber">
  17. <stx:assign name="itemnumber" select="."/>
  18. </stx:template>
  19. <stx:template match="price">
  20. <stx:assign name="price" select="."/>
  21. </stx:template>
  22. </stx:transform>

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

  1. 当找到满足XPath表达式/prices/pricerecord的元素时,检查子元素并生成包含itemnumber变量值,一个|字符,price变量值和一个新行的输出。
  2. 当找到元素时,将该元素的内容存储在变量itemnumber中。
  3. 当找到元素后,将该元素的内容存储在变量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配置必须符合下列限制:

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

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

  1. ---
  2. VERSION: 1.0.0.1
  3. TRANSFORMATIONS:
  4. prices_input:
  5. TYPE: input
  6. 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引擎。

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

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

使用gpfdist和gpload转换外部数据 - 图2

传输数据

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

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

  1. CREATE TABLE prices (
  2. itemnumber integer,
  3. price decimal
  4. )
  5. 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中提到的文件中描述的转换名称。
  1. ---
  2. VERSION: 1.0.0.1
  3. DATABASE: ops
  4. USER: gpadmin
  5. GPLOAD:
  6. INPUT:
  7. - TRANSFORM_CONFIG: config.yaml
  8. - TRANSFORM: prices_input
  9. - SOURCE:
  10. FILE: prices.xml

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

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

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

使用gpfdist和gpload转换外部数据 - 图3

用INSERT INTO SELECT FROM转换

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

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

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

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

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

  1. INSERT INTO prices SELECT * FROM prices_readable;

配置文件格式

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

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

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

  1. ---
  2. VERSION: 1.0.0.1
  3. TRANSFORMATIONS:
  4. transformation_name1:
  5. TYPE: input | output
  6. COMMAND: command
  7. CONTENT: data | paths
  8. SAFE: posix-regex
  9. STDERR: server | console
  10. transformation_name2:
  11. TYPE: input | output
  12. COMMAND: command
  13. ...

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%。

  1. 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子句中设置环境变量。例如:

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

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

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

  1. =# CREATE EXTERNAL WEB TABLE log_output
  2. (linenum int, message text)
  3. EXECUTE '/var/load_scripts/get_log_data.sh' ON HOST
  4. 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文件的一个被删节过的例子。

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <Return returnVersion="2006v2.0"
  3. xmlns="https://www.irs.gov/efile"
  4. xmlns:efile="https://www.irs.gov/efile"
  5. xsi:schemaLocation="https://www.irs.gov/efile"
  6. xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  7. <ReturnHeader binaryAttachmentCount="1">
  8. <ReturnId>AAAAAAAAAAAAAAAAAAAA</ReturnId>
  9. <Timestamp>1999-05-30T12:01:01+05:01</Timestamp>
  10. <ReturnType>990EZ</ReturnType>
  11. <TaxPeriodBeginDate>2005-01-01</TaxPeriodBeginDate>
  12. <TaxPeriodEndDate>2005-12-31</TaxPeriodEndDate>
  13. <Filer>
  14. <EIN>011248772</EIN>
  15. ... more data ...
  16. </Filer>
  17. <Preparer>
  18. <Name>Percy Polar</Name>
  19. ... more data ...
  20. </Preparer>
  21. <TaxYear>2005</TaxYear>
  22. </ReturnHeader>
  23. ... more data ..

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

  1. 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/

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

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <?xml-stylesheet href="../stylesheets/rig.xsl" type="text/xsl" media="screen"?>
  3. <rigs
  4. xmlns="http://www.energistics.org/schemas/131"
  5. xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  6. xsi:schemaLocation="http://www.energistics.org/schemas/131 ../obj_rig.xsd"
  7. version="1.3.1.1">
  8. <documentInfo>
  9. ... misc data ...
  10. </documentInfo>
  11. <rig uidWell="W-12" uidWellbore="B-01" uid="xr31">
  12. <nameWell>6507/7-A-42</nameWell>
  13. <nameWellbore>A-42</nameWellbore>
  14. <name>Deep Drill #5</name>
  15. <owner>Deep Drilling Co.</owner>
  16. <typeRig>floater</typeRig>
  17. <manufacturer>Fitsui Engineering</manufacturer>
  18. <yearEntService>1980</yearEntService>
  19. <classRig>ABS Class A1 M CSDU AMS ACCU</classRig>
  20. <approvals>DNV</approvals>
  21. ... 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数据库中。