14 ms·

Show HN: Natural-SQL-7B, a strong text-to-SQL model

362 points by thecalebf 3y ago

Would love thoughts! Here is the HF page: https://huggingface.co/chatdb/natural-sql-7b https://huggingface.co/chatdb/natural-sql-7b

deleted 3y ago
[deleted]
satvikpendem 3y ago
This is not open source, because you have use-based restrictions. Call it what it is, source available.
- thecalebf 3y ago
  Updated the title since it may have been confusing, appreciate the feedback!
- CharlesW 3y ago
  > Call it what it is, source available. Also, it's only weights AFAICT — no source training data/code is available.
  - Keyframe 3y ago
    Shareware?
    - klabb3 3y ago
      Yep. Open source means you can build and modify it. If not, it’s not open source. You know it’s a bad timeline when releasing the equivalent of a binary is considered “open”.
      - Keyframe 3y ago
        What is old is new again!
      - Palmik 3y ago
        Except people modify "non open source" but "weights available" models all the time. In fact, this very model is such modification (fine tune) of the original base model.
        satvikpendem 3y ago
        Except it's not really known for all non-open-source licenses are allowed to be modified. I can similarly jailbreak a phone but it's not open source.
  - gwd 3y ago
    Share-able weights are an interesting one, because although you can't re-generate it from scratch, you can modify it and share it. They're sort of halfway in between source code (which allows you to regenerate a binary from scratch and also inspect everything that went into the binary) and a free-as-in-beer binary (which you typically can't change at all). We sort of need a new term for this kind of thing. I feel like we should try to reserve "open" for something that has all of the "four freedoms". The key thing about this is that it's not inspect-able, but it is derivable. Derivable-weight license? EDIT: Looking at the "four freedoms" [1], "freedom 1" is: > The freedom to study how the program works, and change it so it does your computing as you wish (freedom 1). Access to the source code is a precondition for this. Essentially the thing about weights is that you can superficially retrain bits of it to adapt it to your use case without needing to do a full re-train. But of course, without access to the training set, you can't really be sure what's in those weights, nor make more fundamental changes that would require adding or removing data. [1] https://www.gnu.org/philosophy/free-sw.en.html#four-freedoms https://www.gnu.org/philosophy/free-sw.en.html#four-freedoms
delichon 3y ago
So you feed it the whole DDL with the prompt. I wonder how it performs on a task like schema normalization or optimization? Say, also include the slow queries log and ask it for the SQL commands to modify the indexes to speed up those queries. Allow it to normalize/denormalize/materialize as options. Give it a way to run the resulting DDL against a test suite and iterate toward some fitness goal. This would save gobs of compute.
- thecalebf 3y ago
  That is really interesting. I took note of this. That would be really cool!
mrgaro 3y ago
Would love to get an ollama Modelfile showing an imaginary database schema as an example!
zurfer 3y ago
really cool, the license is not really standard, but seems open source. The actual model can be found here: https://huggingface.co/cfahlgren1/NaturalSQL-6.7B-v0 https://huggingface.co/cfahlgren1/NaturalSQL-6.7B-v0 This seems like a great base model, although I wonder if text-to-sql is good use case for small models. We are also building a tool in the space and I regularly wish gpt-4 to be even more knowledgable when answering. Even gpt 3.5 is not good enough for production.
- thecalebf 3y ago
  Thanks! Yes, that was an earlier iteration. The v1 is here https://huggingface.co/chatdb/natural-sql-7b https://huggingface.co/chatdb/natural-sql-7b. Plan to push to Ollama soon and build an open source free tool around it. Would love to hear about what you are building!
  - zurfer 3y ago
    We are building Dot (https://www.getdot.ai/ https://www.getdot.ai/). We mostly focus on good conversation design and data governance to ensure a great end user experience. Here is an example conversation about HN data https://eu.getdot.ai/share/c80139c9-13f4-4db4-88f6-6e058ba31ad4?org_id=getdot.ai https://eu.getdot.ai/share/c80139c9-13f4-4db4-88f6-6e058ba31...
    - eurekin 3y ago
      That's impressive
swalsh 3y ago
Is there a DSPy module, where I can give a schema, and can start asking sql questions in a structured way?
- thecalebf 3y ago
  Not yet, planning to build an open source, local, CLI tool that utilizes natural-sql though!
itsoktocry 3y ago
>complex questions like above. This is cool, and up my alley. But that's not a complex question, it's a basic analytics question. Most analysts will be able to write something like that in their sleep. I've been using ChatGPT for writing SQL, and it's mediocre. But it'll get better, I'm sure.
- thecalebf 3y ago
  Sure, there is a blog post with some other examples of the first model iteration with more complex, multipart questions. https://www.chatdb.ai/post/naturalsql-vs-sqlcoder-for-text-to-sql https://www.chatdb.ai/post/naturalsql-vs-sqlcoder-for-text-t... I will update that to be a more truly difficult question. Appreciate the feedback!
  - itsoktocry 3y ago
    Thank you, this is great work! I'll check it out.
- int_19h 3y ago
  Even GPT-4 often writes queries with redundant subqueries, excessive nesting or joins etc. I don't think that's particularly valuable. On the other hand, telling GPT to generate SQL to query a data store as part of solving some task that requires inference from facts captured in that data store works surprisingly well - better than "function calls" with JSON, in my opinion. While such generated queries are also suboptimal, they still capture the intent correctly, and GPT is surprisingly adept at using nested subqueries to get the answer it needs in a single query. And when such generated SQL is wrong, it usually fails to parse (e.g. due to typos in field names), at which point you can just feed the error message back to the model and have it correct that.
xfalcox 3y ago
Context is 4096? My app db DDL is 19877 tokens (using Llama2 tokenizer) long, so that means we need to do a RAG for handling the DDL prompt injection. A model like this with a 32k long seq_len, like Mixtral, would be a killer for me.
- thecalebf 3y ago
  Great call out. Will definitely focus on that in the next iteration!
bottlepalm 3y ago
Is this the data that was used for fine tuning? https://github.com/defog-ai/sql-eval/blob/main/data/questions_gen_postgres.csv https://github.com/defog-ai/sql-eval/blob/main/data/question...
- thecalebf 3y ago
  No, those are benchmark, evaluation questions. The fine tune dataset was a custom, synthetically generated dataset of ~20k PostgreSQL Text to SQL pairs covering different SQL categories and question types. I mention a little more about it here https://x.com/calebfahlgren/status/1754247740291207198?s=20 https://x.com/calebfahlgren/status/1754247740291207198?s=20
  - Semaphor 3y ago
    So this is essentially postgres only? Or how will it handle e.g. MS SQL Schemas and output?
    - thecalebf 3y ago
      Currently Postgres yes, already working on a dataset with more DDLs like MySQL, DuckDB, MSSQL, etc for a second iteration.
cuuupid 3y ago
This is a big improvement, but I'm not a believer that SQL is the most appropriate query lang here. Personally am more bullish on language models being trained with ORMs, as those normally capture much more information about the fields. e.g. Passing in some of my more complex table schemas related to flight data and asking about overflights, the model struggles to resolve out information related to aviation. However, GitHub Copilot writes me a perfect call to Prisma with the same single line instruction + information spanning the rest of my codebase.
- thecalebf 3y ago
  Neat, would you ever use a local model for that if it could work with ORMs?
- tillvz 3y ago
  Agree that an approach that more semantically models the data is better, especially when you want to eventually let the non-technical users ask questions. When you're on a higher abstraction level, it also allows you to make clear definitions (e.g. for certain KPIs) and define business logic that always needs to be applied to get the correct results. There you don't want to leave it up to chance that a filter gets hallucinated in or out when you ask e.g. about your company's revenue. At Veezoo (https://www.veezoo.com https://www.veezoo.com) we have taken the approach that instead of going directly to SQL. So when a user asks a question, Veezoo translates it first into a query against the Knowledge Graph (which represents the business objects, their relationship etc.). From there we compile it into a SQL query depending on the target database (they all have slight differences) without any AI involvement. In this compilation step we also make sure that the business logic is properly applied.
rgbrgb 3y ago
So it looks like it scores 76.5% on SQL-Eval [0], a bit behind GPT-4 at 83% and sqlcoder-15b at 78%. What kind of applications would this be useful for? What can you build with an AI data science intern that's right 75% of the time? As a programmer who always has to look stuff up when I SQL, I could definitely see asking something like this for a first draft of a query but it seems like I'm slightly better off asking the bigger models in these one-off cases (and I can run a 15b easily on my 64GB m1). If I'm in a corporate setting I'm not going to leak my schema into OpenAI's training data and there are definitely times when I'd want to run queries offline. Small/local models are great when you want to do a ton of queries (save $$). A mini data scientist that could be queried by non-technical folks would be awesome but I wonder if there's a way to determine whether the query is falling in the 25% "incorrect" case... maybe there's a RAID-like consensus algorithm where you have multiple interrogate each other's answers to get a higher overall success rate. Mostly thinking out loud :) but maybe ya'll have more ideas. Congrats on the release, OP! [0]: https://github.com/defog-ai/sql-eval https://github.com/defog-ai/sql-eval
- whimsicalism 3y ago
  do you not trust the setting OAI provides to exclude your conversation from training data?
  - tempusalaria 3y ago
    1) OpenAI has consistently gone back on commitments it has made 2) Sam Altman has a shady track record publicly, and if you believe the things people say privately he has consistently done business very dishonestly throughout his career. He is the CEO, and virtually the entire executive team are people he brought in from his network. It’s his company. 3) To give one example of many, OpenAI recently changed the terms of ChatGPT so that web users conversations can now be trained on (and if you want to save any chats you must opt in). Presumably this also applies to all conversations you had under the old policy despite saying they would never train on those conversations. I could go on at length…
    - anonylizard 3y ago
