如何在Netezza NZSQL中使用制表符分隔符输出文件

Question

我试图使用NZSQL输出一些文件，但无法输出为选项卡分隔的文件。在新西兰工作过的人能在下面的命令中分享你的想法吗？

试过:-

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

Answer 1

若要将选项卡指定为分隔符，请将$与-F选项结合使用。

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

nzsql -h输出中记录了这一点。

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)
...

如果您有大量数据，我建议您使用外部表，因为它们的性能更好。

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