Jobmetadaten mit INFORMATION_SCHEMA abrufen

INFORMATION_SCHEMA umfasst mehrere Ansichten, über die auf Metadaten von Datasets, Routinen, Tabellen, Ansichten, Jobs, Reservierungen und Streamingdaten zugegriffen werden kann.

Sie können durch Abrufen der Ansicht INFORMATION_SCHEMA.JOBS_BY_* Echtzeit-Metadaten zu BigQuery-Jobs abrufen. Diese Ansicht enthält derzeit ausgeführte Jobs sowie den Verlauf der abgeschlossenen Jobs der letzten 180 Tage.

Erforderliche Berechtigungen

Zum Abrufen von Jobmetadaten mithilfe von INFORMATION_SCHEMA-Tabellen sind Berechtigungen mit entsprechenden Geltungsbereichen erforderlich:

  • JOBS_BY_USER erfordert bigquery.jobs.list für das Projekt und ist in den Rollen Project Viewer und BigQuery User verfügbar.
  • JOBS_BY_PROJECT erfordert bigquery.jobs.listAll für das Projekt und ist in den Rollen Project Owner und BigQuery Admin verfügbar.
  • JOBS_BY_FOLDER erfordert bigquery.jobs.listAll für den übergeordneten Ordner und ist in den Rollen Folder Admin und BigQuery Admin für den Ordner verfügbar.
  • JOBS_BY_ORGANIZATION erfordert bigquery.jobs.listAll für die Organisation und ist in den Rollen Organization bigquery.resourceAdmin, Organization Owner und Organization Admin verfügbar. Beachten Sie, dass JOBS_BY_ORGANIZATION nur Nutzern mit definierten Google Cloud-Organisationen zur Verfügung steht.

Weitere Informationen über detaillierte BigQuery-Berechtigungen finden Sie unter Rollen und Berechtigungen.

Schema

Wenn Sie die Ansichten des Typs INFORMATION_SCHEMA.JOBS_BY_* abfragen, wird in den Abfrageergebnisse jeder BigQuery-Job in einer eigenen Zeile dargestellt.

  • INFORMATION_SCHEMA.JOBS_BY_USER gibt nur die Jobs zurück, die vom aktuellen Nutzer im aktuellen Projekt gesendet wurden.
  • INFORMATION_SCHEMA.JOBS_BY_PROJECT gibt alle Jobs zurück, die im aktuellen Projekt gesendet wurden.
  • INFORMATION_SCHEMA.JOBS_BY_FOLDER gibt alle Jobs zurück, die im übergeordneten Ordner des aktuellen Projekts gesendet wurden, einschließlich der Jobs in Unterordnern.
  • INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION gibt alle Jobs zurück, die in der Organisation gesendet wurden, die mit dem aktuellen Projekt verknüpft ist.

Die Ansicht INFORMATION_SCHEMA.JOBS_BY_* hat das folgende Schema:

