Query
Query stored data on active databases. Use weather, crime, subway and health data deployed to the insights-demo database for querying. Free Trial also has taxi data preloaded for use.
Access the query tab from the ribbon or left-hand menu with [+], or 3. Query from the Overview page.
![Open the query tab from the left-hand or ribbon menu by clicking [+], or from the Overview page.](img/querymenu.jpg)
Open the query tab from the left-hand or ribbon menu by clicking [+], or from 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.
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 |
| taxi | trips | trips | 2021-12-31T00:00:00.000000000 | 2021-12-31T23:59:00.000000000 |
How to run a filter query with Get Data.
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 |
| taxi | select * from trips |
Define an Output Variable before getting 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
Click 
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"
Click 
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.