如何在greenplum中的引号字段中使用逗号从csv文件创建外部表

Question

我正试图从csv创建外部表，如下所示：

CREATE EXTERNAL TABLE hctest.ex_nkp
(
a text,
b text,
c text,
d text,
e text,
f text,
g text,
h text
)
LOCATION ('gpfdist://192.168.56.111:10000/performnkp.csv')
FORMAT 'CSV' (DELIMITER ',' HEADER);

csv由逗号(，(分隔，如下所示：

"Subject Username","Form Title","Form Start Date","Form End Date","Competency Name","Competency Description","Core Competency","Competency Official Rating"
"90008765","Performance Review - 2nd Semester 2019 for Ely Eisley","01/01/2019","31/12/2019","1. Uncompromising Integrity","<p>High ethical standards, low tolerance of unethical conduct.</p>","Yes","3"
"90008766","Performance Review - 2nd Semester 2019 for Ely Eisley","01/01/2019","31/12/2019","2. Team Synergy","<p>Passionately work together, ensuring completeness, to achieve common goals.</p>","Yes","3"
"90008767","Performance Review - 2nd Semester 2019 for Ely Eisley","01/01/2019","31/12/2019","3. Simplicity","<p>We do our utmost to deliver the easy to use solutions, exceeding customers&#39","","
"90008768","Performance Review - 2nd Semester 2019 for Ely Eisley","01/01/2019","31/12/2019","4. Exceptional Performance","<p>Highest level of performance, with a heart for people.</p>","Yes","3"

我发现了错误：

ERROR:  extra data after last expected column  (seg0 slice1 192.168.56.111:6000 pid=14121)
DETAIL:  External table ex_nkp, line 5 of file gpfdist://192.168.56.111:10000/performnkp.csv

我该如何解决此问题？

Answer 1

第4行中的CSV格式似乎不正确。请注意，在第4行的末尾，有一个引号，Greenplum将其解释为带有换行符的CSV字段。通过在第4行添加缺失的引号，我可以阅读Greenplum中的文件。

"Subject Username","Form Title","Form Start Date","Form End Date","Competency Name","Competency Description","Core Competency","Competency Official Rating"
"90008765","Performance Review - 2nd Semester 2019 for Ely Eisley","01/01/2019","31/12/2019","1. Uncompromising Integrity","<p>High ethical standards, low tolerance of unethical conduct.</p>","Yes","3"
"90008766","Performance Review - 2nd Semester 2019 for Ely Eisley","01/01/2019","31/12/2019","2. Team Synergy","<p>Passionately work together, ensuring completeness, to achieve common goals.</p>","Yes","3"
"90008767","Performance Review - 2nd Semester 2019 for Ely Eisley","01/01/2019","31/12/2019","3. Simplicity","<p>We do our utmost to deliver the easy to use solutions, exceeding customers&#39","","
"90008768","Performance Review - 2nd Semester 2019 for Ely Eisley","01/01/2019","31/12/2019","4. Exceptional Performance","<p>Highest level of performance, with a heart for people.</p>","Yes","3"

结果查询：

fguerrero=# select * from ex_nkp ;
NOTICE:  HEADER means that each one of the data files has a header row
a     |                           b                           |     c      |     d      |              e              |                                         f                                          |  g  | h
----------+-------------------------------------------------------+------------+------------+-----------------------------+------------------------------------------------------------------------------------+-----+---
90008765 | Performance Review - 2nd Semester 2019 for Ely Eisley | 01/01/2019 | 31/12/2019 | 1. Uncompromising Integrity | <p>High ethical standards, low tolerance of unethical conduct.</p>                 | Yes | 3
90008766 | Performance Review - 2nd Semester 2019 for Ely Eisley | 01/01/2019 | 31/12/2019 | 2. Team Synergy             | <p>Passionately work together, ensuring completeness, to achieve common goals.</p> | Yes | 3
90008767 | Performance Review - 2nd Semester 2019 for Ely Eisley | 01/01/2019 | 31/12/2019 | 3. Simplicity               | <p>We do our utmost to deliver the easy to use solutions, exceeding customers&#39  |     |
90008768 | Performance Review - 2nd Semester 2019 for Ely Eisley | 01/01/2019 | 31/12/2019 | 4. Exceptional Performance  | <p>Highest level of performance, with a heart for people.</p>                      | Yes | 3
(4 rows)

让我知道这是否有助于

Answer 2

您可以在外部表定义中指定"LOG ERRORS SEGMENT REJECT LIMIT 10"。这样，segment将跳过有错误的行。然后，您可以使用"select*from gp_read_error.log('xternal_table_name'(；"从示例中可以看出，字段中似乎有多余的逗号。尝试在HEADER.之后指定QUOTE">