Parameterized queries

At this point, you should be familiar with how to construct, run, and debug Persistent Queries. When computations are performed on tables or chains of tables within a Persistent Query, the engine must visit and consider each and every row in the source tables. Most of the time, this is exactly what you need to do; There are many cases, however, when the vast majority of the result rows will never be used or viewed by a user. In these cases the Persistent Query is doing much more work than is strictly necessary to fill the business needs.

Parameterized Queries are a concept that bridges the gap between the traditional Persistent Query, and a query that can be executed “on demand” to produce tailored results without wasting valuable computational time and resources on data that will never be used.

The library is broken down into a few primary concepts:

• Parameter - A definition for a single input to a ParameterizedQuery. It defines the name of the parameter, its value type, display hints and other properties that define how the parameter is represented. Additionally, it contains a Constraint object that the infrastructure uses to validate user inputs before passing them on to the computation.
• Constraint - A tree of objects that allows the definition of complex constraints that individual parameter values must meet in order to be passed onto the ParameterizedQuery function.
• ParameterizedQuery - A set of named Parameter definitions and a single function that defines the computation that should be performed when parameters are supplied.
• ParameterScope - A universe of parameter values and result values that is unique for a single invocation of the query work. The query work function is provided an instance of this to read its input parameters from as well as set the value of any results created during the computation.

Create a Parameterized Query

Creating a Parameterized Query within your Persistent Query is very simple. First, import io.deephaven.query.parameterized.ParameterizedQuery and io.deephaven.query.parameterized.Parameter; then, construct a new Parameterized Query using the .create() method:

import io.deephaven.query.parameterized.ParameterizedQuery
import io.deephaven.query.parameterized.Parameter

myQuery = ParameterizedQuery.create()

Next, add all of the parameters you need by chaining together .with(Parameter) calls. We will cover Parameter creation in the next section.

import io.deephaven.query.parameterized.ParameterizedQuery
import io.deephaven.query.parameterized.Parameter

