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

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

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

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

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

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

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

クエリ パラメータを定義するには、Cloud Console または gcloudコマンドライン ツールを使用します。

Console

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

  1. Dataflow SQL UI に移動します。

    Dataflow SQL UI に移動

  2. クエリエディタに Dataflow SQL クエリを入力します。

    たとえば、次のクエリは、指定した金額以上のタクシー乗車データを選択します。

    SELECT *
    FROM pubsub.topic.`pubsub-public-data`.`taxirides-realtime`
    WHERE
     ride_status = "dropoff"
     AND meter_reading >= @price_min
    
  3. [Cloud Dataflow ジョブを作成] をクリックして、ジョブ オプションのパネルを開きます。

  4. パネルの [SQL クエリ パラメータ] セクションで、[名前]、[]、[] の各フィールドに入力します。この例のパラメータの名前は price_min で、型は FLOAT64 です。

    位置パラメータの場合、[名前] フィールドを省略します。

  5. (省略可)クエリに別のパラメータを追加する場合は、[パラメータを追加] をクリックします。

Cloud Console では ARRAY または STRUCT パラメータは使用できません。ARRAY または STRUCT パラメータを指定する場合は、gcloud コマンドライン ツールを使用します。

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"] のように、要素をカンマで区切って角かっこで囲み、値を指定します。

Console

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 に設定し、正規のタイムスタンプ形式で値を作成します。

Console

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

  1. Dataflow SQL UI に移動します。

    Dataflow SQL UI に移動

  2. クエリエディタに Dataflow SQL クエリを入力します。

    たとえば、次のクエリでは、指定した日付以降に発生したタクシー乗車データを選択します。

    SELECT *
    FROM pubsub.topic.`pubsub-public-data`.`taxirides-realtime`
    WHERE
      event_timestamp >= TIMESTAMP (@date_min)
    
  3. [Cloud Dataflow ジョブを作成] をクリックして、ジョブ オプションのパネルを開きます。

  4. [SQL クエリ パラメータ] セクションで、[パラメータを追加] をクリックします。

  5. [] フィールドで STRING を選択します。

  6. [] フィールドにタイムスタンプを入力します。

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 フィールドの型が TIMESTAMPstatus フィールドの型が STRING の構造体を定義しています。

Console

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の説明をご覧ください。