netezza - NZLOAD 正在工作，而 Netezza 中的外部表因错误输入行数达到 maxerrors 而失败

Question

Netezza 外部表间歇性出现问题。

外部表因系统本身生成的文件而失败(意味着外部表生成的文件不是来自其他来源)。但我们尝试通过 nzload 实用程序将相同的文件加载到另一个表，并且该文件运行正常，没有任何问题.这个问题不一致，大多数时候无法重现。

CREATE EXTERNAL TABLE SP_PORTFOLIO_EXT_DATA_6128_140
(
    CLIENT_ID INTEGER,
    CONFIG_ID INTEGER,
    SCENARIO_ID INTEGER,
    PORTFOLIO_ID INTEGER,
    PORTFOLIO_NAME CHARACTER VARYING(200),
    CUSTOM13 CHARACTER VARYING(600),
    CUSTOM12 CHARACTER VARYING(500),
    CUSTOM11 CHARACTER VARYING(500),
    CUSTOM10 CHARACTER VARYING(500),
    CUSTOM9 CHARACTER VARYING(500),
    CUSTOM8 CHARACTER VARYING(500),
    CUSTOM7 CHARACTER VARYING(500),
    CUSTOM6 CHARACTER VARYING(2000),
    CUSTOM3 CHARACTER VARYING(500),
    CUSTOM2 CHARACTER VARYING(3000),
    CUSTOM1 CHARACTER VARYING(500),
    CREATIVE CHARACTER VARYING(512),
    PLACEMENT CHARACTER VARYING(5000),
    IMPRESSIONS NUMERIC(38,0),
    CLICKS NUMERIC(38,0),
    CONVERSIONS INTEGER,
    TRUE_CONVERSIONS NUMERIC(38,6),
    OPTMETRIC NUMERIC(38,6),
    LASTAD_OPTMETRIC NUMERIC(38,6),
    CURRSPEND NUMERIC(38,6)
)
USING
(
    DATAOBJECT('/san5/Netezza/CAR/CAR_ZEUS/SPBU/test/SP_PORTFOLIO_EXT_DATA_6128_140.csv')
    DELIMITER 254
    ESCAPECHAR '/'
    TIMESTYLE '24HOUR'
    LOGDIR '/tmp'
    Y2BASE 2000
    ENCODING 'internal'
);

命令成功完成

select COUNT(*) from SP_PORTFOLIO_EXT_DATA_6128_140;
ERROR [HY000] ERROR:  External Table : count of bad input rows reached maxerrors limit

NZLOAD方法

CREATE TABLE TEST_LOAD
(
    CLIENT_ID INTEGER,
    CONFIG_ID INTEGER,
    SCENARIO_ID INTEGER,
    PORTFOLIO_ID INTEGER,
    PORTFOLIO_NAME CHARACTER VARYING(200),
    CUSTOM13 CHARACTER VARYING(600),
    CUSTOM12 CHARACTER VARYING(500),
    CUSTOM11 CHARACTER VARYING(500),
    CUSTOM10 CHARACTER VARYING(500),
    CUSTOM9 CHARACTER VARYING(500),
    CUSTOM8 CHARACTER VARYING(500),
    CUSTOM7 CHARACTER VARYING(500),
    CUSTOM6 CHARACTER VARYING(2000),
    CUSTOM3 CHARACTER VARYING(500),
    CUSTOM2 CHARACTER VARYING(3000),
    CUSTOM1 CHARACTER VARYING(500),
    CREATIVE CHARACTER VARYING(512),
    PLACEMENT CHARACTER VARYING(5000),
    IMPRESSIONS NUMERIC(38,0),
    CLICKS NUMERIC(38,0),
    CONVERSIONS INTEGER,
    TRUE_CONVERSIONS NUMERIC(38,6),
    OPTMETRIC NUMERIC(38,6),
    LASTAD_OPTMETRIC NUMERIC(38,6),
    CURRSPEND NUMERIC(38,6)
)
DISTRIBUTE ON RANDOM;

