4 ms·
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 a
by mritchie712 1y ago
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 more consistent at writing a small JSON vs. hundreds of lines of SQL.
We[0] use cube[1] for this. It's the best open source semantic layer, but there's a couple closed source options too.
My last company wrote a post on this in 2021[2]. Looks like the acquirer stopped paying for the blog hosting, but the HN post is still up.
0 - https://www.definite.app/ https://www.definite.app/
1 - https://cube.dev/ https://cube.dev/
2 - https://news.ycombinator.com/item?id=25930190 https://news.ycombinator.com/item?id=25930190
- ljm 1y ago> 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.
- dangscientist 1y ago[flagged]
- fhkatari 1y agoYou 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 1y ago[deleted]
- mritchie712 1y agoThe JSON compiles to SQL. Have you used a semantic layer? You might have a different opinion if you tried one.
- e3bc54b2 1y agoAs someone who actually wrote a JSON to (limited) SQL transpiler at $DAYJOB, as much fun as I had designing and implementing that thing and for as many problems it solved immediately, 'tail wagging the dog' is the perfect description.
- ljm 1y agoSELECT email FROM users WHERE deleted_at IS NOT NULL OR status = 'active' seems more semantic to me at first glance than piping this into a JSON->SQL library { "_select": "email", "_table": "users", "_where": { "deleted_at": { "_is": { "_not": SQL_NULL_VALUE } }, "_or": [ { "status": "inactive" }, ] } } which is usually how these things end up looking.
- indymike 1y agoThis may be the best comment on Hacker News ever.
- sgarland 1y agoI think that honor still belongs to "Did you win the Putnam?" [0] but this is definitely still in the top 5. [0]: https://news.ycombinator.com/item?id=35079 https://news.ycombinator.com/item?id=35079
- andndnndd 1y ago[flagged]
- IncreasePosts 1y agoYou're right, it's a bit ridiculous. This is a perfect time to use xml instead of json.
- meindnoch 1y agoClearly the right solution is to use XML Object Notation, aka XON™! JSON: {"foo": ["bar", 42]} XON: <Object> <Property> <Key>foo</Key> <Value> <Array> <String>bar</String> <Number>42</Number> </Arra> </Value> </Property> </Object> It gives you all the flexibility of JSON with the mature tooling of XML! Edit: jesus christ, it actually exists https://sevenval.gitbook.io/flat/reference/templating/oxn https://sevenval.gitbook.io/flat/reference/templating/oxn
- indymike 1y agoWe had an IT guy who once bought an XML<->JSON server for $12,000. Very proud of his rack of "data appliances". It made XML like XON out of JSON and JSON that was a soup of elements attributes and ___content___, thus giving you the complexity of XML in JSON. I don't think it got used once by our dev team, and I'm pretty sure it never processed a byte of anything of value.
- mritchie712 1y agoLLMs are far more reliable at producing something like this: { "dimensions": [ "users.state", "users.city", "orders.status" ], "measures": [ "orders.count" ], "filters": [ { "member": "users.state", "operator": "notEquals", "values": ["us-wa"] } ], "timeDimensions": [ { "dimension": "orders.created_at", "dateRange": ["2020-01-01", "2021-01-01"] } ], "limit": 10 } than this: SELECT users.state, users.city, orders.status, sum(orders.count) FROM orders CROSS JOIN users WHERE users.state != 'us-wa' AND orders.created_at BETWEEN '2020-01-01' AND '2021-01-01' GROUP BY 1, 2, 3 LIMIT 10;
- Agraillo 1y agoThe programming languages are more predictable than human. So the rules are much easier to be "compressed" after they're basically detected when fed with big data. Your two examples imho are easily interchangeable during follow-up conversation with a decent LLM. Tested this with the following prompt and fed a c fragment and an SQL-fragment, got in both cases something like your first one > Please convert the following fragment of a programming language (auto-detect) into a json-like parsing information when language construct is represented like an object, fixed branches are represented like properties and iterative clauses (statement list for example) as array.
- sgarland 1y agoThis doesn't make sense. From a schema standpoint, table `orders` presumably has a row per order, with columns like `user_id`, `status` (as you stated), `created_at` (same), etc. Why would there be a `count` column? What does that represent? From a query standpoint, I'm not sure what this would accomplish. You want the cartesian product of `users` and `orders`, filtered to all states except Washington, and where the order was created in 2020? The only reason I can think of to use a CROSS JOIN would be if there is no logical link between the tables, but that doesn't make any sense for this, because users:orders should be a 1:M relationship. Orders don't place themselves. I think what you might have meant would be: SELECT users.state, users.city, orders.status, COUNT(*) FROM users JOIN orders ON user.id = orders.user_id WHERE users.state != 'us-wa' AND orders.created_at BETWEEN '2020-01-01' AND '2021-01-01' GROUP BY 1, 2, 3 LIMIT 10; Though without an ORDER BY, this has no significant meaning, and is a random sampling at best. Also, if you or anyone else is creating a schema like this, _please_ don't make this denormalized mess. `orders.status` is going to be extremely low cardinality, as is `users.state` (to a lesser extent), and `users.city` (to an even lesser extent, but still). Make separate lookup tables for `city` and/or `state` (you don't even need to worry about pre-populating these, you can use GeoNames[0]). For `status`, you could do the same, or turn them into native ENUM [1] if you'd like to save a lookup. [0]: https://www.geonames.org https://www.geonames.org [1]: https://www.postgresql.org/docs/current/datatype-enum.html https://www.postgresql.org/docs/current/datatype-enum.html
- galenmarchetti 1y agostill need someone to build the semantic layer, why not use text2sql or something similar for that
- tclancy 1y agoMother of God. I can write JSON instead of a language designed for querying. What is the advantage? If I’m going to move up an abstraction layer, why not give me natural language? Lots of things turn a limited natural language grammar into SQL for you. What is JSON going to {do: for: {me}}?
- 8n4vidtmkvmk 1y agoSorry, I couldn't parse that. You didn't quote your keys
- tclancy 1y agoWas on a phone. Also, there’s more invalid than that. Plus I am lazy.
- Spivak 1y agoI find it funny people are making fun of this while every ORM builds up an object representing the query and then compiles it to SQL. SQL but as a data structure you can manipulate has thousands of implementations because it solves a real problem. This time it's because LLMs have an easier time outputting complex JSON than SQL itself.
- ses1984 1y agoAny idea why that is?
- ljm 1y agoSomething still has to convert the JSON to SQL but in this case, who is writing the JSON? An LLM? Sometimes, it's easier or more efficient to just learn the shit you're working with instead of spending 1000x the compute fobbing it off to OpenAI. Even just putting a blob of SQL in a heredoc and using a prepared statement to parameterise it is good enough. Beyond that, query building is just part of the functionality an ORM provides. The main chunk of it is the mapping of DB-level data structures to app-level models and vice-versa, particularly in an OOP context.
- fkyimeanit 1y ago>you get the added benefit of writing queries in JSON instead of raw SQL You should have written your comment in JSON instead of raw English.
- meindnoch 1y ago>you get the added benefit of writing queries in JSON instead of raw SQL ^ kids, this is what AI-induced brainrot looks like.
- jinjin2 1y agoI agree that using a semantic layer is the best way to get better precision. It is almost like a cheatsheet for the AI. But I would never use one that forced me to express my queries in JSON. The best implementations integrate right into the database so they become an integral part of regular your SQL queries, and as such also available to all your tools. In my experience, from using the Exasol Semantic Layer, it can be a totally seamless experience.
- christophilus 1y agoA semantic layer would be great. It should be a structured layer designed to make relational queries easy to write. We could call it “structured data language” or maybe “structured query language”. In all seriousness, I have some complaints about SQL (I think LINQ’s reordering of it is a good idea), but there’s no need to invent another layer on order for LLMs to be able to wrangle it.
- cmrdporcupine 1y agoThe semantic layer for database queries is (roughly) the relational algebra.