Greeplum 系列（四） 数据的装载与卸裁

装载数据有以下种方法：

1. insert
2. copy
3. 外部表
4. gpload

下面以 member_delta 表为例分别介绍这四种方法。

create table public.member_delta ( 
    member_id varchar(64), 
    phoneno varchar(64), 
    action char(1), 
    dw_ins_date date 
) with (appendonly=true, compresslevel=5) 
distributed by(member_id);

一、insert

insert into public.member_delta values('mem006', '13100000006', 'I', date'2011-12-03'); 
insert into public.member_delta values('mem002', '13100000002', 'D', date'2011-12-03'); 
insert into public.member_delta values('mem003', '13100000003', 'U', date'2011-12-03');

二、copy

-- 导入数据 
copy public.member_delta from '/home/gpadmin/member_his_init.dat' with delimiter ',' 
LOG ERRORS INTO err_member_delta SEGMENT REJECT LIMIT 2 ROWS; 
 
-- 导出数据 
COPY (SELECT * FROM member_delta WHERE date LIKE '2013%') TO '/data/unload/tb_cp_02_2013.out';

member_his_init.dat 文件如下：

mem001,13100000001,2011-12-01,3000-12-31,C,I,2011-12-01 
mem002,13100000002,2011-12-01,3000-12-31,C,I,2011-12-01 
mem003,13100000003,2011-12-01,3000-12-31,C,I,2011-12-01 
mem004,13100000004,2011-12-01,3000-12-31,C,I,2011-12-01 
mem005,13100000005,2011-12-01,3000-12-31,C,I,2011-12-01

三、外部表(××××)

外部表允许用户像访问标准数据库表一样访问外部表。结合 GP 的并行文件分配程序(gpfdist)，外部表支持在装载和卸载数据时全并行化利用所有 segment 实例的资源。GP还可以利用 Hadoop 分布式文件系统的并行架构来访问文件。

GP 提供了两种类型的外部表：

1. 可读外部表： 用于数据装载，不允许对数据进行修改

   按数据源不同，分为两种可读外部表。一是常规的：访问静态的平面文件；二是 WEB：访问动态数据源（比如web服务或者OS的命令或脚本）

2. 可写外部表： 用于数据卸载，从数据库表中选择记录并输出到文件、命令管道或其他的可执行程序，包括并行 MapReduce 计算。只需允许 INSERT 操作。

3.1 三种协议

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

1. gpfdist

   如果文件使用了 gzip 或者 bzip2 压缩，gpfdist 会自动解压，也可使用通配符匹配多个文件。

2. gpfdists

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

3. file

   如果使用 file:// 协议，则外部文件必须存放在 segment 主机上面。pg_max_external_files 用来确定每个外部表中允许多少个外部文件。

4. gphdfs

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

3.2 gpfdist 使用

(1) 启动 gpfdist

# nohup 后台启动，关闭当前会话，进程仍运行；& 后台启动，关闭当前会话，进程关闭 
# -d 数据目录； -l 日志目录 
nohup gpfdist –d /var/load_files –p 8081 –l /home/gpadmin/log & 
# 查看 gpfdist 进程 
ps -ef | grep gpfdist 
# 用 wget 诊断 gpfdist 是否正常工作 
wget  http://gpfdist_hostname:port/filename

(2) 创建外部表，用于写入数据

CREATE EXTERNAL TABLE tb_ext_gf01(id int, name text) 
LOCATION ( 
    'gpfdist://mdw:8081/*.txt', 
    'gpfdist://mdw:8082/*.txt' 
) 
FORMAT 'TEXT' (DELIMITER '|' NULL '') 
LOG ERRORS INTO err_customer SEGMENT REJECT LIMIT 2;

(3) 创建可写外部表，用于导出数据

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;

(4) 定义 OS 命令型 WEB 外部表

drop external table if exists public.tb_ext_wb01; 
CREATE EXTERNAL WEB TABLE tb_ext_wb01(output text) 
EXECUTE 'hostname'  
-- execute 'cat /home/gpadmin/data/member_delta.dat' on master 
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);

四、gpload

gpload 是对外部表的一层封装，首先编写 gpload 控制文件 gpload.yml，代码如下：

---   
VERSION: 1.0.0.1   
DATABASE: test   
USER: gpadmin   
HOST: localhost  
PORT: 5432   
GPLOAD:   
  INPUT:   
    - SOURCE:   
        LOCAL_HOSTNAME:   
          - master   
        PORT: 8888   
        FILE:   
          - /home/gpadmin/data/member_delta.dat 
    - COLUMNS:   
        - member_id: varchar(64), 
        - phoneno: varchar(20), 
        - action: char(1), 
        - dw_ins_date: date 
    - FORMAT: text   
    - DELIMITER: ','   
    - ERROR_LIMIT: 2 
    - ERROR_TABLE: public.member_err  
  OUTPUT:   
    - TABLE: public.member_delta   
    - MODE: INSERT   
  SQL: 
    - BEFORE: "truncate table public.member_delta" 
    - AFTER: "analyze public.member_delta"

执行 gpload 命令，结果如下：

gpload -f gpload.yml

2018-05-18 20:53:10|INFO|gpload session started 2018-05-18 20:53:10 
2018-05-18 20:53:10|INFO|started gpfdist -p 8888 -P 8889 -f "/home/gpadmin/data/member_delta.dat" -t 30 
2018-05-18 20:53:11|INFO|running time: 0.85 seconds 
2018-05-18 20:53:11|INFO|rows Inserted          = 1 
2018-05-18 20:53:11|INFO|rows Updated           = 0 
2018-05-18 20:53:11|INFO|data formatting errors = 0 
2018-05-18 20:53:11|INFO|gpload succeeded

---

每天用心记录一点点。内容也许不重要，但习惯很重要！