4 ms·
text-to-sql is a dead end. There's no way for a model to correctly interpret the meaning of every column in a real world database using the `information_schema`
by mritchie712 3y ago
text-to-sql is a dead end. There's no way for a model to correctly interpret the meaning of every column in a real world database using the `information_schema` alone. Most cloud warehouses (e.g. Snowflake) don't use foreign keys, so you don't even know the joins.
Imagine you hire a highly skilled data analyst (e.g. 9 out of 10 proficiency in SQL) and start asking them questions about your database. They won't answer them, they'll ask you more questions. The conversation would go something like:
you: what is our churn rate by channel?
new analyst: where do we store "channel"? what do we use to process payments? where is that data stored? do we include discounts in MRR / churn? etc.
If a human can't do it, an LLM can't either. An LLM isn't able to write the SQL from scratch get the right answers without a ton of additional context. We're working on an approach using a semantic layer at https://www.definite.app/ https://www.definite.app/ if you're interested in this sort of thing.
- dragonwriter 3y ago> There's no way for a model to correctly interpret the meaning of every column in a real world database using the `information_schema` alone. Why would text-to-sql be limited to information_schema alone? Human analysts would use additional documentation, why wouldn't an LLM-based text-to-sql system?
- mritchie712 3y agoI should have clarified. There's a large number of apps that are: 1. taking info strictly from SQL (e.g. information_schema, query history) 2. taking a user input / question 3. writing SQL to answer that question An app like this is what I call "text-to-sql". Totally agree a better system would pull in additional documentation (which is what we're doing), but I'd no longer consider it "text-to-sql". In our case, we're not even directly writing SQL, but rather generating semantic layer queries (i.e. https://cube.dev/ https://cube.dev/).
- tosh 3y agoproviding some context about the data, the schema + samples from the entries works quite well, definitely room for improvement but already quite usable imho
- mritchie712 3y agoAgreed, very usable if you know SQL and iterate from whatever the LLM spits out.
- sjtly16 3y agoagree, with familiarity with SQL one can use it as a reference for generating the first draft or even the final query
- toddmorey 3y agoAgreed, but perhaps more semantic meaning could be expressed in metadata for tables and columns, extending beyond what's typically found in information_schema. (This may be the semantic layer you are talking about.) Here it seems MQL isn't a query language as much as it's a text-to-SQL translator and you're right... without a bit more understanding of the data's role and purpose and intent it's a hard job for anyone, human or AI. It strikes me that as I write an sql statement I'm not only using knowledge of sql but also knowledge of domain and database structure that I don't even think about until I need to show someone else how to do the query.
- boredemployee 3y agoYes. And also, don't forget that different stakeholders ask in different ways, using different words, which turns out the situation in a nightmare. But I think it's possible to make it to work with mid-size databases.