Spaltenname Datentyp Wert
creation_time TIMESTAMP (Partitionierungsspalte) Erstellungszeit dieses Jobs. Die Partitionierung basiert auf der UTC-Zeit dieses Zeitstempels.
project_id STRING (Clustering-Spalte) ID des Projekts
project_number INTEGER Nummer des Projekts.
folder_numbers RECORD GAIA-IDs (Google Accounts and ID Administration) von Ordnern im Ancestor eines Projekts, beginnend mit dem Blattordner, der dem Projekt am nächsten ist. Diese Spalte enthält nur in JOBS_BY_FOLDER Werte.
user_email STRING (Clustering-Spalte) E-Mail-Adresse oder Dienstkonto des Nutzers, der den Job ausgeführt hat.
job_id STRING ID des Jobs, z. B. bquxjob_1234.
job_type STRING Typ des Jobs. Kann QUERY, LOAD, EXTRACT, COPY oder null sein. Der Jobtyp null gibt einen internen Job an, z. B. die Auswertung einer Anweisung des Skriptjobs oder die Aktualisierung der materialisierten Ansicht.
statement_type STRING Typ der Abfrageanweisung, sofern gültig. Beispiel: SELECT, INSERT, UPDATE oder DELETE.
priority STRING Die Priorität dieses Jobs
start_time TIMESTAMP Startzeit dieses Jobs.
end_time TIMESTAMP Endzeit dieses Jobs.
query STRING SQL-Abfragetext. Hinweis: Nur die Ansicht JOBS_BY_PROJECT hat die Spalte query.
state STRING Ausführungsstatus des Jobs. Gültige Statuswerte sind PENDING, RUNNING und DONE.
reservation_id STRING Name der primären Reservierung, die diesem Job gegebenenfalls zugewiesen ist. Wenn Ihr Job in einem Projekt ausgeführt wurde, das einer Reservierung zugewiesen ist, hat er das Format reservation-admin-project:reservation-location.reservation-name.
total_bytes_processed INTEGER Gesamtzahl der vom Job verarbeiteten Byte.
total_slot_ms INTEGER Slot-Millisekunden für den Job über seine gesamte Dauer.
error_result RECORD Fehlerdetails (falls vorhanden) als ErrorProto.
cache_hit BOOLEAN Ob die Abfrageergebnisse dieses Jobs aus einem Cache stammen.
destination_table RECORD Zieltabelle für Ergebnisse (falls vorhanden).
referenced_tables RECORD Array aus Tabellen, auf die der Job verweist.
labels RECORD Array aus Labels, die als Strings in der Form key, valueauf den Job angewendet werden.
timeline RECORD Abfragezeitachse des Jobs. Enthält Snapshots der Abfrageausführung.
job_stages RECORD Abfragephasen des Jobs
total_bytes_billed INTEGER Wenn das Projekt für die Verwendung von On-Demand-Preisen konfiguriert ist, enthält dieses Feld die Gesamtzahl der für den Job in Rechnung gestellten Byte. Wenn das Projekt für die Verwendung von Pauschalpreisen konfiguriert ist, werden Ihnen keine Byte in Rechnung gestellt. Dieses Feld dient nur zur Information.

Datenaufbewahrung

Derzeit werden nur die letzten 180 Tage des Jobverlaufs in INFORMATION_SCHEMA-Jobansichten aufbewahrt.

Regionalität

Die INFORMATION_SCHEMA-Jobansichten von BigQuery sind regionalisiert. Für die Abfrage dieser Ansichten benötigen Sie einen Regions-Qualifier.

Beispiele

Beispiel 1: Durchschnittliche Slot-Auslastung

Im folgenden Beispiel wird die durchschnittliche Slot-Auslastung für alle Abfragen in den letzten sieben Tagen für ein bestimmtes Projekt berechnet. Beachten Sie, dass diese Berechnung am besten für Projekte mit gleichbleibender Slot-Nutzung im Wochenverlauf funktioniert. Wenn Ihr Projekt keine konsistente Slot-Nutzung aufweist, ist diese Zahl möglicherweise niedriger als erwartet.

