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

Building AI agents to query your databases

blog.dust.tt

Post reply on HN
Print view View original

21–30 of 56 posts

Re: Building AI agents to query your databases

#21

Post by aargh_aargh »

troupo wrote: post #11
ritz_labringue wrote: post #6

Earlier quoted context omitted.

It does require writing good instructions for the LLM to properly use the tables, and it works best if you carefully pick the tables that your agent is allowed to use beforehand. We have many users that use it for every day work with real data (definitely not toy problems).

> It does require writing good instructions for the LLM to properly use the tables --- start quote --- prompt engineering is nothing but an attempt to reverse-engineer a non-deterministic black box for which any of the parameters below are unknown: - training set - weights - constraints on the model - layers between you and the model that transform both your input and the model's output that can change at any time -…

What else is engineering then if not taming the unknown and the unknowable? How is building a bridge any different? Do you know everything in advance about the composition of terrain, the traffic, the wind and the earthquakes? Or are you making educated guesses about unknown quantities to get something that fits into some parameters that are Good Enough(TM) for the given purpose?

Re: Building AI agents to query your databases

#22

Post by tillvz »

mritchie712 wrote: post #18
bob1029 wrote: post #5

> This abstraction shields users from the complexity of the underlying systems and allows us to add new data sources without changing the user experience. Cursed mission. These sorts of things do work amazingly well for toy problem domains. But, once you get into more complex business involving 4-way+ joins, things go sideways fast. I think it might be possible to have a human in the loop during the SQL authoring pha…

Using a semantic layer is the cleanest way to have 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 more consistent at writing a small JSON vs. hundreds of lines of…

Completely agree! A semantic layer is essential for scaling analytics to enterprise complexity.

Another alternative here is Veezoo [0], which combines the semantic layer (a Knowledge Graph) and self-service analytics into one integrated solution.

We built it specifically for the analytics use-case for both the "data persona" to manage the semantic layer, as well as for the "business persona" to analyze the data.

If you’re looking for a semantic-layer + (embedded) BI solution right out of the box. This could be a fit.

0 - https://www.veezoo.com

Re: Building AI agents to query your databases

#23

Post by achierius »

aargh_aargh wrote: post #21
troupo wrote: post #11

Earlier quoted context omitted.

> It does require writing good instructions for the LLM to properly use the tables --- start quote --- prompt engineering is nothing but an attempt to reverse-engineer a non-deterministic black box for which any of the parameters below are unknown: - training set - weights - constraints on the model - layers between you and the model that transform both your input and the model's output that can change at any time -…

What else is engineering then if not taming the unknown and the unknowable? How is building a bridge any different? Do you know everything in advance about the composition of terrain, the traffic, the wind and the earthquakes? Or are you making educated guesses about unknown quantities to get something that fits into some parameters that are Good Enough(TM) for the given purpose?

> and the unknowable

This is the crux. Sure, for high level software (e.g. Web apps), many parts of the system will feel like black boxes, but low-level software does not generally have this problem. Sure, sometimes you have to deal with a binary blob driver, but more often than not you're in control of or and to debug most all of the software running on your system.

> Building a bridge

There should NOT be significant unknowns when you're building a bridge, this is how people die. You turn those parameters into "knowns with high confidence", which is not something you can even begin to do for the LLM parameters described above.

Re: Building AI agents to query your databases

#24

Post by mattpope »

I feel like this misses the most useful technique for this (or really glossed over any techniques at all): flat denormalized schemas. If you have an ENUM column, much like a human the LLM won't have enough context to query it. And much like a human, 4 way JOINs get tricky. Having one large sparse maxtrix-like table with many boolean columns like `is_sale_complete`, `is_sale_cancelled` data warehouse style is straight up the key to effectively use an LLM to generate SQL.

Re: Building AI agents to query your databases

#25

Post by RajT88 »

LaGrange wrote: post #16

