Automatic deactivation of materialization if corresponding view has changed

Follow

0

Sebastian Drozdz

• August 07, 2020 13:07

Dear Data Virtuality,

It would be very useful if the materialization of a view is automatically deactivated when the corresponding view is changed. We keep running into cryptic error messages which are caused by differences in the virtual view and its materialized version.

Regards Sebastian

• 0

  Comment actions Permalink

  

  Niklas Schmidtmer

  • August 10, 2020 12:38

  Hi Sebastian,

   

  thanks for the feedback! The error message is definitely something we can improve. Regarding disabling the optimization, I'm wondering if this would always be the behaviour users expect. Disabling the optimization implies that queries go to the source system again, and we know about cases, where users want to prevent this strictly from happening, e.g. because of rate-limiting on the data source side or load concerns.

  Another way to approach this is to show immediate feedback to the user when issuing the ALTER VIEW command that the structure has changed and prompting the user if the materialization shall be refreshed. There would still be a time window between the structure was changed and until the reload of the materialization has finished, in which queries against the given view would fail because of the structure mismatch. Are you looking for a solution that 100% guarantees that queries always succeed, or would a better user experience, as described above, already be sufficient?

   

  Best

   

  Niklas

• 0

  Comment actions Permalink

  

  Peter Sykes

  • August 14, 2020 14:59

  What would be good is if it just fixed it.

  In my opinion that would be:

  * alter table on the materialized tables such that the current materializatons work - add new columns/drop removed columns. (or create table and then select into from existing active materialization, point to new materialization)
  * offer to return materialization now to populate new columns
  * reschedule corresponding job automatically according to existing materialization job schedule if required.

  Slightly related -  I don't think DV drops materializations when you just drop a view which is materialized.

• 0

  Comment actions Permalink

  

  Niklas Schmidtmer

  • August 19, 2020 11:39

  Hi Peter,

   

  thanks for your thoughts. Besides adding/removing columns, we also need to take the case into consideration that the data type of an existing column was changed. Setting the column to NULL could be dangerous in that case because of implications on other parts of the data model, but for adding/removing columns we can surely consider adjusting the table automatically.

  I'll give provide you with an update as soon as we had the chance to determine priorities internally. It might take a few weeks due to ongoing vacations.

   

  Best

   

  Niklas

• 0

  Comment actions Permalink

  

  Peter Sykes

  • August 19, 2020 13:28

  Thanks Niklas. You're right about the adjustments. Thanks for looking into this.

  The main issue is probably that the state gets broken after view change, so that would certainly help. (The current fix for this is to delete the optimization job to delete the temp tables, and then set it back up again the same as before including schedule). The nulls point is true for sure, but I guess you might have to trust the developer's response to a dialog here. It would also be nice to set that as a preference, I would always "rebuild, repopulate, reschedule materialization now" on view changes.

  Best
  Peter

Please sign in to leave a comment.