Wenn Sie die Abfrage für ein anderes Projekt als Ihr Standardprojekt ausführen möchten, fügen Sie die Projekt-ID im folgenden Format hinzu: `project_id`.`region-region_name`.INFORMATION_SCHEMA.view. Beispiel: `myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT.

So führen Sie die Abfrage aus:

Console

  1. Öffnen Sie in der Cloud Console die Seite "BigQuery".

    Zur Seite "BigQuery"

  2. Geben Sie im Feld Abfrageeditor die folgende Standard-SQL-Abfrage ein. Für INFORMATION_SCHEMA muss die Standard-SQL-Syntax verwendet werden. In der Cloud Console ist Standard-SQL die Standardsyntax.

    SELECT
 SUM(total_slot_ms) / (1000*60*60*24*7) AS avg_slots
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
 -- Filter by the partition column first to limit the amount of data scanned. Eight days
 -- allows for jobs created before the 7 day end_time filter.
 creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 8 DAY) AND CURRENT_TIMESTAMP()
 AND job_type = "QUERY"
 AND end_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND CURRENT_TIMESTAMP()

  3. Klicken Sie auf Ausführen.

bq

Verwenden Sie den Befehl query und geben Sie dabei die Standard-SQL-Syntax mit dem Flag --nouse_legacy_sql oder --use_legacy_sql=false an. Für Abfragen von INFORMATION_SCHEMA ist die Standard-SQL-Syntax erforderlich.

So führen Sie die Abfrage aus:

bq query --nouse_legacy_sql \
'SELECT
   SUM(total_slot_ms) / (1000*60*60*24*7) AS avg_slots
 FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
 WHERE
   -- Filter by the partition column first to limit the amount of data scanned. Eight days
   -- allows for jobs created before the 7 day end_time filter.
   creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 8 DAY) AND CURRENT_TIMESTAMP()
   AND job_type = "QUERY"
   AND statement_type != "SCRIPT"
   AND end_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND CURRENT_TIMESTAMP()'

Die Ergebnisse sollten so aussehen:

  +------------+
  | avg_slots  |
  +------------+
  | 3879.1534  |
  +------------+

Sie können die Nutzung einer bestimmten Reservierung mit WHERE reservation_id = "…" prüfen. Dies kann hilfreich sein, um die prozentuale Nutzung einer Reservierung über einen bestimmten Zeitraum zu ermitteln. Bei Skriptjobs meldet der übergeordnete Job auch die gesamte Slot-Nutzung seiner untergeordneten Jobs. Verwenden Sie zur Vermeidung einer doppelten Zählung WHERE statement_type != "SCRIPT", um den übergeordneten Job auszuschließen.

Wenn Sie stattdessen die durchschnittliche Slot-Auslastung für einzelne Jobs prüfen möchten, verwenden Sie total_slot_ms / TIMESTAMP_DIFF(end_time,start_time, MILLISECOND).

Beispiel 2: Jobverlauf laden

Im folgenden Beispiel werden alle Nutzer oder Dienstkonten aufgelistet, die einen Batch-Ladejob für ein bestimmtes Projekt gesendet haben. Da keine Zeitgrenze angegeben ist, durchsucht diese Abfrage den gesamten verfügbaren Verlauf (z. B. die letzten 30 Tage).

Wenn Sie die Abfrage für ein anderes Projekt als Ihr Standardprojekt ausführen möchten, fügen Sie die Projekt-ID im folgenden Format hinzu: `project_id`.`region-region_name`.INFORMATION_SCHEMA.view. Beispiel: `myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT.

So führen Sie die Abfrage aus:

Console

  1. Öffnen Sie in der Cloud Console die Seite "BigQuery".

    Zur Seite "BigQuery"

  2. Geben Sie im Feld Abfrageeditor die folgende Standard-SQL-Abfrage ein. Für INFORMATION_SCHEMA muss die Standard-SQL-Syntax verwendet werden. In der Cloud Console ist Standard-SQL die Standardsyntax.

    SELECT
 DISTINCT(user_email) AS user
FROM
 `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
 job_type = "LOAD"

  3. Klicken Sie auf Ausführen.

bq

Verwenden Sie den Befehl query und geben Sie dabei die Standard-SQL-Syntax mit dem Flag --nouse_legacy_sql oder --use_legacy_sql=false an. Für Abfragen von INFORMATION_SCHEMA ist die Standard-SQL-Syntax erforderlich.

So führen Sie die Abfrage aus:

bq query --nouse_legacy_sql \
'SELECT
   DISTINCT(user_email) AS user
 FROM
   `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
 WHERE
   job_type = "LOAD"'

Die Ergebnisse sollten so aussehen:

  +--------------+
  | user         |
  +--------------+
  | abc@xyz.com  |
  +--------------+
  | def@xyz.com  |
  +--------------+

Beispiel 3: Die teuersten Jobs

Das folgende Beispiel zeigt, wie Sie die fünf Jobs ermitteln, die in einer Organisation die meisten Byte für den aktuellen Tag gescannt haben. Sie können weiter nach statement_type filtern, um zusätzliche Informationen wie Ladevorgänge, Exporte und Abfragen einzusehen.

