パラメータ化されたクエリを実行する

このドキュメントでは、BigQuery でパラメータ化されたクエリを実行する方法について説明します。

パラメータ化されたクエリを実行する

BigQuery はクエリ パラメータをサポートしています。ユーザーの入力からクエリが作成される場合に、SQL インジェクションを防ぐことができます。この機能は、標準 SQL 構文でのみ利用可能です。クエリ パラメータは、任意の式の代わりに使用できます。パラメータは、識別子、列名、テーブル名、またはクエリの他の部分の代わりとして使用することはできません。

名前付きパラメータを指定するには、@param_name のように、文字 @ の後に識別子を続けます。たとえば、次のクエリは、特定の Shakespeare コーパスで、指定した値以上の単語数を持つ語句をすべて検索します。

#standardSQL
SELECT
  word,
  word_count
FROM
  `bigquery-public-data.samples.shakespeare`
WHERE
  corpus = @corpus
  AND word_count >= @min_word_count
ORDER BY
  word_count DESC;

また、プレースホルダ値 ? を使用して、位置パラメータを指定します。1 つのクエリで位置パラメータまたは名前付きパラメータを使用できますが、両方を使用することはできません。

ウェブ UI

パラメータ化されたクエリは、BigQuery ウェブ UI ではサポートされていません。

コマンドライン

--parameter を使用して、"name:type:value" の形式でパラメータの値を指定します。空の名前を使用すると、位置パラメータが生成されます。型を省略すると、STRING が使用されます。

--parameter フラグは、--use_legacy_sql=False フラグと併用して標準 SQL 構文を指定する必要があります。ロケーションを指定するには、--location フラグを使用します。--location フラグはオプションです。

bq --location=US 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

名前付きパラメータを使用するには、jobs#configuration.query.parameterMode を NAMED に指定します。

jobs#configuration.query.queryParameters[] にパラメータのリストを設定します。各パラメータの名前には、クエリで使用する @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 で試してみましょう。

位置パラメータを使用するには、jobs#configuration.query.parameterMode を POSITIONAL に設定します。

Go

このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Go の設定手順に従ってください。詳細については、BigQuery Go API のリファレンス ドキュメントをご覧ください。

