Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# Copyright 2015 Google LLC
2#
3# Licensed under the Apache License, Version 2.0 (the "License");
4# you may not use this file except in compliance with the License.
5# You may obtain a copy of the License at
6#
7# http://www.apache.org/licenses/LICENSE-2.0
8#
9# Unless required by applicable law or agreed to in writing, software
10# distributed under the License is distributed on an "AS IS" BASIS,
11# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12# See the License for the specific language governing permissions and
13# limitations under the License.
15"""BigQuery query processing."""
17from collections import OrderedDict
18import copy
19import datetime
20import decimal
21from typing import Any, cast, Optional, Dict, Union
23from google.cloud.bigquery.table import _parse_schema_resource
24from google.cloud.bigquery import _helpers
25from google.cloud.bigquery._helpers import _rows_from_json
26from google.cloud.bigquery._helpers import _SCALAR_VALUE_TO_JSON_PARAM
27from google.cloud.bigquery._helpers import _SUPPORTED_RANGE_ELEMENTS
30_SCALAR_VALUE_TYPE = Optional[
31 Union[str, int, float, decimal.Decimal, bool, datetime.datetime, datetime.date]
32]
35class ConnectionProperty:
36 """A connection-level property to customize query behavior.
38 See
39 https://cloud.google.com/bigquery/docs/reference/rest/v2/ConnectionProperty
41 Args:
42 key:
43 The key of the property to set, for example, ``'time_zone'`` or
44 ``'session_id'``.
45 value: The value of the property to set.
46 """
48 def __init__(self, key: str = "", value: str = ""):
49 self._properties = {
50 "key": key,
51 "value": value,
52 }
54 @property
55 def key(self) -> str:
56 """Name of the property.
58 For example:
60 * ``time_zone``
61 * ``session_id``
62 """
63 return self._properties["key"]
65 @property
66 def value(self) -> str:
67 """Value of the property."""
68 return self._properties["value"]
70 @classmethod
71 def from_api_repr(cls, resource) -> "ConnectionProperty":
72 """Construct :class:`~google.cloud.bigquery.query.ConnectionProperty`
73 from JSON resource.
75 Args:
76 resource: JSON representation.
78 Returns:
79 A connection property.
80 """
81 value = cls()
82 value._properties = resource
83 return value
85 def to_api_repr(self) -> Dict[str, Any]:
86 """Construct JSON API representation for the connection property.
88 Returns:
89 JSON mapping
90 """
91 return self._properties
94class UDFResource(object):
95 """Describe a single user-defined function (UDF) resource.
97 Args:
98 udf_type (str): The type of the resource ('inlineCode' or 'resourceUri')
100 value (str): The inline code or resource URI.
102 See:
103 https://cloud.google.com/bigquery/user-defined-functions#api
104 """
106 def __init__(self, udf_type, value):
107 self.udf_type = udf_type
108 self.value = value
110 def __eq__(self, other):
111 if not isinstance(other, UDFResource):
112 return NotImplemented
113 return self.udf_type == other.udf_type and self.value == other.value
115 def __ne__(self, other):
116 return not self == other
119class _AbstractQueryParameterType:
120 """Base class for representing query parameter types.
122 https://cloud.google.com/bigquery/docs/reference/rest/v2/QueryParameter#queryparametertype
123 """
125 @classmethod
126 def from_api_repr(cls, resource):
127 """Factory: construct parameter type from JSON resource.
129 Args:
130 resource (Dict): JSON mapping of parameter
132 Returns:
133 google.cloud.bigquery.query.QueryParameterType: Instance
134 """
135 raise NotImplementedError
137 def to_api_repr(self):
138 """Construct JSON API representation for the parameter type.
140 Returns:
141 Dict: JSON mapping
142 """
143 raise NotImplementedError
146class ScalarQueryParameterType(_AbstractQueryParameterType):
147 """Type representation for scalar query parameters.
149 Args:
150 type_ (str):
151 One of 'STRING', 'INT64', 'FLOAT64', 'NUMERIC', 'BOOL', 'TIMESTAMP',
152 'DATETIME', or 'DATE'.
153 name (Optional[str]):
154 The name of the query parameter. Primarily used if the type is
155 one of the subfields in ``StructQueryParameterType`` instance.
156 description (Optional[str]):
157 The query parameter description. Primarily used if the type is
158 one of the subfields in ``StructQueryParameterType`` instance.
159 """
161 def __init__(self, type_, *, name=None, description=None):
162 self._type = type_
163 self.name = name
164 self.description = description
166 @classmethod
167 def from_api_repr(cls, resource):
168 """Factory: construct parameter type from JSON resource.
170 Args:
171 resource (Dict): JSON mapping of parameter
173 Returns:
174 google.cloud.bigquery.query.ScalarQueryParameterType: Instance
175 """
176 type_ = resource["type"]
177 return cls(type_)
179 def to_api_repr(self):
180 """Construct JSON API representation for the parameter type.
182 Returns:
183 Dict: JSON mapping
184 """
185 # Name and description are only used if the type is a field inside a struct
186 # type, but it's StructQueryParameterType's responsibilty to use these two
187 # attributes in the API representation when needed. Here we omit them.
188 return {"type": self._type}
190 def with_name(self, new_name: Union[str, None]):
191 """Return a copy of the instance with ``name`` set to ``new_name``.
193 Args:
194 name (Union[str, None]):
195 The new name of the query parameter type. If ``None``, the existing
196 name is cleared.
198 Returns:
199 google.cloud.bigquery.query.ScalarQueryParameterType:
200 A new instance with updated name.
201 """
202 return type(self)(self._type, name=new_name, description=self.description)
204 def __repr__(self):
205 name = f", name={self.name!r}" if self.name is not None else ""
206 description = (
207 f", description={self.description!r}"
208 if self.description is not None
209 else ""
210 )
211 return f"{self.__class__.__name__}({self._type!r}{name}{description})"
214class ArrayQueryParameterType(_AbstractQueryParameterType):
215 """Type representation for array query parameters.
217 Args:
218 array_type (Union[ScalarQueryParameterType, StructQueryParameterType]):
219 The type of array elements.
220 name (Optional[str]):
221 The name of the query parameter. Primarily used if the type is
222 one of the subfields in ``StructQueryParameterType`` instance.
223 description (Optional[str]):
224 The query parameter description. Primarily used if the type is
225 one of the subfields in ``StructQueryParameterType`` instance.
226 """
228 def __init__(self, array_type, *, name=None, description=None):
229 self._array_type = array_type
230 self.name = name
231 self.description = description
233 @classmethod
234 def from_api_repr(cls, resource):
235 """Factory: construct parameter type from JSON resource.
237 Args:
238 resource (Dict): JSON mapping of parameter
240 Returns:
241 google.cloud.bigquery.query.ArrayQueryParameterType: Instance
242 """
243 array_item_type = resource["arrayType"]["type"]
245 if array_item_type in {"STRUCT", "RECORD"}:
246 klass = StructQueryParameterType
247 else:
248 klass = ScalarQueryParameterType
250 item_type_instance = klass.from_api_repr(resource["arrayType"])
251 return cls(item_type_instance)
253 def to_api_repr(self):
254 """Construct JSON API representation for the parameter type.
256 Returns:
257 Dict: JSON mapping
258 """
259 # Name and description are only used if the type is a field inside a struct
260 # type, but it's StructQueryParameterType's responsibilty to use these two
261 # attributes in the API representation when needed. Here we omit them.
262 return {
263 "type": "ARRAY",
264 "arrayType": self._array_type.to_api_repr(),
265 }
267 def __repr__(self):
268 name = f", name={self.name!r}" if self.name is not None else ""
269 description = (
270 f", description={self.description!r}"
271 if self.description is not None
272 else ""
273 )
274 return f"{self.__class__.__name__}({self._array_type!r}{name}{description})"
277class StructQueryParameterType(_AbstractQueryParameterType):
278 """Type representation for struct query parameters.
280 Args:
281 fields (Iterable[Union[ \
282 ArrayQueryParameterType, ScalarQueryParameterType, StructQueryParameterType \
283 ]]):
284 An non-empty iterable describing the struct's field types.
285 name (Optional[str]):
286 The name of the query parameter. Primarily used if the type is
287 one of the subfields in ``StructQueryParameterType`` instance.
288 description (Optional[str]):
289 The query parameter description. Primarily used if the type is
290 one of the subfields in ``StructQueryParameterType`` instance.
291 """
293 def __init__(self, *fields, name=None, description=None):
294 if not fields:
295 raise ValueError("Struct type must have at least one field defined.")
297 self._fields = fields # fields is a tuple (immutable), no shallow copy needed
298 self.name = name
299 self.description = description
301 @property
302 def fields(self):
303 return self._fields # no copy needed, self._fields is an immutable sequence
305 @classmethod
306 def from_api_repr(cls, resource):
307 """Factory: construct parameter type from JSON resource.
309 Args:
310 resource (Dict): JSON mapping of parameter
312 Returns:
313 google.cloud.bigquery.query.StructQueryParameterType: Instance
314 """
315 fields = []
317 for struct_field in resource["structTypes"]:
318 type_repr = struct_field["type"]
319 if type_repr["type"] in {"STRUCT", "RECORD"}:
320 klass = StructQueryParameterType
321 elif type_repr["type"] == "ARRAY":
322 klass = ArrayQueryParameterType
323 else:
324 klass = ScalarQueryParameterType
326 type_instance = klass.from_api_repr(type_repr)
327 type_instance.name = struct_field.get("name")
328 type_instance.description = struct_field.get("description")
329 fields.append(type_instance)
331 return cls(*fields)
333 def to_api_repr(self):
334 """Construct JSON API representation for the parameter type.
336 Returns:
337 Dict: JSON mapping
338 """
339 fields = []
341 for field in self._fields:
342 item = {"type": field.to_api_repr()}
343 if field.name is not None:
344 item["name"] = field.name
345 if field.description is not None:
346 item["description"] = field.description
348 fields.append(item)
350 return {
351 "type": "STRUCT",
352 "structTypes": fields,
353 }
355 def __repr__(self):
356 name = f", name={self.name!r}" if self.name is not None else ""
357 description = (
358 f", description={self.description!r}"
359 if self.description is not None
360 else ""
361 )
362 items = ", ".join(repr(field) for field in self._fields)
363 return f"{self.__class__.__name__}({items}{name}{description})"
366class RangeQueryParameterType(_AbstractQueryParameterType):
367 """Type representation for range query parameters.
369 Args:
370 type_ (Union[ScalarQueryParameterType, str]):
371 Type of range element, must be one of 'TIMESTAMP', 'DATETIME', or
372 'DATE'.
373 name (Optional[str]):
374 The name of the query parameter. Primarily used if the type is
375 one of the subfields in ``StructQueryParameterType`` instance.
376 description (Optional[str]):
377 The query parameter description. Primarily used if the type is
378 one of the subfields in ``StructQueryParameterType`` instance.
379 """
381 @classmethod
382 def _parse_range_element_type(self, type_):
383 """Helper method that parses the input range element type, which may
384 be a string, or a ScalarQueryParameterType object.
386 Returns:
387 google.cloud.bigquery.query.ScalarQueryParameterType: Instance
388 """
389 if isinstance(type_, str):
390 if type_ not in _SUPPORTED_RANGE_ELEMENTS:
391 raise ValueError(
392 "If given as a string, range element type must be one of "
393 "'TIMESTAMP', 'DATE', or 'DATETIME'."
394 )
395 return ScalarQueryParameterType(type_)
396 elif isinstance(type_, ScalarQueryParameterType):
397 if type_._type not in _SUPPORTED_RANGE_ELEMENTS:
398 raise ValueError(
399 "If given as a ScalarQueryParameter object, range element "
400 "type must be one of 'TIMESTAMP', 'DATE', or 'DATETIME' "
401 "type."
402 )
403 return type_
404 else:
405 raise ValueError(
406 "range_type must be a string or ScalarQueryParameter object, "
407 "of 'TIMESTAMP', 'DATE', or 'DATETIME' type."
408 )
410 def __init__(self, type_, *, name=None, description=None):
411 self.type_ = self._parse_range_element_type(type_)
412 self.name = name
413 self.description = description
415 @classmethod
416 def from_api_repr(cls, resource):
417 """Factory: construct parameter type from JSON resource.
419 Args:
420 resource (Dict): JSON mapping of parameter
422 Returns:
423 google.cloud.bigquery.query.RangeQueryParameterType: Instance
424 """
425 type_ = resource["rangeElementType"]["type"]
426 name = resource.get("name")
427 description = resource.get("description")
429 return cls(type_, name=name, description=description)
431 def to_api_repr(self):
432 """Construct JSON API representation for the parameter type.
434 Returns:
435 Dict: JSON mapping
436 """
437 # Name and description are only used if the type is a field inside a struct
438 # type, but it's StructQueryParameterType's responsibilty to use these two
439 # attributes in the API representation when needed. Here we omit them.
440 return {
441 "type": "RANGE",
442 "rangeElementType": self.type_.to_api_repr(),
443 }
445 def with_name(self, new_name: Union[str, None]):
446 """Return a copy of the instance with ``name`` set to ``new_name``.
448 Args:
449 name (Union[str, None]):
450 The new name of the range query parameter type. If ``None``,
451 the existing name is cleared.
453 Returns:
454 google.cloud.bigquery.query.RangeQueryParameterType:
455 A new instance with updated name.
456 """
457 return type(self)(self.type_, name=new_name, description=self.description)
459 def __repr__(self):
460 name = f", name={self.name!r}" if self.name is not None else ""
461 description = (
462 f", description={self.description!r}"
463 if self.description is not None
464 else ""
465 )
466 return f"{self.__class__.__name__}({self.type_!r}{name}{description})"
468 def _key(self):
469 """A tuple key that uniquely describes this field.
471 Used to compute this instance's hashcode and evaluate equality.
473 Returns:
474 Tuple: The contents of this
475 :class:`~google.cloud.bigquery.query.RangeQueryParameterType`.
476 """
477 type_ = self.type_.to_api_repr()
478 return (self.name, type_, self.description)
480 def __eq__(self, other):
481 if not isinstance(other, RangeQueryParameterType):
482 return NotImplemented
483 return self._key() == other._key()
485 def __ne__(self, other):
486 return not self == other
489class _AbstractQueryParameter(object):
490 """Base class for named / positional query parameters."""
492 @classmethod
493 def from_api_repr(cls, resource: dict) -> "_AbstractQueryParameter":
494 """Factory: construct parameter from JSON resource.
496 Args:
497 resource (Dict): JSON mapping of parameter
499 Returns:
500 A new instance of _AbstractQueryParameter subclass.
501 """
502 raise NotImplementedError
504 def to_api_repr(self) -> dict:
505 """Construct JSON API representation for the parameter.
507 Returns:
508 Dict: JSON representation for the parameter.
509 """
510 raise NotImplementedError
513class ScalarQueryParameter(_AbstractQueryParameter):
514 """Named / positional query parameters for scalar values.
516 Args:
517 name:
518 Parameter name, used via ``@foo`` syntax. If None, the
519 parameter can only be addressed via position (``?``).
521 type_:
522 Name of parameter type. See
523 :class:`google.cloud.bigquery.enums.SqlTypeNames` and
524 :class:`google.cloud.bigquery.query.SqlParameterScalarTypes` for
525 supported types.
527 value:
528 The scalar parameter value.
529 """
531 def __init__(
532 self,
533 name: Optional[str],
534 type_: Optional[Union[str, ScalarQueryParameterType]],
535 value: _SCALAR_VALUE_TYPE,
536 ):
537 self.name = name
538 if isinstance(type_, ScalarQueryParameterType):
539 self.type_ = type_._type
540 else:
541 self.type_ = type_
542 self.value = value
544 @classmethod
545 def positional(
546 cls, type_: Union[str, ScalarQueryParameterType], value: _SCALAR_VALUE_TYPE
547 ) -> "ScalarQueryParameter":
548 """Factory for positional paramater.
550 Args:
551 type_:
552 Name of parameter type. One of 'STRING', 'INT64',
553 'FLOAT64', 'NUMERIC', 'BIGNUMERIC', 'BOOL', 'TIMESTAMP', 'DATETIME', or
554 'DATE'.
556 value:
557 The scalar parameter value.
559 Returns:
560 google.cloud.bigquery.query.ScalarQueryParameter: Instance without name
561 """
562 return cls(None, type_, value)
564 @classmethod
565 def from_api_repr(cls, resource: dict) -> "ScalarQueryParameter":
566 """Factory: construct parameter from JSON resource.
568 Args:
569 resource (Dict): JSON mapping of parameter
571 Returns:
572 google.cloud.bigquery.query.ScalarQueryParameter: Instance
573 """
574 # Import here to avoid circular imports.
575 from google.cloud.bigquery import schema
577 name = resource.get("name")
578 type_ = resource["parameterType"]["type"]
580 # parameterValue might not be present if JSON resource originates
581 # from the back-end - the latter omits it for None values.
582 value = resource.get("parameterValue", {}).get("value")
583 if value is not None:
584 converted = _helpers.SCALAR_QUERY_PARAM_PARSER.to_py(
585 value, schema.SchemaField(cast(str, name), type_)
586 )
587 else:
588 converted = None
590 return cls(name, type_, converted)
592 def to_api_repr(self) -> dict:
593 """Construct JSON API representation for the parameter.
595 Returns:
596 Dict: JSON mapping
597 """
598 value = self.value
599 converter = _SCALAR_VALUE_TO_JSON_PARAM.get(self.type_, lambda value: value)
600 value = converter(value) # type: ignore
601 resource: Dict[str, Any] = {
602 "parameterType": {"type": self.type_},
603 "parameterValue": {"value": value},
604 }
605 if self.name is not None:
606 resource["name"] = self.name
607 return resource
609 def _key(self):
610 """A tuple key that uniquely describes this field.
612 Used to compute this instance's hashcode and evaluate equality.
614 Returns:
615 Tuple: The contents of this :class:`~google.cloud.bigquery.query.ScalarQueryParameter`.
616 """
617 return (self.name, self.type_.upper(), self.value)
619 def __eq__(self, other):
620 if not isinstance(other, ScalarQueryParameter):
621 return NotImplemented
622 return self._key() == other._key()
624 def __ne__(self, other):
625 return not self == other
627 def __repr__(self):
628 return "ScalarQueryParameter{}".format(self._key())
631class ArrayQueryParameter(_AbstractQueryParameter):
632 """Named / positional query parameters for array values.
634 Args:
635 name (Optional[str]):
636 Parameter name, used via ``@foo`` syntax. If None, the
637 parameter can only be addressed via position (``?``).
639 array_type (Union[str, ScalarQueryParameterType, StructQueryParameterType]):
640 The type of array elements. If given as a string, it must be one of
641 `'STRING'`, `'INT64'`, `'FLOAT64'`, `'NUMERIC'`, `'BIGNUMERIC'`, `'BOOL'`,
642 `'TIMESTAMP'`, `'DATE'`, or `'STRUCT'`/`'RECORD'`.
643 If the type is ``'STRUCT'``/``'RECORD'`` and ``values`` is empty,
644 the exact item type cannot be deduced, thus a ``StructQueryParameterType``
645 instance needs to be passed in.
647 values (List[appropriate type]): The parameter array values.
648 """
650 def __init__(self, name, array_type, values) -> None:
651 self.name = name
652 self.values = values
654 if isinstance(array_type, str):
655 if not values and array_type in {"RECORD", "STRUCT"}:
656 raise ValueError(
657 "Missing detailed struct item type info for an empty array, "
658 "please provide a StructQueryParameterType instance."
659 )
660 self.array_type = array_type
662 @classmethod
663 def positional(cls, array_type: str, values: list) -> "ArrayQueryParameter":
664 """Factory for positional parameters.
666 Args:
667 array_type (Union[str, ScalarQueryParameterType, StructQueryParameterType]):
668 The type of array elements. If given as a string, it must be one of
669 `'STRING'`, `'INT64'`, `'FLOAT64'`, `'NUMERIC'`, `'BIGNUMERIC'`,
670 `'BOOL'`, `'TIMESTAMP'`, `'DATE'`, or `'STRUCT'`/`'RECORD'`.
671 If the type is ``'STRUCT'``/``'RECORD'`` and ``values`` is empty,
672 the exact item type cannot be deduced, thus a ``StructQueryParameterType``
673 instance needs to be passed in.
675 values (List[appropriate type]): The parameter array values.
677 Returns:
678 google.cloud.bigquery.query.ArrayQueryParameter: Instance without name
679 """
680 return cls(None, array_type, values)
682 @classmethod
683 def _from_api_repr_struct(cls, resource):
684 name = resource.get("name")
685 converted = []
686 # We need to flatten the array to use the StructQueryParameter
687 # parse code.
688 resource_template = {
689 # The arrayType includes all the types of the fields of the STRUCT
690 "parameterType": resource["parameterType"]["arrayType"]
691 }
692 for array_value in resource["parameterValue"]["arrayValues"]:
693 struct_resource = copy.deepcopy(resource_template)
694 struct_resource["parameterValue"] = array_value
695 struct_value = StructQueryParameter.from_api_repr(struct_resource)
696 converted.append(struct_value)
697 return cls(name, "STRUCT", converted)
699 @classmethod
700 def _from_api_repr_scalar(cls, resource):
701 """Converts REST resource into a list of scalar values."""
702 # Import here to avoid circular imports.
703 from google.cloud.bigquery import schema
705 name = resource.get("name")
706 array_type = resource["parameterType"]["arrayType"]["type"]
707 parameter_value = resource.get("parameterValue", {})
708 array_values = parameter_value.get("arrayValues", ())
709 values = [value["value"] for value in array_values]
710 converted = [
711 _helpers.SCALAR_QUERY_PARAM_PARSER.to_py(
712 value, schema.SchemaField(name, array_type)
713 )
714 for value in values
715 ]
716 return cls(name, array_type, converted)
718 @classmethod
719 def from_api_repr(cls, resource: dict) -> "ArrayQueryParameter":
720 """Factory: construct parameter from JSON resource.
722 Args:
723 resource (Dict): JSON mapping of parameter
725 Returns:
726 google.cloud.bigquery.query.ArrayQueryParameter: Instance
727 """
728 array_type = resource["parameterType"]["arrayType"]["type"]
729 if array_type == "STRUCT":
730 return cls._from_api_repr_struct(resource)
731 return cls._from_api_repr_scalar(resource)
733 def to_api_repr(self) -> dict:
734 """Construct JSON API representation for the parameter.
736 Returns:
737 Dict: JSON mapping
738 """
739 values = self.values
741 if self.array_type in {"RECORD", "STRUCT"} or isinstance(
742 self.array_type, StructQueryParameterType
743 ):
744 reprs = [value.to_api_repr() for value in values]
745 a_values = [repr_["parameterValue"] for repr_ in reprs]
747 if reprs:
748 a_type = reprs[0]["parameterType"]
749 else:
750 # This assertion always evaluates to True because the
751 # constructor disallows STRUCT/RECORD type defined as a
752 # string with empty values.
753 assert isinstance(self.array_type, StructQueryParameterType)
754 a_type = self.array_type.to_api_repr()
755 else:
756 # Scalar array item type.
757 if isinstance(self.array_type, str):
758 a_type = {"type": self.array_type}
759 else:
760 a_type = self.array_type.to_api_repr()
762 converter = _SCALAR_VALUE_TO_JSON_PARAM.get(
763 a_type["type"], lambda value: value
764 )
765 values = [converter(value) for value in values] # type: ignore
766 a_values = [{"value": value} for value in values]
768 resource = {
769 "parameterType": {"type": "ARRAY", "arrayType": a_type},
770 "parameterValue": {"arrayValues": a_values},
771 }
772 if self.name is not None:
773 resource["name"] = self.name
775 return resource
777 def _key(self):
778 """A tuple key that uniquely describes this field.
780 Used to compute this instance's hashcode and evaluate equality.
782 Returns:
783 Tuple: The contents of this :class:`~google.cloud.bigquery.query.ArrayQueryParameter`.
784 """
785 if isinstance(self.array_type, str):
786 item_type = self.array_type
787 elif isinstance(self.array_type, ScalarQueryParameterType):
788 item_type = self.array_type._type
789 else:
790 item_type = "STRUCT"
792 return (self.name, item_type.upper(), self.values)
794 def __eq__(self, other):
795 if not isinstance(other, ArrayQueryParameter):
796 return NotImplemented
797 return self._key() == other._key()
799 def __ne__(self, other):
800 return not self == other
802 def __repr__(self):
803 return "ArrayQueryParameter{}".format(self._key())
806class StructQueryParameter(_AbstractQueryParameter):
807 """Name / positional query parameters for struct values.
809 Args:
810 name (Optional[str]):
811 Parameter name, used via ``@foo`` syntax. If None, the
812 parameter can only be addressed via position (``?``).
814 sub_params (Union[Tuple[
815 google.cloud.bigquery.query.ScalarQueryParameter,
816 google.cloud.bigquery.query.ArrayQueryParameter,
817 google.cloud.bigquery.query.StructQueryParameter
818 ]]): The sub-parameters for the struct
819 """
821 def __init__(self, name, *sub_params) -> None:
822 self.name = name
823 self.struct_types: Dict[str, Any] = OrderedDict()
824 self.struct_values: Dict[str, Any] = {}
826 types = self.struct_types
827 values = self.struct_values
828 for sub in sub_params:
829 if isinstance(sub, self.__class__):
830 types[sub.name] = "STRUCT"
831 values[sub.name] = sub
832 elif isinstance(sub, ArrayQueryParameter):
833 types[sub.name] = "ARRAY"
834 values[sub.name] = sub
835 else:
836 types[sub.name] = sub.type_
837 values[sub.name] = sub.value
839 @classmethod
840 def positional(cls, *sub_params):
841 """Factory for positional parameters.
843 Args:
844 sub_params (Union[Tuple[
845 google.cloud.bigquery.query.ScalarQueryParameter,
846 google.cloud.bigquery.query.ArrayQueryParameter,
847 google.cloud.bigquery.query.StructQueryParameter
848 ]]): The sub-parameters for the struct
850 Returns:
851 google.cloud.bigquery.query.StructQueryParameter: Instance without name
852 """
853 return cls(None, *sub_params)
855 @classmethod
856 def from_api_repr(cls, resource: dict) -> "StructQueryParameter":
857 """Factory: construct parameter from JSON resource.
859 Args:
860 resource (Dict): JSON mapping of parameter
862 Returns:
863 google.cloud.bigquery.query.StructQueryParameter: Instance
864 """
865 # Import here to avoid circular imports.
866 from google.cloud.bigquery import schema
868 name = resource.get("name")
869 instance = cls(name)
870 type_resources = {}
871 types = instance.struct_types
872 for item in resource["parameterType"]["structTypes"]:
873 types[item["name"]] = item["type"]["type"]
874 type_resources[item["name"]] = item["type"]
875 struct_values = resource["parameterValue"]["structValues"]
876 for key, value in struct_values.items():
877 type_ = types[key]
878 converted: Optional[Union[ArrayQueryParameter, StructQueryParameter]] = None
879 if type_ == "STRUCT":
880 struct_resource = {
881 "name": key,
882 "parameterType": type_resources[key],
883 "parameterValue": value,
884 }
885 converted = StructQueryParameter.from_api_repr(struct_resource)
886 elif type_ == "ARRAY":
887 struct_resource = {
888 "name": key,
889 "parameterType": type_resources[key],
890 "parameterValue": value,
891 }
892 converted = ArrayQueryParameter.from_api_repr(struct_resource)
893 else:
894 value = value["value"]
895 converted = _helpers.SCALAR_QUERY_PARAM_PARSER.to_py(
896 value, schema.SchemaField(cast(str, name), type_)
897 )
898 instance.struct_values[key] = converted
899 return instance
901 def to_api_repr(self) -> dict:
902 """Construct JSON API representation for the parameter.
904 Returns:
905 Dict: JSON mapping
906 """
907 s_types = {}
908 values = {}
909 for name, value in self.struct_values.items():
910 type_ = self.struct_types[name]
911 if type_ in ("STRUCT", "ARRAY"):
912 repr_ = value.to_api_repr()
913 s_types[name] = {"name": name, "type": repr_["parameterType"]}
914 values[name] = repr_["parameterValue"]
915 else:
916 s_types[name] = {"name": name, "type": {"type": type_}}
917 converter = _SCALAR_VALUE_TO_JSON_PARAM.get(type_, lambda value: value)
918 values[name] = {"value": converter(value)}
920 resource = {
921 "parameterType": {
922 "type": "STRUCT",
923 "structTypes": [s_types[key] for key in self.struct_types],
924 },
925 "parameterValue": {"structValues": values},
926 }
927 if self.name is not None:
928 resource["name"] = self.name
929 return resource
931 def _key(self):
932 """A tuple key that uniquely describes this field.
934 Used to compute this instance's hashcode and evaluate equality.
936 Returns:
937 Tuple: The contents of this :class:`~google.cloud.bigquery.ArrayQueryParameter`.
938 """
939 return (self.name, self.struct_types, self.struct_values)
941 def __eq__(self, other):
942 if not isinstance(other, StructQueryParameter):
943 return NotImplemented
944 return self._key() == other._key()
946 def __ne__(self, other):
947 return not self == other
949 def __repr__(self):
950 return "StructQueryParameter{}".format(self._key())
953class RangeQueryParameter(_AbstractQueryParameter):
954 """Named / positional query parameters for range values.
956 Args:
957 range_element_type (Union[str, RangeQueryParameterType]):
958 The type of range elements. It must be one of 'TIMESTAMP',
959 'DATE', or 'DATETIME'.
961 start (Optional[Union[ScalarQueryParameter, str]]):
962 The start of the range value. Must be the same type as
963 range_element_type. If not provided, it's interpreted as UNBOUNDED.
965 end (Optional[Union[ScalarQueryParameter, str]]):
966 The end of the range value. Must be the same type as
967 range_element_type. If not provided, it's interpreted as UNBOUNDED.
969 name (Optional[str]):
970 Parameter name, used via ``@foo`` syntax. If None, the
971 parameter can only be addressed via position (``?``).
972 """
974 @classmethod
975 def _parse_range_element_type(self, range_element_type):
976 if isinstance(range_element_type, str):
977 if range_element_type not in _SUPPORTED_RANGE_ELEMENTS:
978 raise ValueError(
979 "If given as a string, range_element_type must be one of "
980 f"'TIMESTAMP', 'DATE', or 'DATETIME'. Got {range_element_type}."
981 )
982 return RangeQueryParameterType(range_element_type)
983 elif isinstance(range_element_type, RangeQueryParameterType):
984 if range_element_type.type_._type not in _SUPPORTED_RANGE_ELEMENTS:
985 raise ValueError(
986 "If given as a RangeQueryParameterType object, "
987 "range_element_type must be one of 'TIMESTAMP', 'DATE', "
988 "or 'DATETIME' type."
989 )
990 return range_element_type
991 else:
992 raise ValueError(
993 "range_element_type must be a string or "
994 "RangeQueryParameterType object, of 'TIMESTAMP', 'DATE', "
995 "or 'DATETIME' type. Got "
996 f"{type(range_element_type)}:{range_element_type}"
997 )
999 @classmethod
1000 def _serialize_range_element_value(self, value, type_):
1001 if value is None or isinstance(value, str):
1002 return value
1003 else:
1004 converter = _SCALAR_VALUE_TO_JSON_PARAM.get(type_)
1005 if converter is not None:
1006 return converter(value) # type: ignore
1007 else:
1008 raise ValueError(
1009 f"Cannot convert range element value from type {type_}, "
1010 "must be one of the strings 'TIMESTAMP', 'DATE' "
1011 "'DATETIME' or a RangeQueryParameterType object."
1012 )
1014 def __init__(
1015 self,
1016 range_element_type,
1017 start=None,
1018 end=None,
1019 name=None,
1020 ):
1021 self.name = name
1022 self.range_element_type = self._parse_range_element_type(range_element_type)
1023 print(self.range_element_type.type_._type)
1024 self.start = start
1025 self.end = end
1027 @classmethod
1028 def positional(
1029 cls, range_element_type, start=None, end=None
1030 ) -> "RangeQueryParameter":
1031 """Factory for positional parameters.
1033 Args:
1034 range_element_type (Union[str, RangeQueryParameterType]):
1035 The type of range elements. It must be one of `'TIMESTAMP'`,
1036 `'DATE'`, or `'DATETIME'`.
1038 start (Optional[Union[ScalarQueryParameter, str]]):
1039 The start of the range value. Must be the same type as
1040 range_element_type. If not provided, it's interpreted as
1041 UNBOUNDED.
1043 end (Optional[Union[ScalarQueryParameter, str]]):
1044 The end of the range value. Must be the same type as
1045 range_element_type. If not provided, it's interpreted as
1046 UNBOUNDED.
1048 Returns:
1049 google.cloud.bigquery.query.RangeQueryParameter: Instance without
1050 name.
1051 """
1052 return cls(range_element_type, start, end)
1054 @classmethod
1055 def from_api_repr(cls, resource: dict) -> "RangeQueryParameter":
1056 """Factory: construct parameter from JSON resource.
1058 Args:
1059 resource (Dict): JSON mapping of parameter
1061 Returns:
1062 google.cloud.bigquery.query.RangeQueryParameter: Instance
1063 """
1064 name = resource.get("name")
1065 range_element_type = (
1066 resource.get("parameterType", {}).get("rangeElementType", {}).get("type")
1067 )
1068 range_value = resource.get("parameterValue", {}).get("rangeValue", {})
1069 start = range_value.get("start", {}).get("value")
1070 end = range_value.get("end", {}).get("value")
1072 return cls(range_element_type, start=start, end=end, name=name)
1074 def to_api_repr(self) -> dict:
1075 """Construct JSON API representation for the parameter.
1077 Returns:
1078 Dict: JSON mapping
1079 """
1080 range_element_type = self.range_element_type.to_api_repr()
1081 type_ = self.range_element_type.type_._type
1082 start = self._serialize_range_element_value(self.start, type_)
1083 end = self._serialize_range_element_value(self.end, type_)
1084 resource = {
1085 "parameterType": range_element_type,
1086 "parameterValue": {
1087 "rangeValue": {
1088 "start": {"value": start},
1089 "end": {"value": end},
1090 },
1091 },
1092 }
1094 # distinguish between name not provided vs. name being empty string
1095 if self.name is not None:
1096 resource["name"] = self.name
1098 return resource
1100 def _key(self):
1101 """A tuple key that uniquely describes this field.
1103 Used to compute this instance's hashcode and evaluate equality.
1105 Returns:
1106 Tuple: The contents of this
1107 :class:`~google.cloud.bigquery.query.RangeQueryParameter`.
1108 """
1110 range_element_type = self.range_element_type.to_api_repr()
1111 return (self.name, range_element_type, self.start, self.end)
1113 def __eq__(self, other):
1114 if not isinstance(other, RangeQueryParameter):
1115 return NotImplemented
1116 return self._key() == other._key()
1118 def __ne__(self, other):
1119 return not self == other
1121 def __repr__(self):
1122 return "RangeQueryParameter{}".format(self._key())
1125class SqlParameterScalarTypes:
1126 """Supported scalar SQL query parameter types as type objects."""
1128 BOOL = ScalarQueryParameterType("BOOL")
1129 BOOLEAN = ScalarQueryParameterType("BOOL")
1130 BIGDECIMAL = ScalarQueryParameterType("BIGNUMERIC")
1131 BIGNUMERIC = ScalarQueryParameterType("BIGNUMERIC")
1132 BYTES = ScalarQueryParameterType("BYTES")
1133 DATE = ScalarQueryParameterType("DATE")
1134 DATETIME = ScalarQueryParameterType("DATETIME")
1135 DECIMAL = ScalarQueryParameterType("NUMERIC")
1136 FLOAT = ScalarQueryParameterType("FLOAT64")
1137 FLOAT64 = ScalarQueryParameterType("FLOAT64")
1138 GEOGRAPHY = ScalarQueryParameterType("GEOGRAPHY")
1139 INT64 = ScalarQueryParameterType("INT64")
1140 INTEGER = ScalarQueryParameterType("INT64")
1141 NUMERIC = ScalarQueryParameterType("NUMERIC")
1142 STRING = ScalarQueryParameterType("STRING")
1143 TIME = ScalarQueryParameterType("TIME")
1144 TIMESTAMP = ScalarQueryParameterType("TIMESTAMP")
1147class _QueryResults(object):
1148 """Results of a query.
1150 See:
1151 https://g.co/cloud/bigquery/docs/reference/rest/v2/jobs/getQueryResults
1152 """
1154 def __init__(self, properties):
1155 self._properties = {}
1156 self._set_properties(properties)
1158 @classmethod
1159 def from_api_repr(cls, api_response):
1160 return cls(api_response)
1162 @property
1163 def project(self):
1164 """Project bound to the query job.
1166 Returns:
1167 str: The project that the query job is associated with.
1168 """
1169 return self._properties.get("jobReference", {}).get("projectId")
1171 @property
1172 def cache_hit(self):
1173 """Query results served from cache.
1175 See:
1176 https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query#body.QueryResponse.FIELDS.cache_hit
1178 Returns:
1179 Optional[bool]:
1180 True if the query results were served from cache (None
1181 until set by the server).
1182 """
1183 return self._properties.get("cacheHit")
1185 @property
1186 def complete(self):
1187 """Server completed query.
1189 See:
1190 https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query#body.QueryResponse.FIELDS.job_complete
1192 Returns:
1193 Optional[bool]:
1194 True if the query completed on the server (None
1195 until set by the server).
1196 """
1197 return self._properties.get("jobComplete")
1199 @property
1200 def errors(self):
1201 """Errors generated by the query.
1203 See:
1204 https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query#body.QueryResponse.FIELDS.errors
1206 Returns:
1207 Optional[List[Mapping]]:
1208 Mappings describing errors generated on the server (None
1209 until set by the server).
1210 """
1211 return self._properties.get("errors")
1213 @property
1214 def job_id(self):
1215 """Job ID of the query job these results are from.
1217 See:
1218 https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query#body.QueryResponse.FIELDS.job_reference
1220 Returns:
1221 str: Job ID of the query job.
1222 """
1223 return self._properties.get("jobReference", {}).get("jobId")
1225 @property
1226 def location(self):
1227 """Location of the query job these results are from.
1229 See:
1230 https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query#body.QueryResponse.FIELDS.job_reference
1232 Returns:
1233 str: Job ID of the query job.
1234 """
1235 return self._properties.get("jobReference", {}).get("location")
1237 @property
1238 def query_id(self) -> Optional[str]:
1239 """[Preview] ID of a completed query.
1241 This ID is auto-generated and not guaranteed to be populated.
1242 """
1243 return self._properties.get("queryId")
1245 @property
1246 def page_token(self):
1247 """Token for fetching next bach of results.
1249 See:
1250 https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query#body.QueryResponse.FIELDS.page_token
1252 Returns:
1253 Optional[str]: Token generated on the server (None until set by the server).
1254 """
1255 return self._properties.get("pageToken")
1257 @property
1258 def total_rows(self):
1259 """Total number of rows returned by the query.
1261 See:
1262 https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query#body.QueryResponse.FIELDS.total_rows
1264 Returns:
1265 Optional[int]: Count generated on the server (None until set by the server).
1266 """
1267 total_rows = self._properties.get("totalRows")
1268 if total_rows is not None:
1269 return int(total_rows)
1271 @property
1272 def total_bytes_processed(self):
1273 """Total number of bytes processed by the query.
1275 See:
1276 https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query#body.QueryResponse.FIELDS.total_bytes_processed
1278 Returns:
1279 Optional[int]: Count generated on the server (None until set by the server).
1280 """
1281 total_bytes_processed = self._properties.get("totalBytesProcessed")
1282 if total_bytes_processed is not None:
1283 return int(total_bytes_processed)
1285 @property
1286 def num_dml_affected_rows(self):
1287 """Total number of rows affected by a DML query.
1289 See:
1290 https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query#body.QueryResponse.FIELDS.num_dml_affected_rows
1292 Returns:
1293 Optional[int]: Count generated on the server (None until set by the server).
1294 """
1295 num_dml_affected_rows = self._properties.get("numDmlAffectedRows")
1296 if num_dml_affected_rows is not None:
1297 return int(num_dml_affected_rows)
1299 @property
1300 def rows(self):
1301 """Query results.
1303 See:
1304 https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query#body.QueryResponse.FIELDS.rows
1306 Returns:
1307 Optional[List[google.cloud.bigquery.table.Row]]:
1308 Rows containing the results of the query.
1309 """
1310 return _rows_from_json(self._properties.get("rows", ()), self.schema)
1312 @property
1313 def schema(self):
1314 """Schema for query results.
1316 See:
1317 https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query#body.QueryResponse.FIELDS.schema
1319 Returns:
1320 Optional[List[SchemaField]]:
1321 Fields describing the schema (None until set by the server).
1322 """
1323 return _parse_schema_resource(self._properties.get("schema", {}))
1325 def _set_properties(self, api_response):
1326 """Update properties from resource in body of ``api_response``
1328 Args:
1329 api_response (Dict): Response returned from an API call
1330 """
1331 self._properties.clear()
1332 self._properties.update(copy.deepcopy(api_response))
1335def _query_param_from_api_repr(resource):
1336 """Helper: Construct concrete query parameter from JSON resource."""
1337 qp_type = resource["parameterType"]
1338 if "arrayType" in qp_type:
1339 klass = ArrayQueryParameter
1340 elif "structTypes" in qp_type:
1341 klass = StructQueryParameter
1342 else:
1343 klass = ScalarQueryParameter
1344 return klass.from_api_repr(resource)