Skip to content

Query

The purpose of this walkthrough is to guide you through the steps to query stored data on an active databases.

This walkthrough makes reference to data ingested through pipelines created in the other walkthroughs and deployed to the insights-demo database. Therefore, before you can create the queries in this walkthrough you must ensure the insights-demo database is created, as described here.

You must also build the pipelines to ingest the data. Details on these are provided on this pages:

You can access the Query screen from the ribbon, shown below, or left-hand menu with [+], or by selecting 3. Query from the Overview page.

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

View Data

There is more than one method to query data from a database. We are going to start with the Query filter, shown below, as no coding experience is required for this.

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

Query is the simplest way to explore your data.

  1. Select one of the available tables; weather, crime, subway, health, taxi for the Table Name field.

  2. Define 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

  3. The Output Variable is auto-assigned for each of the guided walkthrough examples, but this can be changed if required.

  4. Click Get Data to return data.

If you want to query the data in the insight-demo database with `SQL, enter one of the sql queries from the following table.

  1. Type in the SQL below, replacing tablename with that table you wish to query.

    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

  2. Define an Output Variable before getting data.

    How to run a SQL query with Get Data.

  3. Click Get Data to return data.

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

    select count(*) from subway

You always need to define an Output Variable as this 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.

Scratchpad

You have the option to run additional data investigations in the Scratchpad with q or python. Scratchpad queries must reference the Output Variable defined in in the previous section. For this walkthrough it 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 to return data.

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

Note

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

Console, Table, Visuals

Once you have run queries either by clicking Get Data or Run Scratchpad the results are returned to lower portion of the screen in three tabs. Right-click inside the console window to clear results. For more information on each of these tabs see:

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.

Next Steps

  • Go to View for details on how to visualize data.

Further Reading

To learn more about specific topics mentioned in this page please see the following links: