Returning and Saving Large Result Sets Locally

Some of the common SQL Query tools like Squirrel Sql can have issues when it tries to return large result sets.  This seems to be because it needs to hold them in memory.  There are times you need to just bring back large result sets in a file, local to your client computer.  Believe it or not you can create an external table back to a local file via JDBC or ODBC.

If you are using a JDBC SQL tool like Squirrel , then this syntax works.

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

Just run this in a query window and go find your file in the directory you specified.

Doing this on an ODBC tool like Aginity Workbench (plug to my buddy Oleg, where I got this method!) is the same with just ODBC instead of JDBC…

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

1 comment on this post.
  1. New to Netezza:

    Just wanted to say this is outstanding. I wasn’t sure of the exact syntax to save to local and this also bypasses registering a table within the system which is nice because you don’t need to drop it later (although you probably end up creating a table in the first place before exporting so you have the proper columns).

Leave a comment