So far, we have seen Ibis in interactive mode. Interactive mode (also known as eager mode) makes Ibis return the results of an operation immediately.
In most cases, instead of using interactive mode, it makes more sense to use the default lazy mode. In lazy mode, Ibis won't be executing the operations automatically, but instead, will generate an expression to be executed at a later time.
Let's see this in practice, starting with the same database as in previous tutorials.
from tutorial_utils import setup
setup()
import ibis
connection = ibis.sqlite.connect("geography.db")
countries = connection.table("countries")
In previous tutorials, we set interactive mode to True
, and we obtained the result
of every operation.
ibis.options.interactive = True
countries["name", "continent", "population"].limit(3)
But now let's see what happens if we leave the interactive
option to False
(the default),
and we operate in lazy mode.
ibis.options.interactive = False
countries["name", "continent", "population"].limit(3)
What we find is the graph of the expressions that would return the desired result instead of the result itself.
Let's analyze the expressions in the graph:
countries
table (all rows and all columns)name
, continent
and population
columns3
rowsNow consider that the data is in a database, possibly in a different host than the one executing Ibis. Also consider that the results returned to the user need to be moved to the memory of the host executing Ibis.
When using interactive (or eager) mode, if we perform one operation at a time, we would do:
name
, continent
and population
3
This is not very efficient. If you consider that the table can have millions of rows, backed by a big data system like Spark or Impala, this may not even be possible (not enough memory to load all the data).
The solution is to use lazy mode. In lazy mode, instead of obtaining the results after each operation, we build an expression (a graph) of all the operations that need to be done. After all the operations are recorded, the graph is sent to the backend which will perform the operation in an efficient way - only moving to memory the required data.
You can think of this as writing a shopping list and requesting someone to go to the supermarket and buy everything you need once the list is complete. As opposed as getting someone to bring all the products of the supermarket to your home and then return everything you don't want.
Let's continue with our example, save the expression in a variable countries_expression
, and check its type.
countries_expression = countries["name", "continent", "population"].limit(3)
type(countries_expression)
The type is an Ibis TableExpr
, since the result is a table (in a broad way, you can consider it a dataframe).
Now we have our query instructions (our expression, fetching only 3 columns and 3 rows) in the variable countries_expression
.
At this point, nothing has been requested from the database. We have defined what we want to extract, but we didn't
request it from the database yet. We can continue building our expression if we haven't finished yet. Or once we
are done, we can simply request it from the database using the method .to_pandas()
.
countries_expression.to_pandas()
We can build other types of expressions, for example, one that instead of returning a table, returns a columns.
population_in_millions = (countries["population"] / 1_000_000).name(
"population_in_millions"
)
population_in_millions
If we check its type, we can see how it is a FloatingColumn
expression.
type(population_in_millions)
We can combine the previous expression to be a column of a table expression.
countries["name", "continent", population_in_millions].limit(3)
Since we are in lazy mode (not interactive), those expressions don't request any data from the database
unless explicitly requested with .to_pandas()
.
For SQL backends (and for others when it makes sense), the query sent to the database can be logged.
This can be done by setting the verbose
option to True
.
ibis.options.verbose = True
countries["name", "continent", population_in_millions].limit(3).to_pandas()
By default, the logging is done to the terminal, but we can process the query with a custom function. This allows us to save executed queries to a file, save to a database, send them to a web service, etc.
For example, to save queries to a file, we can write a custom function that given a query, saves it to a log file.
from pathlib import Path
def log_query_to_file(query: str) -> None:
"""Log queries to `./tutorial_queries.log`."""
fname = Path() / "tutorial_queries.log"
query = query.replace("\n", " ")
with fname.open(mode="a") as f:
# log on a single line
f.write(f"{query}\n")
Then we can set the verbose_log
option to the custom function, execute one query,
wait one second, and execute another query.
ibis.options.verbose_log = log_query_to_file
countries.to_pandas()
countries["name", "continent", population_in_millions].limit(3).to_pandas()
This has created a log file in $PWD/tutorial_queries.log
where the executed queries have been logged.
!cat -n $PWD/tutorial_queries.log