4 ms·
I was reading BigQuery the Definitive Guide last week and I was surprised to learn that BigQuery is actually immutable at its core even when using DML statement
by scottfr 6y ago
I was reading BigQuery the Definitive Guide last week and I was surprised to learn that BigQuery is actually immutable at its core even when using DML statements.
This is surfaced in the query language and you can query the table as it existed in the past using the syntax:
SELECT * FROM
table_name
FOR SYSTEM TIME AS OF
TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -5 DAY)
The tables get rewritten periodically squashing this change history so you can only access states for the past week.
- jacques_chester 6y agoWhat you're seeing is an SQL:2011 thing -- system-versioned tables. It's from a more general concept of bitemporal databases. It's not quite the same. A cryptographic chain or Merkle tree can prove that the history has gaps which appeared since the original recording was made. A system-time table's safety rely on the guarantees of the implementing database.
- akudha 6y agoThis book is in the current humble bundle. How do you like the book?