Databricks stöder livscykeler

CREATE OR REPLACE TEMP VIEW legacy_dbrs AS
WITH clusters_dbr_versions AS (
  SELECT
    account_id,
    workspace_id,
    cluster_id,
    cluster_name,
    owned_by,
    dbr_version,
    TRY_CAST(regexp_extract(dbr_version, '(\\d+)\\.(\\w+)?(?:\\.(\\w+))?', 1) AS INT) AS major_version,
    TRY_CAST(regexp_extract(dbr_version, '(\\d+)\\.(\\w+)?(?:\\.(\\w+))?', 2) AS INT) AS minor_version,
    ROW_NUMBER() OVER(PARTITION BY account_id, workspace_id, cluster_id ORDER BY change_time DESC) AS rnk
  FROM
    system.compute.clusters
  QUALIFY rnk=1
),
usage AS (
  SELECT
    account_id,
    workspace_id,
    usage_metadata.cluster_id AS cluster_id,
    usage_unit,
    ROUND(SUM(usage_quantity), 2) AS total_usage_dbu,
    MAX(usage_date) as last_seen_date
  FROM
    system.billing.usage
  WHERE
    usage_metadata.cluster_id IS NOT NULL AND
    usage_date > CURRENT_DATE() - INTERVAL 90 DAYS
  GROUP BY ALL
),
workspace_info AS (
  SELECT
    account_id,
    workspace_id,
    workspace_name,
    workspace_url
  FROM
    system.access.workspaces_latest
)
SELECT
  cdv.workspace_id,
  wi.workspace_name,
  wi.workspace_url,
  cdv.cluster_name,
  cdv.cluster_id,
  cdv.owned_by,
  cdv.dbr_version,
  total_usage_dbu,
  usage_unit,
  last_seen_date
FROM
  clusters_dbr_versions cdv
    INNER JOIN usage u USING (workspace_id, cluster_id)
    LEFT JOIN workspace_info wi USING (workspace_id)
WHERE
  major_version < 10 OR (major_version = 10 AND minor_version < 4)
GROUP BY ALL
ORDER BY
  workspace_id, total_usage_dbu DESC;

%sql
with latest_jobs AS (
  SELECT
    *,
    ROW_NUMBER() OVER(PARTITION BY workspace_id, job_id ORDER BY change_time DESC) as rn
  FROM system.lakeflow.jobs
  QUALIFY rn=1
),
latest_clusters AS (
  SELECT
    *,
    ROW_NUMBER() OVER(PARTITION BY workspace_id, cluster_id ORDER BY change_time DESC) as rn
  FROM system.compute.clusters
  QUALIFY rn=1
),
job_tasks_exploded AS (
  SELECT
    workspace_id,
    job_id,
    EXPLODE(compute_ids) as cluster_id
  FROM system.lakeflow.job_task_run_timeline
  WHERE period_start_time >= CURRENT_DATE() - INTERVAL 90 DAY AND ARRAY_SIZE(compute_ids) > 0
  GROUP BY ALL
),
workspace_info AS (
  SELECT
    account_id,
    workspace_id,
    workspace_name,
    workspace_url
  FROM
    system.access.workspaces_latest
),
clusters_with_dbr AS (
  SELECT
    t1.*,
    t2.cluster_name,
    t2.owned_by,
    t2.dbr_version
  FROM job_tasks_exploded t1
    INNER JOIN latest_clusters t2 USING (workspace_id, cluster_id)
)
SELECT
  wi.account_id,
  wi.workspace_id,
  wi.workspace_name,
  wi.workspace_url,
  latest_jobs.name,
  cwd.job_id,
  cwd.cluster_id,
  cwd.cluster_name,
  cwd.dbr_version
 FROM clusters_with_dbr cwd
 JOIN workspace_info wi ON cwd.workspace_id = wi.workspace_id
 LEFT JOIN latest_jobs USING (workspace_id, job_id)
 WHERE dbr_version RLIKE '^([1-9]\\.|10\\.[0-3]\\.)'