Skip to main content
Version: Python

Query language cheat sheet

note

Backticks ` are used for strings and single quotes ' are used for timestamps and characters.

String examples

Manipulate strings

You can use any of the standard Java String operators in your queries, as the following examples show:

from deephaven import new_table
from deephaven.column import string_col, int_col
from deephaven.constants import NULL_INT

source = new_table([
string_col("Letter", ["A", "C", "F", "B", "E", "D", "A"]),
int_col("Number", [NULL_INT, 2, 1, NULL_INT, 4, 5, 3]),
string_col("Color", ["red", "blue", "orange", "purple", "yellow", "pink", "blue"]),
int_col("Code", [12, 14, 11, NULL_INT, 16, 14, NULL_INT]),
])


stringStuff = source.view(formulas=["NewString = `new_string_example_`",
"ConcatV1 = NewString + Letter",\
"ConcatV2 = NewString + `Note_backticks!!`",\
"ConcatV3 = NewString.concat(Letter)",\
"ConcatV4 = NewString.concat(`Note_backticks!!`)",\
"StartBool = Letter.startsWith(`A`)",\
"NoEndBool = !Letter.endsWith(`D`)",\
"ContainsBool = Letter.contains(`A`)",\
"NoBool = !Letter.contains(`B`)",\
"FirstChar = Letter.substring(0,1)",\
"LengthString = Letter.length()",\
"CharIndexPos = ConcatV1.charAt(1)",\
"SubstringEx = ConcatV1.substring(0,1)",\
"FindIt = NewString.indexOf(`_`)",\
"FindItMiddle = NewString.indexOf(`_`, FindIt + 1)",\
"FindLastOf = NewString.lastIndexOf(`_`)",\
"SplitToArrays = NewString.split(`_`)",\
"SplitWithMax = NewString.split(`_`, 2)",\
"SplitIndexPos = NewString.split(`_`)[1]",\
"LowerCase = Letter.toLowerCase()",\
"UpperCase = NewString.toUpperCase()",\
"DoubleToStringv1 = Number + ``",\
"DoubleToStringv2 = String.valueOf(Number)",\
"DoubleToStringv3 = Double.toString(Number)",\
"StringToDoublev1 = Double.valueOf(DoubleToStringv1)"])
oneStringMatch = source.where(filters=["Letter = `A`"]) # match filter
stringSetMatch = source.where(filters=["Letter in `A`, `B`, `C`"])
caseInsensitive = source.where(filters=["Letter icase in `a`, `B`, `c`"])
notInExample = source.where(filters=["Letter not in `A`, `B`, `C`"]) # see "not"
containsExample = source.where(filters=["Letter.contains(`A`)"])
notContainsExample = source.where(filters=["!Letter.contains(`A`)"])
startsWithExample = source.where(filters=["Letter.startsWith(`A`)"])
endsWithExample = source.where(filters=["Letter.endsWith(`M`)"])

Number examples

caution

Using i and ii is not a good idea in non-static use cases, as calculations based on these variables aren't stable.

from deephaven import new_table
from deephaven.column import string_col, int_col

source = new_table([
string_col("Letter", ["A", "C", "F", "B", "E", "D", "A"]),
int_col("Number1", [44, 42, 41, 44, 40, 45, 37]),
int_col("Number2", [12, 53, 11, 40, 16, 14, 80]),
])

equalsExample = source.where(filters=["round(Number1) = 44"])
lessThanExample = source.where(filters=["Number1 < 40"])
someManipulation = source.where(filters=["(Number1 - Number2) / Number1 > 0.05"])
modExample1 = source.where(filters=["Number1 % 10 = 0"])
modExample2 = source.where(filters=["Number2 % 2 = 0"])

Nulls and NaNs

from deephaven import new_table
from deephaven.column import string_col, int_col
from deephaven.constants import NULL_INT

source = new_table([string_col("Letter", ["A", None, "D"]), int_col("Number", [NULL_INT, 2, 3])])

nullExample = source.where(filters=["isNull(Letter)"]) # all data types supported
notNullExample = source.where(filters=["!isNull(Letter)"])
nanExample = source.where(filters=["isNaN(Number)"])
notNanExample = source.where(filters=["!isNaN(Number)"])

Do math

simpleMath = source.update_view(formulas=["RandomNumber = Math.random()",\
"RandomInt100 = new Random().nextInt(100)",\
"Arithmetic = Number * RandomInt100",\
"SigNum = signum(RandomNumber - 0.5)",\
"Signed = SigNum * Arithmetic",\
"AbsDlrVolume = abs(Signed)",\
"Power = Math.pow(i, 2)",\
"Exp = Number * 1E2",\
"Log = Number * log(2.0)",\
"Round = round(RandomInt100)",\
"Floor = floor(RandomInt100)",\
"Mod = RandomInt100 % 5",\
"CastInt = (int)AbsDlrVolume",\
"CastLong = (long)AbsDlrVolume"])

Handle arrays

arrayExamples = source.update_view(formulas=["RowNumber = i",\
"PrevRowReference = Number_[i-1]",\
"MultipleRowRef = Number_[i] - Number_[i-1]"])

Bin data

bins = source.update_view(formulas=["PriceBin = upperBin(Number, 20)"])

Use ternaries (if-thens)

ifThenexample = source\
.update_view(formulas=["SimpleTernary = Number < 2 ? `smaller` : `bigger`",\
"TwoLayers = Number <= 1 ? `small` : Number < 2 ? `medium` : `large`",\
"Conj = Number < 3 && (!isNull(Letter) && Letter.contains(`A`)) ? `special` : `boring`",\
"Disj = Number < 3 || (!isNull(Letter) && Letter.contains(`A`)) ? `fave` : `other`"])

Create and use custom variables

from deephaven import empty_table

globals()["a"] = 7

result = empty_table(5).update(formulas=["Y = a"])

Create and use custom functions

See our guides:

from deephaven import new_table
from deephaven.column import int_col

def myFunction(a):
return a * (a + 1)

source = new_table([int_col("A", [1, 2, 3])])
result = source.update(formulas=["X = 2 + 3 * (int)myFunction(A)"])