[Tech] Using the Query Service
Objectives
- Learn how to connect to the Query Service.
- Learn how to address Experience Data Models objects and attributes in your queries.
Introduction to Query Service
Adobe Experience Platform Query Service is accessed by clicking on Queries in the left menu.
- By going to Log you’ll see the Query List page, which provides you a list of all the queries that have run in this organisation, with the latest at the top (if there is nothing in the list, clear all the filters).
- Click on any SQL query from the list and observe the details provided in the right rail.
- You can scroll the window to see the entire query, or you can click on the icon highlighted below to copy the entire query to your notepad. You do not have to copy the query at this time:
You can’t just see the queries that have been executed, this User Interface lets you create new datasets from queries. These datasets can be linked to Adobe Experience Platform’s Real-time Customer Profile or can be used as input for Adobe Experience Platform AI services.
Connect PSQL Client to Query Service
Query Service supports clients with a driver for PostgreSQL. In this we’ll be using PSQL, a command-line interface, and Power BI or Tableau. Let’s connect to PSQL.
- Click on Credentials.
- You will see the screen below. The Configuration screen provides server information and credentials for authenticating to Query Service. For now, we will focus on the right side of the screen which contains a connect command for PSQL. Click on the Copy button to copy the command to your clipboard.
For Windows: Open the command line by hitting the windows key and typing cmd and then clicking on the Command Prompt result.
For macOS: Open the terminal.app via spotlight search
Paste the connect command that you have copied from the Query Service UI and hit enter in the command prompt window
You are now connected to Query Service using PSQL.
In the next exercises, there will be quite some interaction with this window. We will refer to it as your PSQL command-line interface.
Now you are ready to start submitting queries!
Basic Queries
In this you will learn about the methods to retrieve information about the available datasets and how to properly retrieve data with a query from an XDM dataset.
All the datasets that we have explored via Adobe Experience Platform so far are also available for access via a SQL interface as tables. To list those tables you can use the show tables; command.
- Execute show tables; in your PSQL command-line interface. (do not forget to end your command with a semicolon).
- At the colon, press space bar to see the next page of the resultset, or enter q to revert to the command prompt.
Query Result
Every dataset in Platform has its corresponding Query Service table. You can find a dataset’s table via the Datasets UI.
The demo_system_event_dataset_for_website_global_v1_1 table is the Query Service table that corresponds with the Demo System - Event Schema for Website (Global v1.1) dataset.
To query some information about which product was viewed, we will select the page name information.
- Copy the statement below and paste it at the prompt in your PSQL command-line interface and hit enter:
select web.webPageDetails
from demo_system_event_dataset_for_website_global_v1_1
where eventType = 'commerce.productViews'
limit 1;In your query result, you will notice that columns in the Experience Data Model (XDM) can be complex types and not just scalar types. In the query above we would like to identify the names of the web pages where a commerce.productViews did occur. To identify a page name we have to navigate through the XDM model using the . (dot) notation.
Notice the result is a flat object rather than a single value? The web.webPageDetails object contains three attributes: page name, page url and page category. And when an object is declared as a column it will return the entire object as a string. The XDM schema may be more complex than what you are familiar with but it’s very powerful and was architected to support many solutions, channels, and use cases.
To select the individual properties of an object, you use the . (dot) notation.
Copy the statement below and paste it at the prompt in your PSQL command-line interface:
select
web.webPageDetails.URL
from demo_system_event_dataset_for_website_global_v1_1
where eventType = 'commerce.productViews'
limit 1;You can get obtain the path towards a specific property in the Experience Platform UI by navigating to the field within the Schema and selecting it. In the right hand menu, you will see the path:
Basic queries for data analysis
Timestamp
Data captured in Adobe Experience Platform is time stamped. The timestamp attribute allows you to analyze data over time.
How many product views do we have on a daily basis?
select date_format( timestamp , 'yyyy-MM-dd') AS Day,
count(*) AS productViews
from demo_system_event_dataset_for_website_global_v1_1
where eventType = 'commerce.productViews'
group by Day
limit 10;- Copy the statement above and execute it in your PSQL command-line interface.
What are the Top 5 products viewed?
select productListItems.name, count(*)
from demo_system_event_dataset_for_website_global_v1_1
where eventType = 'commerce.productViews'
group by productListItems.name
order by 2 desc
limit 5;- Copy the statement above and execute it in your PSQL command-line interface.
In the next set of queries we will extend the above query, in order to get a complete view on the customers and their behaviour. You will learn how to use the Adobe Defined Function to sessionize information, identify the sequence and timing of events. You will also join datasets together to further enrich and prepare the data for analysis in Microsoft Power BI.