BigQuery-Versteckspiel: Auf der Jagd nach verlorenen Tabellen und versteckten Datasets!

In der rasanten Welt der Datenanalyse sticht Google BigQuery als leistungsstarkes, serverloses Data Warehouse hervor, das blitzschnelle SQL-Abfragen über riesige Datenmengen ermöglicht. Ob Data Scientist, Data Engineer oder Analytics-Profi – wer sich durch die vielfältigen Möglichkeiten von BigQuery bewegt, fühlt sich oft wie auf Schatzsuche. Wächst Ihre Organisation, wächst meist auch Ihr BigQuery-Footprint mit – und eine der häufigsten Hürden besteht darin, Tabellen und Datasets über verschiedene Geltungsbereiche hinweg effizient aufzulisten.

Dieser Blogbeitrag führt Sie durch die unterschiedlichen Methoden, mit denen sich Tabellen und Datasets in BigQuery auflisten lassen – mit dem konkreten Endziel, diese Assets organisationsweit zu erfassen. Dabei zeigen wir auch die jeweiligen Grenzen jedes Ansatzes auf.

Beim Durchgehen der verschiedenen Techniken – von einfachen GUI-Interaktionen bis hin zu fortgeschrittenen SQL-Abfragen und API-Aufrufen – erhalten Sie Einblicke in deren Stärken und Einschränkungen. Zum Schluss stellen wir eine Lösung vor, die maximale Flexibilität bietet, dafür aber etwas mehr Aufwand erfordert – sofern wir Ihnen die Hauptarbeit nicht ohnehin schon abgenommen haben!

Wir gehen folgende Optionen durch:

• Web-UI der Google Cloud Console
• BigQuery CLI
• Dataplex (Data Catalog)
• INFORMATION_SCHEMA
• Eigenes Skript

Und betrachten dabei einige Bewertungskriterien:

• Geltungsbereich der Auflistung
• Detailtiefe
• Flexibilität
• Kosten

Ein guter Ausgangspunkt ist die vertraute Web-UI der Google Cloud Console. Im BigQuery Studio auf der BigQuery-Produktseite werden Datasets und Tabellen Ihres aktuellen Projekts (sowie weiterer hinzugefügter Projekte) im Explorer-Bereich angezeigt. Sie können dort auch nach Ressourcen (Dataset, Tabelle oder View) per Name oder Label suchen – die Ergebnisse umfassen sowohl Projekte als auch Organisationen, auf die Sie Zugriff haben.

BigQuery-UI: Datasets (links) und Tabellen (rechts)

Vorteile:

• Direkt aus dem BigQuery Studio heraus erreichbar.
• Durchsucht Projekte und Organisationen – also keine Einschränkungen beim Geltungsbereich.
• Kostenfrei.

Nachteile:

• Liefert keine vollständige Liste aller Datasets oder Tabellen in Ihrer Organisation oder solcher, auf die Sie Zugriff haben.
• Stellt keine weiteren Metadaten zu den Tabellen bereit.

Wer wenig für Oberflächen übrig hat und seine Zeit lieber auf der Kommandozeile verbringt, dürfte mit der BigQuery CLI besser bedient sein.

Mit einer gegebenen PROJECT_ID lassen sich alle Datasets eines Projekts wie folgt auflisten:

bq ls --project_id $PROJECT_ID

BigQuery CLI: Datasets

Oder mit PROJECT_ID und DATASET_ID alle Tabellen eines Datasets:

bq ls --project_id $PROJECT_ID --dataset_id=$DATASET_ID

BigQuery CLI: Tabellen

Vorteile:

• Einfach zu bedienende CLI.
• Die Ausgabe lässt sich gut für eine schlanke Weiterverarbeitung nutzen, z. B. mit jq – insbesondere mit dem Flag --format: <none|json|prettyjson|csv|sparse|pretty>.
• Metadaten-Operationen sind kostenfrei.

Nachteile:

• Begrenzter Geltungsbereich: ein Projekt auf Dataset-Ebene und ein Dataset auf Tabellen-Ebene.
• Auch hier sind die zurückgegebenen Details begrenzt.

Wenn es darum geht, alle relevanten Daten Ihrer Organisation aufzuspüren, denkt man unweigerlich an Data Catalog! Data Catalog war früher ein eigenständiges Produkt, ist aber seit Mitte 2022 ein Feature von Dataplex. Dataplex ist die intelligente Datenmanagement-Plattform von Google Cloud, die Organisation, Sicherheit und Analyse von Daten über Data Lakes, Warehouses und Datenbanken hinweg automatisiert und Ihre Organisation bei Auffindbarkeit, Governance und Compliance unterstützt. Data Catalog fungiert dabei als zentrales Inventar Ihrer Daten-Assets.

