1.外部表
- 外部表允许用户像访问标准数据库表一样访问外部表
- 结合GP的并行文件分配程序(gpfdist)，外部表支持在装载和卸载数据时全并行化利用所有segment实例的资源
- GP还可以利用Hadoop分布式文件系统的并行架构来访问文件
GP提供了两种类型的外部表：
- 可读外部表：用于数据装载，不允许对数据进行修改
- 可写外部表：用于数据卸载，从数据库表中选择记录并输出到文件、命令管道或其他的可执行程序，包括并行MapReduce计算。只需允许INSERT 操作。
按数据源不同，分为两种可读外部表：
- 常规的：访问静态的平面文件
- WEB：访问动态数据源（比如wen服务或者OS的命令或脚本）
2.外部表数据源的协议
在创建外部表定义时，必须指定文件格式和文件位置 三种用来访问外部表数据源的协议：gpfdist, gpfdists和gphdfs
2.1.gpfdist
在外部表指定文件的所有主机上运行GP文件分发程序（gpfdist）。该程序指向一个给定的目录，并行的为所有segment实例提供外部数据文件服务。如果文件使用了gzip或者bzip2压缩，gpfdist会自动解压。可以使用多个gpfdist来提升外部表的扫描性能。可以使用通配符或者C风格的模式匹配多个文件。
外部表提供了对Greenplum数据库之外的来源中数据的访问。可以用SELECT语句访问它们，外部表通常被用于抽取、装载、转换（ELT）模式，这是一种抽取、转换、装载（ETL）模式的变种，这种模式可以利用Greenplum数据库的快速并行数据装载能力。这是COPY命令不持有的。
gpfdist原理： gpfdist是一个使用HTTP协议的文件服务器程序，它以并行的方式向Greenplum数据库的Segment供应外部数据文件一个gpfdist实例，每秒能供应200MB并且很多gpfdist进程可以同时运行，每一个供应要被装载的数据的一部分。当使用者用 “INSERT INTO <table> SELECT * FROM <external_table>;” 这样的语句开始装载时，INSERT语句会被Master解析并且分布给主Segment。Segment连接到gpfdist服务器并且并行检索数据，解析并验证数据，从分布键数据计算一个哈希值并且基于哈希键把行发送给它的目标Segment。每个gpfdist实例默认将接受最多64个来自Segment的连接。通过让许多Segment和gpfdist服务器参与到装载处理中，可以以非常高的速率被装载。
使用GP并行文件服务(gpfdist)
为最大化系统带宽而运行gpfdist要考虑的因素：
- 实际应用中，一般会把gpfdist部署在ETL文件服务器上，在这个服务器上启动一个或者多个gpfdist。
- 一般制定文件数据的父目录，因为大部分是很多数据文件使用同一个gpfdist，如果路径写的细的话，就不能使用同一个gpfdist。比如在开启gpfdist进程时只指定文件根目录，然后定义外部表时就指定子目录。
- gpfdist进程不是越多越好，主要取决于网络带宽。
如果ETL主机配置了多个网口，应将所有网口对应的主机名在LOCATION子句中声明
在ETL主机上运行多个gpfdist并将外部数据均匀的分拆到各gpfdist服务。
控制节点并行度：
gp_external_max_segs 参数设置最大多少segment实例访问同一个gpfdist文件分发程序。
启动和停止gpfdist
要启动gpfdist，必须指定其提供文件服务的目录以及运行的端口（缺省为HTTP端口8080） 在后台启动gpfdist（日志信息和出错信息输出到日志文件）
代码语言：javascript复制$gpfdist -d /var/load_files -p 8081 -l /home/gpadmin/log &
要在同一个ETL主机启动多个gpfdist服务，为每个服务指定不同的目录和端口。例如：
代码语言：javascript复制$gpfdist -d /var/load_files1 -p 8081 -l /home/gpadmin/log1 &
$gpfdist -d /var/load_files2 -p 8082 -l /home/gpadmin/log2 &
停止后台gpfdist服务：
代码语言：javascript复制# 第一步找到进程号，
$ps -ef|grep gpfdist
# 第二步杀掉进程，kill -9 XXX
select pg_cancel_backend(1234);
gpfdist 故障诊断：
确保 GP segment可以访问gpfdist的网络 ，使用wget命令来测试GP集群的连接性
代码语言：javascript复制$ wget http://gpfdist_hostname:port/filename
需要确保 CREATE EXTERNAL TABLE 定义了hostname，port以及gpfdist的文件名
创建外部表并加载数据
step 1.创建表
代码语言：javascript复制test=# create table test_gpfdist(id int primary key,order_num varchar(100),accountid varchar(30),qn varchar(20),appid int,amount numeric(10,2),pay_time timestamp);
CREATE TABLE
test=#
step 2.创建数据库
代码语言：javascript复制cat > test_gpfdist.txt <<EOF
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
EOF
step 3.在Master启动 gpfdist gpfdist工具可以实验并行加载，需要先启动gpfdist进程及监听端口，这个命令在Master和Segment节点的GPHOME/bin目录下，如果配置了GP的环境变量，可以直接使用，如果在没有安装GP的服务器上使用gpfdist工具，只需要将gpfdist命令的文件拷贝到相应的服务器上即可使用。
代码语言：javascript复制nohup gpfdist -d /home/gpadmin -p 1234 -l /home/gpadmin/gpfdist.log &
启动gpfdist进程，扫描路径为 /home/gpadmin，监听端口为1234
step 4.创建一张基于gpfdist工具的外部表。
代码语言：javascript复制test=# create external table ext_test_gpfdist
test-# (id int,order_num varchar(100),accountid varchar(30),qn varchar(20),appid int,amount numeric(10,2),pay_time timestamp)
test-# location('gpfdist://gp-mdw:1234/test_gpfdist.txt')
test-# format 'TEXT' (DELIMITER as ',' null as ''ESCAPE as 'OFF')
test-# encoding 'utf8' LOG ERRORS SEGMENT REJECT LIMIT 100;
CREATE EXTERNAL TABLE
test=#
step 5.加载数据
代码语言：javascript复制test=# insert into test_gpfdist select * from ext_test_gpfdist;
INSERT 0 10
test=#
ANALYZE表
代码语言：javascript复制test=# analyze test_gpfdist;
ANALYZE
test=#
step 6.加载完成之后需要停到gpfdist进程
代码语言：javascript复制[gpadmin@gp-mdw ~]$ ps -ef|grep gpfdist|grep -v "grep"
gpadmin 14881 14705 0 17:00 pts/1 00:00:00 gpfdist -d /home/gpadmin -p 1234 -l /home/gpadmin/gpfdist.log
停掉gpfdist进程
代码语言：javascript复制[gpadmin@gp-mdw ~]$ kill -9 14881
step 7.删除外部表
代码语言：javascript复制test=# drop external table ext_test_gpfdist;
DROP EXTERNAL TABLE
test=#
GP数据库查询数据，先扫描到的数据会直接返回，也就是多次查询的结果可能是不一样的，使用gpfdist工具加载，而gpfdist工具加载数据是并行加载的，最先插入到数据库的数据并不一定是从第一条数据开始的。
step 8.查看数据是否倾斜
代码语言：javascript复制test=# select gp_segment_id,count(*) from test_gpfdist group by 1;
gp_segment_id | count
--------------- -------
5 | 1
0 | 2
1 | 3
16 | 1
7 | 2
6 | 1
(6 rows)
test=#
2.2.gpfdists
gpfdists是gpfdist的安全版本，其开启的加密通信并确保文件与GP之间的安全认证。
2.3.file（适用内部数据）
如果使用file:// 协议，则外部文件必须存放在segment主机上面。
指定的符合segment实例数量的URLs将并行工作来访问外部表
每个segment主机外部文件的数量不能超过segment实例数量。
pg_max_external_files用来确定每个外部表中允许多少个外部文件。
代码语言：javascript复制select * from pg_max_external_files
2.4.gphdfs
该协议指定一个可以在HDFS上包含通配符的路径。 在GP链接到HDFS文件时，所有数据将从HDFS数据节点被并行读取到GP的segment实例以快速处理。 每个GPsegment实例值读取一组Hadoop数据块 对于写来说，每个GP segment实例值写giant实例包含的数据
使用HDFS的三个步骤
- 安装设置
- HDFS协议授权
- 在外部表定义中指定HDFS数据
3.外部表定义
在建立外部表的时候，能够指定分隔符、err表、指定容许出错的数据条数，以及源文件的编码等信息。必须指定文件格式和文件位置。
- 外部文件格式：
- TEXT类型对所有协议有效。
- 逗号分隔的CSV对于gpfdist和file协议有效
- 自定义格式适合于gphdfs
- 外部表中的错误数据： 为了在装载正确格式的记录时隔离错误数据，需要在定义外部表时使用单条记录出错处理
- 外部表备份恢复： 在备份或者恢复操作中，仅仅外部表或者WEB外部表的定义会备份或恢复。
创建外部表
参数：
- location 文件所在位置，能够直接是本地路径、gpfdist地址、gpfdists地址、gphdfs地址。
- format 文本类型
- delimiter 分隔符
- encoding 编码
- log error into 错误数据表，记录错误数据，会自动建立。通常都是tablename_err格式，例如t1_err。
- segment reject limit 错误数据的条数/百分比(rows/percent)，超过设置值会报错。最小值是2。用来确保数据的完整性。
常规外部表
创建单文件服务的可读外部表，文件格式为竖线（|）分割：
代码语言：javascript复制CREATE EXTERNAL TABLE tb_ext_gf01 (id int,name text)
LOCATION('gpfdist://mdw:8081/*.txt')
FORMAT 'TEXT' (DELIMITER '|' NULL '');
创建多文件服务的可读外部表，文件格式为竖线（|）分割：
代码语言：javascript复制CREATE EXTERNAL TABLE tb_ext_gf02 (id int,name text)
LOCATION('gpfdist://mdw:8081/*.txt','gpfdist://mdw:8082/*.txt')
FORMAT 'TEXT' (DELIMITER '|' NULL '');
带有错误数据日志的多文件服务：
代码语言：javascript复制CREATE EXTERNAL TABLE tb_ext_gf03 (id int,name text)
LOCATION('gpfdist://mdw:8081/*.txt','gpfdist://mdw:8082/*.txt')
FORMAT 'CSV' (DELIMITER ',' )
LOG ERRORS INTO err_customer SEGMENT REJECT LIMIT 2;
创建可写外部表：（用于数据导出）
代码语言：javascript复制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 ;
查看错误日志：
代码语言：javascript复制select * from err_customer
使用select from 来访问外部表, 装载全部数据到一个新的数据表
代码语言：javascript复制CREATE TABLE tb_load_01 AS SELECT * FROM tb_ext_gp01；
WEB外部表
查询优化器不允许重复扫描WEB表的数据。
WEB外部表的定义有两种形式
WEB URL 使用 http:// 协议指定WEB服务器上文件的LOCATION; 该WEB数据文件必须在GP segment可以访问的WEB服务上；URL的数量将对应并行访问WEB表的segment实例
代码语言：javascript复制CREATE EXTERNAL WEB TABLE tb_ext_wb02 (name text,date date,amount float4,category text,description text )
LOCATION(
'http://intranet.company.com/expenses/markering/file.csv',
'http://intranet.company.com/expenses/eng/file.csv'
)
FORMAT 'CSV' (HEADER);
OS命令 在一个或者多个segment上指定执行SHELL命令或者脚本，输出结果作为WEB表访问的数据； 使用EXECUTE子句定义的外部表，将在指定的多个segment主机上执行指定的SHELL命令或者脚本； 注意限制执行WEB表命令的segment实例数量。 命令或程序必须放置到所有segment主机上，从数据库执行而不是从登陆的SHELL
代码语言：javascript复制CREATE EXTERNAL WEB TABLE tb_ext_wb01 (output text)
EXECUTE 'hostname'
FORMAT 'TEXT';
4.装载数据到GP
4.1.copy
标准PostgreSQL装载和卸载命令 ; 不具有并行装载/卸载的机制。
对于数据加载,GreenPlum数据库提供copy工具，copy工具源于PostgreSQL数据库，copy命令支持文件与表之间的数据加载和表对文件的数据卸载。使用copy命令进行数据加载，数据需要经过Master节点分发到Segment节点，同样使用copy命令进行数据卸载，数据也需要由Segment发送到Master节点，由Master节点汇总后再写入外部文件，这样就限制了数据加载与卸载的效率，但是数据量较小的情况下，copy命令就非常方便。
- 使用COPY FROM把数据从文件追加拷贝到表中
- Master必须可以访问到该文件
- 可考虑每个CPU执行一个并发的COPY来提高性能
- 使用单条记录错误隔离模式运行COPY（跟定义外部表一样）
step 1.创建表
代码语言：javascript复制test=# create table test_copy (id int primary key,order_num varchar(100),accountid varchar(30),qn varchar(20),appid int,amount numeric(10,2),pay_time timestamp);
CREATE TABLE
test=# d test_copy
Table "public.test_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:
"test_copy_pkey" PRIMARY KEY, btree (id)
Distributed by: (id)
test=#
step 2.测试数据
代码语言：javascript复制cat > ios_pay.txt <<EOF
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
EOF
现在文本“ios_pay.txt”有 10 行记录；大小约2M。
代码语言：javascript复制[gpadmin@gp-mdw ~]$ wc -l test_copy.txt
1474560 test_copy.txt
[gpadmin@gp-mdw ~]$ du -sh test_copy.txt
154M test_copy.txt
[gpadmin@gp-mdw ~]$
step 3.加载数据 使用COPY命令进行外部文件加载数据，必须拥有superuser权限的用户
代码语言：javascript复制test=# du
List of roles
Role name | Attributes | Member of
----------- ---------------------------------------------------------------------------------------------------------- -----------
gpadmin | Superuser, Create role, Create DB, Ext gpfdist Table, Wri Ext gpfdist Table, Ext http Table, Replication | {}
test=#
执行命令,使用默认参数如下:
代码语言：javascript复制test=# copy test_copy from '/home/gpadmin/test_copy.txt' with delimiter ',' null '' ;
ERROR: duplicate key value violates unique constraint "test_copy_pkey"
DETAIL: Key (id)=(75) already exists.
CONTEXT: COPY test_copy, line 5
test=#
默认情况下，COPY会在第一个错误处停止操作，指定SEGMENT REJECT LIMIT 会把COPY操作运行在单行错误隔离模式中。如果COPY操作没有达到操作限制，Greenplum会装载所有正确格式化的行并且丢弃错误行。使用LOG ERRORS子句可以捕获Greenplum数据库内部的数据格式化错误。
如何跳过错误行?
- LOG ERRORS INTO 参数指定错误数据记录到哪张表中
- SEGMENT REJECT LIMIT 参数指定最大跳过的错误数
常用参数
- 分隔符：[DELIMITER [ AS ] ‘delimiter’]
- 处理空列（含有空格符的是不行的）：[NULL [ AS ] ‘null string’]
- 记录错误数据，错误日志表自动创建：[LOG ERRORS INTO error_table] [KEEP]
- 允许错误的行数或者百分比，大于指定值导入失败全部回滚：SEGMENT REJECT LIMIT count [ROWS | PERCENT] ]
test=# copy test_copy from '/home/gpadmin/test_copy.txt' with delimiter ',' NULL '' LOG ERRORS SEGMENT REJECT LIMIT 20;
COPY 10
test=#
可通过执行 “SELECT gp_read_error_log(‘test_copy’)” 来查看错误行的信息，其中 bsscost为目标表。
step 4.卸载数据 Copy工具不仅可以把数据从文件加载到数据库的表中，也可以将数据从数据库的表中卸载到操作系统的文件中，使用 copy to语句可实现数据的导出
–header指定导出表头，若不需要可把header去掉，copy 后可直接跟要导出的表，也可写sql语句
代码语言：javascript复制copy (select * from bsscost limit 100 ) to '/home/yaml/output.txt' with csv header delimiter AS ',';
copy from 将文件的数据复制到表中， copy是非并行的。 使用Greenplum master实例在单个进程中加载数据。建议仅对非常小的数据文件使用copy 。
4.2.gpload
gpload是GP使用可读外部表和GP并行文件服务gpfdist装载数据的一个命令包装。
gpload是一种数据装载工具，它扮演着Greenplum外部表并行装载特性的接口的角色。gpload使用定义在一个YAML格式的控制文件中的规范来执行一次装载。
它会执行下列操作：
- 调用gpfdist进程
- 基于定义的源数据创建一个临时的外部表定义
- 执行INSERT、UPDATE或者MERGE操作将源数据载入数据库中的目标表
- 删除临时外部表
- 清除gpfdist进程
step 1.创建表
代码语言：javascript复制test=# create table test_gpload (id int primary key,order_num varchar(100),accountid varchar(30),qn varchar(20),appid int,amount numeric(10,2),pay_time timestamp);
CREATE TABLE
test=#
step 2.创建数据库
代码语言：javascript复制cat > test_gpload.txt <<EOF
63,ysios_receipt_3793615cb10dc393bba87c82d3c6544f,27388062,yriu1244_16043_001,2616,98.00,2017-11-06 17:36:43
62,ysios_receipt_32946f3d37e774781babe103352bd230,27424976,yriu1244_16043_001,2616,30.00,2017-11-06 15:18:56
65,ysios_receipt_3e2e432550253450412692392c7675d0,27388294,yriu1244_16043_001,2616,98.00,2017-10-19 07:33:03
64,ysios_receipt_3793615cb10dc393bba87c82d3c6544f,27388062,yriu1244_16043_001,2616,98.00,2017-11-06 20:40:46
67,ysios_receipt_ee6bed338a32f836a999133cd2e6d547,27388294,yriu1244_16043_001,2616,98.00,2017-10-19 22:27:46
66,ysios_receipt_ae53b142924c0604820537d61a9dd73e,27424976,yriu1244_16043_001,2616,648.00,2017-10-19 12:10:17
69,ysios_receipt_30ec130bcdf0e864629d12f8392d4b43,27385229,yriu1244_16043_001,2616,98.00,2017-10-21 07:46:01
68,ysios_receipt_e2b62024f1b0c3a2c3aae1e80f126eb6,27387306,yriu1244_16043_001,2616,25.00,2017-10-20 01:54:24
61,ysios_receipt_3e72a8e32c9fee546ab08d103606e6cb,27424976,yriu1244_16043_001,2616,30.00,2017-10-21 13:55:54
50,ysios_receipt_6ca291884fcfe3d1583b49a3611b4ccc,27424976,yriu1244_16043_001,2616,25.00,2017-10-21 13:55:51
EOF
step 3.创建YAML格式装载控制文件 使用gpload工具，需要编写gpload工具的控制文件，这个控制文件是使用yuml语言编写。该文件指定了GP的连接信息，gpfdist配置信息，外部表选项以及数据格式。
代码语言：javascript复制cat > test_gpload.yml <<EOF
---
VERSION: 1.0.0.1
DATABASE: test
USER: gpadmin
HOST: gp-mdw
PORT: 5432
GPLOAD:
INPUT:
- SOURCE:
LOCAL_HOSTNAME:
- gp-mdw
PORT: 1234
FILE:
- /home/gpadmin/test_gpload.txt
- COLUMNS:
- id: int
- order_num: varchar
- accountid: varchar
- qn: varchar
- appid: int
- amount: numeric
- pay_time: timestamp
- FORMAT: text
- ERROR_LIMIT: 25
- error_table: public.test_gpload_err
OUTPUT:
- TABLE: public.test_gpload
- MODE: INSERT
SQL:
- BEFORE: "truncate table public.test_gpload"
- AFTER: "ANALYZE test_gpload"
EOF
“-”后一定要有空格；“:”后也一定要有空格。
参数说明：
- VERSION 自定义版本号（可选项）
- DATABASE 需要连接的数据库，如果没有指定，根据$PGDATABASE变量确定
- USER 执行操作的用户。如果没指定，根据$PGUSER变量确定
- HOST 可选项。指定master节点的主机名(IP)。如果没指定，根据变量$PGHOST确定。
- PORT 可选项。指定master的端口，默认是5432或者$GPORT。
- GPLOAD 必须项。load部分的开始。一个GPLOAD部分必须包含一个INPUT和一个OUTPUT。
- INPUT 必须项。定义加载数据的格式和位置。gpload在当前主机上启动一个或者多个gpfdist文件分布式实例 。注意，gpload命令所在主机可网络访问Greenplum中的每个节点（master&segment）。
- SOURCE 必须项。INPUT部分的SOURCE块其定义了source文件所在位置。一个INPUT部分中可以有1个或者多个SOURCE块定义。每个SOURCE块定义对应了一个本机的gpfdist实例。每个SOURCE块定义必须制定一个source文件。
- LOCAL_HOSTNAME 可选项。gpload工具运行所在的主机名或者IP地址。如果这台主机有多个网卡，能同时使用每个网卡（每个网卡都有一个IP地址），通过设定LOCAL_HOSTNAME和PORT 实现多个gpfdist实例，可提升数据加载速度。默认情况，只使用主主机名或者IP地址。
- PORT 可选项。gpfdist实例需要的端口。
- FILE 必须项。文件位置。可同时制定多个相同格式的文件，入/home/gpadmin/script/*.txt。如果是gzip或bzip2文件，会自动解压（在环境变量中设定好gunzip、bunzip2的路径）。
- CLOUMNS 可选项。说明source文件的格式，列名:数据类型。DELIMITER参数，指明source文件中两个数据之间的分隔符。如果没有指定COLUMNS选项，意味着source文件中的列的顺序、列的数量、数据类型都和目标表一致。COLUMN的作用：SOURCE_TO_TARGET的mapping关系。
- FORMAT 可选项。source文件的类型，比如text、csv。默认text格式不说指定。
- DELIMITER 可选项。一行数据中，各列的分隔符号。TEXT格式中默认tab作为分隔符；CSV中以都好”,”作为分隔符。
- ERROR_LIMIT 可选项。允许的错误行数。加载数据时，错误数据将被忽略。如果没有到达错误限制数量，所有正常行会加载到GP中，问题行会存放到err_table中。如果超过错误值，正常数据也不会加载。
- ERROR_TABLE 可选项。前提是开启了ERROR_LIMIT 。错误表将记录错误行。如果错误表不存在，会自动创建。若存在，直接插入数据。
- EXTERNAL 可选项。定义外部表。
- OUTPUT 必须项。定义最终source文件加载到的目标表。
- TABLE 必须项。目标表。
- MODE 可选项。有三种模式：insert，插入数据；update，当MATCH_COLUMNS参数值（相当于关联列）等于加载数据时，更新UPDATE_COLUMS参数设置的列（相当于update的列）。并且，必须设置UPDATE_CONDITION参数（相当于where过滤条件）。merge, 加载数据时，插入目标表中不存在的数据，更新目标中存在的数据。
- MATCH_COLUMNS 在UPDATE或者MERGE模式下使用。相当于关联列。这里写目标表的列名。
- UPDATE_COLUMNS 在UPDATE或者MERGE模式下使用。更新的目标表列名。
- UPDATE_CONDITION 可选项。目标表的列名，相当于where条件。用在update或者merge模式。
- MAPPING 可选项。如果设置了MAPPING参数，那么前面设置的COLUMNS参数会失效，因为MAPPING级别高于COLUMNS。关联格式：target_column_name: source_column_name。where过滤格式：target_column_name: ‘expression’
- RELOAD 可选项。导入时，是truncate之前目标表的数据，还是保留目标表数据。两种模式，TRUNCATE 和REUSE_TABLES。
- SQL 可选项。定义开始运行gpload和gpload结束执行的SQL语句。BEFORE，开始运行gpload执行的SQL，SQL需引号括起来；AFTER，gpload结束后执行的SQL，SQL需引号括起来。
step 4.然后使用gpload工具，将数据加载到数据库。
代码语言：javascript复制[gpadmin@gp-mdw ~]$ gpload -f test_gpload.yml
2022-10-09 17:21:25|INFO|gpload session started 2022-10-09 17:21:25
2022-10-09 17:21:25|INFO|started gpfdist -p 1234 -P 1235 -f "/home/gpadmin/test_gpload.text" -t 30
2022-10-09 17:21:27|INFO|running time: 1.93 seconds
2022-10-09 17:21:28|INFO|rows Inserted = 10
2022-10-09 17:21:28|INFO|rows Updated = 0
2022-10-09 17:21:28|INFO|data formatting errors = 0
2022-10-09 17:21:28|INFO|gpload succeeded
[gpadmin@gp-mdw ~]$
4.3.数据装载性能技巧
- 在装载前删除索引 在已存在的数据上创建索引比不断的递增索引要快
- 在装载之后运行ANALYZE 执行ANALYZE确保查询计划拥有最新的统计信息
- 在装载出错后执行VACUUM 错误发生前的记录无法访问，但仍然占据磁盘空间
5.从GP中卸载数据
- 两种类型：并行(使用可写外部表)和非并行(COPY)
- 基于可写外部表有两种方式的：基于文件和基于WEB
5.1.并行卸载
定义基于文件的可写外部表
- 使用CREATE WRITABLE EXTERNAL TABLE命令定义外部表并指出输出文件的位置和格式
- 使用gpfdist协议的可写外部表
GP Segment将数据发送给gpfdist进程，该进程将数据写到指定名称的文件； 若希望输出的数据分割到多个文件，可以在外部表的定义中指定多个gpfdist的URL选项
代码语言：javascript复制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;
定义基于命令的可写外部表
使用 “CREATE WRITABLE EXTERNAL WEB TABLE” 命令定义外部表并指定可执行命令或程序
对于可写WEB表，EXECUTE子句指定的命令或脚本准备着接受数据输入流；
可写外部表有分布策略选项，缺省为随机分布；
若使用HASH分布策略，在可写外部表中定义相同的分布键可以改善卸载的性能。
在外部表定义的EXECUTE子句中，可根据需要设置环境变量。例如，
代码语言：javascript复制CREATE WRITABLE EXTERNAL WEB TABLE tb_wext_wb01 (output text)
EXECUTE 'export PATH=$PATH:/home/gpadmin;myprogram.sh‘
FORMAT 'TEXT‘
DISTRIBUTED RANDOMLY;
WEB外部表和可写外部表的可执行性 外部表执行OS命令或者脚本有一定的风险，根据需要，可以禁止在WEB表定义中使用EXECUTE。 在Master的postgresql.conf文件设置：
代码语言：javascript复制gp_external_enable_exec = off
5.2.串行卸载
使用COPY卸载数据
在GP Master上使用COPY TO语句从数据库表串行拷贝数据到文件
代码语言：javascript复制COPY (SELECT * FROM tb_cp_02 WHERE date LIKE '2013%') TO '/data/unload/tb_cp_02_2013.out';
5.3.可读外部表的统计信息
- 无法通过ANALYZE获取，可以通过手工修改数据字典pg_class来设置粗略统计值
- 指定行数和数据库页面数(数据尺寸/32K) 默认行数为1000000，页面数为1000
update pg_class set reltuples=500000, relpages=150 where relname='tb_wext_gf01';
6.装载和卸载自定义数据
在GP中有两种自定义方案可用于装载和卸载数据
使用自定义格式
使用自定义格式用于导入导出TEXT和CSV两种格式之外的数据,有如下3个步骤： 1.编写输入输出函数并编译到共享库中 2.在GP中通过CREATE FUNCTION指定共享库函数 3.将这些函数与CREATE EXTERNAL TABLE的formatter子句关联
对于固定宽度数据来说，使用函数名分别为fixedwidth_in和fixedwidth_out 可以完成自定义
导入导出固定宽度数据
在GP中可直接使用固定宽度数据的函数，需要指定自定义的格式和在formatter参数中指定函数名称。例如，
代码语言：javascript复制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');
其他选项
设置空白和控制字符 要保留补尾的空白，使用preserve_blanks=on选项； 使用null=‘null_string_value’选项指定控制字符
指定行的结尾 使用参数line_delim=‘line_ending’指定行的结尾字符； 下面列举的可以覆盖大部分场景：
代码语言：javascript复制line_delim=E’n’
line_delim=E’r’
line_delim=E’rn’
line_delim=‘’ (没有分隔行)
例如
代码语言：javascript复制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');
使用自定义协议
如果现有的协议(gpfdist、http、file等)不能够很好的用于访问数据，可以编写自定义的协议。
