BigQuery-Reservierungen und -Zuweisungen mit Dataform automatisieren

Einer unserer Kunden betreibt auf BigQuery ein Forschungs- und Entwicklungsprojekt, das sowohl von den Development- als auch von den Data-Science-Teams genutzt wird. Die Teams haben ein typisches Nutzungsmuster:

1. Interaktive Abfragen finden überwiegend werktags während der Geschäftszeiten statt.
2. Hohe Abfrage-Parallelität bei kurzen Antwortzeiten.
3. Bestimmte Tasks laufen geplant nachts oder am Wochenende.

Um diese Anforderungen abzudecken, nutzt das Projekt während der Arbeitszeiten den ‚On-Demand‘-Plan – für niedrige Abfrage-Latenz und hohe Parallelität. Außerhalb der Arbeitszeiten kommt die ‚Enterprise Edition‘ mit maximal 100 Slots zum Einsatz.

Ein Automatisierungs-Flow wechselt den Pricing-Plan entsprechend.

Bislang bietet Google keine Out-of-the-Box-Lösung, um automatisch zwischen den BigQuery-Pricings zu wechseln. Es gibt jedoch eine Schnittstelle, über die sich Reservierungen via CLI oder SQL verwalten lassen.

Im Folgenden setzen wir Dataform und seine SQLX-Sprache ein, um Pricing-Plan-Updates effizienter zu machen. So lassen sich sämtliche Änderungen an Reservierungen und Zuweisungen nahtlos in einem Dataform-Workflow steuern – inklusive Versionierung in Git.

Ein weiterer Vorteil von Dataform: die Einfachheit. Die gesamte Arbeit erfolgt per SQL direkt in BigQuery, ohne dass externe Services nötig wären. Dataform ist kostenlos – es fallen keine zusätzlichen Kosten an.

Zur Vorbereitung legen wir (manuell) eine ‚Enterprise Edition‘-Reservierung test_rd mit maximal 100 Slots an.

Für die Verwaltung der Reservierungskonfiguration und der Zuweisungsänderungen nutzen wir anschließend die BigQuery Reservation API (SQL DDL).

SQL-Codebeispiel zum Anlegen einer Zuweisung in der Region US mit der Reservierung ndv-rd und der assignment_id df_rd:

CREATE ASSIGNMENT `ndv-playground-bq-mgmt.region-us.ndv-rd.df_rd`
OPTIONS( assignee="projects/ndv-playground", job_type="QUERY");

Falls Sie Dataform noch nicht kennen, empfehlen wir den Blogpost Build SQL pipelines to BigQuery with Dataform sowie den Quick Start.

Den vollständigen Code zu diesem Beitrag finden Sie in diesem GitHub-Repository.

Der automatisierte Workflow entsteht in sechs Schritten:

[1] Repository anlegen und Rollen zuweisen

Um im BigQuery-Admin-Projekt ein Dataform-Repository anzulegen, wechseln Sie in den Bereich Dataform und starten dort die Repository-Erstellung. (Dataform-Repository anlegen)

Damit die Ausführung gelingt, benötigen sowohl der Developer-Principal (User) als auch der Dataform-SA (Service Account) während der Workflow-Ausführung folgende Rollen: bigquery.resourceAdmin, bigquery.user

Für die Rollenzuweisung an den SA (Code unten) verwenden Sie die SA-ID, die nach dem Anlegen des Repositorys in der Meldung angezeigt wird.

Der Dataform-SA

gcloud projects add-iam-policy-binding <PROJECT-ID> \
--member="<service-account-dataform>" \
--role=roles/bigquery.resourceAdmin --condition=None
gcloud projects add-iam-policy-binding <PROJECT-ID> \
--member="<service-account-dataform>" \
--role=roles/bigquery.user --condition=None

[2] Development-Workspace anlegen

Folgen Sie dieser Anleitung, um einen neuen Dataform-Development-Workspace zu erstellen: Dataform-Workspace anlegen.

Nach der Initialisierung enthält der Workspace mehrere SQLX-Beispielmethoden sowie eine Konfigurationsdatei namens "dataform.json". Die Beispieldateien (first_view.sqlx, second_view.sqlx) können Sie löschen.

Zur Konfiguration der Dataform-Methoden ergänzen Sie den Abschnitt "vars" mit der unten gezeigten Parameterliste. Dieser Abschnitt steht hinter dem letzten vorhandenen Parameter und enthält die folgenden Werte. Hier definieren Sie die Default-Werte für jeden Parameter; im Workflow lassen sie sich später überschreiben.

1. Setzen Sie unbedingt vor dem Abschnitt "vars" ein Komma ",".
2. Ersetzen Sie die mit "<>" markierten Parameter (die "<>" selbst entfernen).

"vars": {
"assigned_project":"'<R&D project>'",
"Assignment_id":"`df_rd_assignment`",
"reservation_prefix":"<BQ admin project>.region-<reservation location>",
"reservation_name":"`<R&D reservation name>`",
"region":"<reservation location>"
}