CastFX 3y ago
I was wondering how it performs in a more complex (and realistic) benchmark like Bird? https://bird-bench.github.io/ https://bird-bench.github.io/
croes 3y ago
I doubt it's useful for complex queries or databases without proper relation info in the database schemes. So it's limited to rather simple queries for users without SQL knowledge. But I doubt they should have direct access to the database tables.
- thecalebf 3y ago
  Yes it is designed for users without SQL knowledge, however, it can still perform fairly well with questions on the difficult side (for non technical users) with queries having multiple joins, aggregations, ratios, and subqueries. Next step is fine tuning and leveraging larger models that can handle very complex questions, reasoning, and data schemas since this is only a 7B.
- lofties 3y ago
  Usually what you do with these type of LLMs is pass on most if not all of the schema in your query. And you’re right in that the end user wouldn’t have access to the schema, although perhaps via prompt injection they could.
  - croes 3y ago
    The problem is there are programs where the schemes don't show the necessary links between the programs data tables like foreign keys for instance.
Tycho 3y ago
At university I studied 'natural language interfaces to databases' (NLIDBs). I recall that very early, I think in the 60, NASA and other organisations were trying to build things like this for their scientists. Of course, there was no SQL in those days. Once SQL rose to prominence, my impression is that NLIDB interest faded, especially with the lack of NLP breakthroughs. My project was to create an analog to SQL in more plain, naturalistic English (a bit like what AppleScript is to other programming languages), and then let the user construct the queries by using a GUI rather than free-typing. The GUI would constrain the query text to permissible syntax, letting the user click to add new clauses and select columns etc, while maintaining a 'layman intelligible' sentence. Anyway, maybe now we can get NLIDBs with true NLP.
zainhoda 3y ago
Very cool. Would the license allow for use with Vanna? https://github.com/vanna-ai/vanna https://github.com/vanna-ai/vanna
- thecalebf 3y ago
  Yes please do! Looks awesome, would love to help any way I can as well.
  - pama 3y ago
    Not OP, but would it be possible to use a standardized license? Every time a special purpose license is used for a software that gains adaptation, the lawyers of hundreds to thousands of different companies must spend a lot of time and iterations with the team to figure out if they can actually use this model. There is something magical in the GPL, MIT, Apache, etc licenses because these lawyers have already opined on them once and no longer create a bottleneck.
