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

Getting AI to write good SQL

cloud.google.com

Post reply on HN
Print view View original

71–80 of 379 posts

Re: Getting AI to write good SQL

#71

Post by rectang »

hosel wrote: post #66
rectang wrote: post #35

> We will cover state-of-the-art [...] how we approach techniques that allows the system to offer virtually certified correct answers. I don't need AI to generate perfect SQL, because I am never going to trust the output enough to copy/paste it — the risk of subtle semantic errors is too high, even if the code validates. Instead, I find it helpful for AI to suggest approaches — after which I will manually craft the S…

Really? In my experience it’s been pretty good (using Pydantic)! I read over before I execute it, but it’s never done anything malicious.

I don't trust myself to craft a prompt in natural language which completely specifies my intent as codified with the precision of a programming language.

I also tend to turn to AI for advising me on difficult use cases, and most of the time it's for production code rather than one-offs. The easy cases, I just write myself because it's more mental effort to review code for subtle errors than it is to write it.

Re: Getting AI to write good SQL

#72

Post by josephg »

gerdesj wrote: post #68

[flagged]

If you know SQL, then yeah! But if you don't know SQL, using an AI to write a few queries & debug them is a great way to learn it.

I'm pretty comfortable with sql but still found it a fabulous tool recently. I have a sql database which describes a tree of some ~600k events. Each event is in a session (via session_id). Most events have a parent event - and trees of events can involve multiple sessions.

I wanted to add two derived columns to my events table. For each event, I wanted to name the root event for that event's tree and the root event within this session. I had code in typescript to do it - but unsurprisingly it was pretty slow. Well, it turns out you can write a recursive SQL query which can traverse the graph and populate those columns. I had no idea that was even possible.

ChatGPT managed it pretty well - though I ended up making a bunch of tweaks to the query it suggested to simplify it. I learned a bunch of SQL in the process - and that was cool! Obviously I could have read the SQL documentation and figured it out myself, but it was faster & easier using chatgpt. Writing SQL queries is a fantastic use case for LLMs.

Re: Getting AI to write good SQL

#73

Post by [deleted] »

hosel wrote: post #66
rectang wrote: post #35

> We will cover state-of-the-art [...] how we approach techniques that allows the system to offer virtually certified correct answers. I don't need AI to generate perfect SQL, because I am never going to trust the output enough to copy/paste it — the risk of subtle semantic errors is too high, even if the code validates. Instead, I find it helpful for AI to suggest approaches — after which I will manually craft the S…

Really? In my experience it’s been pretty good (using Pydantic)! I read over before I execute it, but it’s never done anything malicious.

[deleted]

Re: Getting AI to write good SQL

#75

Post by [deleted] »

rectang wrote: post #35

> We will cover state-of-the-art [...] how we approach techniques that allows the system to offer virtually certified correct answers. I don't need AI to generate perfect SQL, because I am never going to trust the output enough to copy/paste it — the risk of subtle semantic errors is too high, even if the code validates. Instead, I find it helpful for AI to suggest approaches — after which I will manually craft the S…

[deleted]

Re: Getting AI to write good SQL

#76

Post by [deleted] »

tango12 wrote: post #17

What’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…

[deleted]

Re: Getting AI to write good SQL

#77

Post by mritchie712 »

fhkatari wrote: post #16
ljm wrote: post #13

Earlier quoted context omitted.

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

You move all the tools to debug and inspect slow queries, in a completely unsupported JSON environment, with prompts not to make up column names. And this is progress?

The JSON compiles to SQL. Have you used a semantic layer? You might have a different opinion if you tried one.

Re: Getting AI to write good SQL

#78

Post by zxexz »

I find Gemini excellent for sql. Wouldn’t consider myself an expert in many things, but in sql and database design id consider myself close. I like writing queries and doing the architecture, and that’s where it’s exceptionally helpful. The massive context length combined with pointed questions means i can just dump the entire DDL, and ask “what am i missing?”. It really is an excellent tool for helping with times like checks and catching dumb errors on complex databases.

Re: Getting AI to write good SQL

#79

Post by roywiggins »

deadbabe wrote: post #67

All this LLM written SQL stuff sounds great until you realize if you don’t really know SQL you won’t be able to debug or fix any broken SQL an LLM generates. Thus, this is mainly just a tool for true experts to do less work and still get paid the same, not a tool for beginners to rise to the level of experts.

It depends, sometimes just feeding back broken SQL with "that didn't return any rows, can you fix it" and it comes up with something that works. Or "you're looking at the wrong entity, look at this table instead" or whatever, without knowing how to write competent SQL.

Obviously being able to at least read a bit of SQL and understanding the basic idea of relational databases helps loads.

Re: Getting AI to write good SQL

#80

Post by hsbauauvhabzb »

rectang wrote: post #35

> We will cover state-of-the-art [...] how we approach techniques that allows the system to offer virtually certified correct answers. I don't need AI to generate perfect SQL, because I am never going to trust the output enough to copy/paste it — the risk of subtle semantic errors is too high, even if the code validates. Instead, I find it helpful for AI to suggest approaches — after which I will manually craft the S…

Explain that to the average manager or junior engineer, both who don’t care about your desire to build well but not fast.
Post reply on HN