Query Service UI

Transcript

Hi there, Adobe Experience Platform hinges data from a wide variety of sources. A major challenge for marketers is making sense of this data to gain insights about their customers. Adobe Experience Platform Query service facilitates that by allowing you to use standard SQL to query data in platform, using a user interface. In this video, let’s check out how to access, write and execute queries. You previously executed queries and accessed saved queries by other users within your IMS organization. Experience platform is API friendly, and you can also perform some of these operations in this video using platform API. From your experience platform homepage navigate your datasets under data management. For this video, we’re are using a fictional retail brand called luma. luma loyalty dataset contains information about customers, loyalty details, geographic details, etc. Luma web data contains web traffic information about customers interaction on the luma site, including products viewed by a customer, visited pages products purchased, etc. Now let’s explore how we can query the dataset using platform query service. From the left navigation bar, click on queries under data management. Let’s click on the create query button and you can see a query editor window open up. Query editor enables you to write and execute queries, without using an external client. Let’s create a query to display loyalty details of few luma customers. Query editor provides an editing space where you can begin typing a query. The editor automatically completes the SQL reserved words, tables, and field names within tables as you type. When finished writing your query, select the play button to run the query or use the keyboard shortcut Shift + Enter. The console tab below the editor shows, what query service is currently doing, indicating when a query has been returned. The result tab next to the console displays query results. If you need to run the same query often, as part of your day-to-day work, platform provides an option to save the query so you can run it without rewriting the query. To save a query, provide a name and then click on the save option. The browse tab shows queries saved by users in your organization. Now under the browse tab, you can notice your saved query. Select the saved query, and you can view its associated properties, including the query ID, author name and SQL query itself. You also have an option to delete the saved query, or output the query result to a dataset. To rerun the query click on the query name, and it opens the SQL query in a query editor window. You can keep editing a saved query and please make sure to save your changes. The true power of query service is relieved when queries are used to generate datasets in the data lake to be used as input into more queries on other services, such as data science workspace, real-time customer profile or analysis workspace. To output your query resource as a dataset, click on the output dataset option and provide a dataset name and description. Click on run query to initiate the dataset creation. In the background an SQL query gets executed and a dataset with a user provided name gets created. To keep track of the SQL queries executed and their status, let’s navigate back to the queries window and click on the log. Log tab provides a list of queries that have previously been executed. Log also provides information about the author and the source from where the query was initiated. By default, log lists all the queries in reverse chronology. You can notice that the query that we ran in the previous step to output to a dataset is still processing. Let’s give it a few seconds for the query to execute. When a query is still processing, you can cancel it by selecting the query from logs and then clicking on the cancel query option. If the query run successfully, you can see a dataset created for the query and displayed under the dataset field. You can open the dataset and verify the query result. After the dataset is created, it can be accessed like any other dataset in the data lake and used for various use cases. Note, that the datasets created through this functionality are generated with an ad-hoc schema, that matches the output data structure as defined in the SQL statement. Adobe Experience Platform allows you to schedule SQL queries directly from platform UI to make it easier for users to refresh datasets, processed by query service. While Adobe experience platform lets you schedule queries using API, this capability will help data engineers unfamiliar with the query service API to schedule queries using the UI. I have written a query to fetch the top 10 customers of luma, based on the luma loyalty points. Let’s provide a name for our query and save it. From the browser tab, select the query and click on the add schedule option. The schedule details page appears. On this page you can choose the frequency of the scheduled query, the dates and scheduled query will run and what dataset to export the query to. You can choose the following options for the frequency. For daily, the scheduled query will run every X days at the time and the date period you selected. Please note that the time selected is in UTC and not your local time zone. For weekly the selected query will run on the days of the week, time and the date period you’re selected. For monthly, the selected query will run every month, at the day, time and date period you selected. Yearly, the selected query will run every year at the day, month, time and date period you selected. For hourly, the selected query will run every hour for the date period you selected. I have chosen an hourly frequency for a specific date range. For the dataset, You have the option to use an existing dataset or create a new dataset. After selecting the decide frequency, click save to create a schedule. The query details page reappears and now shows the newly created schedule details, including the scheduled ID, the schedule itself and the scheduled output dataset. Let’s look on the query and you can view options to disable a schedule or delete a schedule. If you want to delete schedule for a query, you must disable the schedule first. Let’s verify the results of the scheduled query by navigating to the dataset that we created and open the dataset to preview the query result, displaying the top 10 luma loyalty customers. Adobe experience platform allows you to schedule SQL queries directly from platform UI to make it easier for users to refresh datasets processed by query service. You can also connect to query service using different desktop applications and tools to write and run your queries. From the queries window, select the credentials tab to obtain the Postgres credentials. If you have PostgreSQL installed on your computer, copy the PSQL command and run it in a terminal window. To test the connection run a query and you can see the query results in the terminal window. Using query service in Adobe Experience Platform, marketers can quickly gain insights about customers by gaining access to datasets and running queries against them. I hope I was able to give you a quick overview of how to access query service and experience platform UI -