Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 3554dad

Browse filesBrowse files
tswasttswast
committed
BigQuery: named parameter query samples
- query using an array value as a parameter. - query using timestamps in named parameters. See: https://cloud.google.com/bigquery/querying-data#using_timestamps_in_parameterized_queries - query using a struct in query parameters.
1 parent 3a5273c commit 3554dad
Copy full SHA for 3554dad

File tree

Expand file treeCollapse file tree

3 files changed

+136
-31
lines changed
Filter options
Expand file treeCollapse file tree

3 files changed

+136
-31
lines changed

‎bigquery/cloud-client/requirements.txt

Copy file name to clipboard
+1Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1 +1,2 @@
11
google-cloud-bigquery==0.22.1
2+
pytz==2016.10

‎bigquery/cloud-client/sync_query_params.py

Copy file name to clipboardExpand all lines: bigquery/cloud-client/sync_query_params.py
+118-27Lines changed: 118 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -24,8 +24,10 @@
2424
"""
2525

2626
import argparse
27+
import datetime
2728

2829
from google.cloud import bigquery
30+
import pytz
2931

3032

3133
def print_results(query_results):
@@ -46,13 +48,14 @@ def print_results(query_results):
4648

4749
def sync_query_positional_params(corpus, min_word_count):
4850
client = bigquery.Client()
51+
query = """SELECT word, word_count
52+
FROM `bigquery-public-data.samples.shakespeare`
53+
WHERE corpus = ?
54+
AND word_count >= ?
55+
ORDER BY word_count DESC;
56+
"""
4957
query_results = client.run_sync_query(
50-
"""SELECT word, word_count
51-
FROM `bigquery-public-data.samples.shakespeare`
52-
WHERE corpus = ?
53-
AND word_count >= ?
54-
ORDER BY word_count DESC;
55-
""",
58+
query,
5659
query_parameters=(
5760
bigquery.ScalarQueryParameter(
5861
# Set the name to None to use positional parameters (? symbol
@@ -72,13 +75,14 @@ def sync_query_positional_params(corpus, min_word_count):
7275

7376
def sync_query_named_params(corpus, min_word_count):
7477
client = bigquery.Client()
78+
query = """SELECT word, word_count
79+
FROM `bigquery-public-data.samples.shakespeare`
80+
WHERE corpus = @corpus
81+
AND word_count >= @min_word_count
82+
ORDER BY word_count DESC;
83+
"""
7584
query_results = client.run_sync_query(
76-
"""SELECT word, word_count
77-
FROM `bigquery-public-data.samples.shakespeare`
78-
WHERE corpus = @corpus
79-
AND word_count >= @min_word_count
80-
ORDER BY word_count DESC;
81-
""",
85+
query,
8286
query_parameters=(
8387
bigquery.ScalarQueryParameter('corpus', 'STRING', corpus),
8488
bigquery.ScalarQueryParameter(
@@ -90,31 +94,118 @@ def sync_query_named_params(corpus, min_word_count):
9094
print_results(query_results)
9195

9296

97+
def sync_query_array_params(gender, states):
98+
client = bigquery.Client()
99+
query = """SELECT name, sum(number) as count
100+
FROM `bigquery-public-data.usa_names.usa_1910_2013`
101+
WHERE gender = @gender
102+
AND state IN UNNEST(@states)
103+
GROUP BY name
104+
ORDER BY count DESC
105+
LIMIT 10;
106+
"""
107+
query_results = client.run_sync_query(
108+
query,
109+
query_parameters=(
110+
bigquery.ScalarQueryParameter('gender', 'STRING', gender),
111+
bigquery.ArrayQueryParameter('states', 'STRING', states)))
112+
query_results.use_legacy_sql = False
113+
query_results.run()
114+
print_results(query_results)
115+
116+
117+
def sync_query_timestamp_params(year, month, day, hour, minute):
118+
client = bigquery.Client()
119+
query = 'SELECT TIMESTAMP_ADD(@ts_value, INTERVAL 1 HOUR);'
120+
query_results = client.run_sync_query(
121+
query,
122+
query_parameters=[
123+
bigquery.ScalarQueryParameter(
124+
'ts_value',
125+
'TIMESTAMP',
126+
datetime.datetime(
127+
year, month, day, hour, minute, tzinfo=pytz.UTC))])
128+
query_results.use_legacy_sql = False
129+
query_results.run()
130+
print_results(query_results)
131+
132+
133+
def sync_query_struct_params(x, y):
134+
client = bigquery.Client()
135+
query = 'SELECT @struct_value AS s;'
136+
query_results = client.run_sync_query(
137+
query,
138+
query_parameters=[
139+
bigquery.StructQueryParameter(
140+
'struct_value',
141+
bigquery.ScalarQueryParameter('x', 'INT64', x),
142+
bigquery.ScalarQueryParameter('y', 'STRING', y))])
143+
query_results.use_legacy_sql = False
144+
query_results.run()
145+
print_results(query_results)
146+
147+
93148
if __name__ == '__main__':
94149
parser = argparse.ArgumentParser(
95150
description=__doc__,
96151
formatter_class=argparse.RawDescriptionHelpFormatter)
97-
parser.add_argument(
152+
subparsers = parser.add_subparsers(dest='sample', help='samples')
153+
named_parser = subparsers.add_parser(
154+
'named',
155+
help='Run a query with named parameters.')
156+
named_parser.add_argument(
98157
'corpus',
99158
help='Corpus to search from Shakespeare dataset.')
100-
parser.add_argument(
159+
named_parser.add_argument(
101160
'min_word_count',
102161
help='Minimum count of words to query.',
103162
type=int)
104-
105-
params_type_parser = parser.add_mutually_exclusive_group(required=False)
106-
params_type_parser.add_argument(
107-
'--use-named-params',
108-
dest='use_named_params',
109-
action='store_true')
110-
params_type_parser.add_argument(
111-
'--use-positional-params',
112-
dest='use_named_params',
113-
action='store_false')
114-
parser.set_defaults(use_named_params=False)
163+
positional_parser = subparsers.add_parser(
164+
'positional',
165+
help='Run a query with positional parameters.')
166+
positional_parser.add_argument(
167+
'corpus',
168+
help='Corpus to search from Shakespeare dataset.')
169+
positional_parser.add_argument(
170+
'min_word_count',
171+
help='Minimum count of words to query.',
172+
type=int)
173+
array_parser = subparsers.add_parser(
174+
'array',
175+
help='Run a query with an array parameter.')
176+
array_parser.add_argument(
177+
'gender',
178+
choices=['F', 'M'],
179+
help='Gender of baby in the Social Security baby names database.')
180+
array_parser.add_argument(
181+
'states',
182+
help='U.S. States to consider for popular baby names.',
183+
nargs='+')
184+
timestamp_parser = subparsers.add_parser(
185+
'timestamp',
186+
help='Run a query with a timestamp parameter.')
187+
timestamp_parser.add_argument('year', type=int)
188+
timestamp_parser.add_argument('month', type=int)
189+
timestamp_parser.add_argument('day', type=int)
190+
timestamp_parser.add_argument('hour', type=int)
191+
timestamp_parser.add_argument('minute', type=int)
192+
struct_parser = subparsers.add_parser(
193+
'struct',
194+
help='Run a query with a struct parameter.')
195+
struct_parser.add_argument('x', help='Integer for x', type=int)
196+
struct_parser.add_argument('y', help='String for y')
115197
args = parser.parse_args()
116198

117-
if args.use_named_params:
199+
if args.sample == 'named':
118200
sync_query_named_params(args.corpus, args.min_word_count)
119-
else:
201+
elif args.sample == 'positional':
120202
sync_query_positional_params(args.corpus, args.min_word_count)
203+
elif args.sample == 'array':
204+
sync_query_array_params(args.gender, args.states)
205+
elif args.sample == 'timestamp':
206+
sync_query_timestamp_params(
207+
args.year, args.month, args.day, args.hour, args.minute)
208+
elif args.sample == 'struct':
209+
sync_query_struct_params(args.x, args.y)
210+
else:
211+
print('Unexpected value for sample')

‎bigquery/cloud-client/sync_query_params_test.py

Copy file name to clipboardExpand all lines: bigquery/cloud-client/sync_query_params_test.py
+17-4Lines changed: 17 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -16,11 +16,11 @@
1616

1717

1818
def test_sync_query_named_params(capsys):
19-
sync_query_params.sync_query_named_params(
20-
corpus='romeoandjuliet',
21-
min_word_count=100)
19+
sync_query_params.sync_query_array_params(
20+
gender='M',
21+
states=['WA', 'WI', 'WV', 'WY'])
2222
out, _ = capsys.readouterr()
23-
assert 'love' in out
23+
assert 'James' in out
2424

2525

2626
def test_sync_query_positional_params(capsys):
@@ -29,3 +29,16 @@ def test_sync_query_positional_params(capsys):
2929
min_word_count=100)
3030
out, _ = capsys.readouterr()
3131
assert 'love' in out
32+
33+
34+
def test_sync_query_struct_params(capsys):
35+
sync_query_params.sync_query_struct_params(765, "hello world")
36+
out, _ = capsys.readouterr()
37+
assert '765' in out
38+
assert 'hello world' in out
39+
40+
41+
def test_sync_query_timestamp_params(capsys):
42+
sync_query_params.sync_query_timestamp_params(2016, 12, 7, 8, 0)
43+
out, _ = capsys.readouterr()
44+
assert '2016-12-07 09:00:00' in out

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.