Package com.google.mu.bigquery

Class ParameterizedQuery

java.lang.Object
com.google.mu.bigquery.ParameterizedQuery
@Immutable @RequiresBigQuery public final class ParameterizedQuery extends Object
Encapsulates a BigQuery parameterized query.

Instances of this class are created from a compile-time template. Template arguments are protected by the same set of compile-time checks that protect StringFormat.

For simple use cases, a one-liner is enough to construct a parameterized query. For example: 


 ParameterizedQuery query = ParameterizedQuery.of(
     "SELECT name FROM Students WHERE id = {id} and status = {status}",
     studentId, Status.ENROLLED);
 TableResult result = query.run();

If you need to reuse the same query for different parameters, or to get a long query "out of the way", you can define the query template as a class constant: 


 private static final Template<ParameterizedQuery> GET_STUDENT = ParameterizedQuery.template(
     "SELECT name FROM Students WHERE id = {id} and status = {status}");

 // 200 lines later
 TableResult enrolled = GET_STUDENT.with(studentId, Status.ENROLLED).run();
 TableResult graduated = GET_STUDENT.with(alumniId, Status.GRADUATED).run();
Compared to building the QueryJobConfiguration object manually, you get the following benefits:
  • Automatic type conversion. Particularly, Instant and LocalDate are formatted and converted to TIMESTAMP and DATE parameters respectively.
  • Concise API for common use cases.
  • Compile-time safety for defining the template as a class constant.

In addition to parameterizing by values, you can also parameterize by columns, table names or sub-queries. The following example allows you to use the same query on different datasets: 


 private static final Template<ParameterizedQuery> GET_TABLES = ParameterizedQuery.template(
     "SELECT table_name FROM `{dataset}.INFORMATION_SCHEMA.TABLES`");

 TableResult marketingTables = GET_TABLES.with(ParameterizedQuery.of("marketing")).run();
 TableResult humanResourceTables = GET_TABLES.with(ParameterizedQuery.of("human-resource")).run();
Non-value string parameters must be wrapped inside ParameterizedQuery to ensure safety.
Since:
7.1

  • Field Details

  • Method Details

    • of

      @TemplateFormatMethod public static ParameterizedQuery of(@CompileTimeConstant @TemplateString String query)
      Returns a query using a compile-time constant query with no parameters.
      Since:
      8.2

    • of

      @TemplateFormatMethod public static ParameterizedQuery of(@CompileTimeConstant @TemplateString String query, Object... args)
      Convenience method when you need to create the ParameterizedQuery inline, with both the query template and the arguments.

      For example: 

      
 TableResult result = ParameterizedQuery.of("select * from JOBS where id = {id}", jobId).run();

    • optionally

      @TemplateFormatMethod public static ParameterizedQuery optionally(@CompileTimeConstant @TemplateString String query, Optional<?> arg)
      An optional query that's only rendered if arg is present; otherwise returns EMPTY. It's for use cases where a subquery is only added when present, for example the following query will add the WHERE clause if the filter is present: 
      
 SafeQuery query = ParameterizedQuery.of(
     "SELECT * FROM jobs {where}",
     ParameterizedQuery.optionally("WHERE {filter}", getOptionalFilter()));
      Since:
      8.2

    • template

      public static StringFormat.Template<ParameterizedQuery> template(@CompileTimeConstant String template)
      Returns a template of QueryJobConfiguration based on the template string.

      For example: 

      
 private static final Template<QueryJobConfiguration> GET_JOB_IDS_BY_QUERY =
     ParameterizedQuery.template(
         """
         SELECT job_id from INFORMATION_SCHEMA.JOBS_BY_PROJECT
         WHERE configuration.query LIKE '%{keyword}%'
         """);

 TableResult result = GET_JOB_IDS_BY_QUERY.with("sensitive word").run();

      Except ParameterizedQuery itself, which are directly substituted into the query, all other placeholder arguments are passed into the QueryJobConfiguration as query parameters.

      Placeholder types supported:

      • CharSequence
      • Enum
      • java.time.Instant (translated to TIMESTAMP)
      • java.time.LocalDate (translated to DATE)
      • Integer
      • Long
      • BigDecimal
      • Double
      • Float
      • arrays
      If you need to supply other types, consider to wrap them explicitly using one of the static factory methods of QueryParameterValue.

    • enumConstants

      public static Stream<ParameterizedQuery> enumConstants(Class<? extends Enum<?>> enumClass)
      Returns the stream of enum constants defined by enumClass, with the names wrapped in ParameterizedQuery}.

    • and

      public static Collector<ParameterizedQuery,?,ParameterizedQuery> and()
      A collector that joins boolean query snippets using AND operator. The AND'ed sub-queries will be enclosed in pairs of parenthesis to avoid ambiguity. If the input is empty, the result will be "TRUE".

      Empty ParameterizedQuery elements are ignored and not joined.

      Since:
      8.2

    • or

      public static Collector<ParameterizedQuery,?,ParameterizedQuery> or()
      A collector that joins boolean query snippets using OR operator. The OR'ed sub-queries will be enclosed in pairs of parenthesis to avoid ambiguity. If the input is empty, the result will be "FALSE".

      Empty ParameterizedQuery elements are ignored and not joined.

      Since:
      8.2

    • joining

      public static Collector<ParameterizedQuery,?,ParameterizedQuery> joining(@CompileTimeConstant String delimiter)
      Returns a collector that joins ParameterizedQuery elements using delimiter.

      Useful if you need to parameterize by a set of columns to select. Say, you might need to query the table names only, or read the project, dataset and table names: 

      
 private static final Template<ParameterizedQuery> QUERY_TABLES =
     ParameterizedQuery.template("SELECT {columns} FROM {dataset}.INFORMATION_SCHEMA.TABLES");

 ParameterizedQuery getTableNames = QUERY_TABLES.with(ParameterizedQuery.of("table_name"));
 ParameterizedQuery getFullyQualified = QUERY_TABLES.with(
     Stream.of("table_catalog", "table_schema", "table_name")
         .map(ParameterizedQuery::of)
         .collect(ParameterizedQuery.joining(", ")),
     ParameterizedQuery.of("my-dataset"));

    • run

      public com.google.cloud.bigquery.TableResult run(com.google.cloud.bigquery.BigQuery.JobOption... options) throws com.google.cloud.bigquery.JobException, InterruptedException
      Sends this query to BigQuery using the default client configuration with options to control BigQuery jobs.

      To use alternative configuration, pass the return value of jobConfiguration() to the BigQuery object of your choice.

      Throws:
      com.google.cloud.bigquery.JobException
      InterruptedException

    • jobConfiguration

      public com.google.cloud.bigquery.QueryJobConfiguration jobConfiguration()
      Returns the QueryJobConfiguration that can be sent to BigQuery.

    • hashCode

      public int hashCode()
      Overrides:
      hashCode in class Object

    • equals

      public boolean equals(Object obj)
      Overrides:
      equals in class Object

    • toString

      public String toString()
      Overrides:
      toString in class Object