Creating External Table takes long time

Question

I have a table called table B that as 28 million records that is in Netezza and I want to export it to a text file so that I can export the text file to the mysql server. When I run the command below, the SQL client hangs. I am using SquirrelSQL.

CREATE EXTERNAL TABLE '/Users/blah/A.txt'
USING(DELIM '\t' REMOTESOURCE 'JDBC')
AS
SELECT * FROM tableB;

I am not sure if this is supposed to be the case.

If you look at any activity monitor, network I/O, disk I/O etc, is it doing anything at all? What does `nzsession` tell you? — cairnz, May 30 '12 at 14:13

score 1 · Answer 1 · edited Jul 28 '12 at 07:35

Well I'm note sure if you are running Squirrel on a Window machine, but if you are you need to use backslash in the path, and you might need to escape them also. Below is an example I use in Squirrel running on a Window 7 laptop

CREATE EXTERNAL TABLE ‘C:\\Users\\ValuedCustomer\\customer dim dump.csv’ 
    USING ( DELIMITER ‘,’ Y2BASE 2000 ENCODING ‘internal’ REMOTESOURCE ‘JDBC’ ESCAPECHAR ‘\’ ) AS 
    SELECT CUSTOMER_FIRST_NAME, CUSTOMER_LASTNAME, CUSTOMER_ADDRESS, CUSTOMER_CITY, CUSTOMER_STATE 
    FROM DIM_CUSTOMER

You can find a little more info here on my blog

http://nztips.com/2012/07/returning-and-saving-large-result-sets-locally/

Creating External Table takes long time

1 Answers1