6 ms·

Materialize has tackled the hardest problem in data warehousing, materialized views, which has never really been solved, and built a solution on a completely ne

Materialize has tackled the hardest problem in data warehousing, materialized views, which has never really been solved, and built a solution on a completely new architecture. This solution is useful by itself, but I'm also watching eagerly how their road map [1] plays out, as they go back and build out features like persistence and start to look more like a full-fledged data warehouse, but one with the first correct implementation of materialized views. [1] https://materialize.com/blog-roadmap/ https://materialize.com/blog-roadmap/

adamnemecek 6y ago
How were previous implementations of materialized views deficient?
- georgewfraser 6y ago
  Joins were unavailable or subject to extreme limitations. Or just plain wrong!
- ahupp 6y ago
  Here's a nice writeup of Materialize: https://lucperkins.dev/blog/new-db-tech-1/#materialize https://lucperkins.dev/blog/new-db-tech-1/#materialize Not really mentioned here, but in standard postgres it might be quite expensive to update the view so you can only do it periodically. Materialize keeps that up-to-date continuously.
- ako 6y ago
  RDBMSes enable you to create materialized views only for data in the database. Materialize enables you to do this for any streaming data source in your organization, with the ease of writing SQL. This enables you to simply write a SQL statement joining data from Salesforce + SAP + Siebel as soon as the data changes, and store the results as a near real-time up to date database table. It does depend on a lot of underlying plumbing: streaming platform (e.g. kafka), and streaming data sources (e.g., kafka connect + debezium).
dataplayer 6y ago
What exactly are "materialized views"?
- bluejekyll 6y ago
  “In computing, a materialized view is a database object that contains the results of a query. For example, it may be a local copy of data located remotely, or may be a subset of the rows and/or columns of a table or join result, or may be a summary using an aggregate function.” https://en.m.wikipedia.org/wiki/Materialized_view https://en.m.wikipedia.org/wiki/Materialized_view
- findjashua 6y ago
  updated results of a query - eg if you do some aggregation or filtering on a table, or join two tables, or anything of the sort - materialized view will give you the updated results of the query in a separate table
- jacques_chester 6y ago
  Suppose you have normalized your data schema, up to at least 3NF, perhaps even further up to 4NF, 5NF or (as Codd intended) BCNF. Great! You are now largely liberated from introducing many kinds of anomaly at insertion time. And you'll often only need to write once for each datum (modulo implementation details like write amplification), because a normalised schema has "a place for everything and everything in its place". Now comes time to query the data. You write some joins, and all is well. But a few things start to happen. One is that writing joins over and over becomes laborious. What you'd really like is some denormalised intermediary views, which transform the fully-normalised base schema into something that's more convenient to query. You can also use this to create an isolation layer between the base schema and any consumers, which will make future schema changes easier and possibly improve security. The logical endpoint of doing so is the Data Warehouse (particularly in the Kimball/star schema/dimensional modelling style). You project your normalised data, which you have high confidence in, into a completely different shape that is optimised for fast summarisation and exploration. You use this as a read-only database, because it massively duplicates a lot of information that could otherwise have been derived via query (for example, instead of a single "date" field, you have fields for day of week, day of month, day of year, week of year, whether it's a holiday ... I've built tables which include columns like "days until major conference X" and "days since last quarterly release"). Now we reach the first problem. It's too slow! Projecting that data from the normalised schema requires a lot of storage and compute. You realise after some scratching that your goal all along was to pay that cost upfront so that you can reap the benefits at query time. What you want is a view that has the physical characteristics of a table. Meaning you want to write out the results of the query, but still treat it like a view. You've "materialized" the view. Now the second problem. Who, or what, does that projection? Right now that role is filled by ETL, "Extract, Transform and Load". Extract from the normalised system, transform it into the denormalised version, then load that into a data warehouse. Most places do this on a regular cadence, such as nightly, because it just takes buckets and buckets of work to regenerate the output every time. Now enters Materialize, who have a secret weapon: timely dataflow. The basic outcome is that instead of re-running an entire view query to regenerate the materialized view, they can, from a given datum, determine exactly what will change in the materialized view and only update that. That makes such views potentially thousands of times cheaper. You could even run the normalised schema and the denormalised projections on the same physical set of data -- no need for the overhead and complexity of ETL, no need to run two database systems, no need to wait (without the added complexity of a full streaming platform).
