Abfrage von Metadaten in BigQuery

BigQuery bietet 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.

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),
• 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:

1SELECT
2 project_id, 
3 project_number,
4 job_id,
5 DATE(creation_time) AS creation_date, 
6 creation_time,
7 start_time, 
8 end_time,
9 user_email, 
10 job_type, 
11 statement_type, 
12 total_bytes_processed, 
13 total_bytes_billed, 
14 destination_table, 
15 referenced_tables, 
16 REGEXP_REPLACE(query, r"\n|\s{2,}|\t", " ") AS query, 
17 state, 
18 ((total_bytes_billed / 1000000000) / 1000) * 5 AS total_cost
19FROM project_id.region-eu.INFORMATION_SCHEMA.JOBS_BY_PROJECT
20WHERE 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:

1SELECT 
2  project_id, 
3  dataset_id, 
4  table_id, 
5  DATE(TIMESTAMP_MILLIS(creation_time)) AS table_creation_date,
6  DATE(TIMESTAMP_MILLIS(last_modified_time)) AS last_modified_date
7FROM `project_id.dataset_id`.__TABLES__ 

Verknüpfung beider Abfragen

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

1CREATE VIEW `project_id.dataset_id.view_name` AS 
2SELECT
3  table.project_id, 
4  table.dataset_id, 
5  table.table_id, 
6  DATE(TIMESTAMP_MILLIS(table.creation_time)) AS table_creation_date,        DATE(TIMESTAMP_MILLIS(table.last_modified_time)) AS last_modified_date, 
7  jobs.project_number, 
8  jobs.job_id, 
9  DATE(jobs.creation_time) as date_last_job, 
10  jobs.start_time, 
11  jobs.end_time, 
12  jobs.user_email, 
13  jobs.job_type, 
14  jobs.statement_type, 
15  jobs.total_bytes_processed, 
16  jobs.total_bytes_billed, 
17  jobs.referenced_tables, 
18  REGEXP_REPLACE(query, r"\n|\s{2,}|\t", " ") AS query, 
19  state, 
20  ((total_bytes_billed / 1000000000) / 1000) * 5 AS total_cost
21FROM `project_id.dataset_id`.__TABLES__ as table
22LEFT JOIN `project_id`.`region eu`.INFORMATION_SCHEMA.JOBS_BY_PROJECT as jobs
23ON table.project_id = jobs.destination_table.project_id
24AND table.dataset_id = jobs.destination_table.dataset_id
25AND 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. Looker 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:

1SELECT 
2  project_id, 
3  dataset_id, 
4  table_id, 
5  max(last_modified_date) as last_modified_date
6FROM `project_id.dataset_id.view_name`
7GROUP BY  
8  project_id, 
9  dataset_id, 
10  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:

1SELECT 
2  project_id, 
3  dataset_id, 
4  table_id, 
5  job_id, 
6  query, 
7  max(total_bytes_billed) as total_bytes_billed, 
8  max(total_cost) as total_cost
9FROM `project_id.dataset_id.view_name`
10GROUP BY  
11  project_id, 
12  dataset_id, 
13  table_id, 
14  job_id, 
15  query
16ORDER by total_cost desc
17LIMIT 5 

Mit diesen einfachen Abfragen bietet BigQuery uns die Möglichkeit einen besseren Überblick über die genutzten Jobs und Tabellen zu erhalten. Dadurch lassen sich Kosten sparen und nicht benötigte Ressourcen identifizieren.