|
| 1 | +/* |
| 2 | + Copyright 2016, Google, Inc. |
| 3 | +
|
| 4 | + Licensed under the Apache License, Version 2.0 (the "License"); |
| 5 | + you may not use this file except in compliance with the License. |
| 6 | + You may obtain a copy of the License at |
| 7 | +
|
| 8 | + http://www.apache.org/licenses/LICENSE-2.0 |
| 9 | +
|
| 10 | + Unless required by applicable law or agreed to in writing, software |
| 11 | + distributed under the License is distributed on an "AS IS" BASIS, |
| 12 | + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| 13 | + See the License for the specific language governing permissions and |
| 14 | + limitations under the License. |
| 15 | +*/ |
| 16 | + |
| 17 | +package com.example.bigquery; |
| 18 | + |
| 19 | +import com.google.cloud.bigquery.BigQuery; |
| 20 | +import com.google.cloud.bigquery.BigQueryOptions; |
| 21 | +import com.google.cloud.bigquery.FieldValue; |
| 22 | +import com.google.cloud.bigquery.Job; |
| 23 | +import com.google.cloud.bigquery.JobId; |
| 24 | +import com.google.cloud.bigquery.JobInfo; |
| 25 | +import com.google.cloud.bigquery.QueryJobConfiguration; |
| 26 | +import com.google.cloud.bigquery.QueryResponse; |
| 27 | +import com.google.cloud.bigquery.QueryResult; |
| 28 | +import com.google.cloud.bigquery.TableId; |
| 29 | +import org.apache.commons.cli.CommandLine; |
| 30 | +import org.apache.commons.cli.CommandLineParser; |
| 31 | +import org.apache.commons.cli.DefaultParser; |
| 32 | +import org.apache.commons.cli.Option; |
| 33 | +import org.apache.commons.cli.OptionGroup; |
| 34 | +import org.apache.commons.cli.Options; |
| 35 | +import org.apache.commons.cli.ParseException; |
| 36 | + |
| 37 | +import java.io.IOException; |
| 38 | +import java.util.Iterator; |
| 39 | +import java.util.List; |
| 40 | +import java.util.UUID; |
| 41 | +import java.util.concurrent.TimeoutException; |
| 42 | + |
| 43 | +/** Runs a query against BigQuery. */ |
| 44 | +public class QuerySample { |
| 45 | + // [START query_config_simple] |
| 46 | + public static void runSimpleQuery(String queryString) |
| 47 | + throws TimeoutException, InterruptedException { |
| 48 | + QueryJobConfiguration queryConfig = |
| 49 | + QueryJobConfiguration.newBuilder(queryString).build(); |
| 50 | + |
| 51 | + runQuery(queryConfig); |
| 52 | + } |
| 53 | + // [END query_config_simple] |
| 54 | + |
| 55 | + // [START query_config_standard_sql] |
| 56 | + public static void runStandardSqlQuery(String queryString) |
| 57 | + throws TimeoutException, InterruptedException { |
| 58 | + QueryJobConfiguration queryConfig = |
| 59 | + QueryJobConfiguration.newBuilder(queryString) |
| 60 | + // To use standard SQL syntax, set useLegacySql to false. |
| 61 | + // See: https://cloud.google.com/bigquery/sql-reference/ |
| 62 | + .setUseLegacySql(false) |
| 63 | + .build(); |
| 64 | + |
| 65 | + runQuery(queryConfig); |
| 66 | + } |
| 67 | + // [END query_config_standard_sql] |
| 68 | + |
| 69 | + // [START query_config_permanent_table] |
| 70 | + public static void runQueryPermanentTable( |
| 71 | + String queryString, |
| 72 | + String destinationDataset, |
| 73 | + String destinationTable, |
| 74 | + boolean allowLargeResults) throws TimeoutException, InterruptedException { |
| 75 | + QueryJobConfiguration queryConfig = |
| 76 | + QueryJobConfiguration.newBuilder(queryString) |
| 77 | + // Save the results of the query to a permanent table. |
| 78 | + // See: https://cloud.google.com/bigquery/querying-data#permanent-table |
| 79 | + .setDestinationTable(TableId.of(destinationDataset, destinationTable)) |
| 80 | + // Allow results larger than the maximum response size. |
| 81 | + // If true, a destination table must be set. |
| 82 | + // See: https://cloud.google.com/bigquery/querying-data#large-results |
| 83 | + .setAllowLargeResults(allowLargeResults) |
| 84 | + .build(); |
| 85 | + |
| 86 | + runQuery(queryConfig); |
| 87 | + } |
| 88 | + // [END query_config_permanent_table] |
| 89 | + |
| 90 | + // [START query_config_cache] |
| 91 | + public static void runUncachedQuery(String queryString) |
| 92 | + throws TimeoutException, InterruptedException { |
| 93 | + QueryJobConfiguration queryConfig = |
| 94 | + QueryJobConfiguration.newBuilder(queryString) |
| 95 | + // Do not use the query cache. Force live query evaluation. |
| 96 | + // See: https://cloud.google.com/bigquery/querying-data#query-caching |
| 97 | + .setUseQueryCache(false) |
| 98 | + .build(); |
| 99 | + |
| 100 | + runQuery(queryConfig); |
| 101 | + } |
| 102 | + // [END query_config_cache] |
| 103 | + |
| 104 | + // [START query_config_batch] |
| 105 | + public static void runBatchQuery(String queryString) |
| 106 | + throws TimeoutException, InterruptedException { |
| 107 | + QueryJobConfiguration queryConfig = |
| 108 | + QueryJobConfiguration.newBuilder(queryString) |
| 109 | + // Run at batch priority, which won't count toward concurrent rate |
| 110 | + // limit. |
| 111 | + // See: https://cloud.google.com/bigquery/querying-data#interactive-batch |
| 112 | + .setPriority(QueryJobConfiguration.Priority.BATCH) |
| 113 | + .build(); |
| 114 | + |
| 115 | + runQuery(queryConfig); |
| 116 | + } |
| 117 | + // [END query_config_batch] |
| 118 | + |
| 119 | + |
| 120 | + // [START run_query] |
| 121 | + public static void runQuery(QueryJobConfiguration queryConfig) |
| 122 | + throws TimeoutException, InterruptedException { |
| 123 | + BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService(); |
| 124 | + |
| 125 | + // Create a job ID so that we can safely retry. |
| 126 | + JobId jobId = JobId.of(UUID.randomUUID().toString()); |
| 127 | + Job queryJob = bigquery.create(JobInfo.newBuilder(queryConfig).setJobId(jobId).build()); |
| 128 | + |
| 129 | + // Wait for the query to complete. |
| 130 | + queryJob = queryJob.waitFor(); |
| 131 | + |
| 132 | + // Check for errors |
| 133 | + if (queryJob == null) { |
| 134 | + throw new RuntimeException("Job no longer exists"); |
| 135 | + } else if (queryJob.getStatus().getError() != null) { |
| 136 | + // You can also look at queryJob.getStatus().getExecutionErrors() for all |
| 137 | + // errors, not just the latest one. |
| 138 | + throw new RuntimeException(queryJob.getStatus().getError().toString()); |
| 139 | + } |
| 140 | + |
| 141 | + // Get the results. |
| 142 | + QueryResponse response = bigquery.getQueryResults(jobId); |
| 143 | + QueryResult result = response.getResult(); |
| 144 | + |
| 145 | + // Print all pages of the results. |
| 146 | + while (result != null) { |
| 147 | + if (response.hasErrors()) { |
| 148 | + String firstError = ""; |
| 149 | + if (response.getExecutionErrors().size() != 0) { |
| 150 | + firstError = response.getExecutionErrors().get(0).getMessage(); |
| 151 | + } |
| 152 | + throw new RuntimeException(firstError); |
| 153 | + } |
| 154 | + |
| 155 | + Iterator<List<FieldValue>> iter = result.iterateAll(); |
| 156 | + while (iter.hasNext()) { |
| 157 | + List<FieldValue> row = iter.next(); |
| 158 | + for (FieldValue val : row) { |
| 159 | + System.out.printf("%s,", val.toString()); |
| 160 | + } |
| 161 | + System.out.printf("\n"); |
| 162 | + } |
| 163 | + |
| 164 | + result = result.getNextPage(); |
| 165 | + } |
| 166 | + } |
| 167 | + // [END run_query] |
| 168 | + |
| 169 | + /** Prompts the user for the required parameters to perform a query. */ |
| 170 | + public static void main(final String[] args) |
| 171 | + throws IOException, InterruptedException, TimeoutException, ParseException { |
| 172 | + Options options = new Options(); |
| 173 | + |
| 174 | + // Use an OptionsGroup to choose which sample to run. |
| 175 | + OptionGroup samples = new OptionGroup(); |
| 176 | + samples.addOption(Option.builder().longOpt("runSimpleQuery").build()); |
| 177 | + samples.addOption(Option.builder().longOpt("runStandardSqlQuery").build()); |
| 178 | + samples.addOption(Option.builder().longOpt("runPermanentTableQuery").build()); |
| 179 | + samples.addOption(Option.builder().longOpt("runUncachedQuery").build()); |
| 180 | + samples.addOption(Option.builder().longOpt("runBatchQuery").build()); |
| 181 | + samples.isRequired(); |
| 182 | + options.addOptionGroup(samples); |
| 183 | + |
| 184 | + options.addOption(Option.builder().longOpt("query").hasArg().required().build()); |
| 185 | + options.addOption(Option.builder().longOpt("destDataset").hasArg().build()); |
| 186 | + options.addOption(Option.builder().longOpt("destTable").hasArg().build()); |
| 187 | + options.addOption(Option.builder().longOpt("allowLargeResults").build()); |
| 188 | + |
| 189 | + CommandLineParser parser = new DefaultParser(); |
| 190 | + CommandLine cmd = parser.parse(options, args); |
| 191 | + |
| 192 | + String query = cmd.getOptionValue("query"); |
| 193 | + if (cmd.hasOption("runSimpleQuery")) { |
| 194 | + runSimpleQuery(query); |
| 195 | + } else if (
10A29
cmd.hasOption("runStandardSqlQuery")) { |
| 196 | + runStandardSqlQuery(query); |
| 197 | + } else if (cmd.hasOption("runPermanentTableQuery")) { |
| 198 | + String destDataset = cmd.getOptionValue("destDataset"); |
| 199 | + String destTable = cmd.getOptionValue("destTable"); |
| 200 | + boolean allowLargeResults = cmd.hasOption("allowLargeResults"); |
| 201 | + runQueryPermanentTable(query, destDataset, destTable, allowLargeResults); |
| 202 | + } else if (cmd.hasOption("runUncachedQuery")) { |
| 203 | + runUncachedQuery(query); |
| 204 | + } else if (cmd.hasOption("runBatchQuery")) { |
| 205 | + runBatchQuery(query); |
| 206 | + } |
| 207 | + } |
| 208 | +} |
0 commit comments