パラメータ化されたクエリの実行
BigQuery はクエリ パラメータをサポートしています。ユーザーの入力からクエリが作成される場合に、SQL インジェクションを防ぐことができます。この機能は、Google 標準 SQL 構文でのみ利用可能です。クエリ パラメータは、任意の式の代わりに使用できます。パラメータは、識別子、列名、テーブル名、またはクエリの他の部分の代わりとして使用することはできません。
名前付きパラメータを指定するには、@param_name
のように、@
記号の後に識別子を続けます。あるいは、プレースホルダ値 ?
を使用して、位置パラメータを指定します。1 つのクエリで位置パラメータまたは名前付きパラメータを使用できますが、両方を使用することはできません。
次の方法で、BigQuery でパラメータ化されたクエリを実行できます。
bq
コマンドライン ツールのbq query
コマンド- API
- クライアント ライブラリ
次の例は、パラメータ値をパラメータ化されたクエリに渡す方法を示しています。
コンソール
パラメータ化されたクエリは、Google Cloud コンソールではサポートされていません。
bq
--parameter
を使用して、name:type:value
の形式でパラメータの値を指定します。空の名前を使用すると、位置パラメータが生成されます。型を省略すると、STRING
が使用されます。
--parameter
フラグは、Google 標準 SQL 構文を指定するフラグ --use_legacy_sql=false
と併用する必要があります。
(省略可)ロケーションを指定するには、--location
フラグを使用します。
bq query \ --use_legacy_sql=false \ --parameter=corpus::romeoandjuliet \ --parameter=min_word_count:INT64:250 \ 'SELECT word, word_count FROM `bigquery-public-data.samples.shakespeare` WHERE corpus = @corpus AND word_count >= @min_word_count ORDER BY word_count DESC;'
API
名前付きパラメータを使用するには、query
ジョブ構成で parameterMode
を NAMED
に設定します。
query
ジョブ構成で、queryParameters
にパラメータのリストを設定します。各パラメータの name
には、クエリで使用する @param_name
を設定します。
useLegacySql
を false
に設定して標準 SQL 構文を有効にします。
{
"query": "SELECT word, word_count FROM `bigquery-public-data.samples.shakespeare` WHERE corpus = @corpus AND word_count >= @min_word_count ORDER BY word_count DESC;",
"queryParameters": [
{
"parameterType": {
"type": "STRING"
},
"parameterValue": {
"value": "romeoandjuliet"
},
"name": "corpus"
},
{
"parameterType": {
"type": "INT64"
},
"parameterValue": {
"value": "250"
},
"name": "min_word_count"
}
],
"useLegacySql": false,
"parameterMode": "NAMED"
}
Google API Explorer で試してみましょう。
位置パラメータを使用するには、query
ジョブ構成で parameterMode
を POSITIONAL
に設定します。
C#
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの C# の手順に沿って設定を行ってください。詳細については、BigQuery C# API のリファレンス ドキュメントをご覧ください。
名前付きパラメータを使用する場合:このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの C# の手順に沿って設定を行ってください。詳細については、BigQuery C# API のリファレンス ドキュメントをご覧ください。
位置パラメータを使用する場合:Go
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの Go の手順に沿って設定を行ってください。詳細については、BigQuery Go API のリファレンス ドキュメントをご覧ください。
名前付きパラメータを使用する場合:Java
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの Java の手順に沿って設定を行ってください。詳細については、BigQuery Java API のリファレンス ドキュメントをご覧ください。
名前付きパラメータを使用する場合:Node.js
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの Node.js の手順に沿って設定を行ってください。詳細については、BigQuery Node.js API のリファレンス ドキュメントをご覧ください。
名前付きパラメータを使用する場合:Python
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの Python の手順に沿って設定を行ってください。詳細については、BigQuery Python API のリファレンス ドキュメントをご覧ください。
名前付きパラメータを使用する場合:パラメータ化されたクエリで配列を使用する
クエリ パラメータで配列型を使用するには、型を ARRAY<T>
に設定します。ここで T
は、配列内の要素の型です。[1, 2,
3]
のように、要素をカンマで区切って角かっこで囲み、値を指定します。
配列型の詳細については、データ型のリファレンスをご覧ください。
コンソール
パラメータ化されたクエリは、Google Cloud コンソールではサポートされていません。
bq
次のクエリは、米国で生まれた男の赤ちゃんに付けられた W で始まる名前の中で、特に多い名前を選択します。
bq query \ --use_legacy_sql=false \ --parameter='gender::M' \ --parameter='states:ARRAY<STRING>:["WA", "WI", "WV", "WY"]' \ 'SELECT name, SUM(number) AS count FROM `bigquery-public-data.usa_names.usa_1910_2013` WHERE gender = @gender AND state IN UNNEST(@states) GROUP BY name ORDER BY count DESC LIMIT 10;'
>
文字によってコマンドの出力がファイルにリダイレクトされないように、配列型の宣言を必ず単一引用符で囲んでください。
API
配列値を持つパラメータを使用するには、query
ジョブ構成で parameterType
を ARRAY
に設定します。
配列値がスカラーの場合は、parameterType
を値の型(STRING
など)に設定します。配列値が構造体の場合は STRUCT
に設定し、必要なフィールド定義を structTypes
に追加します。
たとえば、次のクエリは、米国で生まれた男の赤ちゃんに付けられた W で始まる名前の中で、最も多い名前を選択します。
{
"query": "SELECT name, sum(number) as count\nFROM `bigquery-public-data.usa_names.usa_1910_2013`\nWHERE gender = @gender\nAND state IN UNNEST(@states)\nGROUP BY name\nORDER BY count DESC\nLIMIT 10;",
"queryParameters": [
{
"parameterType": {
"type": "STRING"
},
"parameterValue": {
"value": "M"
},
"name": "gender"
},
{
"parameterType": {
"type": "ARRAY",
"arrayType": {
"type": "STRING"
}
},
"parameterValue": {
"arrayValues": [
{
"value": "WA"
},
{
"value": "WI"
},
{
"value": "WV"
},
{
"value": "WY"
}
]
},
"name": "states"
}
],
"useLegacySql": false,
"parameterMode": "NAMED"
}
C#
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの C# の手順に沿って設定を行ってください。詳細については、BigQuery C# API のリファレンス ドキュメントをご覧ください。
Go
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの Go の手順に沿って設定を行ってください。詳細については、BigQuery Go API のリファレンス ドキュメントをご覧ください。
Java
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの Java の手順に沿って設定を行ってください。詳細については、BigQuery Java API のリファレンス ドキュメントをご覧ください。
Node.js
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの Node.js の手順に沿って設定を行ってください。詳細については、BigQuery Node.js API のリファレンス ドキュメントをご覧ください。
Python
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの Python の手順に沿って設定を行ってください。詳細については、BigQuery Python API のリファレンス ドキュメントをご覧ください。
パラメータ化されたクエリでタイムスタンプを使用する
クエリ パラメータでタイムスタンプを使用するため、基になる REST API は TIMESTAMP
型の値(YYYY-MM-DD HH:MM:SS.DDDDDD time_zone
の形式)を取ります。クライアント ライブラリを使用している場合は、その言語で組み込み日付オブジェクトを作成し、ライブラリによって適切な形式に変換します。詳細については、次の言語固有の例をご覧ください。
TIMESTAMP
型の詳細については、データ型のリファレンスをご覧ください。
コンソール
パラメータ化されたクエリは、Google Cloud コンソールではサポートされていません。
bq
次のクエリは、タイムスタンプ パラメータ値に時間を追加します。
bq query \ --use_legacy_sql=false \ --parameter='ts_value:TIMESTAMP:2016-12-07 08:00:00' \ 'SELECT TIMESTAMP_ADD(@ts_value, INTERVAL 1 HOUR);'
API
タイムスタンプ パラメータを使用するには、クエリジョブ構成で parameterType
を TIMESTAMP
に設定します。
次のクエリは、タイムスタンプ パラメータ値に時間を追加します。
{
"query": "SELECT TIMESTAMP_ADD(@ts_value, INTERVAL 1 HOUR);",
"queryParameters": [
{
"name": "ts_value",
"parameterType": {
"type": "TIMESTAMP"
},
"parameterValue": {
"value": "2016-12-07 08:00:00"
}
}
],
"useLegacySql": false,
"parameterMode": "NAMED"
}
C#
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの C# の手順に沿って設定を行ってください。詳細については、BigQuery C# API のリファレンス ドキュメントをご覧ください。
Go
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの Go の手順に沿って設定を行ってください。詳細については、BigQuery Go API のリファレンス ドキュメントをご覧ください。
Java
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの Java の手順に沿って設定を行ってください。詳細については、BigQuery Java API のリファレンス ドキュメントをご覧ください。
Node.js
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの Node.js の手順に沿って設定を行ってください。詳細については、BigQuery Node.js API のリファレンス ドキュメントをご覧ください。
Python
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの Python の手順に沿って設定を行ってください。詳細については、BigQuery Python API のリファレンス ドキュメントをご覧ください。
パラメータ化されたクエリで構造体を使用する
クエリ パラメータで構造体を使用するには、型を STRUCT<T>
に設定します。T
で構造体内のフィールドと型を定義します。フィールド定義はカンマで区切り、field_name TF
という形式にします。ここで TF
はフィールドの型です。たとえば、STRUCT<x INT64, y STRING>
では、最初のフィールドの名前が x
で型が INT64
、2 番目のフィールドの名前が y
で型が STRING
の構造体を定義しています。
STRUCT
型の詳細については、データ型のリファレンス をご覧ください。
コンソール
パラメータ化されたクエリは、Google Cloud コンソールではサポートされていません。
bq
次の簡単なクエリでは、構造化型を使用してパラメータ値を返しています。
bq query \ --use_legacy_sql=false \ --parameter='struct_value:STRUCT<x INT64, y STRING>:{"x": 1, "y": "foo"}' \ 'SELECT @struct_value AS s;'
API
構造体パラメータを使用するには、クエリジョブ構成で parameterType
を STRUCT
に設定します。
構造体の各フィールドのオブジェクトをジョブの queryParameters
の structTypes
に追加します。構造体の値がスカラーの場合は、type
を値の型(STRING
など)に設定します。構造体の値が配列の場合は ARRAY
に設定し、ネストされた arrayType
フィールドを該当する型に設定します。値が構造体の場合、type
を STRUCT
に設定して、必要な structTypes
を追加します。
次の簡単なクエリでは、構造化型を使用してパラメータ値を返しています。
{
"query": "SELECT @struct_value AS s;",
"queryParameters": [
{
"name": "struct_value",
"parameterType": {
"type": "STRUCT",
"structTypes": [
{
"name": "x",
"type": {
"type": "INT64"
}
},
{
"name": "y",
"type": {
"type": "STRING"
}
}
]
},
"parameterValue": {
"structValues": {
"x": {
"value": "1"
},
"y": {
"value": "foo"
}
}
}
}
],
"useLegacySql": false,
"parameterMode": "NAMED"
}
C#
BigQuery client library for .NET は、構造体パラメータをサポートしていません。
Go
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの Go の手順に沿って設定を行ってください。詳細については、BigQuery Go API のリファレンス ドキュメントをご覧ください。
Java
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの Java の手順に沿って設定を行ってください。詳細については、BigQuery Java API のリファレンス ドキュメントをご覧ください。
Node.js
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの Node.js の手順に沿って設定を行ってください。詳細については、BigQuery Node.js API のリファレンス ドキュメントをご覧ください。
Python
このサンプルを試す前に、クライアント ライブラリを使用した BigQuery クイックスタートの Python の手順に沿って設定を行ってください。詳細については、BigQuery Python API のリファレンス ドキュメントをご覧ください。