Best way to run a job/trigger on alter table SET ROW FILTER or SET MASK

ravi_kumar — Sun, 14 Jan 2024 00:02:53 GMT

I want to run SQL statements whenever we add ROW FILTER or MASK using ALTER TABLE command.

https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-ddl-alter-table.html

https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-ddl-alter-table-manage-column.html#alter-column

I am new to databricks and I am trying to do it through a trigger job which can be created already and run automatically on the ALTER TABLE but I could not find a way to set a trigger on the alter table.

Can someone please help me what could be the best way to do this in databricks?

Re: Best way to run a job/trigger on alter table SET ROW FILTER or SET MASK

Walter_C — Sat, 20 Jan 2024 18:00:13 GMT

The best way to run a jobon ALTER TABLE SET ROW FILTER or SET MASK.

1. **Create the Function**
You need to start by creating a function that will be used to filter the rows or mask the columns. The function should return a boolean value that determines whether a row or column should be included or excluded.

sql
CREATE FUNCTION <function_name> (<parameter_name> <parameter_type>, ..) RETURN {filter clause whose output must be a boolean};

2. **Apply the Row Filter or Mask to a Table**
After creating the function, you apply it to a table using the ALTER TABLE statement.

sql
ALTER TABLE <table_name> SET ROW FILTER <function_name> ON (<column_name>, ..);

sql
ALTER TABLE <table_name> SET MASK <function_name> ON (<column_name>, ..);

3. **Run the Job/Trigger**
Now that the filter or mask is set, you can run your job or trigger. Any queries that are run on the table will now be subject to the filter or mask that you have applied.

Please note that row filters or column masks should not be added to any table that you are accessing from single user clusters. This is commonly done in the context of Databricks Jobs.

topic Best way to run a job/trigger on alter table SET ROW FILTER or SET MASK in Data Governance

Best way to run a job/trigger on alter table SET ROW FILTER or SET MASK

Re: Best way to run a job/trigger on alter table SET ROW FILTER or SET MASK