GP提供了两种类型的外部表:
按数据源不同,分为两种可读外部表:
在创建外部表定义时,必须指定文件格式和文件位置 三种用来访问外部表数据源的协议:gpfdist, gpfdists和gphdfs
在外部表指定文件的所有主机上运行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服务器参与到装载处理中,可以以非常高的速率被装载。
为最大化系统带宽而运行gpfdist要考虑的因素:
如果ETL主机配置了多个网口,应将所有网口对应的主机名在LOCATION子句中声明
在ETL主机上运行多个gpfdist并将外部数据均匀的分拆到各gpfdist服务。
gp_external_max_segs 参数设置最大多少segment实例访问同一个gpfdist文件分发程序。
要启动gpfdist,必须指定其提供文件服务的目录以及运行的端口(缺省为HTTP端口8080) 在后台启动gpfdist(日志信息和出错信息输出到日志文件)
$gpfdist -d /var/load_files -p 8081 -l /home/gpadmin/log &
要在同一个ETL主机启动多个gpfdist服务,为每个服务指定不同的目录和端口。例如:
$gpfdist -d /var/load_files1 -p 8081 -l /home/gpadmin/log1 &
$gpfdist -d /var/load_files2 -p 8082 -l /home/gpadmin/log2 &
停止后台gpfdist服务:
# 第一步找到进程号,
$ps -ef|grep gpfdist
# 第二步杀掉进程,kill -9 XXX
select pg_cancel_backend(1234);
确保 GP segment可以访问gpfdist的网络 ,使用wget命令来测试GP集群的连接性
$ wget http://gpfdist_hostname:port/filename
需要确保 CREATE EXTERNAL TABLE 定义了hostname,port以及gpfdist的文件名
step 1.创建表
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.创建数据库
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命令的文件拷贝到相应的服务器上即可使用。
nohup gpfdist -d /home/gpadmin -p 1234 -l /home/gpadmin/gpfdist.log &
启动gpfdist进程,扫描路径为 /home/gpadmin,监听端口为1234
step 4.创建一张基于gpfdist工具的外部表。
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.加载数据
test=# insert into test_gpfdist select * from ext_test_gpfdist;
INSERT 0 10
test=#
ANALYZE表
test=# analyze test_gpfdist;
ANALYZE
test=#
step 6.加载完成之后需要停到gpfdist进程
[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进程
[gpadmin@gp-mdw ~]$ kill -9 14881
step 7.删除外部表
test=# drop external table ext_test_gpfdist;
DROP EXTERNAL TABLE
test=#
GP数据库查询数据,先扫描到的数据会直接返回,也就是多次查询的结果可能是不一样的,使用gpfdist工具加载,而gpfdist工具加载数据是并行加载的,最先插入到数据库的数据并不一定是从第一条数据开始的。
step 8.查看数据是否倾斜
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=#
gpfdists是gpfdist的安全版本,其开启的加密通信并确保文件与GP之间的安全认证。
如果使用file:// 协议,则外部文件必须存放在segment主机上面。
指定的符合segment实例数量的URLs将并行工作来访问外部表
每个segment主机外部文件的数量不能超过segment实例数量。
pg_max_external_files用来确定每个外部表中允许多少个外部文件。
select * from pg_max_external_files
该协议指定一个可以在HDFS上包含通配符的路径。 在GP链接到HDFS文件时,所有数据将从HDFS数据节点被并行读取到GP的segment实例以快速处理。 每个GPsegment实例值读取一组Hadoop数据块 对于写来说,每个GP segment实例值写giant实例包含的数据
使用HDFS的三个步骤
在建立外部表的时候,能够指定分隔符、err表、指定容许出错的数据条数,以及源文件的编码等信息。必须指定文件格式和文件位置。
参数:
创建单文件服务的可读外部表,文件格式为竖线(|)分割:
CREATE EXTERNAL TABLE tb_ext_gf01 (id int,name text)
LOCATION('gpfdist://mdw:8081/*.txt')
FORMAT 'TEXT' (DELIMITER '|' NULL '');
创建多文件服务的可读外部表,文件格式为竖线(|)分割:
CREATE EXTERNAL TABLE tb_ext_gf02 (id int,name text)
LOCATION('gpfdist://mdw:8081/*.txt','gpfdist://mdw:8082/*.txt')
FORMAT 'TEXT' (DELIMITER '|' NULL '');
带有错误数据日志的多文件服务:
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;
创建可写外部表:(用于数据导出)
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 ;
查看错误日志:
select * from err_customer
使用select from 来访问外部表, 装载全部数据到一个新的数据表
CREATE TABLE tb_load_01 AS SELECT * FROM tb_ext_gp01;
查询优化器不允许重复扫描WEB表的数据。
WEB外部表的定义有两种形式
WEB URL 使用 http:// 协议指定WEB服务器上文件的LOCATION; 该WEB数据文件必须在GP segment可以访问的WEB服务上;URL的数量将对应并行访问WEB表的segment实例
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
CREATE EXTERNAL WEB TABLE tb_ext_wb01 (output text)
EXECUTE 'hostname'
FORMAT 'TEXT';
标准PostgreSQL装载和卸载命令 ; 不具有并行装载/卸载的机制。
对于数据加载,GreenPlum数据库提供copy工具,copy工具源于PostgreSQL数据库,copy命令支持文件与表之间的数据加载和表对文件的数据卸载。使用copy命令进行数据加载,数据需要经过Master节点分发到Segment节点,同样使用copy命令进行数据卸载,数据也需要由Segment发送到Master节点,由Master节点汇总后再写入外部文件,这样就限制了数据加载与卸载的效率,但是数据量较小的情况下,copy命令就非常方便。
step 1.创建表
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.测试数据
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。
[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权限的用户
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=#
执行命令,使用默认参数如下:
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数据库内部的数据格式化错误。
如何跳过错误行?
常用参数
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语句
copy (select * from bsscost limit 100 ) to '/home/yaml/output.txt' with csv header delimiter AS ',';
copy from 将文件的数据复制到表中, copy是非并行的。 使用Greenplum master实例在单个进程中加载数据。建议仅对非常小的数据文件使用copy 。
gpload是GP使用可读外部表和GP并行文件服务gpfdist装载数据的一个命令包装。
gpload是一种数据装载工具,它扮演着Greenplum外部表并行装载特性的接口的角色。gpload使用定义在一个YAML格式的控制文件中的规范来执行一次装载。
它会执行下列操作:
step 1.创建表
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.创建数据库
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配置信息,外部表选项以及数据格式。
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
“-”后一定要有空格;“:”后也一定要有空格。
参数说明:
step 4.然后使用gpload工具,将数据加载到数据库。
[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 ~]$
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;
使用 “CREATE WRITABLE EXTERNAL WEB TABLE” 命令定义外部表并指定可执行命令或程序
对于可写WEB表,EXECUTE子句指定的命令或脚本准备着接受数据输入流;
可写外部表有分布策略选项,缺省为随机分布;
若使用HASH分布策略,在可写外部表中定义相同的分布键可以改善卸载的性能。
在外部表定义的EXECUTE子句中,可根据需要设置环境变量。例如,
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文件设置:
gp_external_enable_exec = off
在GP Master上使用COPY TO语句从数据库表串行拷贝数据到文件
COPY (SELECT * FROM tb_cp_02 WHERE date LIKE '2013%') TO '/data/unload/tb_cp_02_2013.out';
update pg_class set reltuples=500000, relpages=150 where relname='tb_wext_gf01';
在GP中有两种自定义方案可用于装载和卸载数据
使用自定义格式用于导入导出TEXT和CSV两种格式之外的数据,有如下3个步骤: 1.编写输入输出函数并编译到共享库中 2.在GP中通过CREATE FUNCTION指定共享库函数 3.将这些函数与CREATE EXTERNAL TABLE的formatter子句关联
对于固定宽度数据来说,使用函数名分别为fixedwidth_in和fixedwidth_out 可以完成自定义
在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');
设置空白和控制字符 要保留补尾的空白,使用preserve_blanks=on选项; 使用null=‘null_string_value’选项指定控制字符
指定行的结尾 使用参数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');
如果现有的协议(gpfdist、http、file等)不能够很好的用于访问数据,可以编写自定义的协议。