Greenplum中装载和卸载数据

装载和卸载数据

GP装载概述

关于外部表

1) 外部表允许用户像访问标准数据库表一样访问外部表
2) 结合GP的并行文件分配程序(gpfdist)，外部表支持在装载和卸载数据时全并行化利用所有segment实例的资源
3) GP还可以利用Hadoop分布式文件系统的并行架构来访问文件
4) GP提供了两种类型的外部表：
可读外部表：用于数据装载，不允许对数据进行修改
可写外部表：用于数据卸载，从数据库表中选择记录并输出到文件、命令管道或其他的可执行程序，包括并行MapReduce计算。只需允许INSERT 操作。
5) 按数据源不同，分为两种可读外部表：
常规的：访问静态的平面文件
WEB：访问动态数据源（比如wen服务或者OS的命令或脚本）

关于gpload

1) gpload是一个数据并行装载命令
2) 需要创建一个按照YAML格式定义的装载说明控制文件

关于copy

1) 标准PostgreSQL装载和卸载命令
2) 不具有并行装载/卸载的机制

定义外部表

概述

在创建外部表定义时，必须指定文件格式和文件位置；三种用来访问外部表数据源的协议：gpfdist, gpfdists和gphdfs。

gpfdist

1) 在外部表指定文件的所有主机上运行GP文件分发程序(gpfdist)
2) 该程序指向一个给定的目录，并行的为所有segment实例提供外部数据文件服务
3) 如果文件使用了gzip或者bzip2压缩，gpfdist会自动解压
4) 可以使用多个gpfdist来提升外部表的扫描性能
5) 可使用通配符或者C风格的模式匹配多个文件

gpfdists

1) gpfdists是gpfdist的安全版本，其开启的加密通信并确保文件与GP之间的安全认证

file

1) 如果使用file://协议，则外部文件必须存放在segment主机上面
2) 指定的符合segment实例数量的的URIs将并行工作来访问外部表
3) 每个segment主机外部文件的数量不能超过segment实例数量
4) pg_max_external_files用来确定每个外部表中允许多少个外部文件

gphdfs

1) 该协议指定了一个可以在HDFS上包含通配符的路径。
2) 在GP链接到HDFS文件时，所有数据将从HDFS数据节点被并行读取到GP的Segment实例以快速处理。
3) 每个GP Segment实例只读取一组Hadoop数据块。
4) 对于写来说，每个GP Segment实例值写该实例包含的数据

外部文件格式

1) TEXT类型对所有协议有效
2) 逗号分割的CSV对于gpfdist和file协议有效
3) 自定义格式适用于gphdfs

外部表中的错误数据

为了在装载正确格式的记录时隔离错误数据，需在定义外部表时使用单条记录出错处理

外部表备份恢复

在备份或者恢复操作中，仅仅外部表或者WEB外部表的定义会被备份或恢复。

使用GP并行文件服务(gpfdist)

为最大化系统带宽而运行gpfdist要考虑的因素：
1) 关于gpfdist的设置与性能
如果ETL主机配置了多个网口，应将所有网口对应的主机名在LOCATION子句中声明
2) 在ETL主机上运行多个gpfdist并将外部数据均匀的分拆到各gpfdist服务。
3) 控制节点并行度
gp_external_max_segs参数设置最大多少Segment实例访问同一个gpfdist文件分发程序
4) 启动和停止gpfdist
a) 要启动gpfdist，必须指定其提供文件服务的目录以及运行的端口(缺省为HTTP端口8080)
b) 在后台启动gpfdist(日志信息和出错信息输出到日志文件)

 $ gpfdist –d /var/load_files –p 8081 –l /home/gpadmin/log &

c) 要在同一个ETL主机启动多个gpfdist服务，为每个服务指定不同的目录和端口。例如，

    $ gpfdist –d /var/load_files1 –p 8081 –l /home/gpadmin/log1 &
    $ gpfdist –d /var/load_files2 –p 8082 –l /home/gpadmin/log2 &

d) 停止后台的gpfdist服务：
第一步找到进程号，

$ ps –ef|grep gpfdist

第二步杀掉该进程

$ select pg_cancel_backend(1234);

5) gpfdist故障诊断
a) 确保GP Segment可以访问gpfdist的网络
b) 使用wget命令来测试GP集群的连接性

    $ wget  http://gpfdist_hostname:port/filename

c) 需要确保CREATE EXTERNAL TABLE定义了hostname, port以及gpfdist的文件名
6) 创建使用gpfdist协议的外部表
a) 创建单文件服务的可读外部表，文件格式为竖线(|)分割：

  CREATE EXTERNAL TABLE tb_ext_gf01(id int, name text)
    LOCATION (‘gpfdist://mdw:8081/*.txt’)
    FORMAT ‘TEXT’ (DELIMITER ‘|’ NULL ‘’);

b) 创建多文件服务的可读外部表，文件格式为竖线(|)分割：

  CREATE EXTERNAL TABLE tb_ext_gf02(id int, name text)
    LOCATION ('gpfdist://mdw:8081/*.txt','gpfdist://mdw:8082/*.txt')
    FORMAT 'TEXT' (DELIMITER '|' NULL '');

