Execute Redshift query from Table column

[Tan]

Hello,

I'm currently learning TOS and trying to create a simple job.

My requirement is to read Redshift query from table one by one and then execute them.(I have a table with query column which stores different queries in each row).

I need to pick 1st query from table then run and save status to audit table and then pick second and so on.

Before executing query i need to insert 'Started' status in redshift audit table. After execution if that query run successfully then update that status to 'Success'.

If query fail then update status to 'Fail'.

Thanks in advance.

[Anonymous]

Hi

Using tFlowToIterat to iterate each query, the job design looks like:

main job:

tRedshiftInput--main--tFlowToIterate--iterate--tRunJob

tRunjob: pass the current query to child job, refer to this article to learn how to pass a value from parent job to child job.

child job:

tRedShiftRow1 (insert 'Started' Status to audit table)

|onsubjobok

tRedShiftRow2 (execute current query)

-onsubjobok--tRedShiftRow3 (update status with 'Success')

-onsubjoberror--tRedShiftRow3 (update status with 'Fail')

Regards

Shong

[Tan]

Thanks for quick response Shong.

I tried the solution and i think i'm almost there.

Only one item where i need your help.

How i can insert error message to audit table in case of error.

I'm sharing parent and child job images: hope i designed correctly.

Parent:

Child:

 

[Anonymous · Accepted solution]

Hi

You want to insert status 'Fail' or the error message into audit table? If latter, you need to use a tLogCatcher to capture the error message, please refer to the component documentation and learn this component.

Let me know if you have any questions.

Regards

Shong

[Tan]

Thanks Shong, so something like this in child job or parent job?

[Anonymous]

Yes, if you want to filter columns or add extra columns, add a tMap or tFiterRow after tLogCatcher.

[Tan]

Hi Shong,

My aim is when query fail it will update audit table with error message. Or if any other component fail it also catch the error and update the audit table with error.

1) you think my OnSubjobError tDBRow_4 will get all the errors. If yes how i can capture the error message. I'm not sure under which global variable it store that error message.

2) I added tLogCather_1 with tJavaRow_1 just to get error message from cather and then write to tJava_5.

In tJavaRow_1 i'm putting this text:

context.error_message = StringHandling.EREPLACE(input_row.message,"'","");

I'm assigning message to context and then with tJava_5 i want to print the error but its coming up as "log cather: ()". I'm using tJava just to check if message is printing or not then i will change this to tDBRow to store in table.

In tJava_5 i added this code:

System.out.println("log cather: ("+ context.error_message +")");

Thanks for your help Shong.

[Anonymous]

Hi

Change the job to be as below:

....

tLogCatcher--row1-->tJavaRow--oncomponentok--tJava_5-oncomponentok--tDBRow_4

Don't need to use onSubjobError to trigger tDBRow_4 any more if you use tLogCatcher to capture the error whenever an error occurs.

Regards

Shong

[Tan]

Thanks for your reply Shong,

While waiting for reply i changed the job as per screenshot below.

I'm picking origin and message from tLogCather and passing to context via tJavaRow. And then updating my audit table with this error message. This sounds correct to you?

You mentioned i don't need to keep OnSubjobError tDBRow_4 because now Cather will catch all the errors. Is there is any issue if i keep that part. Appreciate your help.

[Anonymous]

if tDBRow4 and tDBRow5 execute the same query, the query will be executed two times.