Efficient Data Upload Method Via External Table

If you ever have tried to upload data to Netezza using convention query tool methods which create individual inserts, you will know this can be EXTREMELY SLOW. The way to upload data efficiently is to create an on-the-fly external table on your local datafile and use that in a CTAS (create table as) or an insert into a table. This is similar to the data download method in the previous post. In my example I create a temp table, but it could also be a perm table. This example is for comma delimited with backslash as the escape character, but you can change this to match your source file. If you cut and paste these examples, becareful your quotes don’t become open and close quotes.

Via ODBC Like Aginity Workbench

CREATE TEMP TABLE BUBBA5 AS

SELECT * FROM EXTERNAL ‘C:\\Users\\ValuedCustomer\\customer dim dump.csv’ ( CUSTOMER_FIRST_NAME VARCHAR(50) NOT NULL,

CUSTOMER_LAST_NAME VARCHAR(50) NOT NULL,

CUSTOMER_ADDRESS VARCHAR(200) NOT NULL,

CUSTOMER_CITY VARCHAR(50) NOT NULL,

CUSTOMER_STATE VARCHAR(20) NOT NULL )

USING ( QUOTEDVALUE ‘DOUBLE’ NULLVALUE ” CRINSTRING TRUE CTRLCHARS TRUE

LOGDIR ‘C:\\Users\\ValuedCustomer\\AppData\\Local\\Temp\\mylog.tmp\\’ DELIMITER ‘,’ Y2BASE 2000 ENCODING ‘internal’ REMOTESOURCE ‘ODBC’ ESCAPECHAR ‘\’ )

Via JDBC Like with Squirrel SQL

CREATE TEMP TABLE BUBBA5 AS

SELECT * FROM EXTERNAL ‘C:\\Users\\ValuedCustomer\\customer dim dump.csv’ ( CUSTOMER_FIRST_NAME VARCHAR(50) NOT NULL,

CUSTOMER_LAST_NAME VARCHAR(50) NOT NULL,

CUSTOMER_ADDRESS VARCHAR(200) NOT NULL,

CUSTOMER_CITY VARCHAR(50) NOT NULL,

CUSTOMER_STATE VARCHAR(20) NOT NULL )

USING ( QUOTEDVALUE ‘DOUBLE’ NULLVALUE ” CRINSTRING TRUE CTRLCHARS TRUE

LOGDIR ‘C:\\Users\\ValuedCustomer\\AppData\\Local\\Temp\\mylog.tmp\\’ DELIMITER ‘,’ Y2BASE 2000 ENCODING ‘internal’ REMOTESOURCE ‘JDBC’ ESCAPECHAR ‘\’ )

Source : http://nztips.com/