Visual Query Builder

A graphical tool enabling users to quickly build and visualize database queries without requiring knowledge of kdb or q.

Visual Query Builder is best used in conjunction with another component, such as Data grid. To see this in action, check out the Demo Data dashboard and switch to the Visual Query Builder tab.

Set up

1. Define the Connection which will populate the list of available data sources to build the query.

2. Define the Data Source which is the output for the query, and used in other components.

Basics

Name

A name for the component provided by the user.

Data Source

This should match the data source used as the output (where Selected Node and KDB are assigned view state parameters). Any errors returned by the data source will be highlighted in the query builder on the node where the error occurs.

Data sources

Connection

Select a kdb database.

Connections

Direction

Define order flow of visual query: left-to-right, right-to-left, top-to-bottom, bottom-to-top.

Dialog Placement

Configuration dialogs can appear to the left or right of the query builder interface, or as a popup

Auto Save Dialogs

Automatically save value changes made in left or right dialog menus, but not for popup dialogs.

Read only

Disables dialogs, preventing changes from being made to the query.

Force Selection

Prevents Visual Query Builder from a no-node-selection state. Selecting a node runs the query to that point - ignoring nodes beyond it. If no nodes are selected, then all node are factored; when Force Selection is enabled, a single node must be selected, which is the last node if no other node is selected (default).

Selected Node

A view state parameter assigned to the selected node within the visual query.

Source

A view state parameter storing the complete structure of the query as JSON. It is best used in conjunction with the Editable list component to toggle between several different queries. See the Demo Data dashboard for an example of this.

KDB

A view state parameter storing the output kdb string. This is used in the output data source, which is generally rendered inside a data grid.

Also assign Selected Node

The output query requires both the KDB and Selected Node view state parameters

Bindings

Allows node functions to be assigned to view state parameters.

Key

The Name of the assigned binding, this will appear in the function dropdown.

Viewstate

The viewstate to link to the function:

Check View State Type

The view state type must be of the same type as the parameter you plan to assign it to, or else it won't be available to select in the function parameter dropdown.

Create the binding

Set the view state

Edit the function

Bind the parameter to the created view state

Build query

Users start by adding a data source to query; this can be done with a right-click on the empty Data node.

Once a data source is assigned, right clicking inside the query builder will offer additional options:

Streaming queries

Support for streaming queries is available with Builder of the data editor.

Add data source

Select a data source from the drop down. Available data sources will be determined by your Connection

Add filter

Filter against the data source using equal, not equal, less, less or equal, greater, greater or equal, is blank, is not blank, in, not in or use a histogram range selector.

Check distinct to pull distinct values from all checked columns; each additional distinct column will include all distinct pairing across selected columns.

Select columns from the data source. Data Columns can be renamed in the Select - Columns option.

Shared column names

When creating a Join between different data sources, the joining column(s) must share the same name.

Select columns, and filter order can be re-organized using drag-and-drop.

Add update

Update values within a column

Add group-by

Create aggregate groupings for data columns; options include: avg, cor, count, cov, dev, first, last, max, med, min, prd, scov, sdev, sum, svar, var, wavg and wsum

Column grouping order can be re-organized using drag-and-drop

Add join

Join an additional data source or function to an existing data source in your visual query. Options include left, inner, union, asof, upsert and plus.

Add function

Add a pre-configured Function into the visual query

Dashboards SDK

Visual Query Functions for Dashboards SDK are added to the demo.q file in the Sample directory.

Example:

api.mortgageRepaymentCalc:{[res;years]
  months: years * 12;
  // rate: (interestRate % 100);
  update Monthly_repayment: ceiling((Price * 1.06) % months) from res(0);

where years is an adjustable view state parameter in the dashboard

Style, Format, Margins

Style for common style settings