Query Service API

Transcript

Hi there. Adobe Experience Platform ingests 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 and API. In this video, let me show you how data engineers can access platform query service using API. In order to make calls to Adobe Experience Platform API’s, you must first gain access to experience platform API using Adobe Developer Console. I have created a project in Adobe Developer Console and have added experience platform. API access. Adobe Developer Console’s export details for postman capability provides an easy way to export all the account details required to access, and interact with an experience platform API in a single postman environment file, removing the need to copy and paste values from Adobe Developer Console into postman. So let me download the environment file and we will import it to postman later in this video. Next, let’s check out the query service API documentation, and download the sample postman collection for query service. Let’s open the postman application and open your workspace. I have already imported the environment variables file that we downloaded in the previous step.

My workspace also contains a postman collection from GitHub for authentication and query service. Links to postman collections can be found along with this video. Before we start using the API option to execute a query, let’s flip screens and explore the platform UI and list of data sets available in platform. From your experience platform homepage, navigate your data sets under data management. For this video, we are using a fictional retail brand called Luma. Luma loyalty data set contains information about customers, loyalty details, geographic details, et cetera. Luma web data contains web traffic information about customers, interaction on the Luma site, including products viewed by a customer, visited pages, products purchased, et cetera. Platform query service provides a query editor UI, that lets you write queries and execute them. Now, let’s flip screens and explore how to use API to access query service. Please make sure that all necessary environment variables are populated before we make an API call to authenticate. Upon successful authentication, you will be provided with an access token to conduct query service API collection and navigate to the connections folder. You can retrieve your connection parameters by making a Get request to the slash connection parameters end point. A successful response returns HTTP status 200 with your connection parameters. Using the connection parameter, you can connect to query service from a variety of desk top client applications. From the queries folder, click on the create a new query option. You can create a new query by making a post request to the slash queries end point. Make sure that you include necessary header fields and values in your API request. Click on the request body below tab, And here I have an SQL query that fetches the top five Luma products ordered by customers from the Luma web data set, and inserts the result into the top product views data set. Let’s execute the API call, and a successful response returns HTTP stages 200, indicating that a new query request is submitted to experience platform, and currently has its state as submitted. To get more details about the query, copy the query ID from the response, and let’s make another call to obtain the query status by ID. A successful response returns HTTP stages 200 with detailed information about the specified query. Let’s switch back to platform UI, and under the credit logs you can see that our query is running, and has its status as submitted. Upon successfully executing the query, it’s status changes from submitted to return to client. Let’s open our data set to verify if the insert into statement has inserted any new rules into the data set. Under the data set overview window you can notice that our query ingested five new records. We explored how to create a simple query that inserts data into an existing data set by making an API call to the query end point. Now let’s explore how to create a new scheduled query. You can retrieve a list of all scheduled queries for your IMS organization, by making a get request to the slash schedules end point.

To create a new scheduled query, We need to make a post request to the slash schedules end point with additional information. Let’s check the body of our request. Here, you can see two adjacent objects, one for query itself, and one for how often platform should run this query. Our current SQL query fetches the top five products ordered by Luma customers, and stores the result in a new data set. Scheduled, our schedule contains the Chrome expression for scheduling a query. To demonstrate the feature, I have a Chrome expression that runs every two minutes. You can also provide a start date, and an end date, for your scheduled query as a UTC timestamp. A successful response returns HTTP stages 200 accepted. With the details of the newly created scheduled query. Once a scheduled query is finished activating, the status will change from registering, to enabled. Let’s visit the platform UI to check if the query ran as expected. You can notice that the status command ran successfully, and has created a new data set. Since we have scheduled our query to run every two minutes, the next run will be scheduled in two minutes. Let’s open the data set to verify the result. Under the data set overview window. You can notice that our query ingested five new records. Let’s switch to the postman window. Now, let’s see how to create a query template. If you run a query very often, and you would like to save it, you could use a query template. You can create a query template by making a post request to the slash query templates end point. You need to provide the database name, SQL command, name, and description for the credit template in the request body. You can also schedule a query template by adding the scheduler object to the request body, of the Chrome scheduler expression. A successful response returns HTTP stages 200, with the details of the newly created query template. To view and rerun the query template, let’s switch to query service UI, and navigate to the browse tab. Here, you can observe our query template listed. Select the query, and it opens our SQL command in a query editor window. Using query service API in Adobe Experience Platform, data engineers can create a new query, schedule a query, and create a query template. -