Thursday, August 2, 2012

Netezza External Tables


You can use Netezza's external table to view data from an external file and use it like a database table. When you create an external table, the actual data still sits in that physical external file, but you can query it from Netezza like you can query a normal database table.

You can also use external tables to export data out of a Netezza table into a file.

From Netezza's Data Loading Manual  - An external table allows Netezza to treat an external file as a database table. An external table has a definition (a table schema), but the actual data exists outside of the Netezza appliance database. External tables can be used to access files which are stored on the Netezza host server or, in the case of a remote external table, Netezza can treat a file on a client system as an external table (see REMOTESOURCE option).

Below are 2 examples to show you how to do both data export and "import".

I am using Aginity Workbench for Netezza on my windows machine (which comes with a netezza odbc driver), and I have a text file stored in my local drive C:\temp\testfile.txt which has the following column header and 3 rows:


employeeid,employeename,salary
1,'John Lee',100000
2,'Marty Short', 120000
3,'Jane Mars', 150000


CREATE EXTERNAL TABLE ext_tbl_employees(
employee_id integer, 
employee_name character varying(100), 
salary decimal (10,2))
USING (
dataobject('c:\temp\testfile.txt') 
remotesource 'odbc'
delimiter ','
skiprows 1);

Then in your Aginity workbench object browser, expand the folder External Tables, and you will your new external table listed there.

You can then query the table just like a normal database table:


select *
from ext_tbl_employees


You can also create a transient external table, in which it only exists for the duration of your query, which means the external table definition does not persist in the system catalog.


--transient external table
SELECT 
employee_id,
employee_name,
salary
FROM EXTERNAL 'c:\temp\testfile.txt' 
(
employee_id integer, 
employee_name character varying(100), 
salary decimal (10,2))
USING (
remotesource 'odbc'
delimiter ','
skiprows 1);

Transient external table is also a very useful way to export data from a netezza database out to a text file. You can export not just an entire table, but the output of any sql statement. The beauty of it is you don't have to specify the schema definition of the data, which can save you a lot of typing:

create table mytesttable (
studentid integer,
studentname character varying(100)
);

insert into mytesttable 
select 1, 'Jane Doe'
union
select 2, 'John Smith';

CREATE EXTERNAL TABLE 'c:\Temp\ExportDataTest.csv' USING (remotesource 'ODBC' DELIM ',') AS
SELECT *
from mytesttable;

drop table mytesttable;


Note: if the file already exists, any existing data will be deleted by Netezza before it inserts data.

If there's comma in your data, and you want to export to csv, use escapeChar option:

CREATE EXTERNAL TABLE 'c:\Temp\OfferAttributesNightlyFeed.csv' USING (remotesource 'ODBC' DELIM ',' ESCAPECHAR '\') AS
SELECT *
from COUPONATTRIBUTESTEST;








16 comments:

  1. Thanks for the efforts on gathering useful content and sharing here. You can find more question and answers on Netezza database in the following forum.

    Netezza database question and answers

    ReplyDelete
  2. thank you for the command.. it works perfect but when i export the results into text file the results are not formatted correctly.. there is no terminator for rows... multiple rows are displaying in the single row.. is there any row terminator

    ReplyDelete
  3. Just for all those that didn't know it, Netezza will typically output with unix line terminators and not windows version. This means, when you open it in word pad or anything, it will show up as one huge line, however, save as a .csv and excel will see the line wraps. My recommendation would be to use powershell which is very quick once you export it to change them to a full CR-LF.

    ReplyDelete
    Replies
    1. Well it seemed to work for me if I append the CR character onto the end of my last column using CHR(13). I then include the option "crinstring true" in the USING() bit.

      Delete
  4. how can we append timestamp with filename or add any parameter to filename

    ReplyDelete
  5. Is there a way that we can create one external table from multiple csv files? Thanks.

    ReplyDelete
  6. How to drop this external table?

    ReplyDelete
  7. thank you for the post, external table creation is completed but I'm getting a ERROR [08S01] Communication link failure message when running a select statement on my table.

    ReplyDelete
  8. How do you give DataObject on unix to create on remote location?

    ReplyDelete
  9. I have read your blog and I gathered some needful information from your blog. Keep update your blog. Awaiting for your next update.

    Tableau Online Training|
    SAS Online Training |
    R Programming Online Training|

    ReplyDelete
  10. Much obliged to you, for sharing those brilliantly expressive perceptions. As the reader of this blog, I'll attempt to do some equity in reacting; there's a great deal that you've pressed in articulating the critical imperatives of, as you pleasantly put it.Keep On Sharing.keep it up.
    C and C++ Training Institute in chennai | C and C++ Training Institute in anna nagar | C and C++ Training Institute in omr | C and C++ Training Institute in porur | C and C++ Training Institute in tambaram | C and C++ Training Institute in velachery

    ReplyDelete