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

妖狐艹你老母 2022-06-01 23:14 177阅读 0赞

背景：使用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

<table style="border-collapse:collapse; border-spacing:0px; table-layout:fixed; height:85px"> 
 <tbody style=""> 
  <tr style="background-color:grey"> 
   <td style="padding:0px; width:144px"><span style="color:rgb(230,230,230)">&nbsp;Product</span></td> 
   <td style="padding:0px; width:136px"><span style="color:rgb(230,230,230)">&nbsp;Version</span></td> 
  </tr> 
  <tr style=""> 
   <td style="padding:0px; width:144px">&nbsp;Pivotal Greenplum</td> 
   <td style="padding:0px; width:136px">&nbsp;4.2.x</td> 
  </tr> 
  <tr style=""> 
   <td style="padding:0px; width:144px">&nbsp;OS</td> 
   <td style="padding:0px; width:136px">&nbsp;All Supported OS</td> 
  </tr> 
 </tbody> 
</table>

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"

As can be seen from the example above, the source data file has one more column than the target table, resulting in the error described above.

官方给出的解决方案如下：

[Chloe Jackson]: https://discuss.pivotal.io/hc/en-us/profiles/401129022-Chloe-Jackson