Hi
In our application to migrate data to, from and between Netezzas (and other systems) we want to use external tables. Problem seems to be with NULL values and NULLVALUE option .
This option doesn't seem to work via ODBC nor NZSQL. Tried both on 7.X and 11.X.
ODBC on 7.X
# isql NZSQL
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> create external table testnull1 sameas testnull using ( REMOTESOURCE 'odbc' DATAOBJECT '/nzscratch/testnull' ENCODING 'utf8' DELIMITER ',' NULLVALUE '\N' ESCAPECHAR '\' SKIPROWS 0 CTRLCHARS true CRINSTRING true MAXERRORS 1 IGNOREZERO TRUE SOCKETBUFSIZE 838860800 INCLUDEZEROSECONDS TRUE);
SQLRowCount returns -1
SQL> insert into testnull1 select * from testnull;
SQLRowCount returns 13
SQL> \q
# cat testnull
1,
NZSQL on 7.X
SYSTEM.ADMIN(ADMIN)=> create external table testnull1 sameas testnull using (dataobject '/nzscratch/testnull' nullvalue '\N' RemoteSource 'nzsql' );
CREATE EXTERNAL TABLE
SYSTEM.ADMIN(ADMIN)=> insert into testnull1 values ('1', null);
INSERT 0 1
SYSTEM.ADMIN(ADMIN)=> \q
# cat /nzscratch/testnull
1|
NZSQL on 11.X
SYSTEM.ADMIN(ADMIN)=> create table testnull (col1 int, col2 int);
CREATE TABLE
SYSTEM.ADMIN(ADMIN)=> insert into testnull values ('1', null);
INSERT 0 1
SYSTEM.ADMIN(ADMIN)=> create external table testnull1 sameas testnull using (dataobject '/nzscratch/testnull' nullvalue '\N' RemoteSource 'nzsql' );
CREATE EXTERNAL TABLE
SYSTEM.ADMIN(ADMIN)=> insert into testnull1 values ('1', null);
INSERT 0 1
SYSTEM.ADMIN(ADMIN)=> \q
# cat /nzscratch/testnull
1|
I think that this option: nullvalue for external tables is not working - or there is some special - hidden registry trick to configure it?
------------------------------
Adam Matusewicz
------------------------------