Showing posts with label nzload. Show all posts
Showing posts with label nzload. Show all posts

Wednesday, March 29, 2017

Netezza: MERGE SQL statement

Netezza NPS release 7.2.1 comes with new MERGE SQL statement. Here are some usage examples.

-----------------------------------------------
-----  Using heap table ----
-----------------------------------------------
MERGE INTO my_target_table a
     USING my_source_table b
ON   a.UID   =  b.UID
WHEN MATCHED  AND a.DATAFIELD <> b.DATAFIELD THEN
       UPDATE SET a.DATAFIELD = b.DATAFIELD, a.UPDATED = CURRENT_TIMESTAMP
WHEN NOT MATCHED  THEN
       INSERT VALUES (b.UID,B.DATAFIELD,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP)
;
-----------------------------------------------
-----  Using TET (transient external table) ---
-----------------------------------------------
MERGE INTO my_target_table A
     USING (SELECT * FROM external '/tmp/data.csv'
                                  ( UID       INT4
                                  , DATAFIELD CHARACTER VARYING(64))
                     using (Delim ',' SkipRows 0 MaxErrors 300 Logdir '/tmp'  )) B
ON   a.UID   =  b.UID
WHEN MATCHED  AND a.DATAFIELD <> b.DATAFIELD THEN
       UPDATE SET a.DATAFIELD = b.DATAFIELD ,a.UPDATED = CURRENT_TIMESTAMP
WHEN NOT MATCHED  THEN
       INSERT VALUES (b.UID,B.DATAFIELD,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP)
;
-----------------------------------------------
-----  Using intermediate TEMP table ---
-----------------------------------------------
create temp table TEMP_source_table
as select * from external '/tmp/data.csv' (UID INT4, DATAFIELD CHARACTER VARYING(64)) 
   using (Delim ',' SkipRows 0 MaxErrors 300 Logdir '/tmp')
;
MERGE INTO my_target_table   A
     USING TEMP_source_table B
ON   a.UID   =  b.UID
WHEN MATCHED AND a.DATAFIELD <> b.DATAFIELD THEN
       UPDATE SET   a.DATAFIELD = b.DATAFIELD,a.UPDATED = CURRENT_TIMESTAMP
WHEN NOT MATCHED  THEN
       INSERT VALUES (b.UID,B.DATAFIELD,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP)
;
-----------------------------------------------

Notice, Aginity Workbench 4.8.0 report only INSERTED records statistics, but UPDATED.  The nzsql reports both UPDATED and INSERTED records affected. 


REFER: 


Friday, April 1, 2016

Netezza: nzload how to handle NUL 0x00 and non-printable characters

PROBLEM:
nzload  responds with errors:

bad #: input row #(byte offset to last char examined) [field #, declaration] diagnostic, "text consumed"[last char examined]
----------------------------------------------------------------------------------------------------------------------------
1: 58351(52) [4, NVARCHAR(1000)] binary zero byte - ignoreZero not enabled, "OOL-US-TDMA"[0x00<NUL>]
2: 59487(104) [4, NVARCHAR(1000)] found control characters and ctrlChars option is off, "SW-BU-BOOST-US:5500000|SW-BU-BOOST-DS:37500000|#K"[0x19]
3: 146957(60) [4, NVARCHAR(1000)] binary zero byte - ignoreZero not enabled, "OOL-US"[0x00<NUL>]
4: 858805(53) [4, NVARCHAR(1000)] binary zero byte - ignoreZero not enabled, "BOOST-US"[0x00<NUL>]



SOLUTION: 

Adding nzload option -ignoreZero YES ingnores loading NUL charactes. They will be stripped out.
Adding nzload option -ctrlChars loads control characters into a table  

nzload
-host 10.10.10.100
-u  db_user
-pw db_password
-db MY_DATABASE
-t  MY_TABLE
-delim ";"
-quotedValue NO
-encoding INTERNAL
-df /usr/local/data/MY_TABLE.data
-lf /usr/local/data/$(date +'%Y%m%d%H%M%S')_$(hostname)_MY_TABLE.nzload.log
-bf /usr/local/data/$(date +'%Y%m%d%H%M%S')_$(hostname)_ MY_TABLE.nzload.bad
-maxErrors 2
-DateStyle 'DMY'
-DateDelim ' '
-TimeStyle '24HOUR'
-TimeDelim ':'
-fillRecord
-ignoreZero YES     <-- This takes care of "binary zero byte - ignoreZero not enabled"
-ctrlChars          <-- This takes care of "found control characters and ctrlChars option is off"



INVESTIGATION AND DETAILS: 


Initial lookup using cat command does not show NUL characters.


vi shows null characters but it's not conclusive: 


Notepad++ comes at help not only displaying non-printable ASCII characters but also identifying their type.