def prep_311_data(file):
catalog = intake_civis.open_redshift_catalog()
expr = catalog.public.import311.to_ibis()
recent_srs = expr[(expr.createddate >
(ibis.now() - ibis.interval(months=6)))
& (expr.requesttype != "Homeless Encampment")]
df = recent_srs.execute()
df.to_csv(file, index=False)
def test_expr_list_no_table_refs(self):
exlist = ibis.api.expr_list([ibis.literal(1).name('a'),
ibis.now().name('b'),
ibis.literal(2).log().name('c')])
result = to_sql(exlist)
expected = """\
SELECT 1 AS `a`, now() AS `b`, ln(2) AS `c`"""
assert result == expected
def test_timestamp_scalar_in_filter(alltypes, translate):
table = alltypes
expr = (table.filter([
table.timestamp_col < (ibis.timestamp('2010-01-01') + ibis.week(3)),
table.timestamp_col < (ibis.now() + ibis.day(10))
]).count())
expr.execute()
def test_timestamp_scalar_in_filter(alltypes, translate):
table = alltypes
expr = table.filter([
table.timestamp_col <
(ibis.timestamp('2010-01-01') + ibis.interval(weeks=3)),
table.timestamp_col < (ibis.now() + ibis.interval(days=10)),
]).count()
expr.execute()
def test_execute_exprs_no_table_ref(con, expr, expected):
result = con.execute(expr)
assert result == expected
# ExprList
exlist = ibis.api.expr_list([L(1).name('a'),
ibis.now().name('b'),
L(2).log().name('c')])
con.execute(exlist)
def test_now(backend, con):
expr = ibis.now()
result = con.execute(expr)
pandas_now = pd.Timestamp('now')
assert isinstance(result, pd.Timestamp)
# this could fail if we're testing in different timezones and we're testing
# on Dec 31st
assert result.year == pandas_now.year
def test_now(backend, con):
expr = ibis.now()
result = con.execute(expr)
pandas_now = pd.Timestamp('now')
assert isinstance(result, pd.Timestamp)
# this could fail if we're testing in different timezones and we're testing
# on Dec 31st
assert result.year == pandas_now.year
def test_timestamp_scalar_in_filter(self):
# #310
table = self.alltypes
expr = (table.filter([table.timestamp_col <
(ibis.timestamp('2010-01-01') + ibis.month(3)),
table.timestamp_col < (ibis.now() + ibis.day(10))
])
.count())
expr.execute()
def test_expr_list_no_table_refs(self):
exlist = ibis.api.expr_list([
ibis.literal(1).name('a'),
ibis.now().name('b'),
ibis.literal(2).log().name('c')
])
result = to_sql(exlist)
expected = """\
SELECT 1 AS `a`, now() AS `b`, ln(2) AS `c`"""
assert result == expected
def test_execute_exprs_no_table_ref(self):
cases = [(ibis.literal(1) + ibis.literal(2), 3)]
for expr, expected in cases:
result = self.con.execute(expr)
assert result == expected
# ExprList
exlist = ibis.api.expr_list([ibis.literal(1).name("a"), ibis.now().name("b"), ibis.literal(2).log().name("c")])
self.con.execute(exlist)
def test_timestamp_scalar_in_filter(self):
# #310
table = self.alltypes
expr = (table.filter([table.timestamp_col <
(ibis.timestamp('2010-01-01') + ibis.month(3)),
table.timestamp_col < (ibis.now() + ibis.day(10))
])
.count())
expr.execute()
def test_expr_list_no_table_refs():
exlist = ibis.api.expr_list([
ibis.literal(1).name('a'),
ibis.now().name('b'),
ibis.literal(2).log().name('c')
])
result = ibis.clickhouse.compile(exlist)
expected = """\
SELECT 1 AS `a`, now() AS `b`, log(2) AS `c`"""
assert result == expected
def test_timestamp_scalar_in_filter(alltypes):
# #310
table = alltypes
expr = table.filter([
table.timestamp_col <
(ibis.timestamp('2010-01-01') + ibis.interval(months=3)),
table.timestamp_col < (ibis.now() + ibis.interval(days=10)),
]).count()
expr.execute()
def test_timestamp_scalar_in_filter(alltypes, translate):
table = alltypes
expr = table.filter(
[
table.timestamp_col
< (ibis.timestamp('2010-01-01') + ibis.interval(weeks=3)),
table.timestamp_col < (ibis.now() + ibis.interval(days=10)),
]
).count()
expr.execute()
def test_scalar_exprs_no_table_refs(con):
expr1 = ibis.now()
expected1 = "SELECT now() AS `tmp`"
expr2 = ibis.literal(1) + ibis.literal(2)
expected2 = "SELECT 1 + 2 AS `tmp`"
cases = [(expr1, expected1), (expr2, expected2)]
for expr, expected in cases:
result = Compiler.to_sql(expr)
assert result == expected
def test_expr_list_no_table_refs():
exlist = ibis.api.expr_list(
[
ibis.literal(1).name('a'),
ibis.now().name('b'),
ibis.literal(2).log().name('c'),
]
)
result = ibis.clickhouse.compile(exlist)
expected = """\
SELECT 1 AS `a`, now() AS `b`, log(2) AS `c`"""
assert result == expected
def test_decimal_timestamp_builtins(self):
table = self.con.table('tpch_lineitem')
dc = table.l_quantity
ts = table.l_receiptdate.cast('timestamp')
exprs = [
dc % 10,
dc + 5,
dc + dc,
dc / 2,
dc * 2,
dc ** 2,
dc.cast('double'),
api.where(table.l_discount > 0,
dc * table.l_discount, api.NA),
dc.fillna(0),
ts < (ibis.now() + ibis.month(3)),
ts < (ibis.timestamp('2005-01-01') + ibis.month(3)),
# hashing
dc.hash(),
ts.hash(),
# truncate
ts.truncate('y'),
ts.truncate('q'),
ts.truncate('month'),
ts.truncate('d'),
ts.truncate('w'),
ts.truncate('h'),
ts.truncate('minute'),
]
timestamp_fields = ['year', 'month', 'day', 'hour', 'minute',
'second', 'millisecond', 'microsecond',
'week']
for field in timestamp_fields:
if hasattr(ts, field):
exprs.append(getattr(ts, field)())
offset = getattr(ibis, field)(2)
exprs.append(ts + offset)
exprs.append(ts - offset)
proj_exprs = [expr.name('e%d' % i)
for i, expr in enumerate(exprs)]
projection = table[proj_exprs].limit(10)
projection.execute()
def test_decimal_timestamp_builtins(self):
table = self.con.table('tpch_lineitem')
dc = table.l_quantity
ts = table.l_receiptdate.cast('timestamp')
exprs = [
dc % 10,
dc + 5,
dc + dc,
dc / 2,
dc * 2,
dc ** 2,
dc.cast('double'),
api.where(table.l_discount > 0,
dc * table.l_discount, api.NA),
dc.fillna(0),
ts < (ibis.now() + ibis.month(3)),
ts < (ibis.timestamp('2005-01-01') + ibis.month(3)),
# hashing
dc.hash(),
ts.hash(),
# truncate
ts.truncate('y'),
ts.truncate('q'),
ts.truncate('month'),
ts.truncate('d'),
ts.truncate('w'),
ts.truncate('h'),
ts.truncate('minute'),
]
timestamp_fields = ['year', 'month', 'day', 'hour', 'minute',
'second', 'millisecond', 'microsecond',
'week']
for field in timestamp_fields:
if hasattr(ts, field):
exprs.append(getattr(ts, field)())
offset = getattr(ibis, field)(2)
exprs.append(ts + offset)
exprs.append(ts - offset)
proj_exprs = [expr.name('e%d' % i)
for i, expr in enumerate(exprs)]
projection = table[proj_exprs].limit(10)
projection.execute()
def test_execute_exprs_no_table_ref(con):
cases = [(L(1) + L(2), 3)]
for expr, expected in cases:
result = con.execute(expr)
assert result == expected
# ExprList
exlist = ibis.api.expr_list(
[L(1).name('a'), ibis.now().name('b'), L(2).log().name('c')]
)
con.execute(exlist)
def test_execute_exprs_no_table_ref(self):
cases = [(L(1) + L(2), 3)]
for expr, expected in cases:
result = self.con.execute(expr)
assert result == expected
# ExprList
exlist = ibis.api.expr_list(
[L(1).name('a'),
ibis.now().name('b'),
L(2).log().name('c')])
self.con.execute(exlist)
def test_now_from_projection(backend, con, alltypes, df):
n = 5
expr = alltypes[[ibis.now().name('ts')]].limit(n)
result = expr.execute()
ts = result.ts
assert isinstance(result, pd.DataFrame)
assert isinstance(ts, pd.Series)
assert issubclass(ts.dtype.type, np.datetime64)
assert len(result) == n
assert ts.nunique() == 1
now = pd.Timestamp('now')
year_expected = pd.Series([now.year] * n, name='ts')
tm.assert_series_equal(ts.dt.year, year_expected)
def test_now_from_projection(backend, con, alltypes, df):
n = 5
expr = alltypes[[ibis.now().name('ts')]].limit(n)
result = expr.execute()
ts = result.ts
assert isinstance(result, pd.DataFrame)
assert isinstance(ts, pd.Series)
assert issubclass(ts.dtype.type, np.datetime64)
assert len(result) == n
assert ts.nunique() == 1
now = pd.Timestamp('now')
year_expected = pd.Series([now.year] * n, name='ts')
tm.assert_series_equal(ts.dt.year, year_expected)
def test_where_analyze_scalar_op(functional_alltypes):
# root cause of #310
table = functional_alltypes
expr = table.filter([
table.timestamp_col <
(ibis.timestamp('2010-01-01') + ibis.interval(months=3)),
table.timestamp_col < (ibis.now() + ibis.interval(days=10)),
]).count()
result = Compiler.to_sql(expr)
expected = """\
SELECT count(*) AS `count`
FROM functional_alltypes
WHERE (`timestamp_col` < date_add(cast({} as timestamp), INTERVAL 3 MONTH)) AND
(`timestamp_col` < date_add(cast(now() as timestamp), INTERVAL 10 DAY))""" # noqa: E501
assert result == expected.format("'2010-01-01 00:00:00'")
def test_scalar_exprs_no_table_refs(self):
expr1 = ibis.now()
expected1 = """\
SELECT now() AS `tmp`"""
expr2 = ibis.literal(1) + ibis.literal(2)
expected2 = """\
SELECT 1 + 2 AS `tmp`"""
cases = [
(expr1, expected1),
(expr2, expected2)
]
for expr, expected in cases:
result = to_sql(expr)
assert result == expected
def test_where_analyze_scalar_op(self):
# root cause of #310
table = self.con.table('functional_alltypes')
expr = (table.filter([table.timestamp_col <
(ibis.timestamp('2010-01-01') + ibis.month(3)),
table.timestamp_col < (ibis.now() +
ibis.day(10))])
.count())
result = to_sql(expr)
expected = """\
SELECT count(*) AS `tmp`
FROM functional_alltypes
WHERE `timestamp_col` < months_add('2010-01-01 00:00:00', 3) AND
`timestamp_col` < days_add(now(), 10)"""
assert result == expected
def test_where_analyze_scalar_op(self):
# root cause of #310
table = self.con.table('functional_alltypes')
expr = (table.filter([
table.timestamp_col <
(ibis.timestamp('2010-01-01') + ibis.month(3)),
table.timestamp_col < (ibis.now() + ibis.day(10))
]).count())
result = to_sql(expr)
expected = """\
SELECT count(*) AS `tmp`
FROM functional_alltypes
WHERE `timestamp_col` < months_add('2010-01-01 00:00:00', 3) AND
`timestamp_col` < days_add(now(), 10)"""
assert result == expected
def test_decimal_timestamp_builtins(self):
table = self.con.table("tpch_lineitem")
dc = table.l_quantity
ts = table.l_receiptdate.cast("timestamp")
exprs = [
dc % 10,
dc + 5,
dc + dc,
dc / 2,
dc * 2,
dc ** 2,
dc.cast("double"),
api.where(table.l_discount > 0, dc * table.l_discount, api.NA),
dc.fillna(0),
ts < (ibis.now() + ibis.month(3)),
ts < (ibis.timestamp("2005-01-01") + ibis.month(3)),
# hashing
dc.hash(),
ts.hash(),
# truncate
ts.truncate("y"),
ts.truncate("q"),
ts.truncate("month"),
ts.truncate("d"),
ts.truncate("w"),
ts.truncate("h"),
ts.truncate("minute"),
]
timestamp_fields = ["year", "month", "day", "hour", "minute", "second", "millisecond", "microsecond", "week"]
for field in timestamp_fields:
if hasattr(ts, field):
exprs.append(getattr(ts, field)())
offset = getattr(ibis, field)(2)
exprs.append(ts + offset)
exprs.append(ts - offset)
proj_exprs = [expr.name("e%d" % i) for i, expr in enumerate(exprs)]
projection = table[proj_exprs].limit(10)
projection.execute()
def test_timestamp_functions(self):
from datetime import datetime
v = L("2015-09-01 14:48:05.359").cast("timestamp")
cases = [
(v.strftime("%Y%m%d"), "20150901"),
(v.year(), 2015),
(v.month(), 9),
(v.day(), 1),
(v.hour(), 14),
(v.minute(), 48),
(v.second(), 5),
(v.millisecond(), 359),
# there could be pathological failure at midnight somewhere, but
# that's okay
(ibis.now().strftime("%Y%m%d %H"), datetime.utcnow().strftime("%Y%m%d %H")),
]
self._check_e2e_cases(cases)
def test_timestamp_functions(self):
from datetime import datetime
v = L('2015-09-01 14:48:05.359').cast('timestamp')
cases = [
(v.strftime('%Y%m%d'), '20150901'),
(v.year(), 2015),
(v.month(), 9),
(v.day(), 1),
(v.hour(), 14),
(v.minute(), 48),
(v.second(), 5),
(v.millisecond(), 359),
# there could be pathological failure at midnight somewhere, but
# that's okay
(ibis.now().strftime('%Y%m%d %H'),
datetime.utcnow().strftime('%Y%m%d %H'))
]
self._check_e2e_cases(cases)
def test_now():
expr = ibis.now()
result = cs_compile.compile(expr)
expected = "SELECT CURRENT_TIMESTAMP() AS `tmp`"
assert result == expected
def test_add_column_depricated(table):
with pytest.deprecated_call():
table.add_column(ibis.now().cast('date'), name='date')
def test_add_column_proxies_to_mutate(table):
result = table.add_column(ibis.now().cast('date'), name='date')
expected = table.mutate(date=ibis.now().cast('date'))
assert_equal(result, expected)
def now() -> float:
"""Returns the timestamp for the current time."""
return ibis.now()
def test_now(con):
expr = ibis.now().strftime('%Y%m%d %H')
result = con.execute(expr)
expected = datetime.datetime.utcnow().strftime('%Y%m%d %H')
assert result == expected
def test_timestamp_now(con, translate):
expr = ibis.now()
# now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
assert translate(expr) == 'now()'
def test_now():
expr = ibis.now()
result = ibis.bigquery.compile(expr)
expected = 'SELECT CURRENT_TIMESTAMP() AS `tmp`'
assert result == expected
def test_timestamp_now(self):
cases = [
(ibis.now(), 'now()')
]
self._check_expr_cases(cases)
def test_timestamp_now():
expr = ibis.now()
result = translate(expr)
assert result == "now()"
def test_timestamp_now(self):
cases = [
(ibis.now(), 'now()')
]
self._check_expr_cases(cases)
def test_timestamp_now(con, translate):
expr = ibis.now()
# now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
assert translate(expr) == 'now()'
def test_now(con):
expr = ibis.now().strftime('%Y%m%d %H')
result = con.execute(expr)
expected = datetime.datetime.utcnow().strftime('%Y%m%d %H')
assert result == expected
[alltypes.count().name('count')]))
expr2 = expr.string_col.cast('double')
query = ibis.clickhouse.compile(expr2)
expected = """SELECT CAST(`string_col` AS Float64) AS `tmp`
FROM (
SELECT `string_col`, count(*) AS `count`
FROM {0}.`functional_alltypes`
GROUP BY `string_col`
) t0"""
assert query == expected.format(db.name)
@pytest.mark.parametrize(
('expr', 'expected'),
[(ibis.now(), 'SELECT now() AS `tmp`'),
(ibis.literal(1) + ibis.literal(2), 'SELECT 1 + 2 AS `tmp`')])
def test_scalar_exprs_no_table_refs(expr, expected):
assert ibis.clickhouse.compile(expr) == expected
def test_expr_list_no_table_refs():
exlist = ibis.api.expr_list([
ibis.literal(1).name('a'),
ibis.now().name('b'),
ibis.literal(2).log().name('c')
])
result = ibis.clickhouse.compile(exlist)
expected = """\
SELECT 1 AS `a`, now() AS `b`, log(2) AS `c`"""
assert result == expected
expr2 = expr.string_col.cast('double')
query = ibis.clickhouse.compile(expr2)
expected = """SELECT CAST(`string_col` AS Float64) AS `tmp`
FROM (
SELECT `string_col`, count(*) AS `count`
FROM {0}.`functional_alltypes`
GROUP BY `string_col`
) t0"""
assert query == expected.format(db.name)
@pytest.mark.parametrize(
('expr', 'expected'),
[
(ibis.now(), 'SELECT now() AS `tmp`'),
(ibis.literal(1) + ibis.literal(2), 'SELECT 1 + 2 AS `tmp`'),
],
)
def test_scalar_exprs_no_table_refs(expr, expected):
assert ibis.clickhouse.compile(expr) == expected
def test_expr_list_no_table_refs():
exlist = ibis.api.expr_list(
[
ibis.literal(1).name('a'),
ibis.now().name('b'),
ibis.literal(2).log().name('c'),
]
)
def test_now():
expr = ibis.now()
result = ibis.bigquery.compile(expr)
expected = 'SELECT CURRENT_TIMESTAMP() AS `tmp`'
assert result == expected
def test_timestamp_now(translate):
expr = ibis.now()
assert translate(expr) == 'now()'
