Wie man eine Pivot-Tabelle in Google BigQuery erstellt

Um die verstrichenen Zeiten der Terraform Cloud-Laufphasen zu berechnen, musste ich eine Pivot-Tabelle in Google BigQuery erstellen. Da die klassische SQL-Abfrage sehr teuer war, änderte ich sie, um den BigQuery PIVOT-Operator zu verwenden, der 750 Mal billiger war. Mit Terraform Cloud können Sie Ihre Infrastruktur auf Unternehmensebene verwalten. Es handelt sich um einen verwalteten Service, und die Preise richten sich nach der Anzahl der Benutzer und der Anzahl der gleichzeitigen Läufe, die Sie benötigen. Standardmäßig erhalten Sie einen einzigen Agenten oder Runner. Zusätzliche Runner kosten ein paar tausend Dollar pro Jahr. Sie müssen also ein Gleichgewicht zwischen Durchsatz und Kosten finden. Dazu benötigen Sie Daten über die Auslastung der Läufe. Terraform Cloud stellt von Haus aus keine Daten zur Verfügung, also habe ich den Webhook für Terraform Run Notifications aktiviert und die Daten in BigQuery gestreamt. Um nützliche Informationen zu erhalten, musste ich die Daten drehen.

Terraform-Lauf-Benachrichtigungen

Der Terraform Run Notification Webhook sendet eine Benachrichtigung, wenn sich der Status eines Laufs ändert. Diese Benachrichtigungen sehen wie folgt aus:

{
  "payload_version": 1,
  "notification_configuration_id": "nc-Mra7BwRJgiyEEPo4",
  "run_url": "https://app.terraform.io/app/binx-io/my-ws/runs/run-BbUuHJv3w1kztNDi",
  "run_id": "run-BbUuHJv3w1kztNDi",
  "run_message": "Queued manually using Terraform",
  "run_created_at": "2021-06-12T12:11:20.000Z",
  "run_created_by": "mjvanholsteijn",
  "workspace_id": "ws-tgHKyu5U8a3dDUGC",
  "workspace_name": "my-ws",
  "organization_name": "binx-io",
  "notifications": [
    {
      "message": "Run Created",
      "trigger": "run:created",
      "run_status": "pending",
      "run_updated_at": "2021-06-12T12:11:20.000Z",
      "run_updated_by": "mjvanholsteijn"
    }
  ]
}

Die Benachrichtigung meldet nur den Zeitpunkt der Statusänderung. Sie meldet nicht die Zeit, die seit der letzten Zustandsänderung verstrichen ist. Als ich all diese Ereignisse in BigQuery lud, musste ich einen intelligenten Weg finden, um alle Zeitstempel der entsprechenden Zustandsänderungen in einer einzigen Zeile zu sammeln.

Entstörung der Benachrichtigungen

Wie Sie aus dem Beispiel ersehen können, werden die Benachrichtigungen als Array modelliert. Terraform sendet also potenziell mehrere Statusänderungen in einer einzigen Benachrichtigung. Um daraus eine einfache Tabelle zu machen, verwende ich den Operator unnest:

SELECT
  e.workspace_id,
  e.run_id,
  n.run_updated_at,
  n.run_status
FROM
  terraform_cloud_notifications.events e,
  UNNEST(notifications) AS n

Das liefert eine Tabelle mit einer Zeile für jede der Benachrichtigungen im Array:

|workspace_id       |run_id              |run_updated_at     |run_status|
|-------------------|--------------------|----------|--------|---|------|
|ws-tgHKyu5U8a3dDUGC|run-ubJZBXt4aT2DprjJ|2021-06-13 20:49:02|applied|
|ws-tgHKyu5U8a3dDUGC|run-EMYsYn6oZd36Bk2o|2021-06-13 19:47:53|pending|
|ws-tgHKyu5U8a3dDUGC|run-ubJZBXt4aT2DprjJ|2021-06-13 20:48:53|planned|
|ws-tgHKyu5U8a3dDUGC|run-jLcoV2rTiWpncAzp|2021-06-13 19:43:37|applying|
|ws-tgHKyu5U8a3dDUGC|run-jLcoV2rTiWpncAzp|2021-06-13 19:43:27|planning|
|ws-tgHKyu5U8a3dDUGC|run-BbUuHJv3w1kztNDi|2021-06-12 12:11:20|pending|

