Netezza External Table Example

--external table is used to Import/export flat the file to Netezza system,
--since Netezza host is a Linux box with the Import/export file format could only being Linux format (use LF as row delimiter).

--several Key Configuration
ENCODING ' UTF8 '--default is INTERNAL, which are for ANSI file
SkipRows 1--SKIP row is SET to 1 IF the FILE has A HEADER ROW.
Format ' FIXED '--default is text, which means the delimited format
DELIMITER ', '--default is pipe, could only set one character
Escapechar '/'--if the file is delimited, and some fields has the delimiter in it, we could use the setting
Quotedvalue Double--default is no quotedvalue
LogDir '/mnt/hqaasan01/development_adhoc/'--specify the log dir, the log is very helpful when troubleshooting
;
----------------------------------------------------------------
--input case

--fetch data from files using a transient external table (TET)
SELECT * from EXTERNAL '/mnt/hqaasan01/development_adhoc/test.txt '
(id int,
Name varchar (50)
)
USING
(
--options
);

INSERT into <TABLE_NAME>
SELECT * from EXTERNAL '/mnt/hqaasan01/development_adhoc/aarp_1968_asi_adhoc_transaction/test.txt '
(id int,
Name varchar (50)
)
USING
(
--options
);

CREATE <TABLE_NAME> as
SELECT * from EXTERNAL '/mnt/hqaasan01/development_adhoc/aarp_1968_asi_adhoc_transaction/test.txt '
(id int,
Name varchar (50)
)
USING
(
--options
);

----------------------------------------------------------------
--create external table from files, the external tables could be used later
--drop TABLE dw_external_test;
CREATE EXTERNAL TABLE Dw_external_test
(
F1 NVARCHAR (50)
)
USING
(
DATAOBJECT ('/mnt/hqaasan01/development_adhoc/ansi.txt ')
ENCODING ' INTERNAL '
);

SELECT * from Ad_hoc. Dw_external_test LIMIT 100;

--note:it is isn't allowed to add data to External table
INSERT into Dw_external_test VALUES (' Vawang ');

--external table with FIXED FORMAT

CREATE EXTERNAL TABLE Stage_asi. Asi_regal_norwegian_20150209_external
(
RecordType VARCHAR (2),
CHID BIGINT,
Tdate VARCHAR (10),
)
USING
(
DATAOBJECT ('/mnt/hqaasan01/development_adhoc/test.txt ')
FORMAT ' FIXED '
LogDir '/tmp/test.txt '
LAYOUT
(
BYTES 2,
BYTES 19,
BYTES 10
)
);

DROP TABLE test_fixed;
CREATE EXTERNAL TABLE test_fixed
(
F1 CHAR (2),
F2 CHAR (2)
)
USING
(
DATAOBJECT ('/mnt/hqaasan01/development_adhoc/test.txt ')
LogDir '/tmp/test.txt '
FORMAT ' FIXED '
LAYOUT
(
Bytes 2,
Bytes 2
)
);

SELECT * from test_fixed;


-----------------------------------------
--output internal tables to external file
CREATE EXTERNAL TABLE dw_ex2 '/mnt/hqaasan01/development_adhoc/aarp_1968_asi_adhoc_transaction/nzouput.txt '
using (DELIM ', ')
As
SELECT * from Dw_test
;

INSERT INTO Dw_ex2 VALUES (1, ' Vawang ');
--------------------------------------------

Netezza External Table Example