Materialized views


Is there any way to force the MapD database to store a created View, in its entirety? AKA a materialized view?

I want to use MapD to perform some expensive join operations, creating Views out of Views, without losing dashboard speed at the end. Thanks!



We have no support for materialized views, managing CREATE TABLE AS is what is available currently but i assume you want to avoid the management overhead.

Before we run too far down the materialized view conclusion we should at least explore the slow join issues :slight_smile:

Could you share the nature of your schema and the joins you are trying to execute? We will need some kind of idea of cardinality on the join columns etc to be able to offer help.



Since you understand this better, here is an overview of my problem:

Joining all my datasets in Spark and then loading a big dataset into MapD was slow/expensive, so I was thinking I could perform the joins on MapD instead. I was hoping to load my individual datasets (venues, events, coordinates) and then perform the joins on MapD, creating a (venues_events_coordinates) table.

I was doing this using CREATE VIEW AS, not knowing that it doesn’t save the actual data, it saves the formula. CREATE TABLE AS seems like a solution, but i have a question: when I upload new venues/events/coordinates data, will it update the resulting venues_events_coordinates table automatically?



CREATE TABLE AS will require you to manage and recreate tables there is nothing automatic and no lineage is kept for when a ‘parent’ table is changed.

You did not address my question about why a straight view doesn’t work? What is the nature of the joins you are trying to produce? Did you try the joins and find them too slow?



I did do Views but I actually have like 10 tables to join so it creates Views built upon Views built upon Views which ultimately results in a slow dashboard