[SOLVED] How to output a file using tab delimiter in Netezza NZSQL

How to output a file using tab delimiter in Netezza NZSQL

I am trying to output some files using NZSQL CLI but not able to output as tab delimited files. Can somebody who has worked on NZ share your thoughts on this below command.

Tried so far :-

nzsql  -o sample.txt -F=  -A -t -c  "SELECT * FROM DW_ETL.USER WHERE datasliceid % 20 = 2 LIMIT 5;"

Solution

To specify tab as the delimiter use $ in conjunction with the -F option.

nzsql  -o sample.txt -F $'\t'  -A -t -c  "SELECT * FROM DW_ETL.USER WHERE datasliceid % 20 = 2 LIMIT 5;"

This is documented in the nzsql -h output.

nzsql -h
This is nzsql, the IBM Netezza SQL interactive terminal.

Usage:
  nzsql [options] [security options] [dbname [username] [password]]

Security Options:
  -securityLevel       Security Level you wish to request (default: preferredUnSecured)
  -caCertFile          ROOT CA certificate file (default: NULL)

Options:
  -a                   Echo all input from script
  -A                   Unaligned table output mode (-P format=unaligned)
  -c <query>           Run only single query (or slash command) and exit
  -d <dbname>          Specify database name to connect to (default: system)
  -D <dbname>          Specify database name to connect to (default: system)
  -schema <schemaname> Specify schema name to connect to (default: $NZ_SCHEMA)
  -e                   Echo queries sent to backend
  -E                   Display queries that internal commands generate
  -f <filename>        Execute queries from file, then exit
  -F <string>          Set field separator (default: "|") (-P fieldsep=)
                       For any binary/control/non-printable character use '$'
                       (e.g., nzsql -F $'\t' // for TAB)
...

If you have a lot of data, I'd recommend using external tables instead as they perform better.

CREATE EXTERNAL TABLE '/tmp/sample.txt' USING (DELIMITER '\t') 
AS SELECT * FROM DW_ETL.USER WHERE datasliceid % 20 = 2 LIMIT 5;