6 ms·

One SQL to Rule Them All: Management of Streams and Tables

260 points by dwenzek 7y ago

dwenzek 7y ago
A really interesting proposal to unify stream processing and relational querying and to extend SQL with a semantics over time-varying relations. In particular, the authors (from the Apache Beam/Flink/Calcite projects) stress on the need to: * smooth the frontier between streams and tables by using a single concept of time-varying relations; * make explicit the difference between the event and processing times, using monotonic watermarks to tie the two; * control when these continuously varying relations are materialized, avoiding unnecessary work and producing only timely outcomes. These ideas are not new per se, but there are here pushed further and nicely combined. However, I'm wondering if SQL is the right tool for the task. For instance, the listing 2 seems complex when compared to the query expressed in plain English. In particular, I disagree that "anyone who understands enough SQL to solve a problem in a non-streaming context still has the knowledge required to solve the problem in a streaming context as well. A must read paper by who is keen on stream processing, Spark, Flink, Beam, Kafka Stream ...
- EdwardDiego 7y ago
  > However, I'm wondering if SQL is the right tool for the task. In my anecdotal experience, yep, it's a data analysis language everyone in the industry knows - BAs, product managers, developers, and data scientists.
  - dwenzek 7y ago
    Having a standard SQL extension definitely eases adoption and ability to migrate from one tool to another. But rather than adding and adding features to SQL; we maybe need a simpler way to extend/compose features. On that point, I see a contradiction in the future work section of the paper where it said that "Experience has also shown that pre-built solutions are never sufficient for all use cases; ultimately, users should be able to utilize the power of SQL to describe their own custom-windowing TVFs". But, how can a SQL user add its own custom operator?
- michaelmior 7y ago
  I agree that listing 2 is a little verbose, but once you understand the syntax (not much different from vanilla SQL), it's pretty powerful. I think writing queries this way will ultimately allow for more expressiveness than something more concise. That isn't to say that this particular SQL is necessarily the best, but I think it works well for the problem at hand.
  - dwenzek 7y ago
    Sure, SQL is powerful and I agree that any alternative would also bring its own complexity. However, my concerns are more about encapsulation rather than expressiveness and conciseness. Even if a watermark is used the same way in different queries, I will have to use the same query clause again and again; as I already have to with the join conditions. Not a fundamental issue, but something which makes me more difficult to focus on the kernel of a query.
- asavinov 7y ago
  > However, I'm wondering if SQL is the right tool for the task. For instance, the listing 2 seems complex when compared to the query expressed in plain English For me Listing 2 is more complex and less intuitive than Listing 1 (CQL). Yet, it is a general problem when we try to adopt relational (and SQL) concepts for solving such kind of tasks (windowing, grouping, aggregation etc.) One solution is to switch to some kind of column algebra rather than relational algebra as described in [1] and [2] (it has also been applied to stream processing). [1] Joins vs. Links or Relational Join Considered Harmful: https://www.researchgate.net/publication/301764816_Joins_vs_Links_or_Relational_Join_Considered_Harmful https://www.researchgate.net/publication/301764816_Joins_vs_... [2] From Group-By to Accumulation: Data Aggregation Revisited: https://www.researchgate.net/publication/316551218_From_Group-by_to_Accumulation_Data_Aggregation_Revisited https://www.researchgate.net/publication/316551218_From_Grou...
  - dwenzek 7y ago
    Thanks for the two links. I will have a look.
- ddmd 7y ago
  It seems that the main proposal is to make SQL watermark-aware. This additional "knowledge" will allow for producing groups and windows taking into account some late and out-of-order records.
truth_seeker 7y ago
Great write-up. SQL is the most underrated language. I am doing most of the stuff mentioned in that paper right now in my project with PipelineDB extension. PostgreSQL 11 + CitusDB extension (horizontal scaling and sharding) + cstore_fdw extnesion (columnar data storage) + file_fdw (to treat file as table) + PipelineDB extension (Streaming computations and MV) + TimescaleDB extension (timeseries data storage) Also, PG 12 release will have many optimizations for table partitioning, query planning and its parallel execution
- buremba 7y ago
  What's your data volume?
  - truth_seeker 7y ago
    Around 7 TB and growing
    - FridgeSeal 7y ago
      That’s cool, what sort of query latency do you get? (For a variety of queries).
      - truth_seeker 7y ago
        We pay close attention to Data growth patterns and schema changes to keep around 70% of queries (which actually brings little over than 90% of business revenue) under 50 millisecond. Rest of 30% queries are either of low priority or backend jobs for which latency range of 200 ms to 1.5 seconds is viable for our use case.
        binakot 7y ago
        And what hardware do you use for this stack and data volume?
        FridgeSeal 7y ago
