Run a SQL Query

After you create or have access to at least one Chaos Refinery view, you can start to explore that indexed data using SQL Analytics. A good starting point is to use the SQL Lab and run a query against a view, its columns, and data. The SQL Editor is a workspace that you can use to build, test, and run ad-hoc queries and save them for later use or as part of a chart definition.

The SQL Lab menu has three options:

• SQL Editor opens a window where you can select a Refinery view and create a SQL query to run against it.

• Saved Queries lists the queries that you have created and saved with a unique name so that you can load them to re-run or to update and run for data exploration.

• Query History lists all of the queries (saved, or ad-hoc) that you have run. You can review information about the successful and failed queries and their definitions, and quickly load one into the SQL Editor.

To run a SQL query:

1. Select SQL Analytics > SQL Lab > SQL Editor to open the SQL Editor window.

2. In the left menu, you can optionally use the See Table Schema list to display the list of views for more information. Select a view if you want to see its columns.

👍

ChaosSearch Progress Bar and Cancel Features

Note the query progress bar and status message at the top of the window, as well as the Cancel Query button and the volume of data scanned. These ChaosSearch features, which are part of the Search Analytics window, are also available in SQL Analytics.

3. To run a query, go to the top right query pane and type or paste the SQL query. As you type a query, the system will prompt with matching view names and also column names to aid in the query process. You can set a result limit using LIMIT syntax, and the UI also has a result LIMIT setting next to Run. The lowest limit value specified is used. When you have completed the query, click Run.

   The following example queries a view for the unique values of a column. As a good practice, do not query against an entire data set, but query in a time-range that typically represents an analysis window of interest, like the last day, last week, or a specific set of hour(s).

4. You can review, edit, and re-run the statement within the lab window.

👍

SQL Analytics Query Timeout

The current maximum query time is 20 minutes for SQL queries in Superset.

Some Refinery view names or column names with special characters require enclosure in double-quotation marks, otherwise Superset throws a database error (see FAQs for SQL Analytics and Superset). The schema_name prefix (such as chaos.viewname) is optional.

👍

Hint: Create SQL Editor tabbed panes for query tests.

Click the plus sign (+) icon in the navigation bar above the SQL Editor query window to open a new query editing pane where you can create and test a new query in its own space. The following screen shows several tabbed query panes (shown as Untitled Query n).

You can rename the tabs to specify more familiar labels. Note that the tabs persist only in your browser cache. If you clear browser cache you could lose the query tabs, and if you connect to ChaosSearch using a different browser application or an incognito/private browser, existing query tabs will not appear in that different browser session. If you have favorite queries that you plan to run frequently or use as a template, be sure to save those queries.

10. You can create a new tab and have multiple query windows available. You can also save the queries that you find most useful for later use, or for use within charts that you create for visualizations.

📘

Query Tab Status LEDs and Refresh

In a query tab, the green and red LEDs indicate whether the last execution was successful or if the query failed; a gray LED indicates that the query has not yet run, or not run since you opened the SQL editor in this browser session. Failure messages appear in the lower right result set panel to provide more information about any syntax or system issues. Each pane has a query history that you can use to review and re-run previous queries.

If you leave the SQL Editor window to go to another area of the console, the status LEDs and Results pane reset. Green LEDs reset to gray; clicking a query tab refreshes the tab's status. If the results from the last run are still in the backend storage, the LED turns green and the Results pane fetches the last results. The LED changes to red if the last results were cleared or if the query failed. Click Run to re-run the query and get the latest results or to obtain more information on errors.

Your saved queries can be accessed on the SQL Lab > Saved Queries page, and on the Home page as well.

🚧

Avoid Multiple Open Browser Windows for SQL Editor

For Superset SQL Editor work, it is best to run queries and operations from only one active web browser window.

If you open multiple browser windows as the same user to perform different querying or work in SQL Editor, the windows will be updated to reflect the latest work/actions taken in any of the open windows. If you run a query in one browser window, the query execution, history, and results will appear in all open SQL Editor windows. You could also accidentally cancel a long-running query from a different browser window than the one that launched the query.