How to incrementally backup system.information_schema.table_privileges (no streaming, no unique keys

[Danish11052000]

I'm trying to incrementally backup system.information_schema.table_privileges but facing challenges:

1. No streaming support: Is streaming supported: False

2. No unique columns for MERGE: All columns contain common values, no natural key combination

3. No timestamp columns availability: Can't track changes incrementally

Table schema:

GRANTOR (STRING, NOT NULL) - Principal that granted privilege
GRANTEE (STRING, NOT NULL) - Principal receiving privilege
TABLE_CATALOG (STRING, NOT NULL) - Catalog name
TABLE_SCHEMA (STRING, NOT NULL) - Schema name
TABLE_NAME (STRING, NOT NULL) - Table name
PRIVILEGE_TYPE (STRING, NOT NULL) - Privilege type
IS_GRANTABLE (STRING, NOT NULL) - Always 'NO'
INHERITED_FROM (STRING, NOT NULL) - Ancestor relation

Problems:

• Spark streaming fails: [UNSUPPORTED_FEATURE.TABLE_OPERATION]

• Batch MERGE fails: No reliable unique key columns

• No last_altered/created_time for incremental logic
  
  

Any solution would be really helpful!

[MoJaMa]

information_schema is not a Delta Table, which is why you can't stream from it. They are basically views on top of the information coming straight from the control plane database.

Also your query is actually going to be quite slow/expensive (you probably have noted that a select * type query on it is slow since it is a union of all the views from each individual catalog's information_schema).

What's the goal here with trying to maintain a "daily" snapshot?

[Danish11052000]

Since we need the previous permissions of a table that was dropped, I thought taking a backup of the system.information_schema.table_privileges table would be helpful. any idea if there is an alternative way to retrieve this information?