ROW FILTER clause

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 12.2 and above check marked yes Unity Catalog

Preview

This feature is in Public Preview.

Specifies a function which is applied as a filter whenever rows are fetched from the table.

You can add row filters when you:

Important

The row filter is applied as soon as the row is fetched from the data source.

For more information on how to use row filters see Filter sensitive table data with row filters and column masks.

Syntax 

ROW FILTER func_name ON ( [ column_name [, ...] ] ) } [...]

Parameters

  • func_name

    A scalar SQL UDF.

    The return type of the function must be BOOLEAN. If the function returns FALSE or NULL the row is filtered out.

  • column_name

    Specifies columns of the subject table to pass to func_name. Each column_name must be castable to the corresponding parameter of func_name. You must provide as many columns as are required by the signature of the function. Note that this feature supports passing zero input columns, in which case the SQL UDF must accept zero parameters and return a boolean result independent of any of the values of the input rows.

Examples

You can find more examples in Filter sensitive table data with row filters and column masks.

-- Create a table with a row filter column
> CREATE FUNCTION filter_emps(dept STRING) RETURN is_member(dept);
> CREATE TABLE employees(emp_name STRING, dept STRING) WITH ROW FILTER filter_emps ON (dept);
> INSERT INTO employees('Jones', 'Engineering'), ('Smith', 'Sales');

-- As a member of engineering
> SELECT * FROM employees;
  Jones Engineering

-- As a member of sales
> SELECT * FROM employees;
  Smith Sales