jimmytucson 3y ago
In the data world I've worked with tons of folks whose responsibilities include getting questions from execs, knowing their way around the data warehouse enough to write SQL to answer those questions, and delivering the answers back (sometimes formatted nicely). Sometimes they have to predict what followup questions the exec will ask, like "why is that number so low, it obviously shouldn't be that low" so they can press the data engineers for bugs. Like all things LLM, I don't know if this is about to make those responsibilities a lot easier, or just eliminate them altogether.
- fwip 3y ago
  My money is on "overpaid execs will use this to get wrong information, and get mad at their subordinates for correcting them."
wantsanagent 3y ago
So I need to ask my lawyer to review a custom license before I can consider using this? No thanks.
- thecalebf 3y ago
  Sorry, it seems complicated. Since it is a finetune of Deepseek-Coder, I had to include their license. Deepseek is pretty open, just says not to use it for: - military purposes - exploiting vulnerabilities etc. Just trying to include as much information as possible from the initial base model.
htrp 3y ago
Would be very interesting if you wrote a bit about what you did to actually do the finetuning
K0IN 3y ago
One Problem I always see in such apps is that the ai can't see in to the database or into all entries, so queries without stating data EXACTLY as in the database run into issues. example: give me the revenue for all logistics firms but in the database these might not be called "logistics" and may be called "transport" (or anything) maybe there are some counters to this like finding unique values per column or even better use a grammar based approach, wich will select only valid entries. but the simple text to SQL is at this point not the "hard thing to solve"
- bm-rf 3y ago
  Usually you include the database schema in the context, usually by showing the CREATE statement for the tables you want to query. I've also found that including comments in the CREATE sql can guide the model somewhat. The best approach is probably to finetune one of these models using curated questions for your database.
buzzm 3y ago
Like many uses of AI, very good as a "seed" especially if it comes up with nuggets like grouping on a range instead of a single value. But as with almost any database, devil is in details. Different products have different interp of "quantity" (e.g. box vs. unit), coupons and discounts are modeled in weird ways, weights are assumed to be pounds/kg and are mixed without assigning units, etc. etc.
- owlstuffing 3y ago
  Good point. However, AI may also train on the database metadata, including DDL comments, locale, etc. and perform data sampling to glean the nuances that are "understood".
