Using NLSQLTableQueryEngine with AzureOpenAI

[LennartHil]

``Hi,

I want to reproduce the Text-to-SQL Guide.
I copied the exact code with the difference that I have only access to a AzureOpenAI deployment.
This is currently not working for me.

Edit:
If I use the llm directly it is working fine.

Example:
python llm.complete("The sky is a beautiful blue and")

Code:

# %%
import os
import openai
from dotenv import load_dotenv

# %%
from sqlalchemy import (
    create_engine,
    MetaData,
    Table,
    Column,
    String,
    Integer,
    select,
)

# %%
engine = create_engine("sqlite:///:memory:")
metadata_obj = MetaData()

# %%
# create city SQL table
table_name = "city_stats"
city_stats_table = Table(
    table_name,
    metadata_obj,
    Column("city_name", String(16), primary_key=True),
    Column("population", Integer),
    Column("country", String(16), nullable=False),
)
metadata_obj.create_all(engine)

# %%
from llama_index import SQLDatabase, ServiceContext
from llama_index.llms import AzureOpenAI

# %%
load_dotenv()
endpoint = os.environ["openai_api_base"]
api_key = os.environ["openai_api_key"]

llm = AzureOpenAI(
    engine="gpt-4",
    temperature=0.1,
    model="gpt-4",
    deployment_name="gpt-4",
    api_key=api_key,
    azure_endpoint=endpoint,
    api_version="2023-07-01-preview",
)
service_context = ServiceContext.from_defaults(llm=llm)

# %%
sql_database = SQLDatabase(engine, include_tables=["city_stats"])

# %%
sql_database = SQLDatabase(engine, include_tables=["city_stats"])
from sqlalchemy import insert

rows = [
    {"city_name": "Toronto", "population": 2930000, "country": "Canada"},
    {"city_name": "Tokyo", "population": 13960000, "country": "Japan"},
    {
        "city_name": "Chicago",
        "population": 2679000,
        "country": "United States",
    },
    {"city_name": "Seoul", "population": 9776000, "country": "South Korea"},
]
for row in rows:
    stmt = insert(city_stats_table).values(**row)
    with engine.begin() as connection:
        cursor = connection.execute(stmt)

# %%
# view current table
stmt = select(
    city_stats_table.c.city_name,
    city_stats_table.c.population,
    city_stats_table.c.country,
).select_from(city_stats_table)

with engine.connect() as connection:
    results = connection.execute(stmt).fetchall()
    print(results)

# %%
from sqlalchemy import text

with engine.connect() as con:
    rows = con.execute(text("SELECT city_name from city_stats"))
    for row in rows:
        print(row)

# %%
from llama_index.indices.struct_store.sql_query import NLSQLTableQueryEngine

query_engine = NLSQLTableQueryEngine(
    sql_database=sql_database,
    tables=["city_stats"],
)
query_str = "Which city has the highest population?"
response = query_engine.query(query_str)

Error Message:

NotFoundError: Error code: 404 - {'error': {'code': '404', 'message': 'Resource not found'}}

[dosubot[bot]]

🤖

Hi @LennartHil! 👋 I'm Dosu, a friendly bot here to lend a virtual hand with bugs, questions, and anything else you might need while we wait for a human maintainer. Glad to make your acquaintance!

Based on the error message you're receiving, it seems like the Azure OpenAI endpoint or the model deployment you're trying to access does not exist or is not correctly configured. This could be due to several reasons:

1. The engine parameter is not correctly set. This parameter corresponds to the custom name you chose for your deployment when you deployed a model on Azure OpenAI. If this is not correctly set, the AzureOpenAIMultiModal class will not be able to find your model deployment.

2. The AZURE_OPENAI_ENDPOINT environment variable is not correctly set. This should be the endpoint of your Azure OpenAI deployment, and it should look like this: https://YOUR_RESOURCE_NAME.openai.azure.com/.

3. The AZURE_OPENAI_API_KEY environment variable is not correctly set. This should be your Azure OpenAI API key.

Please ensure that these parameters and environment variables are correctly set. If the error persists, it might be due to an issue with your Azure OpenAI deployment itself, and you might need to check its configuration on the Azure portal. You can refer to the AzureOpenAIMultiModal class for more details.

Also, there was a similar issue reported in the LlamaIndex repository: [Bug]: Error using Azure opeani. The suggested solution was to ensure you are using the correct model for embeddings as per the Azure documentation and to refer to the guide provided for customization with Azure OpenAI.

If you continue to face issues, please provide more details about your Azure OpenAI deployment and the exact steps you're following from the Text-to-SQL Guide. This will help in identifying the root cause of the issue.

Sources

• [Bug]: Error using Azure opeani
• llama_index/multi_modal_llms/azure_openai.py
• benchmarks/struct_indices/spider/generate_sql.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.