Jobmetadaten mit INFORMATION_SCHEMA abrufen

INFORMATION_SCHEMA umfasst mehrere Ansichten, über die auf Metadaten von Datasets, Abläufe, Tabellen und Reservierungen zugegriffen werden kann.

Sie können die Ansicht INFORMATION_SCHEMA.JOBS_BY_* abfragen, um Echtzeit-Metadaten zu BigQuery-Jobs abzurufen. 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_ORGANIZATION erfordert bigquery.jobs.listAll für die Organisation und ist in den Rollen Organization Owner und Organization Admin verfügbar. Beachten Sie, dass JOBS_BY_ORGANIZATION nur Nutzern mit definierten Google Cloud-Organisationen zur Verfügung steht.

Schema

Wenn Sie die Ansichten des Typs INFORMATION_SCHEMA.JOBS_BY_* abfragen, enthalten die Abfrageergebnisse für jeden BigQuery-Job eine Zeile.

  • 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_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
project_id STRING (Clustering-Spalte) ID des Projekts.
project_number INTEGER Nummer des Projekts
user_email STRING (Clustering-Spalte) E-Mail-Adresse oder Dienstkonto des Nutzers, der den Job ausgeführt hat.
job_id STRING ID des Jobs. Beispiel: bquxjob_1234
job_type STRING Der Typ des Jobs. Kann QUERY, LOAD , EXTRACT, COPY oder UNKNOWN sein.
statement_type STRING Der Typ der Abfrageanweisung, sofern gültig. Beispiel: SELECT, INSERT, UPDATE oder DELETE.
start_time TIMESTAMP Startzeit dieses Jobs
end_time TIMESTAMP Endzeit dieses Jobs
query STRING SQL-Abfragetext. Hinweis: Die Spalte query ist in der Ansicht JOBS_BY_ORGANIZATION nicht enthalten.
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.
total_bytes_processed INTEGER Gesamtzahl der vom Job verarbeiteten Byte
total_slot_ms INTEGER Slotmillisekunden für den Job über seine gesamte Dauer.
error_result RECORD Details zu etwaigen Fehlern.
cache_hit BOOLEAN Gibt an, ob die Abfrageergebnisse im Cache gespeichert wurden.
destination_table RECORD Zieltabelle für etwaige Ergebnisse.
referenced_tables RECORD Array von Tabellen, auf die der Job verweist.
labels RECORD Array von Labels, die auf den Job angewendet werden.
timeline RECORD Abfragezeitachse des Jobs. Enthält Snapshots der Abfrageausführung.
job_stages RECORD Abfragephasen des Jobs.

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. Zum Abfragen dieser Ansichten müssen Sie einen relevanten Regionsnamen im Format `region-region_name`.INFORMATION_SCHEMA.view voranstellen.

Beispiel:

  • Verwenden Sie `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT, um Daten in der Multiregion US abzufragen.
  • Verwenden Sie `region-eu`.INFORMATION_SCHEMA.JOBS_BY_PROJECT, um Daten in der Multiregion EU abzufragen.
  • Verwenden Sie `region-asia-northeast1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT, um Daten in der Region asia-northeast1 abzufragen.

Die Liste der verfügbaren Regionen finden Sie hier.

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.

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.

Das geht so:

Console

  1. Öffnen Sie in der Cloud Console die BigQuery-Web-UI.

    Zur Cloud Console

  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
     job_type = "QUERY"
     AND end_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND CURRENT_TIMESTAMP()
    
  3. Klicken Sie auf Ausführen.

Befehlszeile

Verwenden Sie den Befehl query und geben Sie 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
   job_type = "QUERY"
   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.

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.

Das geht so:

Console

  1. Öffnen Sie in der Cloud Console die BigQuery-Web-UI.

    Zur Cloud Console

  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.

Befehlszeile

Verwenden Sie den Befehl query und geben Sie 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 abzufragen.

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.

Das geht so:

Console

  1. Öffnen Sie in der Cloud Console die BigQuery-Web-UI.

    Zur Cloud Console

  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.

Befehlszeile

Verwenden Sie den Befehl query und geben Sie 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.

Das geht so:

Console

  1. Öffnen Sie in der Cloud Console die BigQuery-Web-UI.

    Zur Cloud Console

  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.

Befehlszeile

Verwenden Sie den Befehl query und geben Sie 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 |
  +--------------+--------------+----------------------------------------------+
  

Weitere Informationen