All Products
Search
Document Center

Configure conditions to filter fields in ApsaraDB RDS and PolarDB data sources

Last Updated: Sep 09, 2021

Introduction

  • Supported field types: TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, REAL, DOUBLE, NUMERIC, DECIMAL, TIME, TIMESTAMP

  • Supported comparison operators: <, >, <=, >=, =, !=, <>. Note that =, !=, and <> can be used to filter fields of the numeric and character types.

  • Supported values: NULL and non-NULL

Example scenarios

1.Filter fields of the character type: field_string='a' or field_string!='b' Note that you can use single quotation marks (') or double quotation marks ("), or do not use quotation marks. The following examples show the conditions that can be used to filter fields of the character type with an empty value or a null value:

field = ''
field != ''
field <> ''
field = 'null'
field = 'NULL'
field != 'null'
field != 'NULL'
field <> 'null'
field <> 'NULL'

2.Filter fields with a null value: The following examples show the conditions that can be used to filter fields of the numeric type or the character type with a null value.

# The following filter conditions are valid:
field = null 
field = NULL
field != null
field != NULL
field <> null
field <> NULL

3. Filter fields of the DATE or DATETIME type: createtime>'2021-02-05 00:00:00'

4.Filter fields based on multiple conditions: status=1,literal_id='abcd',createtime>'2021-02-05 00:00:00'

Usage notes

  • You can filter only the fields from ApsaraDB RDS or PolarDB data sources.

  • When you configure conditions to filter the data of data sources, you must use table fields in source databases instead of fields in the schemas of OpenSearch applications.

  • After a filter condition takes effect, only data entries that meet the condition are pulled. The filter condition applies to full and incremental data. For example, after the filter condition status=1 is configured, a document status=1 in ApsaraDB RDS is changed to status=2. In this case, this document will be deleted in OpenSearch.

  • Only the AND logical operation is supported. Commas (,) are used to implement the AND logical operation. The OR logical operation is not supported.

## The WHERE clause in an SQL statement:
where status=1 and type=2

# Configure filtering of data sources in OpenSearch.
where status=1 and type=2 # Invalid.
status=1 and type=2 # Invalid.
status=1,type=2 # Valid.
  • If you need to filter fields with a null value or a non-null value, do not use is null or is not null. For more information about the valid syntax, see the conditions for filtering fields with a null value in the "Example scenarios" section of this topic.

  • If you need to filter a field of the DATE or DATETIME type, such as the field named createtime, in a database table, the time in the filter condition must be in the same format as the time in createtime>'2018-03-01 00:00:00'.

  • Functions and expressions, such as in(type,1,2,3) and length(title)>10, are not supported.

  • like and not like, such as like '%aaa' and not like '%bbb', are not supported.

  • Identical equations, such as 1=1, are not supported.

  • If primary and secondary tables are configured for a data source, we recommend that you filter data in both the primary and secondary tables. This prevents data in the secondary table from occupying storage space.