Netezza Export Table Data to CSV format

  • Post author:
  • Post last modified:March 12, 2018
  • Post category:Netezza
  • Reading time:3 mins read

You can export the Netezza table in many ways. Netezza export table is done using either Netezza external table or Netezza nzsql commands.

Use of Netezza external table is fastest way to export Netezza table data compared to a Netezza nzsql command. Netezza nzsql with option -o is easy method compared to creating Netezza external tables.

Netezza Export Table using Netezza External Tables

You can use the Netezza transient external table to export the data table to flat file (CSV format). This feature is very handy if you are exporting Netezza table. You can export not just table output but any Netezza SQL query or statement. If you are using transient external table, you do not have to write the schema definition.

For Example;

CREATE EXTERNAL TABLE ‘/data/export.csv’
USING (delimiter ‘,’) AS
SELECT col1, col2, expr1, expr2, col3, col1 || col2
FROM your_table;

You can use above external table if you are exporting data to a mounted file system on local Netezza host. If you are running extract on remote machine that has JDBC, ODBC, or OLEDB installed then your ‘remoteshource odbc’ option in USING clause.

Read:

Netezza Export Table Columns in Quotation Mark

Unfortunately, there is no external table option that will allow you to wrap the Netezza table column data in quotation mark. You can concatenate the single or double quote explicitly in SQL statements. For example;

CREATE EXTERNAL TABLE ‘/data/export.csv’
USING (delimiter ‘,’ remotesource odbc) AS
SELECT ‘”’ || col1 ‘”’, ‘”’ || col2 ‘”’
FROM your_table;

Netezza Export Table using nzsql command

You can use Netezza nzsql CLI with the option -o to export the data to flat file. But it is slower compared to external tables.

For example;

nzsql -u username -pw password -db database -h host -F “,” -t -A -o ‘/data/export.csv’ -c “SELECT col1, col2, expr1, expr2, col3, col1 || col2 FROM your_table”
Tags: , ,

This Post Has 4 Comments

  1. Venkat June 26, 2017

    Hi Vithal, I have a question for you…

    We need to export data from Netezza, around 5-100 MB range every day basis. it is better to directly export the data using the CREATE EXTERNAL TABLE on the actual table or create a temp table based and export the data from temp table?

    Which is better..? when we are exporting data directly from the table/view will it lock the table for write operations ? Currently i am creating a temp table first and exporting the data from there , problem with this approach is everytime we are sending query we need to write two queries 1 for temp table, 1 for exporting..

    1. Vithal Sampagar June 28, 2017

      Hi Venkat,

      Thanks for the question.

      There are couple of ways to export data from table:

      1. CREATE EXTERNAL TABLE on source table and write to external file.
      2. Use nzsql command with -o option.

      The first option is faster compared to nzsql -o option.

      You can export the data without using temp tables. CREATE EXTERNAL TABLE on top of actual/base table. Neither CREATE EXTERNAL TABLE nor nzsql locks the table in Netezza.

      Thanks,
      Vithal

  2. Asu Mi September 14, 2017

    Hi Vithal,

    Thank you for sharing your knowledge on Netezza – it’s saved me many times!

    Is there a way to export table data (or Netezza script output) to Excel (.xls or .xlsx) format? I know SSIS is one way but we are trying to find another way. Prefer using Aginity Workbench/SQL server mgt studio if possible. Thank you for your time.

    1. Vithal Sampagar September 24, 2017

      Hi,

      Please use third-party tools (xlsxwriter, openpyxl etc) to convert CSV files to xls(x). Netezza does not support exporting data to excel format. Automate your process using Python or Shell script.

      Edit: here is the post that may help you – Netezza Export Table Data to Excel Format

      Thanks

Comments are closed.