5 ms·

An open source DuckDB text to SQL LLM

131 points by vgt 3y ago

aldarisbm 3y ago
looks great, most text-to-sql attempts i’ve tried fall short, hoping this is different
- terhechte 3y ago
  I do this a lot with GPT4 and I can't recall it ever generating an invalid query, and I did have some complicated ones.
  - throwup238 3y ago
    The problem isn't the SQL generation per se, it's the mapping between natural language and the data that columns actually hold. If you have a clean schema with well named fields, LLMs are very good at generating valid SQL but if you have an old schema that has been patched and modified for years by a dozen DBAs each with their own naming quirks, short hands, and hacks, it can barely string a valid query together.
    - waynesonfire 3y ago
      Yeah, blame the user for for poorly named columns and whatever "clean" means, that's it. That's what's blocking the feature. The issue is that SQL is not composable. It's necessary to articulate the messy operation all at once and it's not possible to iterate on it. Good luck!
swimwiththebeat 3y ago
I see so many business leaders touting the promise of LLMs allowing business to "talk" to their data. The promise does sound enticing, but it's actually kind of hard to get working in practice. A lot of our databases at work have columns with custom types and enums, and getting the LLM (Llama2) to write SQL queries to robustly answer natural language questions about the data is tough. It requires a lot of instruction prompting, context, and question-SQL examples (few-shot learning), and it still fails in unexpected ways. It's a tough ask for people to use a tool like this if they can't trust the results all the time. It's also a bit infeasible to scale this to tens or hundreds of tables across our data warehouse. It's great that a lot of people are trying to crack this problem, I'm curious to try this model out. I'd also love to see if other people have tried solving this problem and made any headway.
- ipsum2 3y ago
  Llama2 wasn't trained on code, so it's no surprise that you're having trouble generating SQL.
- qsort 3y ago
  I've worked on a similar problem and we have pretty much the same issues as you. An idea that makes things better is having an intermediate representation that exposes the key tables of your dwh and gets compiled to SQL. This allows you to have somewhat better security (even if the model outputs garbage it can't do too much damage because nothing except exactly what you want is even representable), and somewhat better explainability (you can't guarantee the model will get it right, but you can see what SQL gets executed rather than "magic 8-ball says no"). But as you say custom types and encoded domain knowledge is extremely tough and as a result it's very tough to "transfer" the system to different databases.
- pacbard 3y ago
  My biggest concern is that this will allow people to type a question and get a number back from the database, without being able to tell if the query is right or if the LLM just made up something. It can work to support business analysts to crank out more reporters, but I wouldn’t roll it out to all my staff.
  - swimwiththebeat 3y ago
    I've actually done exactly what @qsort suggested and outputted the intermediate SQL query and raw data generated by that query when generating the response back to the user. That definitely helps in establishing more trust with the customer since they can verify the response. My approach right now is to just be honest with our customers in the capabilities of the tool, acknowledge its shortcomings, and keep iterating over time to make it better and better. That's what the team in charge of our company-wide custom LLM has done and it's gained a surprising amount of traction and trust over the last few months.
- PheonixPharts 3y ago
  > but it's actually kind of hard to get working in practice One of the biggest challenges I've personally seen in this space is business "leaders" pushing teams to ship products asap lest they loose face among their fellow CEOs for not pushing out "AI" products before everyone else. I'm fairly optimistic about LLMs being able to truly be transformative, but it's not going to be through forcing the bread-dead UX of hoisting yet another slightly re-imagined chat interface on users. The idea of "talking to your data" is a promising one, and anyone who has worked for a large data driven org will quickly agree that organizing and searching in-house data is not a solved problem from the UX end of things. But to truly solving these problems, even/especially with LLMs, is going to require thought and experimentation. Something few "business leaders" have patience for.
vgt 3y ago
Co-founder and Head of Produck at MotherDuck here, happy to answer any questions or go nag the amazing engineers [0] who worked on this :) [0]https://news.ycombinator.com/user?id=tdoehmen https://news.ycombinator.com/user?id=tdoehmen
- jakozaur 3y ago
  Congratulations! The FixIt seems a pragmatic feature. Any other ideas about where you plan to utilize LLM? E.g. - migrate from different SQL dialects (e.g. date functions) - make my queries nicer (e.g., rewrite them in shorter form or use better variable names) - warn about potential bugs (e.g. SQL fanout)
  - vgt 3y ago
    Love these! We do want to deliver more features like FixIt! [0] What's really exciting is what you can do with DuckDB, MotherDuck, and WASM. A powerful in-browser storage and execution engine tethered to a central serverless data warehouse using hybrid mode [1] opens the doors for unprecedented experiences. Imagine the possibilities if you have metadata, data, query logic, or even LLMs in the client 0ms away from the user and on user's own hardware. So we're doing this in our UI of course, but we also released a WASM SDK so that developers can take advantage of this new architecture in their own apps! [2] [0]https://motherduck.com/blog/introducing-fixit-ai-sql-error-fixer/ https://motherduck.com/blog/introducing-fixit-ai-sql-error-f... [1]https://motherduck.com/docs/architecture-and-capabilities https://motherduck.com/docs/architecture-and-capabilities [2]https://github.com/motherduckdb/wasm-client https://github.com/motherduckdb/wasm-client
