Query

Queries can be used for data mining. On the Projects > your_project > Base > 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.

Note that Metadata tables are created by syncing with the Base module. This synchronization is configured on the Details page within the project.

Input

Queries are executed using SQL (for example Select * From table_name). When there is a syntax issue with the query, the error will be displayed on the query screen when trying to run it. The query can be immediately executed or saved for future use.

Best practices and notes

Do not use queries such as ALTER TABLE to modify your table structure as it will go out of sync with the table definition and will result in processing errors.

• When you have duplicate column names in your query, put the columns explicitly in the select clause and use column aliases for columns with the same name.

• Case sensitive column names (such as the VARIANTS table) must be surrounded by double quotes. For example, select * from MY_TABLE where "PROJECT_NAME" = 'MyProject'.

• The syntax for ICA case-sensitive subfields is without quotes, for example select * from MY_TABLE where ica:Tenant = 'MyTenant' As these are case sensitive, the upper and lowercasing must be respected.

• If you want to query data from a table shared from another tenant (indicated in green), select the table to see the unique name. In the example below, the query will be select * from demo_alpha_8298.public.TestFiles

• For more information on queries, please also see the snowflake documentation: https://docs.snowflake.com/en/user-guide/

Querying data within columns.

Some tables contain columns with an array of values instead of a single value.

Querying data within an array

As of ICA version 2.27, there is a change in the use of capitals for ICA array fields. In previous versions, the data name within the array would start with a capital letter. As of 2.27, lowercase is used. For example ICA:Data_reference has become ICA:data_reference.

You can use the GET_IGNORE_CASE option to adapt existing queries when you have both data in the old syntax and new data in the lowercase syntax. The syntax is GET_IGNORE_CASE(Table_Name.Column_Name,'Array_field')

For example:

select ICA:Data_reference as MY_DATA_REFERENCE from TestTable becomes:

select GET_IGNORE_CASE(TESTTABLE.ICA,'Data_reference') as MY_DATA_REFERENCE from TestTable

You can also modify the data to have consistent capital usage by executing the query update YOUR_TABLE_NAME set ica = object_delete(object_insert(ica, 'data_name', ica:Data_name), 'Data_name') and repeating this process for all field names (Data_name, Data_reference, Execution_reference, Pipeline_name, Pipeline_reference, Sample_name, Sample_reference, Tenant_name and Tenant_reference).

Suppose you have a table called YOUR_TABLE_NAME consisting of three fields. The first is a name, the second is a code and the third field is an array of data called ArrayField:

NameField	CodeField	ArrayField
Name A	Code A	{ “userEmail”: “[e-mail]”, "bundleName": null, “boolean”: false }
Name B	Code B	{ “userEmail”: “[e-mail]”, "bundleName": "thisbundle", “boolean”: true }

Examples
You can use the name field and code field to do queries by running
Select * from YOUR_TABLE_NAME where NameField = "Name A".
If you want to show specific data like the email and bundle name from the array, this becomes
Select ArrayField:userEmail as User_Email, ArrayField:bundleName as Bundle_Name from YOUR_TABLE_NAME where NameField = "Name A".
If you want to use data in the array as your selection criteria, the expression becomes
Select ArrayField:userEmail as User_Email, ArrayField:bundleName as Bundle_Name from YOUR_TABLE_NAME where ArrayField:boolean = true.
If your criteria is text in the array, use the ' to delimit the text. For example:
Select ArrayField:userEmail as User_Email, ArrayField:bundleName as Bundle_Name from YOUR_TABLE_NAME where ArrayField:userEmail = '[e-mail]'.
You can also use the LIKE operator with the % wildcard if you do not know the exact content.
Select ArrayField:userEmail as User_Email, ArrayField:bundleName as Bundle_Name from YOUR_TABLE_NAME where ArrayField:userEmail LIKE '%A@server%'

Query results

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 a new table, as a view or as file to the project in CSV (Tab, Pipe or a custom delimeter is also allowed.) or JSON format. When exporting in JSON format, the result will be saved in a text file that contains a JSON object for each entry, similar to when exporting a table. The exported file can be located in the Data page under the folder named base_export_<user_supplied_name>_<auto generated unique id>.

Run a new query

1. Navigate to Projects > your_project > Base > Query.

2. Enter the query to execute using SQL.

3. Select »Run Query.

4. Optionally, select Save Query to add the query to your saved queries list.

If the query takes more than 30 seconds without returning a result, a message will be displayed to inform you the query is still in progress and the status can be consulted on Projects > your_project > Activity > Base Jobs. Once this Query is successfully completed, the results can be found in Projects > your_project > Base > Query > Query History tab.

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.

1. Navigate to Projects > your_project > Base > Query.

2. Select the Query History tab.

3. Select a query.

4. 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 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. Specifically for Query Templates, the data classification can be edited to be:

• 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. Navigate to Projects > your_project > Base > Query.

2. Select the Saved Queries tab.

3. Select a query.

4. Select Open Query to open the query in the New Query tab from where it can be edited if needed and run by selecting Run Query.

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.