I mean this is generally repulsive, but please I beg of you, run this exclusively against a read only replica. I mean, you should have one for exploratory queries _anyway_, but nobody ever does that. "Validating the query to ensure it's safe and well-formed" all I can say to that is "ROFL. LMAO."

Thank you.

"AI Bot, summarize the number of logins this week broken down by email address and password hash".

Re: Building AI agents to query your databases

#26

Post by totalhack »

gcanyon wrote: post #19

My general method for things like this is to: 1. Get a .dot file of the database. Many tools will export this. 2. Open the .dot in a tool I built for the purpose. 3. Select the tables I'm interested in, and export a subset of the .dot file representing just those tables and relationships. 4. Hand that subset .dot file to the LLM and say, "given this schema, write a query -- here's what I want: " That gets the job don…

You are doing a lot of the work a semantic layer would do for you. I wonder if you would have better luck having the LLM talk to a semantic layer instead of directly to the database.

Re: Building AI agents to query your databases

#27

Post by efromvt »

mritchie712 wrote: post #18
bob1029 wrote: post #5

> This abstraction shields users from the complexity of the underlying systems and allows us to add new data sources without changing the user experience. Cursed mission. These sorts of things do work amazingly well for toy problem domains. But, once you get into more complex business involving 4-way+ joins, things go sideways fast. I think it might be possible to have a human in the loop during the SQL authoring pha…

Using a semantic layer is the cleanest way to have 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 more consistent at writing a small JSON vs. hundreds of lines of…

JSON generation against a semantic layer and validation loops is definitely the easiest way to get high 9s success for going directly to a correct query from text. For the human in the loop cases, going directly to SQL can be fun - I’ve toyed with a SQL-like semantic layer that removes the need for direct table access and joins, which removes two of the risk points for LLMs going off the rails while still leveraging a lot of the baked in knowledge about SQL syntax (window functions, transformations, etc) that can be hard to exhaustively bake into the semantic layer. (It’s annoying when the semantic layer doesn’t quite have what you need.)

Re: Building AI agents to query your databases

#28

Post by jt_b »

totalhack wrote: post #26
gcanyon wrote: post #19

My general method for things like this is to: 1. Get a .dot file of the database. Many tools will export this. 2. Open the .dot in a tool I built for the purpose. 3. Select the tables I'm interested in, and export a subset of the .dot file representing just those tables and relationships. 4. Hand that subset .dot file to the LLM and say, "given this schema, write a query -- here's what I want: " That gets the job don…

You are doing a lot of the work a semantic layer would do for you. I wonder if you would have better luck having the LLM talk to a semantic layer instead of directly to the database.

Can you talk more about what an implementation of such a semantic layer would look like?

Re: Building AI agents to query your databases

#29

Post by breadwinner »

gcanyon wrote: post #19

My general method for things like this is to: 1. Get a .dot file of the database. Many tools will export this. 2. Open the .dot in a tool I built for the purpose. 3. Select the tables I'm interested in, and export a subset of the .dot file representing just those tables and relationships. 4. Hand that subset .dot file to the LLM and say, "given this schema, write a query -- here's what I want: " That gets the job don…

Something like this is what I do also. Is there another way? How are others doing it?

Re: Building AI agents to query your databases

#30

Post by mritchie712 »

efromvt wrote: post #27
mritchie712 wrote: post #18

Earlier quoted context omitted.

Using a semantic layer is the cleanest way to have 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 more consistent at writing a small JSON vs. hundreds of lines of…

JSON generation against a semantic layer and validation loops is definitely the easiest way to get high 9s success for going directly to a correct query from text. For the human in the loop cases, going directly to SQL can be fun - I’ve toyed with a SQL-like semantic layer that removes the need for direct table access and joins, which removes two of the risk points for LLMs going off the rails while still leveraging…

duckdb has a `json_serialize_sql` function that we've been messing with. It could be an interesting middle ground. It lets you write SQL as JSON.
Post reply on HN