owlstuffing 3y ago
Natural language-derived SQL will be useful for programming language integration involving type-safe SQL. I'm currently using manifold-sql[1] for this, and having the ability to transform English directly to SQL type-safely is amazing. 1. https://github.com/manifold-systems/manifold/blob/master/manifold-deps-parent/manifold-sql/readme.md https://github.com/manifold-systems/manifold/blob/master/man...
paidcompute 3y ago
this is pretty cool, I've seen a similar project already, I think it's called SQL translate
lolpanda 3y ago
I don't think any of those text-to-sql models are solving the right problems. The hard part is not syntax or I don't know how to write a group by query. Most data scientists and engineers spend more time on understanding the meaning of the data. One cannot simply look at a 50 columns table in Snowflake and guess what columns are by their names. For example, we have 10 columns in one tables, all named ...price. We have to go to wiki to find the actual meaning or read the DBT definitions. I cannot trust any queries that models produce because they don't understand the data; they only understand the query syntax.
- joshhart 3y ago
  At Databricks we have an LLM that is fine-tuned to do the problem you raise - https://www.databricks.com/blog/announcing-public-preview-ai-generated-documentation-databricks-unity-catalog https://www.databricks.com/blog/announcing-public-preview-ai... Many customers like it a lot. Although perhaps in your case if there are many pricing details it may not be quite accurate.
  - l5870uoo9y 3y ago
    Can I ask how you fine-tune or if you can be a bit more specific?
- mritchie712 3y ago
  Yeah, we've been working on this problem a good bit and I think text-to-sql is a dead-end for analytical questions. We (https://www.definite.app/ https://www.definite.app/) ended up abandoning text-to-sql in favor of answering questions with a semantic layer (which LLM's are far more effective against). https://www.loom.com/share/a0d3c0e273004d7982b2aed24628ef40 https://www.loom.com/share/a0d3c0e273004d7982b2aed24628ef40
  - l5870uoo9y 3y ago
    So you don’t use AI to generate SQL to retrieve data? As you say on the web site?
Uptrenda 3y ago
Next generation of 'software engineers' are going to be brain dead: 'duhhh gpt how 2 do x... duh...'
- jamil7 3y ago
  Maybe? Or the bar will just be a lot higher for them since there will be less work at the entry level. Either way I think previous generations of SWEs will be at an advantage going forward.
floridageorgia 3y ago
I would 100% use this if you had a text -> Big Query SQL feature. Please add.
aussieguy1234 3y ago
I've been using GPT-4 to write SQL to get a bunch of insights. Its very good at writing up queries for all kinds of metrics. These queries answer questions that would have previously required an actual data squad.
fijiaarone 3y ago
We’re calling anyone who can write SQL a data scientist now?
ainesh93 3y ago
Hard to claim success with "complex" questions if you don't account for business context and organizational nuances. For example, "active" listings on Redfin may be a combination of days on market, last open house, last update, etc instead of a Boolean flag called "is_active". How can we expect models to generate correct SQL at the enterprise level without providing a support structure of business context? The model can only be so good.
brendongeils 3y ago
previous palantir & scale ai engineer w/ an ai data science startup here. we found that RAG on a large corpus was the best outcome, not just RAG on queries, but RAG across any data the org uploaded to our system, athena intelligence. semantic search on documents, slack, and other sources provided better outcomes w/ on-prem models too. this has allowed us to unlock some novel analytics workflows including multi-modal, figma style, etc. we also just opened up our platform for public access. currently working with enterprises like anheuser-busch. https://app.athenaintelligence.ai/ https://app.athenaintelligence.ai/
zeroq 3y ago
For anyone who says it's useless because it's only 75% correct, please consider these two points: (1) this is the first instalment, and it's already close to be a thousand times more useful for product owners and analytics than any airtable you can imagine. (2) as much as I love being on point on every challenge, we're leaving in "good enough" economics for quite some time, and if this will be close enough that will be good enough for business.
Too 3y ago
Heh. Then there are those that say SQL already reads like natural language. What happens if you feed the model with sql as input?
saigal 3y ago
can you fine-tune the LLM?
moltar 3y ago
I have a use case where there's no DDL, just a description of the tables, with provided data types, and descriptions of all of the columns, in JSON. I could generate DDL statements, of course. But wondering if this is the best way to hint at the model of the database structure. Also, how would you go about supplying the very verbose descriptions of all of the data types? Would SQL comments be best? Postgres-style column comments? Thanks!
moltar 3y ago
The card states: > This model was evaluated on SQL-Eval, a PostgreSQL-based evaluation framework developed by Defog for testing and alignment of model capabilities. But this explains the testing part. However, does it mean that only the PostgreSQL-flavour of SQL is supported? Would it work for Trino flavour?

