The features and use of the Query Editor in the Kentik Detect portal is discussed in the following topics:
The Query Editor page is provided as a place to experiment with and refine SQL queries to your data stored in the tables of the Kentik Data Engine (see KDE Overview), which may be made either in a PostgreSQL client or via the Query SQL Method of the Query API.
When a valid query is submitted from this page to the Kentik Data Engine (KDE) the results are returned as a table. By editing the submitted SQL you can see how the queries work and how the returned results vary depending on the clauses included and how the statements are specified.
Note: The tables returned in the Query Results table are raw data from KDE, without the additional processing that we apply to present data elsewhere in the portal (e.g. in the graphs and tables displayed in Data Explorer, Dashboards, etc.). For that reason these tables are not directly comparable to other portal tables, even when the query that returns the data is the same. We provide the ability to open a Data Explorer query in the Query Editor simply so that you don’t have to start from scratch when creating SQL queries.
The Query Editor page can be accessed either directly or via Data Explorer:
- Access from Data Explorer:
- From the View Options menu at the top right of the Explorer Chart Display, choose View SQL.
- In the resulting submenu, choose from the SQL Query Options (Default, Total, or Historical).
- When the Query Editor page opens the Query Editor input field will be pre-populated with the underlying SQL for the query in Data Explorer.
- Access from NavBar:
- Choose Query Editor from the Data Explorer menu on the portal’s main navbar.
- The Query Editor page opens with an empty Query Editor (input field).
The SQL query option determines the query that populates the Query Editor (input field) when accessing the Query Editor page from Data Explorer. The options all return datapoints at each timeslice over a specified timespan (see Subqueries, Slices, and Shards), but they differ in what data is returned:
- Default: Returns datapoints for the keys (unique combinations of group-by dimensions) with the top values as measured in the specified metric.
- Total: Returns datapoints for the total (summed) value in the specified metric.
- Historical: Returns datapoints for the total (summed) value in the specified metric over a historical timespan (same duration as default timespan, but from a specified number of days earlier).
The Query Editor page includes the following UI elements:
- Query Editor: An input field in which to write/edit a SQL query:
- If coming from the Query Editor menu item on the portal navbar’s Data Explorer menu, the Query Editor will be empty.
- If coming from Data Explorer, the Query Editor is populated with the SQL of the query behind the current view.
- Format button: Indents the code in the Query Editor to reflect the nesting.
- Clear button: Clears all text from the Query Editor.
- Run Query button: Executes the query in the Query Editor and returns results to the Query Results field.
- Query Results: A table showing the data returned from the query in the Query Editor.
Using Query Editor starts with entering a query in the Query Editor. The query’s SQL may be entered in any of the following ways:
- Use SQL from the current query in Data Explorer, as described in Accessing Query Editor.
- Click in the Query Editor to write or paste SQL.
Once the Query Editor is populated with a query that query can be edited as text. Click the Run Query button to run the query and return the results in the Query Results field.
Note: In the Query Editor field, “$_kntq$” is used as a substitute for single quotes.