Skip to main content
Last updated on

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

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 Row filters and column masks.

Required privileges​

To assign a function that adds a row filter to a table, you must have the EXECUTE privilege on the function, USE SCHEMA on the schema, and USE CATALOG on the parent catalog.

If you are adding a row filter when you create a new table, you must have the CREATE TABLE privilege on the schema.

If you are adding a row filter to an existing table, you must be the table owner or have the MANAGE privilege on the table.

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 relation 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. This feature supports passing zero input columns, in which case the SQL UDF must accept zero parameters and return a Boolean result independent of the values of the input rows.

    important

    If a column's data type doesn't exactly match the corresponding function parameter type, the column value is implicitly cast. With ANSI_MODE disabled, values that can't be cast are silently converted to NULL, which can produce unexpected filter results. UDF parameter types must match the data types of the columns passed to them. See Data type mismatch behavior.

  • 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 Row filters and column masks.

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

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

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

Related articles​

On this page