cancel
Showing results for 
Search instead for 
Did you mean: 
Data Governance
Join discussions on data governance practices, compliance, and security within the Databricks Community. Exchange strategies and insights to ensure data integrity and regulatory compliance.
cancel
Showing results for 
Search instead for 
Did you mean: 

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

ravi_kumar
New Contributor

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...

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?

 

 

1 REPLY 1

Walter_C
Honored Contributor
Honored Contributor

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>, ..);

or

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.

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!