An open source DuckDB text to SQL LLM • Hacker News Board

Re: An open source DuckDB text to SQL LLM

#11

Post by PheonixPharts » Thu, Jan 25, 2024, 6:37 PM UTC

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…

> 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.

Re: An open source DuckDB text to SQL LLM

#12

Post by vgt » Thu, Jan 25, 2024, 6:43 PM UTC

jakozaur wrote: post #9

vgt wrote: post #7

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

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)

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-f...

[1]https://motherduck.com/docs/architecture-and-capabilities

[2]https://github.com/motherduckdb/wasm-client

Re: An open source DuckDB text to SQL LLM

#13

Post by ipsum2 » Thu, Jan 25, 2024, 6:45 PM UTC

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…

Llama2 wasn't trained on code, so it's no surprise that you're having trouble generating SQL.

Re: An open source DuckDB text to SQL LLM

#14

Post by tdoehmen » Thu, Jan 25, 2024, 6:46 PM UTC

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'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 prompt what they actually want. Furthermore, it's simply not the right UX for most SQL users. A much better approach IMO is to keep the SQL analysts in the driver seat, and provide nuanced support wherever/whenever they need it most. The FixIt feature we recently launched goes into the same direction: https://motherduck.com/blog/introducing-fixit-ai-sql-error-f...

In that sense I emphasized in our Blogpost that users should think of it as a documentation oracle that always gives you the exact DuckDB SQL query snippet you are looking for, which is a tremendoues time-saver if you have an abstrat idea of the query you want to write, but you're just not sure about the syntax, expecially with DuckDB having so many functions and SQL extensions.

Here are a few exammples:

- create tmp table from test.csv

- load aws credentials from 'test' profile

- get max of all columns in rideshare table

- show query plan with runtimes for 'SELECT * FROM rideshare'

- cast hvfhs_license_num column to int

- get all columns ending with _amount from taxi table

- show summary statistics of rideshare table

- get a 10% reservoir sample of rideshare table

- get length of drivers array in taxi table

- get violation_type field from other_violations json column in taxi table

- get passenger count, trip distance and fare amount from taxi table and oder by all of them

- list all tables in current database

- get all databases starting with test_

[edit: fixed list formatting]

Re: An open source DuckDB text to SQL LLM

#15

Post by swimwiththebeat » Thu, Jan 25, 2024, 6:47 PM UTC

vgt wrote: post #7

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

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 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.

Re: An open source DuckDB text to SQL LLM

#16

Post by vgt » Thu, Jan 25, 2024, 6:47 PM UTC

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 agree. I think full text-to-results (even bypassing text-to-SQL) is akin to L5 self-driving cars. It's easy to get to some reasonable level, say 90%. But to get to the point, where folks can fully trust the system and don't need a steering wheel (or know SQL) may take decades.

We at MotherDuck took an incremental approach. We launched something more akin to lane-assist. We're calling it FixIt - an in-flow visual aid to help you identify and fix errors in your SQL [0].

I think there's gobs of opportunities to improve the analytics experiences without resorting to "L5 self-driving" (e.g. full text-to-results)

[0] https://motherduck.com/blog/introducing-fixit-ai-sql-error-f...

Re: An open source DuckDB text to SQL LLM

#17

Post by tdoehmen » Thu, Jan 25, 2024, 7:13 PM UTC

swimwiththebeat wrote: post #15

vgt wrote: post #7

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

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 benchmark that covers anything else than plain old SQL select statements with a handful of aggregate functions). And training is also not a one-off thing. With large datasets it is hard to evaluate the quality of the dataset without actually training a few epochs on it and run the benchmark.

2. I left a comment about my view on where such models are effective in a previous commment: https://news.ycombinator.com/item?id=39133155

3. No way - I see a common stack emerging (take a look at companies like https://vanna.ai/, https://www.dataherald.com/, or https://www.waii.ai) that is mainly centered around foundation models like GPT-4 with strong in-context learning capabilities (that's a kind of a must to make these approaches work and comes with long inference times and higher costs). These solutions include things like embedding-based schema filtering, options for users to enrich metadata about tables and columns, including previous related queries into the context etc. around the model. I'd say it's a bit of a different problem from what we aimed at solving.

Re: An open source DuckDB text to SQL LLM

#18

Post by swimwiththebeat » Thu, Jan 25, 2024, 7:38 PM UTC

tdoehmen wrote: post #14

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'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?

Re: An open source DuckDB text to SQL LLM

#19

Post by b_mc2 » Thu, Jan 25, 2024, 7:41 PM UTC

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

[2] https://huggingface.co/datasets/b-mc2/sql-create-context

Re: An open source DuckDB text to SQL LLM

#20

Post by zainhoda » Thu, Jan 25, 2024, 7:41 PM UTC

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’m trying to solve for this with my project using RAG and (at least based on what people say in Discord), it’s working really well for them: https://github.com/vanna-ai/vanna