Greenplum数据加载方式(1) – insert 和 copy
1,844 total views, 1 views today
greenplum数据加载主要包括insert、copy、外部表、gpload、web external table等五种方式。
其中insert和copy是串行;外部表gpfdist和gpload工具是并行方式。
1、insert
这种加载方式和其他数据库SQL语法一样,但是效率最差,只适合加载极少数数据。需要通过master节点操作。
<1>直接在plsq客户端中执行insert语句
注意,插入空值,需用null表示,这点和oracle不同。如,
1 2 3 4 5 | testDB=# insert into t values('','szlsd'); ERROR: invalid input syntax for integer: "" testDB=# testDB=# insert into t values(null,'szlsd'); INSERT 0 1 |
<2>批量insert,类似于oracle的@t.sql
1 | $ psql testDB -f /home/gpadmin/script/t.sql |
或者psql登录后执行
1 | testDB=# \i /home/gpadmin/script/t.sql |
2、copy方式
比SQL方式效率大大提升,但是数据依然需要通过master节点,无法实现并发高效数据加载。
将member.txt文件中的数据导入到t表中(注意,如果文件中有空行,则会引起导入失败)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | $ more member.txt 10 , jj 11 , kk 2 , tom 3 , cc 4 , dd 5 , ee 6 , ff 7 , gg 8 , hh 9 , ii 10 , jj 11 , kk 2 , tom 3 , cc 4 , dd 5 , ee 6 , ff 7 , gg 8 , hh 9 , ii 10 |
copy方式加载数据
1 | testDB=# copy t(id,name) from '/home/gpadmin/script/member.txt' with delimiter ','; |
copy语法
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | COPY table [(column [, ...])] FROM {'file' | STDIN} [ [WITH] [OIDS] [HEADER] [DELIMITER [ AS ] 'delimiter'] [NULL [ AS ] 'null string'] [ESCAPE [ AS ] 'escape' | 'OFF'] [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF'] [CSV [QUOTE [ AS ] 'quote'] [FORCE NOT NULL column [, ...]] [FILL MISSING FIELDS] [[LOG ERRORS [INTO error_table] [KEEP] SEGMENT REJECT LIMIT count [ROWS | PERCENT] ] COPY {table [(column [, ...])] | (query)} TO {'file' | STDOUT} [ [WITH] [OIDS] [HEADER] [DELIMITER [ AS ] 'delimiter'] [NULL [ AS ] 'null string'] [ESCAPE [ AS ] 'escape' | 'OFF'] [CSV [QUOTE [ AS ] 'quote'] [FORCE QUOTE column [, ...]] ] [IGNORE EXTERNAL PARTITIONS ] |
常用参数
分隔符:[DELIMITER [ AS ] ‘delimiter’]
处理空列(含有空格符的是不行的):[NULL [ AS ] ‘null string’]
记录错误数据,错误日志表自动创建: [[LOG ERRORS [INTO error_table] [KEEP]
允许错误的行数,大于指定值导入失败全部回滚:SEGMENT REJECT LIMIT count [ROWS | PERCENT] ]
Greenplum数据加载方式(2) – 外部表(gpfdist)
1,926 total views, 1 views today
上篇《Greenplum数据加载方式(1) – insert 和 copy》介绍了Greenplum最普通、低效的数据导入方式:insert和copy。
为了提高数据导入效率,Greenplum引入了外部表。外部表基于gpfdist工具(类似于Oracle的sqlldr工具),其最大的优势是支持数据并发加载。
一、外部表介绍及原理
所谓外部表,就是在数据库中只有表定义、没有数据,数据都存放在数据库之外的数据文件。greenplum可以对一个外部表执行正常的DML操作,当读取数据的时候,数据库从数据文件中加载数据。外部表支持在segment上并发地告诉从gpfdist导入数据,由于是从segment上导入数据,所以效率很高。
外部表示意图
外部表需要指定gpfdist的IP和端口,还要有详细的目录地址,文件名支持通配符匹配。可以编写多个gpfdist地址,但是总数不能超过总的segment数量,否则会报错。
GPDB提供两种外部表:可读外部表用于数据装载、可写外部表用于数据卸载。外部表可基于文件、亦可基于WEB,这两种都能实现可读、可写。
当一个查询使用一个常规的外部表,该外部表被认为是可重读的,因为在该查询期间数据是静态的。而对于WEB外部表,数据是不可重读的,因为在该查询的执行期间数据可能会发生变化。
可写外部表用以从数据库表中选择记录并输出到文件、命名管道或其他可执行程序。 比如,可以从GPDB中卸载数据并发送到一个可执行程序,该程序连接到其他数据库或者ETL工具并装载数据到其他地方。 可写外部表还可以用于输出到GPDB的并行MapReduce计算。
可写外部表被定义后,数据即可从数据库表中被选择并插入到该可写外部表。 可写外部表只允许INSERT操作 – SELECT、 UPDATE、 DELETE或TRUNCATE是不允许的。可写外部表输出数据到一个可执行程序,该程序要能够接受流输入数据。
在创建外部表的时候,可以指定分隔符、err表、指定允许出错的数据条数,以及源文件的编码等信息。
二、外部表语法
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 | CREATE [READABLE] EXTERNAL TABLE table_name ( column_name data_type [, ...] | LIKE other_table ) LOCATION ('file://seghost[:port]/path/file' [, ...]) | ('gpfdist://filehost[:port]/file_pattern[#transform]' | ('gpfdists://filehost[:port]/file_pattern[#transform]' [, ...]) | ('gphdfs://hdfs_host[:port]/path/file') FORMAT 'TEXT' [( [HEADER] [DELIMITER [AS] 'delimiter' | 'OFF'] [NULL [AS] 'null string'] [ESCAPE [AS] 'escape' | 'OFF'] [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF'] [FILL MISSING FIELDS] )] | 'CSV' [( [HEADER] [QUOTE [AS] 'quote'] [DELIMITER [AS] 'delimiter'] [NULL [AS] 'null string'] [FORCE NOT NULL column [, ...]] [ESCAPE [AS] 'escape'] [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF'] [FILL MISSING FIELDS] )] | 'AVRO' | 'PARQUET' | 'CUSTOM' (Formatter=<formatter specifications>) [ ENCODING 'encoding' ] [ [LOG ERRORS [INTO error_table]] SEGMENT REJECT LIMIT count [ROWS | PERCENT] ] CREATE [READABLE] EXTERNAL WEB TABLE table_name ( column_name data_type [, ...] | LIKE other_table ) LOCATION ('http://webhost[:port]/path/file' [, ...]) | EXECUTE 'command' [ON ALL | MASTER | number_of_segments | HOST ['segment_hostname'] | SEGMENT segment_id ] FORMAT 'TEXT' [( [HEADER] [DELIMITER [AS] 'delimiter' | 'OFF'] [NULL [AS] 'null string'] [ESCAPE [AS] 'escape' | 'OFF'] [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF'] [FILL MISSING FIELDS] )] | 'CSV' [( [HEADER] [QUOTE [AS] 'quote'] [DELIMITER [AS] 'delimiter'] [NULL [AS] 'null string'] [FORCE NOT NULL column [, ...]] [ESCAPE [AS] 'escape'] [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF'] [FILL MISSING FIELDS] )] | 'CUSTOM' (Formatter=<formatter specifications>) [ ENCODING 'encoding' ] [ [LOG ERRORS [INTO error_table]] SEGMENT REJECT LIMIT count [ROWS | PERCENT] ] CREATE WRITABLE EXTERNAL TABLE table_name ( column_name data_type [, ...] | LIKE other_table ) LOCATION('gpfdist://outputhost[:port]/filename[#transform]' | ('gpfdists://outputhost[:port]/file_pattern[#transform]' [, ...]) | ('gphdfs://hdfs_host[:port]/path') FORMAT 'TEXT' [( [DELIMITER [AS] 'delimiter'] [NULL [AS] 'null string'] [ESCAPE [AS] 'escape' | 'OFF'] )] | 'CSV' [([QUOTE [AS] 'quote'] [DELIMITER [AS] 'delimiter'] [NULL [AS] 'null string'] [FORCE QUOTE column [, ...]] ] [ESCAPE [AS] 'escape'] )] | 'AVRO' | 'PARQUET' | 'CUSTOM' (Formatter=<formatter specifications>) [ ENCODING 'write_encoding' ] [ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ] CREATE WRITABLE EXTERNAL WEB TABLE table_name ( column_name data_type [, ...] | LIKE other_table ) EXECUTE 'command' [ON ALL] FORMAT 'TEXT' [( [DELIMITER [AS] 'delimiter'] [NULL [AS] 'null string'] [ESCAPE [AS] 'escape' | 'OFF'] )] | 'CSV' [([QUOTE [AS] 'quote'] [DELIMITER [AS] 'delimiter'] [NULL [AS] 'null string'] [FORCE QUOTE column [, ...]] ] [ESCAPE [AS] 'escape'] )] | 'CUSTOM' (Formatter=<formatter specifications>) [ ENCODING 'write_encoding' ] [ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ] |
三、创建外部表的步骤
<1>在文件/ETL服务器上启动gpfdist服务器,指定文件目录和端口。
只需将gpfdist工件拷贝到相关服务器上,在环境变量中加上$PATH变量即可使用。
语法:
1 2 3 4 | gpfdist [-d directory] [-p http_port] [-l log_file] [-t timeout] [-S] [-w time] [-v | -V] [-s] [-m max_length] [--ssl certificate_path] gpfdist -? | --help gpfdist --version |
启动gpfdist步骤
1 | $ nohup gpfdist -d /home/gpadmin/script -p 8081 -l /home/gpadmin/script/gpfdist.log & |
查看是否已启动
1 2 | $ ps -ef|grep gpfdist|grep -v "grep" gpadmin 13193 1 0 Dec22 ? 00:00:33 gpfdist -d /home/gpadmin/script -p 8081 -l /home/gpadmin/script/gpfdist.log |
停止gpfdist服务
1 | $ kill xxx |
<2>准备好需要加载的数据文件,创建外部表。这里最后两行(21和22行)是错误数据。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | $ more member.txt 10 , jj 11 , kk 2 , tom 3 , cc 4 , dd 5 , ee 6 , ff 7 , gg 8 , hh 9 , ii 10 , jj 11 , kk 2 , tom 3 , cc 4 , dd 5 , ee 6 , ff 7 , gg 8 , hh 9 , ii 10 |
<3>创建外部表t1
1 2 3 4 5 6 7 8 9 10 | create external table public.t1 ( id int, name varchar(200) ) location ('gpfdist://10.9.15.18:8081/member.txt') format 'text' (delimiter ',' null as '' escape 'off') encoding 'utf8' log errors into t1_err segment reject limit 3 rows; |
参数说明:
location 文件所在位置,可以直接是本地路径、gpfdist地址、gpfdists地址、gphdfs地址。
format 文本类型
delimiter 分隔符
encoding 编码
log error into 错误数据表,记录错误数据,会自动创建。一般都是tablename_err格式,例如t1_err。
segment reject limit 错误数据的条数/百分比(rows/percent),超过设置值会报错。最小值是2。用来确保数据的完整性。
<4>查询外部表t1
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | # select * from t1; NOTICE: Found 2 data formatting errors (2 or more input rows). Rejected related input data. id | name ----+------ 10 | jj 11 | kk 2 | tom 3 | cc 4 | dd 5 | ee 6 | ff 7 | gg 8 | hh 9 | ii 10 | jj 11 | kk 2 | tom 3 | cc 4 | dd 5 | ee 6 | ff 7 | gg 8 | hh 9 | ii (20 rows) |
查询t1的日志表(t1_err),可以看到该外部表的错误信息:21和22行数据缺失了name列。
Greenplum外部表的错误日志表格式:tablename_err 。
1 2 3 4 5 6 | testDB=# select * from t1_err; cmdtime | relname | filename | linenum | bytenum | errmsg | rawdata | rawbytes -------------------------------+---------+------------------------------------------------------------------------+---------+---------+--------------------------------+---------+---------- 2015-12-23 16:56:41.945765+08 | t1 | gpfdist://10.9.15.18:8081/member.txt [/home/gpadmin/script/member.txt] | 21 | | missing data for column "name" | | 2015-12-23 16:56:41.945765+08 | t1 | gpfdist://10.9.15.18:8081/member.txt [/home/gpadmin/script/member.txt] | 22 | | missing data for column "name" | 10 | (2 rows) |
字段说明:
cmdtime 记录时间
relname 表名
filename 外部表对应的文件名,包含网络路径和绝对路径
linenum 文件行号
errms 错误信息
rawdata 错误数据的值
装载数据,如果外部文件中,数据的格式错误,装载时会提示错误行数。
1 2 | # insert into public.t1_his select * from t1; NOTICE: Found 2 data formatting errors (2 or more input rows). Rejected related input data. |
至此,外部表创建完毕。
Greenplum数据加载方式(3) – gpload
3,902 total views, 2 views today
《Greenplum数据加载方式(1) – insert 和 copy》和 《Greenplum数据加载方式(2) – 外部表(gpfdist)》两篇文章介绍了Greenplum加载数据的三种方式。本篇引入Greenplum的第四种数据加载方式:gpload。
一、gpload原理介绍
gpload是GP使用可读外部表和GP并行文件服务gpfdist装载数据的一个命令包装。其允许通过使用配置文件的方式设置数据格式等来创建外部表定义。通过按照YAML格式定义的装载说明配置文件,然后执行insert、update、merger操作,将数据装载到目标数据库表中。
二、语法
请参考http://gpdb.docs.pivotal.io/4360/utility_guide/admin_utilities/gpload.html
三、创建YAML格式控制文件
该yaml文件解释:在10.9.15.18服务器上,存放着外部文件/home/gpadmin/script/member.txt。通过gpadmin用户把外部文件insert到到10.9.15.8的testDB的t表中;并把加载的开始和结束时间插入到audit审计表中。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | $ cat my_load.yml --- VERSION: 1.0.0.1 DATABASE: testDB USER: gpadmin HOST: 10.9.15.18 PORT: 5432 GPLOAD: INPUT: - SOURCE: LOCAL_HOSTNAME: - 10.9.15.18 PORT: 8081 FILE: - /home/gpadmin/script/member.txt - COLUMNS: - id: int - name: varchar(200) - FORMAT: text - DELIMITER: ',' - ERROR_LIMIT: 25 - ERROR_TABLE: public.t_err OUTPUT: - TABLE: public.t - MODE: insert SQL: - BEFORE: "INSERT INTO audit VALUES('start',current_timestamp)" - AFTER: "INSERT INTO audit VALUES('end',current_timestamp)" |
特别提醒:“-”后一定要有空格;“:”后也一定要有空格。
参数说明:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | 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需单引号括起来。 |
四、使用gpload命令加载数据
上面已经定义好了yaml文件,下面使用gpload加载数据。gpload命令执行期间有执行时间、插入行、更新行、数据错误次数等信息反馈,帮助DBA定位问题。
1 2 3 4 5 6 7 8 9 | $ gpload -f /home/gpadmin/script/my_load.yml 2015-12-24 17:24:10|INFO|gpload session started 2015-12-24 17:24:10 2015-12-24 17:24:10|INFO|started gpfdist -p 8081 -P 8082 -f "/home/gpadmin/script/member.txt" -t 30 2015-12-24 17:24:10|WARN|2 bad rows 2015-12-24 17:24:10|INFO|running time: 0.21 seconds 2015-12-24 17:24:10|INFO|rows Inserted = 20 2015-12-24 17:24:10|INFO|rows Updated = 0 2015-12-24 17:24:10|INFO|data formatting errors = 2 2015-12-24 17:24:10|INFO|gpload succeeded with warnings |
至此,Greenplum的四种加载方式全部介绍完毕。在生产环境中,大家可以根据不同场景和需求选择合适方式,正确的选择能帮助您高效完成加载任务。
gpfdist的工作原理及性能优化
1,737 total views, 1 views today
在 《Greenplum数据加载方式(2) – 外部表(gpfdist)》 和 《Greenplum数据加载方式(3) – gpload》 两篇文章中都使用到了gpfdist。本篇文章将详细介绍gpfdist的工作原理;Greenplum主要适用于大数据场景,数量都是TB级别,那么利用gpfdist加载数据必须要高效,因此gpfdist的性能优化也很重要。
一、工作原理
<1>启动gpfdist,并在Master上建表。表建好后并没有任何数据流动,只是定义好了外部表的原始数据信息。
<2>将外部表插入到一张Greenplum的物理表中,开始导入数据。
<3>Segment根据建表时定义的gpfdist url个数,启动相同的并发到gpfdist获取数据,其中每个Segment节点都会连接到gpfdist上获取数据。
<4>gpfdist收到Segment的连接并要接收数据时,开始读取文件,顺序读取文件,然后将文件拆分成多个块,随机抛给Segment。
<5>由于gpfdist并不知道数据库中有多少个Segment,数据是按照哪个分布键拆分的,因此数据是随机发送到每个Segment上的,数据到达Segment的时间基本上是随机的,所以外部表可以看成是一张随机分布的表,将数据插入到物理表的时候,需要进行一次重新分布。
<6>为了提高性能,数据读取于与重分布是同时进行的,当数据重分布完毕后,整个数据导入流程结束。
二、gpfdist最主要的功能
<1>负载均衡:每个Segment分配到的数据都是随机的,所以每个节点的负载都非常均衡。
<2>并发读取,性能高:每台Segment都同时通过网卡到文件服务器获取数据,并发读取,从而获取了比较高的性能。相对于copy命令,数据要通过Master流入,使用外部表就消除了Master这个单点问题。
三、如何提高gpfdist性能
Greenplum数据导入,容易出瓶颈的2个地方。
<1>文件服务器
因为所有Segment都连接到文件服务器上获取数据,所以如果文件服务器是单机的,那么文件服务器很容易在磁盘IO和网卡上出现性能瓶颈。
<2>Segment节点
Segment节点比文件系统要多不少,Segment一般不会出现磁盘IO和网卡性能问题。当Segment出现瓶颈时,数据导入引起的瓶颈可能性极小,更多的是整个数据库的性能都出现了瓶颈。
四、如何提高文件服务器性能
<1>IO出现瓶颈。考虑使用磁盘阵列,也可以采用分布式文件系统来提高整体性能,如MooseFS。
<2>网卡出现瓶颈。第一种方法:换成万兆网卡,这种方式成本很高,因为还需要万兆交换机等。第二种方法:通过多网卡机制来解决网络带宽瓶颈。
例如:
1 2 3 4 5 6 7 | CREATE EXTERNAL TABLE table_name (column_name date_type [,...] like other table) LOCATION ('gpfist://filehostip1[:port1]/file_pattern1', 'gpfist://filehostip2[:port2]/file_pattern2', 'gpfist://filehostip3[:port3]/file_pattern3', 'gpfist://filehostip4[:port4]/file_pattern4' ) |
我们可以只启动一个gpfdist,通过不同ip连接到gpfdist上,这样文件的gpfdist只有一个,不能实现IO并发,但是网卡可以使用多张,从而消除网卡瓶颈。