pydeephaven.query¶

This module implements the Query class that can be used to execute a chained set of Table operations in one batch.

class Query(session, table)[source]¶

Bases: TableInterface

A Query object is used to define and execute a sequence of Deephaven table operations on the server.

When the query is executed, the table operations specified for the Query object are batched together and sent to the server in a single request, thus avoiding multiple round trips between the client and the server. The result of executing the query is a new Deephaven table.

Note, an application should always use the factory method on the Session object to create a Query instance as the constructor is subject to future changes to support more advanced features already planned.

agg_all_by(agg, by)[source]¶

Adds an AggregateAll operation to the query.

Parameters:

agg (Aggregation) – the aggregation to be applied
by (Union[str, List[str]]) – the group-by column name(s)

Return type:

Returns:

self

agg_by(aggs, by)[source]¶

Adds an Aggregate operation to the query.

Parameters:

by (Union[str, List[str]]) – the group-by column name(s)
aggs (Union[Aggregation, List[Aggregation]]) – the aggregation(s) to be applied

Return type:

Returns:

self

aj(table, on, joins=None)[source]¶

Adds a as-of join operation to the query.

Parameters:

table (Table) – the right-table of the join
on (Union[str, List[str]]) – the column(s) to match, can be a common name or a match condition of two columns, e.g. ‘col_a = col_b’. The first ‘N-1’ matches are exact matches. The final match is an inexact match. The inexact match can use either ‘>’ or ‘>=’. If a common name is used for the inexact match, ‘>=’ is used for the comparison.
joins (Union[str, List[str]], optional) – the column(s) to be added from the right table to the result table, can be renaming expressions, i.e. “new_col = col”; default is None

Return type:

Returns:

self

avg_by(by=None)[source]¶

Adds an avg-by aggregation to the query.

Parameters:: by (Union[str, List[str]], optional) – the group-by column name(s), default is None
Return type:: Query
Returns:: self

count_by(col, by=None)[source]¶

Adds a count-by aggregation to the query.

Parameters:

col (str) – the name of the column to store the counts
by (Union[str, List[str]], optional) – the group-by column name(s), default is None

Return type:

Returns:

self

drop_columns(cols)[source]¶

Adds a drop-columns operation to the query.

Parameters:: cols (Union[str, List[str]]) – the column name(s)
Return type:: Query
Returns:: self

exact_join(table, on, joins=None)[source]¶

Adds an exact-join operation to the query.

Parameters:

table (Table) – the right-table of the join
on (Union[str, List[str]]) – the column(s) to match, can be a common name or an equal expression, i.e. “col_a = col_b” for different column names
joins (Union[str, List[str]], optional) – the column(s) to be added from the right table to the result table, can be renaming expressions, i.e. “new_col = col”; default is None

Return type:

Returns:

self

exec()[source]¶

Executes the query on the server and return the result table.

Return type:: Table
Returns:: a Table object
Raises:: DHError –

first_by(by=None)[source]¶

Adds a first-by aggregation to the query.

Parameters:: by (Union[str, List[str]], optional) – the group-by column name(s), default is None
Return type:: Query
Returns:: self

group_by(by=None)[source]¶

Adds a group-by aggregation to the query.

Parameters:: by (Union[str, List[str]], optional) – the group-by column name(s); default is None
Return type:: Query
Returns:: self

head(num_rows)[source]¶

Adds a head operation to the query.

Parameters:: num_rows (int) – the number of rows at the head of table
Return type:: Query
Returns:: self

head_by(num_rows, by)[source]¶

Adds a head-by operation to the query.

Parameters:

num_rows (int) – the number of rows at the beginning of each group
by (Union[str, List[str]]) – the group-by column name(s)

Return type:

Returns:

self

join(table, on=None, joins=None, reserve_bits=10)[source]¶

Adds a cross-join operation to the query.

Parameters:

table (Table) – the right-table of the join
on (Union[str, List[str]]) – the column(s) to match, can be a common name or an equal expression, i.e. “col_a = col_b” for different column names
joins (Union[str, List[str]], optional) – the column(s) to be added from the right table to the result table, can be renaming expressions, i.e. “new_col = col”; default is None
reserve_bits (int, optional) – the number of bits of key-space to initially reserve per group; default is 10

Return type:

Returns:

self

last_by(by=None)[source]¶

Adds a last-by aggregation to the query.

Parameters:: by (Union[str, List[str]], optional) – the group-by column name(s), default is None
Return type:: Query
Returns:: self

lazy_update(formulas)[source]¶

Adds a lazy-update operation to the query.

Parameters:: formulas (Union[str, List[str]]) – the column formula(s)
Return type:: Query
Returns:: self

max_by(by=None)[source]¶

Adds a max-by aggregation to the query.

Parameters:: by (Union[str, List[str]], optional) – the group-by column name(s), default is None
Return type:: Query
Returns:: self

median_by(by=None)[source]¶

Adds a median-by aggregation to the query.

Parameters:: by (Union[str, List[str]], optional) – the group-by column name(s), default is None
Return type:: Query
Returns:: self

min_by(by=None)[source]¶

Adds a min-by aggregation to the query.

Parameters:: by (Union[str, List[str]], optional) – the group-by column name(s), default is None
Return type:: Query
Returns:: self

natural_join(table, on, joins=None)[source]¶

Adds a natural-join operation to the query.

Parameters:

table (Table) – the right-table of the join
on (Union[str, List[str]]) – the column(s) to match, can be a common name or an equal expression, i.e. “col_a = col_b” for different column names
joins (Union[str, List[str]], optional) – the column(s) to be added from the right table to the result table, can be renaming expressions, i.e. “new_col = col”; default is None

Return type:

Returns:

self

raj(table, on, joins=None)[source]¶

Adds a reverse as-of join operation to the query.

Parameters:

table (Table) – the right-table of the join
on (Union[str, List[str]]) – the column(s) to match, can be a common name or a match condition of two columns, e.g. ‘col_a = col_b’. The first ‘N-1’ matches are exact matches. The final match is an inexact match. The inexact match can use either ‘<’ or ‘<=’. If a common name is used for the inexact match, ‘<=’ is used for the comparison.
joins (Union[str, List[str]], optional) – the column(s) to be added from the right table to the result table, can be renaming expressions, i.e. “new_col = col”; default is None

Return type:

Returns:

self

select(formulas=None)[source]¶

Adds a select operation to the query.

Parameters:: formulas (Union[str, List[str]], optional) – the column formula(s), default is None
Return type:: Query
Returns:: self

select_distinct(cols=None)[source]¶

Adds a select-distinct operation to the query.

Parameters:: cols (Union[str, List[str]], optional) – the column name(s), default is None
Return type:: Query
Returns:: self

snapshot()[source]¶

Adds a snapshot operation to the query.

Return type:: Query
Returns:: self

snapshot_when(trigger_table, stamp_cols=None, initial=False, incremental=False, history=False)[source]¶

Adds a snapshot_when operation to the query.

Parameters:

trigger_table (Table) – the trigger table
stamp_cols (Union[str, List[str]]) – The column(s) from trigger_table that form the “stamp key”, may be renames, default is None, meaning that all columns from trigger_table form the “stamp key”.
initial (bool) – Whether to take an initial snapshot upon construction, default is False. When False, the resulting table will remain empty until trigger_table first updates.
incremental (bool) – Whether the resulting table should be incremental, default is False. When False, all rows of this table will have the latest “stamp key”. When True, only the rows of this table that have been added or updated will have the latest “stamp key”.
history (bool) – Whether the resulting table should keep history, default is False. A history table appends a full snapshot of this table and the “stamp key” as opposed to updating existing rows. The history flag is currently incompatible with initial and incremental: when history is True, incremental and initial must be False.

Return type:

Returns:

self

sort(order_by, order=None)[source]¶

Adds sort operation to the query.

Parameters:

order_by (Union[str, List[str]]) – the names of the columns to be sorted on
order (Union[SortDirection, List[SortDirection]], optional) – the corresponding sort direction(s) for each sort column, default is None. In the absence of explicit sort directions, data will be sorted in the ascending order.

Return type:

Returns:

self

std_by(by=None)[source]¶

Adds a std-by aggregation to the query.

Parameters:: by (Union[str, List[str]]) – the group-by column name(s)
Return type:: Query
Returns:: self

sum_by(by=None)[source]¶

Adds a sum-by aggregation to the query.

Parameters:: by (Union[str, List[str]]) – the group-by column name(s)
Return type:: Query
Returns:: self

tail(num_rows)[source]¶

Adds a tail operation to the query.

Parameters:: num_rows (int) – the number of rows at the end of table
Return type:: Query
Returns:: self

tail_by(num_rows, by)[source]¶

Adds a tail-by operation to the query.

Parameters:

num_rows (int) – the number of rows at the end of each group
by (Union[str, List[str]]) – the group-by column name(s)

Return type:

Returns:

self

ungroup(cols=None, null_fill=True)[source]¶

Adds an ungroup operation to the query.

Parameters:

cols (Union[str, List[str]], optional) – the array column(s), default is None, meaning all array columns will be ungrouped
null_fill (bool, optional) – indicates whether null should be used to fill missing cells, default is True

Return type:

Returns:

self

update(formulas)[source]¶

Adds an update operation to the query.

Parameters:: formulas (Union[str, List[str]]) – the column formula(s)
Return type:: Query
Returns:: self

update_by(ops, by)[source]¶

Adds an update-by operation to the query.

Parameters:

ops (Union[UpdateByOperation, List[UpdateByOperation]]) – the UpdateByOperation(s) to be applied
by (Union[str, List[str]]) – the group-by column name(s)

Return type:

Returns:

self

update_view(formulas)[source]¶

Adds an update-view operation to the query.

Parameters:: formulas (Union[str, List[str]]) – the column formula(s)
Return type:: Query
Returns:: self

var_by(by=None)[source]¶

Adds a var-by aggregation to the query.

Parameters:: by (Union[str, List[str]], optional) – the group-by column name(s), default is None
Return type:: Query
Returns:: self

view(formulas)[source]¶

Adds a view operation to the query.

Parameters:: formulas (Union[str, List[str]]) – the column formula(s)
Return type:: Query
Returns:: self

where(filters)[source]¶

Adds a filter operation to the query.

Parameters:: filters (Union[str, List[str]]) – the filter condition expression(s)
Return type:: Query
Returns:: self

where_in(filter_table, cols)[source]¶

Adds a where_in operation to the query.

Parameters:

filter_table (Table) – the table containing the set of values to filter on
cols (Union[str, List[str]]) – the column name(s)

Return type:

Returns:

self

where_not_in(filter_table, cols)[source]¶

Adds a where_not_in operation to the query.

Parameters:

filter_table (Table) – the table containing the set of values to filter on
cols (Union[str, List[str]]) – the column name(s)

Return type:

Returns:

self