Geek asks and answers

Create an external table with headers in netezza (postgres)

I create an external table as shown below

CREATE EXTERNAL TABLE '~\test.csv' 
USING ( DELIMITER ',' Y2BASE 2000 ENCODING 'internal' REMOTESOURCE 'ODBC' ESCAPECHAR '\' )
AS SELECT * FROM TEST_TABLE;

It works great. My question is:

Can header names be specified in column names in test.csv? Is this possible in Netezza or postgres.

I think we can do it with COPY, however I want to do it using the EXTERNAL TABLE command.

thank

+5
source share
4 answers

In version 7.2 of Netezza, you can now specify the IncludeHeader parameter to achieve this using external tables.

This is documented here.

+2
source

This is ugly, and it will probably add some amount of overhead to the request, but you can do something like this:

CREATE EXTERNAL TABLE ... AS
SELECT ColumnId, OtherColumn 
FROM (
    SELECT FALSE as IsHeader, ColumnId::VARCHAR(512), OtherColumn::VARCHAR(512)
    FROM TEST_TABLE
    UNION ALL
    SELECT TRUE as IsHeader, 'ColumnId', 'OtherColumn'
) x
ORDER BY IsHeader DESC
+1
source

, , qSlug ...

CREATE EXTERNAL TABLE 
'C:\HEADER_TEST.csv' USING 
(DELIMITER '|' ESCAPECHAR '\' Y2BASE 2000 REMOTESOURCE 'ODBC') AS

--actual query goes here.  Leave the 'data' field on there.
(select store_name, address1, 'data'
from yourtable
limit 10)
union
--field names go here.  Leave the 'header' field on there.
select 'store_name', 'address1', 'header'
from _v_dual
order by 3 desc

csv.

0
source

In fact, there is a way to include the title in the file if you have Netezza version 7.2 or higher.

The includeheader option, but the Aginity Workbench does not seem to highlight the includeheader, as if it were an option (at least in my version: 4.8).

    CREATE EXTERNAL TABLE '~\test.csv' 
    USING
( 
DELIMITER ','
Y2BASE 2000 
ENCODING 'internal' 
REMOTESOURCE 'ODBC' 
ESCAPECHAR '\'
/****THIS IS THE OPTION ****/
INCLUDEHEADER
)
        AS 
    SELECT * 
    FROM TEST_TABLE;

You will notice that Aginity does not apply highlighting to the option, but it will execute and write the title to the first line.

0
source

More articles:


All Articles