How to implement a Duckdb SQLDatabase?

[cboettig]

Checked other resources

• I added a very descriptive title to this question.
• I searched the LangChain documentation with the integrated search.
• I used the GitHub search to find a similar question and didn't find it.

Commit to Help

• I commit to help with one of those options 👆

Example Code

from langchain_community.utilities import SQLDatabase
parquet = "https://data.source.coop/cboettig/pad-us-3/pad-mobi.parquet"
q = f"CREATE OR REPLACE VIEW pad AS SELECT * FROM read_parquet('{parquet}')"
db = SQLDatabase.from_uri("duckdb:///:memory:")
db.run(q)
db.get_usable_table_names() # nothing

But duckdb works fine:

import duckdb
duckdb.sql(q)
duckdb.sql("show tables;")

Description

I'm trying to follow the SQL agent quickstart documentation to use a duckdb connection. langchain appears to initialize my db without error using SQLDatabase.from_uri("duckdb:///:memory:"), and runs a simple query creating a view from a remote, public parquet file, but then langchain shows only an empty list of table names.

The same query run with duckdb python package immediately shows the table names as expected.

System Info

System Information
------------------
> OS:  Linux
> OS Version:  #202401051437~1704728131~22.04~24d69e2 SMP PREEMPT_DYNAMIC Mon J
> Python Version:  3.10.12 (main, Nov 20 2023, 15:14:05) [GCC 11.4.0]

Package Information
-------------------
> langchain_core: 0.1.40
> langchain: 0.1.14
> langchain_community: 0.0.31
> langsmith: 0.1.40
> langchain_openai: 0.1.1
> langchain_text_splitters: 0.0.1

Packages not installed (Not Necessarily a Problem)
--------------------------------------------------
The following packages were not found:

> langgraph
> langserve

[dosubot[bot]]

The discrepancy you're experiencing with LangChain and DuckDB, particularly with the get_usable_table_names() method returning an empty list, might be attributed to how LangChain's SQLDatabase class and DuckDB handle in-memory databases and schemas.

LangChain's SQLDatabase class uses SQLAlchemy to initialize a database connection and to execute SQL commands. The get_usable_table_names() method determines usable table names based on inclusion and exclusion lists, defaulting to all tables if neither list is provided. However, the method's ability to recognize tables could be affected by how the search path or schema is set for the in-memory database.

DuckDB and SQLAlchemy have different approaches to handling in-memory databases, particularly in setting the search path or schema. This difference could lead to discrepancies in table recognition. Specifically, the CREATE OR REPLACE VIEW command might successfully create the view in DuckDB, but the view might not be recognized by LangChain's method due to issues with setting the search path as expected.

To address this issue, ensuring the correct setting of the search path when using LangChain's SQLDatabase class with DuckDB might be necessary. However, this could require modifications to the SQLDatabase class or its interaction with DuckDB, which might be beyond simple user-end adjustments without altering the LangChain codebase.

This explanation aims to clarify the potential cause of the issue you're facing. If further questions arise or additional clarification is needed, feel free to ask.

Sources

• libs/community/langchain_community/utilities/sql_database.py

About Dosu

This response is meant to be useful and save you time. It is not meant to be a precise solution, but rather a starting point for your own research.

Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

If you want to continue the conversation, start your reply with @dosu-bot.

[cboettig]

This is partly related but not entirely accurate. The issue does appear to be related to in-memory tables and views, neither of which langchain tools seem to handle. But our Dosubot is mistaken to pin this on SQLAlchemy -- for instance, ibis-framework uses SQLAlchemy too and works absolutely seamlessly with in-memory tables and views. In fact we can even confirm this is not an issue in langchain's own syntax using db.run("show tables;") above.

This is a problem with db.get_usable_table_names(). It doesn't understand views, and it seems to not understand how to read from in-memory tables either. It also seems to be unable to recognize new tables created after the db object is initialized.

[dosubot[bot]]

The issue you're encountering with get_usable_table_names() not recognizing views and in-memory tables in LangChain's SQLDatabase class seems to stem from the handling of the view_support parameter. By default, this parameter is set to False, which means views, and potentially in-memory tables, are not included in the list of tables returned by get_usable_table_names().

To address this, you should instantiate the SQLDatabase object with view_support=True. This adjustment enables the class to recognize views and in-memory tables. Here's how you can do it:

from sqlalchemy import create_engine
from libs.community.langchain_community.utilities.sql_database import SQLDatabase

# Create an SQLAlchemy engine instance for DuckDB
engine = create_engine('duckdb:///path/to/your/database.duckdb')

# Initialize SQLDatabase with view_support enabled
sql_database = SQLDatabase(engine=engine, view_support=True)

# Now, get_usable_table_names should include views and in-memory tables
usable_table_names = sql_database.get_usable_table_names()
print(usable_table_names)

Make sure to replace 'duckdb:///path/to/your/database.duckdb' with the correct path to your DuckDB database. By enabling view_support, the SQLDatabase instance will include views (and potentially in-memory tables treated similarly to views in DuckDB) in the list of usable tables.

For more details, you can refer to the SQLDatabase class source code.

This approach should help resolve the issue with get_usable_table_names(). If you encounter further issues or have additional questions, feel free to reach out.

Sources

• libs/community/langchain_community/utilities/sql_database.py
• libs/langchain/langchain/chains/sql_database/prompt.py

About Dosu

This response is meant to be useful and save you time. It is not meant to be a precise solution, but rather a starting point for your own research.

Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

If you want to continue the conversation, start your reply with @dosu-bot.

[cboettig]

Just to note, the answer still isn't really complete. Tables added to the database after SQLDatabase() is called (e.g. using .run() method), do not ever appear to be visible. However, we can pre-populate a database, including with views, and then create the connection.

Even that is not a complete solution, since we for an in-memory connection, the connection is not persistent. It seems we must initialize a connection using an SQLalchemy (in memory), create the views, and then pass the existing in-memory engine to SQLDatabase()

[hxt365]

Langchain should fix this issue as the usage of DuckDB is increasing