362 points by thecalebf 3y ago

Would love thoughts! Here is the HF page: https://huggingface.co/chatdb/natural-sql-7b https://huggingface.co/chatdb/natural-sql-7b

deleted 3y ago

[deleted]

satvikpendem 3y ago

This is not open source, because you have use-based restrictions. Call it what it is, source available.

thecalebf 3y ago
Updated the title since it may have been confusing, appreciate the feedback!
CharlesW 3y ago
> Call it what it is, source available. Also, it's only weights AFAICT — no source training data/code is available.
- Keyframe 3y ago
  Shareware?
  - klabb3 3y ago
    Yep. Open source means you can build and modify it. If not, it’s not open source. You know it’s a bad timeline when releasing the equivalent of a binary is considered “open”.
    - Keyframe 3y ago
      What is old is new again!
    - Palmik 3y ago
      Except people modify "non open source" but "weights available" models all the time. In fact, this very model is such modification (fine tune) of the original base model.
      - satvikpendem 3y ago
        Except it's not really known for all non-open-source licenses are allowed to be modified. I can similarly jailbreak a phone but it's not open source.
- gwd 3y ago
  Share-able weights are an interesting one, because although you can't re-generate it from scratch, you can modify it and share it. They're sort of halfway in between source code (which allows you to regenerate a binary from scratch and also inspect everything that went into the binary) and a free-as-in-beer binary (which you typically can't change at all). We sort of need a new term for this kind of thing. I feel like we should try to reserve "open" for something that has all of the "four freedoms". The key thing about this is that it's not inspect-able, but it is derivable. Derivable-weight license? EDIT: Looking at the "four freedoms" [1], "freedom 1" is: > The freedom to study how the program works, and change it so it does your computing as you wish (freedom 1). Access to the source code is a precondition for this. Essentially the thing about weights is that you can superficially retrain bits of it to adapt it to your use case without needing to do a full re-train. But of course, without access to the training set, you can't really be sure what's in those weights, nor make more fundamental changes that would require adding or removing data. [1] https://www.gnu.org/philosophy/free-sw.en.html#four-freedoms https://www.gnu.org/philosophy/free-sw.en.html#four-freedoms

delichon 3y ago

So you feed it the whole DDL with the prompt. I wonder how it performs on a task like schema normalization or optimization? Say, also include the slow queries log and ask it for the SQL commands to modify the indexes to speed up those queries. Allow it to normalize/denormalize/materialize as options. Give it a way to run the resulting DDL against a test suite and iterate toward some fitness goal. This would save gobs of compute.

thecalebf 3y ago
That is really interesting. I took note of this. That would be really cool!

mrgaro 3y ago

Would love to get an ollama Modelfile showing an imaginary database schema as an example!

zurfer 3y ago

really cool, the license is not really standard, but seems open source. The actual model can be found here: https://huggingface.co/cfahlgren1/NaturalSQL-6.7B-v0 https://huggingface.co/cfahlgren1/NaturalSQL-6.7B-v0 This seems like a great base model, although I wonder if text-to-sql is good use case for small models. We are also building a tool in the space and I regularly wish gpt-4 to be even more knowledgable when answering. Even gpt 3.5 is not good enough for production.

thecalebf 3y ago
Thanks! Yes, that was an earlier iteration. The v1 is here https://huggingface.co/chatdb/natural-sql-7b https://huggingface.co/chatdb/natural-sql-7b. Plan to push to Ollama soon and build an open source free tool around it. Would love to hear about what you are building!
- zurfer 3y ago
  We are building Dot (https://www.getdot.ai/ https://www.getdot.ai/). We mostly focus on good conversation design and data governance to ensure a great end user experience. Here is an example conversation about HN data https://eu.getdot.ai/share/c80139c9-13f4-4db4-88f6-6e058ba31ad4?org_id=getdot.ai https://eu.getdot.ai/share/c80139c9-13f4-4db4-88f6-6e058ba31...
  - eurekin 3y ago
    That's impressive

swalsh 3y ago

Is there a DSPy module, where I can give a schema, and can start asking sql questions in a structured way?

thecalebf 3y ago
Not yet, planning to build an open source, local, CLI tool that utilizes natural-sql though!

itsoktocry 3y ago

>complex questions like above. This is cool, and up my alley. But that's not a complex question, it's a basic analytics question. Most analysts will be able to write something like that in their sleep. I've been using ChatGPT for writing SQL, and it's mediocre. But it'll get better, I'm sure.

thecalebf 3y ago
Sure, there is a blog post with some other examples of the first model iteration with more complex, multipart questions. https://www.chatdb.ai/post/naturalsql-vs-sqlcoder-for-text-to-sql https://www.chatdb.ai/post/naturalsql-vs-sqlcoder-for-text-t... I will update that to be a more truly difficult question. Appreciate the feedback!
- itsoktocry 3y ago
  Thank you, this is great work! I'll check it out.
int_19h 3y ago
Even GPT-4 often writes queries with redundant subqueries, excessive nesting or joins etc. I don't think that's particularly valuable. On the other hand, telling GPT to generate SQL to query a data store as part of solving some task that requires inference from facts captured in that data store works surprisingly well - better than "function calls" with JSON, in my opinion. While such generated queries are also suboptimal, they still capture the intent correctly, and GPT is surprisingly adept at using nested subqueries to get the answer it needs in a single query. And when such generated SQL is wrong, it usually fails to parse (e.g. due to typos in field names), at which point you can just feed the error message back to the model and have it correct that.

xfalcox 3y ago

Context is 4096? My app db DDL is 19877 tokens (using Llama2 tokenizer) long, so that means we need to do a RAG for handling the DDL prompt injection. A model like this with a 32k long seq_len, like Mixtral, would be a killer for me.

thecalebf 3y ago
Great call out. Will definitely focus on that in the next iteration!

bottlepalm 3y ago

Is this the data that was used for fine tuning? https://github.com/defog-ai/sql-eval/blob/main/data/questions_gen_postgres.csv https://github.com/defog-ai/sql-eval/blob/main/data/question...

thecalebf 3y ago
No, those are benchmark, evaluation questions. The fine tune dataset was a custom, synthetically generated dataset of ~20k PostgreSQL Text to SQL pairs covering different SQL categories and question types. I mention a little more about it here https://x.com/calebfahlgren/status/1754247740291207198?s=20 https://x.com/calebfahlgren/status/1754247740291207198?s=20
- Semaphor 3y ago
  So this is essentially postgres only? Or how will it handle e.g. MS SQL Schemas and output?
  - thecalebf 3y ago
    Currently Postgres yes, already working on a dataset with more DDLs like MySQL, DuckDB, MSSQL, etc for a second iteration.

cuuupid 3y ago

This is a big improvement, but I'm not a believer that SQL is the most appropriate query lang here. Personally am more bullish on language models being trained with ORMs, as those normally capture much more information about the fields. e.g. Passing in some of my more complex table schemas related to flight data and asking about overflights, the model struggles to resolve out information related to aviation. However, GitHub Copilot writes me a perfect call to Prisma with the same single line instruction + information spanning the rest of my codebase.

thecalebf 3y ago
Neat, would you ever use a local model for that if it could work with ORMs?
tillvz 3y ago
Agree that an approach that more semantically models the data is better, especially when you want to eventually let the non-technical users ask questions. When you're on a higher abstraction level, it also allows you to make clear definitions (e.g. for certain KPIs) and define business logic that always needs to be applied to get the correct results. There you don't want to leave it up to chance that a filter gets hallucinated in or out when you ask e.g. about your company's revenue. At Veezoo (https://www.veezoo.com https://www.veezoo.com) we have taken the approach that instead of going directly to SQL. So when a user asks a question, Veezoo translates it first into a query against the Knowledge Graph (which represents the business objects, their relationship etc.). From there we compile it into a SQL query depending on the target database (they all have slight differences) without any AI involvement. In this compilation step we also make sure that the business logic is properly applied.

rgbrgb 3y ago

So it looks like it scores 76.5% on SQL-Eval [0], a bit behind GPT-4 at 83% and sqlcoder-15b at 78%. What kind of applications would this be useful for? What can you build with an AI data science intern that's right 75% of the time? As a programmer who always has to look stuff up when I SQL, I could definitely see asking something like this for a first draft of a query but it seems like I'm slightly better off asking the bigger models in these one-off cases (and I can run a 15b easily on my 64GB m1). If I'm in a corporate setting I'm not going to leak my schema into OpenAI's training data and there are definitely times when I'd want to run queries offline. Small/local models are great when you want to do a ton of queries (save $$). A mini data scientist that could be queried by non-technical folks would be awesome but I wonder if there's a way to determine whether the query is falling in the 25% "incorrect" case... maybe there's a RAID-like consensus algorithm where you have multiple interrogate each other's answers to get a higher overall success rate. Mostly thinking out loud :) but maybe ya'll have more ideas. Congrats on the release, OP! [0]: https://github.com/defog-ai/sql-eval https://github.com/defog-ai/sql-eval

whimsicalism 3y ago
do you not trust the setting OAI provides to exclude your conversation from training data?
- tempusalaria 3y ago
  1) OpenAI has consistently gone back on commitments it has made 2) Sam Altman has a shady track record publicly, and if you believe the things people say privately he has consistently done business very dishonestly throughout his career. He is the CEO, and virtually the entire executive team are people he brought in from his network. It’s his company. 3) To give one example of many, OpenAI recently changed the terms of ChatGPT so that web users conversations can now be trained on (and if you want to save any chats you must opt in). Presumably this also applies to all conversations you had under the old policy despite saying they would never train on those conversations. I could go on at length…
  - anonylizard 3y ago

