3

Intermittent issues with Netezza External Table .

External table is failing with the file generated by the system itself(Meant the file generated by external table not from other sources).But we tried to load the same file via nzload utility to another table and that is working without any issues.This issue is not consistent and unable to reproduce most of times.

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'
);

The command completed successfully

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

NZLOAD method

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

Adding the 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

The .nzbad data containing the bad row (with pipe symbols standing in for the actual delimiter for readbility):

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
ScottMcG
  • 3,867
  • 2
  • 12
  • 21
Anoop R
  • 545
  • 3
  • 8
  • 19
  • Two things spring to mind here. The first is that your external table has a LOGDIR of /tmp. The answer as to why the external table load is failing is in the .nzlog file in that directory. That information is criticial to debugging your issue. The second is that you are using different options with nzload than with the external table. For example, you use CtrlChars on nzload and not on the ext table, and you use escapchar on the external table but not the nzload. Share what is your /tmp/SP_PORTFOLIO_EXT_DATA_6128_140.*.nzlog and we can help you diagnose further. – ScottMcG Oct 07 '15 at 15:53
  • Thanks for the reply ScottMcg.But here the case is little bit weird.This external table creation is running via NZSQL procedure call and it is working with out failure for 99.9% of time.This 6128 in the file name indicates a specific scenario name and if we flush out this file and recreate the same file with 6128 metadata using same escape characters and all external table won't fail.That's why at first I told issue as intermittent .Anyways I will share the log as you said.Cheers – Anoop R Oct 07 '15 at 17:12

3 Answers3

1

From the nzlog we can tell that the load is failing on the 25th row. Specifically, as it tries to load the 21st column it encounters a value that is not an integer.

The log shows that it encounters a 0 which is then followed by a period. So the data likely has something like 0.0 or 0.1234 which can't be loaded as an integer.

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"[.]

With the .nzbad data you have provided (here with '|' instead of your actual delimiter for readability purposes):

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

One thing I notice here is that you have a varchar field with '/' in it. One of the differences between your external table and your nzload approach is that the external table specifies escapechar '/' while the nzload doesn't.

You will find that your data 'test.com/vehicledetail/detail/632888199/overview' will be loaded as 'test.comvehicledetaildetail632888199overview' because the '/' characters will be removed as they are not themselves escaped (e.g. '//').

If a '/' directly preceded a column delimiter in the data, it will direct it to consider the column delimeter to be part of the data instead, and will think column 22 in the data is actually for column 21 in the table which would match what we are seeing here.

ScottMcG
  • 3,867
  • 2
  • 12
  • 21
  • ScottMcG This file is created by the same external table by the system with same definition and as I explained most of the time system is working with out error. Badfile as follows and 21st element is 0 which supposed to load without error 140�1305�6128��NULL�SEO�SEO�http://www.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. And the main doubt remains why NZLOAD is running without fail – Anoop R Oct 08 '15 at 17:17
  • I hear what you are saying, but the only way we can figure out what's happening is to follow the diagnostic path. If it works intermittently, perhaps it's the output of the file that's behaving intermittently, and the load is doing the same thing each time but with different data. Let's look at the nzbad and see what we can see. – ScottMcG Oct 08 '15 at 17:18
  • [ja.prod@inet11026 log]$ cat SP_PORTFOLIO_EXT_DATA_6128_140.SPBU_REPORT_DB_TEST.nzbad 140�1305�6128��NULL�SEO�SEO�http://www.tests.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 [ja.prod@inet11026 log]$ .This is the bad file available – Anoop R Oct 08 '15 at 17:25
  • If that works for you don't forget to mark it as answered – ScottMcG Oct 08 '15 at 21:25
  • escapechar '\' also won't work with my dataset.If that is the case it would be better to avoid that option.Correct me if I am wrong. – Anoop R Oct 09 '15 at 06:11
  • You are correct. If your data set was not created with an escapechar there is no use in specifying one – ScottMcG Oct 09 '15 at 13:33
0

ScottMcG as you said I compared the nzlog files generated by Nzload and External table and found that escape character is the only difference.So I commented out that part and tried again and the things working .

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 TIMESTYLE '24HOUR' LOGDIR '/tmp' Y2BASE 2000 ENCODING 'internal' );

select count(*) from SP_PORTFOLIO_EXT_DATA_6128_140;

191322.

Anoop R
  • 545
  • 3
  • 8
  • 19
0

The data type must be changed as follows: CHARACTER VARYING replace for VARCHAR/ NVARCHAR

CREATE TABLE TEST_LOAD
(
CLIENT_ID INTEGER,
CONFIG_ID INTEGER,
SCENARIO_ID INTEGER,
PORTFOLIO_ID INTEGER,
PORTFOLIO_NAME VARCHAR(200),
CUSTOM13 VARCHAR(600),
CUSTOM12 VARCHAR(500),
CUSTOM11 VARCHAR(500),
CUSTOM10 VARCHAR(500),
CUSTOM9 VARCHAR(500),
CUSTOM8 VARCHAR(500),
CUSTOM7 VARCHAR(500),
CUSTOM6 VARCHAR(2000),
CUSTOM3 VARCHAR(500),
CUSTOM2 VARCHAR(3000),
CUSTOM1 VARCHAR(500),
CREATIVE VARCHAR(512),
PLACEMENT VARCHAR(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;
keikai
  • 14,085
  • 9
  • 49
  • 68
aura viviana duarte hernandez
  • 1