Cómo ejecutar consultas con parámetros

Este documento describe cómo ejecutar consultas con parámetros en BigQuery.

BigQuery admite parámetros de consulta para ayudar a evitar la inyección de SQL cuando se compilan las consultas mediante la entrada del usuario. Esta característica solo está disponible con la sintaxis de SQL estándar. Los parámetros de consulta pueden usarse como sustitutos de expresiones arbitrarias. Los parámetros no se pueden usar como sustitutos de identificadores, nombres de columnas, nombres de tablas ni otras partes de la consulta.

Para especificar un parámetro de nombre, usa el carácter @ seguido de un identificador como @param_name. Por ejemplo, esta consulta encuentra todas las palabras en un corpus específico de Shakespeare, con conteos que son al menos el valor especificado.

#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;

Como alternativa, puedes usar el valor de marcador de posición ? para especificar un parámetro posicional. Ten en cuenta que una consulta puede usar parámetros posicionales o con nombre, pero no ambos.

IU web

Las consultas con parámetros no son compatibles con la IU web de BigQuery.

Línea de comandos

Usa --parameter para proporcionar valores para los parámetros en la forma "name:type:value". Un nombre vacío produce un parámetro posicional. Se puede omitir el tipo para asumir STRING.

La marca --parameter debe usarse en conjunto con la marca --use_legacy_sql=False para especificar la sintaxis de SQL estándar. Especifica tu ubicación con la marca --location. La marca --location es opcional.

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

Para usar parámetros con nombre, configura jobs#configuration.query.parameterMode en NAMED.

Propaga jobs#configuration.query.queryParameters[] con la lista de los parámetros. Configura el nombre de cada parámetro con el @param_name usado en la consulta.

Habilita la sintaxis de SQL estándar mediante la configuración de useLegacySql en false.

{
  "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"
}

Pruébalo en el Explorador de API de Google.

Para usar parámetros posicionales, configura jobs#configuration.query.parameterMode en POSITIONAL.

Go

Antes de probar esta muestra, sigue las instrucciones de configuración para Go que se encuentran en la Guía de inicio rápido de BigQuery con bibliotecas cliente. Para obtener más información, consulta la documentación de referencia de la API de BigQuery para Go.

Para usar parámetros con nombre:

Ver en GitHub

	// 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)
	}

Para usar parámetros posicionales:

Ver en GitHub

	// 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

Antes de probar esta muestra, sigue las instrucciones de configuración para Java que se encuentran en la Guía de inicio rápido de BigQuery con bibliotecas cliente. Para obtener más información, consulta la documentación de referencia de la API de BigQuery para Java.

Ver en GitHub Comentarios

// 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

Antes de probar esta muestra, sigue las instrucciones de configuración para Python que se encuentran en la Guía de inicio rápido de BigQuery con bibliotecas cliente. Para obtener más información, consulta la documentación de referencia de la API de BigQuery para Python.

Para usar parámetros con nombre:

Ver en GitHub

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

Para usar parámetros posicionales:

Ver en GitHub

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

Cómo usar arreglos en consultas con parámetros

Para usar un tipo de arreglo en un parámetro de consulta, configura el tipo en ARRAY<T> donde T es el tipo de elementos en el arreglo. Construye el valor como una lista separada por comas de elementos encerrados entre corchetes, como por ejemplo [1, 2, 3].

Consulta la referencia de los tipos de datos para obtener más información sobre el tipo de arreglo.

IU web

Las consultas con parámetros no son compatibles con la IU web de BigQuery.

Línea de comandos

Esta consulta selecciona los nombres más populares para los bebés varones nacidos en los estados de EE.UU. que comienzan con la letra W. Este ejemplo utiliza la marca --location=US, ya que la consulta se realiza a un conjunto de datos públicos. Los conjuntos de datos públicos de BigQuery se almacenan en la ubicación multirregión de EE.UU. Debido a que los conjuntos de datos públicos se almacenan en EE.UU., no puedes escribir resultados de consultas a datos públicos en una tabla de otra región ni unir tablas en conjuntos de datos públicos con tablas en otra región.

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

Preocúpate de encerrar la declaración del tipo de arreglo entre comillas simples para que la respuesta del comando no se redirija accidentalmente a un campo con el carácter >.

API

Para usar un parámetro con valor de arreglo, establece jobs#configuration.query.queryParameters[].parameterType.type en ARRAY.

Si los valores del arreglo son escalares, establece jobs#configuration.query.queryParameters[].parameterType.arrayType.type en el tipo de los valores, como por ejemplo, STRING. Si los valores del arreglo están estructurados, configúralo en STRUCT y agrega las definiciones de campo necesarias para structTypes.

Por ejemplo, esta consulta selecciona los nombres más populares para los bebés varones nacidos en los estados de EE.UU. que comienzan con la letra 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"
}

Pruébalo en el Explorador de API de Google.

Go

Ver en GitHub Comentarios

	// 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

Ver en GitHub Comentarios

// 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

Ver en GitHub Comentarios

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

Cómo usar marcas de tiempo en consultas con parámetros

Para usar una marca de tiempo en un parámetro de consulta, configura el tipo en TIMESTAMP. El valor debe estar en el formato YYYY-MM-DD HH:MM:SS.DDDDDD time_zone.

Consulta la referencia de tipos de datos para obtener más información sobre el tipo de marca de tiempo.

IU web

Las consultas con parámetros no son compatibles con la IU web de BigQuery.

Línea de comandos

Esta consulta agrega una hora al valor del parámetro de marco de tiempo.

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

Para usar un parámetro de marca de tiempo, establece jobs#configuration.query.queryParameters[].parameterType.type en TIMESTAMP.

Esta consulta agrega una hora al valor del parámetro de marco de tiempo.

{
  "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"
}

Pruébalo en el Explorador de API de Google.

Go

Ver en GitHub Comentarios

// 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

Ver en GitHub Comentarios

// 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

Ver en GitHub Comentarios

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

Cómo usar structs en consultas con parámetros

Para usar una estructura en un parámetro de consulta, configura el tipo en STRUCT<T> donde T define los campos y tipos dentro de la estructura. Las definiciones de los campos están separadas por comas y tienen el formato field_name TF, donde TF es el tipo de campo. Por ejemplo, STRUCT<x INT64, y STRING> define una estructura con un campo llamado x de tipo INT64, y un segundo campo llamado y de tipo STRING.

Consulta la referencia de los tipos de datos para obtener más información sobre el tipo de struct.

IU web

Las consultas con parámetros no son compatibles con la IU web de BigQuery.

Línea de comandos

Esta consulta trivial demuestra el uso de tipos estructurados devolviendo el valor del parámetro.

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

Para usar un parámetro de struct, establece jobs#configuration.query.queryParameters.parameterType.type en STRUCT.

Agrega un objeto para cada campo de la estructura en jobs#configuration.query.queryParameters.parameterType.structTypes. Si los valores de la estructura son escalares, establece el tipo en el tipo de los valores, como por ejemplo, STRING. Si los valores de la estructura son arreglos, establécelos en ARRAY y configura el campo arrayType anidado en el tipo correspondiente. Si los valores de la estructura son estructuras, establece el type en STRUCT y agrega los structTypes necesarios.

Esta consulta trivial demuestra el uso de tipos estructurados devolviendo el valor del parámetro.

{
  "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"
}

Pruébalo en el Explorador de API de Google.

Go

Ver en GitHub Comentarios

// 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

Ver en GitHub Comentarios

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

Cómo ejecutar consultas con parámetros