Greenplum外表gpfdist加载数据

使用gpfdist加载数据

Greenplum通过外表，使得segment连接到gpfdist服务，获取数据。
gpfdist随机分发数据到各个segment中。
gpfdist线性处理，一次处理一个文本。根据服务器情况，可启动多个gpfdist服务。

一. 启动gpfdist服务

在ETL服务器上启动gpfdist服务
创建脚本文件gpfdistServer.sh，gpfdist后台启动

#!/bin/bash
# 指定了端口,指定了文件主目录(可不指定)
nohup /opt/greenplum/greenplum-db/bin/gpfdist -p 8080 -d /edw/data > /dev/null 2>&1 &
1
2
3

二. 创建外表

用户需要有创建外表的权限，只读外表/可写外表

--cascade 把关联的表/视图也删掉
drop external table ext.test cascade;
create external table ext.test(
   num integer,
   name varchar(32),
   income decimal(18,2)
   start_dt date,
   create_dt timestamp
) location('gpfdist://192.168.1.110:8080/tmp/ITEM.csv')
FORMAT 'CSV' (DELIMITER '|')
1
2
3
4
5
6
7
8
9
10

location 指定了gpfdist服务和文件的位置
gpfdist启动指定了路径为/edw/data，外表设置的文件路径为/tmp/ITEM.csv
文件在ETL服务器的绝对路径为:/edw/data/tmp/ITEM.csv

注意：gpfdist所在服务器要能被gp集群访问，此处ELT服务器和gp互相可访问。

-- 外表结构也可以用like
create external table ext.test(like ext.test1
) location('gpfdist://192.168.1.110:8080/tmp/ITEM.csv')
FORMAT 'CSV' (DELIMITER '|')
1
2
3
4

特点

文件可以是压缩的，可以是未压缩的

三.创建内部表,将外表数据导入其中

1. 创建内部表

drop table if exists ob.test;
create table ob.test(
   num integer,
   name varchar(32),
   income decimal(18,2)
   start_dt date,
   create_dt timestamp
) with(OIDS=false) DISTRIBUTED BY(num );
1
2
3
4
5
6
7
8

2. 将数据加载到内部表中

insert into ob.test select * from ext.test;
1

3. 分析执行计划(扩展)

explain insert into ob.test select * from ext.test;
1

外表特点

1. 创建外表可以用普通文件，可以用管道文件

普通文件
1.当删除ETL的普通文件/edw/data/tmp/ITEM.csv，查询外表会ext.test报错
2.当删除外表ext.test时，ETL的普通文件仍然在服务器中
3.外表创建成功后，当删除ETL的普通文件时，此时查询外表报错
4.当gpfdist服务停止时，此时查询只读外表报错
5.普通文件尽量是解压后的文件

管道文件
1.创建外表方式和普通文件的方式一样
2.创建外表，此时外表进程卡住的，此时相当于生成者生产了数据，需要消费者消费才能知道是什么数据。如当插入到内部表时，数据被消费掉。
3.管道文件不占I/O，减少磁盘开销

2. 外表文件可以添加表/字段注释，不能使用ANALYZE

COMMENT ON TABLE ext.test IS '测试表';
COMMENT ON COLUMN ext.test.num IS '编号';
1
2

外表创建格式

1. 分隔符外表

加载不同的gpfdist服务器中的文件,分隔符为 | ,NULL值为空字符串。
LOG ERRORS 只读外表可设置，可写外表不能设置。

-- 只读外表
CREATE EXTERNAL TABLE ext.test( 
    num integer,
    name varchar(32),
    income decimal(18,2)
    start_dt date,
    create_dt timestamp
)
LOCATION ('gpfdist://192.168.1.110:8080/tmp/ITEM.csv',
'gpfdist://192.168.1.111:8080/tmp/ITEM.csv')
FORMAT 'CSV' (DELIMITER '|' NULL '')
LOG ERRORS SEGMENT REJECT LIMIT 100;
1
2
3
4
5
6
7
8
9
10
11
12

2. 定长外表

内置的函数fixedwidth_in (加载用)，fixedwidth_out（导出用）
CUSTOM 设置各个字段对应数据的长度

CREATE EXTERNAL TABLE ext.test( 
    num integer,
    name varchar(32),
    income decimal(18,2)
    start_dt date,
    create_dt timestamp
)
LOCATION ('gpfdist://192.168.1.110:8080/tmp/ITEM2.dat')
FORMAT 'CUSTOM' (formatter=fixedwidth_in,
num='12', name='32', income='20',start_dt='8',create_dt='19');
LOG ERRORS SEGMENT REJECT LIMIT 100;
1
2
3
4
5
6
7
8
9
10
11

3. 无限定符只读外表
   line_delim 参数为空字符串