CastFX 3y ago

I was wondering how it performs in a more complex (and realistic) benchmark like Bird? https://bird-bench.github.io/ https://bird-bench.github.io/

croes 3y ago

I doubt it's useful for complex queries or databases without proper relation info in the database schemes. So it's limited to rather simple queries for users without SQL knowledge. But I doubt they should have direct access to the database tables.

thecalebf 3y ago
Yes it is designed for users without SQL knowledge, however, it can still perform fairly well with questions on the difficult side (for non technical users) with queries having multiple joins, aggregations, ratios, and subqueries. Next step is fine tuning and leveraging larger models that can handle very complex questions, reasoning, and data schemas since this is only a 7B.
lofties 3y ago
Usually what you do with these type of LLMs is pass on most if not all of the schema in your query. And you’re right in that the end user wouldn’t have access to the schema, although perhaps via prompt injection they could.
- croes 3y ago
  The problem is there are programs where the schemes don't show the necessary links between the programs data tables like foreign keys for instance.

Tycho 3y ago

At university I studied 'natural language interfaces to databases' (NLIDBs). I recall that very early, I think in the 60, NASA and other organisations were trying to build things like this for their scientists. Of course, there was no SQL in those days. Once SQL rose to prominence, my impression is that NLIDB interest faded, especially with the lack of NLP breakthroughs. My project was to create an analog to SQL in more plain, naturalistic English (a bit like what AppleScript is to other programming languages), and then let the user construct the queries by using a GUI rather than free-typing. The GUI would constrain the query text to permissible syntax, letting the user click to add new clauses and select columns etc, while maintaining a 'layman intelligible' sentence. Anyway, maybe now we can get NLIDBs with true NLP.