- swimwiththebeat 3y ago
  1. First of all, thanks for outlining how you trained the model here in the repo: https://github.com/NumbersStationAI/DuckDB-NSQL?tab=readme-ov-file#training-data https://github.com/NumbersStationAI/DuckDB-NSQL?tab=readme-o...! I did not know about `sqlglot`, that's a pretty cool lib. Which part of the project was the most challenging or time-consuming: generating the training data, the actual training, or testing? How did you iterate, improve, and test the model? 2. How would you suggest using this model effectively if we have custom data in our DBs? For example, we might have a column called `purpose` that's a custom defined enum (i.e. not a very well-known concept outside of our business). Currently, we've fed it in as context by defining all the possible values it can have. Do you have any other recs on how to tune our prompts so that this model is just as effective with our own custom data? 3. Similar to above, do you know you can use the same model to work effectively on tens or even hundreds of tables? I've used multiple question-SQL example pairs as context, but I've found that I need 15-20 for it to be effective for even one table, let alone tens of tables.
b_mc2 3y ago
This is awesome, congratulations. I'm glad to see some text-to-sql models being created. Shameless plug: I also just realized you used NSText2SQL[1] which itself contains my text-to-sql dataset, sql-create-context[2], so I'm honored. I used sqlglot pretty heavily on it as well. Do you think a 3B model might also be in the future, or something small enough that can be loaded up in Transformers.js? [1] https://huggingface.co/datasets/NumbersStation/NSText2SQL https://huggingface.co/datasets/NumbersStation/NSText2SQL [2] https://huggingface.co/datasets/b-mc2/sql-create-context https://huggingface.co/datasets/b-mc2/sql-create-context
datadrivenangel 3y ago
The core issue of text to SQL is that your data has to be good for the generated queries to be correct. The queries may run and return good looking results, but if the data requires domain knowledge ("Don't count people in the customer table without filtering out records with the test flag in the customer attributes table and at least one order in the orders table") you'll get results that don't actually answer your question.
CastFX 3y ago
I'd love to see how it performs in some benchmarks, specifically against Spider (https://yale-lily.github.io/spider https://yale-lily.github.io/spider) and BIRD (https://bird-bench.github.io/ https://bird-bench.github.io/)

131 points by vgt 3y ago

aldarisbm 3y ago

looks great, most text-to-sql attempts i’ve tried fall short, hoping this is different

terhechte 3y ago
I do this a lot with GPT4 and I can't recall it ever generating an invalid query, and I did have some complicated ones.
- throwup238 3y ago
  The problem isn't the SQL generation per se, it's the mapping between natural language and the data that columns actually hold. If you have a clean schema with well named fields, LLMs are very good at generating valid SQL but if you have an old schema that has been patched and modified for years by a dozen DBAs each with their own naming quirks, short hands, and hacks, it can barely string a valid query together.
  - waynesonfire 3y ago
    Yeah, blame the user for for poorly named columns and whatever "clean" means, that's it. That's what's blocking the feature. The issue is that SQL is not composable. It's necessary to articulate the messy operation all at once and it's not possible to iterate on it. Good luck!

swimwiththebeat 3y ago

I see so many business leaders touting the promise of LLMs allowing business to "talk" to their data. The promise does sound enticing, but it's actually kind of hard to get working in practice. A lot of our databases at work have columns with custom types and enums, and getting the LLM (Llama2) to write SQL queries to robustly answer natural language questions about the data is tough. It requires a lot of instruction prompting, context, and question-SQL examples (few-shot learning), and it still fails in unexpected ways. It's a tough ask for people to use a tool like this if they can't trust the results all the time. It's also a bit infeasible to scale this to tens or hundreds of tables across our data warehouse. It's great that a lot of people are trying to crack this problem, I'm curious to try this model out. I'd also love to see if other people have tried solving this problem and made any headway.

