Skip to content

Commit 239c456

Browse files
mikekappongad
mikekap
authored and
pongad
committed
Add support for Google Sheets external tables in BigQuery (#3160)
1 parent e5a28f4 commit 239c456

5 files changed

Lines changed: 209 additions & 0 deletions

File tree

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

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -236,6 +236,9 @@ com.google.api.services.bigquery.model.ExternalDataConfiguration toExternalDataC
236236
if (getFormatOptions() != null && FormatOptions.CSV.equals(getFormatOptions().getType())) {
237237
externalConfigurationPb.setCsvOptions(((CsvOptions) getFormatOptions()).toPb());
238238
}
239+
if (getFormatOptions() != null && FormatOptions.GOOGLE_SHEETS.equals(getFormatOptions().getType())) {
240+
externalConfigurationPb.setGoogleSheetsOptions(((GoogleSheetsOptions) getFormatOptions()).toPb());
241+
}
239242
if (getAutodetect() != null) {
240243
externalConfigurationPb.setAutodetect(getAutodetect());
241244
}
@@ -340,6 +343,9 @@ static ExternalTableDefinition fromPb(Table tablePb) {
340343
if (externalDataConfiguration.getCsvOptions() != null) {
341344
builder.setFormatOptions(CsvOptions.fromPb(externalDataConfiguration.getCsvOptions()));
342345
}
346+
if (externalDataConfiguration.getGoogleSheetsOptions() != null) {
347+
builder.setFormatOptions(GoogleSheetsOptions.fromPb(externalDataConfiguration.getGoogleSheetsOptions()));
348+
}
343349
builder.setMaxBadRecords(externalDataConfiguration.getMaxBadRecords());
344350
builder.setAutodetect(externalDataConfiguration.getAutodetect());
345351
}
@@ -367,6 +373,9 @@ static ExternalTableDefinition fromExternalDataConfiguration(
367373
if (externalDataConfiguration.getCsvOptions() != null) {
368374
builder.setFormatOptions(CsvOptions.fromPb(externalDataConfiguration.getCsvOptions()));
369375
}
376+
if (externalDataConfiguration.getGoogleSheetsOptions() != null) {
377+
builder.setFormatOptions(GoogleSheetsOptions.fromPb(externalDataConfiguration.getGoogleSheetsOptions()));
378+
}
370379
if (externalDataConfiguration.getMaxBadRecords() != null) {
371380
builder.setMaxBadRecords(externalDataConfiguration.getMaxBadRecords());
372381
}

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

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -33,6 +33,7 @@ public class FormatOptions implements Serializable {
3333
static final String JSON = "NEWLINE_DELIMITED_JSON";
3434
static final String DATASTORE_BACKUP = "DATASTORE_BACKUP";
3535
static final String AVRO = "AVRO";
36+
static final String GOOGLE_SHEETS = "GOOGLE_SHEETS";
3637
private static final long serialVersionUID = -443376052020423691L;
3738

3839
private final String type;
@@ -95,6 +96,13 @@ public static FormatOptions avro() {
9596
return new FormatOptions(AVRO);
9697
}
9798

99+
/**
100+
* Default options for GOOGLE_SHEETS format.
101+
*/
102+
public static FormatOptions googleSheets() {
103+
return GoogleSheetsOptions.newBuilder().build();
104+
}
105+
98106
/**
99107
* Default options for the provided format.
100108
*/
@@ -103,6 +111,8 @@ public static FormatOptions of(String format) {
103111
return csv();
104112
} else if (format.equals(DATASTORE_BACKUP)) {
105113
return datastoreBackup();
114+
} else if (format.equals(GOOGLE_SHEETS)) {
115+
return googleSheets();
106116
}
107117
return new FormatOptions(format);
108118
}

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

Lines changed: 123 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,123 @@
1+
/*
2+
* Copyright 2018 Google LLC
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.google.cloud.bigquery;
18+
19+
import com.google.common.base.MoreObjects;
20+
21+
import java.util.Objects;
22+
23+
/**
24+
* Google BigQuery options for the Google Sheets format.
25+
*/
26+
public final class GoogleSheetsOptions extends FormatOptions {
27+
28+
private static final long serialVersionUID = 1837436979033106123L;
29+
30+
private final Long skipLeadingRows;
31+
32+
public static final class Builder {
33+
34+
private Long skipLeadingRows;
35+
36+
private Builder() {}
37+
38+
private Builder(GoogleSheetsOptions options) {
39+
this.skipLeadingRows = options.skipLeadingRows;
40+
}
41+
42+
/**
43+
* Sets the number of rows at the top of a sheet that BigQuery will skip when reading the
44+
* data. The default value is 0. This property is useful if you have header rows
45+
* that should be skipped.
46+
*/
47+
public Builder setSkipLeadingRows(long skipLeadingRows) {
48+
this.skipLeadingRows = skipLeadingRows;
49+
return this;
50+
}
51+
52+
/**
53+
* Creates a {@link GoogleSheetsOptions} object.
54+
*/
55+
public GoogleSheetsOptions build() {
56+
return new GoogleSheetsOptions(this);
57+
}
58+
}
59+
60+
private GoogleSheetsOptions(Builder builder) {
61+
super(FormatOptions.GOOGLE_SHEETS);
62+
this.skipLeadingRows = builder.skipLeadingRows;
63+
}
64+
65+
66+
/**
67+
* Returns the number of rows at the top of a sheet that BigQuery will skip when reading the
68+
* data.
69+
*/
70+
public Long getSkipLeadingRows() {
71+
return skipLeadingRows;
72+
}
73+
74+
/**
75+
* Returns a builder for the {@link GoogleSheetsOptions} object.
76+
*/
77+
public Builder toBuilder() {
78+
return new Builder(this);
79+
}
80+
81+
@Override
82+
public String toString() {
83+
return MoreObjects.toStringHelper(this)
84+
.add("type", getType())
85+
.add("skipLeadingRows", skipLeadingRows)
86+
.toString();
87+
}
88+
89+
@Override
90+
public int hashCode() {
91+
return Objects.hash(getType(), skipLeadingRows);
92+
}
93+
94+
@Override
95+
public boolean equals(Object obj) {
96+
return obj == this
97+
|| obj instanceof GoogleSheetsOptions
98+
&& Objects.equals(toPb(), ((GoogleSheetsOptions) obj).toPb());
99+
}
100+
101+
com.google.api.services.bigquery.model.GoogleSheetsOptions toPb() {
102+
com.google.api.services.bigquery.model.GoogleSheetsOptions options =
103+
new com.google.api.services.bigquery.model.GoogleSheetsOptions();
104+
options.setSkipLeadingRows(skipLeadingRows);
105+
return options;
106+
}
107+
108+
109+
/**
110+
* Returns a builder for a {@link GoogleSheetsOptions} object.
111+
*/
112+
public static Builder newBuilder() {
113+
return new Builder();
114+
}
115+
116+
static GoogleSheetsOptions fromPb(com.google.api.services.bigquery.model.GoogleSheetsOptions options) {
117+
Builder builder = newBuilder();
118+
if (options.getSkipLeadingRows() != null) {
119+
builder.setSkipLeadingRows(options.getSkipLeadingRows());
120+
}
121+
return builder.build();
122+
}
123+
}

‎google-cloud-bigquery/src/test/java/com/google/cloud/bigquery/FormatOptionsTest.java‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -41,6 +41,7 @@ public void testFactoryMethods() {
4141
assertEquals(FormatOptions.JSON, FormatOptions.json().getType());
4242
assertEquals(FormatOptions.DATASTORE_BACKUP, FormatOptions.datastoreBackup().getType());
4343
assertEquals(FormatOptions.AVRO, FormatOptions.avro().getType());
44+
assertEquals(FormatOptions.GOOGLE_SHEETS, FormatOptions.googleSheets().getType());
4445
}
4546

4647
@Test
@@ -52,5 +53,6 @@ public void testEquals() {
5253
assertEquals(FormatOptions.datastoreBackup(), FormatOptions.datastoreBackup());
5354
assertEquals(FormatOptions.datastoreBackup().hashCode(),
5455
FormatOptions.datastoreBackup().hashCode());
56+
assertEquals(FormatOptions.googleSheets(), FormatOptions.googleSheets());
5557
}
5658
}

‎google-cloud-bigquery/src/test/java/com/google/cloud/bigquery/GoogleSheetsOptionsTest.java‎

Lines changed: 65 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,65 @@
1+
/*
2+
* Copyright 2018 Google LLC
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.google.cloud.bigquery;
18+
19+
import org.junit.Test;
20+
21+
import static com.google.common.truth.Truth.assertThat;
22+
23+
public class GoogleSheetsOptionsTest {
24+
25+
private static final long SKIP_LEADING_ROWS = 42L;
26+
private static final GoogleSheetsOptions GOOGLE_SHEETS_OPTIONS = GoogleSheetsOptions.newBuilder()
27+
.setSkipLeadingRows(SKIP_LEADING_ROWS)
28+
.build();
29+
30+
@Test
31+
public void testToBuilder() {
32+
compareGoogleSheetsOptions(GOOGLE_SHEETS_OPTIONS, GOOGLE_SHEETS_OPTIONS.toBuilder().build());
33+
GoogleSheetsOptions googleSheetsOptions = GOOGLE_SHEETS_OPTIONS.toBuilder()
34+
.setSkipLeadingRows(123)
35+
.build();
36+
assertThat(googleSheetsOptions.getSkipLeadingRows()).isEqualTo(123);
37+
googleSheetsOptions = googleSheetsOptions.toBuilder().setSkipLeadingRows(SKIP_LEADING_ROWS).build();
38+
compareGoogleSheetsOptions(GOOGLE_SHEETS_OPTIONS, googleSheetsOptions);
39+
}
40+
41+
@Test
42+
public void testToBuilderIncomplete() {
43+
GoogleSheetsOptions googleSheetsOptions = GoogleSheetsOptions.newBuilder().build();
44+
assertThat(googleSheetsOptions.toBuilder().build()).isEqualTo(googleSheetsOptions);
45+
}
46+
47+
@Test
48+
public void testBuilder() {
49+
assertThat(GOOGLE_SHEETS_OPTIONS.getType()).isEqualTo(FormatOptions.GOOGLE_SHEETS);
50+
assertThat(GOOGLE_SHEETS_OPTIONS.getSkipLeadingRows()).isEqualTo(SKIP_LEADING_ROWS);
51+
}
52+
53+
54+
@Test
55+
public void testToAndFromPb() {
56+
compareGoogleSheetsOptions(GOOGLE_SHEETS_OPTIONS, GoogleSheetsOptions.fromPb(GOOGLE_SHEETS_OPTIONS.toPb()));
57+
GoogleSheetsOptions googleSheetsOptions = GoogleSheetsOptions.newBuilder().build();
58+
compareGoogleSheetsOptions(googleSheetsOptions, GoogleSheetsOptions.fromPb(googleSheetsOptions.toPb()));
59+
}
60+
61+
private void compareGoogleSheetsOptions(GoogleSheetsOptions expected, GoogleSheetsOptions value) {
62+
assertThat(value).isEqualTo(expected);
63+
assertThat(value.getSkipLeadingRows()).isEqualTo(expected.getSkipLeadingRows());
64+
}
65+
}

0 commit comments

Comments
 (0)