Wenn Sie die Abfrage für ein anderes Projekt als Ihr Standardprojekt ausführen möchten, fügen Sie die Projekt-ID im folgenden Format hinzu: `project_id`.`region-region_name`.INFORMATION_SCHEMA.view. Beispiel: `myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT.

So führen Sie die Abfrage aus:

Console

  1. Öffnen Sie in der Cloud Console die Seite "BigQuery".

    Zur Seite "BigQuery"

  2. Geben Sie im Feld Abfrageeditor die folgende Standard-SQL-Abfrage ein. Für INFORMATION_SCHEMA muss die Standard-SQL-Syntax verwendet werden. In der Cloud Console ist Standard-SQL die Standardsyntax.

    SELECT
 job_id,
 user_email,
 total_bytes_processed
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
WHERE EXTRACT(DATE FROM  creation_time) = current_date()
ORDER BY total_bytes_processed DESC
LIMIT 5

  3. Klicken Sie auf Ausführen.

bq

Verwenden Sie den Befehl query und geben Sie dabei die Standard-SQL-Syntax mit dem Flag --nouse_legacy_sql oder --use_legacy_sql=false an. Für Abfragen von INFORMATION_SCHEMA ist die Standard-SQL-Syntax erforderlich.

So führen Sie die Abfrage aus:

bq query --nouse_legacy_sql \
'SELECT
   job_id,
   user_email,
   total_bytes_processed
 FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
 WHERE EXTRACT(DATE FROM  creation_time) = current_date()
 ORDER BY total_bytes_processed DESC
 LIMIT 5'

Die Ergebnisse sollten so aussehen:

  +--------------+--------------+---------------------------+
  | job_id       |  user_email  |  total_bytes_processed    |
  +--------------+--------------+---------------------------+
  | bquxjob_1    |  abc@xyz.com |    999999                 |
  +--------------+--------------+---------------------------+
  | bquxjob_2    |  def@xyz.com |    888888                 |
  +--------------+--------------+---------------------------+
  | bquxjob_3    |  ghi@xyz.com |    777777                 |
  +--------------+--------------+---------------------------+
  | bquxjob_4    |  jkl@xyz.com |    666666                 |
  +--------------+--------------+---------------------------+
  | bquxjob_5    |  mno@xyz.com |    555555                 |
  +--------------+--------------+---------------------------+

Beispiel 4: Ausstehende und laufende Jobs

Im folgenden Beispiel werden Jobs aufgeführt, die vom aktuellen Nutzer gestartet wurden und sich derzeit im Status "Ausstehend" oder "Wird ausgeführt" befinden.

Wenn Sie die Abfrage für ein anderes Projekt als Ihr Standardprojekt ausführen möchten, fügen Sie die Projekt-ID im folgenden Format hinzu: `project_id`.`region-region_name`.INFORMATION_SCHEMA.view. Beispiel: `myproject`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT.

So führen Sie die Abfrage aus:

Console

  1. Öffnen Sie in der Cloud Console die Seite "BigQuery".

    Zur Seite "BigQuery"

  2. Geben Sie im Feld Abfrageeditor die folgende Standard-SQL-Abfrage ein. Für INFORMATION_SCHEMA muss die Standard-SQL-Syntax verwendet werden. In der Cloud Console ist Standard-SQL die Standardsyntax.

    SELECT
 job_id,
 creation_time,
 query
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
WHERE state != "DONE"

  3. Klicken Sie auf Ausführen.

bq

Verwenden Sie den Befehl query und geben Sie dabei die Standard-SQL-Syntax mit dem Flag --nouse_legacy_sql oder --use_legacy_sql=false an. Für Abfragen von INFORMATION_SCHEMA ist die Standard-SQL-Syntax erforderlich.

So führen Sie die Abfrage aus:

bq query --nouse_legacy_sql \
'SELECT
   job_id,
   creation_time,
   query
 FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
 WHERE state != "DONE"'

Die Ergebnisse sollten so aussehen:

  +--------------+--------------+----------------------------------------------+
  | job_id       |  creation_time            |  query                          |
  +--------------+--------------+----------------------------------------------+
  | bquxjob_1    |  2019-10-10 00:00:00 UTC  |  SELECT ... FROM dataset.table1 |
  +--------------+--------------+----------------------------------------------+
  | bquxjob_2    |  2019-10-10 00:00:01 UTC  |  SELECT ... FROM dataset.table2 |
  +--------------+--------------+----------------------------------------------+
  | bquxjob_3    |  2019-10-10 00:00:02 UTC  |  SELECT ... FROM dataset.table3 |
  +--------------+--------------+----------------------------------------------+
  | bquxjob_4    |  2019-10-10 00:00:03 UTC  |  SELECT ... FROM dataset.table4 |
  +--------------+--------------+----------------------------------------------+
  | bquxjob_5    |  2019-10-10 00:00:04 UTC  |  SELECT ... FROM dataset.table5 |
  +--------------+--------------+----------------------------------------------+

Tipp