Using SQLDatabase with Llama 2 for snowflake connection, i get ProgramingError

[adrinanou]

System Info

Langchain 0.0.277
Python 3.9

Who can help?

@hwchase17

Information

• The official example notebooks/scripts
• My own modified scripts

Related Components

• LLMs/Chat Models
• Embedding Models
• Prompts / Prompt Templates / Prompt Selectors
• Output Parsers
• Document Loaders
• Vector Stores / Retrievers
• Memory
• Agents / Agent Executors
• Tools / Toolkits
• Chains
• Callbacks/Tracing
• Async

Reproduction

from langchain.agents import create_sql_agent, initialize_agent, create_spark_sql_agent
from langchain.agents.agent_toolkits import SQLDatabaseToolkit, SparkSQLToolkit
from langchain.sql_database import SQLDatabase
from langchain_experimental.sql.base import SQLDatabaseChain
from langchain.llms import HuggingFacePipeline
from langchain.agents import AgentExecutor
from langchain.agents.agent_types import AgentType
from langchain.chat_models import ChatOpenAI

from snowflake.sqlalchemy import URL
from sqlalchemy import create_engine
from transformers import AutoTokenizer, AutoModelForCausalLM, pipeline

model_id= "Photolens/llama-2-13b-langchain-chat"
user= ""
password= ""
account= "-"
database="*SNOWFLAKE_SAMPLE_DATA"
schema="*"
warehouse=""
def load_model():
model_id = "Photolens/llama-2-13b-langchain-chat"
tokenizer = AutoTokenizer.from_pretrained(model_id)

model = AutoModelForCausalLM.from_pretrained(model_id,
                                           device_map='auto',
                                           low_cpu_mem_usage=True,
                                           trust_remote_code=True
                                          )

pipe = pipeline(
    "text-generation",
    model=model,
    tokenizer=tokenizer,
    max_length=1100,
    repetition_penalty=1.15,
    top_p=0.95,
    temperature=0.2,
    pad_token_id=tokenizer.eos_token_id,
    max_new_tokens=300
)

local_llm = HuggingFacePipeline(pipeline=pipe)

return local_llm

LLM=load_model()
engine = create_engine(URL(
user= "",
password= "",
account= "-",
database="SNOWFLAKE_SAMPLE_DATA,
schema="",
warehouse="***")
)
db = SQLDatabase(engine)
#here comes the problem, SQLDatabase makes a wrong query on Snowflake

Expected behavior

I'm expecting to generate a connection with SQLDatabase, but it doesn't in fact makes a wrong query that i dont get it. I'm new at this so i would apreciate some help.

