Evaluating Job Name / Component Name at run time?

Matillion ETL
1

Hi all,

 

I'm using Matillion ETL for Snowflake to connect to a relational database source to ingest and transform data into Snowflake.

 

Let's say I have 5 hardcoded ingest jobs that handle peculiarities of each job:

  • Job A
  • Job B
  • Job C
  • Job D
  • Job E

 

For each of these ingest jobs, I can determine common attributes, such as:

  • Number of delta records ingested
  • Earliest modified datetime of the records captured in the delta
  • Most recent modified datetime of the records captured in the delta

 

As I can generate these common attributes within each of the 5 jobs, I'd like to create one orchestration/transaction job to include/execute from within each of the 5 (as opposed to hardcoding the logging within each separately).

 

Rather than hardcode the name of caller job (ie. one of the 5), it'd be good if I can determine this at runtime and pass it into the include (ie. set scalar variable or grid).

 

Whilst I haven't got to doing this in a separate job file, I've successfully used an "SQL Script" within an orchestration job, to insert variables such as:

  • ${job_id}
  • ${component_id}

But their values are "-1" .. not sure why.

 

But if I try these variables, it fails:

  • ${job_name}
  • ${component_name}

 

So, is it possible to access these variables, what are their expected values and is there documentation that lists what is available.

 

Unfortunately, doing a search for anything with "job" (eg. "Job Name variables") just returns documentation about job scope variables.

 

Next step would be to try and move away from an "SQL Script" component embedded within each of the 5 jobs and get it into a separate file but I could live with that if it were possible.

 

Hoping someone has done something similar in native Matillion as opposed to Bash/Python.

 

Cheers,

Mick

2

Hi Mick,

 

Both job_name and component_name are documented in Matillion's document on variables, so I'm not sure why yours are showing as -1 ... On my instance (Matillion ETL for Snowflake v1.53) they do have real values, whether I choose Run Job or Run Component.

 

The trick I often use is to add an env|sort in a Bash Script (see this post) although it doesn't wokr for Grid Variables though.

 

BR

3

Thanks @deBaris​ ,

Your confirmation that it should work has got me giving it another go and I got it working first go this time around 😬

Using an SQL Script component in an Orchestration Job, this works:

insert into DATABASE.SCHEMA.YOUR_TABLE (

JOB_ID,

JOB_NAME,

RUN_HISTORY_ID,

VERSION_ID,

VERSION_NAME,

PROJECT_ID,

PROJECT_NAME,

QUEUED_TIME,

PROJECT_GROUP_ID,

PROJECT_GROUP_NAME,

ENVIRONMENT_ID,

ENVIRONMENT_NAME,

DETAILED_ERROR,

COMPONENT_ID,

COMPONENT_NAME,

COMPONENT_MESSAGE

) select

${job_id},

'${job_name}',

${run_history_id},

${version_id},

'${version_name}',

${project_id},

'${project_name}',

'${queued_time}',

${project_group_id},

'${project_group_name}',

${environment_id},

'${environment_name}',

'${detailed_error}',

${component_id},

'${component_name}',

'${component_message}'

I think my issue was either:

  1. This won't work when using the "Run" button available when authoring the SQL Script component (which makes sense)
  2. Placing the strings within quotes, so it generates valid SQL

Either way, it does work when running the component / job.

Leaving this here incase it helps another.

Cheers,

Mick