使用nzsql将数据导出到CSV [英] Export data to CSV using nzsql

问题描述

我想从netezza将数据导出为CSV.数据包含数百万行的记录.数据应在反逗号内，并且应以^作为定界符. 例如:"a" ^"b" ^"c"

I would like to export data as CSV from netezza. The data consists of millions of lines of records. The data should be within inverted commas and should have ^ as delimiter. Eg: "a"^"b"^"c"

推荐答案

每当您要从Netezza导出数据时，都将希望使用其外部表功能.

Whenever you want to export data from Netezza, you will want to use its external tables functionality.

如果要导出到本地安装到Netezza主机的文件系统，则可以使用以下方法:

If you are exporting to a filesystem mounted locally to the Netezza host, you could use this:

CREATE external TABLE '/tmp/test_export.txt' USING (delimiter '^') AS
SELECT *
FROM test_export;

如果要通过Aginity Workbench之类的工具通过JDBC，ODBC或OLE-DB连接到Netezza，并且想要将数据本地导出到工作站，则可以使用以下方法:

If you are connecting to Netezza via JDBC, ODBC, or OLE-DB via tool like Aginity Workbench, and want to export the data locally to your workstation, you could use this:

CREATE external TABLE 'c:\test_export.txt' USING (delimiter '^' remotesource odbc) AS
SELECT *
FROM test_export;

不幸的是，没有外部表选项可以让您也将每一列都用引号引起来.您将必须在SQL中使用以下连接来明确地做到这一点:

Unfortunately, there is is no external table option that will allow you to also wrap every column in quotation marks. You will have to do that explicitly with a concatenation in the SQL like this:

CREATE external TABLE 'c:\test_export.txt' USING (delimiter '^' remotesource odbc) AS
SELECT  '"' || col1 || '"',
        '"' || col2 || '"'
FROM test_export;

您还可以将nzsql CLI界面与以下选项一起使用，以实现相似的功能，但是速度要慢得多.例如，在我的系统上，使用外部表方法导出大约200万行，这将创建一个大小约为3.5 GB的导出文件，需要20秒.使用CLI方法需要3秒钟约180秒.

You can also use the nzsql CLI interface with the following options to achieve something similar, but it is quite a bit slower. For example, on my system, using the external table method to export about 2 million rows, which creates an export file about 3.5 GB in size, takes 20 seconds. With the CLI method it took 3about 180 seconds.

nzsql -d DB_NAME -F "^" -t -A -o export.txt  -c "select * from test_export" 

这篇关于使用nzsql将数据导出到CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！