Skip to content

Commit 1fefc4c

Browse files
vam-googlevam-google
authored
Implement missing features and fix found bugs for BigQuery Client Library (googleapis#2446)
0) Access query results row cells by name. Now `row.get(1)` and `get("firstColumnName")` are supported (currently supported only for results returned by `BigQuery.query()` and not supported by results returned `BigQuery.listTableData()` because there is no schema and it would require additional request to the server to get schema (but still can be added in the future easily with cost of an extra network call)). Same syntax is supported for record-type fields (i.e. nested fields can be accessed by name too). 1) Refactor `BigQuery.query()` to use `jobs.insert/jobs.getQueryResults` combination instead of `jobs.query`. `QueryRequest` class was removed, now `query()` method directly accepts `QueryJobConfiguration` instead. The remaining properties of removed `QueryRequest` class now are passed in a form of `QueryOption` vararg argument. `QueryOption` combines query and waiting options (waiting options are necessary because query() is now waiting for query to complete). 2) Make `BigQuery.query()` call synchronous (waits for query completion before return result), with jittered exponential backoff for polling (based on `gax.retrying` package) 3) Add client side job id generation (`JobId.of()`) 4) Replace `WaitForOption` with `RetryOption` (based on `RetrySettings` and is consistent with the rest of the codebase). This affected compute and spanner clients too. 5) Rewrite `Job.wait()` to use jittered exponentiall backoff polling (based on`gax.retrying` package). Polling is performed differently depending on the type of job: for query jobs on each poll `getQueryResults()` is called, for all other job types `getJob()` is used instead. 6) Use standard SQL as default for all queries 7) Fix wrong query results iteration samples code used all over the place in documentation. 8) Various smaller changes/refactorings
1 parent 549bc4d commit 1fefc4c

File tree

70 files changed

+2127
-2361
lines changed

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

70 files changed

+2127
-2361
lines changed

‎google-cloud-bigquery/README.md‎

Lines changed: 10 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -125,7 +125,7 @@ Then add the following code to create the table:
125125
```java
126126
TableId tableId = TableId.of(datasetId, "my_table_id");
127127
// Table field definition
128-
Field stringField = Field.of("StringField", Field.Type.string());
128+
Field stringField = Field.of("StringField", LegacySQLTypeName.STRING);
129129
// Table schema definition
130130
Schema schema = Schema.of(stringField);
131131
// Create a table
@@ -172,7 +172,7 @@ for the result. Add the following imports at the top of your file:
172172

173173
```java
174174
import com.google.cloud.bigquery.FieldValue;
175-
import com.google.cloud.bigquery.QueryRequest;
175+
import com.google.cloud.bigquery.QueryJobConfiguration;
176176
import com.google.cloud.bigquery.QueryResponse;
177177

178178
import java.util.Iterator;
@@ -182,22 +182,17 @@ Then add the following code to run the query and wait for the result:
182182

183183
```java
184184
// Create a query request
185-
QueryRequest queryRequest =
186-
QueryRequest.newBuilder("SELECT * FROM my_dataset_id.my_table_id")
187-
.setMaxWaitTime(60000L)
188-
.setPageSize(1000L)
189-
.build();
185+
QueryJobConfiguration queryConfig =
186+
QueryJobConfiguration.of("SELECT * FROM my_dataset_id.my_table_id");
190187
// Request query to be executed and wait for results
191-
QueryResponse queryResponse = bigquery.query(queryRequest);
192-
while (!queryResponse.jobCompleted()) {
193-
Thread.sleep(1000L);
194-
queryResponse = bigquery.getQueryResults(queryResponse.getJobId());
195-
}
188+
QueryResponse queryResponse = bigquery.query(
189+
queryConfig,
190+
QueryOption.of(QueryResultsOption.maxWaitTime(60000L)),
191+
QueryOption.of(QueryResultsOption.pageSize(1000L)));
196192
// Read rows
197-
Iterator<List<FieldValue>> rowIterator = queryResponse.getResult().iterateAll();
198193
System.out.println("Table rows:");
199-
while (rowIterator.hasNext()) {
200-
System.out.println(rowIterator.next());
194+
for (FieldValues row : queryResponse.getResult().iterateAll()) {
195+
System.out.println(row);
201196
}
202197
```
203198
#### Complete source code

