使用Ibis的con.explain获取DuckDB查询计划报错，用法是否正确？

我尝试通过Ibis的con.explain接口从DuckDB后端获取Ibis表达式的查询计划，但执行时抛出错误。请问我是否正确使用了con.explain函数？

复现代码 #

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
    """
    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())

报错信息 #

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" 
FROM accidents AS t0
 LIMIT :param_1'

原因分析 #

报错根源在于：Ibis生成了带参数绑定（:param_1）的EXPLAIN SQL字符串，直接传给SQLAlchemy的Connection.execute方法，但SQLAlchemy 2.0+版本不再支持直接执行字符串类型的SQL，必须用sqlalchemy.text()将其包装为可执行对象。而旧版本的Ibis DuckDB后端的explain方法没有处理这种带参数的场景，导致报错。

解决方法 #

方法1：手动编译SQL并使用raw_sql执行 #

raw_sql方法会正确处理带参数的SQL字符串，不需要额外包装：

con = ibis.duckdb.connect(dbname)
table = con.table(tablename)
# 编译Ibis表达式为SQL字符串
query_sql = con.compile(table.head())
# 执行EXPLAIN查询并打印结果
explain_result = con.raw_sql(f"EXPLAIN {query_sql}")
print('\n'.join(row[0] for row in explain_result))

方法2：用SQLAlchemy text包装执行 #

如果需要更底层的控制，可以手动用text包装SQL：

from sqlalchemy import text

con = ibis.duckdb.connect(dbname)
table = con.table(tablename)
query_sql = con.compile(table.head())
# 用text包装EXPLAIN语句
explain_stmt = text(f"EXPLAIN {query_sql}")
# 执行查询
with con.begin() as sqa_con:
    result = sqa_con.execute(explain_stmt)
    print('\n'.join(row[0] for row in result))

方法3：升级Ibis到最新版本 #

Ibis的新版本已经修复了这个兼容性问题，直接升级后即可正常使用con.explain：

pip install --upgrade ibis-framework ibis-duckdb

内容的提问来源于stack exchange，提问作者Aarati K