deleted 6y ago
[deleted]
jameslk 6y ago
Isn't this pretty similar to what Dremio does?
- benesch 6y ago
  Dremio is a batch processor, not a stream processor. The fundamental difference is that a batch processor will need to recompute a query from scratch whenever the input data changes, while a stream processor can incrementally update the existing query result based on the change to the input. This can make a huge difference when making small changes to large datasets. Materialize can incrementally compute small changes to very complicated queries in just a few milliseconds, while with batch processors you're looking at latency in the hundreds of milliseconds, seconds, or minutes, depending on the size of the data. Another way of looking at it is that in batch processors, latency scales with the size of the total data, while in stream processors, latency scales with the size of the updates to the data.
  - jameslk 6y ago
    I see, thank you for the explanation!
btown 6y ago
For a primer on materialized views, and one of the key rationales for Materialize's existence, there's no better presentation than Martin Kleppman's "Turning the Database Inside-Out" (2015). (At my company it's required viewing for engineers across our stack, because every data structure is a materialized view no matter where on frontend or backend that data structure lives.) https://www.confluent.io/blog/turning-the-database-inside-out-with-apache-samza/ https://www.confluent.io/blog/turning-the-database-inside-ou... Confluent is building an incredible business helping companies to build these types of systems on top of Kafka, Samza, and architectural principles originally developed at LinkedIn, but more along the lines of "if you'd like this query to be answered, or this recommender system to be deployed for every user, we can reliably code a data pipeline to do so at LinkedIn scale" than "you can run this query right away against our OLAP warehouse without knowing about distributed systems." (If it's more nuanced than this please correct me!) On the other hand, Materialize could allow businesses to realize this architecture, with its vast benefits to millisecond-scale data freshness and analytical flexibility, simply by writing SQL queries as if it was a traditional system. As its capabilities expand beyond parity with SQL (though I agree that's absolutely the best place for them to start and optimize), there are tremendous wins here that could power the next generation of real-time systems. EDIT: some clarifications and additional examples
- Liron 6y ago
  I also wrote a primer for why the world needs Materialize [1]. It had a big discussion on HN [2], and Materialize's cofounder said it was part of his motivation [3]. [1] https://medium.com/@lironshapira/data-denormalization-is-broken-7b697352f405 https://medium.com/@lironshapira/data-denormalization-is-bro... [2] https://news.ycombinator.com/item?id=12613586 https://news.ycombinator.com/item?id=12613586 [3] https://twitter.com/narayanarjun/status/1241450203095465986 https://twitter.com/narayanarjun/status/1241450203095465986
  - quodlibetor 6y ago
    Ha! Your blog post was one of the reasons that I trusted in the future of Materialize enough to decide to work here! I agree, that is exactly the problem that I, in particular, think we are solving.
random314 6y ago
How is this different from Apache flink real time SQL support? https://flink.apache.org/2020/07/28/flink-sql-demo-building-e2e-streaming-application.html https://flink.apache.org/2020/07/28/flink-sql-demo-building-...
- deleted 6y ago
  [deleted]
- frankmcsherry 6y ago
  I think the main distinction is around "interactivity" and how long it takes from typing a query to getting results out. Once you stand up a Flink dataflow, it should move along a brisk clip. But standing up a new dataflow is relatively heavy-weight for them; typically you have to re-flow all of the data that feeds the query. Materialize has a different architecture that allows more state-sharing between operators, and allows many queries to spin up in milliseconds. Primarily, this is when your query depends on existing relational data in pre-indexed form (e.g. joining by primary and foreign keys). You can read a bit more in an overview blog post [0] and in more detail in a VLDB paper [1]. I'm sure there are a number of other quirks distinguishing Flink and Materialize, probably several in their favor, but this is the high-order bit for me. [0]: https://materialize.com/materialize-under-the-hood/ https://materialize.com/materialize-under-the-hood/ [1]: http://www.vldb.org/pvldb/vol13/p1793-mcsherry.pdf http://www.vldb.org/pvldb/vol13/p1793-mcsherry.pdf

