Data filtering or parameter settings - DataWorks - Alibaba Cloud Documentation Center

In data quality monitoring, you typically monitor only the data that changed, instead of every row in a table. This topic describes how to configure data filtering for each monitoring run.

Data filtering example

In most cases, changed data is filtered by a time field. For example, MaxCompute uses time partitions, whereas a MySQL business transaction table can be filtered by the creation time of each transaction.

Set a filter in monitoring and rules to filter data. Example:

datasets:
  - type: Table
    tables:
      - tb_d_spec_demo
    filter: "dt = '$[yyyymmdd]' AND hh = '$[hh24-1/24]'"
    dataSource:
      name: odps_first
      envType: Dev
rules:
  - assertion: "row_count > 0"
    # A rule-level filter works together with the filter in Scan.dataset
    # At runtime, the filter is (dt = '$[yyyymmdd-1]' AND hh = '$[hh24-1/24]') AND (id IS NOT NULL)
    filter: "id IS NOT NULL"
computeResource:
  id: 2001

Filter syntax

Basic syntax

filter syntax generally follows SQL-92 WHERE conditions, except for the match and overlaps predicates. The syntax is as follows:

<search condition> ::=
    <boolean term> | <search condition> OR <boolean term>

<boolean term> ::=
    <boolean factor> | <boolean term> AND <boolean factor>

<boolean factor> ::=
    [ NOT ] <boolean test>

<boolean test> ::=
    <predicate> | ( <search condition> )

<predicate> ::=
    <comparison predicate> | <between predicate> |
    <in predicate> | <like predicate> | <null predicate> |
    <quantified comparison predicate> | <exists predicate>

<comparison predicate> ::=
    <expression> <comparison_operator> <expression>

<between predicate> ::=
    <expression> [NOT] BETWEEN <lower_bound> AND <upper_bound>

<in predicate> ::=
    <expression> [NOT] IN (value_list | subquery)

<like predicate> ::=
    <expression> [NOT] LIKE pattern [ESCAPE escape_character]

<null predicate> ::=
    <expression> IS [NOT] NULL

<quantified comparison predicate> ::=
    <expression> <comparison_operator> { ALL | ANY | SOME } (subquery)

<exists predicate> ::=
    EXISTS (subquery)

Configuration examples

comparison predicate
```
salary > 50000
```
between predicate
```
price BETWEEN 10 AND 50
```
in predicate
```
department IN ('Sales', 'Marketing')
```
like predicate
```
name LIKE 'Jo%'
```
null predicate
```
department IS NOT NULL
```

quantified comparison predicate

salary > ALL (SELECT salary FROM employees WHERE department = 'Sales')

exists predicate

EXISTS (SELECT 1 FROM customers cu WHERE cu.country_id = c.country_id)

complex search condition

-- Use the AND logical operator to connect two predicates
salary > ALL (SELECT salary FROM employees WHERE department = 'Sales') AND EXISTS (SELECT 1 FROM customers cu WHERE cu.country_id = c.country_id)
-- Use the OR logical operator to connect predicates
department IS NOT NULL OR name LIKE 'Jo%'

Define a filter with a native query

If the underlying data store's query cannot be expressed in SQL-92 SQL, such as a query using RLIKE, geospatial search, or full-text search, start the filter value with the query: keyword and write the native query directly.

For example, to filter MaxCompute table data with RLIKE, configure the filter as follows:

datasets:
  - type: Table
    indices:
      - ods_d_customers
    dataSource:
      name: odps_first
      envType: Dev
rules:
  - assertion: "row_count > 0"
    filter: "query: email_address RLIKE '^info@'"

Use time variables in a filter

When you use time partitions, a quality monitoring run scans the current day's partition or the T-1 partition each day. To obtain the corresponding date automatically for each run, use a scheduling system time-offset expression.

For example, if the filter contains a scheduling time expression such as $[yyyymmdd-1], the data quality system automatically applies the offset based on the scheduled time of the scheduling instance and substitutes the resulting value.

Note

If data quality monitoring is not triggered by a scheduling instance, specify CreateDataQualityScanRun's trigger parameter triggerTime. The system applies the offset relative to triggerTime,