Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

An open source DuckDB text to SQL LLM

motherduck.com

Post reply on HN
Print view View original

21–30 of 31 posts

Re: An open source DuckDB text to SQL LLM

#21

Post by swimwiththebeat »

tdoehmen wrote: post #17
swimwiththebeat wrote: post #15

Earlier quoted context omitted.

1. First of all, thanks for outlining how you trained the model here in the repo: 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…

Hi, Till here, worked on the DuckDB-NSQL model on MotherDuck side. 1. definitely training data (for me), we explored about 10 different directions before settling on the current approach. It's easy to underestimate the effect of training data on the quality of the model. Starting point was the benchmark dataset though, which we assembled manually (to avoid data pollution and also because there was simply no text2sql…

Thanks for taking the time to answer the questions and link those resources, really appreciate it and the work your team did!

Re: An open source DuckDB text to SQL LLM

#23

Post by datadrivenangel »

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.

Re: An open source DuckDB text to SQL LLM

#25

Post by nerpderp82 »

swimwiththebeat wrote: post #18
tdoehmen wrote: post #14

Earlier quoted context omitted.

I've been working on the DuckDB-NSQL model on MotherDuck side. I fully agree that general text-2-sql, in the sense of "give me a question, and I'll produce you an arbitrary complex query", is a very tough problem and I actually believe that that's not the right problem to solve. Not necessarily because models are not (going to be) cabable enough, but rather because it's way too hard for humans to express in a single…

Thanks for replying, that's a perspective I didn't consider. The capability to "talk to your data" just seems so enticing as a solution that I was tunnel-visioned into that UX. If I'm understanding correctly, what you're suggesting is more of a SQL assistant to help people write the correct SQL queries instead of writing the entire SQL query from scratch to answer a generic natural-language question?

I believe that is what they are saying.

I have found LLMs to be extremely helpful in mapping between schemas as well as helping me formulate queries where, because of decay, data in tables and column names, etc don't map to what you think they would.

You need to provide as much context as you can to the LLM. So full schema definitions, and histographic summarization and samples from the tables themselves.

Re: An open source DuckDB text to SQL LLM

#26

Post by zainhoda »

tdoehmen wrote: post #17
swimwiththebeat wrote: post #15

Earlier quoted context omitted.

1. First of all, thanks for outlining how you trained the model here in the repo: 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…

Hi, Till here, worked on the DuckDB-NSQL model on MotherDuck side. 1. definitely training data (for me), we explored about 10 different directions before settling on the current approach. It's easy to underestimate the effect of training data on the quality of the model. Starting point was the benchmark dataset though, which we assembled manually (to avoid data pollution and also because there was simply no text2sql…

Founder of Vanna AI here -- appreciate the link and I agree, we're solving slightly different problems.

Re: An open source DuckDB text to SQL LLM

#27

Post by theboat »

tdoehmen wrote: post #17
swimwiththebeat wrote: post #15

Earlier quoted context omitted.

1. First of all, thanks for outlining how you trained the model here in the repo: 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…

Hi, Till here, worked on the DuckDB-NSQL model on MotherDuck side. 1. definitely training data (for me), we explored about 10 different directions before settling on the current approach. It's easy to underestimate the effect of training data on the quality of the model. Starting point was the benchmark dataset though, which we assembled manually (to avoid data pollution and also because there was simply no text2sql…

I didn't see this in the blog post, but did you train this from scratch or finetune an existing base model?

If from scratch, quite impressive that the model is capable of understanding natural language prompts (English presumably) from such a small, targeted training set.

Re: An open source DuckDB text to SQL LLM

#28

Post by DebtDeflation »

swimwiththebeat wrote: post #4

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…

I assume that "Talk to your data" presupposes the existence of some simple view that has been created and that embeds 99% of the required business logic.

"What was the average order size per customer for product XYZ in the West region?"

Imagine turning that one loose against the typical legacy system.

I wouldn't trust an LLM to figure out the joins or aggregate calculation, LET ALONE the definition of a customer, a product, or a region.

But it would almost certainly generate AN answer.

Re: An open source DuckDB text to SQL LLM

#29

Post by waynesonfire »

throwup238 wrote: post #5
terhechte wrote: post #3

Earlier quoted context omitted.

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.

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.

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!

Re: An open source DuckDB text to SQL LLM

#30

Post by cognaitiv »

swimwiththebeat wrote: post #4

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…

Lots of folks taking this approach and feels like the wrong entry point, e.g., similar to asking LLMs to generate bytecode when compilers exist or 3d printing a machine vs. building a machine from 3d printed components.

1. Business users aren’t prepared to talk to their data in meaningful ways and this is an opportunity for LLMs to enhance the way users ask questions.

2. SQL modeling languages exist (although I’m not sure there are well maintained open source good ones and this is the biggest obstacle to what I’m working on now) and LLMs can extend these effectively by adding components such as dimensions, metrics, relationships, filters, etc. with less chance of hallucination

3. Deterministic SQL generation from a modeling repository is easier to troubleshoot and provide guarantees than end-to-end generation.

4. Existing SQL can be parsed to generate modeling components that can be committed to the model repository with LLM assistance

5. Much of the richness of going to data to answer questions is context, e.g., how does this dept compare to others, this month to same month last year, etc. Modeling languages are good at expressing these transformations, but business users and often analysts aren’t good at capturing all the permutations of these types of comparisons. Another area where LLMs can help apply tooling.

IMO, LLMs are more effective at using tools than generating complex outputs.

Post reply on HN