Obtaining query plans for Ibis expression

Question

I'm trying to obtain query plans from the DuckDB backend for an Ibis expression using the con.explain interface which is throwing an error. Am I using the con.explain function correctly?

def init_ddb_from_csv(db_filename, tablename, csv_filename, **kwargs):
    """
    Load from the csv file into a DuckDB database.
    
    db_filename: Name of the database
    tablename: Table to load to
    csv_filename: CSV file to load from
    **kwargs: Options for DuckDB's read_csv function, see https://duckdb.org/docs/data/csv/overview
    """
    import duckdb
    duckdb_con = duckdb.connect(db_filename)
    read_csv_args_list = ["'{}'".format(csv_filename)]
    for key, value in kwargs.items():
        read_csv_args_list.append("{0} = {1}".format(key, value))
    read_csv_args = ','.join(read_csv_args_list)
    sql_stmt = "CREATE TABLE {} AS SELECT * FROM read_csv({}, AUTO_DETECT=TRUE)".format(tablename, read_csv_args)
    print(sql_stmt)
    duckdb_con.sql(sql_stmt)
    duckdb_con.close()

dbname = "us_accidents.db"
tablename = "accidents"
csv_filename = "US_Accidents_Dec21_updated.csv"
init_ddb_from_csv(dbname, tablename, csv_filename)

con = ibis.duckdb.connect(dbname)
table = con.table(tablename)
con.explain(table.head())

Throws the following error:

AttributeError                            Traceback (most recent call last)
File ~/workspace/csv/VENV/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1408, in Connection.execute(self, statement, parameters, execution_options)
   1407 try:
-> 1408     meth = statement._execute_on_connection
   1409 except AttributeError as err:

AttributeError: 'str' object has no attribute '_execute_on_connection'

The above exception was the direct cause of the following exception:

ObjectNotExecutableError                  Traceback (most recent call last)
Cell In[29], line 1
----> 1 con.explain(table.head())

File ~/workspace/csv/VENV/lib/python3.8/site-packages/ibis/backends/base/sql/__init__.py:373, in BaseSQLBackend.explain(self, expr, params)
    369     query = expr
    371 statement = f'EXPLAIN {query}'
--> 373 with self._safe_raw_sql(statement) as cur:
    374     result = self._get_list(cur)
    376 return '\n'.join(['Query:', util.indent(query, 2), '', *result])

File /usr/lib/python3.8/contextlib.py:113, in _GeneratorContextManager.__enter__(self)
    111 del self.args, self.kwds, self.func
    112 try:
--> 113     return next(self.gen)
    114 except StopIteration:
    115     raise RuntimeError("generator didn't yield") from None

File ~/workspace/csv/VENV/lib/python3.8/site-packages/ibis/backends/base/sql/alchemy/__init__.py:130, in BaseAlchemyBackend._safe_raw_sql(self, *args, **kwargs)
    127 @contextlib.contextmanager
    128 def _safe_raw_sql(self, *args, **kwargs):
    129     with self.begin() as con:
--> 130         yield con.execute(*args, **kwargs)

File ~/workspace/csv/VENV/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1410, in Connection.execute(self, statement, parameters, execution_options)
   1408     meth = statement._execute_on_connection
   1409 except AttributeError as err:
-> 1410     raise exc.ObjectNotExecutableError(statement) from err
   1411 else:
   1412     return meth(
   1413         self,
   1414         distilled_parameters,
   1415         execution_options or NO_OPTIONS,
   1416     )

ObjectNotExecutableError: Not an executable object: 'EXPLAIN SELECT t0."ID", t0."Severity", t0."Start_Time", t0."End_Time", t0."Start_Lat", t0."Start_Lng", t0."End_Lat", t0."End_Lng", t0."Distance(mi)", t0."Description", t0."Number", t0."Street", t0."Side", t0."City", t0."County", t0."State", t0."Zipcode", t0."Country", t0."Timezone", t0."Airport_Code", t0."Weather_Timestamp", t0."Temperature(F)", t0."Wind_Chill(F)", t0."Humidity(%)", t0."Pressure(in)", t0."Visibility(mi)", t0."Wind_Direction", t0."Wind_Speed(mph)", t0."Precipitation(in)", t0."Weather_Condition", t0."Amenity", t0."Bump", t0."Crossing", t0."Give_Way", t0."Junction", t0."No_Exit", t0."Railway", t0."Roundabout", t0."Station", t0."Stop", t0."Traffic_Calming", t0."Traffic_Signal", t0."Turning_Loop", t0."Sunrise_Sunset", t0."Civil_Twilight", t0."Nautical_Twilight", t0."Astronomical_Twilight" \nFROM accidents AS t0\n LIMIT :param_1'

Answer 1

sorry for the slow response here -- I recommend opening a GitHub issue, we don't miss those as frequently!

I also see this -- checking the documentation, the only reference I can find to this is in v0.4.0's release notes adding this method for the Impala backend. I'm guessing this is a relic from then not implemented for any other backend, but could be wrong. I'll ask someone with more knowledge to check on this!

Release notes mention: https://ibis-project.org/release_notes/?h=release#04-2015-08-14