Links

Query

Queries can be used for data mining. On the Query page:
  • New queries can be created and executed
  • Already executed queries can be found in the query history
  • Saved queries and query templates are listed under the saved queries tab.

New Query

Available tables

All available tables and their details are listed on the New Query tab. There are 3 types of tables that can be used for querying:
  • Created tables: Tables created in the project by a user.
  • Metadata tables: Contains metadata from samples that are linked to a table in Base. It is created by syncing it with the Base module. This synchronization is configured on the Details page within the project.
  • Public tables: public databases that are made available within Base by Illumina

Create new query

Input

Queries are executed using standard SQL (e.g., Select * From table). While running the query, errors are checked. If errors are captured, this is represented above the input box. The query can be immediately executed or saved for future use.

Result

If the query is valid for execution, the result will be shown as a table underneath the input box. From within the result page of the query, it is possible to save the result in two ways:
  • Download: As Excel or JSON file to the computer.
  • Export: As new table, as a view or as file to the project in CSV, JSON or AVRO format.

Run a New Query

  1. 1.
    Select a project.
  2. 2.
    From the project menu, select Query.
  3. 3.
    Enter the query to execute using SQL.
  4. 4.
    Select Run Query.
  5. 5.
    Select Save Query to add the query to your saved queries list.

Query history

The query history lists all queries that were executed. Historical queries are shown with their date, executing user, returned rows and duration of the run. For each historical query listed, it is possible to:
  • Open: This will open the query again in the “New query” tab.
  • Save: This will save the query, so it will be visible in the “Saved queries” tab.
  • View results: The results of an executed query are available for approximately 24 hours. To see the results after that time period, the query needs to be re-executed.

Run a Previous Query

If you have run a query, you can run the query again by selecting it from the Query History.
  1. 1.
    Select a project.
  2. 2.
    From the project menu, select Query.
  3. 3.
    Select the Query History tab.
  4. 4.
    Select a query.
  5. 5.
    Perform one of the following actions:
    • Open Query—Open the query in the New Query tab. You can then select Run Query to execute the query again.
    • Save Query—Save the query to the saved queries list.
    • View Results—Download the results from a query or export results to a new table, view, or file in the project. Results are available for 24 hours after the query is executed. To view results after 24 hours, you need to execute the query again.

Saved queries

All queries saved within the project are listed under the “Saved Queries” tab together with the query templates.
The saved queries can be:
  • Opened: This will open the query again in the “New query” tab.
  • Saved as template: The saved query becomes a query template.
  • Deleted: The query is removed from the list and cannot be opened again.
The query templates can be:
  • Opened: This will open the query again in the “New query” tab.
  • Deleted: The query is removed from the list and cannot be opened again.
It is possible to edit the saved queries and templates by double-clicking on each query or template. The data classification of the templates can also be changed as follows:
  • Account: The query template will be available for everyone within the account
  • User: The query template will be available for the user who created it

Run a Saved Query

If you have saved a query, you can run the query again by selecting it from the list of saved queries.
  1. 1.
    Select a project.
  2. 2.
    From the project menu, select Query.
  3. 3.
    Select the Saved Query tab.
  4. 4.
    Select a query.
  5. 5.
    Perform one of the following actions:
    • Open Query—Open the query in the New Query tab. You can edit the query, and then select Run Query to execute the query again.
    • Save as template—Save the query as a template for future queries.
    • Delete Query—Remove the query from the saved queries list.

Shared Database for Project

Shared databases are displayed under the list of Tables as `Shared Database for project <project name>.
For ICA Cohorts Customers, shared databases are available in a project Base instance. For more information on specific Cohorts shared database tables that are viewable, See Cohorts Base.
Last modified 1d ago