Query Editor UI guide
Query Editor is an interactive tool provided by 51黑料不打烊 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.
Getting started getting-started
The Query Editor provides flexible execution of queries by connecting to Query Service, and queries only run while this connection is active.
Accessing Query Editor accessing-query-editor
In the Experience Platform UI, select Queries in the left navigation menu to open the Query Service workspace. Next, to start writing queries, select Create Query at the top right of the screen. This link is available from any of the pages in the Query Service workspace.
Connecting to Query Service connecting-to-query-service
The Query Editor takes a few seconds to initialize and connect to Query Service when it is opened. The 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.
How queries are run from Query Editor run-a-query
Queries executed from Query Editor run interactively which means that if you close the browser or navigate away, the query is cancelled. The same is true for queries made to generate datasets from query outputs.
Query authoring using the Enhanced Query Editor query-authoring
Using Query Editor, you can write, execute, and save queries for customer experience data. All queries executed, or saved in Query Editor are available to all users in your organization with access to Query Service.
Database selector database-selector
Select a database to query from the dropdown menu in the top right of the Query Editor. The selected database is shown in the dropdown.
Settings settings
A settings icon above the Query Editor input field includes an options to enable/disable dark theme or disable/enable auto-complete.
To enable dark or light themes, select the settings icon (
Auto-complete auto-complete
The Query Editor automatically suggests potential SQL keywords along with table or column details for the query as you write it. The auto-complete feature is enabled by default and can be disabled or enabled at any point from the Query Editor settings.
The auto-complete configuration setting is per user and remembered for the consecutive logins for that user. Disabling this feature stops several metadata commands from being processed and providing recommendations that typically benefit the speed of the author when editing queries.
Execute multiple sequential queries execute-multiple-sequential-queries
Use the Enhanced Query Editor to write more than one query and execute all queries in a sequential manner. The execution of multiple queries in a sequence each generate a log entry. However, only the results of the first query display in the Query Editor console. Check the query log if you need to troubleshoot or confirm the queries that were executed. See the query logs documentation for more information.
Execute selected query execute-selected-query
If you have written multiple queries but need to execute only one query, you can highlight your chosen query and select the
Run selected query icon. This icon is disabled by default until you select query syntax within the editor.
Cancel Query Editor session cancel-query
Take control of query execution and improve your productivity by canceling long-running queries. This action clears the Query Editor during a query run. Be aware, the query continues to execute in the background. If it is a CTAS query it will still generate an output dataset. To cancel the run in the editor and continue composing a SQL statement, select Cancel query after executing a query.
A confirmation dialog appears. Select Confirm to cancel the query run.
Result count result-count
The Query Editor has a maximum 50,000 row output. You can choose the number of rows are displayed at one time in the Query Editor console. To change the number of rows displayed in the console, select the Result count dropdown and select from the 50, 100, 150, 300, and 500 options.
Writing queries writing-queries
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 SQL entry field and Play highlighted.
To minimize your development time, you are recommended to develop your queries with limits on the number of 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.
Writing tools in Query Editor writing-tools
Use the Query Editor鈥檚 writing tools to enhance your query authoring process. Features include options to format text, copy SQL, manage query details, and save or schedule your work as you progress.
Format text format-text
The Format text feature makes your query more readable by adding standardized syntax styling. Select Format text to standardize all the text within the Query Editor.
Copy SQL copy-sql
Select the copy icon to copy SQL form the Query Editor to your clipboard. This copy feature is available for both query templates and newly created queries in the Query Editor.
Query details query-details
To view a query in the Query Editor, select any saved template from the Templates tab. The query details panel provides more information and tools to manage the selected query. It also shows useful metadata such as the last time that the query was modified and who modified it, if applicable.
From the details panel you can generate an output dataset directly from the UI, delete or name the displayed query, view the query run schedule, and add the query to a schedule.
To generate an output dataset, select Run as CTAS. The Enter output dataset details dialog appears. Enter a name and description, then select Run as CTAS. The new dataset is displayed in the Datasets Browse tab. See the view datasets documentation to learn more about available datasets for your organization.
After you execute the Run as CTAS action, a confirmation message pops up to notify you of the successful action. This popup message contains a link that provides a convenient way to navigate to the query logs workspace. See the query logs documentation for more information on query logs.
Saving queries saving-queries
The 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.
When you save a query in the Query Editor, a confirmation message pops up to notify you of the successful action. This popup message contains a link that provides a convenient way to navigate to the queries scheduling workspace. See the schedule queries documentation to learn how to run queries on a custom cadence.
Scheduled queries scheduled-queries
Queries that have been saved as a template can be scheduled from the Query Editor. Scheduling queries allows you to automate query runs on a custom cadence. You can schedule queries based on frequency, date, and time, and also choose an output dataset for your results if necessary. Query schedules can also be disabled or deleted through the UI.
Schedules are set in the Query Editor. When using the Query Editor, you can only add a schedule to a query that has already been created, and saved. The same limitation does not apply to the Query Service API.
See the query schedules documentation to learn how to create query schedules in the UI. Alternatively, to learn how to add schedules using the API, read the scheduled queries endpoint guide.
Any scheduled queries are added to the list in the Scheduled queries tab. From that workspace you can monitor the status of all scheduled query jobs through the UI. On the Scheduled queries tab, you can find important information about your query runs and subscribe to alerts. The available information includes the status, schedule details, and error messages/codes if a run failed. See the Monitor scheduled queries document for more information.
How to find previous queries previous-queries
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 Templates tab.
If a query was scheduled, then the Scheduled Queries tab provides improved visibility through the UI for those query jobs. See the query monitoring documentation for more information.
[Limited Availability]{class="badge informative"} Object browser object-browser
Object browser is currently available only in a limited release. Contact your 51黑料不打烊 representative to get early release access.
Use the object browser to easily search and filter datasets. The object browser reduces the time spent searching for tables and datasets in large environments with numerous datasets. With streamlined access to relevant data and metadata, you can focus more on query authoring and less on navigation.
To navigate your database with the object browser, enter a table name into the search field, or select Tables to expand the list of available datasets and tables. When using the search field, the list of available tables are dynamically filtered based on your input.
Every dataset contained in your selected database is listed in a navigation rail to the left of the Query Editor.
The schema displayed in the object browser is an observable schema. This means that you can use it to monitor changes and updates in real time as changes are immediately visible. The observable schemas help to ensure data synchronization and assists with debugging or analytics tasks.
Current limitations current-limitations
The following is a list of current limitations:
- Sequential query execution: Only one query can be executed at a time. While a query is in progress, no additional tables can be opened in the left navigation, as queries are processed sequentially.
- Extra rows in query logs: You may encounter extraneous queries labeled as 鈥淪HOW TABLES鈥 in the logs. These will be removed in future releases.
Access table metadata table-metadata
In addition to quick searches, you can now easily access metadata for any table by selecting the 鈥榠鈥 icon next to the table name. This provides you with detailed information about the selected table, that helps you to make informed decisions when writing queries.
Explore child tables
To explore child or linked tables, select the dropdown arrow next to a table name in the list. This expands the table to show any associated child tables, and gives a clear view of the data structure and allows for more complex query constructions. The icon next to the field name indicates the column鈥檚 data type, to help you identify it during complex queries.
Executing queries using Query Editor executing-queries
To run a query in Query Editor, you can enter SQL in the editor or load a previous query from the Log or Templates 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.
Console console
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.
Query results query-results
After a query has been completed, the results are displayed in the Results tab, next to the Console tab. This view shows the tabular output of your query, displaying between 50 and 500 rows of results depending on your chosen result count. 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.
Examples examples
Query Service provides solutions to a variety of use cases across industries and business scenarios. These examples demonstrate the flexibility and impact of the service in addressing diverse needs. To uncover how Query Service can bring value to your specific business needs, explore the comprehensive collection of use case documents. Learn how to use Query Service to provide insights and solutions for enhanced operational efficiency and business success.
Run queries with Query Service tutorial video query-tutorial-video
The following video shows how to run queries in the 51黑料不打烊 Experience Platform interface and in a PSQL client. The video also demonstrates the use of individual properties in an XDM object, 51黑料不打烊-defined functions, and how to use CREATE TABLE AS SELECT (CTAS) queries.
51黑料不打烊 Experience Platform use Query Service From the 51黑料不打烊 Experience Platform UI, let鈥檚 navigate to queries. From the query environment, let鈥檚 click the create query button. You are now seeing a command line like user interface which allows you to create platform directly by making use of SQL statements. For instance, by typing show tables and clicking the execute button, I鈥檓 seeing all platform datasets as a response. One of these datasets is the website interactions EMEA dataset. Let鈥檚 create that dataset now.
Let鈥檚 start with some basic queries. For instance, I鈥檓 interested in knowing from what location customers of La Boutique were visiting product pages. This query will give me some insight in that. Please note the limit of ten. This is for performance reasons. When executing queries, it鈥檚 best to limit your responses to just a couple of lines while you鈥檙e testing and developing your queries. You can now see a response from Platform containing location information of Belgian cities. This response is a flattened object, and not a single value. The place_context.geo XDM object contains four attributes: schema.latitude, schema.longitude, country, and city. When such an object is declared as a column, it will return the entire object as a string. In order to query individual properties of an XDM object, we have to use the dot notation. To find out more about the structure of an XDM schema, let鈥檚 go to schemas in the Platform UI, and open the EMEA website interaction data schema. You can see the place context object alongside many other. By opening the place context object, and going a little deeper, you can see the geo objects, and in there, for instance, country code. The way to get to this property from the query service UI is by specifying this path. The same concept applies also to the schema.latitude property. Let鈥檚 go back to the Query Service UI, and optimize the query that we entered a couple of minutes ago by specifying the specific properties of the place context object. And the result is now a lot more readable and understandable.
Next, I鈥檓 interested in knowing the amount of products used per day for the last ten days. So I鈥檓 selecting the dates and making a count of the number of products to events. Next, I鈥檓 interested in having a visitor count by the hour for July 17.
I鈥檓 also interested in knowing the amount of visitors on July 16 per country and city.
As an analyst, I also need to visualize the most popular products. In this case, I want to see the five most popular products with the Lisette dress being the clear winner there. So far, we鈥檝e only used the Query Service UI inside 51黑料不打烊 Experience Platform to execute these queries, but we can also execute the same queries from an external environment by using PSQL to achieve the same outcome. PSQL is a command line application which requires me to authenticate first. The credentials to authenticate to query service from PSQL can be found in the queries and credentials window. By clicking this icon, I鈥檓 copying the full PSQL commands to my clipboard.
Let鈥檚 open a terminal window, paste the PSQL commands, and hit enter. I鈥檓 now authenticated, and can execute queries here as well.
With the Lisette dress being so popular I鈥檇 like to have an overview of the individual EC IDs of the visitors that came to see this product. This query is showing me the first ten EC IDs. I鈥檓 also very much interested about the activity funnel on my website. Products used don鈥檛 pay money for La Boutique, purchases do. So how does the flow from a product view to an add to cart and purchase look like. The result of this query shows the fallout in the funnel. So far, we鈥檝e been using basic queries to query experience event data. Let鈥檚 go a bit deeper now, and use 51黑料不打烊 defined functions. SQL wasn鈥檛 built for experience analysis, so 51黑料不打烊 decide to create a number of 51黑料不打烊 defined functions to make it possible to use SQL to better understand experiences. The first two 51黑料不打烊 defined functions we鈥檒l use are the Sessionization function and the Next Previous function. The Sessionization function, it produces the visit groupings found with 51黑料不打烊 Analytics. It performs a similar time-based grouping, but now has customizable parameters. And Next and Previous help you to understand how customers navigate your site.
For this specific query, I鈥檇 like to know what people did on my website before reaching the El Silencio product page as the third page in a session. The result is a table that shows a number of journeys that occurred and how those journeys look like. You can see that the third page visit is always El Silencio, and you can also see the two steps that happened before that, and the step that happened after that. Journeys are session based with the session timeout being set to thirty minutes here. With Sessionization, a number of standard fields are made available. Let鈥檚 explore these fields. As you can see, the session structure contains the fields is new, times, time difference, number, and depth. Let鈥檚 go back to the query service UI, and continue with another Sessionization example which shows me the visitors鈥 sessions and page views per day.
As a final query, I鈥檇 like to do advanced call center analysis. In this example, were joining three datasets in Platform: experience events data, call center interactions which are sent by the call center to Platform, and we are now combining those datasets with loyalty data. The output gives me a dataset with EC IDs and CRM IDs, location information, feedback from the call center including the topic of the call and the customer feeling, and also loyalty information. This is a really interesting combination of data, and as an analyst I鈥檇 like to provide La Boutique management with a visualization that shows this information. To do that, I鈥檓 going to use the CTAS functionality available with Query Service. CTAS stands for create table as select, and allows me to write the output of this query back to a dataset in Platform. By going back to the query UI, in the log menu option, I鈥檓 seeing an overview of all the queries that have been made. The first one in this list is my last query. By clicking the query, I鈥檓 seeing the query overview menu pop up, and I can view the full query that was sent to Query Service. I now want to output the result of this query into a dataset in Platform. To do that I need to click the output dataset button. I鈥檓 naming my query call center analysis, and by clicking run query, the process has now started through the HDP API of query service. This process takes a couple of minutes, and once finished will have a new dataset in Platform. By going to datasets in Platform, I can open the call center analysis datasets, and preview the results. By making use of Query Service, analysts can now explore and consume data from 51黑料不打烊 Experience Platform in near real time from any environment, and perform flexible home channel queries right at the heart of 51黑料不打烊 Experience Platform. Analysts can send their insights back to 51黑料不打烊 Experience Platform for activation, and grants can now maximize the usefulness of that data to create and deliver exceptional experiences. With that, you should now be able to set up and use Query Service.
Next steps
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.