def test_substring_neg_length():
t = ibis.table([("value", "string")], name="t")
expr = t["value"].substr(3, -1)
with pytest.raises(Exception) as exception_info:
ibis_bigquery.compile(expr)
expected = "Length parameter should not be a negative value."
assert str(exception_info.value) == expected
def test_window_unbounded(kind, begin, end, expected):
t = ibis.table([('a', 'int64')], name='t')
kwargs = {kind: (begin, end)}
expr = t.a.sum().over(ibis.window(**kwargs))
result = ibis_bigquery.compile(expr)
assert (result == f"""\
SELECT sum(`a`) OVER (ROWS BETWEEN {expected}) AS `tmp`
FROM t""")
def test_binary():
t = ibis.table([('value', 'double')], name='t')
expr = t["value"].cast(dt.binary).name("value_hash")
result = ibis_bigquery.compile(expr)
expected = """\
SELECT CAST(`value` AS BYTES) AS `tmp`
FROM t"""
assert result == expected
def test_extract_temporal_from_timestamp(kind):
t = ibis.table([('ts', dt.timestamp)], name='t')
expr = getattr(t.ts, kind)()
result = ibis_bigquery.compile(expr)
expected = f"""\
SELECT {kind.upper()}(`ts`) AS `tmp`
FROM t"""
assert result == expected
def test_temporal_truncate(unit, expected_unit, expected_func):
t = ibis.table([('a', getattr(dt, expected_func.lower()))], name='t')
expr = t.a.truncate(unit)
result = ibis_bigquery.compile(expr)
expected = f"""\
SELECT {expected_func}_TRUNC(`a`, {expected_unit}) AS `tmp`
FROM t"""
assert result == expected
def test_substring():
t = ibis.table([("value", "string")], name="t")
expr = t["value"].substr(3, 1)
expected = """\
SELECT substr(`value`, 3 + 1, 1) AS `tmp`
FROM t"""
result = ibis_bigquery.compile(expr)
assert result == expected
def test_compile_toplevel():
t = ibis.table([('foo', 'double')], name='t0')
# it works!
expr = t.foo.sum()
result = ibis_bigquery.compile(expr)
# FIXME: remove quotes because bigquery can't use anythig that needs
# quoting?
expected = """\
SELECT sum(`foo`) AS `sum`
FROM t0""" # noqa
assert str(result) == expected
def test_bucket():
t = ibis.table([('value', 'double')], name='t')
buckets = [0, 1, 3]
expr = t.value.bucket(buckets).name('foo')
result = ibis_bigquery.compile(expr)
expected = """\
SELECT
CASE
WHEN (`value` >= 0) AND (`value` < 1) THEN 0
WHEN (`value` >= 1) AND (`value` <= 3) THEN 1
ELSE CAST(NULL AS INT64)
END AS `tmp`
FROM t"""
assert result == expected
def test_bucket():
t = ibis.table([("value", "double")], name="t")
buckets = [0, 1, 3]
expr = t.value.bucket(buckets).name("foo")
result = ibis_bigquery.compile(expr)
expected = """\
SELECT
CASE
WHEN (0 <= `value`) AND (`value` < 1) THEN 0
WHEN (1 <= `value`) AND (`value` <= 3) THEN 1
ELSE CAST(NULL AS INT64)
END AS `tmp`
FROM t"""
expected_2 = """\
SELECT
CASE
WHEN (`value` >= 0) AND (`value` < 1) THEN 0
WHEN (`value` >= 1) AND (`value` <= 3) THEN 1
ELSE CAST(NULL AS INT64)
END AS `tmp`
FROM t"""
assert result == expected or result == expected_2
def test_projection_fusion_only_peeks_at_immediate_parent():
if IBIS_VERSION < IBIS_1_4_VERSION:
pytest.skip("requires ibis 1.4+")
schema = [
("file_date", "timestamp"),
("PARTITIONTIME", "date"),
("val", "int64"),
]
table = ibis.table(schema, name="unbound_table")
table = table[table.PARTITIONTIME < ibis.date("2017-01-01")]
table = table.mutate(file_date=table.file_date.cast("date"))
table = table[table.file_date < ibis.date("2017-01-01")]
table = table.mutate(XYZ=table.val * 2)
expr = table.join(table.view())[table]
result = ibis_bigquery.compile(expr)
expected = """\
WITH t0 AS (
SELECT *
FROM unbound_table
WHERE `PARTITIONTIME` < DATE '2017-01-01'
),
t1 AS (
SELECT CAST(`file_date` AS DATE) AS `file_date`, `PARTITIONTIME`, `val`
FROM t0
),
t2 AS (
SELECT t1.*
FROM t1
WHERE t1.`file_date` < DATE '2017-01-01'
),
t3 AS (
SELECT *, `val` * 2 AS `XYZ`
FROM t2
)
SELECT t3.*
FROM t3
INNER JOIN t3 t4"""
assert result == expected
def test_projection_fusion_only_peeks_at_immediate_parent():
schema = [
('file_date', 'timestamp'),
('PARTITIONTIME', 'date'),
('val', 'int64'),
]
table = ibis.table(schema, name='unbound_table')
table = table[table.PARTITIONTIME < ibis.date('2017-01-01')]
table = table.mutate(file_date=table.file_date.cast('date'))
table = table[table.file_date < ibis.date('2017-01-01')]
table = table.mutate(XYZ=table.val * 2)
expr = table.join(table.view())[table]
result = ibis_bigquery.compile(expr)
expected = """\
WITH t0 AS (
SELECT *
FROM unbound_table
WHERE `PARTITIONTIME` < DATE '2017-01-01'
),
t1 AS (
SELECT CAST(`file_date` AS DATE) AS `file_date`, `PARTITIONTIME`, `val`
FROM t0
),
t2 AS (
SELECT t1.*
FROM t1
WHERE t1.`file_date` < DATE '2017-01-01'
),
t3 AS (
SELECT *, `val` * 2 AS `XYZ`
FROM t2
)
SELECT t3.*
FROM t3
INNER JOIN t3 t4"""
assert result == expected
def test_now():
expr = ibis.now()
result = ibis_bigquery.compile(expr)
expected = 'SELECT CURRENT_TIMESTAMP() AS `tmp`'
assert result == expected
),
(
'2017-01-01 04:55:59',
"TIMESTAMP '2017-01-01 04:55:59'",
dt.timestamp,
),
(
pd.Timestamp('2017-01-01 04:55:59'),
"TIMESTAMP '2017-01-01 04:55:59'",
dt.timestamp,
),
],
)
def test_literal_date(case, expected, dtype):
expr = ibis.literal(case, type=dtype).year()
result = ibis_bigquery.compile(expr)
assert result == f"SELECT EXTRACT(year from {expected}) AS `tmp`"
@pytest.mark.parametrize(
('case', 'expected', 'dtype', 'strftime_func'),
[
(
datetime.date(2017, 1, 1),
"DATE '2017-01-01'",
dt.date,
'FORMAT_DATE',
),
(
pd.Timestamp('2017-01-01'),
"DATE '2017-01-01'",