Skip to content

Commit d573a4d

Browse files
tswasttswast
authored
Merge pull request GoogleCloudPlatform#464 from GoogleCloudPlatform/tswast-bq
BigQuery: add samples for query parameters.
2 parents ae73f1e + 682d765 commit d573a4d

File tree

3 files changed

+362
-0
lines changed

3 files changed

+362
-0
lines changed

‎bigquery/cloud-client/pom.xml

+5
Original file line numberDiff line numberDiff line change
@@ -39,6 +39,11 @@
3939
<artifactId>google-cloud-bigquery</artifactId>
4040
<version>0.8.0-beta</version>
4141
</dependency>
42+
<dependency>
43+
<groupId>joda-time</groupId>
44+
<artifactId>joda-time</artifactId>
45+
<version>2.9.7</version>
46+
</dependency>
4247

4348
<!-- Test dependencies -->
4449
<dependency>

‎bigquery/cloud-client/src/main/java/com/example/bigquery/QueryParametersSample.java

+286
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,286 @@
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.QueryParameterValue;
23+
import com.google.cloud.bigquery.QueryRequest;
24+
import com.google.cloud.bigquery.QueryResponse;
25+
import com.google.cloud.bigquery.QueryResult;
26+
import org.joda.time.DateTime;
27+
import org.joda.time.DateTimeZone;
28+
import org.joda.time.format.DateTimeFormatter;
29+
import org.joda.time.format.ISODateTimeFormat;
30+
31+
import java.io.IOException;
32+
import java.util.Arrays;
33+
import java.util.Iterator;
34+
import java.util.List;
35+
import java.util.stream.Collectors;
36+
37+
/**
38+
* A sample that demonstrates use of query parameters.
39+
*/
40+
public class QueryParametersSample {
41+
private static final int ERROR_CODE = 1;
42+
43+
private static void printUsage() {
44+
System.err.println("Usage:");
45+
System.err.printf(
46+
"\tmvn exec:java -Dexec.mainClass=%s -Dexec.args='%s'\n",
47+
QueryParametersSample.class.getCanonicalName(),
48+
"${sample}");
49+
System.err.println();
50+
System.err.println("${sample} can be one of: named, array, timestamp");
51+
System.err.println();
52+
System.err.println("Usage for ${sample}=named:");
53+
System.err.printf(
54+
"\tmvn exec:java -Dexec.mainClass=%s -Dexec.args='%s'\n",
55+
QueryParametersSample.class.getCanonicalName(),
56+
"named ${corpus} ${minWordCount}");
57+
System.err.println();
58+
System.err.println("Usage for sample=array:");
59+
System.err.printf(
60+
"\tmvn exec:java -Dexec.mainClass=%s -Dexec.args='%s'\n",
61+
QueryParametersSample.class.getCanonicalName(),
62+
"array ${gender} ${states...}");
63+
System.err.println();
64+
System.err.println("\twhere ${gender} can be on of: M, F");
65+
System.err.println(
66+
"\tand ${states} is any upper-case 2-letter code for U.S. a state, e.g. CA.");
67+
System.err.println();
68+
System.err.printf(
69+
"\t\tmvn exec:java -Dexec.mainClass=%s -Dexec.args='%s'\n",
70+
QueryParametersSample.class.getCanonicalName(),
71+
"array F MD WA");
72+
}
73+
74+
/**
75+
* Prompts the user for the required parameters to perform a query.
76+
*/
77+
public static void main(final String[] args) throws IOException, InterruptedException {
78+
if (args.length < 1) {
79+
System.err.println("Expected first argument 'sample'");
80+
printUsage();
81+
System.exit(ERROR_CODE);
82+
}
83+
String sample = args[0];
84+
85+
switch (sample) {
86+
case "named":
87+
if (args.length != 3) {
88+
System.err.println("Unexpected number of arguments for named query sample.");
89+
printUsage();
90+
System.exit(ERROR_CODE);
91+
}
92+
runNamed(args[1], Long.parseLong(args[2]));
93+
break;
94+
case "array":
95+
if (args.length < 2) {
96+
System.err.println("Unexpected number of arguments for array query sample.");
97+
printUsage();
98+
System.exit(ERROR_CODE);
99+
}
100+
String gender = args[1];
101+
String[] states = Arrays.copyOfRange(args, 2, args.length);
102+
runArray(gender, states);
103+
break;
104+
case "timestamp":
105+
if (args.length != 1) {
106+
System.err.println("Unexpected number of arguments for timestamp query sample.");
107+
printUsage();
108+
System.exit(ERROR_CODE);
109+
}
110+
runTimestamp();
111+
break;
112+
default:
113+
System.err.println("Got bad value for sample");
114+
printUsage();
115+
System.exit(ERROR_CODE);
116+
}
117+
}
118+
119+
/**
120+
* Query the Shakespeare dataset for words with count at least {@code minWordCount} in the corpus
121+
* {@code corpus}.
122+
*/
123+
// [START bigquery_query_params]
124+
private static void runNamed(final String corpus, final long minWordCount)
125+
throws InterruptedException {
126+
BigQuery bigquery =
127+
new BigQueryOptions.DefaultBigqueryFactory().create(BigQueryOptions.getDefaultInstance());
128+
129+
String queryString = "SELECT word, word_count\n"
130+
+ "FROM `bigquery-public-data.samples.shakespeare`\n"
131+
+ "WHERE corpus = @corpus\n"
132+
+ "AND word_count >= @min_word_count\n"
133+
+ "ORDER BY word_count DESC";
134+
QueryRequest queryRequest =
135+
QueryRequest.newBuilder(queryString)
136+
.addNamedParameter("corpus", QueryParameterValue.string(corpus))
137+
.addNamedParameter("min_word_count", QueryParameterValue.int64(minWordCount))
138+
// Standard SQL syntax is required for parameterized queries.
139+
// See: https://cloud.google.com/bigquery/sql-reference/
140+
.setUseLegacySql(false)
141+
.build();
142+
143+
// Execute the query.
144+
QueryResponse response = bigquery.query(queryRequest);
145+
146+
// Wait for the job to finish (if the query takes more than 10 seconds to complete).
147+
while (!response.jobCompleted()) {
148+
Thread.sleep(1000);
149+
response = bigquery.getQueryResults(response.getJobId());
150+
}
151+
152+
if (response.hasErrors()) {
153+
throw new RuntimeException(
154+
response
155+
.getExecutionErrors()
156+
.stream()
157+
.<String>map(err -> err.getMessage())
158+
.collect(Collectors.joining("\n")));
159+
}
160+
161+
QueryResult result = response.getResult();
162+
Iterator<List<FieldValue>> iter = result.iterateAll();
163+
164+
while (iter.hasNext()) {
165+
List<FieldValue> row = iter.next();
166+
System.out.printf(
167+
"%s: %d\n",
168+
row.get(0).getStringValue(),
169+
row.get(1).getLongValue());
170+
}
171+
}
172+
// [END bigquery_query_params]
173+
174+
/**
175+
* Query the baby names database to find the most popular names for a gender in a list of states.
176+
*/
177+
// [START bigquery_query_params_arrays]
178+
private static void runArray(String gender, String[] states)
179+
throws InterruptedException {
180+
BigQuery bigquery =
181+
new BigQueryOptions.DefaultBigqueryFactory().create(BigQueryOptions.getDefaultInstance());
182+
183+
String queryString = "SELECT name, sum(number) as count\n"
184+
+ "FROM `bigquery-public-data.usa_names.usa_1910_2013`\n"
185+
+ "WHERE gender = @gender\n"
186+
+ "AND state IN UNNEST(@states)\n"
187+
+ "GROUP BY name\n"
188+
+ "ORDER BY count DESC\n"
189+
+ "LIMIT 10;";
190+
QueryRequest queryRequest =
191+
QueryRequest.newBuilder(queryString)
192+
.addNamedParameter("gender", QueryParameterValue.string(gender))
193+
.addNamedParameter(
194+
"states",
195+
QueryParameterValue.array(states, String.class))
196+
// Standard SQL syntax is required for parameterized queries.
197+
// See: https://cloud.google.com/bigquery/sql-reference/
198+
.setUseLegacySql(false)
199+
.build();
200+
201+
// Execute the query.
202+
QueryResponse response = bigquery.query(queryRequest);
203+
204+
// Wait for the job to finish (if the query takes more than 10 seconds to complete).
205+
while (!response.jobCompleted()) {
206+
Thread.sleep(1000);
207+
response = bigquery.getQueryResults(response.getJobId());
208+
}
209+
210+
if (response.hasErrors()) {
211+
throw new RuntimeException(
212+
response
213+
.getExecutionErrors()
214+
.stream()
215+
.<String>map(err -> err.getMessage())
216+
.collect(Collectors.joining("\n")));
217+
}
218+
219+
QueryResult result = response.getResult();
220+
Iterator<List<FieldValue>> iter = result.iterateAll();
221+
222+
while (iter.hasNext()) {
223+
List<FieldValue> row = iter.next();
224+
System.out.printf("%s: %d\n", row.get(0).getStringValue(), row.get(1).getLongValue());
225+
}
226+
}
227+
// [END bigquery_query_params_arrays]
228+
229+
// [START bigquery_query_params_timestamps]
230+
private static void runTimestamp() throws InterruptedException {
231+
BigQuery bigquery =
232+
new BigQueryOptions.DefaultBigqueryFactory().create(BigQueryOptions.getDefaultInstance());
233+
234+
// Timestamps are expected to be in the format YYYY-MM-DD HH:MM:SS.DDDDDD time_zone
235+
// See: https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#timestamp-type
236+
DateTime timestamp = new DateTime(2016, 12, 7, 8, 0, 0, DateTimeZone.UTC);
237+
238+
String queryString = "SELECT TIMESTAMP_ADD(@ts_value, INTERVAL 1 HOUR);";
239+
QueryRequest queryRequest =
240+
QueryRequest.newBuilder(queryString)
241+
.addNamedParameter(
242+
"ts_value",
243+
QueryParameterValue.timestamp(
244+
// Timestamp takes microseconds since 1970-01-01T00:00:00 UTC
245+
timestamp.getMillis() * 1000))
246+
// Standard SQL syntax is required for parameterized queries.
247+
// See: https://cloud.google.com/bigquery/sql-reference/
248+
.setUseLegacySql(false)
249+
.build();
250+
251+
// Execute the query.
252+
QueryResponse response = bigquery.query(queryRequest);
253+
254+
// Wait for the job to finish (if the query takes more than 10 seconds to complete).
255+
while (!response.jobCompleted()) {
256+
Thread.sleep(1000);
257+
response = bigquery.getQueryResults(response.getJobId());
258+
}
259+
260+
if (response.hasErrors()) {
261+
throw new RuntimeException(
262+
response
263+
.getExecutionErrors()
264+
.stream()
265+
.<String>map(err -> err.getMessage())
266+
.collect(Collectors.joining("\n")));
267+
}
268+
269+
QueryResult result = response.getResult();
270+
Iterator<List<FieldValue>> iter = result.iterateAll();
271+
272+
DateTimeFormatter formatter = ISODateTimeFormat.dateTimeNoMillis().withZoneUTC();
273+
while (iter.hasNext()) {
274+
List<FieldValue> row = iter.next();
275+
System.out.printf(
276+
"%s\n",
277+
formatter.print(
278+
new DateTime(
279+
// Timestamp values are returned in microseconds since 1970-01-01T00:00:00 UTC,
280+
// but org.joda.time.DateTime constructor accepts times in milliseconds.
281+
row.get(0).getTimestampValue() / 1000,
282+
DateTimeZone.UTC)));
283+
}
284+
}
285+
// [END bigquery_query_params_timestamps]
286+
}

