6 ms·

This is immediately useful. The number of awkwardly nested subqueries that I write because I don't have this kind of functionality in BigQuery is a huge bummer.

This is immediately useful. The number of awkwardly nested subqueries that I write because I don't have this kind of functionality in BigQuery is a huge bummer. I am curious, how many Haskell programmers do you all have? And more seriously, do you have any plans to help provide this functionality to non-Postgres SQLs, or at least help those trying to take inspiration from it?

djk447 5y ago
(NB: Post author here) We don't have many Haskell programmers, we mostly work in Rust, C and Go, but we're always open to new things... This is pretty Postgres specific. From the beginning Postgres has focused on extensibility and allowed this sort of stuff with custom functions/operators/types. Many other SQL variants don't have that. It's one of the main things that sets Postgres apart from other databases, see Stonebreaker's great history of this, specifically the stuff on Object Relational databases [1]. We're pretty focused on building on top of Postgres because of that functionality. We do have some other stuff to make Postgres more scalable, and you're welcome to try us out, but if there's something specific that BigQuery offers that you need feel free to file a github issue around that too. But yeah, no plans to do things like this in other databases, they just don't have the infrastructure... [1]:https://people.cs.umass.edu/~yanlei/courses/CS691LL-f06/papers/SH05.pdf https://people.cs.umass.edu/~yanlei/courses/CS691LL-f06/pape...
- akulkarni 5y ago
  (TimescaleDB co-founder) Agree - what would you need to see in PostgreSQL / TimescaleDB to enable you to switch from Big Query? We are all ears :-)
  - methyl 5y ago
    Not the op, but infinite scalability without any infrastructure work required besides loading the data is what holds me on BigQuery
    - akulkarni 5y ago
      Thanks for sharing. "infinite scalability" is something we are aiming for in our new vision for Timescale Cloud: https://blog.timescale.com/blog/announcing-the-new-timescale-cloud-and-a-new-vision-for-the-future-of-database-services-in-the-cloud/ https://blog.timescale.com/blog/announcing-the-new-timescale... We are not there today, but we are making progress. Any other feedback - please let us know :-)
fifilura 5y ago
I wonder if the array functions in the presto engine (used for example in Amazon Athena) should be able to handle this too? https://prestodb.io/docs/0.217/functions/array.html https://prestodb.io/docs/0.217/functions/array.html
- fifilura 5y ago
  Ok, a 15 minute hack. It may be possible to make it look nicer. And it looked better in TFA, so good job! SELECT device_id, reduce( zip_with( array_sort(zip(array_agg(ts), array_agg(val))), slice(array_sort(zip(array_agg(ts), array_agg(val))),2, 5000), (a,b) -> abs(a.field1 - b.field1)) , 0, (s, x) -> s + COALESCE(x,0), s -> s) as res FROM ( VALUES (1, 2, 1), (1, 3, 0), (1, 9, 3), (1, 3, 4), (2, 2, 1), (2, 8, 0), (3, 4, 3) ) AS t(device_id, val, ts) GROUP BY device_id device_id res 1 14 3 0 2 6
jpitz 5y ago
Minor nitpick: Nested subqueries ARE awkward, which is why I would express SELECT device id, sum(abs_delta) as volatility FROM ( SELECT device_id, abs(val - lag(val) OVER (PARTITION BY device_id ORDER BY ts)) as abs_delta FROM measurements WHERE ts >= now() - '1 day'::interval) calc_delta ) GROUP BY device_id; this way: WITH temperature_delta_past_day AS ( SELECT device_id, abs(val - lag(val) OVER (PARTITION BY device_id ORDER BY ts)) as abs_delta FROM measurements WHERE ts >= now() - '1 day'::interval //edit - the remainder of this line is a typo: ) calc_delta ) SELECT device id, sum(abs_delta) as volatility FROM temperature_delta_past_day GROUP BY device_id; To me, it is a lot more natural to use SQL's CTE syntax to 'predefine' my projections before making use of them, instead of trying to define them inline - like the difference between when you'd define a lambda directly inline vs defining a separate function for it. I don't know if trying to embed a DataFrame-esque api inside of SQL is a thing that would benefit me, but it is an interesting idea.
- fifilura 5y ago
  I agree. There is something magic with CTEs, just moving things around a little bit makes it so much easier to comprehend. You could even move the val and lag(val) to two different columns and do the abs() in the summary. That way you can query temperature_delta_past_day and see for yourself what it does.
- djk447 5y ago
  (NB: Post author here) Yeah. CTEs definitely make it a bit easier to read, though some people get more confused by them, especially because they don't exist in all SQL variants. And totally agree with that last bit! We want to see if it's useful for folks, it's released experimentally now and we'll see what folks can do with it. One thing that's fun and that we may do a post explaining a bit more is that these pipelines are actually values as well, so the transforms that you run can be stored in a column in the database as well. And that starts offering some really mind-bending stuff. The example I used was building on the one in the post except now you have thermocouples with different calibration curves. You can actually store a polynomial or other calibration curve in a column and apply the correct calibration to each individual thermocouple with a JOIN...which is kinda crazy, but pretty awesome. So we want to figure out how to use these and what people can do with them and see where it takes us.

djk447 5y ago

(NB: Post author here) We don't have many Haskell programmers, we mostly work in Rust, C and Go, but we're always open to new things... This is pretty Postgres specific. From the beginning Postgres has focused on extensibility and allowed this sort of stuff with custom functions/operators/types. Many other SQL variants don't have that. It's one of the main things that sets Postgres apart from other databases, see Stonebreaker's great history of this, specifically the stuff on Object Relational databases [1]. We're pretty focused on building on top of Postgres because of that functionality. We do have some other stuff to make Postgres more scalable, and you're welcome to try us out, but if there's something specific that BigQuery offers that you need feel free to file a github issue around that too. But yeah, no plans to do things like this in other databases, they just don't have the infrastructure... [1]:https://people.cs.umass.edu/~yanlei/courses/CS691LL-f06/papers/SH05.pdf https://people.cs.umass.edu/~yanlei/courses/CS691LL-f06/pape...

akulkarni 5y ago
(TimescaleDB co-founder) Agree - what would you need to see in PostgreSQL / TimescaleDB to enable you to switch from Big Query? We are all ears :-)
- methyl 5y ago
  Not the op, but infinite scalability without any infrastructure work required besides loading the data is what holds me on BigQuery
  - akulkarni 5y ago
    Thanks for sharing. "infinite scalability" is something we are aiming for in our new vision for Timescale Cloud: https://blog.timescale.com/blog/announcing-the-new-timescale-cloud-and-a-new-vision-for-the-future-of-database-services-in-the-cloud/ https://blog.timescale.com/blog/announcing-the-new-timescale... We are not there today, but we are making progress. Any other feedback - please let us know :-)