c) 带有错误数据日志的多文件服务

   CREATE EXTERNAL TABLE tb_ext_gf03 ( id int, name text, amount float4)
    LOCATION ('gpfdist://mdw:8081/*.csv','gpfdist://mdw:8082/*.csv')
    FORMAT 'CSV' ( DELIMITER ',' )
    LOG ERRORS INTO err_customer SEGMENT REJECT LIMIT 2;

d) 创建可写外部表

 CREATE WRITABLE EXTERNAL TABLE tb_ext_gf04 (LIKE tb_cp_02)
    LOCATION ('gpfdist://mdw:8081/tb_cp_02.out')
    FORMAT 'TEXT' ( DELIMITER '|' NULL ' ')
    DISTRIBUTED BY (id);
    insert into tb_ext_gf04 select * from tb_cp_02;

7) 使用SELECT FROM来访问外部表
装载全部数据到一个新的数据表

CREATE TABLE tb_load_01 AS SELECT * FROM tb_ext_gf01;

使用Hadoop分布式文件系统表

1) GP使用gphdfs协议支持并行架构Hadoop分布式文件系统的读写。
2) 使用HDFS的三个步骤
a) 安装设置
b) HDFS协议授权
c) 在外部表定义中指定HDFS数据

创建和使用WEB外部表

1) 查询优化器不允许重复扫描WEB表的数据
2) 使用CREATE EXTERNAL WEB TABLE创建GP的WEB表
3) WEB外部表的定义有两种形式
a) WEB URL
使用http://协议指定WEB服务器上文件的LOCATION；
该WEB数据文件必须在GP Segment可以访问的WEB服务上；
URL的数量将对应并行访问WEB表的Segment实例
b) OS命令
在一个或数个Segment上指定执行SHELL命令或者脚本，输出结果作为WEB表访问的数据；
使用EXECUTE子句定义的外部表，将在指定的数个Segment主机上执行指定的SHELL命令或脚本；
注意限制执行WEB表命令的Segment实例数量
4) 定义OS命令型WEB外部表
a) 命令或程序必须放置到所有Segment主机上
b) 命令是从数据库执行而不是从登录的SHELL
例如：

 CREATE EXTERNAL WEB TABLE tb_ext_wb01(output text)
    EXECUTE 'hostname' 
    FORMAT 'TEXT';

5) 定义URL型WEB外部表

 CREATE EXTERNAL WEB TABLE tb_ext_wb02 (name text,
    date date, amount float4, category text, description text)
    LOCATION (
    'http://intranet.company.com/expenses/marketing/file.csv',
    'http://intranet.company.com/expenses/eng/file.csv‘
    )
    FORMAT 'CSV' ( HEADER );

装载和卸载数据

在GP中有两种自定义方案可用于装载和卸载数据

使用自定义格式

1) 自定义格式用于导入导出TEXT和CSV两种格式之外的数据；
使用自定义格式有如下3个步骤：
a) 编写输入输出函数并编译到共享库中
b) 在GP中通过CREATE FUNCTION指定共享库函数
c) 将这些函数与CREATE EXTERNAL TABLE的formatter子句关联
d) 对于固定宽度数据来说，前两步在GP中已经完成。函数名分别为fixedwidth_in和fixedwidth_out
2) 导入导出固定宽度数据
在GP中可直接使用固定宽度数据的函数，需要指定自定义的格式和在formatter参数中指定函数名称。例如，

  CREATE READABLE EXTERNAL TABLE tb_ext_cs01 (id int, name text)
    LOCATION ('gpfdist://mdw:8081/a_cs.txt')
    FORMAT 'CUSTOM' (formatter=fixedwidth_in, id='2',name='3');

3) 其他选项
a) 设置空白和控制字符
要保留补尾的空白，使用preserve_blanks=on选项；
使用null=‘null_string_value’选项指定控制字符
b) 指定行的结尾
使用参数line_delim=‘line_ending’指定行的结尾字符；
下面列举的可以覆盖大部分场景：

line_delim=E’\n’
    line_delim=E’\r’
    line_delim=E’\r\n’
    line_delim=‘’ (没有分隔行)

例如，

 CREATE READABLE EXTERNAL TABLE tb_ext_cs02 (id int, name text)
    LOCATION ('gpfdist://mdw:8081/b_cs.txt')
    FORMAT 'CUSTOM' (formatter=fixedwidth_in,
    id='2',name='3', preserve_blanks='on',null='NUL');