ipsum2 3y ago
Llama2 wasn't trained on code, so it's no surprise that you're having trouble generating SQL.
qsort 3y ago
I've worked on a similar problem and we have pretty much the same issues as you. An idea that makes things better is having an intermediate representation that exposes the key tables of your dwh and gets compiled to SQL. This allows you to have somewhat better security (even if the model outputs garbage it can't do too much damage because nothing except exactly what you want is even representable), and somewhat better explainability (you can't guarantee the model will get it right, but you can see what SQL gets executed rather than "magic 8-ball says no"). But as you say custom types and encoded domain knowledge is extremely tough and as a result it's very tough to "transfer" the system to different databases.
pacbard 3y ago
My biggest concern is that this will allow people to type a question and get a number back from the database, without being able to tell if the query is right or if the LLM just made up something. It can work to support business analysts to crank out more reporters, but I wouldn’t roll it out to all my staff.
- swimwiththebeat 3y ago
  I've actually done exactly what @qsort suggested and outputted the intermediate SQL query and raw data generated by that query when generating the response back to the user. That definitely helps in establishing more trust with the customer since they can verify the response. My approach right now is to just be honest with our customers in the capabilities of the tool, acknowledge its shortcomings, and keep iterating over time to make it better and better. That's what the team in charge of our company-wide custom LLM has done and it's gained a surprising amount of traction and trust over the last few months.
PheonixPharts 3y ago
> but it's actually kind of hard to get working in practice One of the biggest challenges I've personally seen in this space is business "leaders" pushing teams to ship products asap lest they loose face among their fellow CEOs for not pushing out "AI" products before everyone else. I'm fairly optimistic about LLMs being able to truly be transformative, but it's not going to be through forcing the bread-dead UX of hoisting yet another slightly re-imagined chat interface on users. The idea of "talking to your data" is a promising one, and anyone who has worked for a large data driven org will quickly agree that organizing and searching in-house data is not a solved problem from the UX end of things. But to truly solving these problems, even/especially with LLMs, is going to require thought and experimentation. Something few "business leaders" have patience for.

vgt 3y ago

Co-founder and Head of Produck at MotherDuck here, happy to answer any questions or go nag the amazing engineers [0] who worked on this :) [0]https://news.ycombinator.com/user?id=tdoehmen https://news.ycombinator.com/user?id=tdoehmen

jakozaur 3y ago
Congratulations! The FixIt seems a pragmatic feature. Any other ideas about where you plan to utilize LLM? E.g. - migrate from different SQL dialects (e.g. date functions) - make my queries nicer (e.g., rewrite them in shorter form or use better variable names) - warn about potential bugs (e.g. SQL fanout)
- vgt 3y ago
  Love these! We do want to deliver more features like FixIt! [0] What's really exciting is what you can do with DuckDB, MotherDuck, and WASM. A powerful in-browser storage and execution engine tethered to a central serverless data warehouse using hybrid mode [1] opens the doors for unprecedented experiences. Imagine the possibilities if you have metadata, data, query logic, or even LLMs in the client 0ms away from the user and on user's own hardware. So we're doing this in our UI of course, but we also released a WASM SDK so that developers can take advantage of this new architecture in their own apps! [2] [0]https://motherduck.com/blog/introducing-fixit-ai-sql-error-fixer/ https://motherduck.com/blog/introducing-fixit-ai-sql-error-f... [1]https://motherduck.com/docs/architecture-and-capabilities https://motherduck.com/docs/architecture-and-capabilities [2]https://github.com/motherduckdb/wasm-client https://github.com/motherduckdb/wasm-client
swimwiththebeat 3y ago
1. First of all, thanks for outlining how you trained the model here in the repo: https://github.com/NumbersStationAI/DuckDB-NSQL?tab=readme-ov-file#training-data https://github.com/NumbersStationAI/DuckDB-NSQL?tab=readme-o...! I did not know about `sqlglot`, that's a pretty cool lib. Which part of the project was the most challenging or time-consuming: generating the training data, the actual training, or testing? How did you iterate, improve, and test the model? 2. How would you suggest using this model effectively if we have custom data in our DBs? For example, we might have a column called `purpose` that's a custom defined enum (i.e. not a very well-known concept outside of our business). Currently, we've fed it in as context by defining all the possible values it can have. Do you have any other recs on how to tune our prompts so that this model is just as effective with our own custom data? 3. Similar to above, do you know you can use the same model to work effectively on tens or even hundreds of tables? I've used multiple question-SQL example pairs as context, but I've found that I need 15-20 for it to be effective for even one table, let alone tens of tables.

b_mc2 3y ago

This is awesome, congratulations. I'm glad to see some text-to-sql models being created. Shameless plug: I also just realized you used NSText2SQL[1] which itself contains my text-to-sql dataset, sql-create-context[2], so I'm honored. I used sqlglot pretty heavily on it as well. Do you think a 3B model might also be in the future, or something small enough that can be loaded up in Transformers.js? [1] https://huggingface.co/datasets/NumbersStation/NSText2SQL https://huggingface.co/datasets/NumbersStation/NSText2SQL [2] https://huggingface.co/datasets/b-mc2/sql-create-context https://huggingface.co/datasets/b-mc2/sql-create-context

datadrivenangel 3y ago

The core issue of text to SQL is that your data has to be good for the generated queries to be correct. The queries may run and return good looking results, but if the data requires domain knowledge ("Don't count people in the customer table without filtering out records with the test flag in the customer attributes table and at least one order in the orders table") you'll get results that don't actually answer your question.

CastFX 3y ago

I'd love to see how it performs in some benchmarks, specifically against Spider (https://yale-lily.github.io/spider https://yale-lily.github.io/spider) and BIRD (https://bird-bench.github.io/ https://bird-bench.github.io/)