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, note that the ChaosSearch database is preselected.

3. In the Schema field, select chaos to query the Refinery views. You could select information_schema to query the system tables for the columns, schemata, or tables (views) in the system. For this example, choose chaos to query a Refinery view.

4. The See Table Schema list updates to include all of the Refinery views that you can access.

5. Select the view of interest. The window automatically updates to run a SELECT preview query for a limit of 100 records of the selected view. The results appear in the lower right result set panel as shown below.

👍

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.

6. Note the tools in the preview area for the view; these tools include options to display the keys and index, to sort the columns in the view, to copy the SELECT statement used for the preview, and to delete the preview for that view. The option to copy the SELECT statement can be very helpful for more refined queries against the view.

7. For example, copy the preview SELECT statement and paste it into the run query area in the top right:

8. You can review the statement and edit it to change the query. This example reduces the number of columns to return and uses a WHERE clause to limit results to order prices that exceeded 200,000:

👍

SQL Analytics Query Timeout

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

9. To run a new SQL query, type the query in the upper right frame.

Note that 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 to the view name 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). The green and red LEDs indicate when the last query execution was successful or if the query failed. Failure messages appear in the lower right result set panel to provide more information about the problems. Each pane has a query history that you can use to review previous test runs.

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.

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