Define Filter Dialog Box

Defines the filter conditions.

You can specify row filters in two modes:

  • Simple mode: Use a simple template to define the filter, choosing a column, an inequality (for example, ">" or "between"), and a value (for example, a literal value or a column expression).
  • Script mode: Enter almost any set of filters by using SQL or Pig script.
Note: If you switch from Simple mode to Script mode, your filters are converted to script. However, your script is lost if you switch from Script mode to Simple mode.
Simple Mode

For Simple Mode, you can add multiple condition filters by clicking the add new filter link.

To define a filter, choose a column, a condition, and a value or expression.

AND/OR: You must also specify whether all filters must be met for each row (AND conditions), or just one of the filters (OR conditions).

Simple Mode Conditions
Condition Database Hadoop
= Yes Yes
<> Yes Yes
> Yes Yes
< Yes Yes
>= Yes Yes
<= Yes Yes
contains Yes Yes
between No Yes
is null Yes Yes
is not null Yes Yes
is blank No Yes
  • If your filter value is not numeric, you must add single quotes; for example, status = 'Active'.
  • If your filter value is numeric or an expression, you do not need to use quotes (for example, start_date = current_date, or customer_id > 37).
  • If your filter value is a column and the column name contains spaces or uppercase characters, you must add double quotes (for example, "Start Date" - 1)
Script Mode

In Script mode, filters are added as a "where" clause in the SQL or Pig scripting languages.

  • You do not need to add the WHERE keyword.
  • You can combine multiple filters with boolean expressions (AND, OR, and so on) and parentheses.
  • Any expression that fits within a WHERE clause in SQL (for DB) or Pig (for HD) can be used.
Note: For Hadoop data, if your filter value is a DateTime value, it can be compared to any other ISO-formatted DateTime Value. For details, see ISO DateTime Format.

Additionally, for Hadoop, any DateTime Pig Function can be applied to a DateTime field, such as GetMonth(datetime) or GetDay(datetime), and the result can then be used as Row Filter criteria.

You can find the full list of available DateTime-related Pig Functions at Apache Pig DateTime Functions.

Because some Pig functions have issues handling null values, using the Team Studio alternatives for certain functions is recommended. Just append "Alpine" to the method name; for example, "DaysBetween" becomes "DaysBetweenAlpine".

Pig DateTime Functions Alternative Pig DateTime Functions
AddDuration AddDurationAlpine
SubtractDuration SubtractDurationAlpine
MilliSecondsBetween MilliSecondsBetweenAlpine
SecondsBetween SecondsBetweenAlpine
MinutesBetween MinutesBetweenAlpine
HoursBetween HoursBetweenAlpine
DaysBetween DaysBetweenAlpine
WeeksBetween WeeksBetweenAlpine
MonthsBetween MonthsBetweenAlpine
YearsBetween YearsBetweenAlpine
Related reference