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

11–20 of 379 posts

Re: Getting AI to write good SQL

#11

Post by flysand7 »

mousetree wrote: post #6

Out of all the AI tools and models I’ve tried, the most disappointing is the Gemini built into BigQuery. Despite having well named columns with good descriptions it consistently gets nowhere close to solving the problem.

Having written more SQL than any other programming language by now, every time I've tried to use AI to write the query for me, I'd spend way more time getting the output right than if I'd just written it myself.

As a quick aside there's one thing I wish SQL had that would make writing queries so much faster. At work we're using a DSL that has one operator that automatically generates joins from foreign key columns, just like

    credit.CLIENT->NAME
And you got clients table automatically joined into the query. Having to write ten to twenty joins for every query is by far the worst thing, everything else about writing SQL is not that bad.

Re: Getting AI to write good SQL

#13

Post by ljm »

mritchie712 wrote: post #2

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

Re: Getting AI to write good SQL

#15

Post by dangscientist »

ljm wrote: post #13
mritchie712 wrote: post #2

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

[flagged]

Re: Getting AI to write good SQL

#16

Post by fhkatari »

ljm wrote: post #13
mritchie712 wrote: post #2

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

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?

Re: Getting AI to write good SQL

#17

Post by tango12 »

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 ecommmerce transaction yesterday?

> Why is customer acquisition cost trending up?

> Why was the campaign in NYC worse than the same in SF?

Re: Getting AI to write good SQL

#18

Post by [deleted] »

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?

[deleted]

Re: Getting AI to write good SQL

#19

Post by cdavid »

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…

My observation is the latter, but I agree the results fall short of expectations. Business will often want last minute change in reporting, don't get what they want at the right time because lack of analysts, and hope having "infinite speed" will solve the problem.

But ofc the real issue is that if your report metrics change last minute, you're unlikely to get good report. That's a symptom of not thinking much about your metrics.

Also, reports / analysis generally take time because the underlying data are messy, lots of business knowledge encoded "out of band", and poor data infrastructure. The smarter analytics leaders will use the AI push to invest in the foundations.

Re: Getting AI to write good SQL

#20

Post by mynegation »

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…

To be fair, these don’t look like SQL problems either. SQL answers “what”, not “why” questions. The goal of text2sql is to free up analyst time to get through “what” much faster and - possibly- focus on “why” questions.
Post reply on HN