0
# Query Parameters
1
2
Type-safe parameter binding for SQL queries supporting scalar, array, struct, and range parameter types with proper type validation. Parameters enable secure and efficient query execution while preventing SQL injection attacks.
3
4
## Capabilities
5
6
### Scalar Parameters
7
8
Single-value parameters for basic data types in SQL queries.
9
10
```python { .api }
11
class ScalarQueryParameter:
12
def __init__(self, name: str, type_: str, value: Any):
13
"""
14
Scalar query parameter for single values.
15
16
Args:
17
name: Parameter name (without @ prefix).
18
type_: BigQuery data type (STRING, INT64, FLOAT64, etc.).
19
value: Parameter value.
20
"""
21
22
@property
23
def name(self) -> str:
24
"""Parameter name."""
25
26
@property
27
def type_(self) -> str:
28
"""Parameter data type."""
29
30
@property
31
def value(self) -> Any:
32
"""Parameter value."""
33
34
class ScalarQueryParameterType:
35
def __init__(self, type_: str):
36
"""
37
Type definition for scalar parameters.
38
39
Args:
40
type_: BigQuery data type.
41
"""
42
43
@property
44
def type_(self) -> str:
45
"""Parameter data type."""
46
```
47
48
### Array Parameters
49
50
Array-type parameters for passing lists of values to SQL queries.
51
52
```python { .api }
53
class ArrayQueryParameter:
54
def __init__(self, name: str, array_type: str, values: List[Any]):
55
"""
56
Array query parameter for multiple values.
57
58
Args:
59
name: Parameter name (without @ prefix).
60
array_type: BigQuery data type for array elements.
61
values: List of parameter values.
62
"""
63
64
@property
65
def name(self) -> str:
66
"""Parameter name."""
67
68
@property
69
def array_type(self) -> str:
70
"""Array element data type."""
71
72
@property
73
def values(self) -> List[Any]:
74
"""Array values."""
75
76
class ArrayQueryParameterType:
77
def __init__(self, array_type: Union[str, ScalarQueryParameterType, StructQueryParameterType]):
78
"""
79
Type definition for array parameters.
80
81
Args:
82
array_type: Type of array elements.
83
"""
84
85
@property
86
def array_type(self) -> Union[str, ScalarQueryParameterType, StructQueryParameterType]:
87
"""Array element type."""
88
```
89
90
### Struct Parameters
91
92
Structured parameters for passing complex nested data to SQL queries.
93
94
```python { .api }
95
class StructQueryParameter:
96
def __init__(self, name: str, *sub_params: Union[ScalarQueryParameter, ArrayQueryParameter]):
97
"""
98
Struct query parameter for nested data.
99
100
Args:
101
name: Parameter name (without @ prefix).
102
*sub_params: Sub-parameters for struct fields.
103
"""
104
105
@property
106
def name(self) -> str:
107
"""Parameter name."""
108
109
@property
110
def struct_types(self) -> Dict[str, Any]:
111
"""Struct field types."""
112
113
@property
114
def struct_values(self) -> Dict[str, Any]:
115
"""Struct field values."""
116
117
class StructQueryParameterType:
118
def __init__(self, *sub_types: Union[ScalarQueryParameterType, ArrayQueryParameterType]):
119
"""
120
Type definition for struct parameters.
121
122
Args:
123
*sub_types: Types for struct fields.
124
"""
125
126
@property
127
def struct_types(self) -> List[Union[ScalarQueryParameterType, ArrayQueryParameterType]]:
128
"""Struct field types."""
129
```
130
131
### Range Parameters
132
133
Range parameters for passing range values (intervals) to SQL queries.
134
135
```python { .api }
136
class RangeQueryParameter:
137
def __init__(self, name: str, range_element_type: str, start: Any, end: Any):
138
"""
139
Range query parameter for interval values.
140
141
Args:
142
name: Parameter name (without @ prefix).
143
range_element_type: Type of range bounds (DATE, DATETIME, TIMESTAMP).
144
start: Range start value (inclusive).
145
end: Range end value (exclusive).
146
"""
147
148
@property
149
def name(self) -> str:
150
"""Parameter name."""
151
152
@property
153
def range_element_type(self) -> str:
154
"""Range element data type."""
155
156
@property
157
def start(self) -> Any:
158
"""Range start value (inclusive)."""
159
160
@property
161
def end(self) -> Any:
162
"""Range end value (exclusive)."""
163
164
class RangeQueryParameterType:
165
def __init__(self, element_type: str):
166
"""
167
Type definition for range parameters.
168
169
Args:
170
element_type: Type of range bounds.
171
"""
172
173
@property
174
def element_type(self) -> str:
175
"""Range element type."""
176
```
177
178
### Connection Properties
179
180
Connection-level properties for query execution context.
181
182
```python { .api }
183
class ConnectionProperty:
184
def __init__(self, key: str, value: str):
185
"""
186
Connection property for query context.
187
188
Args:
189
key: Property key.
190
value: Property value.
191
"""
192
193
@property
194
def key(self) -> str:
195
"""Property key."""
196
197
@property
198
def value(self) -> str:
199
"""Property value."""
200
```
201
202
### Parameter Type Constants
203
204
```python { .api }
205
class SqlParameterScalarTypes:
206
"""Constants for SQL parameter scalar types."""
207
208
BOOL: str = "BOOL"
209
INT64: str = "INT64"
210
FLOAT64: str = "FLOAT64"
211
NUMERIC: str = "NUMERIC"
212
BIGNUMERIC: str = "BIGNUMERIC"
213
STRING: str = "STRING"
214
BYTES: str = "BYTES"
215
DATE: str = "DATE"
216
DATETIME: str = "DATETIME"
217
TIME: str = "TIME"
218
TIMESTAMP: str = "TIMESTAMP"
219
GEOGRAPHY: str = "GEOGRAPHY"
220
JSON: str = "JSON"
221
```
222
223
## Usage Examples
224
225
### Basic Scalar Parameters
226
227
```python
228
from google.cloud import bigquery
229
from google.cloud.bigquery import ScalarQueryParameter
230
231
client = bigquery.Client()
232
233
# Query with scalar parameters
234
query = """
235
SELECT name, age, city
236
FROM `my_project.my_dataset.users`
237
WHERE age >= @min_age
238
AND city = @target_city
239
AND created_date >= @start_date
240
LIMIT @max_results
241
"""
242
243
job_config = bigquery.QueryJobConfig(
244
query_parameters=[
245
ScalarQueryParameter("min_age", "INT64", 25),
246
ScalarQueryParameter("target_city", "STRING", "San Francisco"),
247
ScalarQueryParameter("start_date", "DATE", "2023-01-01"),
248
ScalarQueryParameter("max_results", "INT64", 100),
249
]
250
)
251
252
query_job = client.query(query, job_config=job_config)
253
results = query_job.result()
254
255
for row in results:
256
print(f"{row.name}, {row.age}, {row.city}")
257
```
258
259
### Array Parameters
260
261
```python
262
# Query with array parameters
263
query = """
264
SELECT product_id, product_name, category, price
265
FROM `my_project.my_dataset.products`
266
WHERE category IN UNNEST(@categories)
267
AND product_id IN UNNEST(@product_ids)
268
ORDER BY price DESC
269
"""
270
271
job_config = bigquery.QueryJobConfig(
272
query_parameters=[
273
bigquery.ArrayQueryParameter(
274
"categories",
275
"STRING",
276
["electronics", "computers", "smartphones"]
277
),
278
bigquery.ArrayQueryParameter(
279
"product_ids",
280
"INT64",
281
[1001, 1002, 1003, 1004, 1005]
282
),
283
]
284
)
285
286
query_job = client.query(query, job_config=job_config)
287
results = query_job.result()
288
289
for row in results:
290
print(f"{row.product_name}: ${row.price}")
291
```
292
293
### Struct Parameters
294
295
```python
296
# Query with struct parameters
297
query = """
298
SELECT user_id, event_type, event_timestamp
299
FROM `my_project.my_dataset.events`
300
WHERE event_timestamp BETWEEN @date_range.start_date AND @date_range.end_date
301
AND user_id = @user_filter.user_id
302
AND event_type IN UNNEST(@user_filter.event_types)
303
"""
304
305
job_config = bigquery.QueryJobConfig(
306
query_parameters=[
307
bigquery.StructQueryParameter(
308
"date_range",
309
ScalarQueryParameter("start_date", "TIMESTAMP", "2023-01-01 00:00:00"),
310
ScalarQueryParameter("end_date", "TIMESTAMP", "2023-01-31 23:59:59"),
311
),
312
bigquery.StructQueryParameter(
313
"user_filter",
314
ScalarQueryParameter("user_id", "INT64", 12345),
315
bigquery.ArrayQueryParameter("event_types", "STRING", ["login", "purchase", "logout"]),
316
),
317
]
318
)
319
320
query_job = client.query(query, job_config=job_config)
321
results = query_job.result()
322
323
for row in results:
324
print(f"User {row.user_id}: {row.event_type} at {row.event_timestamp}")
325
```
326
327
### Range Parameters
328
329
```python
330
# Query with range parameters (for date/time ranges)
331
query = """
332
SELECT order_id, customer_id, order_date, total_amount
333
FROM `my_project.my_dataset.orders`
334
WHERE order_date IN UNNEST(GENERATE_DATE_ARRAY(@date_range.start, @date_range.end))
335
ORDER BY order_date DESC
336
"""
337
338
from datetime import date
339
340
job_config = bigquery.QueryJobConfig(
341
query_parameters=[
342
bigquery.RangeQueryParameter(
343
"date_range",
344
"DATE",
345
date(2023, 12, 1), # Start date (inclusive)
346
date(2023, 12, 31) # End date (exclusive)
347
),
348
]
349
)
350
351
query_job = client.query(query, job_config=job_config)
352
results = query_job.result()
353
354
for row in results:
355
print(f"Order {row.order_id}: ${row.total_amount} on {row.order_date}")
356
```
357
358
### Complex Nested Parameters
359
360
```python
361
# Advanced query with nested struct and array parameters
362
query = """
363
WITH filtered_events AS (
364
SELECT
365
user_id,
366
event_type,
367
event_timestamp,
368
properties
369
FROM `my_project.my_dataset.user_events`
370
WHERE user_id IN UNNEST(@filters.user_ids)
371
AND event_type IN UNNEST(@filters.event_types)
372
AND event_timestamp BETWEEN @filters.time_range.start_time AND @filters.time_range.end_time
373
)
374
SELECT
375
user_id,
376
COUNT(*) as event_count,
377
ARRAY_AGG(event_type ORDER BY event_timestamp) as event_sequence
378
FROM filtered_events
379
GROUP BY user_id
380
HAVING COUNT(*) >= @filters.min_events
381
ORDER BY event_count DESC
382
LIMIT @output.max_results
383
"""
384
385
job_config = bigquery.QueryJobConfig(
386
query_parameters=[
387
bigquery.StructQueryParameter(
388
"filters",
389
bigquery.ArrayQueryParameter("user_ids", "INT64", [100, 101, 102, 103]),
390
bigquery.ArrayQueryParameter("event_types", "STRING", ["page_view", "click", "purchase"]),
391
bigquery.StructQueryParameter(
392
"time_range",
393
ScalarQueryParameter("start_time", "TIMESTAMP", "2023-12-01 00:00:00"),
394
ScalarQueryParameter("end_time", "TIMESTAMP", "2023-12-31 23:59:59"),
395
),
396
ScalarQueryParameter("min_events", "INT64", 5),
397
),
398
bigquery.StructQueryParameter(
399
"output",
400
ScalarQueryParameter("max_results", "INT64", 50),
401
),
402
]
403
)
404
405
query_job = client.query(query, job_config=job_config)
406
results = query_job.result()
407
408
for row in results:
409
print(f"User {row.user_id}: {row.event_count} events")
410
print(f" Sequence: {', '.join(row.event_sequence)}")
411
```
412
413
### Dynamic Parameter Building
414
415
```python
416
def build_filter_query(user_ids=None, event_types=None, date_range=None, min_count=1):
417
"""Build parameterized query dynamically based on provided filters."""
418
419
conditions = []
420
parameters = []
421
422
# Base query
423
query_parts = [
424
"SELECT user_id, event_type, event_timestamp, properties",
425
"FROM `my_project.my_dataset.user_events`",
426
"WHERE 1=1" # Always true condition to simplify adding AND clauses
427
]
428
429
# Add user ID filter
430
if user_ids:
431
conditions.append("AND user_id IN UNNEST(@user_ids)")
432
parameters.append(
433
bigquery.ArrayQueryParameter("user_ids", "INT64", user_ids)
434
)
435
436
# Add event type filter
437
if event_types:
438
conditions.append("AND event_type IN UNNEST(@event_types)")
439
parameters.append(
440
bigquery.ArrayQueryParameter("event_types", "STRING", event_types)
441
)
442
443
# Add date range filter
444
if date_range:
445
conditions.append("AND event_timestamp BETWEEN @start_date AND @end_date")
446
parameters.extend([
447
ScalarQueryParameter("start_date", "TIMESTAMP", date_range[0]),
448
ScalarQueryParameter("end_date", "TIMESTAMP", date_range[1]),
449
])
450
451
# Add aggregation and filtering
452
query_parts.extend(conditions)
453
query_parts.extend([
454
"GROUP BY user_id, event_type, event_timestamp, properties",
455
"HAVING COUNT(*) >= @min_count",
456
"ORDER BY event_timestamp DESC"
457
])
458
459
parameters.append(ScalarQueryParameter("min_count", "INT64", min_count))
460
461
return " ".join(query_parts), parameters
462
463
# Use the dynamic query builder
464
query, params = build_filter_query(
465
user_ids=[100, 101, 102],
466
event_types=["login", "purchase"],
467
date_range=("2023-12-01 00:00:00", "2023-12-31 23:59:59"),
468
min_count=2
469
)
470
471
job_config = bigquery.QueryJobConfig(query_parameters=params)
472
query_job = client.query(query, job_config=job_config)
473
results = query_job.result()
474
475
for row in results:
476
print(f"User {row.user_id}: {row.event_type} at {row.event_timestamp}")
477
```
478
479
### Parameter Validation and Error Handling
480
481
```python
482
def validate_and_execute_query(client, query, parameters):
483
"""Execute query with parameter validation and error handling."""
484
485
try:
486
# Validate parameters
487
for param in parameters:
488
if isinstance(param, ScalarQueryParameter):
489
if param.value is None and param.type_ != "STRING":
490
raise ValueError(f"Parameter {param.name} cannot be None for type {param.type_}")
491
elif isinstance(param, bigquery.ArrayQueryParameter):
492
if not param.values:
493
raise ValueError(f"Array parameter {param.name} cannot be empty")
494
495
# Create job config
496
job_config = bigquery.QueryJobConfig(query_parameters=parameters)
497
498
# Execute query
499
query_job = client.query(query, job_config=job_config)
500
501
# Wait for completion with timeout
502
results = query_job.result(timeout=30) # 30 second timeout
503
504
print(f"Query executed successfully")
505
print(f"Processed {query_job.total_bytes_processed:,} bytes")
506
print(f"Returned {results.total_rows:,} rows")
507
508
return results
509
510
except Exception as e:
511
print(f"Query execution failed: {e}")
512
if hasattr(e, 'errors') and e.errors:
513
for error in e.errors:
514
print(f" Error: {error}")
515
raise
516
517
# Example usage with validation
518
parameters = [
519
ScalarQueryParameter("user_id", "INT64", 12345),
520
ScalarQueryParameter("start_date", "DATE", "2023-01-01"),
521
bigquery.ArrayQueryParameter("categories", "STRING", ["electronics", "books"]),
522
]
523
524
query = """
525
SELECT * FROM `my_project.my_dataset.purchases`
526
WHERE user_id = @user_id
527
AND purchase_date >= @start_date
528
AND category IN UNNEST(@categories)
529
"""
530
531
try:
532
results = validate_and_execute_query(client, query, parameters)
533
# Process results...
534
except Exception as e:
535
print(f"Failed to execute query: {e}")
536
```