Query Editor is an interactive tool provided by Adobe Experience Platform Query Service, which allows you to write, validate, and run queries for customer experience data within the Experience Platform user interface. Query Editor supports developing queries for analysis and data exploration, and allows you to run interactive queries for development purposes as well as non-interactive queries to populate datasets in Experience Platform.
For more information about the concepts and features of Query Service, see the Query Service overview. To learn more about how to navigate the Query Service user interface on Platform, see the Query Service UI overview.
Query Editor provides flexible execution of queries by connecting to Query Service, and queries will only run while this connection is active.
Query Editor takes a few seconds to initialize and connect to Query Service when it is opened. Console tells you when it is connected, as shown below. If you attempt to run a query before the editor has connected, it delays execution until the connection is complete.
Queries executed from Query Editor run interactively. This means that if you close the browser or navigate away, the query is canceled. This is also true for queries made to generate datasets from query outputs.
Using Query Editor, you can write, execute, and save queries for customer experience data. All queries executed in Query Editor, or saved, are available to all users in your organization with access to Query Service.
In the Experience Platform UI, select Queries in the left navigation menu to open the Query Service workspace. Next, select Create Query at the top right of the screen to start writing queries. This link is available from any of the pages in the Query Service workspace.
Query Editor is organized to make writing queries as easy as possible. The screenshot below shows how the editor appears in the UI, with the Play button and SQL entry field highlighted.
To minimize your development time, it is recommended that you develop your queries with limits on the rows returned. For example,
SELECT fields FROM table WHERE conditions LIMIT number_of_rows. After you have verified that your query produces the expected output, remove the limits and run the query with
CREATE TABLE tablename AS SELECT to generate a dataset with the output.
SELECTfrom, then use the arrow keys to navigate to the table you are looking for, and press Enter. Once a table is selected, autocomplete will recognize fields in that table.
Query Editor automatically validates a query as you write it, providing generic SQL validation and specific execution validation. If a red underline appears below the query (as shown in the image below), it represents an error within the query.
When errors are detected, you can view the specific error messages by hovering over the SQL code.
While you are viewing a query in Query Editor, the Query Details panel provides tools to manage the selected query.
This panel allows you to generate an output dataset directly from the UI, delete or name the displayed query, and add a schedule to the query.
This panel also shows useful metadata such as the last time the query was modified and who modified it, if applicable. To generate a dataset, select Output Dataset. The Output Dataset dialog appears. Enter a name and description, then select Run Query. The new dataset is displayed in the Datasets tab on the Query Service user interface on Platform.
You can only add a schedule to a query that has already been created, saved, and run. Additionally, you will not be able to add a schedule to a parameterized query.
To add a schedule to a query, select Add schedule.
The Schedule details page appears. On this page, you can choose the frequency of the scheduled query, the dates the scheduled query will run, as well as what dataset to export the query to.
You can choose the following options for Frequency:
For the dataset, you have the option to use either an existing dataset or create a new dataset.
Since you are using either an existing or creating a new dataset, you do not need to include either
INSERT INTO or
CREATE TABLE AS SELECT as part of the query, since the datasets are already set. Including either
INSERT INTO or
CREATE TABLE AS SELECT as part of your scheduled queries will result in an error.
After confirming all these details, select Save to create a schedule.
The query details page re-appears, and now showing the details of the newly created schedule, including the schedule ID, the schedule itself, and the schedule’s output dataset. You can use the schedule ID to look up more information about the runs of the scheduled query itself. To learn more, please read the scheduled query run endpoints guide.
You can only schedule one query template using the UI. If you want to add additional schedules to a query template, you will need to use the API. If a schedule has already been added using the API, you will not be add additional schedules using the UI. If multiple schedules are already attached to a query template, only the oldest schedule will be displayed. To learn how to add schedules using the API, please read the scheduled queries endpoint guide.
Additionally, you should refresh the page if you want to ensure you have the latest state for the schedule you are viewing.
You can delete a schedule by selecting Delete a schedule.
If you want to delete a schedule for a query, you must disable the schedule first.
Query Editor provides a save function that allows you to save a query and work on it later. To save a query, select Save in the top right corner of Query Editor. Before a query can be saved, a name must be provided for the query using the Query Details panel.
All queries executed from Query Editor are captured in the Log table. You can use the search functionality in the Log tab to find query executions. Saved queries are listed in the Browse tab.
See the Query Service UI overview for more information.
Queries that are not executed are not saved by the Log. In order for the query to be available in Query Service, it must be run or saved in Query Editor.
To run a query in Query Editor, you can enter SQL in the editor or load a previous query from the Log or Browse tab, and select Play. The status of query execution is displayed in the Console tab below, and output data is shown in the Results tab.
The console provides information on the status and operation of Query Service. The console displays the connection status to Query Service, query operations being executed, and any error messages that result from those queries.
The console only shows errors that resulted from executing a query. It does not show query validation errors before a query is executed.
After a query has completed, the results are displayed in the Results tab, next to the Console tab. This view shows the tabular output of your query, displaying up to 100 rows. This view allows you to verify that your query produces the expected output. To generate a dataset with your query, remove limits on rows returned, and run the query with
CREATE TABLE tablename AS SELECT to generate a dataset with the output. See the generating datasets tutorial for instructions on how to generate a dataset from query results in Query Editor.
The following video shows how to run queries in the Adobe Experience Platform interface and in a PSQL client. Additionally, using individual properties in an XDM object, using Adobe-defined functions, and using CREATE TABLE AS SELECT (CTAS) are demonstrated.
Now that you know what features are available in Query Editor and how to navigate the application, you can start authoring your own queries directly in Platform. For more information about running SQL queries against datasets in Data Lake, see the guide on running queries.