Parametrisierte Dataflow-SQL-Abfragen verwenden

Auf dieser Seite wird erläutert, wie parametrisierte Dataflow-SQL-Abfragen ausgeführt werden.

Dataflow SQL unterstützt benannte Abfrageparameter und Positionsabfrageparameter. Geben Sie Abfrageparameter mit der folgenden Syntax an:

  • Benannte Parameter mit dem Zeichen @ gefolgt von einer Kennung wie z. B. @param_name
  • Positionsparameter mit dem Zeichen ?

Sie können entweder benannte Parameter oder Positionsparameter in einer Abfrage verwenden, aber nicht beides.

Parametrisierte Abfragen ausführen

Zum Ausführen einer parametrisierten Abfrage definieren Sie den Datentyp und den Wert der Abfrageparameter. Der Typ und der Wert der Abfrageparameter müssen mit dem Typ der Datenbankobjekte übereinstimmen, die die Parameter darstellen.

Sie können Abfrageparameter mit der Google Cloud Console oder der Google Cloud CLI definieren:

Console

Verwenden Sie zum Definieren von Abfrageparametern die Dataflow SQL-UI:

  1. Rufen Sie die Dataflow SQL-UI auf.

    Zur Dataflow SQL-UI

  2. Geben Sie die Dataflow SQL-Abfrage in den Abfrageeditor ein.

    Die folgende Abfrage wählt beispielsweise Taxifahrten aus, die mindestens den angegebenen Preis kosten:

    SELECT *
FROM pubsub.topic.`pubsub-public-data`.`taxirides-realtime`
WHERE
 ride_status = "dropoff"
 AND meter_reading >= @price_min

  3. Klicken Sie auf Cloud Dataflow-Job erstellen, um ein Feld mit Joboptionen zu öffnen.

  4. Füllen Sie im Bereich SQL-Abfrageparameter des Steuerfelds die Felder Name, Typ und Wert aus. Der Name des Parameters im Beispiel lautet price_min und der Typ ist FLOAT64.

    Wenn der Parameter positionsbezogen ist, lassen Sie das Feld Name weg.

  5. Optional: Wenn die Abfrage weitere Parameter enthält, klicken Sie auf Parameter hinzufügen.

Die Google Cloud Console unterstützt die Parameter ARRAY und STRUCT nicht. Verwenden Sie das Google Cloud CLI, um die Parameter ARRAY oder STRUCT anzugeben.

gcloud

Zum Definieren von Abfrageparametern verwenden Sie den Befehl gcloud dataflow sql query und die --parameter-Flags.

Sie können das --parameter-Flag für jeden Abfrageparameter wiederholen. Setzen Sie jedes --parameter-Flag auf name:type:value.

Der folgende Befehl führt eine parametrisierte Abfrage für einen Pub/Sub-Stream von Taxifahrten aus:

gcloud dataflow sql query \
  --job-name=job-name \
  --region=region \
  --bigquery-dataset=destination-dataset \
  --bigquery-table=destination-table \
  --parameter=status:STRING:dropoff \
  --parameter=price_min:FLOAT64:5.5 \
'SELECT *
FROM pubsub.topic.`pubsub-public-data`.`taxirides-realtime`
WHERE
  ride_status = @status
  AND meter_reading >= @price_min'

Wenn der Parameter positionsbezogen ist, setzen Sie die --parameter-Flags auf :type:value.

Arrays in parametrisierten Abfragen verwenden

Wenn Sie in einem Abfrageparameter einen Arraytyp verwenden möchten, setzen Sie den Typ auf ARRAY<data-type>. Erstellen Sie den Wert als kommagetrennte Liste von Elementen, die in eckige Klammern eingeschlossen sind, beispielsweise ["pickup", "enroute", "dropoff"].

Console

Die Dataflow SQL-UI unterstützt in Dataflow SQL-Abfragen keine ARRAY-Parameter.

gcloud

Um einen Arrayparameter zu definieren, setzen Sie das --parameter-Flag des Befehls gcloud dataflow sql query auf name:ARRAY<data-type>:value.

Der folgende Befehl führt eine Abfrage mit einem benannten Arrayparameter in einem Pub/Sub-Stream von Taxifahrten aus:

gcloud dataflow sql query \
  --job-name=job-name
  --region=region
  --bigquery-dataset=destination-dataset \
  --bigquery-table=destination-table \
  --parameter='status:ARRAY<STRING>:["pickup", "enroute", "dropoff"]' \
'SELECT *
FROM pubsub.topic.`pubsub-public-data`.`taxirides-realtime`
WHERE
  ride_status IN UNNEST(@status)'

Weitere Informationen finden Sie in der Dataflow SQL-Referenz für ARRAY-Typen.

Zeitstempel in parametrisierten Abfragen verwenden

Wenn Sie in einem Abfrageparameter einen Zeitstempel verwenden möchten, legen Sie den Typ auf STRING fest und erstellen den Wert im kanonischen Zeitstempelformat.

Console

Verwenden Sie zum Definieren von Zeitstempelparametern die Dataflow SQL-UI:

  1. Rufen Sie die Dataflow SQL-UI auf.

    Zur Dataflow SQL-UI

  2. Geben Sie die Dataflow SQL-Abfrage in den Abfrageeditor ein.

    Die folgende Abfrage wählt beispielsweise Taxifahrten aus, die nach dem angegebenen Datum stattgefunden haben:

    SELECT *
FROM pubsub.topic.`pubsub-public-data`.`taxirides-realtime`
WHERE
  event_timestamp >= TIMESTAMP (@date_min)

  3. Klicken Sie auf Cloud Dataflow-Job erstellen, um ein Feld mit Joboptionen zu öffnen.

  4. Klicken Sie im Abschnitt SQL-Abfrageparameter auf Parameter hinzufügen.

  5. Wählen Sie im Feld Typ die Option STRING aus.

  6. Geben Sie im Feld Wert einen Zeitstempel ein.

gcloud

Um einen Zeitstempelparameter zu definieren, setzen Sie das --parameter-Flag des Befehls gcloud dataflow sql query auf name:STRING:value.

Der folgende Befehl führt eine Abfrage mit einem benannten Zeitstempelparameter in einem Pub/Sub-Stream von Taxifahrten aus:

gcloud dataflow sql query \
  --job-name=job-name \
  --region=region \
  --bigquery-dataset=destination-dataset \
  --bigquery-table=destination-table \
  --parameter='date_min:STRING:2020-01-01 00:00:00.000 UTC' \
'SELECT *
FROM pubsub.topic.`pubsub-public-data`.`taxirides-realtime`
WHERE
  event_timestamp >= TIMESTAMP (@date_min)'

Weitere Informationen finden Sie in der Dataflow SQL-Referenz für die TIMESTAMP-Typen.

Strukturen in parametrisierten Abfragen verwenden

Wenn Sie eine Struktur in einem Abfrageparameter verwenden möchten, setzen Sie den Typ auf STRUCT<field-name data-type, ...>. Erstellen Sie den Wert als durch Kommas getrennte Liste von Schlüssel/Wert-Paaren.

Beispielsweise definiert STRUCT<date_min TIMESTAMP, status STRING> eine Struktur mit einem Feld namens date_min vom Typ TIMESTAMP und einem Feld namens status vom Typ STRING.

Console

Die Dataflow SQL-UI unterstützt in Dataflow SQL-Abfragen keine STRUCT-Parameter.

gcloud

Um einen Arrayparameter zu definieren, setzen Sie das --parameter-Flag des Befehls gcloud dataflow sql query auf name:STRUCT<field-name data-type, ...>:value.

Der folgende Befehl führt eine Abfrage mit einem benannten Strukturparameter in einem Pub/Sub-Stream von Taxifahrten aus:

gcloud dataflow sql query \
  --job-name=job-name \
  --region=region \
  --bigquery-dataset=destination-dataset \
  --bigquery-table=destination-table \
  --parameter='rides:STRUCT<riders_min INT64, status STRING>:
  {"riders_min": 2, "status": "dropoff"}' \
'SELECT *
FROM pubsub.topic.`pubsub-public-data`.`taxirides-realtime`
WHERE
  passenger_count >= @rides.riders_min
  AND ride_status = @rides.status'

Weitere Informationen finden Sie in der Dataflow SQL-Referenz für die STRUCT-Typen.