This is the error a i get:
/home/zeusone/anaconda3/envs/snowflake_ai/bin/python /home/zeusone/Documents/ChatbotFalcon/SQLagent/snoflake_simple_agent.py
/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/snowflake/connector/options.py:96: UserWarning: You have an incompatible version of 'pyarrow' installed (13.0.0), please install a version that adheres to: 'pyarrow<8.1.0,>=8.0.0; extra == "pandas"'
warn_incompatible_dep(
Loading checkpoint shards: 100%|██████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 3/3 [00:15<00:00, 5.20s/it]
Traceback (most recent call last):
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
self.dialect.do_execute(
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
cursor.execute(statement, parameters)
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/snowflake/connector/cursor.py", line 827, in execute
Error.errorhandler_wrapper(self.connection, self, error_class, errvalue)
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/snowflake/connector/errors.py", line 275, in errorhandler_wrapper
handed_over = Error.hand_to_other_handler(
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/snowflake/connector/errors.py", line 330, in hand_to_other_handler
cursor.errorhandler(connection, cursor, error_class, error_value)
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/snowflake/connector/errors.py", line 209, in default_errorhandler
raise error_class(
snowflake.connector.errors.ProgrammingError: 001059 (22023): SQL compilation error:
Must specify the full search path starting from database for SNOWFLAKE_SAMPLE_DATA

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "/home/zeusone/Documents/ChatbotFalcon/SQLagent/snoflake_simple_agent.py", line 56, in
db = SQLDatabase(engine)#"snowflake://ADRIANOCABRERA:Semilla_1@EKKFOPI-YK08475/SNOWFLAKE_SAMPLE_DATA/TPCH-SF1?warehouse=COMPUTE_WH")
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/langchain/utilities/sql_database.py", line 111, in init
self.metadata.reflect(
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/sqlalchemy/sql/schema.py", line 4901, in reflect
Table(name, self, **reflect_opts)
File "", line 2, in new
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/sqlalchemy/util/deprecations.py", line 375, in warned
return fn(*args, **kwargs)
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/sqlalchemy/sql/schema.py", line 619, in new
metadata.remove_table(name, schema)
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in exit
compat.raise(
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise
raise exception
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/sqlalchemy/sql/schema.py", line 614, in new
table._init(name, metadata, *args, **kw)
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/sqlalchemy/sql/schema.py", line 689, in _init
self._autoload(
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/sqlalchemy/sql/schema.py", line 724, in _autoload
conn_insp.reflect_table(
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/sqlalchemy/engine/reflection.py", line 774, in reflect_table
for col_d in self.get_columns(
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/sqlalchemy/engine/reflection.py", line 497, in get_columns
col_defs = self.dialect.get_columns(
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/snowflake/sqlalchemy/snowdialect.py", line 669, in get_columns
schema_columns = self._get_schema_columns(connection, schema, **kw)
File "", line 2, in _get_schema_columns
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/sqlalchemy/engine/reflection.py", line 55, in cache
ret = fn(self, con, *args, **kw)
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/snowflake/sqlalchemy/snowdialect.py", line 479, in _get_schema_columns
schema_primary_keys = self._get_schema_primary_keys(
File "", line 2, in _get_schema_primary_keys
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/sqlalchemy/engine/reflection.py", line 55, in cache
ret = fn(self, con, *args, *kw)
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/snowflake/sqlalchemy/snowdialect.py", line 323, in _get_schema_primary_keys
result = connection.execute(
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1385, in execute
return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
return connection._execute_clauseelement(
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1577, in _execute_clauseelement
ret = self._execute_context(
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1953, in _execute_context
self.handle_dbapi_exception(
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2134, in handle_dbapi_exception
util.raise(
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise
raise exception
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
self.dialect.do_execute(
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
cursor.execute(statement, parameters)
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/snowflake/connector/cursor.py", line 827, in execute
Error.errorhandler_wrapper(self.connection, self, error_class, errvalue)
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/snowflake/connector/errors.py", line 275, in errorhandler_wrapper
handed_over = Error.hand_to_other_handler(
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/snowflake/connector/errors.py", line 330, in hand_to_other_handler
cursor.errorhandler(connection, cursor, error_class, error_value)
File "/home/zeusone/anaconda3/envs/snowflake_ai/lib/python3.9/site-packages/snowflake/connector/errors.py", line 209, in default_errorhandler
raise error_class(
sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001059 (22023): SQL compilation error:
Must specify the full search path starting from database for SNOWFLAKE_SAMPLE_DATA
[SQL: SHOW / sqlalchemy:_get_schema_primary_keys */PRIMARY KEYS IN SCHEMA snowflake_sample_data]
(Background on this error at: https://sqlalche.me/e/14/f405)

[dosubot]

🤖

Hello,

Based on the information you've provided, it seems like you're encountering a ProgrammingError when trying to establish a connection with SQLDatabase using Llama 2 for snowflake connection in LangChain 0.0.277. This error is likely due to the fact that the table you're trying to fetch sample rows from is empty.

In the SQLDatabase class, the _get_sample_rows method is used to fetch sample rows from a table. If the table is empty, some dialects return a ProgrammingError. This is handled in the code with a try-except block:

try:
    # get the sample rows
    with self._engine.connect() as connection:
        sample_rows_result = connection.execute(command)  # type: ignore
        # shorten values in the sample rows
        sample_rows = list(
            map(lambda ls: [str(i)[:100] for i in ls], sample_rows_result)
        )

    # save the sample rows in string format
    sample_rows_str = "\n".join(["\t".join(row) for row in sample_rows])

# in some dialects when there are no rows in the table a
# 'ProgrammingError' is returned
except ProgrammingError:
    sample_rows_str = ""

If the table is empty, the ProgrammingError is caught and sample_rows_str is set to an empty string.

To avoid this error, you could check if the table is empty before trying to fetch sample rows. You can find this code in the libs/langchain/langchain/utilities/sql_database.py file.

Also, there was a similar issue reported in the LangChain repository: SQLDatabase chain having issue running queries on the database after connecting. The issue was due to a change in the way table definition statements were retrieved, which was done in a sqlite3-specific way, breaking compatibility with other database systems. This issue was fixed in a later version.

Unfortunately, I couldn't find any specific changes in LangChain version 0.0.279 that could potentially resolve the issue you're facing in version 0.0.277. I recommend updating to the latest version of LangChain and trying again. If the problem persists, please provide more details about the error message you're receiving.

I hope this helps! Let me know if you have any other questions.

Sources

• SQLDatabase chain having issue running queries on the database after connecting
• libs/langchain/langchain/utilities/sql_database.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.

[dosubot]

Hi, @adrinanou. I'm Dosu, and I'm helping the LangChain team manage our backlog. I wanted to let you know that we are marking this issue as stale.

Based on my understanding of the issue, you are experiencing a ProgrammingError when using SQLDatabase with Llama 2 for a Snowflake connection. The error message suggests that the full search path starting from the database for SNOWFLAKE_SAMPLE_DATA must be specified. In a comment, I suggest that the error may be due to an empty table and provide code to handle this scenario. I also mention a similar issue in the LangChain repository that was fixed in a later version. I recommend updating to the latest version of LangChain and provide links to relevant sources.

Before we close this issue, we wanted to check with you if it is still relevant to the latest version of the LangChain repository. If it is, please let us know by commenting on this issue. Otherwise, feel free to close the issue yourself, or it will be automatically closed in 7 days.

Thank you for your understanding and contribution to the LangChain project. If you have any further questions or concerns, please don't hesitate to reach out.