4) 使用自定义协议
如果现有的协议(gpfdist、http、file等)不能够很好的用于访问数据，可以编写自定义的协议。

使用gpload装载数据

1) gpload是GP使用可读外部表和GP并行文件服务gpfdist装载数据的一个命令包装。
2) 使用gpload
a) 创建YAML格式装载控制文件
该文件指定了GP的连接信息，gpfdist配置信息，外部表选项以及数据格式。例如，

    VERSION: 1.0.0.1
    DATABASE: testdw
    USER: gpadmin
    HOST: mdw
    PORT: 5432
    GPLOAD:
     INPUT:
      - SOURCE:
     LOCAL_HOSTNAME:
    - mdw
       PORT: 8081
    FILE:
    - /data/load_files/a.txt
      - COLUMNS:
    - id: int
    - name: text
      - FORMAT: text
      - DELIMITER: '|'
      - ERROR_LIMIT: 1
      - ERROR_TABLE: err_a
     OUTPUT:
      - TABLE: tb_gf01
      - MODE: INSERT
     SQL:
      - BEFORE: "INSERT INTO audit VALUES('start', current_timestamp)"
      - AFTER: "INSERT INTO audit VALUES('end', current_timestamp)"

3) 通过装载控制文件运行gpload命令，例如：

    gpload –f my_load.yml

使用COPY装载数据

1) 使用COPY FROM把数据从文件追加拷贝到表中
2) Master必须可以访问到该文件
3) 可考虑每个CPU执行一个并发的COPY来提高性能
4) 使用单条记录错误隔离模式运行COPY（跟定义外部表一样），例如：

    CREATE TABLE tb_ext_cp01 (LIKE tb_ext_gf01);
    COPY tb_ext_cp01 FROM '/data/load_files/a.txt‘
    WITH DELIMITER '|' LOG ERRORS INTO err_tb_ext_cp01
    SEGMENT REJECT LIMIT 2 ROWS;

数据装载性能技巧

1) 在装载前删除索引：在已存在的数据上创建索引比不断的递增索引要快
2) 在装载之后运行ANALYZE：执行ANALYZE确保查询计划拥有最新的统计信息
3) 在装载出错后执行VACUUM：错误发生前的记录无法访问，但仍然占据磁盘空间

从GP中卸载数据

1) 两种类型：并行(可写外部表)和非并行(COPY)
2) 两种方式的可写外部表：基于文件和基于WEB
3) 定义基于文件的可写外部表
a) 使用CREATE WRITABLE EXTERNAL TABLE命令定义外部表并指出输出文件的位置和格式
b) 使用gpfdist协议的可写外部表
GP Segment将数据发送给gpfdist进程，该进程将数据写到指定名称的文件；
若希望输出的数据分割到多个文件，可以在外部表的定义中指定多个gpfdist的URL选项
例如，

  CREATE WRITABLE EXTERNAL TABLE tb_wext_gf01(LIKE tb_cp_02)
    LOCATION ('gpfdist://mdw:8081/tb_wext_gf01.out',
    'gpfdist://mdw:8082/tb_wext_gf02.out')
    FORMAT 'TEXT' (DELIMITER ',')
    DISTRIBUTED RANDOMLY;

4) 定义基于命令的可写外部表
a) 使用CREATE WRITABLE EXTERNAL WEB TABLE命令定义外部表并指定可执行命令或程序
b) 对于可写WEB表，EXECUTE子句指定的命令或脚本准备着接受数据输入流；
c) 可写外部表有分布策略选项，缺省为随机分布；
d) 若使用HASH分布策略，在可写外部表中定义相同的分布键可以改善卸载的性能。
e) 在外部表定义的EXECUTE子句中，可根据需要设置环境变量。例如，

    CREATE WRITABLE EXTERNAL WEB TABLE tb_wext_wb01 (output text)
    EXECUTE 'export PATH=$PATH:/home/gpadmin;myprogram.sh‘
    FORMAT 'TEXT‘
    DISTRIBUTED RANDOMLY;

f) WEB外部表和可写外部表的可执行性
外部表执行OS命令或者脚本有一定的风险，根据需要，可以禁止在WEB表定义中使用EXECUTE。在Master的postgresql.conf文件设置：

 gp_external_enable_exec = off

5) 使用COPY卸载数据
在GP Master上使用COPY TO语句从数据库表串行拷贝数据到文件
例如：

 COPY (SELECT * FROM tb_cp_02 WHERE date LIKE '2013%') TO '/data/unload/tb_cp_02_2013.out';

6) 可读外部表的统计信息
a) 无法通过ANALYZE获取，可以通过手工修改数据字典pg_class来设置粗略统计值
b) 指定行数和数据库页面数(数据尺寸/32K)
c) 默认行数为1000000，页面数为1000
手工修改：

  update pg_class set reltuples=500000, relpages=150 where relname='tb_wext_gf01';