[firestore-bigquery-export] switch to materialized views

[meyerovb]

trafficstars

Since this isnt using materialized views I need to scheduled copy its views into physical tables in order to use bigquery bi engine with it in google data studio. if it was using mat views data studio could use accelerated access

[dackers86]

Thanks @meyerovb

I have updated this for discussion.

[i14h]

@meyerovb thanks for the suggestion. is there anything stopping you from generating the materialized views yourself based on the gen schema scripts?

[jasonberryman]

@i14h I have tried to create a Materialized View from one of the current views. I had to reference the raw table (Materialized Views only work with native tables), rather than the <table>_raw_latest view and received the error Materialized views do not support analytic functions or WITH OFFSET.

[dackers86]

HI @jasonberryman

Did you manage resolve a Materialised view for your project?

Based on the current data, this appears tricky as Materialised Views appear to be more limited than standard views.

I'll move this issue back to accepted to investigate whether a script os possible for this.

[cimox]

Hey guys, do you have any ideas how to create materialized view for <collection>_raw_latest views? latest views are using PARTITION BY and ORDER BY to fetch latest timestamp, flag if document is deleted etc. But, ORDER operation is not allowed in the BQ materialized views.

Any tips and tricks how to overcome this issue? I am thinking about writing Firebase -> BQ connector which will use DML to update existing rows. What do you think?

[jshandorov1]

+1 vote for matterialized views

[davestimpert]

Hey guys, do you have any ideas how to create materialized view for <collection>_raw_latest views? latest views are using PARTITION BY and ORDER BY to fetch latest timestamp, flag if document is deleted etc. But, ORDER operation is not allowed in the BQ materialized views.

Any tips and tricks how to overcome this issue? I am thinking about writing Firebase -> BQ connector which will use DML to update existing rows. What do you think?

@cimox I have the same question. Have you found a way to achieve this?

[cimox]

@davestimpert I did not find a solution to this issue at the time. However, I created a custom set of materialized views to improve performance, which worked nicely for my use case. Unfortunately, I did not need to work with these Firebase export datasets since then, so no more progress on my end.

[RajvirBains]

@davestimpert I am not maintaining that system anymore so writing it based on my memory from few years back. I think we used the raw_changlog table to create a materialized view that flattened all the columns. We couldn't do any filtering in this view, which is sad. Then we created a latest view based on it and used same logic as what Google gives in raw_latest. I had attended some Google sessions at that time and I had heard about some enhancements being planned by Google for materialized view that will make it easier for creating the latest_view from raw_changelog and have it materialized in one step. If you are a paid customer, it might not hurt to create a ticket for the Google BQ team to confirm the possibilities.

[Aetherall]

I dont know what I'm doing but maybe this query would do the trick ? It seems compatible with materialized views, and how I understand it, MAX_BY will run for each GROUP

SELECT
  document_id,
  document_name,
  MAX(timestamp) AS latest_timestamp,
  MAX_BY(operation, timestamp) AS latest_operation,
  MAX_BY(data, timestamp) AS latest_data
FROM
  `xxx.xxx.raw_changelog`
GROUP BY
  document_id, document_name

It wont exclude the deleted documents, but materialized views seems to support WITH statements

[cabljac]

https://github.com/firebase/extensions/pull/2258 - change tracker

https://github.com/firebase/extensions/pull/2262 - extension