Export Netezza Table Data to Linux Local File System

  • Post author:
  • Post last modified:March 9, 2018
  • Post category:Netezza
  • Reading time:4 mins read

In my other post ‘Netezza Export Table Data to CSV format’, we have seen how to export table to CSV format on a Netezza host machine. In this article, we will check how to export Netezza table data to Linux local file system.

We will discuss below topics in this post

  • How to install and configure Netezza ODBC drivers in remote machie
  • Export Netezza table data to Linux local file system using External Tables
  • How to export Netezza table using nzsql command line interface

Install Netezza ODBC Drivers

In other to connect to Netezza from your local system, you should install Netezza ODBC drivers into the Linux system.

You can read about ODBC installation in my other post:

Note that, you must have support service agreement with IBM to download any software related to Puredata systems powered by Netezza.

Export Netezza Table Data to Linux Local File System

If you have followed steps to install Netezza ODBC drives, you are ready to export data to Linux local file system.

There are couple of approached that you can use to export table to local system. You can either use Netezza external tables with REMOTESOURCE option or Netezza nzsql command options to export table data to required format.

Export Netezza Table Data to Linux Local File System using External Tables

Note that, this option is not supported in Netezza version 7.x. There is a enhancement request created to support this. However, in case enhancement is available, below is the details on using Netezza external tables on remote machines.

You can use the Netezza external table to export the data table to Linux local file system in the form of flat file. Unloading Netezza table to local system is very simple and fast using external tables.

For Example;

  1. Create external table by providing file path and name.

CREATE EXTERNAL TABLE exp_table

SAMEAS your_table USING (DATAOBJECT (‘/path/your_tabl.dat’) REMOTESOURCE ‘ODBC’);

Note the option ‘remoteshource odbc’ option in USING clause, this option is mandatory to run external table script on remote Linux system that has ODBC drivers configured.

  1. Insert data from Netezza table into above created external table

INSERT INTO exp_table SELECT * FROM your_table;

Netezza Export Table using nzsql command

You can use Netezza nzsql CLI with the option ‘-o /file-path/file.txt’ to export the data to flat file. But it is slower compared to external tables. This option is easy and widely used to copy data from Netezza to local system.

Related Reading:

For example;

nzsql -u username -pw password -db database -h host -F “,” -t -A -o ‘/data/export.csv’ -c “SELECT * FROM your_table”

Hope this helps, Let me know which option worked for you.

Tags: