Best Query engines and retriever for Structred Data...

[mraguth]

I am using Pandas and SQL query engine for sturctured data. Do we have any other query engines and retrievers for structured data?
Any suggestions please?

[dosubot[bot]]

Some of the best query engines and retrievers for structured data besides Pandas and SQL are:

1. JSONalyze Query Engine
2. Knowledge Graph Query Engine
3. DuckDB Query Engine
4. Recursive Table Retriever
5. BM25 Hybrid Retriever
6. Auto Merging Retriever
7. Composable Retrievers
8. Auto-Retrieval (with Pinecone, Lantern, Chroma, BagelDB, Vectara)
9. Multi-Doc Auto-Retrieval
10. Recursive Node Retrieval
11. Router Retriever
12. Ensemble Retriever
13. Google Retriever
14. Vectara Retriever
15. VideoDB Retriever
16. Zilliz Retriever
17. Amazon Bedrock Retriever
18. Multi-Doc Hybrid Retriever
19. You Retriever
20. Text-to-SQL Retriever
21. DeepMemory (Activeloop) Retriever
22. Pathway Retriever
23. NLSQLTableQueryEngine (from LlamaIndex)
24. SQLTableRetrieverQueryEngine (from LlamaIndex)
25. PGVectorSQLQueryEngine (from LlamaIndex)
26. CitationQueryEngine (from LlamaIndex)
27. CogniswitchQueryEngine (from LlamaIndex)
28. CustomQueryEngine (from LlamaIndex)
29. FLAREInstructQueryEngine (from LlamaIndex)
30. ComposableGraphQueryEngine (from LlamaIndex)
31. SimpleMultiModalQueryEngine (from LlamaIndex)
32. MultiStepQueryEngine (from LlamaIndex)
33. RetrieverQueryEngine (from LlamaIndex)
34. RetryGuidelineQueryEngine (from LlamaIndex)
35. RetryQueryEngine (from LlamaIndex)
36. RetrySourceQueryEngine (from LlamaIndex)
37. RetrieverRouterQueryEngine (from LlamaIndex)
38. RouterQueryEngine (from LlamaIndex)
39. ToolRetrieverRouterQueryEngine (from LlamaIndex)
40. SQLJoinQueryEngine (from LlamaIndex)
41. SQLAutoVectorQueryEngine (from LlamaIndex)
42. SubQuestionQueryEngine (from LlamaIndex)
43. TransformQueryEngine (from LlamaIndex) ^{[1][2][3][4][5]}.

To continue talking to Dosu, mention @dosu.

[mraguth]

@dosu Thank you. Is there any vector database I can save structured data and use 'pandas' query engine or pandas query pipe line? I did see more accuracy via pandas.

[dosubot[bot]]

Yes, LlamaIndex supports queries over structured data, including Pandas DataFrames. You can use the Pandas query engine or query pipeline with LlamaIndex.

For example, you can use the PandasQueryEngine directly for querying Pandas DataFrames ^[1][2].

To continue talking to Dosu, mention @dosu.

[mraguth]

@dosu What vector databases I can use for pandas query engine?

[dosubot[bot]]

You can use the following vector databases for the Pandas query engine with LlamaIndex:

• Astra DB
• AwaDB
• Azure AI Search
• Azure Cognitive Search
• Azure CosmosDB MongoDB
• Bagel
• Cassandra
• ChatGPT Retrieval Plugin
• Chroma
• DashVector
• DeepLake
• DocArray (Hnsw and InMemory)
• Elasticsearch
• Epsilla
• Faiss
• LanceDB
• Lantern
• Metal
• Milvus
• MongoDB Atlas
• MyScale
• Neo4j
• OpenSearch
• Pinecone
• Postgres (PGVector)
• pgvecto.rs
• Qdrant
• Redis
• Rockset
• Simple
• SingleStore
• Supabase
• Tair
• TencentVectorDB
• Timescale
• txtai
• Upstash
• Weaviate
• Zep

These vector stores can be integrated with LlamaIndex to store and query embeddings for input text chunks ^[1][2][3].

To continue talking to Dosu, mention @dosu.

[mraguth]

@dosu Can you give me sample code to save my pandas dataframe in the 'Faiss' vector database?

[dosubot[bot]]

To index a Pandas DataFrame and make it compatible with the JSONalyze Query Engine, you can follow these steps. Below is a complete example from start to end:

1. Install necessary packages:

   !pip install llama-index sqlite-utils

2. Set up the environment:

   import os
   import pandas as pd
   from llama_index.llms.openai import OpenAI
   from llama_index.core.query_engine import JSONalyzeQueryEngine
   from IPython.display import Markdown, display
   
   os.environ["OPENAI_API_KEY"] = "YOUR_KEY_HERE"

3. Create a sample Pandas DataFrame and convert it to a list of dictionaries:

   data = {
       "name": ["John Doe", "Jane Smith", "Michael Johnson"],
       "age": [25, 30, 35],
       "major": ["Computer Science", "Business Administration", "Finance"],
       "email": ["[email protected]", "[email protected]", "[email protected]"]
   }
   
   df = pd.DataFrame(data)
   json_list = df.to_dict(orient='records')

4. Define the LLM:

   llm = OpenAI(model="gpt-3.5-turbo")

5. Create a JSONalyze Query Engine instance:

   json_stats_query_engine = JSONalyzeQueryEngine(
       list_of_dict=json_list,
       llm=llm,
       verbose=True,
   )

6. Run the query:

   query = "What is the maximum age among the individuals?"
   
   display(
       Markdown("> Question: {}".format(query)),
       Markdown("Answer: {}".format(json_stats_query_engine.query(query))),
   )

This code sets up the environment, creates a sample DataFrame, converts it to a list of dictionaries, and runs the workflow to query the data using the JSONalyze Query Engine ^[1][2][3].

To continue talking to Dosu, mention @dosu.

[mraguth]

@dosu... Json query engine is great. However it is failing in responding complex questions like 'compare one data piont with others with in the dataset'. Do you suggest any mitigation strategy like vectoring before pass in to json query engine? or do you suggest any other good query engine other than 'Pandas query engine'? Thank you for your resopnse.

[dosubot[bot]]

Yes, I suggest using the JSONalyze Query Engine for handling complex questions like comparing one data point with others within the dataset. JSONalyze creates an in-memory SQLite table from the JSON list, allowing you to perform SQL queries on the data. This can be particularly useful for statistical analysis and complex queries.

Additionally, you might consider using LlamaIndex for more advanced query handling. LlamaIndex supports various query engines and techniques, including vector stores, summarization, and structured data queries. For instance, you can use the SubQuestionQueryEngine for compare/contrast queries, which generates a "query plan" containing sub-queries against sub-documents before synthesizing the final answer.

Here is an example of how to set up and use the JSONalyze Query Engine:

from llama_index.llms.openai import OpenAI
from llama_index.core.query_engine import JSONalyzeQueryEngine

# Initialize the OpenAI model
llm = OpenAI(model="gpt-3.5-turbo")

# Sample JSON list
json_list = [
    {
        "name": "John Doe",
        "age": 25,
        "major": "Computer Science",
        "email": "[email protected]",
        "address": "123 Main St",
        "city": "New York",
        "state": "NY",
        "country": "USA",
        "phone": "+1 123-456-7890",
        "occupation": "Software Engineer",
    },
    # ... other entries ...
]

# Create JSONalyze Query Engine instance
json_stats_query_engine = JSONalyzeQueryEngine(
    list_of_dict=json_list,
    llm=llm,
    verbose=True,
)

# Example questions
questions = [
    "What is the average age of the individuals in the dataset?",
    "What is the most common major among the individuals?",
]

# Query the engine
for question in questions:
    print(f"Question: {question}")
    print(f"Answer: {json_stats_query_engine.query(question)}")

For more advanced use cases, such as compare/contrast queries, you can use LlamaIndex's SubQuestionQueryEngine:

from llama_index.core.tools import QueryEngineTool, ToolMetadata
from llama_index.core.query_engine import SubQuestionQueryEngine

# Define query engines for different data sources
query_engine_tools = [
    QueryEngineTool(
        query_engine=sept_engine,
        metadata=ToolMetadata(
            name="sept_22",
            description="Provides information about Uber quarterly financials ending September 2022",
        ),
    ),
    QueryEngineTool(
        query_engine=june_engine,
        metadata=ToolMetadata(
            name="june_22",
            description="Provides information about Uber quarterly financials ending June 2022",
        ),
    ),
    QueryEngineTool(
        query_engine=march_engine,
        metadata=ToolMetadata(
            name="march_22",
            description="Provides information about Uber quarterly financials ending March 2022",
        ),
    ),
]

# Define a SubQuestionQueryEngine
query_engine = SubQuestionQueryEngine.from_defaults(
    query_engine_tools=query_engine_tools
)

# Example query
response = query_engine.query("Compare the financial performance of Uber in Q1 and Q2 of 2022.")
print(response)

This setup allows you to handle complex queries efficiently and leverage advanced query techniques provided by LlamaIndex ^[1][2][3].

To continue talking to Dosu, mention @dosu.

[mraguth]

@dosu... thank you and it did work. However, I am struggling to incorporate my pandas dataframe into 'router_query_engine'. Can you give me a sample code please? I came up to below part, but I am not sure how I plug this recursive_retriever_pandas to query engine. I am not sure I am doing right... please help.

table_dfs = []
table_dfs.append(df)
first_dataframe = table_dfs[0]
df_query_engines = [
    PandasQueryEngine(table_df, llm=llm) for table_df in table_dfs
]
df = pd.read_excel("c:/parquet/txt/sample3.xlsx")
df.head()

summaries = [
    (
        "Please note the following context about the dataframe columns and understand carefully before you write summary or insights:\n"
    ),
]

nodes = [
    IndexNode(text=summary, index_id=f"pandas{idx}")
    for idx, summary in enumerate(summaries)
]


df_id_query_engine_mapping = {
    f"pandas{idx}": df_query_engine
    for idx, df_query_engine in enumerate(df_query_engines)
}


# construct top-level vector index + query engine
vector_index = VectorStoreIndex(nodes)
vector_retriever = vector_index.as_retriever(similarity_top_k=1)

recursive_retriever_pandas = RecursiveRetriever(
    "vector",
    retriever_dict={"vector": vector_retriever},
    query_engine_dict=df_id_query_engine_mapping,
    verbose=False,
)

[dosubot[bot]]

To incorporate your Pandas DataFrame into a RouterQueryEngine and plug the recursive_retriever_pandas into the query engine, you can follow the steps below. Here is a complete example:

1. Install necessary packages:

   pip install pandas faiss-cpu fastembed llama-index

2. Set up the environment:

   import os
   import pandas as pd
   from llama_index.core import VectorStoreIndex, Settings
   from llama_index.experimental.query_engine import PandasQueryEngine
   from llama_index.core.schema import IndexNode
   from llama_index.llms.openai import OpenAI
   from llama_index.core.retrievers import RecursiveRetriever
   from llama_index.core.query_engine import RouterQueryEngine, QueryEngineTool
   from llama_index.core.selectors import PydanticSingleSelector
   from llama_index.core import get_response_synthesizer
   
   # Set up OpenAI API key
   os.environ["OPENAI_API_KEY"] = "YOUR_API_KEY"
   
   # Initialize OpenAI settings
   Settings.llm = OpenAI(model="gpt-3.5-turbo")
   Settings.embed_model = OpenAIEmbedding(model="text-embedding-3-small")

3. Load your DataFrame and create Pandas query engines:

   # Load the DataFrame
   df = pd.read_excel("c:/parquet/txt/sample3.xlsx")
   
   # Create Pandas query engines for each table
   llm = OpenAI(model="gpt-4")
   df_query_engines = [PandasQueryEngine(df, llm=llm)]
   
   # Define index nodes
   summaries = [
       "Please note the following context about the dataframe columns and understand carefully before you write summary or insights:\n"
   ]
   
   nodes = [IndexNode(text=summary, index_id=f"pandas{idx}") for idx, summary in enumerate(summaries)]
   df_id_query_engine_mapping = {f"pandas{idx}": df_query_engine for idx, df_query_engine in enumerate(df_query_engines)}

4. Construct the top-level vector index and recursive retriever:

   # Construct top-level vector index
   vector_index = VectorStoreIndex(nodes)
   vector_retriever = vector_index.as_retriever(similarity_top_k=1)
   
   # Use RecursiveRetriever in our RouterQueryEngine
   recursive_retriever_pandas = RecursiveRetriever(
       "vector",
       retriever_dict={"vector": vector_retriever},
       query_engine_dict=df_id_query_engine_mapping,
       verbose=False,
   )

5. Set up the RouterQueryEngine:

   # Create QueryEngineTool for the recursive retriever
   query_engine_tool = QueryEngineTool(
       query_engine=recursive_retriever_pandas,
       metadata={"description": "Handles queries for the Pandas DataFrame"}
   )
   
   # Create the RouterQueryEngine
   selector = PydanticSingleSelector.from_defaults()
   query_engine = RouterQueryEngine(
       selector=selector,
       query_engine_tools=[query_engine_tool],
       llm=llm,
       verbose=True
   )

6. Run example queries:

   # Example queries
   response = query_engine.query("What is the maximum age in the dataset?")
   print(str(response))
   
   response = query_engine.query("Provide a summary of the data.")
   print(str(response))

This code sets up the environment, loads the DataFrame, creates Pandas query engines, builds a vector index, and finally sets up a RouterQueryEngine to handle queries using the recursive_retriever_pandas. This approach allows you to efficiently query your DataFrame using the RouterQueryEngine ^[1][2][3].

To continue talking to Dosu, mention @dosu.

[mraguth]

@dosu getting error 'AttributeError: 'RecursiveRetriever' object has no attribute 'query''

[dosubot[bot]]

To resolve the AttributeError: 'RecursiveRetriever' object has no attribute 'query' error, you need to ensure that the RecursiveRetriever is correctly wrapped in a QueryEngineTool before being passed to the RouterQueryEngine. The RouterQueryEngine expects a sequence of QueryEngineTool objects, which have a query method.

Here's an example of how you can wrap your RecursiveRetriever in a QueryEngineTool and use it with the RouterQueryEngine:

import os
import pandas as pd
from llama_index.core import VectorStoreIndex, Settings
from llama_index.experimental.query_engine import PandasQueryEngine
from llama_index.core.schema import IndexNode
from llama_index.llms.openai import OpenAI, OpenAIEmbedding
from llama_index.core.retrievers import RecursiveRetriever
from llama_index.core.query_engine import RouterQueryEngine, QueryEngineTool
from llama_index.core.selectors import PydanticSingleSelector
from llama_index.core import get_response_synthesizer

# Set up OpenAI API key
os.environ["OPENAI_API_KEY"] = "YOUR_API_KEY"

# Initialize OpenAI settings
Settings.llm = OpenAI(model="gpt-3.5-turbo")
Settings.embed_model = OpenAIEmbedding(model="text-embedding-3-small")

# Load the DataFrame
df = pd.read_excel("c:/parquet/txt/sample3.xlsx")

# Create Pandas query engines for each table
llm = OpenAI(model="gpt-4")
df_query_engines = [PandasQueryEngine(df, llm=llm)]

# Define index nodes
summaries = [
    "Please note the following context about the dataframe columns and understand carefully before you write summary or insights:\n"
]

nodes = [IndexNode(text=summary, index_id=f"pandas{idx}") for idx, summary in enumerate(summaries)]
df_id_query_engine_mapping = {f"pandas{idx}": df_query_engine for idx, df_query_engine in enumerate(df_query_engines)}

# Construct top-level vector index
vector_index = VectorStoreIndex(nodes)
vector_retriever = vector_index.as_retriever(similarity_top_k=1)

# Use RecursiveRetriever in our RouterQueryEngine
recursive_retriever_pandas = RecursiveRetriever(
    "vector",
    retriever_dict={"vector": vector_retriever},
    query_engine_dict=df_id_query_engine_mapping,
    verbose=False,
)

# Create QueryEngineTool for the recursive retriever
query_engine_tool = QueryEngineTool(
    query_engine=recursive_retriever_pandas,
    metadata={"description": "Handles queries for the Pandas DataFrame"}
)

# Create the RouterQueryEngine
selector = PydanticSingleSelector.from_defaults()
query_engine = RouterQueryEngine(
    selector=selector,
    query_engine_tools=[query_engine_tool],
    llm=llm,
    verbose=True
)

# Example queries
response = query_engine.query("What is the maximum age in the dataset?")
print(str(response))

response = query_engine.query("Provide a summary of the data.")
print(str(response))

This ensures that the RecursiveRetriever is properly encapsulated in a QueryEngineTool, which provides the necessary query method expected by the RouterQueryEngine ^[1][2].

To continue talking to Dosu, mention @dosu.

[FaizanRafique2303005KHIDEG]

@dosu. im facing this issue:
final_response = selected_query_engine.query(query_bundle)
AttributeError: 'RecursiveRetriever' object has no attribute 'query'

[dosubot[bot]]

To resolve the AttributeError: 'RecursiveRetriever' object has no attribute 'query', ensure that the RecursiveRetriever is properly wrapped in a QueryEngineTool before being used in the RouterQueryEngine. Here's how you can do it:

1. Wrap the RecursiveRetriever in a QueryEngineTool:

   query_engine_tool = QueryEngineTool.from_defaults(
       query_engine=recursive_retriever_pandas
   )

2. Use the RouterQueryEngine with the QueryEngineTool:

   selector = PydanticSingleSelector.from_defaults()
   query_engine = RouterQueryEngine(
       selector=selector,
       query_engine_tools=[query_engine_tool],
       llm=llm,
       verbose=True
   )

3. Run your queries:

   response = query_engine.query("Your query here")
   print(str(response))

Additionally, if you need to use the RecursiveRetriever directly, you should use the retrieve or retrieve_all methods instead of query:

# Use retrieve method
retrieved_nodes = recursive_retriever.retrieve(query_bundle)

# Or use retrieve_all method
retrieved_nodes, additional_nodes = recursive_retriever.retrieve_all(query_bundle)

This setup ensures that the RecursiveRetriever is properly encapsulated and can be queried through the RouterQueryEngine ^[1].

To continue talking to Dosu, mention @dosu.

[mraguth]

@dosu... thank you. It did work. But I have to do slight change in the code...

query_engine_tool = QueryEngineTool.from_defaults(
    query_engine=recursive_retriever_pandas,
#  metadata={"description": "Handles queries for the Pandas DataFrame"}
)

[FaizanRafique2303005KHIDEG]

@dosu... what if we try it out with this:
llama-index-core/llama_index/core/retrievers/router_retriever.py