名前付きパラメータを使用するには: 
	// To run this sample, you will need to create (or reuse) a context and
	// an instance of the bigquery client.  For example:
	// import "cloud.google.com/go/bigquery"
	// ctx := context.Background()
	// client, err := bigquery.NewClient(ctx, "your-project-id")
	q := client.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;`)
	q.Parameters = []bigquery.QueryParameter{
		{
			Name:  "corpus",
			Value: "romeoandjuliet",
		},
		{
			Name:  "min_word_count",
			Value: 250,
		},
	}
	job, err := q.Run(ctx)
	if err != nil {
		return err
	}
	status, err := job.Wait(ctx)
	if err != nil {
		return err
	}
	if err := status.Err(); err != nil {
		return err
	}
	it, err := job.Read(ctx)
	for {
		var row []bigquery.Value
		err := it.Next(&row)
		if err == iterator.Done {
			break
		}
		if err != nil {
			return err
		}
		fmt.Println(row)
	}
位置パラメータを使用するには: 
	// To run this sample, you will need to create (or reuse) a context and
	// an instance of the bigquery client.  For example:
	// import "cloud.google.com/go/bigquery"
	// ctx := context.Background()
	// client, err := bigquery.NewClient(ctx, "your-project-id")
	q := client.Query(
		`SELECT word, word_count
        FROM ` + "`bigquery-public-data.samples.shakespeare`" + `
        WHERE corpus = ?
        AND word_count >= ?
        ORDER BY word_count DESC;`)
	q.Parameters = []bigquery.QueryParameter{
		{
			Value: "romeoandjuliet",
		},
		{
			Value: 250,
		},
	}
	job, err := q.Run(ctx)
	if err != nil {
		return err
	}
	status, err := job.Wait(ctx)
	if err != nil {
		return err
	}
	if err := status.Err(); err != nil {
		return err
	}
	it, err := job.Read(ctx)
	for {
		var row []bigquery.Value
		err := it.Next(&row)
		if err == iterator.Done {
			break
		}
		if err != nil {
			return err
		}
		fmt.Println(row)
	}

Java

このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Java の設定手順に従ってください。詳細については、BigQuery Java API のリファレンス ドキュメントをご覧ください。 

// BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
String corpus = "romeoandjuliet";
long minWordCount = 250;
String query =
    "SELECT word, word_count\n"
        + "FROM `bigquery-public-data.samples.shakespeare`\n"
        + "WHERE corpus = @corpus\n"
        + "AND word_count >= @min_word_count\n"
        + "ORDER BY word_count DESC";
// Note: Standard SQL is required to use query parameters.
QueryJobConfiguration queryConfig =
    QueryJobConfiguration.newBuilder(query)
        .addNamedParameter("corpus", QueryParameterValue.string(corpus))
        .addNamedParameter("min_word_count", QueryParameterValue.int64(minWordCount))
        .build();

// Print the results.
for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
  for (FieldValue val : row) {
    System.out.printf("%s,", val.toString());
  }
  System.out.printf("\n");
}

Python

このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Python の設定手順に従ってください。詳細については、BigQuery Python API のリファレンス ドキュメントをご覧ください。

名前付きパラメータを使用するには: 
# from google.cloud import bigquery
# client = bigquery.Client()

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;
"""
query_params = [
    bigquery.ScalarQueryParameter('corpus', 'STRING', 'romeoandjuliet'),
    bigquery.ScalarQueryParameter('min_word_count', 'INT64', 250)
]
job_config = bigquery.QueryJobConfig()
job_config.query_parameters = query_params
query_job = client.query(
    query,
    # Location must match that of the dataset(s) referenced in the query.
    location='US',
    job_config=job_config)  # API request - starts the query

# Print the results
for row in query_job:
    print('{}: \t{}'.format(row.word, row.word_count))

assert query_job.state == 'DONE'
位置パラメータを使用するには: 
# from google.cloud import bigquery
# client = bigquery.Client()

query = """
    SELECT word, word_count
    FROM `bigquery-public-data.samples.shakespeare`
    WHERE corpus = ?
    AND word_count >= ?
    ORDER BY word_count DESC;
"""
# Set the name to None to use positional parameters.
# Note that you cannot mix named and positional parameters.
query_params = [
    bigquery.ScalarQueryParameter(None, 'STRING', 'romeoandjuliet'),
    bigquery.ScalarQueryParameter(None, 'INT64', 250)
]
job_config = bigquery.QueryJobConfig()
job_config.query_parameters = query_params
query_job = client.query(
    query,
    # Location must match that of the dataset(s) referenced in the query.
    location='US',
    job_config=job_config)  # API request - starts the query

# Print the results
for row in query_job:
    print('{}: \t{}'.format(row.word, row.word_count))

assert query_job.state == 'DONE'

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

クエリ パラメータで配列型を使用するには、型を ARRAY<T> に設定します。T は、配列内の要素の型です。[1, 2, 3] のように、要素をカンマで区切って角括弧で囲み、値を指定します。

配列型の詳細については、データ型のリファレンスをご覧ください。

ウェブ UI

パラメータ化されたクエリは、BigQuery ウェブ UI ではサポートされていません。

コマンドライン

このクエリは、W で始まる米国の州で生まれた赤ちゃんの中で最も多い名前を選択します。この例では、一般公開データセットをクエリするため、--location=US フラグを使用しています。BigQuery の一般公開データセットは、US マルチリージョン ロケーションに保存されています。一般公開データセットは米国内に格納されているため、一般公開データのクエリ結果を別のリージョンにあるテーブルに書き込むことも、一般公開データセット内のテーブルを別のリージョンのテーブルと結合することもできません。

bq --location=US 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

配列値を持つパラメータを使用するには、jobs#configuration.query.queryParameters[].parameterType.type を ARRAY に設定します。

配列値がスカラーの場合は、jobs#configuration.query.queryParameters[].parameterType.arrayType.type を値の型（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"
}

Google API Explorer で試してみましょう。

Go

このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Go の設定手順に従ってください。詳細については、BigQuery Go API のリファレンス ドキュメントをご覧ください。 

	// To run this sample, you will need to create (or reuse) a context and
	// an instance of the bigquery client.  For example:
	// import "cloud.google.com/go/bigquery"
	// ctx := context.Background()
	// client, err := bigquery.NewClient(ctx, "your-project-id")
	q := client.Query(
		`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;`)
	q.Parameters = []bigquery.QueryParameter{
		{
			Name:  "gender",
			Value: "M",
		},
		{
			Name:  "states",
			Value: []string{"WA", "WI", "WV", "WY"},
		},
	}
	job, err := q.Run(ctx)
	if err != nil {
		return err
	}
	status, err := job.Wait(ctx)
	if err != nil {
		return err
	}
	if err := status.Err(); err != nil {
		return err
	}
	it, err := job.Read(ctx)
	for {
		var row []bigquery.Value
		err := it.Next(&row)
		if err == iterator.Done {
			break
		}
		if err != nil {
			return err
		}
		fmt.Println(row)
	}

Java

このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Java の設定手順に従ってください。詳細については、BigQuery Java API のリファレンス ドキュメントをご覧ください。 

// BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
String gender = "M";
String[] states = {"WA", "WI", "WV", "WY"};
String query =
    "SELECT name, sum(number) as count\n"
        + "FROM `bigquery-public-data.usa_names.usa_1910_2013`\n"
        + "WHERE gender = @gender\n"
        + "AND state IN UNNEST(@states)\n"
        + "GROUP BY name\n"
        + "ORDER BY count DESC\n"
        + "LIMIT 10;";
// Note: Standard SQL is required to use query parameters.
QueryJobConfiguration queryConfig =
    QueryJobConfiguration.newBuilder(query)
        .addNamedParameter("gender", QueryParameterValue.string(gender))
        .addNamedParameter("states", QueryParameterValue.array(states, String.class))
        .build();

// Print the results.
for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
  for (FieldValue val : row) {
    System.out.printf("%s,", val.toString());
  }
  System.out.printf("\n");
}

Python

このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Python の設定手順に従ってください。詳細については、BigQuery Python API のリファレンス ドキュメントをご覧ください。 

# from google.cloud import bigquery
# client = bigquery.Client()

query = """
    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;
"""
query_params = [
    bigquery.ScalarQueryParameter('gender', 'STRING', 'M'),
    bigquery.ArrayQueryParameter(
        'states', 'STRING', ['WA', 'WI', 'WV', 'WY'])
]
job_config = bigquery.QueryJobConfig()
job_config.query_parameters = query_params
query_job = client.query(
    query,
    # Location must match that of the dataset(s) referenced in the query.
    location='US',
    job_config=job_config)  # API request - starts the query

# Print the results
for row in query_job:
    print('{}: \t{}'.format(row.name, row.count))

assert query_job.state == 'DONE'

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

クエリ パラメータでタイムスタンプを使用するには、型を TIMESTAMP に設定します。値は、YYYY-MM-DD HH:MM:SS.DDDDDD time_zone の形式にします。

タイムスタンプ型の詳細については、データ型のリファレンスをご覧ください。

ウェブ UI

パラメータ化されたクエリは、BigQuery ウェブ UI ではサポートされていません。

コマンドライン

次のクエリは、タイムスタンプ パラメータ値に時間を追加します。

bq --location=US query --use_legacy_sql=False \
    --parameter='ts_value:TIMESTAMP:2016-12-07 08:00:00' \
    'SELECT TIMESTAMP_ADD(@ts_value, INTERVAL 1 HOUR);'

API

タイムスタンプ パラメータを使用するには、jobs#configuration.query.queryParameters[].parameterType.type を 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"
}

Google API Explorer で試してみましょう。

Go

このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Go の設定手順に従ってください。詳細については、BigQuery Go API のリファレンス ドキュメントをご覧ください。 

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
q := client.Query(
	`SELECT TIMESTAMP_ADD(@ts_value, INTERVAL 1 HOUR);`)
q.Parameters = []bigquery.QueryParameter{
	{
		Name:  "ts_value",
		Value: time.Date(2016, 12, 7, 8, 0, 0, 0, time.UTC),
	},
}
job, err := q.Run(ctx)
if err != nil {
	return err
}
status, err := job.Wait(ctx)
if err != nil {
	return err
}
if err := status.Err(); err != nil {
	return err
}
it, err := job.Read(ctx)
for {
	var row []bigquery.Value
	err := it.Next(&row)
	if err == iterator.Done {
		break
	}
	if err != nil {
		return err
	}
	fmt.Println(row)
}

Java

このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Java の設定手順に従ってください。詳細については、BigQuery Java API のリファレンス ドキュメントをご覧ください。 

// BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
DateTime timestamp = new DateTime(2016, 12, 7, 8, 0, 0, DateTimeZone.UTC);
String query = "SELECT TIMESTAMP_ADD(@ts_value, INTERVAL 1 HOUR);";
// Note: Standard SQL is required to use query parameters.
QueryJobConfiguration queryConfig =
    QueryJobConfiguration.newBuilder(query)
        .addNamedParameter(
            "ts_value",
            QueryParameterValue.timestamp(
                // Timestamp takes microseconds since 1970-01-01T00:00:00 UTC
                timestamp.getMillis() * 1000))
        .build();

// Print the results.
DateTimeFormatter formatter = ISODateTimeFormat.dateTimeNoMillis().withZoneUTC();
for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
  System.out.printf(
      "%s\n",
      formatter.print(
          new DateTime(
              // Timestamp values are returned in microseconds since 1970-01-01T00:00:00 UTC,
              // but org.joda.time.DateTime constructor accepts times in milliseconds.
              row.get(0).getTimestampValue() / 1000, DateTimeZone.UTC)));
  System.out.printf("\n");
}

Python

このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Python の設定手順に従ってください。詳細については、BigQuery Python API のリファレンス ドキュメントをご覧ください。 

# from google.cloud import bigquery
# client = bigquery.Client()

import datetime
import pytz

query = 'SELECT TIMESTAMP_ADD(@ts_value, INTERVAL 1 HOUR);'
query_params = [
    bigquery.ScalarQueryParameter(
        'ts_value',
        'TIMESTAMP',
        datetime.datetime(2016, 12, 7, 8, 0, tzinfo=pytz.UTC))
]
job_config = bigquery.QueryJobConfig()
job_config.query_parameters = query_params
query_job = client.query(
    query,
    # Location must match that of the dataset(s) referenced in the query.
    location='US',
    job_config=job_config)  # API request - starts the query

# Print the results
for row in query_job:
    print(row)

assert query_job.state == 'DONE'

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

クエリ パラメータで構造体を使用するには、型を STRUCT<T> に設定します。T で構造体内のフィールドと型を定義します。フィールド定義はカンマで区切り、field_name TF という形式にします。TF はフィールドの型です。たとえば、STRUCT<x INT64, y STRING> では、最初のフィールドの型が INT64 で名前が x で、2 番目のフィールドの型が STRING で名前が y の構造体を定義しています。

構造体の型の詳細については、データ型のリファレンスをご覧ください。

ウェブ UI

パラメータ化されたクエリは、BigQuery ウェブ UI ではサポートされていません。

コマンドライン

次の簡単なクエリでは、構造化型を使用してパラメータ値を返しています。

bq --location=US query --use_legacy_sql=False \
    --parameter='struct_value:STRUCT<x INT64, y STRING>:{"x": 1, "y": "foo"}' \
    'SELECT @struct_value AS s;'

API

構造体パラメータを使用するには、jobs#configuration.query.queryParameters.parameterType.type を STRUCT に設定します。

構造体の各フィールドのオブジェクトを jobs#configuration.query.queryParameters.parameterType.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"
}

Google API Explorer で試してみましょう。

Go

このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Go の設定手順に従ってください。詳細については、BigQuery Go API のリファレンス ドキュメントをご覧ください。 

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
type MyStruct struct {
	X int64
	Y string
}
q := client.Query(
	`SELECT @struct_value as s;`)
q.Parameters = []bigquery.QueryParameter{
	{
		Name:  "struct_value",
		Value: MyStruct{X: 1, Y: "foo"},
	},
}
job, err := q.Run(ctx)
if err != nil {
	return err
}
status, err := job.Wait(ctx)
if err != nil {
	return err
}
if err := status.Err(); err != nil {
	return err
}
it, err := job.Read(ctx)
for {
	var row []bigquery.Value
	err := it.Next(&row)
	if err == iterator.Done {
		break
	}
	if err != nil {
		return err
	}
	fmt.Println(row)
}

Python

このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Python の設定手順に従ってください。詳細については、BigQuery Python API のリファレンス ドキュメントをご覧ください。

Python

このサンプルを試す前に、BigQuery クイックスタート: クライアント ライブラリの使用の Python の設定手順に従ってください。詳細については、BigQuery Python API のリファレンス ドキュメントをご覧ください。 

# from google.cloud import bigquery
# client = bigquery.Client()

query = 'SELECT @struct_value AS s;'
query_params = [
    bigquery.StructQueryParameter(
        'struct_value',
        bigquery.ScalarQueryParameter('x', 'INT64', 1),
        bigquery.ScalarQueryParameter('y', 'STRING', 'foo')
    )
]
job_config = bigquery.QueryJobConfig()
job_config.query_parameters = query_params
query_job = client.query(
    query,
    # Location must match that of the dataset(s) referenced in the query.
    location='US',
    job_config=job_config)  # API request - starts the query

# Print the results
for row in query_job:
    print(row.s)

assert query_job.state == 'DONE'

このページは役立ちましたか？評価をお願いいたします。

フィードバックを送信...

このページ
ドキュメントのフィードバック
BigQuery
サービスのフィードバック
ご不明な点がありましたら、Google のサポートページをご覧ください。