合 Greenplum中的数据加载工具之gpfdist和gpload
Tags: GreenPlumgpfdistgpload数据加载
- 装载数据概述
- 简介
- 带列值的INSERT语句
- COPY语句
- 外部表
- 使用gpfdist外部表
- gpload
- 装载数据最佳实践
- gpfdist
- 概要
- 描述
- 选项
- 注解
- 示例
- gpload
- 概要
- 先决条件
- 描述
- 选项
- 控制文件格式
- 日志文件格式
- 注解
- 示例
- gpfdist和gpload的区别
- 使用gpfdist和gpload转换外部数据
- 关于gpfdist转换
- 确定转换方案
- 编写转换
- 编写gpfdist配置文件
- 传输数据
- 用GPLOAD转换
- 用INSERT INTO SELECT FROM转换
- 配置文件格式
- XML转换示例
- 基于命令的外部Web表
- IRS MeF XML 文件(在demo目录中)
- WITSML™ 文件 (在demo目录中)
- gpfdist示例
- 创建实验环境
- gpfdist加载数据
- 卸载数据
- gpload示例
- 创建实验环境
- 创建YAML格式控制文件
- 数据装载性能技巧
- 权限
- 总结
- 参考
装载数据概述
简介
在GreenPlum中有以下几种方式来进行数据的加载,包括
- 通过insert命令来实现少量数据的导入;
- 通过copy命令来实现数据的导入导出;
- 通过建立外部表及gpfdist实现数据的导入导出;
- 通过gpload实现数据的导入;
注意:进行数据加载后,一个好的习惯是查看数据有没有倾斜。
带列值的INSERT语句
带有值的单个INSERT语句会向表中加入一行。这个行会流过master并且被分布到一个segment上。 这是最慢的方法并且不适合装载大量数据。
通过简单的insert语句来实现,常用于少量数据的导入,当数据量较大时,会很耗时,从而不适合使用。
1 | insert into tablename values('data'); |
COPY语句
PostgreSQL的COPY语句从外部文件拷贝数据到数据表中。它比INSERT 语句插入多行的效率更高,但是行仍需流过master。所有数据都在一个命令中被拷贝,它并不是一种并行处理。
对于数据加载,GreenPlum数据库提供copy工具,copy工具源于PostgreSQL数据库,copy命令支持文件与表之间的数据加载和表对文件的数据卸载。使用copy命令进行数据加载,数据需要经过Master节点分发到Segment节点,同样使用copy命令进行数据卸载,数据也需要由Segment发送到Master节点,由Master节点汇总后再写入外部文件。这样就限制了数据加载与卸载的效率,但是数据量较小的情况下,copy命令就非常方便。
COPY命令的数据输入来自于一个文件或者标准输入。例如:
1 | COPY table FROM '/data/mydata.csv' WITH CSV HEADER; |
使用COPY适合于增加相对较小的数据集合(例如多达上万行的维度表)或者一次性数据装载。
在编写脚本处理装载少于1万行的少量数据时使用COPY。
因为COPY是一个单一命令,在使用这种方法填充表时没有必要禁用自动提交。
使用者可以运行多个并发的COPY命令以提高性能。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | aalhr=## create table tbl_pay_log_copy (id int primary key,order_num varchar(100),accountid varchar(30),qn varchar(20),appid int,amount numeric(10,2),pay_time timestamp); aalhr=## \d tbl_pay_log_copy Table "public.tbl_pay_log_copy" Column | Type | Modifiers -----------+-----------------------------+----------- id | integer | not null order_num | character varying(100) | accountid | character varying(30) | qn | character varying(20) | appid | integer | amount | numeric(10,2) | pay_time | timestamp without time zone | Indexes: "tbl_pay_log_copy_pkey" PRIMARY KEY, btree (id), tablespace "tbs_aalhr" Distributed by: (id) Tablespace: "tbs_aalhr" [gpadmin@mdw ~]$ head ios_pay.txt 73,ysios_receipt_3793615cb10dc393bba87c82d3c6544f,27388062,yriu1244_16043_001,2616,98.00,2017-11-06 17:36:43 72,ysios_receipt_32946f3d37e774781babe103352bd230,27424976,yriu1244_16043_001,2616,30.00,2017-11-06 15:18:56 75,ysios_receipt_3e2e432550253450412692392c7675d0,27388294,yriu1244_16043_001,2616,98.00,2017-10-19 07:33:03 74,ysios_receipt_3793615cb10dc393bba87c82d3c6544f,27388062,yriu1244_16043_001,2616,98.00,2017-11-06 20:40:46 77,ysios_receipt_ee6bed338a32f836a999133cd2e6d547,27388294,yriu1244_16043_001,2616,98.00,2017-10-19 22:27:46 76,ysios_receipt_ae53b142924c0604820537d61a9dd73e,27424976,yriu1244_16043_001,2616,648.00,2017-10-19 12:10:17 79,ysios_receipt_30ec130bcdf0e864629d12f8392d4b43,27385229,yriu1244_16043_001,2616,98.00,2017-10-21 07:46:01 78,ysios_receipt_e2b62024f1b0c3a2c3aae1e80f126eb6,27387306,yriu1244_16043_001,2616,25.00,2017-10-20 01:54:24 81,ysios_receipt_3e72a8e32c9fee546ab08d103606e6cb,27424976,yriu1244_16043_001,2616,30.00,2017-10-21 13:55:54 80,ysios_receipt_6ca291884fcfe3d1583b49a3611b4ccc,27424976,yriu1244_16043_001,2616,25.00,2017-10-21 13:55:51 [gpadmin@mdw ~]$ wc -l ios_pay.txt 20027 ios_pay.txt [gpadmin@mdw ~]$ du -sh ios_pay.txt 1.9M ios_pay.txt |
外部表
外部表提供了对Greenplum数据库之外的数据来源的访问。可以用SELECT语句访问它们,外部表 通常被用于抽取、装载、转换(ELT)模式,这是一种抽取、转换、装载(ETL)模式的变种,这种模式可以利用 Greenplum数据库的快速并行数据装载能力。
通过ETL,数据被从其来源抽取,在数据库外部使用外部转换工具(Informatica或者Datastage)转换,然后 被装载到数据库中。
通过ELT,Greenplum外部表提供对外部来源中数据的访问,外部来源可以是只读文件(例如文本、CSV或者XML文件)、 Web服务器、Hadoop文件系统、可执行的OS程序或者Greenplum gpfdist文件服务器,这些在 下一节中描述。外部表支持选择、排序和连接这样的SQL操作,这样数据可以被同时装载和转换,或者被装载到一个 装载表并且在数据库内被转换成目标表。
外部表使用CREATE EXTERNAL TABLE语句定义,该语句有一个LOCATION 子句定义数据的位置以及一个FORMAT子句定义源数据的格式,这样系统才能够解析输入数据。 文件使用file://协议,并且文件必须位于一台segment主机上由Greenplum超级用户可访问 的位置。数据可以被分散在segment主机上,并且每台主机上的每个主segment有不超过一个文件。LOCATION 子句中列出的文件的数量是将并行读取该外部表的segment的数量。
使用gpfdist外部表
外部表提供了对Greenplum数据库之外的来源中数据的访问。可以用SELECT语句访问它们,外部表通常被用于抽取、装载、转换(ELT)模式,这是一种抽取、转换、装载(ETL)模式的变种,这种模式可以利用Greenplum数据库的快速并行数据装载能力。这是COPY命令不持有的。
gpfdist原理:
gpfdist是一个使用HTTP协议的文件服务器程序,它以并行的方式向Greenplum数据库的Segment供应外部数据文件一个gpfdist实例,每秒能供应200MB并且很多gpfdist进程可以同时运行,每一个供应要被装载的数据的一部分。当使用者用INSERT INTO
1 2 3 4 5 6 7 8 9 10 11 12 | Segment 1 - gpfdist 1 Segment 2 - gpfdist 2 Segment 3 - gpfdist 3 Segment 4 - gpfdist 4 Segment 5 - gpfdist 1 Segment 6 - gpfdist 2 Segment 7 - gpfdist 3 Segment 8 - gpfdist 4 Segment 9 - gpfdist 1 Segment 10 - gpfdist 2 Segment 11 - gpfdist 3 Segment 12 - gpfdist 4 |
在装载到已有表之前删除索引,并且在装载之后重建索引。在装载完数据后重新创建索引比装载每行时增量更新 索引更快。
装载后在表上运行ANALYZE。在装载期间通过设置gp_autostats_mode 为NONE来禁用自动统计信息收集。在装载出错后运行VACUUM来回收空间。
对重度分区的列存表执行少量高频的数据装载可能会对系统有很大影响,因为在每个时间间隔内被访问的物理文件会很多。
gpload
gpload是一种数据装载工具,它扮演着Greenplum外部表并行装载特性的接口的角色。
要当心对gpload的使用,因为它会创建并且删除外部表,从而可能会导致系统目录膨胀。 可转而使用gpfdist,因为它能提供最好的性能。
gpload使用定义在一个YAML格式的控制文件中的规范来执行一次装载。它会执行下列操作:
- 调用gpfdist进程
- 基于定义的源数据创建一个临时的外部表定义
- 执行INSERT、UPDATE或者MERGE操作 将源数据载入数据库中的目标表
- 删除临时外部表
- 清除gpfdist进程
装载会在单个事务中完成。
装载数据最佳实践
在装载数据之前删掉现有表上的任何索引,并且在装载之后重建那些索引。新创建索引比装载每行时 增量更新索引更快。
在装载期间通过将gp_autostats_mode配置参数设置为NONE 禁用自动统计信息收集。
外部表并非为频繁访问或者ad hoc访问而设计。
外部表没有统计信息来告知优化器。可以用下面这样的语句在pg_class系统目录中为 外部表设置粗略的行数和磁盘页数估计:
1UPDATE pg_class SET reltuples=400000, relpages=400 WHERE relname='myexttable';在使用gpfdist时,通过为ETL服务器上的每一块NIC运行一个gpfdist 实例以最大化网络带宽。在gpfdist实例之间均匀地划分源数据。
在使用gpload时,在资源允许的情况下同时运行尽可能多的gpload 实例。利用可用的CPU、内存和网络资源以增加能从ETL服务器传输到Greenplum数据库的数据量。
使用COPY语句的SEGMENT REJECT LIMIT子句设置在 COPY FROM命令被中止之前可以出现错误的行的百分数限制。这个拒绝限制是针对每个 segment的,当任意一个segment超过该限制时,命令将被中止且不会有行被增加。使用LOG ERRORS 子句可以保存错误行。如果有一行在格式上有错误—例如缺少值或者有多余的值,或者数据类型不对— Greenplum数据库会在内部存储错误信息和行。使用内建SQL函数gp_read_error_log() 可以访问这种存储下来的信息。
如果装载出现错误,在该表上运行VACUUM以恢复空间。
在用户装载数据到表中后,在堆表(包括系统目录)上运行VACUUM,并且在所有的表上运行 ANALYZE。没有必要在追加优化表上运行VACUUM。如果表已经被分过区, 用户可以只清理和分析受数据装载影响的分区。这些步骤会清除来自于被中止的装载、删除或者更新中的行并且为表 更新统计信息。
在装载大量数据之后重新检查表中的segment倾斜。用户可以使用下面这样的查询来检查倾斜:
1SELECT gp_segment_id, count(*) FROM schema.table GROUP BY gp_segment_id ORDER BY 2;gpfdist默认假定最大记录尺寸为32K。要装载大于32K的数据记录,用户必须通过在 gpfdist命令行上指定-m <bytes选项来增加最大行 尺寸参数。如果用户使用的是gpload,在gpload控制文件中设置 MAX_LINE_LENGTH参数。
Note: 与Informatica Power Exchange的集成当前被限制为默认的32K记录长度。
gpfdist
将数据文件载入Greenplum数据库Segment或从其中写出数据文件到文件系统。
概要
1 2 3 4 5 6 | gpfdist [-d directory] [-p http_port] [-P last_http_port] [-l log_file] [-t timeout] [-S] [-w time] [-v | -V] [-s] [-m max_length] [--ssl certificate_path [--sslclean wait_time] ] [-c config.yml] gpfdist -? | --help gpfdist --version |
描述
gpfdist是Greenplum数据库并行文件分发程序。它可以被外部表和gpload 用来并行地将外部表文件提供给所有的Greenplum数据库Segment。它由可写外部表使用,并行接受来自Greenplum数据库 Segment的输出流,并将它们写出到文件中。
Note: gpfdist和gpload是在Greenplum的主版本级别有效的。 例如,Greenplum 4.x版本的gpfdist不能用于Greenplum 5.x或6.x版本。
为了使外部表使用gpfdist,外部表定义的LOCATION子句必须使用 gpfdist://协议(参见Greenplum数据库命令CREATE EXTERNAL TABLE)。
Note: 如果--ssl选项被指定来启用SSL安全性,请使用gpfdists://协议创建外部表。
使用gpfdist的好处是在读取或写入外部表时可以保证最大的并行性,从而提供最佳的性能, 并且更容易管理外部表。
对于只读外部表,当用户在外部表中SELECT时,gpfdist 将数据文件均匀地分析并提供给Greenplum数据库系统的所有Segment实例。对于可写的外部表, gpfdist在用户INSERT外部表时接受来自Segment的 并行输出流,并写入输出文件。
对于可读外部表,如果被加载的文件使用gzip或bzip2 (具有.gz或.bz2的文件扩展名),gpfdist 会在装载之前自动解压文件,前提是gunzip或bunzip2在用户的 可执行文件路径中。
Note: 目前,可读外部表不支持在Windows平台上的压缩,可写外部表不支持任何平台上的压缩。
当使用gpfdist或gpfdists协议读写数据时,Greenplum数据库 在HTTP请求头部中包含X-GP-PROTO,以指示该请求来自Greenplum数据库。该工具拒绝 请求头部中不包含X-GP-PROTO的HTTP请求。
大多数情况下,用户很可能希望在ETL机器而不是安装Greenplum数据库的主机上运行gpfdist。 要在其他主机上安装gpfdist,只需简单的将该程序复制到该主机上,然后将gpfdist 添加到用户的$PATH路径中。
Note: 使用IPv6时,请始终将数字IP地址包裹在括号内。
选项
-d directory
指定一个目录,gpfdist将从该目录中为可读外部表提供文件,或为可写外部表创建 输出文件。如果没有指定,默认为当前目录。
-l log_file
要记录标准输出消息的完全限定路径和日志文件名称。
-p http_port
gpfdist提供文件要使用的HTTP端口。默认为8080。
-P last_http_port
gpfdist将会提供文件服务的端口号范围 (http_port到last_http_port 包含岂止号码)中最后一个HTTP端口号号码。gpfdist会 以端口号设定范围内第一个成功绑定的端口号作为服务端口。
-t timeout
设置Greenplum数据库建立与gpfdist进程的连接所允许的时间。默认值是5秒。 允许的值是2到7200秒(2小时)。在网络流量大的系统上可能要增加。
-m max_length
设置以字节为单位的最大数据行长度。默认值是32768。当用户数据包含非常宽的行时 (或者当line too long错误消息发生时)应该使用,否则不应该使用, 因为它会增加资源分配。有效范围是32K到256MB(Windows系统上限为1MB)。
Note: 如果用户指定较大的最大行长度并运行大量的gpfdist并发连接, 则可能会发生内存问题。例如,使用96个并行gpfdist进程需要大约 24GB的内存((96 + 1) x 246MB)。
-s
启用简化的日志记录。指定此选项时,只有具有WARN级别或者更高级别的消息才 会写入gpfdist日志文件。INFO级别的消息不写入日志文件。 如果未指定这一选项,则所有gpfdist消息都写入日志文件。
用户可以指定此选项以减少写入日志文件的信息。
-S (use O_SYNC)
使用O_SYNC标志打开同步I/O的文件。任何对结果文件描述的写都会阻塞gpfdist, 直到数据被物理地写到底层硬件。
-w time
设置关闭目标文件(如命名管道)之前Greenplum数据库延迟的秒数。 默认值是0,没有延迟。最大值是7200秒(2小时)。
对于具有多个Segment的Greenplum数据库,在将不同Segment中的数据写入文件时,Segment之间可能会有延迟。 用户可以指定Greenplum数据库关闭文件之前要等待的时间,以确保所有数据都被写入文件。
--ssl certificate_path
将SSL加密添加到使用gpfdist传输的数据。使用--ssl certificate_path选项执行gpfdist之后, 从此文件服务器加载数据的唯一方法是使用gpfdist://协议。 有关gpfdist:// 协议的信息,请参阅Greenplum数据库管理员指南中的“装载和卸载数据”部分。
在certificate_path中指定的位置必须包含以下文件:
- 服务器证书文件,server.crt
- 服务器私钥文件,server.key
- 可信证书机构,root.crt
根目录(/)不能指定为certificate_path。