Bedrock/Titan Langchain with Athena: Force single quotes.

Question

Hello, I am trying to use Bedrock **Titan Express** model to convert text2sql using langchain. I am able to generate **an almost valid** sql statement but except string literal are being **"double-quoted"**. I have tried to prompt the LLM to use single quotes but it is not listening to me. Any ideas for a better prompt?

Below is my prompt and output I get from the model and error:

```
     This is a task to convert text into a SQL statement. We will first give the dataset schema and then
     ask a question in text. You are asked to generate a SQL statement that includes the database name and table.
     Use single quotes to quote text values

{query}
     User:
     
 |SELECT COUNT ( * )  FROM s3.library_data WHERE genre  =  "Science Fiction"
SET database to athena
Step complete. Channel is:  db

> Entering new SQLDatabaseChain chain...
How many books with a genre of Science Fiction are there?
SQLQuery:SELECT COUNT ( * )  FROM library_data WHERE genre  =  "Science Fiction"
```

```
OperationalError: COLUMN_NOT_FOUND: line 1:55: Column 'science fiction' cannot be resolved or requester is not authorized to access requested resources
```

Answer

Hello, seems like you're almost there. One thing i would recommend to do is to provide examples within your prompt that dictate exactly what you want just to clarify to the LLM. 
As every model has differences in prompting techniques, I have provided the documentation on prompting for Titan [here](https://docs.aws.amazon.com/bedrock/latest/userguide/general-guidelines-for-bedrock-users.html) have a look at how examples are provided to assist you with creating examples within your prompt.

Also, I have linked a similar project [here](https://github.com/aws-samples/amazon-bedrock-samples/tree/main/agents/text-2-sql-agent) that uses an Amazon Bedrock Agent that is designed to do text to SQL please free to check it out as another possible solution.

Answer

I ended solving my problem by switching to Amazon Bedrock Titan Lite LLM and the below prompt.  It seems to use single-quotes 80% of the time as opposed to the Titan Express model which I was never able to get it to use single quotes. The main learning from this is if you can use the Anthropic Claude v2 model then do so, as it performs way better. I am constrained to using the Titan models.

```
You are a SQL expert. Convert the below natural language question into a valid SQL statement. The schema has the structure below:

Here is the question to be answered:

{query}

Provide the SQL query that would retrieve the data based on the natural language request.

SELECT count(*) FROM s3_library_data WHERE genre  =  'Fantasy'
SET database to athena
Step complete. Channel is:  db

> Entering new SQLDatabaseChain chain...
How many books with a genre of Fantasy are there?
SQLQuery:SELECT count(*) FROM s3_library_data WHERE genre = 'Fantasy'
SQLResult: [(404,)]
Answer:SELECT count(*) FROM s3_library_data WHERE genre = 'Fantasy'
    SQLResult: 404
> Finished chain.
```

Bedrock/Titan Langchain with Athena: Force single quotes.

Relevant content