Positional parameters and provided types

Run a query with positional parameters and provided parameter types.

Code sample

Java

Before trying this sample, follow the Java setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Java API reference documentation. 

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.QueryParameterValue;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableResult;

// Sample to run query with positional types parameters.
public class QueryWithPositionalTypesParameters {

  public static void main(String[] args) {
    String[] words = {"and", "is", "the", "moon"};
    String corpus = "romeoandjuliet";
    Integer wordsCount = 250;
    String query =
        "SELECT word, word_count"
            + " FROM `bigquery-public-data.samples.shakespeare`"
            + " WHERE word IN UNNEST(?)"
            + " AND corpus = ?"
            + " AND word_count >= ?"
            + " ORDER BY word_count DESC";
    queryWithPositionalTypesParameters(query, words, corpus, wordsCount);
  }

  public static void queryWithPositionalTypesParameters(
      String query, String[] words, String corpus, Integer wordsCount) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      QueryParameterValue wordList = QueryParameterValue.array(words, StandardSQLTypeName.STRING);
      QueryParameterValue corpusParam = QueryParameterValue.of(corpus, StandardSQLTypeName.STRING);
      QueryParameterValue minWordCount =
          QueryParameterValue.of(wordsCount, StandardSQLTypeName.INT64);

      // Note: Standard SQL is required to use query parameters.
      QueryJobConfiguration queryConfig =
          QueryJobConfiguration.newBuilder(query)
              .addPositionalParameter(wordList)
              .addPositionalParameter(corpusParam)
              .addPositionalParameter(minWordCount)
              .build();

      TableResult results = bigquery.query(queryConfig);

      results
          .iterateAll()
          .forEach(row -> row.forEach(val -> System.out.printf("%s,", val.toString())));

      System.out.println("Query with positional types parameters performed successfully.");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

Node.js

Before trying this sample, follow the Node.js setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Node.js API reference documentation. 

// Import the Google Cloud client library
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function queryParamsPositionalTypes() {
  // Run a query using positional query parameters and provided parameter types.

  // The SQL query to run
  const sqlQuery = `SELECT word, word_count
  FROM \`bigquery-public-data.samples.shakespeare\`
  WHERE word IN UNNEST(?)
  AND corpus = ?
  AND word_count >= ?
  ORDER BY word_count DESC`;

  const queryOptions = {
    query: sqlQuery,
    params: [['and', 'is', 'the', 'moon'], 'romeoandjuliet', 250],
    types: [['STRING'], 'STRING', 'INT64'],
  };

  // Run the query
  const [rows] = await bigquery.query(queryOptions);

  console.log('Rows:');
  rows.forEach(row => console.log(row));
}

What's next

To search and filter code samples for other Google Cloud products, see the Google Cloud sample browser