Um die Dauer der einzelnen Phasen zu berechnen, musste ich die Tabelle drehen.

Einfaches SQL verwenden

Da ich den PIVOT-Operator nicht kannte, bestand meine erste Lösung darin, einfaches altes SQL zu verwenden, um dieses Problem zu lösen. Die folgende Anweisung verwendet eine Unterauswahl für jede der Ereignisbenachrichtigungszeiten:

SELECT
  workspace_id,
  run_id,
  (SELECT ANY_VALUE(n.run_updated_at) 
   FROM terraform_cloud_notifications.events, unnest(notifications) n
   WHERE workspace_id = e.workspace_id AND run_id = e.run_id AND n.run_status = 'pending') as pending,
  (SELECT ANY_VALUE(n.run_updated_at) 
   FROM terraform_cloud_notifications.events, unnest(notifications) n 
   WHERE workspace_id = e.workspace_id AND run_id = e.run_id AND n.run_status = 'planning') as planning,
  (SELECT ANY_VALUE(n.run_updated_at) 
   FROM terraform_cloud_notifications.events, unnest(notifications) n 
   WHERE workspace_id = e.workspace_id AND run_id = e.run_id AND n.run_status = 'planned') as planned,
  (SELECT ANY_VALUE(n.run_updated_at) 
   FROM terraform_cloud_notifications.events, unnest(notifications) n 
   WHERE workspace_id = e.workspace_id AND run_id = e.run_id AND n.run_status = 'applying') as applying,
  (SELECT ANY_VALUE(n.run_updated_at) 
   FROM terraform_cloud_notifications.events, unnest(notifications) n 
   WHERE workspace_id = e.workspace_id AND run_id = e.run_id AND n.run_status = 'applied') as applied,
FROM
( SELECT DISTINCT workspace_id, run_id
  FROM terraform_cloud_notifications.events
) e

Es führt zu dem erwarteten Ergebnis:

Aber die Kosten waren horrend! Die Abfrage lieferte das Ergebnis in 1,5 Sekunden und verbrauchte fast 30 Sekunden Slot-Zeit. Für eine Tabelle mit nur einer Handvoll Zeilen ist das natürlich nicht akzeptabel.

Pivot-Tabelle in Google BigQuery

Nach einigem Googeln habe ich herausgefunden, dass der PIVOT-Operator genau das tut, was ich will. Die folgende Anweisung fügt die Spalten pending, planning, planned, applying, applied mit
hinzu, wobei der Wert der Spalte auf max(run_updated_at) gesetzt wird.

SELECT
  *
FROM (
  SELECT
    e.workspace_id,
    e.run_id,
    n.run_updated_at,
    n.run_status
  FROM
    terraform_cloud_notifications.events e,
    UNNEST(notifications) AS n 
) 
PIVOT ( 
    MAX(run_updated_at)
    FOR run_status IN ('pending','planning','planned', 'applying','applied')
)

Es führt zu folgendem Ergebnis:

Die Daten wurden in 0,2 Sekunden zurückgegeben und verbrauchten nur 0,04 Sekunden der Slot-Zeit. Das ist 750 Mal billiger. Wie genial ist das denn!

Fazit

Nachdem ich zunächst das gute alte verschachtelte SQL für Pivot-Tabellen ausprobiert hatte, stellte ich fest, dass der PIVOT-Operator sehr einfach zu verwenden ist. Er ist wirklich süß. Bild von Harry Strauss von Pixabay