SQLTableRetrieverQueryEngine and database views instead of tables

[dinonovak]

Question Validation

• [X] I have searched both the documentation and discord for an answer.

Question

I am trying to use SQLTableRetrieverQueryEngine to answer question about data stored in database. As database structure is extremely complex I created database views with data that I would like to query. Executing the code I am not getting any errors but on the other hand looks like obj_index is not really populated with table definitions and columns so querying over multiple tables in not possible and I am getting quite a lot of halucinations as there are non existent columns in queries.

Any hints if using views instead of tables is supported ? and are there any adjustments required.

I am justing just simple code from example below:

from llama_index.objects import SQLTableNodeMapping, ObjectIndex, SQLTableSchema from llama_index.indices.struct_store import SQLTableRetrieverQueryEngine

table_node_mapping = SQLTableNodeMapping(sql_database) table_schema_objs = [(SQLTableSchema(table_name="city_stats")), ...] # one SQLTableSchema for each table

obj_index = ObjectIndex.from_objects( table_schema_objs, table_node_mapping, VectorStoreIndex, )

query_engine = SQLTableRetrieverQueryEngine( sql_database, obj_index.as_retriever(similarity_top_k=1) ) response = query_engine.query("Which city has the highest population?") print(response)

[dosubot[bot]]

🤖

Hello @dinonovak!

Nice to meet you, I'm Dosu, a friendly bot designed to help out with your coding questions and issues. Although I'm not a human, I'm here to assist you with the LlamaIndex as best as I can while we wait for the human maintainers to join us. Feel free to ask me anything and I'll do my best to provide useful answers and guidance. Let's work together to solve your problem!

Yes, it is possible to use database views instead of tables with SQLTableRetrieverQueryEngine in LlamaIndex. The SQLTableRetrieverQueryEngine does not explicitly differentiate between tables and views. It uses SQL queries to interact with the database, and these queries can be executed on both tables and views.

However, there might be some adjustments required depending on the specific use case. For instance, if the view is based on multiple tables or includes computed columns, the SQL queries might need to be adjusted accordingly. Also, if the view does not include all the columns that are required for the application, additional adjustments might be needed.

In your case, you would need to pass the name of the view to the 'table_name' parameter when initializing the SQLTableSchema. Here is an example of how you might do this:

view_name = "your_view_name"
table_schema_objs = [(SQLTableSchema(table_name=view_name)), ...] # one SQLTableSchema for each view

Please note that this is a general answer and the exact adjustments might depend on the specific database schema and the structure of the view. You can refer to the SQLTableRetrieverQueryEngine for more details.

If you continue to experience issues, please provide more details about the structure of your views and the specific errors you are encountering. This will help in providing a more accurate solution.

Sources

• llama_index/indices/struct_store/sql_query.py

---

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.

[vjr]

@dinonovak have you set the option flag to enable view support like this: sql_database = SQLDatabase(sql_engine, view_support=True) ?

[dinonovak]

I have enabled it and also listed only views that I would like to use sql_database = SQLDatabase(engine, include_tables=['view1','view2'], view_support=True)

[dosubot[bot]]

Hi, @dinonovak,

I'm helping the LlamaIndex team manage their backlog and am marking this issue as stale. From what I understand, you opened this issue to report issues with the obj_index not being populated with table definitions and columns when using views with SQLTableRetrieverQueryEngine. There were some discussions around using views with the query engine, and adjustments were suggested depending on the specific use case. However, the issue remains unresolved.

Could you please confirm if this issue is still relevant to the latest version of the LlamaIndex repository? If it is, please let the LlamaIndex team know by commenting on the issue. Otherwise, feel free to close the issue yourself, or it will be automatically closed in 7 days.

Thank you for your understanding and contributions to the LlamaIndex project.

Dosu