Business Intelligence
/10. Juli 2023- Aktualisiert am 26. August 2025/3 Min. LesezeitAbfrage 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.
JOBS_BY_* | Abfrageergebnis | Berechtigungen |
|---|---|---|
USER | Jobs, die vom aktuellen Nutzer im aktuellen Projekt gesendet wurden | Project Viewer, BigQuery User |
PROJECT | Jobs, die im aktuellen Projekt gesendet wurden | Project Owner, BigQuery Admin |
FOLDER | Jobs, 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 sind | Organization 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),
- 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 Row | project_id | dataset_id | table_id | last_modified_date |
|---|---|---|---|---|
1 | myproject | dataset1 | table_4 | 2020-11-13 |
2 | myproject | dataset3 | table_a | 2020-11-01 |
3 | myproject | dataset1 | table_1 | 2020-11-01 |
4 | myproject | dataset2 | table_D | 2020-09-15 |
5 | myproject | dataset2 | table_G | 2022-01-01 |
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 Row | project_id | dataset_id | table_id | job_id | query | total_bytes_billed | total_costs |
|---|---|---|---|---|---|---|---|
1 | myproject | dataset1 | table_5 | bqjob_12 | MERGE | 1234567890 | 0,006172839 |
2 | myproject | dataset2 | table_D | bqjob_23 | MERGE | 12345667805 | 0,006172839 |
3 | myproject | dataset2 | table_A | bqjob_3 | MERGE | 234567870 | 0,001172839 |
4 | myproject | dataset1 | table_1 | bqjob14 | MERGE | 234567500 | 0,001172838 |
5 | myproject | dataset3 | table_b | bqjob_5 | MERGE | 34567000 | 0,000172835 |
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.
Lust auf einen
Austausch?
Newsletter
Als Erstes informiert, wenn es Neuigkeiten in der digitalen Welt gibt!
Durch die Bereitstellung Ihrer E-Mail-Adresse erklären Sie sich damit einverstanden, Newsletter und Werbe-E-Mails von uns zu erhalten. Wir respektieren Ihre Privatsphäre und werden Ihre Informationen nicht an Dritte weitergeben. Sie können sich jederzeit abmelden.
You've reached the end
Linienstrasse 222, 10119 Berlin
Raboisen 30, 20095 Hamburg
Conversion Optimierung
Business Intelligence
Digital Analytics
Marketing Automation
Team
Kontakt
Jobs
Datenschutzerklärung
Impressum
Cookie-Einstellungen
©2026 Peaks & Pies GmbH
