Extra Data after Last Expected Column：hawq建外表load数据报错

背景：使用sqoop import 才oracle抽数据入HDFS后，通过pxf建立hawq外表，查询数据报错

解决思路：1、从报错上看应该是数据源分割后字段数比目标表字段多，且pgadmin3里报错还出现源数据异常的一条记录，去源头oracle查看对应的记录，发现地段内部包含分隔符。

2、重新执行sqoop import 并用 “ \t “分隔，问题解决。

Loading Data from a Flat File “ERROR: Extra Data after Last Expected Column”

Chloe Jackson

March 23, 2017 16:26

Environment

Product	Version
Pivotal Greenplum	4.2.x
OS	All Supported OS

Symptom

Loading data from flat files result in the following error message:

ERROR: extra data after last expected column

Cause

You will encounter this error if your source data has more columns than the target table.

Resolution

You must ensure that the source data and table columns align.

Example:

-- Target table

ddata=# \d emp
      Table "public.emp"
Column  " Type    " Modifiers
--------+---------+-----------
id      " integer "
dept    " integer "
role    " integer "
Distributed by: (id)

-- Source data file

$ cat /tmp/load.dat
1"2"3"4
1"2"3"4
1"2"3"4
1"2"3"4
1"2"3"4
1"2"3"4

--Error

ddata=# copy emp from '/tmp/load.dat' with delimiter as '"';
ERROR: extra data after last expected column (seg0 sdw1:55001
pid=12476)
CONTEXT: COPY emp, line 1: "1"2"3"4"