Query Management

With workspace setup, datasource connections, and alias mapping complete, queries can be executed. It can be run and saved by Workspace Admins and Analysts. ShardOne also provides a feature to download the results for the user roles mentioned above. Check out this to know more about user roles.

How to run queries?

For query execution, navigate to the Query Composer located within the Query Tab of the workspace. The Query Composer serves as an SQL editor where SQL queries can be entered. To initiate query execution and retrieve results, simply press the ‘Run’ button.

query

There are two methods to refer to a table in ShardOne:

Using Datasource & Item Name
To query using this method, refer to the item inside the datasource with the dot (.) operator.
General syntax:
```
 SELECT <attributes> FROM <Datasource Name>.<Item Name>
```
In the image shown below, the datasource name is ‘Cleveland_Clinic_Florida’ and an item in it ‘dbo.patients’.
So according to the syntax supported in ShardOne, a sample query would be
```
SELECT id, first_name, last_name FROM Cleveland_Clinic_Florida.dbo.patients
```
Using Alias
To run a query using an alias, refer to that in the query directly without any dot (.) operator.
General Syntax:
```
 SELECT <attributes> FROM <Entity Alias>
```
In the image shown below, for the item ‘dbo.patients’ an item alias ‘patients’ is mapped.
A sample query built with the syntax would look like
```
SELECT id, patientFirstName, patientLastName FROM patients
```

For more details about building a query and SQL support in ShardOne, refer to this.

As shown in the images above, the results are shown below the Query Composer. Similarly, in case the query fails the error due to which it failed will also be shown in the same section as the results.

query

Query History

The history of queries run in the platform can be viewed and saved and the results of the query could be downloaded by the user. The users can also expand and view the complete query by clicking on the plus (+) icon.

On clicking the id of the query, the user will be redirected to the Query tab where they view the results for completed ones and errors for the failed ones. It is also possible for the user to re-run the query by clicking on the ‘Run’ button if required.

query

The query history can be searched by name & query, sorted by name, status, submitted on, and execution time. The Query History can be filtered by the

Submission time - Start date and End date
Status - There are multiple query statuses/ stages for each query.
- Submitted - The query has been submitted
- Queued - After submission of the query, it will be queued along with the other queries for processing.
- In Progress - The query is running.
- Completed - The query is completed and the results are obtained.
- Completed with Partial Results - The query is completed but the results are partial as the results from all the databases were not obtained.
- Stopped - The query is killed or stopped by a user.
- Failed - The query failed due to an error.
Submitted by - User who submitted the query

How to Download Query Results?

A query can be downloaded if and only if it is completed. To download a query, click on the ‘Download’ icon in the actions column.

query

On clicking this, a CSV file will be downloaded with the results of that query in it.

How to Kill/Stop a Query?

Users can stop/kill a query if required or if the query is taking longer than expected. This action will be possible only if the query has the status ‘In Progress’ i.e. the query is running.

To kill/stop a query, open the Query History tab and click on the Kill button.
A pop-up will appear to confirm the process. Click on Kill to complete the action. The query will be stopped or killed after a few seconds.

How to Save a Query?

Users can Save a query and manage Saved Queries.

To save a new query, click on the ‘Save as’ button.
Fill in the Query Name and Description to save the Query.
Users can also save an existing query by clicking on the ‘Save Query’ icon from Query History.
Upon clicking the icon, a pop-up open up where a Query Name and a suitable Description can be typed in. Subsequently, hit the ‘Save’ button.