Damit lässt sich organisations- und systemübergreifend suchen sowie nach Datentypen, Tags usw. filtern. Für unseren konkreten Anwendungsfall können wir alle Datasets und Tabellen einer bestimmten Organisation einfach abrufen, indem wir den passenden Filter in der UI setzen:

Das Schöne ist: Wer die CLI der UI vorzieht, kann dasselbe per gcloud-Befehl erreichen:

Für Datasets:

gcloud data-catalog search "type=dataset" --include-organization-ids=YOUR_ORG_ID

Dataplex: Datasets

Für Tabellen:

gcloud data-catalog search "type=table" --include-organization-ids=YOUR_ORG_ID

Dataplex: Tabellen

Vorteile:

• Sucht organisationsübergreifend UND geht über die reine BigQuery-Suche hinaus.

Nachteile:

• Data Catalog speichert verschiedene Arten von Metadaten (sowohl fachliche als auch technische), erfasst aber bestimmte Details wie Tabellengröße oder gespeicherte Bytes nicht.
• Weder Dataplex noch Data Catalog sind kostenfrei – die Kosten dürften für die meisten Anwendungsfälle aber überschaubar sein. Details siehe Preisseite.

BigQuery-Profis kennen das INFORMATION_SCHEMA bestens. Die BigQuery-INFORMATION_SCHEMA-Views sind schreibgeschützte, systemdefinierte Views, die Metadaten zu Ihren BigQuery-Objekten bereitstellen. Es gibt eine ganze Reihe verschiedener Views – einen umfassenden Überblick liefert die Dokumentation.

Für unseren Anwendungsfall sind insbesondere die Views SCHEMATA und TABLES interessant:

Mit dem SCHEMATA-View lassen sich alle Datasets innerhalb einer Region auflisten, z. B. für us-central1:

SELECT * FROM `region-us-central1`.INFORMATION_SCHEMA.SCHEMATA;

INFORMATION_SCHEMA: Datasets

Oder mit dem TABLES-View alle Tabellen einer Region, z. B. für us-central1:

SELECT * FROM `region-us-central1`.INFORMATION_SCHEMA.TABLES;

INFORMATION_SCHEMA: Tabellen

Vorteile:

• Einfacher, programmatischer Zugriff direkt per SQL in BigQuery.
• Sehr detaillierte Informationen; z. B. enthält der TABLES-View Felder wie creation_time und ddl; der TABLE_STORAGE-View liefert Informationen zu total_rows sowie zur Tabellengröße (sowohl physischer als auch logischer Speicher).

Nachteile:

• Informationen sind bestenfalls auf eine Region beschränkt (auch beim View auf Organisationsebene). Da die Liste der Regionen bereits recht lang ist und sich dynamisch ändern kann, ist Skripting auf dieser Ebene nicht ideal.
• Nicht kostenfrei – die Kosten dürften für die meisten Anwendungsfälle aber überschaubar sein.

Wie das so ist: Unsere technisch versierten Kunden haben uns herausgefordert und sich mit dem Wunsch an uns gewandt, einen Überblick über die gesamte Organisation hinweg zu erhalten und zugleich detaillierte Informationen zu Tabellen über alle Projekte hinweg abzurufen.

Am Ende haben wir ein kleines Skript geschrieben, das genau das leistet – Sie finden es auf unserem GitHub. Es setzt einige Umgebungsvariablen, mit denen ein Service-Account auf Organisationsebene bereitgestellt wird. Anschließend iteriert das Skript durch alle Projekte und Datasets dieser Organisation und ruft die Tabellen samt relevanten Details ab (in diesem Fall: Tabellengröße). Etwas Python-Grundwissen und Vertrautheit mit der BigQuery-API sind nötig, dafür bietet dieser Ansatz maximale Flexibilität (weitere Tabelleneigenschaften lassen sich der API-Doku entnehmen), und die Daten können in jedem beliebigen Format für die Weiterverarbeitung ausgegeben werden.

Vorteile:

• Maximale Flexibilität:

- Alle gewünschten Daten lassen sich aus der API extrahieren.

- Die Ausgabe kann beliebig gestaltet werden.

- Filter ergänzen oder das Skript nach Belieben erweitern – ganz wie Sie möchten.

• Kostenfrei – von DoiT für Sie.

Nachteile:

• Zusätzlicher Zeit- und Arbeitsaufwand für das Skript – aber den Großteil der Vorarbeit haben wir bereits erledigt!