6 ms·

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 description

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.

quantadev 1y ago
Having proper constraints and foreign keys that are clear is generally all that's needed in my experience. Are you sure your tables have well defined constraints, so that the AI can be absolutely 100% sure how everything links up? SQL is very precise, but only if you're utilizing constraints and foreign key definitions well.
- carderne 1y ago
  It’s BigQuery, so it likely won’t have any of these.
  - quantadev 1y ago
    BigQuery supports all those SQL things I mentioned.
    - carderne 1y ago
      I’m just saying it’s likely they aren’t using them. But clearly you should if you want LLMs to do anything useful.
flysand7 1y ago
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.
- emptysea 1y ago
  That's one of the features of EdgeQL: select Movie { id, title, actors: { name } }; https://docs.geldata.com/learn/edgeql#select-objects https://docs.geldata.com/learn/edgeql#select-objects Although I think good enough language server / IDE could automatically insert the join when you typed `credit.CLIENT->NAME`
- efromvt 1y ago
  (Shameless plug) writing the same joins over and over (and refactoring when you update stuff) was one of my biggest boilerplate annoyances with SQL - I’ve tried to fix that while still keeping the rest of SQL in https://trilogydata.dev/ https://trilogydata.dev/
- galenmarchetti 1y ago
  yeah we’re doing something similar under the hood at AstroBee. it’s way way way easier to handle joins this way. imo any hope of really leveraging llms in this context needs this + human review on additions to a shared ontology/semantic layer so most of the nuanced stuff is expressed simply and reviewed by engineering before business goes wild with it
- emmelaich 1y ago
  I'd like there to be a function or macro for a bunch of joins, say DEFINE products_by_order AS orders o JOIN order_items oi ON o.order_id = oi.order_id JOIN products p ON oi.product_id = p.product_id You could make it visible to the DB rather than just a macro so it could optimise it by caching etc. Sort of like a view but on demand.
  - icedchai 1y ago
    Sounds like a CTE?
- aradox66 1y ago
  Can't recommend dbt highly enough to get over the SQL boilerplate problem. After using dbt for a few years it's unimaginable to work without it.

quantadev 1y ago

Having proper constraints and foreign keys that are clear is generally all that's needed in my experience. Are you sure your tables have well defined constraints, so that the AI can be absolutely 100% sure how everything links up? SQL is very precise, but only if you're utilizing constraints and foreign key definitions well.

carderne 1y ago
It’s BigQuery, so it likely won’t have any of these.
- quantadev 1y ago
  BigQuery supports all those SQL things I mentioned.
  - carderne 1y ago
    I’m just saying it’s likely they aren’t using them. But clearly you should if you want LLMs to do anything useful.

flysand7 1y ago

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.

emptysea 1y ago
That's one of the features of EdgeQL: select Movie { id, title, actors: { name } }; https://docs.geldata.com/learn/edgeql#select-objects https://docs.geldata.com/learn/edgeql#select-objects Although I think good enough language server / IDE could automatically insert the join when you typed `credit.CLIENT->NAME`
efromvt 1y ago
(Shameless plug) writing the same joins over and over (and refactoring when you update stuff) was one of my biggest boilerplate annoyances with SQL - I’ve tried to fix that while still keeping the rest of SQL in https://trilogydata.dev/ https://trilogydata.dev/
galenmarchetti 1y ago
yeah we’re doing something similar under the hood at AstroBee. it’s way way way easier to handle joins this way. imo any hope of really leveraging llms in this context needs this + human review on additions to a shared ontology/semantic layer so most of the nuanced stuff is expressed simply and reviewed by engineering before business goes wild with it
emmelaich 1y ago
I'd like there to be a function or macro for a bunch of joins, say DEFINE products_by_order AS orders o JOIN order_items oi ON o.order_id = oi.order_id JOIN products p ON oi.product_id = p.product_id You could make it visible to the DB rather than just a macro so it could optimise it by caching etc. Sort of like a view but on demand.
- icedchai 1y ago
  Sounds like a CTE?
aradox66 1y ago
Can't recommend dbt highly enough to get over the SQL boilerplate problem. After using dbt for a few years it's unimaginable to work without it.