zainhoda 3y ago

Very cool. Would the license allow for use with Vanna? https://github.com/vanna-ai/vanna https://github.com/vanna-ai/vanna

thecalebf 3y ago
Yes please do! Looks awesome, would love to help any way I can as well.
- pama 3y ago
  Not OP, but would it be possible to use a standardized license? Every time a special purpose license is used for a software that gains adaptation, the lawyers of hundreds to thousands of different companies must spend a lot of time and iterations with the team to figure out if they can actually use this model. There is something magical in the GPL, MIT, Apache, etc licenses because these lawyers have already opined on them once and no longer create a bottleneck.

jimmytucson 3y ago

In the data world I've worked with tons of folks whose responsibilities include getting questions from execs, knowing their way around the data warehouse enough to write SQL to answer those questions, and delivering the answers back (sometimes formatted nicely). Sometimes they have to predict what followup questions the exec will ask, like "why is that number so low, it obviously shouldn't be that low" so they can press the data engineers for bugs. Like all things LLM, I don't know if this is about to make those responsibilities a lot easier, or just eliminate them altogether.

fwip 3y ago
My money is on "overpaid execs will use this to get wrong information, and get mad at their subordinates for correcting them."

wantsanagent 3y ago

So I need to ask my lawyer to review a custom license before I can consider using this? No thanks.

thecalebf 3y ago
Sorry, it seems complicated. Since it is a finetune of Deepseek-Coder, I had to include their license. Deepseek is pretty open, just says not to use it for: - military purposes - exploiting vulnerabilities etc. Just trying to include as much information as possible from the initial base model.

htrp 3y ago

Would be very interesting if you wrote a bit about what you did to actually do the finetuning

K0IN 3y ago

One Problem I always see in such apps is that the ai can't see in to the database or into all entries, so queries without stating data EXACTLY as in the database run into issues. example: give me the revenue for all logistics firms but in the database these might not be called "logistics" and may be called "transport" (or anything) maybe there are some counters to this like finding unique values per column or even better use a grammar based approach, wich will select only valid entries. but the simple text to SQL is at this point not the "hard thing to solve"

bm-rf 3y ago
Usually you include the database schema in the context, usually by showing the CREATE statement for the tables you want to query. I've also found that including comments in the CREATE sql can guide the model somewhat. The best approach is probably to finetune one of these models using curated questions for your database.

buzzm 3y ago

Like many uses of AI, very good as a "seed" especially if it comes up with nuggets like grouping on a range instead of a single value. But as with almost any database, devil is in details. Different products have different interp of "quantity" (e.g. box vs. unit), coupons and discounts are modeled in weird ways, weights are assumed to be pounds/kg and are mixed without assigning units, etc. etc.

owlstuffing 3y ago
Good point. However, AI may also train on the database metadata, including DDL comments, locale, etc. and perform data sampling to glean the nuances that are "understood".