fifilura 5y ago

I wonder if the array functions in the presto engine (used for example in Amazon Athena) should be able to handle this too? https://prestodb.io/docs/0.217/functions/array.html https://prestodb.io/docs/0.217/functions/array.html

fifilura 5y ago
Ok, a 15 minute hack. It may be possible to make it look nicer. And it looked better in TFA, so good job! SELECT device_id, reduce( zip_with( array_sort(zip(array_agg(ts), array_agg(val))), slice(array_sort(zip(array_agg(ts), array_agg(val))),2, 5000), (a,b) -> abs(a.field1 - b.field1)) , 0, (s, x) -> s + COALESCE(x,0), s -> s) as res FROM ( VALUES (1, 2, 1), (1, 3, 0), (1, 9, 3), (1, 3, 4), (2, 2, 1), (2, 8, 0), (3, 4, 3) ) AS t(device_id, val, ts) GROUP BY device_id device_id res 1 14 3 0 2 6

jpitz 5y ago

Minor nitpick: Nested subqueries ARE awkward, which is why I would express SELECT device id, sum(abs_delta) as volatility FROM ( SELECT device_id, abs(val - lag(val) OVER (PARTITION BY device_id ORDER BY ts)) as abs_delta FROM measurements WHERE ts >= now() - '1 day'::interval) calc_delta ) GROUP BY device_id; this way: WITH temperature_delta_past_day AS ( SELECT device_id, abs(val - lag(val) OVER (PARTITION BY device_id ORDER BY ts)) as abs_delta FROM measurements WHERE ts >= now() - '1 day'::interval //edit - the remainder of this line is a typo: ) calc_delta ) SELECT device id, sum(abs_delta) as volatility FROM temperature_delta_past_day GROUP BY device_id; To me, it is a lot more natural to use SQL's CTE syntax to 'predefine' my projections before making use of them, instead of trying to define them inline - like the difference between when you'd define a lambda directly inline vs defining a separate function for it. I don't know if trying to embed a DataFrame-esque api inside of SQL is a thing that would benefit me, but it is an interesting idea.

fifilura 5y ago
I agree. There is something magic with CTEs, just moving things around a little bit makes it so much easier to comprehend. You could even move the val and lag(val) to two different columns and do the abs() in the summary. That way you can query temperature_delta_past_day and see for yourself what it does.
djk447 5y ago
(NB: Post author here) Yeah. CTEs definitely make it a bit easier to read, though some people get more confused by them, especially because they don't exist in all SQL variants. And totally agree with that last bit! We want to see if it's useful for folks, it's released experimentally now and we'll see what folks can do with it. One thing that's fun and that we may do a post explaining a bit more is that these pipelines are actually values as well, so the transforms that you run can be stored in a column in the database as well. And that starts offering some really mind-bending stuff. The example I used was building on the one in the post except now you have thermocouples with different calibration curves. You can actually store a polynomial or other calibration curve in a column and apply the correct calibration to each individual thermocouple with a JOIN...which is kinda crazy, but pretty awesome. So we want to figure out how to use these and what people can do with them and see where it takes us.