‎bigquery/cloud-client/src/test/java/com/example/bigquery/QueryParametersSampleIT.java

+71
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,71 @@
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 static com.google.common.truth.Truth.assertThat;
20+
21+
import org.junit.After;
22+
import org.junit.Before;
23+
import org.junit.Test;
24+
import org.junit.runner.RunWith;
25+
import org.junit.runners.JUnit4;
26+
27+
import java.io.ByteArrayOutputStream;
28+
import java.io.PrintStream;
29+
30+
/**
31+
* Tests for simple app sample.
32+
*/
33+
@RunWith(JUnit4.class)
34+
@SuppressWarnings("checkstyle:abbreviationaswordinname")
35+
public class QueryParametersSampleIT {
36+
private ByteArrayOutputStream bout;
37+
private PrintStream out;
38+
39+
@Before
40+
public void setUp() {
41+
bout = new ByteArrayOutputStream();
42+
out = new PrintStream(bout);
43+
System.setOut(out);
44+
}
45+
46+
@After
47+
public void tearDown() {
48+
System.setOut(null);
49+
}
50+
51+
@Test
52+
public void testNamedSample() throws Exception {
53+
QueryParametersSample.main(new String[]{"named", "romeoandjuliet", "100"});
54+
String got = bout.toString();
55+
assertThat(got).contains("love");
56+
}
57+
58+
@Test
59+
public void testArraySample() throws Exception {
60+
QueryParametersSample.main(new String[]{"array", "M", "WA", "WI", "WV", "WY"});
61+
String got = bout.toString();
62+
assertThat(got).contains("James");
63+
}
64+
65+
@Test
66+
public void testTimestampSample() throws Exception {
67+
QueryParametersSample.main(new String[]{"timestamp"});
68+
String got = bout.toString();
69+
assertThat(got).contains("2016-12-07T09:00:00Z");
70+
}
71+
}

0 commit comments

Comments
 (0)