GP中外部表数据装载

最新推荐文章于 2024-09-26 09:44:43 发布

原创最新推荐文章于 2024-09-26 09:44:43 发布

· 5.6k 阅读

3 ·

版权

GP开发基础专栏收录该内容

13 篇文章

订阅专栏

CREATE EXTERNAL TABLE ext_expenses
( name text, date date, amount float4, description text )
LOCATION ('gpfdist//etlhost:8081/*','gpfdist//etlhost:8082/*')
FORMAT 'TEXT' (DELIMITER '|')
ENCODING ’UTF-8’
LOG ERRORS INTO ext_expenses_loaderrors
SEGMENT REJECT LIMIT 10000 ROWS ;

处理装载错误数据
对于可读外部表来说，最常见的应用就是SELECT数据并装载到常规数据库表。代表性的操作为CREATE TABLE AS SELECT或INSERT INTO SELECT命令，这里的SELECT语句查询外部表。缺省情况下，如果外部表包含错误数据，整个命令失败，且没有数据被装载到目标数据库表。为了将正确的数据装载并隔离错误的数据，可以在定义可读外部表时使用CREATE EXTERNAL TABLE命令结合使用SEGMENT REJECT LIMIT子句。
l 拒绝限制count参数可用于指定记录数(缺省)，或者使用PERCENT指定记录百分比。
l 保存错误记录以备将来的检查，使用LOG ERRORS INTO子句指定错误记录日志表。

定义一个带有单条记录错误隔离的外部表
可以使用SEGMENT REJECT LIMIT指定每个Segment Instance可接受的错误记录数，可以按照行数设置或者百分比设置。如果错误记录达到了SEGMENT REJECT LIMIT的设置，整个外部表操作失败，且没有数据被处理。需要注意的是，错误限制是Segment Instance的设置，为不是整个集群的设置。如果SEGMENT REJECT LIMIT没有达到，所有正确的记录会被处理，而错误的记录被隔离到错误日志表中(如果指定了错误日志表)。下面的例子使用了LOG ERRORS INTO子句，因此，任何包含错误格式的记录都会被记录到错误日志表err_expenses中。
例如：
CREATE EXTERNAL TABLE ext_expenses ( name text,
date date, amount float4, category text, desc text )
LOCATION ('gpfdist://etlhost-1:8081/*',
'gpfdist://etlhost-2:8082/*')
FORMAT 'TEXT' (DELIMITER '|')
LOG ERRORS INTO err_expenses SEGMENT REJECT LIMIT 10 ROWS;
在使用了SEGMENT REJECT LIMIT后，外部表将以单条记录错误隔离模式扫描数据。这对于使用CREATE TABLE AS SELECT和INSERT INTO SELECT装载数据时隔离错误记录是很有帮助的。单条记录错误隔离模式仅适用于外部表记录格式错误。例如，多余或缺失列、错误数据类型的列、错误的客户端编码。约束错误不会被检查，不过在使用外部表装载数据时可以通过限制SELECT外部表的数据来过滤约束错误。例如，要排除重复记录错误：
=# INSERT INTO table_with_pkeys
SELECT DISTINCT * FROM external_table;
查看错误日志表中出错记录
若使用了单条记录错误隔离模式，任何格式错误的记录会被隔离记录到一个错误日志表中，该错误日志表包含如下的列属性：

字段名类型描述
cmdtime timestamp 错误出现的时间
relname text 扫描的外部表名称或者COPY命令的目标表名称
filename text 出现该错误的被装载文件名
linenum int 使用COPY命令时装载文件的行号，对于file://协议和gpfdist://协议的外部表，对应出错文件的行号
bytenum int 如果使用的是gpfdist://协议，记录的是错误发生时的byte偏移量。由于TEXT文件使用块处理方式，无法记录行号。CSV是按行处理的，所以其可以记录行号
errmsg text 出错描述信息
rawdata text 被拒绝记录的原始数据
rawbytes bytea 如果是数据库的编码与客户端编码无法转换，将无法记录rawdata。取而代之的是记录这些字符的8进制ASCII编码

可以使用SQL命令查询错误日志表的记录。例如：
=# SELECT * from err_expenses;
在错误日志表数据中识别无效CSV文件
如果逗号分割的(CSV)文件存在无效格式，错误日志表的rawdata列中可能会包含多个组合的记录行。比如，某个字段的关闭引号缺失，接下来的一行也会被当作当前行的记录。如果发生了这种情况，GP会在字符解析达到64K时自动停止，并将这些数据记录到错误日志表中作为一条记录，重置引号标识，然后继续解析。如果装载处理中发生了3次，该文件将被作为无效文件处理，并且整个装载失败，获得一个提示信息”rejected N or more rows”。查看相关的”CSV格式文件转义”章节获取更过关于在CSV文件中正确使用引号的信息。
在表之间移动数据
可以使用CREATE TABLE AS或者INSERT…SELECT来装载外部表或者WEB外部表到另一个常规数据库表，且数据被外部表或者WEB外部表以并行(如果WEB外部表也定义了并行性)的方式被装载。

如果外部表文件或者WEB外部表数据源包含错误，任何读取操作都会失败。类似与COPY，从外部表或者WEB外部表装载的整体操作要么全部成功要么整体失败。