myQuery = ParameterizedQuery.create()
	.with(Parameter.ofLong("intParam").inRange(0,100))
	.with(Parameter.ofString("stringParam")
	// Chain together as many as you need

Finally, attach the query method that will do the hard work using the .willDo() method. Your query method must be in the form of a function that accepts a single ParameterScope argument. The code within that method must get its parameter values and set the values of any results from the ParameterScope instance. Invoke .build() and your query is ready to go!

import io.deephaven.query.parameterized.ParameterizedQuery
import io.deephaven.query.parameterized.Parameter

myQuery = ParameterizedQuery.create()
	.with(Parameter.ofLong("intParam").inRange(0,100))
	.with(Parameter.ofString("stringParam"))
	.with(Parameter.ofDouble("doubleParam").inRange(-1.0, 1.0).optional())
	.willDo({scope ->
		def intParam = scope.getInteger("intParam")
		def stringParam = scope.getString("stringParam")

		// Get the authenticated or effective ('run as') user names of the person or
		// process executing this query.
		def authenticatedUser = scope.getAuthenticatedUser()
		def effectiveUser = scope.getEffectiveUser()

		// Get the groups of the effective user.
		def groups = scope.getPermissionFilterProvider().getGroupsForUser(scope.getUserContext())

                // Using db.i() or db.t() will return results in the context of the query's owner, not the executor.
		// You may want to perform this fetch with the owner's elevated permissions, for example, to produce
		// summaries that can be more broadly shared. The query writer is responsible for ensuring that they
		// are not returning sensitive data to viewers.
		def tableResultAsOwner = db.i("SomeNamespace", "SomeTable")

		// Using the fetchTableIntraday() or fetchTableHistorical() from the scope will return results in the
		// context of the query's executor, which will apply the permissions for that user. You can then safely
		// display the resulting table to that user.
		def tableResult = scope.fetchTableIntraday("SomeNamespace", "SomeTable")

		def tableResult = tableResult.where("Date=currentDateNy()", "StringCol=`$stringParam`")
			.updateView("OffsetInt = IntCol + $intParam", "Scaled = (double)OffsetInt")

		if (scope.isPresent("doubleParam")) {
		    def maybeDoubleParam = scope.getDouble("doubleParam")
			tableResult = tableResult.updateView("Scaled = OffsetInt * $maybeDoubleParam")
		}

		plotResult = plot("Stuff", tableResult, "Timestamp", "Scaled").show()

		scope.setResult("tableResult", tableResult)
		scope.setResult("plotResult", plotResult)
    }).build()

Before we go into more detail, there are a few things to watch out for:

• First, make sure that you are not polluting the parent environment with your query variables. For example, in Groovy, if you do not use the def keyword when defining variables within your method, you will put those variables in the global scope of the query, instead of just within your ParameterSession.
• Second, if parameters are declared as optional(), you must first check that they are present in the scope with .isPresent() before you request them. Requesting a parameter that has not been provided will produce a ParameterException.
• Third, use the fetchTableIntraday() and fetchTableHistorical() methods of the query's willDo lambda variable (scope in above example) to fetch database results as the user id who is executing the query, and not the query owner.

Computations and the Live Table Monitor Lock

By default, Parameterized Queries will use the Live Table Monitor's Shared Lock for computation. This means that other operations that require the shared lock can execute concurrently with them, but the Live Table Monitor will not be able to update tables until they are complete.

If you can guarantee that the operations used within the query method do not require the Live Table Monitor lock at all, you can instruct the library to not use it with the ParameterizedQueryBuilder requireComputeLock(boolean requiresLock) method. Calling this method with a false parameter will tell the library that it does not need to take the Live Table monitor lock for computations.

Parameters

Parameters form the core of the Parameterized Query. Parameters are defined by name and contain all of the information required to present them to a user, process, and validate them. To create a parameter, use one of the factory methods in the Parameter class.

Each one of the methods above return a ParameterBuilder with which you can specify additional properties of the Parameter.

Examples

Let’s say you wanted to create a String parameter, representing a calendar date that was optional, and present it as a date picker:

Parameter.ofString("Date").asDatePicker().optional()

Let’s create a double parameter that must fall within the range [-1, 1]:

Parameter.ofDouble("aDoubleParam").inRange(-1, 1)

An open ended set of double values in the range (0, 100):

Parameter.setOfDouble("aRangeSet").inRange(0,100)

A set of String from a specified set of values:

Parameter.setOfString("aStringSet").in("One", "Two", "Three", "Four")

Constraints

In all of the sections above, we have been using various helper methods in ParameterBuilder to assign the Constraints of the Parameters. This is useful for simple and commonly used constraints. You may choose to specify far more complex constraints for your Parameters directly using the Constraint API.

There are three Constraint classes that can be used to build a complex constraint.

Example

As an example, you could specify a Double parameter that must be between -1, and 1, but not 0, or between 100 and 200.

Parameter.ofDouble("fancyDouble")
        .withConstraint(Composition.or(
                Composition.and(
                        Comparison.greaterThan("-1"),
                        Comparison.lessThan("1"),
                        InConstraint.notIn("0")
                ),
                Composition.and(
                        Comparison.greaterThan("100"),
                        Comparison.lessThan("200")
                )
        ))

This syntax is much more verbose than the helper methods, but allows you to specify much more sophisticated constraints.

The Query Function

The query function defines the work that will be performed whenever a user submits a set of parameters for processing from the UI. This can be a Groovy closure, Python function, or Java lambda function. The only requirement is that the function accepts a single parameter that is the ParameterScope object to interact with.

The ParameterScope interface contains a handful of methods that your function can use to retrieve parameters and set results.

When you write the query function, be sure to use appropriate local variable declarations for the language you are using so you do not pollute the global query scope. For example, if the language is Groovy, you must use the def keyword to define local variables for your query function (see the example above). Additionally, when using optional parameters, you must check .isPresent() before attempting to acquire an optional Parameter’s value with one of the getter methods or a ParameterException will be thrown if it is not available.

Access Parameterized Queries in the Classic UI

Once a ParameterizedQuery has been created and is running, users simply need to open the widget from the Show Widget menu in the QueryConfig panel.

This will present you with the Parameter Control Panel:

Each parameter is named and is laid out in the order they were set in the query, flowing from left to right, top to bottom. Any default values that were set in the query will be applied here the first time the user opens the panel.

• On the lower left are the controls you will use to open and view the results of a particular invocation of the Parameterized Query.
• On the lower right hand side is the Submit button used to execute the query.

See the next section for a breakdown of the different available display types.

Once parameters have been entered, click Submit to execute the query. Upon completion, the items on the lower left hand side of the panel will be populated with the results of the query.

Select the desired item and simply click Show Table or Show Widget in the same way you would interact with the Query Config panel.

You may open the Parameter widget multiple times to perform different queries concurrently. Windows opened will be permanently associated with the panel they were created from and will close with it. The layout and configuration of each view is saved to the user’s workspace just like any other standard Deephaven table or widget. Further, when a console is restarted the widgets will automatically re-populate when the user executes a new query.

Display Types

Text Editor

The Text Editor is the most basic input component. It allows the user to enter any text into the box. As the user types the value will be automatically validated and the user will be alerted if it does not meet constraints.

Date Picker

The Date Picker is a component that lets the user pick a specific calendar date using a convenient pop-up selector. The pop-up is activated when the user clicks the button to the right of the component.

Date Time Picker

The Date Time Picker is similar to the Date Picker. It provides a simple calendar popup to select a date, and it also provides a Time of Day editor to select a specific time of the day.

Drop Down

The Drop Down editor allows users to select a single value from a set of specified input values va a Combo Box style drop down menu.

Fixed List

The Fixed List component allows users to select one or more items from a given set of items. Depending on the configuration of the Parameter, it will let users select a single, or multiple items. It is also searchable and sortable using the controls on the top of the item.

Editable List

The Editable List component allows the user to enter desired values using the Add Item button. Items may also be deleted from the list by clicking the Trash Can icon on the right hand side of each row.

Checkbox

A Checkbox is a simple component that allows the user to set a single boolean value.

Links

The parameter control panel fully supports both inbound and outbound links for any parameters that are not set types. When operating as a link source, clicking the Submit button will filter any linked views to the values of the current parameter set. When operating as a link target, the parameter values will be taken from the source of the link.

OneClick

Parameterized Queries accept input from a OneClick. If a OneClick event's Column matches a Parameter name within the Parameterized Query panel, the Parameter is updated and the change is automatically applied.

Appendix

The following toy program generates a table of what looks and behaves like a simple order feed. It then builds a ParameterizedQuery on top of that table that allows the user to filter and mutate the results to produce a result table and some plots. It also shows an example of creation of a complex widget like an Input Table.

import io.deephaven.query.parameterized.ParameterizedQuery
import io.deephaven.query.parameterized.Parameter

Symbols = ["SPY", "PFF", "FB", "DIS", "USO", "XLF", "XLU", "REM", "REZ", "GME", "HDSN"]
Exchanges = ["NYSE", "INET", "EDGX", "BATS", "EDGA" , "MEMX", "BATY", "XOTC", "OTCU"]
Traders = ["John", "Mark", "Paul", "Bill", "Steve", "Brian", "Kevin", "Miranda", "Shirley", "Diana", "Grettle", "Clarissa"]

priceMap = ["PFF":37.50, "SPY":395.00, "USO":40.00, "XLF":31.50, "XLU":62.50, "REM":33.33, "REZ":71.33, "HDSN":1.85, "FB":270.00, "DIS":190.00, "GME":17.50]
pricerFunc = { final String USym, final double E ->
    def _aPrice = priceMap[USym]
    if (_aPrice == null) {
        priceMap[USym] = abs((int)E)/100.0
    } else {
        priceMap[USym] = ((int)(_aPrice*100) + (int)(E/2000))/100.0
    }
    return (double)priceMap[USym]
}

distributerFunc = { final int cnt ->
    def n = new Random().nextInt((int)(cnt*(cnt+1)/2)) +1
    for (def i = 0; i < cnt; i++) {
        n -= (cnt-i)
        if (n <= 0) {
            return (int)i;
        }
    }
}

TickingTable = db.timeTable("00:00:00.333")
        .tail(100000)
        .update("Date=currentDateNy()",
                "A=new Random().nextInt(10)",
                "B=new Random().nextInt(2)",
                "C=new Random().nextInt(50)",
                "D=(new Random().nextDouble()-.5) * 20000.0",
                "USym=Symbols[(int)distributerFunc.call(Symbols.size())].toString()",
                "Size=Math.max((new Random().nextInt(11))*100, (new Random().nextInt(149))+1)",
                "Price=(double)pricerFunc.call(USym, D)",
                "TradeVal=Size*Price",
                "Exchange=Exchanges[C%Exchanges.size()].toString()",
                "Trader=Traders[C % Traders.size()].toString()",
                "I=i")


ITNS = "Example"
ITNAME = "ParQInput"

if(!db.hasTable(ITNS, ITNAME)) {
    InputTable.newInputTable(db, ITNS, ITNAME,
            cKey("KeyCol", String),
            c("Value", String))
}

theIt = InputTable.it(db, ITNS, ITNAME)
Editor = new com.illumon.iris.console.utils.LiveInputTableEditor(theIt)

buildLongSetFilter = { long[] aArray, columnName ->
    def filterString = columnName + " in "

    for(def ii = 0; ii < aArray.length; ii++)  {
        filterString = filterString + aArray[ii] + ((ii == aArray.length-1) ? "" : ", ")
    }
    return filterString
}

buildStringSetFilter = { String[] aArray, columnName ->
    def filterString = columnName + " in "

    for(def ii = 0; ii < aArray.length; ii++)  {
        filterString = filterString + "`" + aArray[ii] + "`" + ((ii == aArray.length-1) ? "" : ", ")
    }
    return filterString
}

pq = ParameterizedQuery.create()
        .with(Parameter.ofString("Date").asDatePicker().optional().toolTip("The Date!"))
        .with(Parameter.ofDateTime("StartTime").optional().toolTip("The Start!"))
        .with(Parameter.ofDateTime("EndTime").optional().toolTip("The End!"))
        .with(Parameter.ofString("USym1").sizeHint(50,-1).toolTip("The First USym"))
        .with(Parameter.ofString("USym2").sizeHint(50,-1).optional().toolTip("Maybe Another USym"))
        .with(Parameter.ofDouble("PriceFactor").inRange(0.0d, 1.0d).withDefault(1).sizeHint(80, -1).toolTip("Price Multiplier"))
        .with(Parameter.ofDouble("SizeFactor").inRange(0.0d, 1.0d).withDefault(1).sizeHint(80, -1).toolTip("Size Multiplier"))
        .with(Parameter.ofString("Exchange").in(Exchanges as String[]).sizeHint(150, -1).toolTip("The Exchange")) // Allows any single value within the specified set
        .with(Parameter.ofBoolean("EvenOnly").withDefault(false).sizeHint(50, -1).toolTip("Should it be even?"))
        .with(Parameter.setOfLong("FromA").in(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10).optional().sizeHint(50, -1).toolTip("Which from A?"))
        .with(Parameter.setOfLong("FromC").asEditableList().inRange(0, 50).optional().sizeHint(50, -1).toolTip("Which from C"))
        .with(Parameter.ofTable("OfTable", theIt, "Value").sizeHint(-1, 100).toolTip("From the table").optional())
        .willDo({ h ->
            def date;
            if(h.isPresent("Date")) {
                date = h.getString("Date")
            } else {
                date = currentDateNy()
            }

            def whereClauses = new ArrayList()
            whereClauses.add("Date=`" + date + "`")
            if(h.isPresent("StartTime") && h.isPresent("EndTime")) {
                whereClauses.add("Timestamp >= " + h.getLong("StartTime"))
                whereClauses.add("Timestamp <= " + h.getLong("EndTime"))
            }

            def USym1 = h.getString("USym1")
            if(h.isPresent("USym2")) {
                whereClauses.add("USym in `" + USym1 + "`, `" + h.getString("USym2") + "`")
            } else {
                whereClauses.add("USym in `" + USym1 + "`")
            }

            def Exchange = h.getString("Exchange")
            whereClauses.add("Exchange = `" + Exchange + "`")

            if(h.isPresent("FromA")) {
                whereClauses.add(buildLongSetFilter(h.getLongSet("FromA"), "A"))
            }

            if(h.isPresent("FromC")) {
                whereClauses.add(buildLongSetFilter(h.getLongSet("FromC"), "C"))
            }

            if(h.isPresent("OfTable")) {
                whereClauses.add(buildStringSetFilter(h.getStringSet("OfTable"), "Trader"))
            }

            if(h.getBoolean("EvenOnly")) {
                whereClauses.add("(I % 2) == 0")
            }

            println "Filters: " + whereClauses

            def PriceFactor = h.getDouble("PriceFactor")
            def SizeFactor = h.getDouble("SizeFactor")

            // Filter the source based on the whereClauses we have built up from parameters
            // then apply the scaling factors
            def adjustedTrades = TickingTable.where(whereClauses.toArray(new String[0]))
                    .updateView("AdjustedPrice=Price*$PriceFactor", "SizeAdj = Size*$SizeFactor")

            def priceSizePlot = figure().plotBy("Price", adjustedTrades, "Timestamp", "Price", "USym")
                    .newChart().plotBy("Size", adjustedTrades, "Timestamp", "Size", "USym").show()

            def oc = oneClick(adjustedTrades, "USym")
            def ocPlot = plot("Price ", oc, "Timestamp", "Price").twinX()
                    .plot("Size", oc, "Timestamp", "Size").show()

            def NS = "Example"
            def TN = "OnDemand-Internal"
            if(!db.hasTable(NS,TN)) {
                InputTable.newInputTable(db, NS, TN,
                        cKey("Key", String),
                        c("C1", int),
                        c("C2", double),
                        c("C3", String))
            }
            def it = InputTable.it(db, NS, TN)
            def edit = new com.illumon.iris.console.utils.LiveInputTableEditor(it)

            h.setResult("adjustedTrades", adjustedTrades)
            h.setResult("it", it)
            h.setResult("edit", edit)
            h.setResult("PriceSize", priceSizePlot)
            h.setResult("ocPlot", ocPlot)
        }).build()