d__k 7y ago
> A time-varying relation is just what it says: a relation that changes over time ... Critically, the full suite of existing SQL operators remain valid on time-varying relations Any relation by definition is supposed to change in time. So why do we need to introduce a special kind of relations? The authors essentially confirm that there is no difference between classical relations and "time-varying" relations. Also, it is not clear how does it relate to EPL (in CEP) which is also based on SQL.
- drunkpotato 7y ago
  According to relational theory, relations are immutable sets of tuples. The way you “change” a relation is by assigning a new relation to the name, e.g.: R1 <- R1 union R2 Is the relational algebra way of “inserting” R2 into R1. Of course, most RDBMS’s (and the SQL family of languages) are only reasonably faithful implementations of relational theory under some practical constraints. But those differences re important, and I think the authors are proposing a new relational model (though I don’t think this is a totally new idea; I’m not a database researcher). One that incorporates time and history of a relation as first-class citizens of the theory, that allows sql to operate natively on both traditional relational-theoretic relations and streams of data. Pretty cool! Can anyone who knows more than me comment on how this relates to Kafka tables, which seems to me a similar concept?
- solidsnack9000 7y ago
  Relations in most systems don’t implicitly store the time a change occurred or the past versions of a row, nor do they have a model for building a “now table” from an “event table”. When people talk about time-varying tables or temporal tables, they are describing event sourcing in a SQL context.
SmokeGS 7y ago
Commenting to read again.
- grzm 7y ago
  You can use the favorite link under the submission title to effectively bookmark it. Your favorites are available in your profile.
  - SmokeGS 7y ago
    thanks grzm
  - scott_s 7y ago
    Or an upvote.
Nano2rad 7y ago
Not yet time to combine sql and nosql.
- chrisjc 7y ago
  What does this article/paper have to do with "NoSQL"?
iblaine 7y ago
Is the point of this paper that SQL should be used to in streaming data pipelines? Aren’t people doing this already?

260 points by dwenzek 7y ago

A really interesting proposal to unify stream processing and relational querying and to extend SQL with a semantics over time-varying relations. In particular, the authors (from the Apache Beam/Flink/Calcite projects) stress on the need to: * smooth the frontier between streams and tables by using a single concept of time-varying relations; * make explicit the difference between the event and processing times, using monotonic watermarks to tie the two; * control when these continuously varying relations are materialized, avoiding unnecessary work and producing only timely outcomes. These ideas are not new per se, but there are here pushed further and nicely combined. However, I'm wondering if SQL is the right tool for the task. For instance, the listing 2 seems complex when compared to the query expressed in plain English. In particular, I disagree that "anyone who understands enough SQL to solve a problem in a non-streaming context still has the knowledge required to solve the problem in a streaming context as well. A must read paper by who is keen on stream processing, Spark, Flink, Beam, Kafka Stream ...

EdwardDiego 7y ago
> However, I'm wondering if SQL is the right tool for the task. In my anecdotal experience, yep, it's a data analysis language everyone in the industry knows - BAs, product managers, developers, and data scientists.
- dwenzek 7y ago
  Having a standard SQL extension definitely eases adoption and ability to migrate from one tool to another. But rather than adding and adding features to SQL; we maybe need a simpler way to extend/compose features. On that point, I see a contradiction in the future work section of the paper where it said that "Experience has also shown that pre-built solutions are never sufficient for all use cases; ultimately, users should be able to utilize the power of SQL to describe their own custom-windowing TVFs". But, how can a SQL user add its own custom operator?
michaelmior 7y ago
I agree that listing 2 is a little verbose, but once you understand the syntax (not much different from vanilla SQL), it's pretty powerful. I think writing queries this way will ultimately allow for more expressiveness than something more concise. That isn't to say that this particular SQL is necessarily the best, but I think it works well for the problem at hand.
- dwenzek 7y ago
  Sure, SQL is powerful and I agree that any alternative would also bring its own complexity. However, my concerns are more about encapsulation rather than expressiveness and conciseness. Even if a watermark is used the same way in different queries, I will have to use the same query clause again and again; as I already have to with the join conditions. Not a fundamental issue, but something which makes me more difficult to focus on the kernel of a query.
