Skip to content

Query

Once an database 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 queryicon from the icon menu or Overview page.

Access query from the ribbon menu.
Access query from the ribbon menu.

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.

QUERY

I want to learn more about query filters.

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.

SQL

For q the database and mount you wish to target must first be selected. Choose insights-demo from the Database 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.

HDB

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:

  1. Focus on just crimes in the cirme data set that were committed in the Bronx, we could include a kdb+/q query:

    select from crime where borough like "BRONX"

    scratchpad

  2. Convert air temperature in the weather data set from fahrenheit to celsius, add the following q or python:

    q update airtempcelcius:(airtemp-32)%1.8 from w

    python 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.