CODEWITHSUNDEEP
sqloracle-databasegoogle-bigquery
Balajee Venkatesh
Balajee Venkatesh

Reputation: 1099

Materialized Views support to Big query or any similar functionality

I want a particular query to get triggered and update a table every time any change in any particular table occurs. Example: Table 'A' gets updated with some records. Now, I want to take some specified columns and insert some records from Table 'A' to Table 'B' based on a query. Basically this insertion should be triggered automatically whenever Table 'A' goes through any kind of change. In Oracle, There is a concept of Materialized Views which does nothing but running a query every time a specified table data gets changed. It keeps the physical storage of data resulted by query written as View. Does Big query supports such functionality? if not, is there any workaround to achieve the same?

Upvotes: 3

Views: 653

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172964

The closest to what you described can be done as below and in Legacy SQL only (because Standard SQL does not support Range Decorators):

So, the idea is -

1 - Each N minutes you query last N minutes (for sake of example assume N=10 min)

SELECT * FROM [your_project:your_dataset.tableA@-600000-]

In this step you retrieve all rows changed in last 10 min

2 - Next, you apply logic of transforming (if any) and loading into your_project:your_dataset.tableB

3 - Done

Of course you should implement above steps in language of your preference and set it run as a cron job for example

Upvotes: 4

Related Questions