asavinov 7y ago
> However, I'm wondering if SQL is the right tool for the task. For instance, the listing 2 seems complex when compared to the query expressed in plain English For me Listing 2 is more complex and less intuitive than Listing 1 (CQL). Yet, it is a general problem when we try to adopt relational (and SQL) concepts for solving such kind of tasks (windowing, grouping, aggregation etc.) One solution is to switch to some kind of column algebra rather than relational algebra as described in [1] and [2] (it has also been applied to stream processing). [1] Joins vs. Links or Relational Join Considered Harmful: https://www.researchgate.net/publication/301764816_Joins_vs_Links_or_Relational_Join_Considered_Harmful https://www.researchgate.net/publication/301764816_Joins_vs_... [2] From Group-By to Accumulation: Data Aggregation Revisited: https://www.researchgate.net/publication/316551218_From_Group-by_to_Accumulation_Data_Aggregation_Revisited https://www.researchgate.net/publication/316551218_From_Grou...
- dwenzek 7y ago
  Thanks for the two links. I will have a look.
ddmd 7y ago
It seems that the main proposal is to make SQL watermark-aware. This additional "knowledge" will allow for producing groups and windows taking into account some late and out-of-order records.

truth_seeker 7y ago

Great write-up. SQL is the most underrated language. I am doing most of the stuff mentioned in that paper right now in my project with PipelineDB extension. PostgreSQL 11 + CitusDB extension (horizontal scaling and sharding) + cstore_fdw extnesion (columnar data storage) + file_fdw (to treat file as table) + PipelineDB extension (Streaming computations and MV) + TimescaleDB extension (timeseries data storage) Also, PG 12 release will have many optimizations for table partitioning, query planning and its parallel execution

buremba 7y ago
What's your data volume?
- truth_seeker 7y ago
  Around 7 TB and growing
  - FridgeSeal 7y ago
    That’s cool, what sort of query latency do you get? (For a variety of queries).
    - truth_seeker 7y ago
      We pay close attention to Data growth patterns and schema changes to keep around 70% of queries (which actually brings little over than 90% of business revenue) under 50 millisecond. Rest of 30% queries are either of low priority or backend jobs for which latency range of 200 ms to 1.5 seconds is viable for our use case.
      - binakot 7y ago
        And what hardware do you use for this stack and data volume?
      - FridgeSeal 7y ago

d__k 7y ago

> A time-varying relation is just what it says: a relation that changes over time ... Critically, the full suite of existing SQL operators remain valid on time-varying relations Any relation by definition is supposed to change in time. So why do we need to introduce a special kind of relations? The authors essentially confirm that there is no difference between classical relations and "time-varying" relations. Also, it is not clear how does it relate to EPL (in CEP) which is also based on SQL.

drunkpotato 7y ago
According to relational theory, relations are immutable sets of tuples. The way you “change” a relation is by assigning a new relation to the name, e.g.: R1 <- R1 union R2 Is the relational algebra way of “inserting” R2 into R1. Of course, most RDBMS’s (and the SQL family of languages) are only reasonably faithful implementations of relational theory under some practical constraints. But those differences re important, and I think the authors are proposing a new relational model (though I don’t think this is a totally new idea; I’m not a database researcher). One that incorporates time and history of a relation as first-class citizens of the theory, that allows sql to operate natively on both traditional relational-theoretic relations and streams of data. Pretty cool! Can anyone who knows more than me comment on how this relates to Kafka tables, which seems to me a similar concept?
solidsnack9000 7y ago
Relations in most systems don’t implicitly store the time a change occurred or the past versions of a row, nor do they have a model for building a “now table” from an “event table”. When people talk about time-varying tables or temporal tables, they are describing event sourcing in a SQL context.

SmokeGS 7y ago

Commenting to read again.

grzm 7y ago
You can use the favorite link under the submission title to effectively bookmark it. Your favorites are available in your profile.
- SmokeGS 7y ago
  thanks grzm
- scott_s 7y ago
  Or an upvote.

Nano2rad 7y ago

Not yet time to combine sql and nosql.

chrisjc 7y ago
What does this article/paper have to do with "NoSQL"?

iblaine 7y ago

Is the point of this paper that SQL should be used to in streaming data pipelines? Aren’t people doing this already?