Visitar URL original
feat: support RANGE in queries Part 1: JSON (#1884) · googleapis/google-cloud-python@410ec1d · GitHub
Skip to content

Commit 410ec1d

Browse files
LinchinLinchin
authored
feat: support RANGE in queries Part 1: JSON (#1884)
* feat: support range in queries as dict * fix sys tests * lint * fix typo
1 parent 5959471 commit 410ec1d

4 files changed

Lines changed: 153 additions & 4 deletions

File tree

‎packages/google-cloud-bigquery/google/cloud/bigquery/_helpers.py‎

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -309,6 +309,46 @@ def _json_from_json(value, field):
309309
return None
310310

311311

312+
def _range_element_from_json(value, field):
313+
"""Coerce 'value' to a range element value, if set or not nullable."""
314+
if value == "UNBOUNDED":
315+
return None
316+
elif field.element_type == "DATE":
317+
return _date_from_json(value, None)
318+
elif field.element_type == "DATETIME":
319+
return _datetime_from_json(value, None)
320+
elif field.element_type == "TIMESTAMP":
321+
return _timestamp_from_json(value, None)
322+
else:
323+
raise ValueError(f"Unsupported range field type: {value}")
324+
325+
326+
def _range_from_json(value, field):
327+
"""Coerce 'value' to a range, if set or not nullable.
328+
329+
Args:
330+
value (str): The literal representation of the range.
331+
field (google.cloud.bigquery.schema.SchemaField):
332+
The field corresponding to the value.
333+
334+
Returns:
335+
Optional[dict]:
336+
The parsed range object from ``value`` if the ``field`` is not
337+
null (otherwise it is :data:`None`).
338+
"""
339+
range_literal = re.compile(r"\[.*, .*\)")
340+
if _not_null(value, field):
341+
if range_literal.match(value):
342+
start, end = value[1:-1].split(", ")
343+
start = _range_element_from_json(start, field.range_element_type)
344+
end = _range_element_from_json(end, field.range_element_type)
345+
return {"start": start, "end": end}
346+
else:
347+
raise ValueError(f"Unknown range format: {value}")
348+
else:
349+
return None
350+
351+
312352
# Parse BigQuery API response JSON into a Python representation.
313353
_CELLDATA_FROM_JSON = {
314354
"INTEGER": _int_from_json,
@@ -329,6 +369,7 @@ def _json_from_json(value, field):
329369
"TIME": _time_from_json,
330370
"RECORD": _record_from_json,
331371
"JSON": _json_from_json,
372+
"RANGE": _range_from_json,
332373
}
333374

334375
_QUERY_PARAMS_FROM_JSON = dict(_CELLDATA_FROM_JSON)

‎packages/google-cloud-bigquery/tests/system/helpers.py‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -25,6 +25,7 @@
2525
_naive = datetime.datetime(2016, 12, 5, 12, 41, 9)
2626
_naive_microseconds = datetime.datetime(2016, 12, 5, 12, 41, 9, 250000)
2727
_stamp = "%s %s" % (_naive.date().isoformat(), _naive.time().isoformat())
28+
_date = _naive.date().isoformat()
2829
_stamp_microseconds = _stamp + ".250000"
2930
_zoned = _naive.replace(tzinfo=UTC)
3031
_zoned_microseconds = _naive_microseconds.replace(tzinfo=UTC)
@@ -78,6 +79,10 @@
7879
),
7980
("SELECT ARRAY(SELECT STRUCT([1, 2]))", [{"_field_1": [1, 2]}]),
8081
("SELECT ST_GeogPoint(1, 2)", "POINT(1 2)"),
82+
(
83+
"SELECT RANGE<DATE> '[UNBOUNDED, %s)'" % _date,
84+
{"start": None, "end": _naive.date()},
85+
),
8186
]
8287

8388

