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.
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.
Queries are executed using standard SQL (e.g., Select * From table_name). 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.
- When you have duplicate column names in your query, put the columns explicit in the select clause and use column aliases for columns with the same name.
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>.
- 1.Navigate to Projects > your_project > Base > Query.
- 2.Enter the query to execute using SQL.
- 3.Select »Run Query.
- 4.Select Save Query to add the query to your saved queries list.
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 Query: This will open the query again in the “New query” tab.
- Save Query: 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.
If you have run a query, you can run the query again by selecting it from the Query History.
- 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.
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. 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
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.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 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 24d ago