Skip to content

Commit 63d443b

Browse files
tswasttswast
committed
BigQuery: add samples for query parameters.
Samples for: - Named parameters with scalar values. - Named parameters with array values. - Named parameters with timestamp values.
1 parent ae73f1e commit 63d443b

File tree

3 files changed

+351
-0
lines changed

3 files changed

+351
-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

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

‎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)