Managing Queries

The Aunsight data lake provides a powerful and scalable means for storing large amounts of data from various sources, with or without schemas. Because most data sources are structured as tables of information as in traditional databases, the Aunsight query service allows users to query datasets the same way they might do with a traditional database management system (DBMS). Backing Aunsight's query service is the industry standard Apache Drill implementation of SQL. Apache Drill is fully compatible with the SQL 2003 standard, allowing existing queries to be run exactly as intended.

The query workspace in the Aunsight web interface allows users to store and manage queries, including their version histories. Queries stored within Aunsight can then be executed within workflows and scripts, and invoked from processes using the SDKs. This tutorial shows how to navigate the query workspace, view and manage queries, and how to create new queries. To actually begin writing queries, users will want to have some familiarity with Apache Drill SQL and basic database technology.

The Query Workspace

The Aunsight web interface provides a Query workspace to users who have the AU-QUERY:view-query permission available to them through a role or group they have in a given context.

To access this workspace, log in to the web interface and select the context you wish to work in through the context selector. From that context's dashboard, click the "Query" icon () in the palette on the right.

The workflows workspace is a standard list-based view of queries available in the present context. You can search () and sort () the list to find a query by clicking the appropriate icon at the top of the list. You can also create a new query by clicking the plus icon ().

The Query Editor

Clicking the plus icon () to create a new query will immediately open the query code editor, an assisted text editor with highlighting for Apache Drill SQL. This is the interface through which users edit the actual query code.

Above the text editor window where users can enter their Drill SQL queries there is a toolbar for editing and running scripts:

• 
  Displays information about the query dataset, a list of field IDs that can be inserted into the query, and a link to its record in the dataset workspace.

• 
  Opens a dataset selection dialog to easily insert a dataset ID into a query.

• 
  Displays the editor widget in fullscreen mode.

• 
  Saves the query SQL code.

• 
  Opens a dialog for selecting query run settings such as a task executor resource.

• 
  Opens a dialog for getting a download link to the results of the query.

• 
  Opens a dialog to define a new or existing dataset to save the query results.

• 
  Runs the query and displays the results in a table.

Viewing Queries

In addition to the actual query code, Aunsight queries contain metadata and a version history that can be viewed from the Query record details. To view the details of a query, simply sort or search the list panel in the query workspace and click on the name of the query you want to see.

Query metadata is displayed in two tabs, "Details" and "Versions." In addition there are action buttons that affect the entire query record as a whole.

Details tab

By default, Aunsight shows the details tab where basic metadata about the query is displayed. Important information contained on this page relates to the resource (compute backend) and records (dataset upon which the query operates). Users can edit the name, description, and resource fields as well by clicking the edit icon () in the upper right corner of this section.

Versions tab

The versions tab displays a history of query versions. Every time Aunsight saves the query code, a new query version is created, and this history is displayed in a table in the query versions tab.

Clicking on a row in this table will display the code for that version, which can be displayed either as raw code or highlighted to show differences between the selected version and the present one.

Action Buttons

The two action buttons on the right of the screen allow users to edit query code () or delete () the query record.

Warning

Query deletion is irreversible!

Note

Unlike with other Aunsight versioning tools, it is not possible to delete a single query version. Users can only delete the entire query record.