1

Data Alert for Current Running Job

Data Alert for Current Running Job

Problem Statement

Incorta job runs for more than 40 hours and none of us noticed until business users contacted us and asked why they do not see the updated data from the dashboard.

Incorta did provide us the Long Spinning Job Alert before. Why does it not work?

Requirements

We should get the notification via email or messenger.

Solution

Deploy the Incorta Metadata schema

We recommend all customers deploy the Incorta metadata schema. In particular, the tables involved are

  • LDR_JOB

  • LDR_JOB_DETAILS

Please note that these two history tables are useful for Job Analysis (Incorta Loading Job Analysis ) and for sending alert after the fact, but they are not satisfying our requirement.

  • LDR_JOB_HISTORY

  • LDR_JOB_DETAIL_HISTORY

Create formula columns for displaying the Job Load State

Unfortunately Incorta currently does not ship Incorta schema for Incorta metadata database. You can use Schema Wizard to create these Incorta tables in a local schema. However, you will need to create addition formula columns manually.

You can copy and paste the following formula columns:

Create the formula column STATE_NAME in LDR_JOB:

case(

     IncortaMetadata.LDR_JOB.STATE=-1,'Initial'

   , IncortaMetadata.LDR_JOB.STATE=1,'Extract Ready'

   , IncortaMetadata.LDR_JOB.STATE=2,'Extraction Started'

   , IncortaMetadata.LDR_JOB.STATE=3,'Extraction Failed'

   , IncortaMetadata.LDR_JOB.STATE=4,'Extraction Finished'

   , IncortaMetadata.LDR_JOB.STATE=5,'Loading Started'

   , IncortaMetadata.LDR_JOB.STATE=6,'Loading Failed'

   , IncortaMetadata.LDR_JOB.STATE=7,'Loading Finished'

   , IncortaMetadata.LDR_JOB.STATE=8,'Transformation Started'

   , IncortaMetadata.LDR_JOB.STATE=9,'Transformation Failed'

   , IncortaMetadata.LDR_JOB.STATE=10,'Transformation Finished'

   , IncortaMetadata.LDR_JOB.STATE=11,'PostLoad Started'

   , IncortaMetadata.LDR_JOB.STATE=-10,'Not Completed'

   , IncortaMetadata.LDR_JOB.STATE=100,'Finished with Errors'

   , IncortaMetadata.LDR_JOB.STATE=102,'Interrupting'

   , IncortaMetadata.LDR_JOB.STATE=101,'Interrupted'

   , 'N/A')

Create the formula column STATE_NAME in LDR_JOB_DETAILS:

 

case(

     IncortaMetadata.LDR_JOB_DETAILS.STATE=-1,'Initial'

,    IncortaMetadata.LDR_JOB_DETAILS.STATE=1,'Extract Ready'

,    IncortaMetadata.LDR_JOB_DETAILS.STATE=2,'Extraction Started'

,    IncortaMetadata.LDR_JOB_DETAILS.STATE=3,'Extraction Failed'

,    IncortaMetadata.LDR_JOB_DETAILS.STATE=4,'Extraction Finished'

,    IncortaMetadata.LDR_JOB_DETAILS.STATE=5,'Loading Started'

,    IncortaMetadata.LDR_JOB_DETAILS.STATE=6,'Loading Failed'

,    IncortaMetadata.LDR_JOB_DETAILS.STATE=7,'Loading Finished'

,    IncortaMetadata.LDR_JOB_DETAILS.STATE=8,'Transformation Started'

,    IncortaMetadata.LDR_JOB_DETAILS.STATE=9,'Transformation Failed'

,    IncortaMetadata.LDR_JOB_DETAILS.STATE=10,'Transformation Finished'

,    IncortaMetadata.LDR_JOB_DETAILS.STATE=11,'PostLoad Started'

,    'N/A')

Add the DH_UPDATE_TIME column to the extract SQL

Here are the SQL statement used in extracting data from MySQL database.

The reason why we need this column is to report the correct status to the user in the alert. We are showing the status when the metadata database is refreshed in Incorta. The status is as good as the time when the data was extracted.

SELECT

    `ID`,

    `NAME`,

    `NODE_NAME`,

    `TENANT_ID`,

    `OWNER_ID`,

    `STATE`,

    `START_TIME`,

    `LAST_MODIFIED`,

    `EXTRACT_START`,

    `EXTRACT_END`,

    `TRANSFORMATION_START`,

    `TRANSFORMATION_END`,

    `LOAD_START`,

    `LOAD_END`,

    `IS_TEST_RUN`,

    current_timestamp() DH_UPDATE_TIME

FROM

    `incorta`.`LDR_JOB`

and the other table:

 

SELECT

    `JOB_ID`,

    `TABLE_NAME`,

    `STATE`,

    `LOAD_TYPE`,

    `EXTRACT_START`,

    `EXTRACT_END`,

    `LOAD_START`,

    `LOAD_END`,

    `EXTRACTED_ROW_COUNT`,

    `REJECTED_ROW_COUNT`,

    `LOADED_ROW_COUNT`,

    `PRE_ROW_COUNT`,

    `POST_ROW_COUNT`,

    `MESSAGE`,

    `TRANSFORMATION_START`,

    `TRANSFORMATION_END`,

    current_timestamp() DH_UPDATE_TIME

FROM

    `incorta`.`LDR_JOB_DETAILS`


 

Joins between LDR_JOB and LDR_JOB_DETAILS

 

Dreate a Current Running Jobs dashboard

Create a dashboard including the the following formula fields.

The formula used to calculate the Job level duration:

timeBetween(IncortaMetadata.LDR_JOB.DH_UPDATE_TIME, IncortaMetadata.LDR_JOB.START_TIME) / 60000

The formula used to calculate the Table level duration:

timeBetween( ifNull(IncortaMetadata.LDR_JOB_DETAILS.LOAD_END, ifNull(IncortaMetadata.LDR_JOB_DETAILS.TRANSFORMATION_END, ifNull(IncortaMetadata.LDR_JOB_DETAILS.EXTRACT_END, IncortaMetadata.LDR_JOB_DETAILS.DH_UPDATE_TIME)))   , ifNull(IncortaMetadata.LDR_JOB_DETAILS.EXTRACT_START, ifNull(IncortaMetadata.LDR_JOB_DETAILS.TRANSFORMATION_START, ifNull(IncortaMetadata.LDR_JOB_DETAILS.LOAD_START, IncortaMetadata.LDR_JOB_DETAILS.DH_UPDATE_TIME)))) /60000

Conditional formatting is defined to highlight the duration that is more than one hour.
This can be adjusted based on the deploying company’s requirement.

Create the Data Alert - Long Running Jobs

If the Incorta Metadata is not refreshed often enough, you may get false alarms in the alerts! We need to schedule the alert and schedule the schema refresh of Incorta Metadata according to the requirement on how timely the information needs to be.

Schedule the Alert to be executed periodically. The alert needs to be there to check the job load as frequently as the minimum delay in awareness and acting on the issue.

Scheduling it to run every hour does not mean that you will get email every hour.

On the other hand, when a long running job is currently running, even though it is expected to run for a long time, it is still reasonable to send an alert every hour to show the status.

Reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
Like1 Follow
  • 1 Likes
  • 6 mths agoLast active
  • 37Views
  • 2 Following

Product Announcement


We are happy to
announce Incorta 4.8 !!!