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

This entry was posted in SQL Tricks and tagged , , , , , . Bookmark the permalink.

One Response to Returning and Saving Large Result Sets Locally

  1. New to Netezza says:

    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 Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>