Sorry I could not answer earlier. The external table is just used while importing and the file is actually a FIFO aka named pipes (not to be confused with Windows NamedPipes). There is a design document that I wrote describing this – there have been a few bug fixes and enhancements since the initial version but the structure and functionality is largely the same.
You can see SQOOP-846 for the design doc Venkat From: Suraj Nayak <[email protected]> Reply-To: "[email protected]" <[email protected]> Date: Monday, January 30, 2017 at 7:46 PM To: "[email protected]" <[email protected]> Subject: Re: Netezza Direct Sqoop Import Hi All, Found out the puzzle is all about Netezza TET - Transient external tables. http://www.ibm.com/support/knowledgecenter/SSULQD_7.2.0/com.ibm.nz.load.doc/c_load_transient_external_tables.html Sqoop does not need DROP table privilege as Netezza TET lasts only till the query is executed. Thanks! On Mon, Jan 30, 2017 at 11:58 AM, Suraj Nayak <[email protected]<mailto:[email protected]>> wrote: Hi Szabolcs/Sqoop Users, Few more questions, * if Sqoop does not execute DROP table, does the system catalog in Netezza is cleaned up? * Is there a cleanup operation kicks in after the mapper finish? * Or the Netezza system itself does not perform bookkeeping on these external tables ? Thanks! On Mon, Jan 30, 2017 at 9:25 AM, Suraj Nayak <[email protected]<mailto:[email protected]>> wrote: Thanks Szabolcs for the explanation. YARN local files /yarn/local/usercache/someuser/appcache/application_1483624176418_42787/work/task_1483624176418_42787_m_000000/nzexttable-0.txt will also be cleaned up right ? Thanks! On Mon, Jan 30, 2017 at 8:06 AM, Szabolcs Vasas <[email protected]<mailto:[email protected]>> wrote: Hi Suraj, I have looked into the code and found the following: In case of Netezza direct imports Sqoop executes a CREATE EXTERNAL TABLE command (so you will need CREATE EXTERNAL TABLE privilege) to create a backup of the content of the table to a temporary file and it copies the content of this file to the final output on HDFS. The SQL command you pasted in your email is indeed the one which is executed by Sqoop but as far as I understand from the Netezza documentation (http://www.ibm.com/support/knowledgecenter/SSULQD_7.2.1/com.ibm.nz.load.doc/c_load_create_external_tbl_expls.html, 6th example) this does not really create a new external table in any schema it just backs up the content of the table and because of that no DROP TABLE statement is executed. I hope this helps, let me know if you have any further doubts. Regards, Szabolcs On Fri, Jan 27, 2017 at 11:22 PM, Suraj Nayak <[email protected]<mailto:[email protected]>> wrote: Link to question in Stack Overflow : http://stackoverflow.com/questions/41903493/sqoop-direct-import-netezza-table-permissions On Fri, Jan 27, 2017 at 1:31 PM, Suraj Nayak <[email protected]<mailto:[email protected]>> wrote: Hi All, We are using netezza direct to import data from Netezza to Hadoop as part of POC. Q1. Does direct mode always require CREATE EXTERNAL TABLE and DROP privilege to perform direct transfer? Q2. Does the external table get created in Netezza ? If yes, which database ? I see Sqoop using below query : CREATE EXTERNAL TABLE '/yarn/local/usercache/someuser/appcache/application_1483624176418_42787/work/task_1483624176418_42787_m_000000/nzexttable-0.txt' USING (REMOTESOURCE 'JDBC' BOOLSTYLE 'T_F' CRINSTRING FALSE DELIMITER 44 ENCODING 'internal' FORMAT 'Text' INCLUDEZEROSECONDS TRUE NULLVALUE 'null' MAXERRORS 1) AS SELECT * FROM SOME_TBL WHERE (DATASLICEID % 3) Q3. If Netezza needs to create External tables, can it create the external table in different database than the one which the actual table with data that needs to be pulled into Hadoop. Q4. Does Sqoop run DROP table on external table which was created by individual mappers ? I have limited knowledge on External table. Above question will solve lot of doubts w.r.t implementation. Thanks in advance.! -- Thanks Suraj Nayak M -- Thanks Suraj Nayak M -- Szabolcs Vasas Software Engineer [http://files.cloudera.com.s3.amazonaws.com/New%20Branding/cloudera-small.png]<http://www.cloudera.com> -- Thanks Suraj Nayak M -- Thanks Suraj Nayak M -- Thanks Suraj Nayak M
