Abfrage von Metadaten in BigQuery

In BigQuery existiert die Möglichkeit, eine interne Darstellung der Metadaten abzurufen. Durch die Abfrage des INFORMATION_SCHEMA kann man unter anderem Metadaten von Datasets, Query-Jobs, oder Tabellen abrufen. In diesem Blogartikel fokussieren wir uns auf die Job-Metadaten. Dadurch erhalten wir Zugriff auf eine Übersicht über alle Query-, Load-, Extract- und Copy-Jobs, die wir innerhalb der letzten 180 Tage durchgeführt haben. Ziel dieses Beitrags ist es, eine Abfrage zu erstellen, die uns anzeigt, welche Jobs wann und für welche Tabelle laufen bzw. gelaufen sind. Weitere Informationen zu den Job-Metadaten sowie zu den anderen Metadaten kann in der Dokumentation gefunden werden.

Schema und Berechtigungen

Für die Abfrage der Job-Metadaten existieren vier verschiedene Abfragen. Hierbei wird die Syntax INFORMATION_SCHEMA.JOBS_BY_* benutzt. In der folgenden Tabelle sind die erforderlichen Berechtigungen und die Abfrageergebnisse kurz aufgeführt.

JOBS_BY_* Abfrageergebnis-Berechtigungen

USERJobs, die vom aktuellen Nutzer im aktuellen Projekt gesendet wurdenProject Viewer, BigQuery UserPROJECTJobs, die im aktuellen Projekt gesendet wurdenProject Owner, BigQuery AdminFOLDERJobs, die im übergeordneten Ordnern des aktuellen Projektes gesendet wurden Folder Admin, BigQuery Admin ORGANIZATION Jobs, die in der Organisation gesendet wurden, die mit dem aktuellen Projekt verknüpft sindOrganization BigQuery. resourceAdmin, Organization Owner und Organization Admin

Abfrage der Job-Metadaten

Im Folgenden entwickeln wir die SQL-Abfrage um Informationen über die laufenden Jobs zu erhalten. Wir interessieren uns für die Projekt ID und Nummer (project_id, project_number) für die Informationen, wann der Job gelaufen ist (creation_date, creation_time, start_time, end_time) sowie die Job ID (job_id).

Zudem wollen wir wissen, wer den Job ausgeführt hat (user_email) und wie viele Bytes der Job verbraucht hat (total_bytes_processed, total_bytes_billed) um so die Gesamtkosten der Abfragen zu berechnen (total_cost).

Zusätzlich wollen wir Informationen über den Job- und Abfrage-Typ (job_type, statement_type), den Status (state) und die Query (query) erhalten. Abschließend interessieren wir uns noch für die Zieltabelle (destination_table) und die Tabellen, auf die der Job verweist (referenced_tables).

Eine Abfrage der Metadaten für den aktuellen Tag sieht damit wie folgt aus:

SELECT
 project_id, 
 project_number,
 job_id,
 DATE(creation_time) AS creation_date, 
 creation_time,
 start_time, 
 end_time,
 user_email, 
 job_type, 
 statement_type, 
 total_bytes_processed, 
 total_bytes_billed, 
 destination_table, 
 referenced_tables, 
 REGEXP_REPLACE(query, r"\n|\s{2,}|\t", " ") AS query, 
 state, 
 ((total_bytes_billed / 1000000000) / 1000) * 5 AS total_cost
FROM project_id.region-eu.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE DATE(creation_time) = CURRENT_DATE()
 

Beachtet bitte hierbei, je nachdem, in welcher Region sich das Projekt befindet, muss gegebenenfalls die Region in der Query angepasst werden (region-eu).

Abfrage aller Tabellen in einem Projekt

Als nächstes benötigen wir die Informationen aller Tabellen in unserem Projekt. Hierfür verwenden wir die Syntax `project_id.dataset_id`.__TABLES__ um aus einem Dataset (dataset_id), von einem aktuellen Projekt (project_id), alle Tabellen (table_id), deren Erstellungs- (creation_time) und Aktualisierungsdatum (last_modified_time) herauszufinden. Daraus ergibt sich folgende Query:

SELECT 
  project_id, 
  dataset_id, 
  table_id, 
  DATE(TIMESTAMP_MILLIS(creation_time)) AS table_creation_date,
  DATE(TIMESTAMP_MILLIS(last_modified_time)) AS last_modified_date
FROM `project_id.dataset_id`.__TABLES__ 

Verknüpfung beider Abfragen

Anschließend können wir beide mit einem LEFT JOIN verbinden und eine View erstellen.

CREATE VIEW `project_id.dataset_id.view_name` AS 
SELECT
  table.project_id, 
  table.dataset_id, 
  table.table_id, 
  DATE(TIMESTAMP_MILLIS(table.creation_time)) AS table_creation_date,        DATE(TIMESTAMP_MILLIS(table.last_modified_time)) AS last_modified_date, 
  jobs.project_number, 
  jobs.job_id, 
  DATE(jobs.creation_time) as date_last_job, 
  jobs.start_time, 
  jobs.end_time, 
  jobs.user_email, 
  jobs.job_type, 
  jobs.statement_type, 
  jobs.total_bytes_processed, 
  jobs.total_bytes_billed, 
  jobs.referenced_tables, 
  REGEXP_REPLACE(query, r"\n|\s{2,}|\t", " ") AS query, 
  state, 
  ((total_bytes_billed / 1000000000) / 1000) * 5 AS total_cost
FROM `project_id.dataset_id`.__TABLES__ as table
LEFT JOIN `project_id`.`region eu`.INFORMATION_SCHEMA.JOBS_BY_PROJECT as jobs
ON table.project_id = jobs.destination_table.project_id
AND table.dataset_id = jobs.destination_table.dataset_id
AND table.table_id = jobs.destination_table.table_id 

Mögliche Abfragen

In diesem Abschnitt zeigen wir zwei mögliche Beispielabfragen, um einen besseren Überblick über die Job-Metadaten zu erlangen. Alternativ kann man die oben erstellte View auch mit z.B. Data Studio auswerten, um einen visuellen Eindruck zu erhalten.

1. Beispiel: Wann wurden meine Tabellen zuletzt aktualisiert?

Wir wollen eine Übersicht von allen Tabellen in einem Projekt nach Datum erstellen. Hierbei soll sich das Datum (last_modified_date) auf den letzten ausgeführten Job beziehen. Aus diesem Grund benutzen wir die Funktion max(last_modified_date). Diese Abfrage könnte wie folgt aussehen:

SELECT 
  project_id, 
  dataset_id, 
  table_id, 
  max(last_modified_date) as last_modified_date
FROM `project_id.dataset_id.view_name`
GROUP BY  
  project_id, 
  dataset_id, 
  table_id   

Wir interessieren uns für die Jobs, die am meisten Bytes verbrauchen und somit die meisten Kosten generieren. Hierfür erstellen wir eine Abfrage, die uns die Job-ID, die Query, sowie die Anzahl der Bytes und die daraus resultierenden Kosten anzeigt. Diese Abfrage könnte wie folgt aussehen:

SELECT 
  project_id, 
  dataset_id, 
  table_id, 
  job_id, 
  query, 
  max(total_bytes_billed) as total_bytes_billed, 
  max(total_cost) as total_cost
FROM `project_id.dataset_id.view_name`
GROUP BY  
  project_id, 
  dataset_id, 
  table_id, 
  job_id, 
  query
ORDER by total_cost desc
LIMIT 5  

Wir hoffen, dass euch der Artikel zur Abfrage von Job-Metadaten gefallen hat. Bei Fragen hinterlasst gerne ein Kommentar!