turbot/databricks
steampipe plugin install databricks

Table: databricks_job_run - Query Databricks Job Runs using SQL

Databricks Job Runs are a resource within the Databricks service that allows users to execute a specific job on a schedule or on-demand. They provide detailed information about each execution, including the job ID, run ID, start time, and state. This helps users track the status and progress of their Databricks jobs.

Table Usage Guide

The databricks_job_run table provides insights into Job Runs within Databricks. As a Data Engineer, you can explore specific details about each job run through this table, including the job ID, run ID, start time, and state. Utilize it to monitor the status and progress of your Databricks jobs, helping you to manage and optimize your data processing tasks.

Examples

Basic info

Explore which Databricks job runs have taken place, identifying instances such as who initiated them and how long they lasted. This can be particularly beneficial for auditing purposes or for understanding patterns in job run durations and frequencies.

select
run_id,
run_name,
job_id,
original_attempt_run_id,
attempt_number,
creator_user_name,
run_duration as run_duration_ms,
account_id
from
databricks_job_run;
select
run_id,
run_name,
job_id,
original_attempt_run_id,
attempt_number,
creator_user_name,
run_duration as run_duration_ms,
account_id
from
databricks_job_run;

Get total runs per job

Discover the frequency of each job execution to better understand the workload distribution. This could be useful in identifying heavily used jobs that may need optimization or resource allocation adjustments.

select
job_id,
count(*) as total_runs
from
databricks_job_run
group by
job_id;
select
job_id,
count(*) as total_runs
from
databricks_job_run
group by
job_id;

Get total runs per job per day

Explore the frequency of job runs on a daily basis to understand the workload distribution and identify any potential bottlenecks or high-activity periods. This can help in optimizing job scheduling and resource allocation.

select
job_id,
date_trunc('day', start_time) as day,
count(*) as total_runs
from
databricks_job_run
group by
job_id,
day
order by
day;
select
job_id,
date(start_time) as day,
count(*) as total_runs
from
databricks_job_run
group by
job_id,
day
order by
day;

Get the state of the last run for each job

Determine the status of the most recent job execution for each task. This can help you understand the success or failure of your latest tasks, enabling you to troubleshoot issues or optimize performance.

select
job_id,
run_id,
run_name,
attempt_number,
state ->> 'state_message',
state ->> 'life_cycle_state',
state ->> 'result_state',
account_id
from
databricks_job_run
order by
attempt_number desc
limit
1;
select
job_id,
run_id,
run_name,
attempt_number,
json_extract(state, '$.state_message'),
json_extract(state, '$.life_cycle_state'),
json_extract(state, '$.result_state'),
account_id
from
databricks_job_run
order by
attempt_number desc
limit
1;

Get task details for each job run

This query is used to gain insights into the details of each task within job runs, including the duration, start and end times, and state. It's useful for understanding the performance and efficiency of tasks within job runs in a Databricks environment.

select
job_id,
run_id,
run_name,
t ->> 'task_key' as task_key,
t ->> 'cleanup_duration' as cleanup_duration,
t ->> 'cluster_instance' as cluster_instance,
t ->> 'start_time' as start_time,
t ->> 'end_time' as end_time,
t ->> 'existing_cluster_id' as existing_cluster_id,
t ->> 'notebook_task' as notebook_task,
t ->> 'cleanup_duration' as cleanup_duration,
t ->> 'state' as state,
account_id
from
databricks_job_run,
jsonb_array_elements(tasks) as t
where
tasks is not null;
select
job_id,
run_id,
run_name,
json_extract(t.value, '$.task_key') as task_key,
json_extract(t.value, '$.cleanup_duration') as cleanup_duration,
json_extract(t.value, '$.cluster_instance') as cluster_instance,
json_extract(t.value, '$.start_time') as start_time,
json_extract(t.value, '$.end_time') as end_time,
json_extract(t.value, '$.existing_cluster_id') as existing_cluster_id,
json_extract(t.value, '$.notebook_task') as notebook_task,
json_extract(t.value, '$.cleanup_duration') as cleanup_duration,
json_extract(t.value, '$.state') as state,
account_id
from
databricks_job_run,
json_each(tasks) as t
where
tasks is not null;

List jobs that are waiting for retry

Explore which jobs are currently in a waiting state for a retry attempt. This is useful for identifying potential issues with certain jobs and understanding where intervention may be necessary.

select
run_id,
run_name,
job_id,
original_attempt_run_id,
attempt_number,
creator_user_name,
run_duration as run_duration_ms,
account_id
from
databricks_job_run
where
state ->> 'life_cycle_state' = 'WAITING_FOR_RETRY';
select
run_id,
run_name,
job_id,
original_attempt_run_id,
attempt_number,
creator_user_name,
run_duration as run_duration_ms,
account_id
from
databricks_job_run
where
json_extract(state, '$.life_cycle_state') = 'WAITING_FOR_RETRY';

List retry job runs for a particular job

Analyze the settings to understand the frequency and reasons for job reruns in a specific Databricks job. This can help to pinpoint areas of instability and inform decisions on system optimization.

select
run_id,
run_name,
job_id,
original_attempt_run_id,
attempt_number,
creator_user_name,
run_duration as run_duration_ms,
account_id
from
databricks_job_run
where
job_id = '572473586420586'
and original_attempt_run_id <> run_id;
select
run_id,
run_name,
job_id,
original_attempt_run_id,
attempt_number,
creator_user_name,
run_duration as run_duration_ms,
account_id
from
databricks_job_run
where
job_id = '572473586420586'
and original_attempt_run_id <> run_id;

Schema for databricks_job_run

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
account_idtext=, !=, ~~, ~~*, !~~, !~~*The Databricks Account ID in which the resource is located.
attempt_numberbigintThe sequence number of this run attempt for a triggered job run.
cleanup_durationbigintThe time in milliseconds it took to terminate the cluster and clean up any associated artifacts.
cluster_instancejsonbThe cluster instance that was used to run this job.
cluster_specjsonbA snapshot of the job's cluster specification when this run was created.
continuousjsonbThe continuous trigger that triggered this run.
creator_user_nametextThe user who created this job run.
end_timetimestamp with time zoneThe time at which this run ended.
execution_durationbigintThe time in milliseconds it took to execute the commands in the JAR or notebook.
git_sourcejsonbAn optional specification for a remote repository containing the notebooks used by this job's notebook tasks.
job_clustersjsonbA list of job cluster specifications that can be shared and reused by tasks of this job.
job_idbigint=The canonical identifier of the job that contains this run.
job_parametersjsonbJob-level parameters used in the run.
number_in_jobbigintA unique identifier for this job run. This is set to the same value as `run_id`.
original_attempt_run_idbigintIf this run is a retry of a prior run attempt, this field contains the run_id of the original attempt.
overriding_parametersjsonbThe parameters used for this run.
repair_historyjsonbThe repair history of this job run.
run_durationbigintThe time in milliseconds it took the job run and all of its repairs to finish.
run_idbigint=The canonical identifier of the run.
run_nametextAn optional name for the run.
run_page_urltextThe URL to the detail page of the run.
run_typetext=The type of run.
schedulejsonbThe schedule that triggered this run.
setup_durationbigintThe time in milliseconds it took to set up the cluster.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
start_timetimestamp with time zoneThe time at which this run started.
statejsonbThe current state of the run.
tasksjsonbThe list of tasks performed by the run.
titletextThe title of the resource.
triggertextThe trigger that triggered this run.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- databricks

You can pass the configuration to the command with the --config argument:

steampipe_export_databricks --config '<your_config>' databricks_job_run