owlstuffing 3y ago

Natural language-derived SQL will be useful for programming language integration involving type-safe SQL. I'm currently using manifold-sql[1] for this, and having the ability to transform English directly to SQL type-safely is amazing. 1. https://github.com/manifold-systems/manifold/blob/master/manifold-deps-parent/manifold-sql/readme.md https://github.com/manifold-systems/manifold/blob/master/man...

paidcompute 3y ago

this is pretty cool, I've seen a similar project already, I think it's called SQL translate

lolpanda 3y ago

I don't think any of those text-to-sql models are solving the right problems. The hard part is not syntax or I don't know how to write a group by query. Most data scientists and engineers spend more time on understanding the meaning of the data. One cannot simply look at a 50 columns table in Snowflake and guess what columns are by their names. For example, we have 10 columns in one tables, all named ...price. We have to go to wiki to find the actual meaning or read the DBT definitions. I cannot trust any queries that models produce because they don't understand the data; they only understand the query syntax.

joshhart 3y ago
At Databricks we have an LLM that is fine-tuned to do the problem you raise - https://www.databricks.com/blog/announcing-public-preview-ai-generated-documentation-databricks-unity-catalog https://www.databricks.com/blog/announcing-public-preview-ai... Many customers like it a lot. Although perhaps in your case if there are many pricing details it may not be quite accurate.
- l5870uoo9y 3y ago
  Can I ask how you fine-tune or if you can be a bit more specific?
mritchie712 3y ago
Yeah, we've been working on this problem a good bit and I think text-to-sql is a dead-end for analytical questions. We (https://www.definite.app/ https://www.definite.app/) ended up abandoning text-to-sql in favor of answering questions with a semantic layer (which LLM's are far more effective against). https://www.loom.com/share/a0d3c0e273004d7982b2aed24628ef40 https://www.loom.com/share/a0d3c0e273004d7982b2aed24628ef40
- l5870uoo9y 3y ago
  So you don’t use AI to generate SQL to retrieve data? As you say on the web site?

Uptrenda 3y ago

Next generation of 'software engineers' are going to be brain dead: 'duhhh gpt how 2 do x... duh...'

jamil7 3y ago
Maybe? Or the bar will just be a lot higher for them since there will be less work at the entry level. Either way I think previous generations of SWEs will be at an advantage going forward.

floridageorgia 3y ago

I would 100% use this if you had a text -> Big Query SQL feature. Please add.

aussieguy1234 3y ago

I've been using GPT-4 to write SQL to get a bunch of insights. Its very good at writing up queries for all kinds of metrics. These queries answer questions that would have previously required an actual data squad.

fijiaarone 3y ago

We’re calling anyone who can write SQL a data scientist now?

ainesh93 3y ago

Hard to claim success with "complex" questions if you don't account for business context and organizational nuances. For example, "active" listings on Redfin may be a combination of days on market, last open house, last update, etc instead of a Boolean flag called "is_active". How can we expect models to generate correct SQL at the enterprise level without providing a support structure of business context? The model can only be so good.

brendongeils 3y ago

previous palantir & scale ai engineer w/ an ai data science startup here. we found that RAG on a large corpus was the best outcome, not just RAG on queries, but RAG across any data the org uploaded to our system, athena intelligence. semantic search on documents, slack, and other sources provided better outcomes w/ on-prem models too. this has allowed us to unlock some novel analytics workflows including multi-modal, figma style, etc. we also just opened up our platform for public access. currently working with enterprises like anheuser-busch. https://app.athenaintelligence.ai/ https://app.athenaintelligence.ai/

zeroq 3y ago

For anyone who says it's useless because it's only 75% correct, please consider these two points: (1) this is the first instalment, and it's already close to be a thousand times more useful for product owners and analytics than any airtable you can imagine. (2) as much as I love being on point on every challenge, we're leaving in "good enough" economics for quite some time, and if this will be close enough that will be good enough for business.

Too 3y ago

Heh. Then there are those that say SQL already reads like natural language. What happens if you feed the model with sql as input?

saigal 3y ago

can you fine-tune the LLM?

moltar 3y ago

I have a use case where there's no DDL, just a description of the tables, with provided data types, and descriptions of all of the columns, in JSON. I could generate DDL statements, of course. But wondering if this is the best way to hint at the model of the database structure. Also, how would you go about supplying the very verbose descriptions of all of the data types? Would SQL comments be best? Postgres-style column comments? Thanks!

moltar 3y ago

The card states: > This model was evaluated on SQL-Eval, a PostgreSQL-based evaluation framework developed by Defog for testing and alignment of model capabilities. But this explains the testing part. However, does it mean that only the PostgreSQL-flavour of SQL is supported? Would it work for Trino flavour?