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/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s