Data import/export of Netezza using external table

亦凉 2021-12-20 22:57 186阅读 0赞

**Introduction**

External table is a special table in Netezza system, which could be used to import/export data between flat files and Netezza directly.

**Import data from external file to Netezza**

Grammar:

CREATE EXTERNAL TABLE EXTERNAL_TABLE_TEST
    (    
         id int,
         name varchar(50) ) 
    USING 
    ( 
         DATAOBJECT('/mnt/external_files/Test.txt') 
    --other settings
    );

In this scenario, external table is kind of like a pointer. It could be used by other SQL statements, like: CREATE <TABLE\_NAME> AS SELECT \* FROM EXTERNAL\_TABLE\_TEST.

Since it is not the real table, once the external table is created, it is not allowed to alter the data of the external table.

For the external file location, it should be an address of the Netezza host linux box.? ODBC driver?

When the requirement is only to check the data in the external file, there is no need to store a external table, then a transient external table(TET) could be used.

Here is an example of TET table:

SELECT * FROM
    
    EXTERNAL '/mnt/external_files/Test.txt'
    (    
         id int,
         name varchar(50)
    )
    USING
    (
        --settings
    );

Basically, when using *EXTERNAL '...' (...) USING (...)*, the system will create a TET table when executing the query. The TET phrases could also be used as a part of other SQL statements, like:

INSERT INTO <TABLE\_NAME> SELECT \* FROM EXTERNAL '...' (...) USING (...)  
CREATE <TABLE\_NAME> AS SELECT \* FROM EXTERNAL '...' (...) USING (...)

**Export data from Netezza to external file**

Grammar:

CREATE EXTERNAL TABLE [EXTERNAL_TABLE_TEST] '/mnt/external_files/nzoutput.txt'
    USING
    (
           DELIM ','
           --other settings
    )
    AS
    SELECT * FROM OUTPUT_TABLE
    ;

The grammar is similar, and external table is also like a pointer to the file. Each time the query executed, the external file will be refreshed. If the EXTERNAL\_TABLE\_TEST name is , then a system TET will be used.

So it is not allowed to alter the external table?

The external table could also be used by other SQL statements?

external file could be modified?

**File format setting**

Basically, there are two kinds of format for a file, delimited(default) and fixed. Here is an example of external file with FIXED FORMAT.

DROP TABLE Test_Fixed;
    CREATE EXTERNAL TABLE Test_Fixed
    (
         ID BIGINT,
         NAME VARCHAR(10),
    )
    USING
    (
    DATAOBJECT('/mnt/test.txt')
    FORMAT 'FIXED'
    LOGDIR '/tmp/test.txt'
    LAYOUT
        (
            BYTES 19,
            BYTES 10
        )
    );
    SELECT * FROM Test_Fixed;

There are many other settings, here is the most frequently ones:

ENCODING 'UTF8'-- default is INTERNAL, which is for ansi file  
SKIPROWS 1 -- Skip the first row, this is usually setted when the file has a header row.  
FORMAT 'FIXED' --default is text, which means the delimited format  
DELIMITER ',' --default is pipe, this is limited to only one character  
ESCAPECHAR '/' --this is used when the file is delimited and the delimiter is included as the data  
QUOTEDVALUE double --default is no quotedvalue  
LOGDIR '/mnt/hqaasan01/development\_adhoc/' --specify the log dir, the log is very helpful when troubleshooting

Note: Since Netezza host is a Linux box, the import/export file format could only be linux format(use LF as row delimiter).

**External articles**

http://www.cnblogs.com/s021368/p/3582914.html

转载于:https://www.cnblogs.com/davablog/p/4921835.html