Skip to main content

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.TableTools import newTable, stringCol, intCol
from deephaven.conversion_utils import NULL_INT

source = newTable(
stringCol("Letter", "A", "C", "F", "B", "E", "D", "A"),
intCol("Number", NULL_INT, 2, 1, NULL_INT, 4, 5, 3),
stringCol("Color", "red", "blue", "orange", "purple", "yellow", "pink", "blue"),
intCol("Code", 12, 13, 11, NULL_INT, 16, 14, NULL_INT),
)

stringStuff = source.view("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("Letter = `A`") # match filter

stringSetMatch = source.where("Letter in `A`, `B`, `C`")
caseInsensitive = source.where("Letter icase in `a`, `B`, `c`")
notInExample = source.where("Letter not in `A`, `B`, `C`") # see "not"

containsExample = source.where("Letter.contains(`A`)")
notContainsExample = source.where("!Letter.contains(`A`)")
startsWithExample = source.where("Letter.startsWith(`A`)")
endsWithExample = source.where("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.TableTools import newTable, stringCol, intCol

source = newTable(
stringCol("Letter", "A", "C", "F", "B", "E", "D", "A"),
intCol("Number1", 44, 42, 41, 44, 40, 45, 37),
intCol("Number2", 12, 53, 11, 40, 16, 14, 80),
)
equalsExample = source.where("round(Number1) = 44")
lessThanExample = source.where("Number1 < 40")
someManipulation = source.where("(Number1 - Number2) / Number1 > 0.05")
modExample1 = source.where("Number1 % 10 = 0")
modExample2 = source.where("Number2 % 2 = 0")

Nulls and NaNs

from deephaven.TableTools import newTable, intCol, stringCol
from deephaven.conversion_utils import NULL_INT

source = newTable(stringCol("Letter", "A", None, "D"), intCol("Number", NULL_INT, 2, 3))

nullExample = source.where("isNull(Letter)") # all data types supported
notNullExample = source.where("!isNull(Letter)")

nanExample = source.where("isNaN(Number)")
notNanExample = source.where("!isNaN(Number)")

Do math

simpleMath = source.updateView("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.updateView("RowNumber = i",\
"PrevRowReference = Number_[i-1]",\
"MultipleRowRef = Number_[i] - Number_[i-1]")

Bin data

bins = source.updateView("PriceBin = upperBin(Number, 20)")

Use ternaries (if-thens)

ifThenexample = source\
.updateView("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.TableTools import emptyTable

globals()["a"] = 7

result = emptyTable(5).update("Y = a")

Create and use custom functions

See our guides:

from deephaven.TableTools import newTable, intCol

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

source = newTable(intCol("A", 1, 2, 3))

result = source.update("X = 2 + 3 * (int)myFunction(A)")