So sieht das in unserer Implementierung aus:

"vars": {
"assigned_project":"'ndv-playground'",
"Assignment_id":"`df_rd_assignment`",
"reservation_prefix":"`ndv-playground-bq-mgmt.region-us`",
"reservation_name":"`ndv-rd`",
"region":"region-us"
}

[3] SQLX-Methoden entwickeln

In diesem Schritt schreiben wir den SQLX-Code zur Verwaltung von Reservierungen und Zuweisungen.

• Legen Sie unter dem Ordner 'definitions' eine Datei drop_and_create_assignment.sqlx an.
• Fügen Sie den folgenden SQLX-Code ein, um alle Projektzuweisungen zu löschen und die benötigte Zuweisung neu anzulegen.
• Ein ✅ bestätigt, dass die Kompilierung erfolgreich war:

Hier der SQLX-Code für drop_and_create_assignment.sqlx:

config {type: "operations"}
BEGIN

CREATE TEMP TABLE to_drop (drop_string STRING) AS (
SELECT
CONCAT("DROP ASSIGNMENT IF EXISTS `",project_id,".",SPLIT(ddl,".")[OFFSET(1)],
       ".",reservation_name,".",assignment_id,"`")
FROM
${dataform.projectConfig.vars.region}.INFORMATION_SCHEMA.ASSIGNMENTS_BY_PROJECT
WHERE
assignee_id = ${dataform.projectConfig.vars.assigned_project}
AND job_type = 'QUERY' );

FOR drop_statement IN (
SELECT drop_string FROM to_drop)
DO EXECUTE IMMEDIATE drop_statement.drop_string;
END FOR;

END;

Mit unserer Konfiguration entsteht daraus dieser generierte Code:

BEGIN
CREATE TEMP TABLE to_drop (drop_string STRING) AS (
SELECT
CONCAT("DROP ASSIGNMENT IF EXISTS `",project_id,".",SPLIT(ddl,".")[OFFSET(1)],".",reservation_name,".",assignment_id,"`")
FROM
region-us.INFORMATION_SCHEMA.ASSIGNMENTS_BY_PROJECT
WHERE
assignee_id = 'ndv-playground' AND job_type = 'QUERY' );
FOR drop_statement IN (
SELECT drop_string FROM to_drop)
DO EXECUTE IMMEDIATE drop_statement.drop_string;
END FOR;
END;

CREATE ASSIGNMENT
`ndv-playground-bq-mgmt.region-us`.`ndv-rd`.`df_rd_assignment`
OPTIONS(
assignee=CONCAT("projects/",'ndv-playground'),
job_type="QUERY");

[4] SQLX-Methoden testen und verifizieren

Hinweis vor dem Test: Die Konfiguration der zugewiesenen Projekte ändert sich gemäß dem Wert in dataform.json. Es lohnt sich, zunächst auf einem ‚Test‘-Projekt zu prüfen.

Für die Ausführung haben Sie zwei Optionen: ‚Run‘ führt den Code mit Ihren Anmeldedaten aus, ‚Start Execution‘ über den Dataform-SA, wie in der unten gezeigten Meldung beschrieben.

Execute all actions, or select a subset of actions. Service account [email protected] will be used.

Prüfen Sie die Ausführungsausgabe auf This statement created a new assignment und kontrollieren Sie anschließend die Änderungen in der Ansicht ‚Capacity management‘.

Die Reservierung ist dem Projekt zugewiesen

Sobald die SA-Verifizierung erfolgreich war, können Sie die Änderungen committen und in den Default-Branch pushen. Damit ist die Entwicklung abgeschlossen und alles bereit für das Release.

[5] Dataform-Release-Konfiguration anlegen

Um einen Snapshot des Codes zu erstellen und mit überschriebenen Parametern auszuführen, nutzen Sie die release configuration. Der Einfachheit halber konfigurieren wir nur einen Parameter und setzen den Refresh auf Never.

Wichtig: nach jeder Änderung neu kompilieren.

Das R&D-Projekt erhält zwei Releases: eines für ‚On-Demand‘ mit reservation_name= `none``` und eines für die ‚Enterprise Edition‘ mit reservation_name=ndv-rd``` (achten Sie auf die ``""``).

Testen Sie die "Release-Konfiguration" mit der ausgewählten Action:

ndv-playground-bq-mgmt.dataform.drop_and_create_assignment

Release für die Enterprise-Reservierung

Release für die On-Demand-Reservierung

[6] Dataform-Workflow-Konfiguration anlegen

Die Workflow-Konfiguration legt fest, wie SQLX-Actions automatisch ausgeführt werden. Grundlage jedes Workflows ist eine "Release-Konfiguration" mit ihren Parametern. Die ausgewählten Actions starten dann über einen definierten Trigger.

Das R&D-Projekt hat zwei Workflows mit unterschiedlichen Release-Konfigurationen, die zu unterschiedlichen Zeitpunkten getriggert werden. Die Startzeit wird im 'cron'-Format definiert.

On-Demand-Workflow

Enterprise-Edition-Workflow