ROW FILTER clause

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

Specifies a function that 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 using row filters and column masks.

Syntax 

ROW FILTER func_name ON ( [ column_name | constant_literal [, ...] ] ) [...]

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.

  • constant_literal

    Specifies a constant parameter with the type matching a function parameter. The following types are supported: STRING, numeric (INTEGER, FLOAT, DOUBLE, DECIMAL …), BOOLEAN, INTERVAL, NULL.

Examples

You can find more examples in Filter sensitive table data using 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