---
title: [Tech] Using the Query Service
slug: ikea-summer-2025-hol/tech-using-the-query-service
docTags: 
createdAt: 2024-01-10T09:53:05.240Z
---

# 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:

![](https://api.archbee.com/api/optimize/1Jp0iolWv35Z_mkI7oUiT/A5OPnL-cmdzHN7zjL5wR8_image.png)

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.

![](https://api.archbee.com/api/optimize/1Jp0iolWv35Z_mkI7oUiT/BckVHKO2uDOA5nS_vh-AW_image.png)

**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

![](https://api.archbee.com/api/optimize/1Jp0iolWv35Z_mkI7oUiT/B8Q3XUnzXm6M7Wxm1xNMO_image.png)

:::hint{type="success"}
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.

1. Execute `show tables;` in your **PSQL command-line interface**. (do not forget to end your command with a semicolon).
2. 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

![](https://api.archbee.com/api/optimize/1Jp0iolWv35Z_mkI7oUiT/Xhg2SKEEKmh4ncDMViScH_image.png)

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:

```sql
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.

![](https://api.archbee.com/api/optimize/1Jp0iolWv35Z_mkI7oUiT/RM4_3j1U2IG8kdxsw1NO8_image.png)

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**:

```sql
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:

![](https://api.archbee.com/api/optimize/1Jp0iolWv35Z_mkI7oUiT/LtWwrplYh5eW_lGNTR7oU_image.png)

## 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?

```sql
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?

```sql
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.