Package com.google.mu.bigquery

Class ParameterizedQuery

  • @Immutable
public final class ParameterizedQuery
extends java.lang.Object
    Facade class to create BigQuery parameterized queries using a template string and parameters.

    The string template syntax is defined by StringFormat and protected by the same set of compile-time checks.

    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 StringFormat.To<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 StringFormat.To<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

    • Method Summary

      Modifier and Type Method Description
      static java.util.stream.Stream<ParameterizedQuery> enumConstants​(java.lang.Class<? extends java.lang.Enum<?>> enumClass)
      Returns the stream of enum constants defined by enumClass, with the names wrapped in ParameterizedQuery}.
      boolean equals​(java.lang.Object obj)  
      int hashCode()  
      com.google.cloud.bigquery.QueryJobConfiguration jobConfiguration()
      Returns the QueryJobConfiguration that can be sent to BigQuery.
      static java.util.stream.Collector<ParameterizedQuery,​?,​ParameterizedQuery> joining​(java.lang.String delimiter)
      Returns a collector that joins ParameterizedQuery elements using delimiter.
      static ParameterizedQuery of​(java.lang.String query, java.lang.Object... args)
      Convenience method when you need to create the ParameterizedQuery inline, with both the query template and the arguments.
      com.google.cloud.bigquery.TableResult run​(com.google.cloud.bigquery.BigQuery.JobOption... options)
      Sends this query to BigQuery using the default options.
      static StringFormat.To<ParameterizedQuery> template​(java.lang.String template)
      Returns a template of QueryJobConfiguration based on the template string.
      java.lang.String toString()  

      • Methods inherited from class java.lang.Object

        clone, finalize, getClass, notify, notifyAll, wait, wait, wait

    • Method Detail

      • of

        public static ParameterizedQuery of​(@CompileTimeConstant
                                    java.lang.String query,
                                    java.lang.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();

      • template

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

        For example: 

        
 private static final StringFormat.To<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 java.util.stream.Stream<ParameterizedQuery> enumConstants​(java.lang.Class<? extends java.lang.Enum<?>> enumClass)
        Returns the stream of enum constants defined by enumClass, with the names wrapped in ParameterizedQuery}.

      • joining

        public static java.util.stream.Collector<ParameterizedQuery,​?,​ParameterizedQuery> joining​(@CompileTimeConstant
                                                                                                      java.lang.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 StringFormat.To<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,
                                                 java.lang.InterruptedException
        Sends this query to BigQuery using the default options.

        To use alternative options, pass jobConfiguration() to the {link BigQueryOptions} of your choice.

        Throws:
        com.google.cloud.bigquery.JobException
        java.lang.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 java.lang.Object

      • equals

        public boolean equals​(java.lang.Object obj)
        Overrides:
        equals in class java.lang.Object

      • toString

        public java.lang.String toString()
        Overrides:
        toString in class java.lang.Object