装载数据

本节描述了将数据装载到Greenplum数据库的不同方式。

Parent topic: Greenplum数据库最佳实践

带列值的INSERT语句

带有值的单个INSERT语句会向表中加入一行。这个行会流过master并且被分布到一个segment上。 这是最慢的方法并且不适合装载大量数据。

COPY语句

PostgreSQL的COPY语句从外部文件拷贝数据到数据表中。它比INSERT 语句插入多行的效率更高，但是行仍需流过master。所有数据都在一个命令中被拷贝，它并不是一种并行处理。

COPY命令的数据输入来自于一个文件或者标准输入。例如：

  1. COPY table FROM '/data/mydata.csv' WITH CSV HEADER;

使用COPY适合于增加相对较小的数据集合（例如多达上万行的维度表）或者一次性数据装载。

在编写脚本处理装载少于1万行的少量数据时使用COPY。

因为COPY是一个单一命令，在使用这种方法填充表时没有必要禁用自动提交。

使用者可以运行多个并发的COPY命令以提高性能。

外部表

外部表提供了对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外部表

装载大型事实表的最快方式是使用基于gpdist的外部表。gpfdist 是一个使用HTTP协议的文件服务器程序，它以并行的方式向Greenplum数据库的segment供应外部数据文件。 一个gpfdist实例每秒能供应200MB并且很多gpfdist进程可以 同时运行，每一个供应要被装载的数据的一部分。当使用者用INSERT INTO

SELECT * FROM 这样的语句开始装载时，INSERT语句会被master解析并且分布给主segment。segment 连接到gpfdist服务器并且并行检索数据，解析并验证数据，从分布键数据计算一个哈希值 并且基于哈希键把行发送给它的目标segment。每个gpfdist实例默认将接受最多64个 来自segment的连接。通过让更多的segment和gpfdist服务器参与到装载处理中，可以 以非常高的速度进行数据装载。

在使用gpfdist数量达到配置参数gp_external_max_segments 的最大值时，主segment会并行访问外部文件。在优化gpfdist的性能时，随着 segment的数量增加会最大化并行性。在尽可能多的ETL节点上均匀地散布数据。将非常大型的数据文件 分解成相等的部分，并且把数据分散在尽可能多的文件系统上。

在每个文件系统上运行两个gpfdist实例。在装载数据时，gpfdist 在segment节点上容易变成CPU密集型的操作。举个例子，如果有八个机架的segment节点，在segment上就有大量 可用的CPU来驱动更多的gpfdist进程。在尽可能多的接口上运行gpfdist。 要注意绑定网卡并且确保启动足够的gpfdist实例配合它们一起工作。

有必要在所有这些资源上保持工作处于均衡状态。装载的速度与最慢的节点相同。装载文件布局上的倾斜将导致 整体装载受制于资源瓶颈。

gp_external_max_segs配置参数控制每个gpfdist进程能服务的 segment数量。默认值是64。使用者可以在saster上的postgresql.conf配置文件中设置 一个不同的值。总是保持gp_external_max_segs和gpfdist进程的 数量为一个偶因子，也就是说gp_external_max_segs值应该是gpfdist 进程数的倍数。例如，如果有12个segment和4个gpfdist进程，规划器会按照下面的方式 循环分配segment连接：

  1. Segment 1  - gpfdist 1 
  2. Segment 2  - gpfdist 2 
  3. Segment 3  - gpfdist 3 
  4. Segment 4  - gpfdist 4 
  5. Segment 5  - gpfdist 1 
  6. Segment 6  - gpfdist 2 
  7. Segment 7  - gpfdist 3 
  8. Segment 8  - gpfdist 4 
  9. Segment 9  - gpfdist 1 
  10. Segment 10 - gpfdist 2 
  11. Segment 11 - gpfdist 3 
  12. 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系统目录中为 外部表设置粗略的行数和磁盘页数估计：

    1. UPDATE pg_class SET reltuples=400000, relpages=400
    2. 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倾斜。用户可以使用下面这样的查询来检查倾斜：

    1. SELECT gp_segment_id, count(*) 
    2. FROM schema.table 
    3. 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和gpload装载数据的详细指导步骤请见 Greenplum数据库参考指南。