create external table with headers in netezza (postgres)

Question

I am creating 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 fine. My question is :

Is there a way we can name the header values as column names in the test.csv file ? Is it possible in either Netezza or postgres.

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

Thanks

Niello

Answer 1

In version 7.2 of Netezza you can now specify the IncludeHeader option to achieve this with external tables.

This is documented here

Santoro

Yes, a header is included but the columns are unnamed – Cloverleaf

Answer 2

It's not pretty, and it would likely add some amount of overhead to the query, but you could 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

Vice

Answer 3

There actually is a way to include the header in the file if you have Netezza version 7.2 or greater.

The option is 'includeheader' , but it doesn't look like the Aginity Workbench highlights 'includeheader' as though it's 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'll notice that Aginity doesn't apply highlighting to the option but it will execute and write a header to the first row.

Beaulahbeaulieu

the header included in unnamed – Cloverleaf

Answer 4

This is another example, along the same idea that qSlug gave...

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

You can then just delete the last column from your csv file.

Ferd

Only works for all character data or casting all your real data to char first. – Liverpudlian