/** Example of running a query. */ // [TARGET query(QueryJobConfiguration, JobOption...)] public void runQuery() throws InterruptedException { // [START bigquery_query] // BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService(); String query = "SELECT corpus FROM `bigquery-public-data.samples.shakespeare` GROUP BY corpus;"; QueryJobConfiguration queryConfig = QueryJobConfiguration.newBuilder(query).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] } }
/** Example of running a query with the cache disabled. */ public void runUncachedQuery() throws TimeoutException, InterruptedException { // [START bigquery_query_no_cache] // BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService(); String query = "SELECT corpus FROM `bigquery-public-data.samples.shakespeare` GROUP BY corpus;"; QueryJobConfiguration queryConfig = QueryJobConfiguration.newBuilder(query) // Disable the query cache to force live query evaluation. .setUseQueryCache(false) .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_no_cache] }
/** Example of running a Legacy SQL query. */ public void runLegacySqlQuery() throws InterruptedException { // [START bigquery_query_legacy] // BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService(); String query = "SELECT corpus FROM [bigquery-public-data:samples.shakespeare] GROUP BY corpus;"; QueryJobConfiguration queryConfig = // To use legacy SQL syntax, set useLegacySql to true. QueryJobConfiguration.newBuilder(query).setUseLegacySql(true).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_legacy] }
/** Example of running a query and saving the results to a table. */ public void runQueryPermanentTable(String destinationDataset, String destinationTable) throws InterruptedException { // [START bigquery_query_destination_table] // BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService(); // String destinationDataset = 'my_destination_dataset'; // String destinationTable = 'my_destination_table'; String query = "SELECT corpus FROM `bigquery-public-data.samples.shakespeare` GROUP BY corpus;"; QueryJobConfiguration queryConfig = // Note that setUseLegacySql is set to false by default QueryJobConfiguration.newBuilder(query) // Save the results of the query to a permanent table. .setDestinationTable(TableId.of(destinationDataset, destinationTable)) .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_destination_table] }
/** 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] }
/** Example of running a query and saving the results to a table. */ public void runQueryLargeResults(String destinationDataset, String destinationTable) throws InterruptedException { // [START bigquery_query_legacy_large_results] // BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService(); // String destinationDataset = 'my_destination_dataset'; // String destinationTable = 'my_destination_table'; String query = "SELECT corpus FROM [bigquery-public-data:samples.shakespeare] GROUP BY corpus;"; QueryJobConfiguration queryConfig = // To use legacy SQL syntax, set useLegacySql to true. QueryJobConfiguration.newBuilder(query) .setUseLegacySql(true) // Save the results of the query to a permanent table. .setDestinationTable(TableId.of(destinationDataset, destinationTable)) // Allow results larger than the maximum response size. // If true, a destination table must be set. .setAllowLargeResults(true) .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_legacy_large_results] }
/** 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] }