‎packages/google-cloud-bigquery/tests/system/test_query.py‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -425,7 +425,7 @@ def test_query_statistics(bigquery_client, query_api_method):
425425
),
426426
(
427427
"SELECT @range_date",
428-
"[2016-12-05, UNBOUNDED)",
428+
{"end": None, "start": datetime.date(2016, 12, 5)},
429429
[
430430
RangeQueryParameter(
431431
name="range_date",
@@ -436,7 +436,7 @@ def test_query_statistics(bigquery_client, query_api_method):
436436
),
437437
(
438438
"SELECT @range_datetime",
439-
"[2016-12-05T00:00:00, UNBOUNDED)",
439+
{"end": None, "start": datetime.datetime(2016, 12, 5, 0, 0)},
440440
[
441441
RangeQueryParameter(
442442
name="range_datetime",
@@ -447,7 +447,7 @@ def test_query_statistics(bigquery_client, query_api_method):
447447
),
448448
(
449449
"SELECT @range_unbounded",
450-
"[UNBOUNDED, UNBOUNDED)",
450+
{"end": None, "start": None},
451451
[
452452
RangeQueryParameter(
453453
name="range_unbounded",

‎packages/google-cloud-bigquery/tests/unit/test__helpers.py‎

Lines changed: 104 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -452,6 +452,99 @@ def test_w_bogus_string_value(self):
452452
self._call_fut("12:12:27.123", object())
453453

454454

455+
class Test_range_from_json(unittest.TestCase):
456+
def _call_fut(self, value, field):
457+
from google.cloud.bigquery._helpers import _range_from_json
458+
459+
return _range_from_json(value, field)
460+
461+
def test_w_none_nullable(self):
462+
self.assertIsNone(self._call_fut(None, _Field("NULLABLE")))
463+
464+
def test_w_none_required(self):
465+
with self.assertRaises(TypeError):
466+
self._call_fut(None, _Field("REQUIRED"))
467+
468+
def test_w_wrong_format(self):
469+
range_field = _Field(
470+
"NULLABLE",
471+
field_type="RANGE",
472+
range_element_type=_Field("NULLABLE", element_type="DATE"),
473+
)
474+
with self.assertRaises(ValueError):
475+
self._call_fut("[2009-06-172019-06-17)", range_field)
476+
477+
def test_w_wrong_element_type(self):
478+
range_field = _Field(
479+
"NULLABLE",
480+
field_type="RANGE",
481+
range_element_type=_Field("NULLABLE", element_type="TIME"),
482+
)
483+
with self.assertRaises(ValueError):
484+
self._call_fut("[15:31:38, 15:50:38)", range_field)
485+
486+
def test_w_unbounded_value(self):
487+
range_field = _Field(
488+
"NULLABLE",
489+
field_type="RANGE",
490+
range_element_type=_Field("NULLABLE", element_type="DATE"),
491+
)
492+
coerced = self._call_fut("[UNBOUNDED, 2019-06-17)", range_field)
493+
self.assertEqual(
494+
coerced,
495+
{"start": None, "end": datetime.date(2019, 6, 17)},
496+
)
497+
498+
def test_w_date_value(self):
499+
range_field = _Field(
500+
"NULLABLE",
501+
field_type="RANGE",
502+
range_element_type=_Field("NULLABLE", element_type="DATE"),
503+
)
504+
coerced = self._call_fut("[2009-06-17, 2019-06-17)", range_field)
505+
self.assertEqual(
506+
coerced,
507+
{
508+
"start": datetime.date(2009, 6, 17),
509+
"end": datetime.date(2019, 6, 17),
510+
},
511+
)
512+
513+
def test_w_datetime_value(self):
514+
range_field = _Field(
515+
"NULLABLE",
516+
field_type="RANGE",
517+
range_element_type=_Field("NULLABLE", element_type="DATETIME"),
518+
)
519+
coerced = self._call_fut(
520+
"[2009-06-17T13:45:30, 2019-06-17T13:45:30)", range_field
521+
)
522+
self.assertEqual(
523+
coerced,
524+
{
525+
"start": datetime.datetime(2009, 6, 17, 13, 45, 30),
526+
"end": datetime.datetime(2019, 6, 17, 13, 45, 30),
527+
},
528+
)
529+
530+
def test_w_timestamp_value(self):
531+
from google.cloud._helpers import _EPOCH
532+
533+
range_field = _Field(
534+
"NULLABLE",
535+
field_type="RANGE",
536+
range_element_type=_Field("NULLABLE", element_type="TIMESTAMP"),
537+
)
538+
coerced = self._call_fut("[1234567, 1234789)", range_field)
539+
self.assertEqual(
540+
coerced,
541+
{
542+
"start": _EPOCH + datetime.timedelta(seconds=1, microseconds=234567),
543+
"end": _EPOCH + datetime.timedelta(seconds=1, microseconds=234789),
544+
},
545+
)
546+
547+
455548
class Test_record_from_json(unittest.TestCase):
456549
def _call_fut(self, value, field):
457550
from google.cloud.bigquery._helpers import _record_from_json
@@ -1323,11 +1416,21 @@ def test_w_str(self):
13231416

13241417

13251418
class _Field(object):
1326-
def __init__(self, mode, name="unknown", field_type="UNKNOWN", fields=()):
1419+
def __init__(
1420+
self,
1421+
mode,
1422+
name="unknown",
1423+
field_type="UNKNOWN",
1424+
fields=(),
1425+
range_element_type=None,
1426+
element_type=None,
1427+
):
13271428
self.mode = mode
13281429
self.name = name
13291430
self.field_type = field_type
13301431
self.fields = fields
1432+
self.range_element_type = range_element_type
1433+
self.element_type = element_type
13311434

13321435

13331436
def _field_isinstance_patcher():

0 commit comments

Comments
 (0)