adamnemecek 6y ago

How were previous implementations of materialized views deficient?

georgewfraser 6y ago
Joins were unavailable or subject to extreme limitations. Or just plain wrong!
ahupp 6y ago
Here's a nice writeup of Materialize: https://lucperkins.dev/blog/new-db-tech-1/#materialize https://lucperkins.dev/blog/new-db-tech-1/#materialize Not really mentioned here, but in standard postgres it might be quite expensive to update the view so you can only do it periodically. Materialize keeps that up-to-date continuously.
ako 6y ago
RDBMSes enable you to create materialized views only for data in the database. Materialize enables you to do this for any streaming data source in your organization, with the ease of writing SQL. This enables you to simply write a SQL statement joining data from Salesforce + SAP + Siebel as soon as the data changes, and store the results as a near real-time up to date database table. It does depend on a lot of underlying plumbing: streaming platform (e.g. kafka), and streaming data sources (e.g., kafka connect + debezium).

dataplayer 6y ago

What exactly are "materialized views"?

bluejekyll 6y ago
“In computing, a materialized view is a database object that contains the results of a query. For example, it may be a local copy of data located remotely, or may be a subset of the rows and/or columns of a table or join result, or may be a summary using an aggregate function.” https://en.m.wikipedia.org/wiki/Materialized_view https://en.m.wikipedia.org/wiki/Materialized_view
findjashua 6y ago
updated results of a query - eg if you do some aggregation or filtering on a table, or join two tables, or anything of the sort - materialized view will give you the updated results of the query in a separate table
jacques_chester 6y ago
Suppose you have normalized your data schema, up to at least 3NF, perhaps even further up to 4NF, 5NF or (as Codd intended) BCNF. Great! You are now largely liberated from introducing many kinds of anomaly at insertion time. And you'll often only need to write once for each datum (modulo implementation details like write amplification), because a normalised schema has "a place for everything and everything in its place". Now comes time to query the data. You write some joins, and all is well. But a few things start to happen. One is that writing joins over and over becomes laborious. What you'd really like is some denormalised intermediary views, which transform the fully-normalised base schema into something that's more convenient to query. You can also use this to create an isolation layer between the base schema and any consumers, which will make future schema changes easier and possibly improve security. The logical endpoint of doing so is the Data Warehouse (particularly in the Kimball/star schema/dimensional modelling style). You project your normalised data, which you have high confidence in, into a completely different shape that is optimised for fast summarisation and exploration. You use this as a read-only database, because it massively duplicates a lot of information that could otherwise have been derived via query (for example, instead of a single "date" field, you have fields for day of week, day of month, day of year, week of year, whether it's a holiday ... I've built tables which include columns like "days until major conference X" and "days since last quarterly release"). Now we reach the first problem. It's too slow! Projecting that data from the normalised schema requires a lot of storage and compute. You realise after some scratching that your goal all along was to pay that cost upfront so that you can reap the benefits at query time. What you want is a view that has the physical characteristics of a table. Meaning you want to write out the results of the query, but still treat it like a view. You've "materialized" the view. Now the second problem. Who, or what, does that projection? Right now that role is filled by ETL, "Extract, Transform and Load". Extract from the normalised system, transform it into the denormalised version, then load that into a data warehouse. Most places do this on a regular cadence, such as nightly, because it just takes buckets and buckets of work to regenerate the output every time. Now enters Materialize, who have a secret weapon: timely dataflow. The basic outcome is that instead of re-running an entire view query to regenerate the materialized view, they can, from a given datum, determine exactly what will change in the materialized view and only update that. That makes such views potentially thousands of times cheaper. You could even run the normalised schema and the denormalised projections on the same physical set of data -- no need for the overhead and complexity of ETL, no need to run two database systems, no need to wait (without the added complexity of a full streaming platform).

