This is pretty simple in any foundation model, provide a well commented schema and ask for the query
there’s two kinds of people using AI to generate SQL…those who say it’s already solved and those who say it’ll be impossible to ever solve
Getting AI to write good SQL
51–60 of 379 posts
Re: Getting AI to write good SQL
#52This is on howto to to write good SELECTS, not SQL. AI is good enough to also create schemas from spec, migrate, explore databases, testing etc which tgis article does not touch upon
Re: Getting AI to write good SQL
#53In real life I find using AI for SQL dangerous. It allows people that don't know what they do to write queries that can significantly impact servers. In my world databases are relatively big for most developers, but not huge. Sometimes when I want to fine tune a query I am challenging AI to provide a better solution. I give it the already optimized query and I ask for better. I never got a better answer, sometimes be…
> I give it the already optimized query and I ask for better. I never got a better answer..
This was my experience as well. However I have observed that things have been improving this regard. Newer LLMs do perform much better. And I suspect they will continue to get better over time.
Re: Getting AI to write good SQL
#54the short answer: use a semantic layer. It's the cleanest way to give the right context and the best place to pull a human in the loop. A human can validate and create all important metrics (e.g. what does "monthly active users" really mean) then an LLM can use that metric definition whenever asked for MAU. With a semantic layer, you get the added benefit of writing queries in JSON instead of raw SQL. LLM's are much…
still need someone to build the semantic layer, why not use text2sql or something similar for that
Re: Getting AI to write good SQL
#55For the problems where it would matter the most, these tools seem to help the least. The hardest problem domains don't have just one schema to worry about. They have hundreds. If you need to spin up a personal blog or todo list tracker, I have no doubt that Google, et. al. can take you exactly where you want to go.
and then add in ambiguity in the business terms / intention behind the query. still a big need for something like semantic layer or ontology to sit between business and at least right now that stuff hasn’t been automated away yet (it should be though)
Re: Getting AI to write good SQL
#56the short answer: use a semantic layer. It's the cleanest way to give the right context and the best place to pull a human in the loop. A human can validate and create all important metrics (e.g. what does "monthly active users" really mean) then an LLM can use that metric definition whenever asked for MAU. With a semantic layer, you get the added benefit of writing queries in JSON instead of raw SQL. LLM's are much…
> you get the added benefit of writing queries in JSON instead of raw SQL. I’m sorry, I can’t. The tail is wagging the dog. dang, can you delete my account and scrub my history? I’m serious.
This may be the best comment on Hacker News ever.
Re: Getting AI to write good SQL
#57Earlier quoted context omitted.
How do you validate it if you don’t know the syntax? Or are you saying that looking up syntax –> semantics is significantly quicker than semantics –> syntax? Which I don’t find to be the case. What takes time is grokking the semantics in context, which you have to do in both cases.
https://regex101.com/
That doesn’t answer the question. By “validate”, I mean “prove to yourself that the regular expression is correct”. Much like with program code, you can’t do that by only testing it. You need to understand what the expression actually says.
Re: Getting AI to write good SQL
#58What’s the eventual goal of text to sql? Is it to build a copilot for a data analyst or to get business insight without going through an analyst? If it’s the latter - then imho no amount of text to sql sophistication will solve the problem because it’s impossible for a non analyst to understand if the sql is correct or sufficient. These don’t seem like text2sql problems: > Why did we hit only 80% of our daily ecommme…
> These don’t seem like text2sql problems: Correct, but I would propose two things to add to your analysis: 1. Natural language text is a universal input to LLM systems 2. text2sql makes the foundation of retrieving the information that can help answer these higher-level questions And so in my mind, the goals for text2sql might be a copilot (near-term), but the long-term is to have a good foundation for automating te…
yeah I agree with this - good text2sql is essential but just one part of a larger stack that will actually get there. Seems possible tho
Re: Getting AI to write good SQL
#59Earlier quoted context omitted.
This comment appears frequently and always surprises me. Do people just... not know regex? It seems so foreign to me. It's not like it's some obscure thing, it's absolutely ubiquitous. Relatively speaking it's not very complicated, it's widely documented, has vast learning resources, and has some of the best ROI of any DSL. It's funny to joke that it looks like line noise, but really, there is not a lot to learn to u…
Its something you use so sparingly far away usually that never sticks around
A cheat sheet is just a web search away.
Re: Getting AI to write good SQL
#60Earlier quoted context omitted.
But “text to sql” isn’t an algorithm.
The processes the people want the sql for are likely filled with algo’s. An exec wants info in a known domain, set up a text to sql system with lots of context and testing to generate queries. If they think they have something good, get an expert to test and productionise it. “Thank you for your request. Can you walk me through the steps you’d use to do this manually? What things would you watch out for? What kind of…
This is the same fallacy as low-code/no-code. If you have to check a precise algorithm, you’re effectively coding, and you need a language with the same precision as a programming language.