Skip to content

Query

Query stored data on active databases. Use weather, crime, subway and health data deployed to the insights-demo database for querying.

Access the query tab from the ribbon menu with [+], or by clicking query icon from the left-hand icon menu or Overview page.

Open the query tab from the ribbon menu by clicking [+], from the left-hand icon menu or the Overview page.
Open the query tab from the ribbon menu by clicking [+], from the left-hand icon menu or the Overview page.

Go to View to visualize data

View Data

There are three methods to query data from a database: Query, SQL or kdb+/q. Start with the Query filter as no coding experience is required.

The query page has a filter option, a SQL and a kdb+/q query option.
The query page has a filter option, a SQL and a kdb+/q query option.

Define an Output Variable. The Output Variable is used for querying in the scratchpad and is valid for the current session.

Reserved keywords for output variable names

Avoid naming pipelines with reserved keywords for SQL or q ; e.g. table, as this results in an error on the query.

Query is the simplest way to explore your data. Select from one of the available data tables; note, the selected table must be from an activated pipeline. An Output Variable is auto-assigned for each of the guided walkthrough examples, but this can be changed if required.

Mandatory is defining the Start Date and End Date filters; see the table for guided walkthrough examples. Typically, the pipeline deployment date is the Start Date and End Date; for example, if you deployed a sample pipeline today, then the start and end date would be from midnight to 23:59 of today's date. The exceptions are the crime and weather data sets; see the table below.

data table name output variable start date end date
weather weather weather 2022-07-28T00:00:00.000000000 2022-07-28T23:59:00.000000000
crime crime crime 2022-03-31T00:00:00.000000000 2022-03-31T23:59:00.000000000
subway subway subway deployment date T00:00:00.000000000 deployment date T23:59:00.000000000
health health health deployment date T00:00:00.000000000 deployment date T23:59:00.000000000

How to run a filter query with Get Data.
How to run a filter query with Get Data.

I want to learn more about query filters.

Query data on the database with SQL.

select * from tablename
data SQL query
weather select * from weather
crime select * from crime
subway select * from subway
health select * from health

Define an Output Variable before getting data.

How to run a SQL query with Get Data.
How to run a SQL query with Get Data.

To see the number of events generated in the subway pipeline:

select count(*) from subway

I want to learn more about SQL queries.

Query data on the databae with q. First select the insights-demo database, then the mount hosting the data; for guided walkthrough, the mount will always be hdb.

select from tablename
data q query
weather select from weather
crime select from crime
subway select from subway
health select from health

Define an Output Variable before getting data.

Note

'insights-demo' stores data in mounts based on the timestamp of the partition column property of the schema.

  • rdb: less than 10 minutes old
  • idb: today
  • hdb: older than today

How to run a q query with Get Data
How to run a q query with Get Data.

I want to learn more about q queries.

Click get data button to return data.

Note

"There is no requirement to use scratchpad to view data. Scratchpad is for ad hoc analysis of the data and is done after Get Data is run."

Scratchpad

Run additional data investigations in scratchpad with q or python. Scratchpad queries must reference the Output Variable defined in Get Data- which for guided walkthrough is also the table name. For example, to focus on crimes committed in the Bronx, use the kdb+/q query:

select from crime where borough like "BRONX"

A scratchpad q query selecting crime data from the Bronx.

Click run scratchpad button to return data.

If using python, only the first line of code is processed.

I want to learn more about data exploration

Console, Table, Visuals

By default, all Get Data and Run Scratchpad requests are returned to the console. Right-click inside the console window to clear results. For more information on each of the options:

Note

The console shows results of the most recent query irrespective of the selected tab. When switching between tabs, re-run Get Data or Run Scratchpad to repopulate results for the selected tab.