Ibis for SQL Programmers¶
Among other things, Ibis provides a full-featured replacement for SQL
SELECT queries, but expressed with Python code that is:
Type-checked and validated as you go. No more debugging cryptic database errors; Ibis catches your mistakes right away.
Easier to write. Pythonic function calls with tab completion in IPython.
More composable. Break complex queries down into easier-to-digest pieces
Easier to reuse. Mix and match Ibis snippets to create expressions tailored for your analysis.
We intend for all SELECT queries to be fully portable to Ibis. Coverage of
other DDL statements (e.g. CREATE TABLE or INSERT) may vary from
engine to engine.
This document will use the Impala SQL compiler (i.e. ibis.impala.compile)
for convenience, but the code here is portable to whichever system you are
using Ibis with.
Note: If you find any SQL idioms or use cases in your work that are not represented here, please reach out so we can add more to this guide!
Projections: select/add/remove columns¶
All tables in Ibis are immutable. To select a subset of a table’s columns, or to add new columns, you must produce a new table by means of a projection.
In [1]: t = ibis.table([('one', 'string'),
...: ('two', 'double'),
...: ('three', 'int32')], 'my_data')
...:
In [2]: t
Out[2]:
UnboundTable[table]
name: my_data
schema:
one : string
two : float64
three : int32
In SQL, you might write something like:
SELECT two, one
FROM my_data
In Ibis, this is
In [3]: proj = t['two', 'one']
or
In [4]: proj = t.projection(['two', 'one'])
This generates the expected SQL:
In [5]: print(ibis.impala.compile(proj))
SELECT `two`, `one`
FROM my_data
What about adding new columns? To form a valid projection, all column expressions must be named. Let’s look at the SQL:
SELECT two, one, three * 2 AS new_col
FROM my_data
The last expression is written:
In [6]: new_col = (t.three * 2).name('new_col')
Now, we have:
In [7]: proj = t['two', 'one', new_col]
In [8]: print(ibis.impala.compile(proj))
SELECT `two`, `one`, `three` * 2 AS `new_col`
FROM my_data
mutate: Add or modify columns easily¶
Since adding new columns or modifying existing columns is so common, there is a
convenience method mutate:
In [9]: mutated = t.mutate(new_col=t.three * 2)
Notice that using the name was not necessary here because we’re using
Python keywords to provide the name. Indeed:
In [10]: print(ibis.impala.compile(mutated))
SELECT *, `three` * 2 AS `new_col`
FROM my_data
If you modify an existing column with mutate it will list out all the other
columns:
In [11]: mutated = t.mutate(two=t.two * 2)
In [12]: print(ibis.impala.compile(mutated))
SELECT `one`, `two` * 2 AS `two`, `three`
FROM my_data
SELECT * equivalent¶
Especially in combination with relational joins, it’s convenient to be able to
select all columns in a table using the SELECT * construct. To do this, use
the table expression itself in a projection:
In [13]: proj = t[t]
In [14]: print(ibis.impala.compile(proj))
SELECT *
FROM my_data
This is how mutate is implemented. The example above
t.mutate(new_col=t.three * 2) can be written as a normal projection:
In [15]: proj = t[t, new_col]
In [16]: print(ibis.impala.compile(proj))
SELECT *, `three` * 2 AS `new_col`
FROM my_data
Let’s consider a table we might wish to join with t:
In [17]: t2 = ibis.table([('key', 'string'),
....: ('value', 'double')], 'dim_table')
....:
Now let’s take the SQL:
SELECT t0.*, t0.two - t1.value AS diff
FROM my_data t0
INNER JOIN dim_table t1
ON t0.one = t1.key
To write this with Ibis, it is:
In [18]: diff = (t.two - t2.value).name('diff')
In [19]: joined = t.join(t2, t.one == t2.key)[t, diff]
And verify the generated SQL:
In [20]: print(ibis.impala.compile(joined))
SELECT t0.*, t0.`two` - t1.`value` AS `diff`
FROM my_data t0
INNER JOIN dim_table t1
ON t0.`one` = t1.`key`
Using functions in projections¶
If you pass a function instead of a string or Ibis expression in any projection context, it will be invoked with the “parent” table as its argument. This can help significantly when composing complex operations. Consider this SQL:
SELECT one, avg(abs(the_sum)) AS mad
FROM (
SELECT one, three, sum(two) AS the_sum
FROM my_data
GROUP BY 1, 2
) t0
GROUP BY 1
This can be written as one chained expression:
In [21]: expr = (t.group_by(['one', 'three'])
....: .aggregate(the_sum=t.two.sum())
....: .group_by('one')
....: .aggregate(mad=lambda x: x.the_sum.abs().mean()))
....:
Indeed:
In [22]: print(ibis.impala.compile(expr))
SELECT `one`, avg(abs(`the_sum`)) AS `mad`
FROM (
SELECT `one`, `three`, sum(`two`) AS `the_sum`
FROM my_data
GROUP BY 1, 2
) t0
GROUP BY 1
Filtering / WHERE¶
You can add filter clauses to a table expression either by indexing with []
(like pandas) or use the filter method:
In [23]: filtered = t[t.two > 0]
In [24]: print(ibis.impala.compile(filtered))
SELECT *
FROM my_data
WHERE `two` > 0
filter can take a list of expressions, which must all be satisfied for a
row to be included in the result:
In [25]: filtered = t.filter([t.two > 0,
....: t.one.isin(['A', 'B'])])
....:
In [26]: print(ibis.impala.compile(filtered))
SELECT *
FROM my_data
WHERE (`two` > 0) AND
(`one` IN ('B', 'A'))
To compose boolean expressions with AND or OR, use the respective &
and | operators:
In [27]: cond = (t.two < 0) | ((t.two > 0) | t.one.isin(['A', 'B']))
In [28]: filtered = t[cond]
In [29]: print(ibis.impala.compile(filtered))
SELECT *
FROM my_data
WHERE (`two` < 0) OR ((`two` > 0) OR `one` IN ('B', 'A'))
Aggregation / GROUP BY¶
To aggregate a table, you need:
Zero or more grouping expressions (these can be column names)
One or more aggregation expressions
Let’s look at the aggregate method on tables:
In [30]: stats = [t.two.sum().name('total_two'),
....: t.three.mean().name('avg_three')]
....:
In [31]: agged = t.aggregate(stats)
If you don’t use any group expressions, the result will have a single row with your statistics of interest:
In [32]: agged.schema()
Out[32]:
ibis.Schema {
total_two float64
avg_three float64
}
In [33]: print(ibis.impala.compile(agged))
SELECT sum(`two`) AS `total_two`, avg(`three`) AS `avg_three`
FROM my_data
To add groupings, use either the by argument of aggregate or use the
group_by construct:
In [34]: agged2 = t.aggregate(stats, by='one')
In [35]: agged3 = t.group_by('one').aggregate(stats)
In [36]: print(ibis.impala.compile(agged3))
SELECT `one`, sum(`two`) AS `total_two`, avg(`three`) AS `avg_three`
FROM my_data
GROUP BY 1
Non-trivial grouping keys¶
You can use any expression (or function, like in projections) deriving from the table you are aggregating. The only constraint is that the expressions must be named. Let’s look at an example:
In [37]: events = ibis.table([('ts', 'timestamp'),
....: ('event_type', 'int32'),
....: ('session_id', 'int64')],
....: 'web_events')
....:
Suppose we wanted to total up event types by year and month:
In [38]: keys = [events.ts.year().name('year'),
....: events.ts.month().name('month')]
....:
In [39]: sessions = events.session_id.nunique()
In [40]: stats = (events.group_by(keys)
....: .aggregate(total=events.count(),
....: sessions=sessions))
....:
Now we have:
In [41]: print(ibis.impala.compile(stats))
SELECT extract(`ts`, 'year') AS `year`, extract(`ts`, 'month') AS `month`,
count(DISTINCT `session_id`) AS `sessions`, count(*) AS `total`
FROM web_events
GROUP BY 1, 2
Aggregates considering table subsets¶
In analytics is it common to compare statistics from different subsets of a table. Let’s consider a dataset containing people’s name, age, gender, and nationality:
In [42]: pop = ibis.table([('name', 'string'),
....: ('country', 'string'),
....: ('gender', 'string'),
....: ('age', 'int16')], 'population')
....:
Now, suppose you wanted to know for each country:
Average overall age
Average male age
Average female age
Total number of persons
In SQL, you may write:
SELECT country,
count(*) AS num_persons,
AVG(age) AS avg_age
AVG(CASE WHEN gender = 'M'
THEN age
ELSE NULL
END) AS avg_male,
AVG(CASE WHEN gender = 'F'
THEN age
ELSE NULL
END) AS avg_female,
FROM population
GROUP BY 1
Ibis makes this much simpler by giving you where option in aggregation
functions:
In [43]: expr = pop.group_by('country').aggregate(
....: num_persons=pop.count(),
....: avg_age=pop.age.mean(),
....: avg_male=pop.age.mean(where=pop.gender == 'M'),
....: avg_female=pop.age.mean(where=pop.gender == 'F')
....: )
....:
This indeed generates the correct SQL. Note that SQL engines handle NULL
values differently in aggregation functions, but Ibis will write the SQL
expression that is correct for your query engine.
In [44]: print(ibis.impala.compile(expr))
SELECT `country`, avg(`age`) AS `avg_age`,
avg(CASE WHEN `gender` = 'F' THEN `age` ELSE NULL END) AS `avg_female`,
avg(CASE WHEN `gender` = 'M' THEN `age` ELSE NULL END) AS `avg_male`,
count(*) AS `num_persons`
FROM population
GROUP BY 1
count(*) convenience: size()¶
Computing group frequencies is so common that, like pandas, we have a method
size that is a shortcut for the count(*) idiom:
In [45]: freqs = events.group_by(keys).size()
In [46]: print(ibis.impala.compile(freqs))
SELECT extract(`ts`, 'year') AS `year`, extract(`ts`, 'month') AS `month`,
count(*) AS `count`
FROM web_events
GROUP BY 1, 2
Frequency table convenience: value_counts¶
Consider the SQL idiom:
SELECT some_column_expression, count(*)
FROM table
GROUP BY 1
This is so common that, like pandas, there is a generic array method
value_counts which does this for us:
In [47]: expr = events.ts.year().value_counts()
In [48]: print(ibis.impala.compile(expr))
SELECT extract(`ts`, 'year') AS `year`, count(*) AS `count`
FROM web_events
GROUP BY 1
HAVING clause¶
The SQL HAVING clause enables you to filter the results of an aggregation
based on some group-wise condition holding true. For example, suppose we wanted
to limit our analysis to groups containing at least 1000 observations:
SELECT one, sum(two) AS total
FROM my_data
GROUP BY 1
HAVING count(*) >= 1000
With Ibis, you can do:
In [49]: expr = (t.group_by('one')
....: .having(t.count() >= 1000)
....: .aggregate(t.two.sum().name('total')))
....:
In [50]: print(ibis.impala.compile(expr))
SELECT `one`, sum(`two`) AS `total`
FROM my_data
GROUP BY 1
HAVING count(*) >= 1000
Sorting / ORDER BY¶
To sort a table, use the sort_by method along with either column names or
expressions that indicate the sorting keys:
In [51]: sorted = events.sort_by([events.ts.year(),
....: events.ts.month()])
....:
In [52]: print(ibis.impala.compile(sorted))
SELECT *
FROM web_events
ORDER BY extract(`ts`, 'year'), extract(`ts`, 'month')
The default for sorting is in ascending order. To reverse the sort direction of
any key, either wrap it in ibis.desc or pass a tuple with False as the
second value:
In [53]: sorted = (events.sort_by([ibis.desc('event_type'),
....: (events.ts.month(), False)])
....: .limit(100))
....:
In [54]: print(ibis.impala.compile(sorted))
SELECT *
FROM web_events
ORDER BY `event_type` DESC, extract(`ts`, 'month') DESC
LIMIT 100
LIMIT and OFFSET¶
This one is easy. The table limit function truncates a table to the
indicates number of rows. So if you only want the first 1000 rows (which may
not be deterministic depending on the SQL engine), you can do:
In [55]: limited = t.limit(1000)
In [56]: print(ibis.impala.compile(limited))
SELECT *
FROM my_data
LIMIT 1000
The offset option in limit skips rows. So if you wanted rows 11 through
20, you could do:
In [57]: limited = t.limit(10, offset=10)
In [58]: print(ibis.impala.compile(limited))
SELECT *
FROM my_data
LIMIT 10 OFFSET 10
Common column expressions¶
See the full API documentation for all of the available value methods and tools for creating value expressions. We mention a few common ones here as they relate to common SQL queries.
Type casts¶
Ibis’s type system is independent of any SQL system. You cast Ibis expressions from one Ibis type to another. For example:
In [59]: expr = t.mutate(date=t.one.cast('timestamp'),
....: four=t.three.cast('double'))
....:
In [60]: print(ibis.impala.compile(expr))
SELECT *, CAST(`one` AS timestamp) AS `date`,
CAST(`three` AS double) AS `four`
FROM my_data
CASE statements¶
SQL dialects typically support one or more kind of CASE statements. The
first is the simple case that compares against exact values of an expression.
CASE expr
WHEN value_1 THEN result_1
WHEN value_2 THEN result_2
ELSE default
END
Value expressions in Ibis have a case method that allows us to emulate
these semantics:
In [61]: case = (t.one.cast('timestamp')
....: .year()
....: .case()
....: .when(2015, 'This year')
....: .when(2014, 'Last year')
....: .else_('Earlier')
....: .end())
....:
In [62]: expr = t.mutate(year_group=case)
In [63]: print(ibis.impala.compile(expr))
SELECT *,
CASE extract(CAST(`one` AS timestamp), 'year')
WHEN 2015 THEN 'This year'
WHEN 2014 THEN 'Last year'
ELSE 'Earlier'
END AS `year_group`
FROM my_data
The more general case is that of an arbitrary list of boolean expressions and result values:
CASE
WHEN boolean_expr1 THEN result_1
WHEN boolean_expr2 THEN result_2
WHEN boolean_expr3 THEN result_3
ELSE default
END
To do this, use ibis.case:
In [64]: case = (ibis.case()
....: .when(t.two < 0, t.three * 2)
....: .when(t.two > 1, t.three)
....: .else_(t.two)
....: .end())
....:
In [65]: expr = t.mutate(cond_value=case)
In [66]: print(ibis.impala.compile(expr))
SELECT *,
CASE
WHEN `two` < 0 THEN `three` * 2
WHEN `two` > 1 THEN `three`
ELSE `two`
END AS `cond_value`
FROM my_data
There are several places where Ibis builds cases for you in a simplified
way. One example is the ifelse function:
In [67]: switch = (t.two < 0).ifelse('Negative', 'Non-Negative')
In [68]: expr = t.mutate(group=switch)
In [69]: print(ibis.impala.compile(expr))
SELECT *,
CASE WHEN `two` < 0 THEN 'Negative' ELSE 'Non-Negative' END AS `group`
FROM my_data
Using NULL in expressions¶
To use NULL in an expression, either use the special ibis.NA value or
ibis.null():
In [70]: pos_two = (t.two > 0).ifelse(t.two, ibis.NA)
In [71]: expr = t.mutate(two_positive=pos_two)
In [72]: print(ibis.impala.compile(expr))
SELECT *, CASE WHEN `two` > 0 THEN `two` ELSE NULL END AS `two_positive`
FROM my_data
Set membership: IN / NOT IN¶
Let’s look again at the population dataset. Suppose you wanted to combine the United States and Canada data into a “North America” category. Here would be some SQL to do it:
CASE
WHEN upper(country) IN ('UNITED STATES', 'CANADA')
THEN 'North America'
ELSE country
END AS refined_group
The Ibis equivalent of IN is the isin method. So we can write:
In [73]: refined = (pop.country.upper()
....: .isin(['UNITED STATES', 'CANADA'])
....: .ifelse('North America', pop.country))
....:
In [74]: expr = pop.mutate(refined_group=refined)
In [75]: print(ibis.impala.compile(expr))
SELECT *,
CASE WHEN upper(`country`) IN ('CANADA', 'UNITED STATES') THEN 'North America' ELSE `country` END AS `refined_group`
FROM population
The opposite of isin is notin.
Constant and literal expressions¶
Consider a SQL expression like:
'foo' IN (column1, column2)
which is equivalent to
column1 = 'foo' OR column2 = 'foo'
To build expressions off constant values, you must first convert the value
(whether a Python string or number) to an Ibis expression using
ibis.literal:
In [76]: t3 = ibis.table([('column1', 'string'),
....: ('column2', 'string'),
....: ('column3', 'double')], 'data')
....:
In [77]: value = ibis.literal('foo')
Once you’ve done this, you can use the literal expression like any other array or scalar expression:
In [78]: has_foo = value.isin([t3.column1, t3.column2])
In [79]: expr = t3.mutate(has_foo=has_foo)
In [80]: print(ibis.impala.compile(expr))
SELECT *, 'foo' IN (`column1`, `column2`) AS `has_foo`
FROM `data`
In many other situations, you can use constants without having to use
ibis.literal. For example, we could add a column containing nothing but the
number 5 like so:
In [81]: expr = t3.mutate(number5=5)
In [82]: print(ibis.impala.compile(expr))
SELECT *, 5 AS `number5`
FROM `data`
IS NULL and IS NOT NULL¶
These are simple: use the isnull and notnull functions respectively,
which yield boolean arrays:
In [83]: indic = t.two.isnull().ifelse('valid', 'invalid')
In [84]: expr = t.mutate(is_valid=indic)
In [85]: print(ibis.impala.compile(expr))
SELECT *,
CASE WHEN `two` IS NULL THEN 'valid' ELSE 'invalid' END AS `is_valid`
FROM my_data
In [86]: agged = (expr
....: [expr.one.notnull()]
....: .group_by('is_valid')
....: .aggregate(three_count=lambda t: t.three.notnull().sum()))
....:
In [87]: print(ibis.impala.compile(agged))
SELECT `is_valid`, sum(`three` IS NOT NULL) AS `three_count`
FROM (
SELECT *,
CASE WHEN `two` IS NULL THEN 'valid' ELSE 'invalid' END AS `is_valid`
FROM my_data
WHERE `one` IS NOT NULL
) t0
GROUP BY 1
BETWEEN¶
The between method on arrays and scalars compiles to the SQL BETWEEN
keyword. The result of between is boolean and can be used with any other
boolean expression:
In [88]: expr = t[t.two.between(10, 50) & t.one.notnull()]
In [89]: print(ibis.impala.compile(expr))
SELECT *
FROM my_data
WHERE (`two` BETWEEN 10 AND 50) AND
(`one` IS NOT NULL)
Joins¶
Ibis supports several kinds of joins between table expressions:
inner_join: maps to SQLINNER JOINcross_join: a cartesian product join with no keys. Equivalent toinner_joinwith no join predicatesleft_join: maps to SQLLEFT OUTER JOINouter_join: maps to SQLFULL OUTER JOINsemi_join: maps to SQLLEFT SEMI JOIN. May or may not be an explicit join type in your query engine.anti_join: maps to SQLLEFT ANTI JOIN. May or may not be an explicit join type in your query engine.
The join table method is by default the same as inner_join.
Let’s look at a couple example tables to see how joins work in Ibis:
In [90]: t1 = ibis.table([('value1', 'double'),
....: ('key1', 'string'),
....: ('key2', 'string')], 'table1')
....:
In [91]: t2 = ibis.table([('value2', 'double'),
....: ('key3', 'string'),
....: ('key4', 'string')], 'table2')
....:
Let’s join on one key:
In [92]: joined = t1.left_join(t2, t1.key1 == t2.key3)
The immediate result of a join does not yet have a set schema. That is determined by the next action that you take. There’s several ways forward from here that address the spectrum of SQL use cases.
Join + projection¶
Consider the SQL:
SELECT t0.*, t1.value2
FROM table1 t0
LEFT OUTER JOIN table2 t1
ON t0.key1 = t1.key3
After one or more joins, you can reference any of the joined tables in a projection immediately after:
In [93]: expr = joined[t1, t2.value2]
In [94]: print(ibis.impala.compile(expr))
SELECT t0.*, t1.`value2`
FROM table1 t0
LEFT OUTER JOIN table2 t1
ON t0.`key1` = t1.`key3`
If you need to compute an expression that involves both tables, you can do that also:
In [95]: expr = joined[t1.key1, (t1.value1 - t2.value2).name('diff')]
In [96]: print(ibis.impala.compile(expr))
SELECT t0.`key1`, t0.`value1` - t1.`value2` AS `diff`
FROM table1 t0
LEFT OUTER JOIN table2 t1
ON t0.`key1` = t1.`key3`
Join + aggregation¶
You can directly aggregate a join without need for projection, which also allows you to form statistics that reference any of the joined tables.
Consider this SQL:
SELECT t0.key1, avg(t0.value1 - t1.value2) AS avg_diff
FROM table1 t0
LEFT OUTER JOIN table2 t1
ON t0.key1 = t1.key3
GROUP BY 1
As you would hope, the code is as follows:
In [97]: avg_diff = (t1.value1 - t2.value2).mean()
In [98]: expr = (t1.left_join(t2, t1.key1 == t2.key3)
....: .group_by(t1.key1)
....: .aggregate(avg_diff=avg_diff))
....:
In [99]: print(ibis.impala.compile(expr))
SELECT t0.`key1`, avg(t0.`value1` - t1.`value2`) AS `avg_diff`
FROM table1 t0
LEFT OUTER JOIN table2 t1
ON t0.`key1` = t1.`key3`
GROUP BY 1
Join with SELECT *¶
If you try to compile or execute a join that has not been projected or aggregated, it will be fully materialized:
In [100]: joined = t1.left_join(t2, t1.key1 == t2.key3)
In [101]: print(ibis.impala.compile(joined))
SELECT *
FROM table1 t0
LEFT OUTER JOIN table2 t1
ON t0.`key1` = t1.`key3`
You can do this explicitly using the materialize method:
In [102]: expr = joined.materialize()
In [103]: print(ibis.impala.compile(expr))
SELECT *
FROM table1 t0
LEFT OUTER JOIN table2 t1
ON t0.`key1` = t1.`key3`
Multiple joins¶
You can join multiple tables together in succession without needing to address any of the above concerns.
In [104]: t3 = ibis.table([('value3', 'double'),
.....: ('key5', 'string')], 'table3')
.....:
In [105]: total = (t1.value1 + t2.value2 + t3.value3).sum()
In [106]: expr = (t1.join(t2, [t1.key1 == t2.key3,
.....: t1.key2 == t2.key4])
.....: .join(t3, t1.key1 == t3.key5)
.....: .group_by([t2.key4, t3.key5])
.....: .aggregate(total=total))
.....:
In [107]: print(ibis.impala.compile(expr))
SELECT t1.`key4`, t2.`key5`,
sum((t0.`value1` + t1.`value2`) + t2.`value3`) AS `total`
FROM table1 t0
INNER JOIN table2 t1
ON (t0.`key1` = t1.`key3`) AND
(t0.`key2` = t1.`key4`)
INNER JOIN table3 t2
ON t0.`key1` = t2.`key5`
GROUP BY 1, 2
Self joins¶
What about when you need to join a table on itself? For example:
SELECT t0.one, avg(t0.two - t1.three) AS metric
FROM my_data t0
INNER JOIN my_data t1
ON t0.one = t1.one
GROUP BY 1
The table view method enables you to form a self-reference that is
referentially distinct in expressions. Now you can proceed normally:
In [108]: t_view = t.view()
In [109]: stat = (t.two - t_view.three).mean()
In [110]: expr = (t.join(t_view, t.three.cast('string') == t_view.one)
.....: .group_by(t.one)
.....: .aggregate(metric=stat))
.....:
In [111]: print(ibis.impala.compile(expr))
SELECT t0.`one`, avg(t0.`two` - t1.`three`) AS `metric`
FROM my_data t0
INNER JOIN my_data t1
ON CAST(t0.`three` AS string) = t1.`one`
GROUP BY 1
Overlapping join keys¶
In many cases the columns being joined between two tables or table expressions have the same name. Consider this example:
In [112]: t4 = ibis.table([('key1', 'string'),
.....: ('key2', 'string'),
.....: ('key3', 'string'),
.....: ('value1', 'double')], 'table4')
.....:
In [113]: t5 = ibis.table([('key1', 'string'),
.....: ('key2', 'string'),
.....: ('key3', 'string'),
.....: ('value2', 'double')], 'table5')
.....:
In these case, we can specify a list of common join keys:
In [114]: joined = t4.join(t5, ['key1', 'key2', 'key3'])
In [115]: expr = joined[t4, t5.value2]
In [116]: print(ibis.impala.compile(expr))
SELECT t0.*, t1.`value2`
FROM table4 t0
INNER JOIN table5 t1
ON (t0.`key1` = t1.`key1`) AND
(t0.`key2` = t1.`key2`) AND
(t0.`key3` = t1.`key3`)
You can mix the overlapping key names with other expressions:
In [117]: joined = t4.join(t5, ['key1', 'key2',
.....: t4.key3.left(4) == t4.key3.left(4)])
.....:
In [118]: expr = joined[t4, t5.value2]
In [119]: print(ibis.impala.compile(expr))
SELECT t0.*, t1.`value2`
FROM table4 t0
INNER JOIN table5 t1
ON (t0.`key1` = t1.`key1`) AND
(t0.`key2` = t1.`key2`) AND
(substr(t0.`key3`, 0 + 1, 4) = substr(t0.`key3`, 0 + 1, 4))
Non-equality join predicates¶
You can join tables with boolean clauses that are not equality. Some query
engines support these efficiently, some inefficiently, or some not at all. In
the latter case, these conditions get moved by Ibis into the WHERE part of
the SELECT query.
In [120]: expr = (t1.join(t2, t1.value1 < t2.value2)
.....: .group_by(t1.key1)
.....: .size())
.....:
In [121]: print(ibis.impala.compile(expr))
SELECT t0.`key1`, count(*) AS `count`
FROM table1 t0
INNER JOIN table2 t1
ON t0.`value1` < t1.`value2`
GROUP BY 1
Other ways to specify join keys¶
You can also pass a list of column names instead of forming boolean expressions:
In [122]: joined = t1.join(t2, [('key1', 'key3'),
.....: ('key2', 'key4')])
.....:
Subqueries¶
Ibis creates inline views and nested subqueries automatically. This section concerns more complex subqueries involving foreign references and other advanced relational algebra.
Subqueries with IN / NOT IN¶
Subquery filters with IN (and NOT IN) are functionally similar to
EXISTS subqueries. Let’s look at some SQL:
SELECT *
FROM events
WHERE user_id IN (
SELECT user_id
FROM purchases
)
This is almost semantically the same as the EXISTS example. Indeed, you can
write with Ibis:
In [130]: cond = events.user_id.isin(purchases.user_id)
In [131]: expr = events[cond]
In [132]: print(ibis.impala.compile(expr))
SELECT *
FROM events
WHERE `user_id` IN (
SELECT `user_id`
FROM purchases
)
Depending on the query engine, the query planner/optimizer will often rewrite
IN or EXISTS subqueries into the same set of relational algebra
operations.
Comparison with scalar aggregates¶
Sometime you want to compare a value with an unconditional aggregate value from a different table. Take the SQL:
SELECT *
FROM table1
WHERE value1 > (
SELECT max(value2)
FROM table2
)
With Ibis, the code is simpler and more pandas-like:
In [133]: expr = t1[t1.value1 > t2.value2.max()]
In [134]: print(ibis.impala.compile(expr))
SELECT *
FROM table1
WHERE `value1` > (
SELECT max(`value2`) AS `max`
FROM table2
)
Conditional aggregates¶
Suppose you want to compare a value with the aggregate value for some common group values between two tables. Here’s some SQL:
SELECT *
FROM table1 t0
WHERE value1 > (
SELECT avg(value2)
FROM table2 t1
WHERE t0.key1 = t1.key3
)
This query computes the average for each distinct value of key3 and uses
the corresponding average for the comparison, rather than the whole-table
average as above.
With Ibis, the code is similar, but you add the correlated filter to the average statistic:
In [135]: stat = t2[t1.key1 == t2.key3].value2.mean()
In [136]: expr = t1[t1.value1 > stat]
In [137]: print(ibis.impala.compile(expr))
SELECT t0.*
FROM table1 t0
WHERE t0.`value1` > (
SELECT avg(t1.`value2`) AS `mean`
FROM table2 t1
WHERE t0.`key1` = t1.`key3`
)
DISTINCT expressions¶
In SQL, the DISTINCT keyword is used in a couple of ways:
Deduplicating identical rows in some
SELECTstatementAggregating on the distinct values of some column expression
Ibis supports both use cases. So let’s have a look. The first case is the
simplest: call distinct on a table expression. First, here’s the SQL:
SELECT DISTINCT *
FROM table1
And the Ibis Python code:
In [138]: expr = t1.distinct()
In [139]: print(ibis.impala.compile(expr))
SELECT DISTINCT *
FROM table1
For distinct aggregates, the most common case is COUNT(DISTINCT ...), which
computes the number of unique values in an expression. So if we’re looking at
the events table, let’s compute the number of distinct event_type
values for each user_id. First, the SQL:
SELECT user_id, COUNT(DISTINCT event_type) AS unique_events
FROM events
GROUP BY 1
In Ibis this is:
In [140]: metric = events.event_type.nunique()
In [141]: expr = (events.group_by('user_id')
.....: .aggregate(unique_events=metric))
.....:
In [142]: print(ibis.impala.compile(expr))
SELECT `user_id`, count(DISTINCT `event_type`) AS `unique_events`
FROM events
GROUP BY 1
Window functions¶
Window functions in SQL allow you to write expressions that involve possibly-ordered groups of a table. Each window function involves one of the following:
An analytic function. Most aggregate functions are valid analytic functions, and there are additional ones such as
LEAD,LAG,NTILE, and others.A
PARTITION BYclause. This may be omitted.An
ORDER BYclause. This may be omitted for many functions.A window frame clause. The default is to use the entire partition.
So you may see SQL like:
AVG(value) OVER (PARTITION BY key1)
Or simply
AVG(value) OVER ()
Ibis will automatically write window clauses when you use aggregate functions in a non-aggregate context. Suppose you wanted to subtract the mean of a column from itself:
In [143]: expr = t.mutate(two_demean=t.two - t.two.mean())
In [144]: print(ibis.impala.compile(expr))
SELECT *, `two` - avg(`two`) OVER () AS `two_demean`
FROM my_data
If you use mutate in conjunction with group_by, it will add a
PARTITION BY to the OVER specification:
In [145]: expr = (t.group_by('one')
.....: .mutate(two_demean=t.two - t.two.mean()))
.....:
In [146]: print(ibis.impala.compile(expr))
SELECT *, `two` - avg(`two`) OVER (PARTITION BY `one`) AS `two_demean`
FROM my_data
For functions like LAG that require an ordering, we can add an order_by
call:
In [147]: expr = (t.group_by('one')
.....: .order_by(t.two)
.....: .mutate(two_first_diff=t.two - t.two.lag()))
.....:
In [148]: print(ibis.impala.compile(expr))
SELECT *,
`two` - lag(`two`) OVER (PARTITION BY `one` ORDER BY `two`) AS `two_first_diff`
FROM my_data
For more precision, you can create a Window object that also includes a
window frame clause:
In [149]: w = ibis.window(group_by='one', preceding=5, following=5)
In [150]: expr = t.mutate(group_demeaned=t.two - t.two.mean().over(w))
In [151]: print(ibis.impala.compile(expr))
SELECT *,
`two` - avg(`two`) OVER (PARTITION BY `one` ROWS BETWEEN 5 PRECEDING AND 5 FOLLOWING) AS `group_demeaned`
FROM my_data
Top-K operations¶
A common SQL idiom is the “top-K” or “top-N” operation: subsetting a dimension by aggregate statistics:
SELECT key1, count(*) AS `count`
FROM table1
GROUP BY 1
ORDER BY `count` DESC
LIMIT 10
Ibis has a special analytic expression topk:
In [152]: expr = t1.key1.topk(10)
This can be evaluated directly, yielding the above query:
In [153]: print(ibis.impala.compile(expr))
SELECT *
FROM (
SELECT `key1`, count(`key1`) AS `count`
FROM table1
GROUP BY 1
) t0
ORDER BY `count` DESC
LIMIT 10
You can also use expr as a filter:
In [154]: expr2 = t1[expr]
In [155]: print(ibis.impala.compile(expr2))
SELECT t0.*
FROM table1 t0
LEFT SEMI JOIN (
SELECT *
FROM (
SELECT `key1`, count(`key1`) AS `count`
FROM table1
GROUP BY 1
) t2
ORDER BY `count` DESC
LIMIT 10
) t1
ON t0.`key1` = t1.`key1`
Date / time data¶
See Timestamp methods for a table of available date/time methods.
For example, we can do:
In [156]: expr = events.mutate(year=events.ts.year(),
.....: month=events.ts.month())
.....:
In [157]: print(ibis.impala.compile(expr))
SELECT *, extract(`ts`, 'month') AS `month`, extract(`ts`, 'year') AS `year`
FROM events
Casting to date / time types¶
In many cases, you can convert string values to datetime / timestamp with
strings.cast('timestamp'), but you may have to do some more reconnaissance
into the data if this does not work.
Timedeltas¶
Ibis has a set of timedelta object that allow you to do date/time arithmetic. For example:
In [158]: expr = events[events.ts > (ibis.now() - ibis.interval(years=1))]
In [159]: print(ibis.impala.compile(expr))
SELECT *
FROM events
WHERE `ts` > date_sub(cast(now() as timestamp), INTERVAL 1 YEAR)
The implementation of each timedelta offset will depend on the query engine.
Buckets and histograms¶
To appear.
Unions¶
SQL dialects often support two kinds of UNION operations:
UNION: the combination of distinct rows from each table.UNION ALL: the combination of all rows from each table, whether or not they are distinct.
The Ibis union function by distinct is a UNION ALL, and you can set
distinct=True to get the normal UNION behavior:
In [160]: expr1 = t1.limit(10)
In [161]: expr2 = t1.limit(10, offset=10)
In [162]: expr = expr1.union(expr2)
In [163]: print(ibis.impala.compile(expr))
SELECT *
FROM table1
LIMIT 10
UNION ALL
SELECT *
FROM table1
LIMIT 10 OFFSET 10
Esoterica¶
This area will be the spillover for miscellaneous SQL concepts and how queries featuring them can be ported to Ibis.
Common table expressions (CTEs)¶
The simplest SQL CTE is a SQL statement that is used multiple times in a
SELECT query, which can be “factored” out using the WITH keyword:
WITH t0 AS (
SELECT region, kind, sum(amount) AS total
FROM purchases
GROUP BY 1, 2
)
SELECT t0.region, t0.total - t1.total
FROM t0
INNER JOIN t0 t1
ON t0.region = t1.region
WHERE t0.kind = 'foo' AND t1.kind = 'bar'
Explicit CTEs are not necessary with Ibis. Let’s look at an example involving joining an aggregated table on itself after filtering:
In [164]: purchases = ibis.table([('region', 'string'),
.....: ('kind', 'string'),
.....: ('user', 'int64'),
.....: ('amount', 'double')], 'purchases')
.....:
In [165]: metric = purchases.amount.sum().name('total')
In [166]: agged = (purchases.group_by(['region', 'kind'])
.....: .aggregate(metric))
.....:
In [167]: left = agged[agged.kind == 'foo']
In [168]: right = agged[agged.kind == 'bar']
In [169]: result = (left.join(right, left.region == right.region)
.....: [left.region,
.....: (left.total - right.total).name('diff')])
.....:
Ibis automatically creates a CTE for agged:
In [170]: print(ibis.impala.compile(result))
SELECT t0.`region`, t0.`total` - t1.`total` AS `diff`
FROM (
SELECT `region`, `kind`, sum(`amount`) AS `total`
FROM purchases
WHERE `kind` = 'foo'
GROUP BY 1, 2
) t0
INNER JOIN (
SELECT `region`, `kind`, sum(`amount`) AS `total`
FROM purchases
WHERE `kind` = 'bar'
GROUP BY 1, 2
) t1
ON t0.`region` = t1.`region`