Filter

Filtering is a way to exclude data you do not want to see or use in your analysis. Because less data gets loaded, filtering reduces the processing times for queries so you can work faster.

Filtering is accomplished using the where method and other related methods described below

where()

The where method returns all the rows from the source table that meet the conditions in the argument:

.where("USym = AAPL")

For example:

t = db.t("LearnDeephaven" , "StockQuotes")\
    .where("Date = `2017-08-25`")\
    .where("USym = `AAPL`")

You could also write this like:

t = db.t("LearnDeephaven" , "StockQuotes")\
    .where("Date = `2017-08-25`", "USym = `AAPL`")

Filters are applied from left to right. Therefore, the order in which they are passed to the function can have a substantial impact on the execution time.

For example, if we moved the "USym" filter first, Deephaven would evaluate that filter for all partitions of the table before reducing the data to a single date.

t = db.t("LearnDeephaven" , "StockQuotes")\
.where("USym = `AAPL`", "Date = `2017-08-25`")

So far, the examples used for describing the where method have involved a table with only a couple thousand rows of data. Filtering on such a small table is a trivial task for Deephaven. However, when your table size grows to thousands or millions of rows (or more), you will want to ensure you are filtering the data in the most efficient method to reduce compute expense and execution time.

Partitioning and Grouping Columns

When you partition a hard drive, you are dividing the entire hard drive into multiple, logical, independent volumes. In a high-end data system like Deephaven, data is also divided into multiple, logical and independent partitions, which improves the speed, performance and efficiency of the data analysis.

The most common partitionioning column is Date. For example, the "StockTrades" table in the "LearnDeephaven" namespace contains data from August 21, 2017 - August 25, 2017. Instead of having one massive file that contains every record for that business week, the data is stored in 5 smaller partitions. In most cases, you will want to analyze only a specific range of time. In Deephaven, you can do so without analyzing or loading the entire dataset stored in that table.

The Deephaven query language is designed to make filtering on partitioning columns highly efficient. Therefore, when possible, one should filter first on partitioning columns.

Grouping columns determine how table data is organized on disk such that each unique value, and its related row data, are located in contiguous blocks of rows. These have persistent indexes that the Deephaven query engine can leverage to vastly improve the efficiency of match filters.

If your analyses require filtering on the data contained in both partitioning columns and grouping columns, the most efficient method would be to first filter on partitioning columns, and then separately filter on grouping columns and/or any remaining columns as needed. To determine the partitioning and grouping columns in your dataset, you will need to review the metadata for your applicable table(s).

One way this can be accomplished is by using the getMeta method, which will return a table with column details:

tMeta=db.t("LearnDeephaven", "StockTrades").getMeta()

Match Filters

Because match filters benefit from special handling in Deephaven, you should use them whenever possible and before any conditional filters. Match filters often enable Deephaven to perform optimizations that would not be possible with a potentially more expressive conditional filter. For example, grouping information on historical data sources can be used with match filters, but not conditional filters.

There are five kinds of match filters in Deephaven:

• = (the equal sign)
• in
• not in
• icase in
• icase not in

= (the equal sign)

This method returns rows that have a matching value in the specified column.

.where("columnName=value")

Example

t = db.t("LearnDeephaven" , "StockQuotes")\
    .where("Date = `2017-08-25`", "USym = `AAPL`")

in

This method returns rows that contain a match of one or more values in the specified column.

.where("columnName in valueList")

Example

t = db.t("LearnDeephaven" , "StockQuotes")\
    .where("Date = `2017-08-25`")\
    .where("USym in `AAPL` , `GOOG`")

not in

This method returns rows that do not contain a match of one or more values in the specified column.

.where("columnName not in valueList")

Example

t = db.t("LearnDeephaven" , "StockQuotes")\
    .where("Date = `2017-08-25`")\
    .where("USym not in `AAPL` , `GOOG`")

icase in

This method returns rows that contain a match of one or more values in the specified column regardless of the capitalization of the values.

.where("columnName icase in valueList")

Example

t = db.t("LearnDeephaven" , "StockQuotes")\
    .where("Date = `2017-08-25`")\
    .where("USym icase in `aapl` , `goog`")

icase not in

This method returns rows that do not contain a match of one or more values in the specified column regardless of the capitalization of the values.

.where("columnName icase not in valueList")

Example

t = db.t("LearnDeephaven" , "StockQuotes")\
    .where("Date = `2017-08-25`")\
    .where("USym icase not in `aapl` , `goog`")

Using Variables within Match Filters

The right side of a match filter using the "in" statement allows for use of variables as follows:

• list of variables: A in X, Y, Z - the filter will return true for all the rows where A is equal to X, Y, or Z
• single variable name: A in X:
  • If X is a Java array or java.util.Collection, the filter will return true for all the rows where A is equal to one element of X
  • For all other types of X, the filter will return true for all the rows where A is equal to X

Conversely, the right side of a "not in" statement allows for use of variables as follows:

• list of variables: A not in X, Y, Z - the filter will return true for all the rows where A is not equal to X, Y, or Z
• single variable name A not in X:
  • If X is a Java array or Collection, the filter will return true for all the rows where A is not equal to one element of X
  • For all other types of X, the filter will return true for all the rows where A is not equal to X

Conditional Filters

Conditional filters can be used to filter data based on formulas other than those included in match filters. Conditional filters are not optimized like match filters. Therefore they should be placed after match filters in a given where clause. Conditional filters can be any arbitrary Java expression evaluator, including:

• where("x==y")
• where("x>y")
• where("x<y")
• startsWith()
• endsWith()

Example 1

t1 = db.t("LearnDeephaven" , "StockQuotes")\
  .where("Date = `2017-08-25`")\
  .where("Bid>30")

As you can see below, the lowest value in "t1" is now 30.79.

Example 2

t2 = db.t("LearnDeephaven" , "StockQuotes")\
    .where("Date = `2017-08-25`")\
    .where("Bid%2==0")

Example 3

The next example uses formulas on multiple columns.

t3 = db.t("LearnDeephaven" , "StockQuotes")\
    .where("Date = `2017-08-25`")\
    .where("BidSize%2==1","Exchange.startsWith(`N`)")

Here, StockQuotes is filtered to rows where:

• the value in the BidSize column has a remainder of 1 after being divided by 2, and
• the value in the Exchange column starts with the string N.

Filtering with Regexes

When working with Strings, it is often useful to be able to filter your data down using more complicated patterns than 'starts with' or 'ends with.' For these situations, the RegexFilter may be useful.

The general syntax follows:

.where(new com.illumon.iris.db.v2.select.RegexFilter("ColumnName", "<regex>"))

Example

t2 = db.t("LearnDeephaven" , "StockQuotes")\
 .where("Date = `2017-08-25`")\
 .where(new com.illumon.iris.db.v2.select.RegexFilter("USym", "C..O"))

This example will find all rows where the USym column has a value starting with C and ending with O, with exactly two characters in between, such as CSCO or C12O or even C O.

Conjunctive and Disjunctive Filtering

When filtering on multiple columns in a table, your query can be written so they work on a conjunctive basis or a disjunctive basis.

Conjunctive

In conjunctive filtering, all filters within a where() clause are evaluated. For example, two filters are working conjunctively in the following:

t4 = db.t("LearnDeephaven" , "StockQuotes")\
    .where("Date = `2017-08-25`")\
    .where("Bid>30","Exchange.startsWith(`N`)")

In this case, the filter returns rows only when the value in the Bid column is greater than 25, and the value in the Exchange column starts with the string N.

The following sample queries all use equivalent variations of conjunctive filtering:

Example 1

t1=t.where("A in firstSet", "B in secondSet")

t1 will evaluate, using an optimized match filter, "A in firstSet" and then only for values which match the A filter, it will evaluate "B in secondSet".

Example 2

Assuming firstSet and secondSet are collections, the query could also be written with a condition filter as:

t2=t.where("firstSet.contains(A)", "secondSet.contains(B)")

t2 is logically the same, but instead of using an optimized match filter, it will evaluate the "A" column for each row and pass it to the condition filter "firstSet.contains(A)". For rows that pass this filter, it will evaluate the "B" column, and pass those values to "secondSet.contains(B)".

Example 3

This query could also be written as:

t3=t.where("firstSet.contains(A) && secondSet.contains(B)")

t3 will produce the same output as t2, but the "A" and "B" columns are evaluated and passed into the filter expression ("firstSet.contains(A) && secondSet.contains(B)").

Disjunctive

In disjunctive filtering, each filter clause is evaluated independently, and results are presented if any of the filters return results. For example, two filters are working disjunctively in this clause:

t5 = db.t("LearnDeephaven" , "StockQuotes")\
    .where("Date = `2017-08-25`")\
    .where("Bid>30 || Exchange.startsWith(`N`)")

In this case, the filter will present rows only when the value in the Bid column is greater than 30, or the value in the Exchange column starts with the string N, or both.

The following syntax can also be used to indicate disjunctive filtering:

t6 = t.whereOneOf("Bid>25", "Exchange.startsWith(`N`)")

Composing Complex Where Clauses

• When building a query that applies multiple filters to the same data, the best approach is to chain multiple filter clauses within the same where method.
• When you use any filter on a partitioning column or when you specifically use a match filter on a grouping column, that filter should be an independent clause.
• If you have a complex formula, then it is best to combine the clauses if and only if they reference the same column(s).
• Multiple clauses can be combined into single where method calls or separated into multiple where method calls. The results and processing effort are the same, but multiple where clauses may improve readability of the query.
• Order matters and you should generally put the more selective or more efficient filters first, although the exact performance may vary depending on the filters themselves or the data being filtered. In many cases, doing so allows the query engine to avoid overhead.

The following where clauses illustrate these differences:

Example 1 ✅

where("Date=2011-03-21","USym==AAPL", "BidMkt > 100")

The partitioning column (Date) is evaluated first, followed by the USym filter.

Example 2 🚫

where("USym==AAPL", "Date=2011-03-21", "BidMkt > 100")

The USym filter is evaluated first, without having first pruned off irrelevant partitions. Every row of every partition's USym column must be examined, which takes longer and uses more computing power.

Example 3 🚫

where("Date=2011-03-21", "BidMkt > 100","USym==AAPL")

The partitioning column (Date) is evaluated first, which is good. However, the USym filter can be executed more efficiently on grouped data, so that filter should be placed ahead of the BidMkt filter.

Example 4 ✅

where("Date=2011-03-21", "BidMkt > 100 && BidMkt < 200")

The partitioning column (Date) is evaluated first, and there is only a single formula evaluation for the conditions on BidMck.

Example 5 ⚠

where("Date=2011-03-21", "BidMkt > 100", "BidMkt < 200")

The partitioning column (Date) is evaluated first, but two formulas must be evaluated (one for BidMkt > 100, and one for BidMkt < 200) rather than one.

Example 6 ⚠

where("Date=2011-03-21").where("BidMkt > 100 && BidMkt < 200")

The partitioning column (Date) is evaluated first, but an intermediate table is created by the secondary where clause.

Example 7 ✅

where("Date=2011-03-21", "USym == AAPL", "BidMkt > 100 && BidMkt < 200")

The partitioning column (Date) is evaluated first, and the USym filter and the BidMkt filter are independently evaluated in the best order.

Example 8 🚫

where("Date=2011-03-21", "USym == AAPL && BidMkt > 100 && BidMkt > 200")

The partitioning column (Date) is evaluated first, but "USym == AAPL" cannot be applied as a match filter, because it is part of a more complex formula.

whereIn and whereNotIn

The whereIn and whereNotIn methods enable you to filter one table based on the contents of another table, which may or may not contain ticking data.

A where clause is evaluated only when a row in the filtered table ticks. whereIn and whereNotIn are evaluated whenever either table changes. Join expressions, such as join and naturalJoin are also evaluated when either table changes.

The syntax follows:

validResults = tableToFilter.whereIn(validValuesTable, "ColName")

The "tableToFilter" table is the left table; the "validValuesTable" table is the right table. This query will filter the specified column in "tableToFilter" to only the values in the "validValueTables" and store the results in a new table, "validResults".

The whereNotIn syntax is identical:

invalidResults = tableToFilter.whereNotIn(validValuesTable, "ColName")

As you can imagine, whereNotIn returns the values from the specified column that do not appear in our "validValuesTable", or the "invalid" results.

You can also specify more than one column to filter, and the names need not be the same.

For example, if we wanted to match on USym and Expiry, but the "tableToFilter" table had a column named Maturity instead, we could use the following construct:

validResults=tableToFilter.whereIn(validValueTables, "USym", "Maturity=Expiry")

Example

To demonstrate, we will create a "usymsOfInterest" table. If this were a ticking table, its contents could vary as it returns the first three distinct USyms at any given time. Then, we'll filter another table of stock data (that might include thousands of USyms), to only show the USyms that we care about — the ones in your "usymsOfInterest" table. We'll also create a table with the invalid results.

trades = db.t("LearnDeephaven", "StockTrades").where("Date=`2017-08-25`")

usymsOfInterest = trades.countBy("Count", "USym").sortDescending("Count").head(3)

interestingStockQuotes = trades.whereIn(usymsOfInterest, "USym")

invalid=trades.whereNotIn(usymsOfInterest, "USym")

In the image below, we use the Advanced Filters dialog as a quick way to confirm "interestingStockQuotes" is limited to the three USyms in our "usymsOfInterest" table.

The invalid table contains only rows with a USym value that is not in the "usymsOfInterest" list.

Unlike naturalJoin, whereIn can be used when there are more than one matching value in the right table for values in the left table. This is true of join as well, but whereIn is faster to return matching rows than join.

Also, whereIn only provides filtering, and does not allow adding columns from the right table. In some cases it may be desirable to use whereIn to filter and then join to add columns from the right table. This provides similar performance to naturalJoin while still allowing multiple matches from the right table.

Head & Tail Filtering

Head and tail filters are used to return the first or last set of rows of a table by specifying the number of rows desired or by specifying the percent of the table.

The following filters are used to return a specific number of rows:

• head()
• tail()

The following filters are used to return a specific percentage of rows:

• headPct()
• tailPct()

For example,

t = db.t("LearnDeephaven" , "StockQuotes")\
    .where("Date = `2017-08-25`")\
t2 = t.head(10) # returns the first 10 rows of a table
t3 = t.tail(20) # returns the last 20 rows of a table
t4 = t.headPct(0.25) # returns the first 25% of rows contained in a table
t5 = t.tailPct(0.5) # returns the last 50% of rows contained in a table

DownsampledWhereFilter

The DownsampledWhereFilter enables users to downsample time series data by calculating the bin intervals for values, and then using upperBin and lastBy to select the last row for each bin.

import com.illumon.iris.db.v2.select.DownsampledWhereFilter

downsampledX = x.where(new DownsampledWhereFilter("Timestamp", 5 * MINUTE))

from deephaven import *

downsampledX = x.where(DownsampledWhereFilter("Timestamp", 5*dbtu.MINUTE))

The default for this method is to downsample the bins based on upperBin and lastBy. However, you can downsample the bin based on lowerBin and firstBy by adding a third argument to the DownsampledWhereFilter method. An example follows with the third argument highlighted:

import com.illumon.iris.db.v2.select.DownsampledWhereFilter

downsampledX = x.where(new DownsampledWhereFilter("Timestamp", 5 * MINUTE,
    DownsampledWhereFilter.SampleOrder.LOWERFIRST))

from deephaven import *

downsampledX = x.where(DownsampledWhereFilter("Timestamp", 5*dbtu.MINUTE,\
                                              DownsampledWhereFilter.SampleOrder.LOWERFIRST))

• LOWERFIRST is the constant for lowerBin/firstBy.
• UPPERLAST is the constant for upperBin/lastBy.

Either constant works in this query. However, if the third argument is not present, the downsampling will occur on an upperBin/lastBy basis.

All the following examples will give you the same result of trades after or including 11am:

p=db.i("SystemEquity","TradeData").where("Date=`2012-02-15`", "Timestamp>='2012-02-15T11:00:00 NY'")

p=db.i("SystemEquity","TradeData").where("Date=`2012-02-15`", "Timestamp+'1:00:00'>='2012-02-15T10:00:00 NY'")

p=db.i("SystemEquity","TradeData").where("Date=`2012-02-15`", "Timestamp+'T1h'>='2012-02-15T10:00:00 NY'")

p=db.i("SystemEquity","TradeData").where("Date=`2012-02-15`", "hourOfDay(Timestamp, TZ_NY)>=11")