‎google-cloud-bigquery/src/main/java/com/google/cloud/bigquery/BigQuery.java‎

Lines changed: 120 additions & 59 deletions
Original file line numberDiff line numberDiff line change
@@ -21,12 +21,15 @@
2121
import com.google.api.gax.paging.Page;
2222
import com.google.cloud.FieldSelector;
2323
import com.google.cloud.FieldSelector.Helper;
24+
import com.google.cloud.RetryOption;
2425
import com.google.cloud.Service;
2526
import com.google.cloud.bigquery.spi.v2.BigQueryRpc;
2627
import com.google.common.base.Function;
2728
import com.google.common.collect.ImmutableList;
2829
import com.google.common.collect.Lists;
2930

31+
import java.io.Serializable;
32+
import java.util.ArrayList;
3033
import java.util.List;
3134

3235
/**
@@ -440,6 +443,72 @@ public static QueryResultsOption maxWaitTime(long maxWaitTime) {
440443
}
441444
}
442445

446+
class QueryOption implements Serializable {
447+
private static final long serialVersionUID = 6206193419355824689L;
448+
449+
private final Object option;
450+
451+
private QueryOption(Object option) {
452+
this.option = option;
453+
}
454+
455+
public QueryResultsOption getQueryResultsOption() {
456+
return option instanceof QueryResultsOption ? (QueryResultsOption) option : null;
457+
}
458+
459+
public RetryOption getRetryOption() {
460+
return option instanceof RetryOption ? (RetryOption) option : null;
461+
}
462+
463+
static QueryResultsOption[] filterQueryResultsOptions(QueryOption... options) {
464+
List<QueryResultsOption> queryResultOptions = new ArrayList<>(options.length);
465+
for (QueryOption opt : options) {
466+
if (opt.getQueryResultsOption() != null) {
467+
queryResultOptions.add(opt.getQueryResultsOption());
468+
}
469+
}
470+
return queryResultOptions.toArray(new QueryResultsOption[queryResultOptions.size()]);
471+
}
472+
473+
static RetryOption[] filterRetryOptions(QueryOption... options) {
474+
List<RetryOption> retryOptions = new ArrayList<>(options.length);
475+
for (QueryOption opt : options) {
476+
if (opt.getRetryOption() != null) {
477+
retryOptions.add(opt.getRetryOption());
478+
}
479+
}
480+
return retryOptions.toArray(new RetryOption[retryOptions.size()]);
481+
}
482+
483+
public static QueryOption of(QueryResultsOption resultsOption) {
484+
return new QueryOption(resultsOption);
485+
}
486+
487+
public static QueryOption of(RetryOption waitOption) {
488+
return new QueryOption(waitOption);
489+
}
490+
491+
@Override
492+
public boolean equals(Object o) {
493+
if (this == o) {
494+
return true;
495+
}
496+
if (o == null || getClass() != o.getClass()) {
497+
return false;
498+
}
499+
500+
QueryOption that = (QueryOption) o;
501+
502+
return option != null ? option.equals(that.option) : that.option == null;
503+
}
504+
505+
@Override
506+
public int hashCode() {
507+
return option != null ? option.hashCode() : 0;
508+
}
509+
}
510+
511+
443512
/**
444513
* Creates a new dataset.
445514
*
@@ -540,9 +609,7 @@ public static QueryResultsOption maxWaitTime(long maxWaitTime) {
540609
* <p>Example of listing datasets, specifying the page size.
541610
* <pre> {@code
542611
* Page<Dataset> datasets = bigquery.listDatasets(DatasetListOption.pageSize(100));
543-
* Iterator<Dataset> datasetIterator = datasets.iterateAll();
544-
* while (datasetIterator.hasNext()) {
545-
* Dataset dataset = datasetIterator.next();
612+
* for (Dataset dataset : datasets.iterateAll()) {
546613
* // do something with the dataset
547614
* }
548615
* }</pre>
@@ -562,9 +629,7 @@ public static QueryResultsOption maxWaitTime(long maxWaitTime) {
562629
* <pre> {@code
563630
* String projectId = "my_project_id";
564631
* Page<Dataset> datasets = bigquery.listDatasets(projectId, DatasetListOption.pageSize(100));
565-
* Iterator<Dataset> datasetIterator = datasets.iterateAll();
566-
* while (datasetIterator.hasNext()) {
567-
* Dataset dataset = datasetIterator.next();
632+
* for (Dataset dataset : datasets.iterateAll()) {
568633
* // do something with the dataset
569634
* }
570635
* }</pre>
@@ -729,9 +794,7 @@ public static QueryResultsOption maxWaitTime(long maxWaitTime) {
729794
* <pre> {@code
730795
* String datasetName = "my_dataset_name";
731796
* Page<Table> tables = bigquery.listTables(datasetName, TableListOption.pageSize(100));
732-
* Iterator<Table> tableIterator = tables.iterateAll();
733-
* while (tableIterator.hasNext()) {
734-
* Table table = tableIterator.next();
797+
* for (Table table : tables.iterateAll()) {
735798
* // do something with the table
736799
* }
737800
* }</pre>
@@ -753,9 +816,7 @@ public static QueryResultsOption maxWaitTime(long maxWaitTime) {
753816
* String datasetName = "my_dataset_name";
754817
* DatasetId datasetId = DatasetId.of(projectId, datasetName);
755818
* Page<Table> tables = bigquery.listTables(datasetId, TableListOption.pageSize(100));
756-
* Iterator<Table> tableIterator = tables.iterateAll();
757-
* while (tableIterator.hasNext()) {
758-
* Table table = tableIterator.next();
819+
* for (Table table : tables.iterateAll()) {
759820
* // do something with the table
760821
* }
761822
* }</pre>
@@ -804,18 +865,16 @@ public static QueryResultsOption maxWaitTime(long maxWaitTime) {
804865
* <pre> {@code
805866
* String datasetName = "my_dataset_name";
806867
* String tableName = "my_table_name";
807-
* Page<List<FieldValue>> tableData =
868+
* Page<FieldValueList> tableData =
808869
* bigquery.listTableData(datasetName, tableName, TableDataListOption.pageSize(100));
809-
* Iterator<List<FieldValue>> rowIterator = tableData.iterateAll();
810-
* while (rowIterator.hasNext()) {
811-
* List<FieldValue> row = rowIterator.next();
870+
* for (FieldValueList row : tableData.iterateAll()) {
812871
* // do something with the row
813872
* }
814873
* }</pre>
815874
*
816875
* @throws BigQueryException upon failure
817876
*/
818-
Page<List<FieldValue>> listTableData(String datasetId, String tableId,
877+
Page<FieldValueList> listTableData(String datasetId, String tableId,
819878
TableDataListOption... options);
820879

821880
/**
@@ -826,18 +885,16 @@ Page<List<FieldValue>> listTableData(String datasetId, String tableId,
826885
* String datasetName = "my_dataset_name";
827886
* String tableName = "my_table_name";
828887
* TableId tableIdObject = TableId.of(datasetName, tableName);
829-
* Page<List<FieldValue>> tableData =
888+
* Page<FieldValueList> tableData =
830889
* bigquery.listTableData(tableIdObject, TableDataListOption.pageSize(100));
831-
* Iterator<List<FieldValue>> rowIterator = tableData.iterateAll();
832-
* while (rowIterator.hasNext()) {
833-
* List<FieldValue> row = rowIterator.next();
890+
* for (FieldValueList row : rowIterator.hasNext()) {
834891
* // do something with the row
835892
* }
836893
* }</pre>
837894
*
838895
* @throws BigQueryException upon failure
839896
*/
840-
Page<List<FieldValue>> listTableData(TableId tableId, TableDataListOption... options);
897+
Page<FieldValueList> listTableData(TableId tableId, TableDataListOption... options);
841898

842899
/**
843900
* Returns the requested job or {@code null} if not found.
@@ -878,9 +935,7 @@ Page<List<FieldValue>> listTableData(String datasetId, String tableId,
878935
* <p>Example of listing jobs, specifying the page size.
879936
* <pre> {@code
880937
* Page<Job> jobs = bigquery.listJobs(JobListOption.pageSize(100));
881-
* Iterator<Job> jobIterator = jobs.iterateAll();
882-
* while (jobIterator.hasNext()) {
883-
* Job job = jobIterator.next();
938+
* for (Job job : jobs.iterateAll()) {
884939
* // do something with the job
885940
* }
886941
* }</pre>
@@ -939,74 +994,81 @@ Page<List<FieldValue>> listTableData(String datasetId, String tableId,
939994
*
940995
* <p>Example of running a query.
941996
* <pre> {@code
942-
* String query = "SELECT unique(corpus) FROM [bigquery-public-data:samples.shakespeare]";
943-
* QueryRequest request = QueryRequest.of(query);
944-
* QueryResponse response = bigquery.query(request);
945-
* // Wait for things to finish
946-
* while (!response.jobCompleted()) {
947-
* Thread.sleep(1000);
948-
* response = bigquery.getQueryResults(response.getJobId());
949-
* }
997+
* String query = "SELECT distinct(corpus) FROM `bigquery-public-data.samples.shakespeare`";
998+
* QueryJobConfiguration queryConfig = QueryJobConfiguration.of(query);
999+
*
1000+
* // To run the legacy syntax queries use the following code instead:
1001+
* // String query = "SELECT unique(corpus) FROM [bigquery-public-data:samples.shakespeare]"
1002+
* // QueryJobConfiguration queryConfig =
1003+
* // QueryJobConfiguration.newBuilder(query).setUseLegacySql(true).build();
1004+
*
1005+
* QueryResponse response = bigquery.query(queryConfig);
9501006
* if (response.hasErrors()) {
9511007
* // handle errors
9521008
* }
9531009
* QueryResult result = response.getResult();
954-
* Iterator<List<FieldValue>> rowIterator = result.iterateAll();
955-
* while (rowIterator.hasNext()) {
956-
* List<FieldValue> row = rowIterator.next();
1010+
* for (FieldValueList row : result.iterateAll()) {
9571011
* // do something with the data
9581012
* }
9591013
* }</pre>
9601014
*
9611015
* <p>Example of running a query with query parameters.
9621016
* <pre> {@code
963-
* String query = "SELECT distinct(corpus) FROM `bigquery-public-data.samples.shakespeare` where word_count > ?";
964-
* QueryRequest request = QueryRequest.newBuilder(query)
965-
* .setUseLegacySql(false) // standard SQL is required to use query parameters
1017+
* String query =
1018+
* "SELECT distinct(corpus) FROM `bigquery-public-data.samples.shakespeare` where word_count > ?";
1019+
* QueryJobConfiguration queryConfig = QueryJobConfiguration.newBuilder(query)
9661020
* .addPositionalParameter(QueryParameterValue.int64(5))
9671021
* .build();
968-
* QueryResponse response = bigquery.query(request);
969-
* // Wait for things to finish
970-
* while (!response.jobCompleted()) {
971-
* Thread.sleep(1000);
972-
* response = bigquery.getQueryResults(response.getJobId());
973-
* }
1022+
* QueryResponse response = bigquery.query(queryConfig);
9741023
* if (response.hasErrors()) {
9751024
* // handle errors
9761025
* }
9771026
* QueryResult result = response.getResult();
978-
* Iterator<List<FieldValue>> rowIterator = result.iterateAll();
979-
* while (rowIterator.hasNext()) {
980-
* List<FieldValue> row = rowIterator.next();
1027+
* for (FieldValueList row : result.iterateAll()) {
9811028
* // do something with the data
9821029
* }
9831030
* }</pre>
9841031
*
9851032
* @throws BigQueryException upon failure
1033+
* @throws InterruptedException if the current thread gets interrupted while waiting for the query
1034+
* to complete
9861035
*/
987-
QueryResponse query(QueryRequest request);
1036+
QueryResponse query(QueryJobConfiguration configuration, QueryOption... options)
1037+
throws InterruptedException;
9881038

9891039
/**
9901040
* Runs the query associated with the request, using the given job id.
9911041
*
992-
* <p>See {@link #query(QueryRequest)} for examples on populating a {@link QueryRequest}. The
993-
* recommended way to populate a JobId is the following:
1042+
* <p>See {@link #query(QueryJobConfiguration, QueryOption...)} for examples on populating a
1043+
* {@link QueryJobConfiguration}.
9941044
*
9951045
* <p>
1046+
* The recommended way to create a randomly generated JobId is the following:
1047+
*
9961048
* <pre> {@code
997-
* JobId jobId = JobId.of(UUID.randomUUID().toString());
1049+
* JobId jobId = JobId.of();
9981050
* }</pre>
1051+
*
1052+
* For a user specified job id with an optional prefix use the following:
1053+
* <pre> {@code
1054+
* JobId jobId = JobId.of("my_prefix-my_unique_job_id");
1055+
* }</pre>
1056+
*
1057+
* @throws BigQueryException upon failure
1058+
* @throws InterruptedException if the current thread gets interrupted while waiting for the query
1059+
* to complete
9991060
*/
1000-
QueryResponse query(QueryRequest request, JobId jobId);
1061+
QueryResponse query(QueryJobConfiguration configuration, JobId jobId, QueryOption... options)
1062+
throws InterruptedException;
10011063

10021064
/**
10031065
* Returns results of the query associated with the provided job.
10041066
*
10051067
* <p>Example of getting the results of query.
10061068
* <pre> {@code
1007-
* String query = "SELECT unique(corpus) FROM [bigquery-public-data:samples.shakespeare]";
1008-
* QueryRequest request = QueryRequest.of(query);
1009-
* QueryResponse response = bigquery.query(request);
1069+
* String query = "SELECT distinct(corpus) FROM `bigquery-public-data.samples.shakespeare`";
1070+
* QueryJobConfiguration queryConfig = QueryJobConfiguration.of(query);
1071+
* QueryResponse response = bigquery.query(queryConfig);
10101072
* // Wait for things to finish
10111073
* while (!response.jobCompleted()) {
10121074
* Thread.sleep(1000);
@@ -1016,9 +1078,8 @@ Page<List<FieldValue>> listTableData(String datasetId, String tableId,
10161078
* // handle errors
10171079
* }
10181080
* QueryResult result = response.getResult();
1019-
* Iterator<List<FieldValue>> rowIterator = result.iterateAll();
1020-
* while (rowIterator.hasNext()) {
1021-
* List<FieldValue> row = rowIterator.next();
1081+
* Iterator<FieldValueList> rowIterator = result.iterateAll();
1082+
* for (FieldValueList row : result.iterateAll()) {
10221083
* // do something with the data
10231084
* }
10241085
* }</pre>

‎google-cloud-bigquery/src/main/java/com/google/cloud/bigquery/BigQueryException.java‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -23,6 +23,7 @@
2323
import java.io.IOException;
2424
import java.util.Objects;
2525
import java.util.Set;
26+
import java.util.concurrent.ExecutionException;
2627

2728
/**
2829
* BigQuery service exception.
@@ -101,4 +102,9 @@ static BaseServiceException translateAndThrow(RetryHelperException ex) {
101102
BaseServiceException.translate(ex);
102103
throw new BigQueryException(UNKNOWN_CODE, ex.getMessage(), ex.getCause());
103104
}
105+
106+
static BaseServiceException translateAndThrow(ExecutionException ex) {
107+
BaseServiceException.translate(ex);
108+
throw new BigQueryException(UNKNOWN_CODE, ex.getMessage(), ex.getCause());
109+
}
104110
}

0 commit comments

Comments
 (0)