/** Example of running a query with timestamp query parameters. */ public void runQueryWithTimestampParameters() throws InterruptedException { // [START bigquery_query_params_timestamps] // 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"); } // [END bigquery_query_params_timestamps] }
/** Example of running a query with array query parameters. */ public void runQueryWithArrayParameters() throws InterruptedException { // [START bigquery_query_params_arrays] // 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"); } // [END bigquery_query_params_arrays] }
/** Example of running a query with named query parameters. */ public void runQueryWithNamedParameters() throws InterruptedException { // [START bigquery_query_params_named] // 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"); } // [END bigquery_query_params_named] }
@Test public void testNamedQueryParameters() throws InterruptedException { String query = "SELECT TimestampField, StringField, BooleanField FROM " + TABLE_ID.getTable() + " WHERE StringField = @stringParam" + " AND IntegerField IN UNNEST(@integerList)"; QueryParameterValue stringParameter = QueryParameterValue.string("stringValue"); QueryParameterValue intArrayParameter = QueryParameterValue.array(new Integer[] {3, 4}, Integer.class); QueryJobConfiguration config = QueryJobConfiguration.newBuilder(query) .setDefaultDataset(DatasetId.of(DATASET)) .setUseLegacySql(false) .addNamedParameter("stringParam", stringParameter) .addNamedParameter("integerList", intArrayParameter) .build(); TableResult result = bigquery.query(config); assertEquals(QUERY_RESULT_SCHEMA, result.getSchema()); assertEquals(2, Iterables.size(result.getValues())); }
@Test public void testBytesParameter() throws Exception { String query = "SELECT BYTE_LENGTH(@p) AS length"; QueryParameterValue bytesParameter = QueryParameterValue.bytes(new byte[] {1, 3}); QueryJobConfiguration config = QueryJobConfiguration.newBuilder(query) .setDefaultDataset(DatasetId.of(DATASET)) .setUseLegacySql(false) .addNamedParameter("p", bytesParameter) .build(); TableResult result = bigquery.query(config); int rowCount = 0; for (FieldValueList row : result.getValues()) { rowCount++; assertEquals(2, row.get(0).getLongValue()); assertEquals(2, row.get("length").getLongValue()); } assertEquals(1, rowCount); }