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]> 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]> wrote: > >> Thanks Szabolcs for the explanation. >> >> YARN local files /yarn/local/usercache/someuser >> /appcache/application_1483624176418_42787/work/task_14836241 >> 76418_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]> >> 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]> wrote: >>> >>>> Link to question in Stack Overflow : http://stackoverflow.com/que >>>> stions/41903493/sqoop-direct-import-netezza-table-permissions >>>> >>>> On Fri, Jan 27, 2017 at 1:31 PM, Suraj Nayak <[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/someuse >>>>> r/appcache/application_1483624176418_42787/work/task_1483624 >>>>> 176418_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://www.cloudera.com> >>> >> >> >> >> -- >> Thanks >> Suraj Nayak M >> > > > > -- > Thanks > Suraj Nayak M > -- Thanks Suraj Nayak M
