Error handling Talend greenplumRow

Question

I want to create views in greenplum HAWQ using a simple talent task that will basically have a file containing all the views, then I need to run a CREATE VIEW script.

Since these representations (50-60,000) come from the oracle system, I need to find those that we could not create.

Here is the layout for my problem:

I have a view already in the database, and I want to create it 3 more times. This obviously will not work.

Here's the conclusion:

Exception in component tGreenplumRow_2 org.postgresql.util.PSQLException: ERROR: relation "ad_apps_dependencies" already exists at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592) .--------------. | tLogRow_4 | |=------------=| |componenterror| |=------------=| |componenterror| '--------------' .-----------------------------------------------------+-------------------------------------------------------------. | tLogRow_5 | |=----------------------------------------------------+------------------------------------------------------------=| |result |result1 | |=----------------------------------------------------+------------------------------------------------------------=| |ERROR: relation "ad_apps_dependencies" already exists|CREATE VIEW SYSTEM.AD_APPS AS SELECT * FROM APPLSYS.AD_APPS| '-----------------------------------------------------+-------------------------------------------------------------' .------------. | tLogRow_6 | |=----------=| |subjobError | |=----------=| |Subjob Error| '------------' 

I want to get this output 3 times since I am trying to run a query 3 times. (In the final version, I would create another file containing only those requests that failed, so we can fix them later, but this is a very important point.)

As a workaround: I could move this part of tRow -> OnError -> FixedFlow -> FailedViews to another job, but this is not an elegant solution.

Answer 1

Do it with tJavaFlex. It works for Greenplumrow !!! In the example below, I execute a query in tOracleInput_6:

 "SELECT count(*) FROM "+((String)globalMap.get("ora_sch.SCHEMA_NAME")) + "." + ((String)globalMap.get("ora_tab.TABLE_NAME")) 

I am not sure about the results of the query. This may be unsuccessful (for example: someone just dropped the table), so I use tJavaFlex + connection iteration! The resulting code generated a try {...} catch block.

In the following case, I iterate over the schema tables and count rows in each table. I am collecting exceptions in jash hashmap. (hashmap in context, but that's a different story)

 tJavaFlex Begin code: try{ tJavaflex main code: // here is the main part of the component, // a piece of code executed in the row // loop System.out.println(((String)globalMap.get("TYPE"))+" SELECT count(*) FROM "+((String)globalMap.get("ora_sch.SCHEMA_NAME")) + "." + ((String)globalMap.get("ora_tab.TABLE_NAME"))); tJavaFlex1 End code: // end of the component, outside/closing the loop } catch (Exception e) { //put ((Map<String, String>)context.EXCEPTIONS).put( ((String)globalMap.get("TYPE"))+((String)globalMap.get("ora_sch.SCHEMA_NAME"))+"_" +((Integer)globalMap.get("tFlowToIterate_8_CURRENT_ITERATION")).toString(), ((String)globalMap.get("ora_sch.SCHEMA_NAME"))+"," + ((String)globalMap.get("ora_tab.TABLE_NAME"))); //print some debug message System.out.print(((String)globalMap.get("TYPE"))+" ### SQL Exception at "); System.out.print("Iteration Number: "+((Integer)globalMap.get("tFlowToIterate_8_CURRENT_ITERATION")).toString()+","+((String)globalMap.get("ora_sch.SCHEMA_NAME"))+","+ ((String)globalMap.get("ora_tab.TABLE_NAME"))); System.out.println(" ###"); //e.printStackTrace(); }