CREATE EXTERNAL TABLE ext.test( 
    num integer,
    name varchar(32),
    income decimal(18,2)
    start_dt date,
    create_dt timestamp
)
LOCATION ('gpfdist://192.168.1.110:8080/tmp/ITEM2.dat'
FORMAT 'CUSTOM' (formatter=fixedwidth_in,
num='12', name='32', income='20',start_dt='8',create_dt='19',line_delim='');
LOG ERRORS SEGMENT REJECT LIMIT 100;
1
2
3
4
5
6
7
8
9
10
11

gpload方式加载数据

配置文件gpload.yaml

VERSION: 1.0.0.1
DATABASE: gpdb
USER: testuser
PASSWORD: 123456
HOST: 192.168.0.110
PORT: 5432
GPLOAD:
   INPUT:
    - SOURCE:         
         FILE:
           - /data/import/test.txt
    - COLUMNS:              #txt中每列的顺序
           - id: text
           - name: text
    - FORMAT: text
    - DELIMITER: ','
    - ERROR_LIMIT: 25
    - LOG_ERRORS: true
   OUTPUT:
    - TABLE: ext.load_test
    - MODE: INSERT
   PRELOAD:
    - REUSE_TABLES: true
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

启动gpload

gpload -f gpload.yaml -l ./gpload.log &
1

外表参数说明

delimiter 指定文件字段之间的分隔符
header 声明文件存在表头,默认false
format 取值text/csv，设置文件类型
quote 指定引号作为字段引号
encoding 指定文件的编码格式
null 指定对null值处理 ,一般null ‘’
escape 指定文件转义字符E’\t’

ESCAPE as ‘OFF’ 关闭转义，默认打开

补充

换行格式

Formatting rows

Greenplum数据库要求数据行由LF字符（换行，0x0A）、CR（回车，0x0D）或CR后跟LF（CR+LF，0x0D0x0A）分隔。LF是UNIX或类UNIX操作系统上的标准换行表示。Windows或Mac OS X等操作系统使用CR或CR+LF。Greenplum数据库支持所有这些换行符表示形式作为行分隔符。有关详细信息，请参见导入和导出固定宽度数据。

Formatting columns

默认的列或字段分隔符是文本文件的水平TAB字符（0x09）和CSV文件的逗号字符（0x2C）。定义数据格式时，可以使用COPY、CREATE EXTERNAL TABLE或gpload的delimiter子句声明单字符分隔符。分隔符必须出现在任意两个数据值字段之间。不要在行的开头或结尾放置分隔符。例如，如果管道字符（|）是分隔符：

以下命令显示了管道字符作为列分隔符的使用：
DELIMITER ‘|’

表示NULL值

NULL表示列或字段中的未知数据。在数据文件中，可以指定一个字符串来表示空值。在TEXT模式下，默认字符串为\N（反斜杠-N），或者在CSV模式下为空值，不带引号。定义数据格式时，还可以使用COPY、CREATE EXTERNAL TABLE或gpload的NULL子句声明不同的字符串。例如，如果不想区分NULL和空字符串，可以使用空字符串。使用Greenplum数据库加载工具时，与指定的空字符串匹配的任何数据项都被视为空值。

Escaping

有两个保留字符对Greenplum数据库具有特殊意义：
指定的分隔符分隔数据文件中的列或字段。
换行符指定数据文件中的新行。
如果数据包含这些字符中的任何一个，则必须对该字符进行转义，以便Greenplum将其视为数据，而不是字段分隔符或新行。默认情况下，对于文本格式的文件，转义字符是\（反斜杠），对于csv格式的文件则是双引号（“）。

Escaping in Text Formatted Files

默认情况下，转义字符是文本格式文件的\（反斜杠）。您可以在COPY、CREATE EXTERNAL TABLE或gpload的escape子句中声明不同的转义字符。如果转义字符出现在数据中，请使用它来转义自身。
例如，假设您有一个包含三列的表，并且希望加载以下三个字段：
反斜杠=
垂直条=|
感叹号=！
指定的分隔符是|（管道字符），指定的转义字符是\（反斜杠）。数据文件中的格式化行如下所示：
backslash = \ | vertical bar = | | exclamation point = !

请注意，作为数据一部分的反斜杠字符是如何用另一个反斜杠符转义的，作为数据的一部分的管道字符是如何使用反斜杠符号转义的。
可以使用转义字符转义八进制和十六进制序列。转义值在加载到Greenplum数据库时转换为等效字符。例如，要加载与号字符（&），请使用转义字符转义其等效的十六进制（\0x26）或八进制（\046）表示。
您可以使用COPY、CREATE EXTERNAL TABLE或gpload的ESCAPE子句停用文本格式文件中的转义，如下所示
ESCAPE ‘OFF’
这对于包含许多反斜杠字符的输入数据非常有用，例如web日志数据。

Escaping in CSV Formatted Files

默认情况下，对于CSV格式的文件，转义字符为“（双引号）。如果要使用不同的转义字符，请使用COPY、CREATE EXTERNAL TABLE或gpload的escape子句声明不同的转码字符。如果选定的转义符出现在数据中，则可以使用它来转义。
例如，假设您有一个包含三列的表，并且希望加载以下三个字段：
免费前往A、B
5.89
特价“1.79”
指定的分隔符字符为（逗号），指定的转义字符为“（双引号）。数据文件中的格式化行如下所示：

"Free trip to A,B","5.89","Special rate ""1.79"""   
1

数据中包含逗号字符的数据值用双引号括起来。作为数据一部分的双引号用双引号转义，即使字段值用双引号括起来。
将整个字段嵌入一组双引号中可以确保保留前导和尾随空格字符：

"Free trip to A,B ","5.89 ","Special rate ""1.79"" "

1
2

在CSV模式下，所有字符都是有意义的。由空格或除DELIMITER之外的任何字符包围的带引号的值包括这些字符。如果从将CSV行填充为固定宽度的系统导入数据，这可能会导致错误。在这种情况下，在将数据导入Greenplum数据库之前，对CSV文件进行预处理以删除尾部空白。

字符编码

字符编码系统由一种代码组成，该代码将字符集中的每个字符与其他字符（如数字序列或八位字节）配对，以便于数据传输和存储。Greenplum数据库支持多种字符集，包括ISO 8859系列等单字节字符集和EUC（扩展UNIX代码）、UTF-8和Mule内部代码等多字节字符集。服务器端字符集是在数据库初始化期间定义的，UTF-8是默认值，可以更改。客户端可以透明地使用所有受支持的字符集，但不支持在服务器中使用少数字符集作为服务器端编码。当将数据加载或插入Greenplum数据库时，Greenplus会将数据从指定的客户端编码透明地转换为服务器编码。当将数据发送回客户端时，Greenplum将数据从服务器字符编码转换为指定的客户端编码。
数据文件必须采用Greenplum数据库识别的字符编码。有关支持的字符集，请参阅《Greenplum数据库参考指南》。包含无效或不受支持的编码序列的数据文件在加载到Greenplum数据库时会遇到错误。
注意：对于在Microsoft Windows操作系统上生成的数据文件，在加载到Greenplum数据库之前，运行dos2unix系统命令以删除所有仅限Windows的字符。
注意：如果更改现有gpload控制文件中的ENCODING值，则必须手动删除使用先前ENCODING配置创建的任何外部表。如果REUSE_tables设置为true，gpload不会删除并重新创建外部表以使用新的ENCODING。

可写外表

Unloading Data Using a Writable External Table
可写外部表只允许INSERT操作。必须授予表的INSERT权限，才能访问非表所有者或超级用户的用户。例如：

GRANT INSERT ON writable_ext_table TO admin;
1

要使用可写外部表卸载数据，请从源表中选择数据并将其插入可写外部表格。结果行输出到可写外部表。例如：

INSERT INTO writable_ext_table SELECT * FROM regular_table;
1

常见加载问题

1. ERROR:segment reject limit reached,aborting operation(seg0 slice1 192.168.0.112:40000 pid=xxxx)

DETAIL: Last error was:extra data after last expected column
CONTEXT: External table xxx ,line N/A of gpfdist://xxxxxxx:"20220101, 10000001ABC, 2222, -.000, .00 "

描述：1.txt文件内容如下,文件utf8编码格式
20220101, 10000001ABC, 2222, -.000, .00

外表创建

drop external table if exists tmp.test restrict;

create external table tmp.test(like sddl.test)
location('gpfdist://etlhost:8080/data/import/test.txt')
FORMAT 'TEXT' (delimiter ',' null '' escape 'off')
ENCODING 'GB18030'
LOG ERRORS SEGMENT REJECT LIMIT 10;
1
2
3
4
5
6
7

解决方法：
检查字段数量、长度、文件的内容

2. 外表创建完查询报错

创建外表完成后，并不能说明外表是正确的。
当查询外表时候，若外表有问题则默认将错误信息记录。

# 查询外表，若有问题则查询日志表
select * from tmp.test;
# 查询日志表，若有记录说明外表有问题
select * from gp_read_error_log('tmp.test') where cmdtime='2022-01-01 00:00:00';
# errmsg记录错误信息
# rawdata记录错误数据

1
2
3
4
5
6
7

3. DETAIL: Last error was: extra data after last expected column

gpfdist加载时候，经常遇到上述问题。
排查文件内容和表结构字段的数量、顺序及类型是否一致

按分隔符方式导出文件，注意转义符问题，如文件存在\线，加载失败。
delimiter E’\x0f’ NULL as ’ ’ ESCAPE as ‘OFF’ FILL MISSING FIELDS
ESCAPE as 'OFF’关闭转义

数据中有双引号
sed命令替换掉即可:# sed -i ‘s/"//g’ file

外表错误处理-官网

有效的方式，将字段分隔符设置不可见字符。

4. 0x00 latin1

加载后查询提示0x00问题，postgresql无法存储0x00。
数据库UTF8，导出文件us-ascii。通过gpfdist加载按分隔符encoding指定ISO-8859-1加载之前通过sed替换掉文件的0x00为空。

# perl
system("sed -i 's/\\\\x00//g' $filename");
1
2

#shell
sed -i "s/\x00//g" $filename
1
2

ASCII 码值

ascii table