パラメータ化された Dataflow SQL クエリの使用

このページでは、パラメータ化された Dataflow SQL クエリを実行する方法について説明します。

Dataflow SQL のクエリでは、名前付きパラメータと位置パラメータがサポートされています。次の構文を使用してクエリパラメータを指定します。

@ 文字の後に識別子が続く名前付きパラメータ。例: @param_name
? 文字を含む位置パラメータ。

クエリでは名前付きパラメータと位置パラメータのいずれかを使用できますが、両方は使用できません。

パラメータ化されたクエリの実行

パラメータ化されたクエリを実行するには、クエリパラメータのデータ型と値を定義します。クエリパラメータの型と値は、パラメータが表すデータベースオブジェクトと一致する必要があります。

クエリパラメータを定義するには、Google Cloud コンソールまたは Google Cloud CLI を使用します。

コンソール

クエリパラメータを定義するには、Dataflow SQL UI を使用します。

Dataflow SQL UI に移動します。

Dataflow SQL UI に移動
クエリエディタに Dataflow SQL クエリを入力します。

たとえば、次のクエリは、指定した金額以上のタクシー乗車データを選択します。
```
SELECT *
FROM pubsub.topic.`pubsub-public-data`.`taxirides-realtime`
WHERE
 ride_status = "dropoff"
 AND meter_reading >= @price_min
```
[Cloud Dataflow ジョブを作成] をクリックして、ジョブオプションのパネルを開きます。
パネルの [SQL クエリパラメータ] セクションで、[名前]、[型]、[値] の各フィールドに入力します。この例のパラメータの名前は price_min で、型は FLOAT64 です。

位置パラメータの場合、[名前] フィールドを省略します。
（省略可）クエリに別のパラメータを追加する場合は、[パラメータを追加] をクリックします。

Google Cloud コンソールでは ARRAY または STRUCT パラメータはサポートされていません。ARRAY または STRUCT パラメータを指定するには、Google Cloud CLI を使用します。

gcloud

クエリパラメータを定義するには、gcloud dataflow sql query コマンドと --parameter フラグを使用します。

クエリパラメータごとに --parameter フラグを指定できます。各 --parameter フラグを name:type:value に設定します。

次のコマンドでは、タクシー乗車データの Pub/Sub ストリームにパラメータ化されたクエリを実行します。

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'

位置パラメータの場合は、--parameter フラグを :type:value に設定します。

パラメータ化されたクエリで配列を使用する

クエリパラメータで配列型を使用するには、型を ARRAY<data-type> に設定します。["pickup", "enroute", "dropoff"] のように、要素をカンマで区切って角かっこで囲み、値を指定します。

コンソール

Dataflow SQL UI では、Dataflow SQL クエリに ARRAY パラメータを指定できません。

gcloud

配列パラメータを定義するには、gcloud dataflow sql query コマンドの --parameter フラグを name:ARRAY<data-type>:value に設定します。

次のコマンドでは、タクシー乗車データの Pub/Sub ストリームに名前付き配列パラメータを含むクエリを実行します。

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)'

詳細については、Dataflow SQL リファレンスで ARRAY 型の説明をご覧ください。

パラメータ化されたクエリでタイムスタンプを使用する

クエリパラメータでタイムスタンプを使用するには、型を STRING に設定し、正規のタイムスタンプ形式で値を作成します。

コンソール

タイムスタンプパラメータを定義するには、Dataflow SQL UI を使用します。

Dataflow SQL UI に移動します。

Dataflow SQL UI に移動
クエリエディタに Dataflow SQL クエリを入力します。

たとえば、次のクエリでは、指定した日付以降に発生したタクシー乗車データを選択します。
```
SELECT *
FROM pubsub.topic.`pubsub-public-data`.`taxirides-realtime`
WHERE
  event_timestamp >= TIMESTAMP (@date_min)
```
[Cloud Dataflow ジョブを作成] をクリックして、ジョブオプションのパネルを開きます。
[SQL クエリパラメータ] セクションで、[パラメータを追加] をクリックします。
[型] フィールドで STRING を選択します。
[値] フィールドにタイムスタンプを入力します。

gcloud

タイムスタンプパラメータを定義するには、gcloud dataflow sql query コマンドの --parameter フラグを name:STRING:value に設定します。

次のコマンドでは、タクシー乗車データの Pub/Sub ストリームに名前付きタイムスタンプパラメータを含むクエリを実行します。

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)'

詳細については、Dataflow SQL リファレンスで TIMESTAMP 型の説明をご覧ください。

パラメータ化されたクエリで構造体を使用する

クエリパラメータで構造体を使用するには、型を STRUCT<field-name data-type, ...> に設定します。Key-Value ペアのカンマ区切りリストとして値を作成します。

たとえば、STRUCT<date_min TIMESTAMP, status STRING> では、date_min フィールドの型が TIMESTAMP、status フィールドの型が STRING の構造体を定義しています。

コンソール

Dataflow SQL UI では、Dataflow SQL クエリに STRUCT パラメータを指定できません。

gcloud

配列パラメータを定義するには、gcloud dataflow sql query コマンドの --parameter フラグを name:STRUCT<field-name data-type, ...>:value に設定します。

次のコマンドでは、タクシー乗車データの Pub/Sub ストリームに名前付き構造体パラメータを含むクエリを実行します。

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'

詳細については、Dataflow SQL リファレンスで STRUCT 型の説明をご覧ください。