# Loading data  from the same file using Nzload

nzload -host 10.200.29.30 -u xxxxx -pw xxxxx -db SPBU_REPORT_DB_TEST -t test_load -delim 254 -ctrlChars  -df /san5/Netezza/CAR/CAR_ZEUS/SPBU/test/SP_PORTFOLIO_EXT_DATA_6128_140.csv

Load session of table 'TEST_LOAD' completed successfully

[ja.prod@inet11026 ~]$ cat /san5/Netezza/CAR/CAR_ZEUS/SPBU/test/SP_PORTFOLIO_EXT_DATA_6128_140.csv|wc -l
191322

select count(*) from test_load;
191322

添加 nzlog

File Buffer Size (MB): 8                  Load Replay Region (MB): 0
  Encoding:              INTERNAL           Max errors:            1
  Skip records:          0                  Max rows:              0
  FillRecord:            No                 Truncate String:       No
  Escape Char:           '/'                Accept Control Chars:  No
  Allow CR in string:    No                 Ignore Zero:           No
  Quoted data:           NO                 Require Quotes:        No

  BoolStyle:             1_0                Decimal Delimiter:     '.'

  Disable NFC:           No
  Date Style:            YMD                Date Delim:            '-'
  Y2Base:                2000
  Time Style:            24HOUR             Time Delim:            ':'
  Time extra zeros:      No

Found bad records

bad #: input row #(byte offset to last char examined) [field #, declaration] diagnostic, "text consumed"[last char examined]
----------------------------------------------------------------------------------------------------------------------------
1: 25(184) [21, INT4] expected field delimiter or end of record, "0"[.]

Statistics

  number of records read:      25
  number of bad records:       1
  -------------------------------------------------
  number of records loaded:    0

  Elapsed Time (sec): 0.0

-----------------------------------------------------------------------------
Load completed at: 08-Oct-15 09:59:04 EDT

包含坏行的.nzbad数据(为了可读性，管道符号代表实际分隔符):

140|1305|6128||NULL|SEO|SEO|test.com/vehicledetail/detail/632888199/overview|SEO|SEO|SEO|SEO Brand|SEO Brand|best Tracking|Google(Seo)|SEO|Impression Tracker|Unknown|0|1|0|0.000000|0.000000|0.000000|0.000000

Answer 1

从 nzlog 中我们可以看出第 25 行加载失败。具体来说，当它尝试加载第 21 列时，它遇到一个非整数值。

日志显示它遇到了一个 0，然后是一个句点。因此数据可能有 0.0 或 0.1234 之类的值，无法作为整数加载。

bad #: input row #(byte offset to last char examined) [field #, declaration] diagnostic, "text consumed"[last char examined]
----------------------------------------------------------------------------------------------------------------------------
1: 25(184) [21, INT4] expected field delimiter or end of record, "0"[.]

使用您提供的 .nzbad 数据(为了便于阅读，此处使用“|”而不是实际的分隔符):

140|1305|6128||NULL|SEO|SEO|test.com/vehicledetail/detail/632888199/overview|SEO|SEO|SEO|SEO Brand|SEO Brand|best Tracking|Google(Seo)|SEO|Impression Tracker|Unknown|0|1|0|0.000000|0.000000|0.000000|0.000000

我注意到的一件事是你有一个 varchar 字段，其中包含“/”。外部表和 nzload 方法之间的区别之一是外部表指定转义字符“/”，而 nzload 则没有。

您会发现您的数据“test.com/vehicledetail/detail/632888199/overview”将加载为“test.comvehicledetaildetail632888199overview”，因为“/”字符将被删除，因为它们本身并未转义(例如“/”/')。

如果“/”直接位于数据中的列分隔符之前，它将指示它将列分隔符视为数据的一部分，并且会认为数据中的第 22 列实际上是表中的第 21 列这与我们在这里看到的相符。