Query

Query is for interacting with data on the database. You can query data with a set of filters, q or SQL code. Results are passed to an output variable you can ad-hoc query with q or python in a scratchpad. Data is presented in a console, table or visual chart.

Query your data with filters, kdb+/q, or sql and run ad hoc investigations in the scratchpad with q/sql or python.

Use the central divider to resize the viewable area between query and outputs

Get Started

Step 1: Check your database query settings

Before you start, you need to ensure your data can be queried on the database. Under Query Settings of your database check you have at least one Production Read Replicas and Exploratory Read Replicas.

Ensure your database has one Production and Exploratory Read Replica.

Step 2: Have an active database

Deploy the database with the data you want to query; a successfully deployed database shows a green tick when active (viewable in the left-hand menu).

A rollover of an active database shows a series of green ticks for each successfully deployed component of the database.

Step 3. Choose how to query the data.

You can query an active database through one of three methods: a filter query, q, or SQL:

=== "Filter query"

    A filter query is a non-coding method of querying your data. Select a **Table Name** from the list of active databases. Then define the **Start Time** and **End Time** range - this is from the `timestamp` column used to [partition](schema.md) your data.

    :fontawesome-solid-hand-point-right:[Additional details on available filters can be found in the GetData API](https://code.kx.com/insights/microservices/data-access/getData.html)

    ![When querying with a table, it's necessary to reference the table as used by the database, the output variable, and the start and end time; both of which must be timestamps.](img/get-data-form.jpg)<br><span style= "font-size:0.8em">_When querying with a table, it's necessary to reference the name of the table as used by the database, the output variable, and the start and end time; both of which must be timestamps.*</span>

    **Filtering by Label**

    In the absence of a label, a query is run against all databases.  You can target particular databses by including a filter for its assigned `kxname` or custom label.

=== "SQL"

    `SQL` is a standardized programming language for managing relational databases and is supported by kdb Insights Enterprise.

    ![A simple `SQL` query returns data to an output variable.](img/get-data-sql.jpg)<br><span style= "font-size:0.8em">_A simple `SQL` query returns data to an output variable._</span>

=== "Q"

    `q` is the core programming language of kdb Insights Enterprise.

    ![A simple `q` query returns data to an output variable.](img/get-data-q.jpg)<br><span style= "font-size:0.8em">_A simple `q` query returns data to an output variable._</span>

Step 4. Get Data

Click Get Data Button to return data to the Console.

Get data from the database with a SQL query.

Step 5. Use the Scratchpad

Ad hoc queries are run against the output variable with kdb+/q or python. If using python, only the first line of code is processed. Standalone code can also be run in the scratchpad.

Run Scratchpad Button , or the keyboard shortcut of CTRL + Enter, or ⌘Enter on Mac to execute code.

Console populated by a scratchpad query run against an output variable, 't', defined by Get Data

Running Queries

Running a selected line of code context is set by preceding lines, similar to \d .myContext or system "d .myContext". The global context is used in the absence of a preceding line.

Firefox Users

If you are unable to scroll in the scratchpad if using Firefox, check scrollbars are configured to be always visible.

Scratchpad Limitations

There is a (combined user) memory limit of 500MB for scratchpad queries. When exceeded, the scratchpad process shuts down and restarts. To optimize performance, limit the amount of data returned in your query; for example: select[10000] from myTable to return 10,000 rows of data.

After a scratchpad restart, earlier results are removed and are no longer available.

Stream Processor

Scratchpad uses the Stream Processor (SP) to run pipelines. A scratchpad pipeline does not have to be deployed as for pipelines built with import or pipeline templates.

!!! note "An expression pipeline built in scratchpad"

    This is how a sample stream processor expression pipeline looks in scratchpad:

    ```q
    pipelineA: .qsp.read.fromExpr["til 10"] .qsp.write.toVariable[`outputA];
    pipelineB: .qsp.read.fromExpr["`a`b`c"] .qsp.write.toVariable[`outputB];
    .qsp.run (pipelineA; pipelineB)
    ```

Running pipeline(s) can be torn down in the scratchpad with:

.qsp.teardown[]

Stream Processor pipelines

Pipeline(s) run in the scratchpad are not listed under Pipelines on the Overview page. Pipelines initiated in the scratchpad must be managed in the scratchpad.

Starting a pipeline with Stream Processor

Step 6 View Data

Queried data is returned to the Console, Table or Visual

Console populated with data following an ad hoc scratchpad query.