原 Greenplum中的外部表（gpfdist和gpload）
Tags： 原创GreenPlum外部表gpfdistgploadpg_hba.conf
- 简介
- 外部表创建和使用
- 可读外部表
- 可写外部表
- 外部数据表读写实现机制
- 外部数据转换
- 外部表查询计划
- 外部表的事务
- 使用可读外部表加载数据
- EXTERNAL TABLE语法
- 描述
- 参数
- 示例
- 注解
- 兼容性
- 另见
- 外部表示例
- gpfdist示例
- 读取pg_hba.conf文件
- 故障处理
- connection with gpfdist failed for 。。。 error code = 110 (Connection timed out)
- connection with gpfdist failed for 。。。error code = 111 (Connection refused)
- ERROR: could not assign a segment database for "file://mdw/home/gpadmin/txt/pg_hba.conf"
- http response code 404 from gpfdist
- 总结
- 参考
简介
外部表是greenplum的一种数据表，它与普通表不同的地方是：外部表是用来访问存储在greenplum数据库之外的数据。如普通表一样，可使用SQL对外部表进行查询和插入操作。外部表主要用于Greenplum数据的导入及导出。
1、外部表允许用户像访问标准数据库表一样的访问外部表。
2、结合GP的并行文件分配程序（gpfdist），外部表支持在装载和卸载数据时全并行化利用所有segment实例的资源。
3、GP还可以利用Hadoop分布式文件系统的并行架构来访问文件。
GP提供了两种类型的外部表：
- 可读外部表：用于数据装载，不允许对数据进行修改。
- 可写外部表：用于数据卸载，从数据库表中选择记录并输出到文件、命令管道或其他的可执行程序，包括并行MapRuduce计算。只允许INSERT操作。
按照数据源不同，分为两种可读外部表：
常规的：访问静态的平面文件。
WEB：访问动态数据源（比如wen服务或者OS的命令或者脚本）
在创建外部表定义时，必须指定文件格式和文件位置。
外部文件格式：
- TEXT类型对所有协议有效。
- 逗号分隔的CSV对于gpfdist和file协议有效
- 自定义格式适合于gphdfs
外部表中的错误数据：为了在装载正确格式的记录时隔离错误数据，需要在定义外部表时使用单条记录出错处理
外部表备份恢复：在备份或者恢复操作中，仅仅外部表或者WEB外部表的定义会备份或恢复。
定义外部表–使用GP并行文件服务（gpfdist）
为最大化系统带宽而运行gpfdist要考虑的因素：
·关于gpfdist的设置与性能
如果ETL主机配置了多个网口，应将说有网口对应的主机名在LOCATION子句中声明。
在ETL主机上运行多个gpfdist并将外部数据均匀的拆分到各gpfdist服务。
创建单文件服务的可读外部表，文件格式为竖线（|）分割：
1 2 3 | CREATE EXTERNAL TABLE tb_ext_gf01 (id int,name text) LOCATION('gpfdist://mdw:8081/*.txt') FORMAT 'TEXT' (DELIMITER '|' NULL ''); |
创建多文件服务的可读外部表，文件格式为竖线（|）分割：
1 2 3 | CREATE EXTERNAL TABLE tb_ext_gf02 (id int,name text) LOCATION('gpfdist://mdw:8081/*.txt','gpfdist://mdw:8082/*.txt') FORMAT 'TEXT' (DELIMITER '|' NULL ''); |
带有错误数据日志的多文件服务：
1 2 3 4 5 | 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; |
查看错误日志：
1 | select * from err_customer |
创建可写外部表：（用于数据导出）
1 2 3 4 5 | 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 来访问外部表
装载全部数据到一个新的数据表
1 2 | CREATE TABLE tb_load_01 AS SELECT * FROM tb_ext_gp01； |
创建和使用WEB外部表
查询优化器不允许重复扫描WEB表的数据
使用CREATE EXTERNAL WEB TABLE 创建GP的WEB表
WEB外部表的定义有两种形式
WEB URL
使用http://协议指定WEB服务器上文件的LOCATION;
该WEB数据文件必须在GP segment可以访问的WEB服务上；
URL的数量将对应并行访问WEB表的segment实例
例如：
1 2 3 4 5 6 7 | 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
例如：
1 2 3 | CREATE EXTERNAL WEB TABLE tb_ext_wb01 (output text) EXECUTE 'hostname' FORMAT 'TEXT'; |
外部表创建和使用
Greenplum数据库在创建一个外部表时，需要声明外部数据的LOCATION和FORMAT。LOCATION指定外部数据URL，包含外部数据读写协议；FORMAT指定外部数据格式，如TEXT、CSV等，greenplum会根据指定的格式，实现外部数据和数据库内部tuple的转换。
创建外部表之后，可以与操作普通表一样，对其进行select、insert等操作。
外部表分为可读外部表和可写外部表，可读外部表可以执行select 操作，对可写外部表只能执行insert操作，不能对其进行update和delete。
可读外部表
创建可读外部表时声明READABLE，或者直接使用缺省值。数据源可以是文件，gpfdist 进程（后面介绍），或者可执行程序。例如：
1 2 3 4 5 | CREATE EXTERNAL TABLE ext_expenses (name text, date date, amount float4, category text, desc1 text ) LOCATION ('file://filehost/data/international/*', 'file://filehost/data/regional/*', 'file://filehost/data/supplement/*.csv') FORMAT 'CSV'; |
上面的例子从多个位置的文件创建一个可读外部表ext_expenses。
- LOCATION 指定外部数据URL, 数据源地址是’file://filehost/data/international/’, ‘file://filehost/data/regional/’, ‘file://filehost/data/supplement/*.csv’ （其中file是外部数据读写协议， filehost是文件所在的机器hostname）。
- FORMAT指定外部数据格式是csv。
可读外部表创建成功后，可以使用select做查询操作。比如从外部表ext_expenses查询上述外部数据源（文件）中所有amount大于10000的记录：
1 | select * from ext_expenses where amount>10000; |
例如：
1 2 3 4 5 6 7 8 9 10 11 12 | cat > /home/gpadmin/1.txt <<"EOF" 1 2 EOF drop external table test; create external table test(id int) location('file://gpdb6/home/gpadmin/1.txt') format 'csv' (DELIMITER as ',') encoding 'utf8'; select * from test; |
可写外部表
创建可写外部表时需要声明WRITABLE。数据可以写入到gpfdist或者可执行程序，不支持写入本地文件。例：
1 2 3 4 | CREATE WRITABLE EXTERNAL TABLE sales_out (LIKE sales) LOCATION ('gpfdist://etl1:8081/sales.out') FORMAT 'TEXT' ( DELIMITER '|' NULL ' ') DISTRIBUTED BY (txn_id); |
上面的例子创建了一个输出到gpfdist的可写外部表sales_out。
- sales是greenplum数据库中的一个普通表，作为外部表sales_out的内部数据源。
- LOCATION指定外部数据URL，通过gpfdist进程将数据写入sales.out文件。
- FORMAT指定外部数据格式是text。
可写外部表创建成功后，可以使用insert从greenplum数据库中导出数据。比如将sales表中customer_id=123的数据写入上述的sales.out文件：
1 | INSERT INTO sales_out SELECT * FROM sales WHERE customer_id=123; |
外部数据表读写实现机制
这一节介绍外部表的不同类型的数据源，以及数据读写的实现机制。
外部表的数据源可分为四类：
- File，本地文件
- Execute，外部可执行程序
- Gpfdist，实现了gp_proto协议的http server
- Custom，预留的用于扩展外部表的存储类型接口
Greenplum实现读写各种类型数据源的代码在src/backend/access/external目录下。其中url.c 是外部表数据读写的入口，url_file.c、url_execute.c、url_curl.c、url_custom.c实现了url.c中的接口，分别读写本地文件、外部可执行程序、gpfdist进程(http server)、扩展的外部数据存储类型的数据。
url.c对外的方法如下：
- url_fopen(): 打开外部数据文件
- url_fclose()：关闭外部数据文件
- url_feof()：判断外部数据文件是否结尾
- url_ferror()：获取外部文件操作错误信息
- url_fread()：读取外部文件
- url_fwrite()：写外部文件
- url_flush()：flush外部文件
2.1 file
file类型的外部数据URL格式是：file://host/path
URL中file指明了数据读写协议，host在下文执行计划部分会讲到。
file 类型的外部数据只支持读取，不支持写入。读取时不仅可以读一个文件，也可以读取多个文件，例如file://filehost/data/international/*。
2.2 execute
execute类型的外部数据URL格式是：EXECUTE ‘/var/load_scripts/get_log_data.sh’
其中execute指明了数据读写协议，/var/load_scripts/get_log_data.sh指定可执行程序。
execute类型不仅支持读取外部数据，同时也支持写入外部数据。
Greenplum外部表通过执行定义的外部执行程序：例如get_log_data.sh，和管道实现execute类型数据的读写。读外部数据：将程序的标准输出作为数据来源；向外部写数据：将外部程序的标准输入作为数据表中的数据。
2.3 gpfdist
gpfdist是greenplum数据库的并行文件分发程序。代码在src/bin/gpfdist/下。
启动gpfdist进程的命令如下：
1 | gpfdist -d /tmp/test/ -p 8081 |
其中-d /tmp/test指定了gpfdist的工作目录，-p 8081是gpfdist进程的端口号。
gpfdist进程管理的文件在工作目录/tmp/test下。client向gpfdist 进程发出读请求时，设置要读取文件的path，则可以读取到/tmp/test/path的文件。例如/tmp/test下有两个目录d1,d2，client要读取d1下的文件，则需要设置path=/d1。
gpfdist的主要功能是实现了并行分发文件。当多个client同时读取同一文件，gpfdist不会把文件内容整体发送给每个client，而是把文件进行切分，每个client只得到文件中的一部分。例如有n 个client 同时向gpfdist进程发送读取同一path下文件的请求, path下全部文件大小是nmblock_size，gpfdist首先从文件中读出n 个数据块分发给这n个client，每个client处理完读取到的数据块后，可以再次向gpfdist发送读数据请求，直到gpfdist 管理的path下的文件被全部读完为止。当client是greenplum的segment时，可以实现segments对外部数据的并行处理。
gpfdist中有一个session的概念。session管理一组并发读或写同一目录文件的request。Session维护request列表，记录文件读取的offset，维护文件的读取状态，用于实现文件的读写分发。
Session处理相关的函数如下：
- session_attach(): 把一个request加入到一个session中
- session_detach()：把request从session中删除
- session_end(): 结束一个session，把session打开的文件关闭
- session_free()：删除session
- session_get_block():session读取path下文件一个block数据
- sessions_cleanup()：清理过期session
gpfdist怎么判断client的request请求应该属于哪个session呢？
