在使用外部表装载文本时，如果外部表的列数据类型定义成数值型，但是文本中的对应的数据存在空值，可能会导致该记录被抛弃。比如
基础表:
create table dbo.test_csv (
c1 integer,
c2 real,
c3 double precision,
c4 numeric (3,2),
c5 char(1),
c6 varchar(32)
) distributed by (c1)
;
create table dbo.test_csv (
c1 integer,
c2 real,
c3 double precision,
c4 numeric (3,2),
c5 char(1),
c6 varchar(32)
) distributed by (c1)
;
外部表:
create external table ext.test_csv (
like dbo.test_csv
) location (
'gpfdist://localhost:8081/test_csv.txt'
) format 'CSV' (
header
delimiter ','
NULL as ''
quote '"'
) log errors into err.test_csv
segment reject limit 10 percent
;
原始文本文件内容:
"c1","c2","c3","c4","c5","c6"
"1","2","3","4.56","a","xyz"
"2","","3","4.56","a","xyz"
"c1","c2","c3","c4","c5","c6"
"1","2","3","4.56","a","xyz"
"2","","3","4.56","a","xyz"
ncc=# select * from ext.test_csv;
NOTICE: Found 1 data formatting errors (1 or more input rows). Rejected related input data.
c1 | c2 | c3 | c4 | c5 | c6
----+----+----+------+----+-----
1 | 2 | 3 | 4.56 | a | xyz
(1 row)
可以看到，访问记录时抛掉了含有空值的记录。
ncc=# select * from err.test_csv;
cmdtime | relname | filename | linenum | bytenum | errmsg | rawdata | rawbytes
------------------------------+----------+------------------------------------------------------------------------+---------+---------+---------------------------------------------------+-----------------------------+----------
2011-03-13 23:34:34.07102-04 | test_csv | gpfdist://localhost:8081/test_csv.txt [/Users/rixonj/POC/test_csv.txt] | 3 | | invalid input syntax for type real: "", column c2 | "2","","3","4.56","a","xyz" |
(1 row)
cmdtime | relname | filename | linenum | bytenum | errmsg | rawdata | rawbytes
------------------------------+----------+------------------------------------------------------------------------+---------+---------+---------------------------------------------------+-----------------------------+----------
2011-03-13 23:34:34.07102-04 | test_csv | gpfdist://localhost:8081/test_csv.txt [/Users/rixonj/POC/test_csv.txt] | 3 | | invalid input syntax for type real: "", column c2 | "2","","3","4.56","a","xyz" |
(1 row)
很多用户会选择把外部表的c2列定义成varchar类型，来解决问题。我们也可以写成下列形式进行处理
select
c1,
case when c2='' then null else c2end::real as c2,
c3,
c4,
c5,
c6
from ext.test_csv
c1,
case when c2='' then null else c2end::real as c2,
c3,
c4,
c5,
c6
from ext.test_csv
c1 | c2 | c3 | c4 | c5 | c6
----+----+----+------+----+-----
2 | | 3 | 4.56 | a | xyz
1 | 2 | 3 | 4.56 | a | xyz
(2 rows)
----+----+----+------+----+-----
2 | | 3 | 4.56 | a | xyz
1 | 2 | 3 | 4.56 | a | xyz
(2 rows)
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/25548387/viewspace-707320/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
GREENPLUM数据库软件下载
请登录后发表评论
登录
全部评论