deleted 6y ago

[deleted]

jameslk 6y ago

Isn't this pretty similar to what Dremio does?

benesch 6y ago
Dremio is a batch processor, not a stream processor. The fundamental difference is that a batch processor will need to recompute a query from scratch whenever the input data changes, while a stream processor can incrementally update the existing query result based on the change to the input. This can make a huge difference when making small changes to large datasets. Materialize can incrementally compute small changes to very complicated queries in just a few milliseconds, while with batch processors you're looking at latency in the hundreds of milliseconds, seconds, or minutes, depending on the size of the data. Another way of looking at it is that in batch processors, latency scales with the size of the total data, while in stream processors, latency scales with the size of the updates to the data.
- jameslk 6y ago
  I see, thank you for the explanation!

btown 6y ago

For a primer on materialized views, and one of the key rationales for Materialize's existence, there's no better presentation than Martin Kleppman's "Turning the Database Inside-Out" (2015). (At my company it's required viewing for engineers across our stack, because every data structure is a materialized view no matter where on frontend or backend that data structure lives.) https://www.confluent.io/blog/turning-the-database-inside-out-with-apache-samza/ https://www.confluent.io/blog/turning-the-database-inside-ou... Confluent is building an incredible business helping companies to build these types of systems on top of Kafka, Samza, and architectural principles originally developed at LinkedIn, but more along the lines of "if you'd like this query to be answered, or this recommender system to be deployed for every user, we can reliably code a data pipeline to do so at LinkedIn scale" than "you can run this query right away against our OLAP warehouse without knowing about distributed systems." (If it's more nuanced than this please correct me!) On the other hand, Materialize could allow businesses to realize this architecture, with its vast benefits to millisecond-scale data freshness and analytical flexibility, simply by writing SQL queries as if it was a traditional system. As its capabilities expand beyond parity with SQL (though I agree that's absolutely the best place for them to start and optimize), there are tremendous wins here that could power the next generation of real-time systems. EDIT: some clarifications and additional examples

Liron 6y ago
I also wrote a primer for why the world needs Materialize [1]. It had a big discussion on HN [2], and Materialize's cofounder said it was part of his motivation [3]. [1] https://medium.com/@lironshapira/data-denormalization-is-broken-7b697352f405 https://medium.com/@lironshapira/data-denormalization-is-bro... [2] https://news.ycombinator.com/item?id=12613586 https://news.ycombinator.com/item?id=12613586 [3] https://twitter.com/narayanarjun/status/1241450203095465986 https://twitter.com/narayanarjun/status/1241450203095465986
- quodlibetor 6y ago
  Ha! Your blog post was one of the reasons that I trusted in the future of Materialize enough to decide to work here! I agree, that is exactly the problem that I, in particular, think we are solving.

random314 6y ago

How is this different from Apache flink real time SQL support? https://flink.apache.org/2020/07/28/flink-sql-demo-building-e2e-streaming-application.html https://flink.apache.org/2020/07/28/flink-sql-demo-building-...

deleted 6y ago
[deleted]
frankmcsherry 6y ago
I think the main distinction is around "interactivity" and how long it takes from typing a query to getting results out. Once you stand up a Flink dataflow, it should move along a brisk clip. But standing up a new dataflow is relatively heavy-weight for them; typically you have to re-flow all of the data that feeds the query. Materialize has a different architecture that allows more state-sharing between operators, and allows many queries to spin up in milliseconds. Primarily, this is when your query depends on existing relational data in pre-indexed form (e.g. joining by primary and foreign keys). You can read a bit more in an overview blog post [0] and in more detail in a VLDB paper [1]. I'm sure there are a number of other quirks distinguishing Flink and Materialize, probably several in their favor, but this is the high-order bit for me. [0]: https://materialize.com/materialize-under-the-hood/ https://materialize.com/materialize-under-the-hood/ [1]: http://www.vldb.org/pvldb/vol13/p1793-mcsherry.pdf http://www.vldb.org/pvldb/vol13/p1793-mcsherry.pdf