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并发，但是网卡可以使用多张，从而消除网卡瓶颈。
2281
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
