Query
Once an assembly and the associated data pipelines are running, the next step is to query its data with Query. Query can be selected from the document bar [+] or by clicking from the icon menu or Overview page.
The Query window has two input sectons:
Data
Data written to a database is accessed in the Data window.
There are three methods to query data from a database; Query table, SQL
or kdb+/q
. For new users unfamiliar with SQL
or kdb+/q
we recommend using the query tab as no coding experience is required.
In each case, an Output Variable is defined. It's this Output Variable which is used in the scratchpad to work with your data and is valid only for the current session (the Table name generated by the pipeline is what persists).
The query tab is the simplest way to explore your data. Available data tables are listed in the dropdown for Table Name.
Next, you need to define the Output Variable; this will be auto-assigned when selecting your first data table but may need to be changed if you decide to use a different data set. The Output Variable is used for queries in the scratchpad.
Mandatory is defining the Start Date and End Date filters, and this range will be dependent on the data used; for example, the crime
data is for March 31st 2022.
Additional filters are available, but none of these are required for use in Free Trial.
If you are familiar with SQL
you can use a simple query to return data.
SELECT * FROM crime
An Output Variable has to be defined for use in queries for the scratchpad.
For q the database and mount you wish to target must first be selected. Choose insights-demo
from the assembly dropdown on the top left hand side and choose which instance (or mount) i.e. rdb, idb or hdb that the data is stored in.
Note
The Free Trial 'insights-demo' stores the data in the mounts based on the value of the timestamp associated with the Partition Column property in the database schema.
- rdb: less than 10 minutes old
- idb: today
- hdb: older than today
If you are familiar with kdb+/q
you can use a simple query to return data.
select from crime
An Output Variable has to be defined for use in queries for the scratchpad.
Scratchpad
Scratchpad is where data returned from a database and outputted to an Output Variable can be manipulated.
Free Trial users are not required to use the Scratchpad to view their data. Scratchpad queries must reference the Output Variable, and not the database table name, although for Free Trial, the database table and the Output Variable name are the same (and this is okay to do).
Examples:
-
Focus on just crimes in the
crime
data set that were committed in the Bronx, we could include akdb+/q
query:select from crime where borough like "BRONX"
-
Convert air temperature in the
weather
data set from fahrenheit to celsius, add the followingq
orpython
:update airtempcelcius:(airtemp-32)%1.8 from w
import pykx as kx w = kx.q['w'] df = w.pd() df['airtempcelcius'] = (df['airtemp'] -32 )/ 1.8
In the output, there will be a new column called
airtempcelcius
!
I want to learn more about data exploration
Results
The bottom panel on the Query window displays the results of Get Data
and Run Scratchpad
requests.
It contains three tabs:
Note
When switching between tabs you need to re-run the Get Data
or Run Scratchpad
request to populate the tab.