# 4.7 Query Service API

## Objective

* Use the Query Service API to manage query templates and query schedules

## Context

In this exercise you will execute API calls to manage query templates and query schedules using a Postman collection. You will define query templates, execute regular queries and CTAS queries. A **CTAS** query (create table as select query) stores its resultset in an explicit dataset. While regular queries are stored in a implicit (or system generated) dataset, that is typically exported in parquet file format.

## Documentation

* [Adobe Experience Platform Query Service Help](https://experienceleague.adobe.com/docs/experience-platform/query/api/getting-started.html)
* [Query Service API](https://www.adobe.io/apis/experienceplatform/home/api-reference.html#!acpdr/swagger-specs/qs-api.yaml)

## 4.7.1 Query Service API

The Query Service API lets you manage non-interactive queries against the Adobe Experience Platform data-lake.

Non-interactive means that a request to execute a query will not result in an immediate response. The query will be processed and its resultset will be stored in an implicit or explicit (CTAS: create table as select) dataset.

## 4.7.2 Sample Query

As a sample query you'll use the first query listed in [4.3 - Queries, queries, queries... and churn analysis](https://docs.adobesandbox.com/comprehensive-technical-tutorial-archive/module4/ex3):

How many product views do we have on a daily basis?

**SQL**

```sql
select date_format( timestamp , 'yyyy-MM-dd') AS Day,
       count(*) AS productViews
from   demo_system_event_dataset_for_website_global_v1_1
where  --aepTenantId--.demoEnvironment.brandName IN ('Luma Telco', 'Citi Signal')
and eventType = 'commerce.productViews'
group by Day
limit 10;
```

## 4.7.3 Queries

Open Postman on your computer. As part of Module 3, you created a Postman environment and imported a Postman collection. Follow the instructions in [Exercise 3.3.3](https://docs.adobesandbox.com/comprehensive-technical-tutorial-archive/module3/ex3) in case you haven't done that yet.

As part of the Postman collection you imported, you'll see a folder **3. Query Service**. If you don't see this folder, please redownload the [Postman collection](https://github.com/AdobePartnerTechnicalServices/aep-quickstart-guide/blob/main/assets/postman/postman_profile.zip) and reimport that collection in Postman as instructed in [Exercise 3.3.3](https://docs.adobesandbox.com/comprehensive-technical-tutorial-archive/module3/ex3).

![QS](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-50af8f926c54bc78df22504d2cb7b04875cb94c0%2Fpm3.png?alt=media)

{% hint style="info" %}
At this moment, only the folder **1. Queries** contains requests. Other requests will be added at a layer stage.
{% endhint %}

Open that folder and get to know the Query Service API calls to execute, monitor and download the query resultset.

A POST call to \[/query/queries] with the following payload will trigger the execution of our query;

### 4.7.3.1 Create Query

Click on the request named **1.1 QS - Create Query** and go to **Headers**. You'll then see this:

![Segmentation](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-aeac42339e2b10979ac17ce33118bb2703a977c0%2Fs1_call.png?alt=media)

Let's focus on this header field:

| Key            | Value                |
| -------------- | -------------------- |
| x-sandbox-name | `--module7sandbox--` |

{% hint style="info" %}
You need to specify the name of the Adobe Experience Platform sandbox you're using. The header field **x-sandbox-name** should be `--module7sandbox--`.
{% endhint %}

Go the the **Body** section of this request. In the **Body** of this request, you'll see the following:

![Segmentation](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-d93e6a16eb74ac29090bca8a0488d9cbf1e6d55a%2Fs1_bodydtl.png?alt=media)

```sql
{
    "name" : "ldap - QS API demo - Citi Signal - Product Views Per Day",
	"description": "ldap - QS API demo - Citi Signal - Product Views Per Day",
	"dbName": "module7:all",
	"sql": "select date_format( timestamp , 'yyyy-MM-dd') AS Day, count(*) AS productViews from demo_system_event_dataset_for_website_global_v1_1 where _experienceplatform.demoEnvironment.brandName IN ('Luma Telco', 'Citi Signal') and eventType = 'commerce.productViews' group by Day limit 10"
}
```

Attention: please update the variable **name** in the below request by replacing **ldap** with your specific **ldap**.

After adding your specific **ldap**, the Body should look similar to this:

```json
{
    "name" : "vangeluw - QS API demo - Citi Signal - Product Views Per Day",
	"description": "vangeluw - QS API demo - Citi Signal - Product Views Per Day",
	"dbName": "module7:all",
	"sql": "select date_format( timestamp , 'yyyy-MM-dd') AS Day, count(*) AS productViews from demo_system_event_dataset_for_website_global_v1_1 where _experienceplatform.demoEnvironment.brandName IN ('Luma Telco', 'Citi Signal') and eventType = 'commerce.productViews' group by Day limit 10"
}
```

{% hint style="info" %}
The key **dbName** in the above JSON body refers to the sandbox that is used in your Adobe Experience Platform instance. If you're using the PROD sandbox, the dbName should be **prod:all**, if you use another sandbox like for instance **module7**, the dbName should be equal to **module7:all**.
{% endhint %}

Next, click the blue **Send** button to create the segment and view the results of that.

![Segmentation](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-b8edfa25c90d8ad6d9c06dc82b5f657789a5ba5a%2Fs1_bodydtl_results.png?alt=media)

When successful the POST request will return the following response:

```json
{
    "isInsertInto": false,
    "request": {
        "dbName": "module7:all",
        "sql": "select date_format( timestamp , 'yyyy-MM-dd') AS Day, count(*) AS productViews from demo_system_event_dataset_for_website_global_v1_1 where _experienceplatform.demoEnvironment.brandName IN ('Luma Telco', 'Citi Signal') and eventType = 'commerce.productViews' group by Day limit 10",
        "name": "vangeluw - QS API demo - Citi Signal - Product Views Per Day",
        "description": "vangeluw - QS API demo - Citi Signal - Product Views Per Day"
    },
    "clientId": "5a143b5ae4aa4631a1f3b09cd051333f",
    "state": "SUBMITTED",
    "rowCount": 0,
    "errors": [],
    "isCTAS": false,
    "version": 1,
    "id": "8f0d7f25-f7aa-493b-9792-290f884a7e5b",
    "elapsedTime": 0,
    "updated": "2021-01-20T13:23:13.951Z",
    "client": "API",
    "userId": "A3392DB95FFF08EE0A495E87@techacct.adobe.com",
    "created": "2021-01-20T13:23:13.951Z",
    "_links": {
        "self": {
            "href": "https://platform-va7.adobe.io/data/foundation/query/queries/8f0d7f25-f7aa-493b-9792-290f884a7e5b",
            "method": "GET"
        },
        "soft_delete": {
            "href": "https://platform-va7.adobe.io/data/foundation/query/queries/8f0d7f25-f7aa-493b-9792-290f884a7e5b",
            "method": "PATCH",
            "body": "{ \"op\": \"soft_delete\"}"
        },
        "cancel": {
            "href": "https://platform-va7.adobe.io/data/foundation/query/queries/8f0d7f25-f7aa-493b-9792-290f884a7e5b",
            "method": "PATCH",
            "body": "{ \"op\": \"cancel\"}"
        }
    }
}
```

The current **state** of the query is **SUBMITTED**, once executed its state will become **SUCCESS**.

You can also lookup submitted queries via Adobe Experience Platform UI, open [Adobe Experience Platform](https://experience.adobe.com/#/@experienceplatform/platform/home), navigate to **Queries**, to **Log** and select your query:

![Segmentation](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-ff222cde32b947269cbf37297ca9431319eb530c%2Fs1_bodydtl_results_qs.png?alt=media)

### 4.7.3.2 Get Queries

Click on the request named **1.2 QS - Get Queries** and go to **Headers**. You'll then see this:

![Segmentation](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-f10be880b4ad4cdcf8ab3ee552c208365b39334e%2Fs2_call.png?alt=media)

Let's focus on this header field:

| Key            | Value                |
| -------------- | -------------------- |
| x-sandbox-name | `--module7sandbox--` |

{% hint style="info" %}
You need to specify the name of the Adobe Experience Platform sandbox you're using. The header field **x-sandbox-name** should be `--module7sandbox--`.
{% endhint %}

Go to **Params**. You'll then see this:

![Segmentation](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-7f7db5627285c378b427ef048bf0825da6db8b1a%2Fs2_call_p.png?alt=media)

The **orderby** parameter allows you to specify a sort order based on the **created** property. Notice the **'-'** sign in front of created, which means that the order in which the list of queries is returned will be using their created date in **descending** order. Your query should be on top of the list.

Next, click the blue **Send** button to create the segment and view the results of that.

![Segmentation](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-d8548cb35bf6dcbf0eb90892efec6f6c51d1de71%2Fs2_bodydtl_results.png?alt=media)

When successful the request will return a response similar to the below one. The **state** of the response may be **SUBMITTED**, **IN\_PROGRESS** or **SUCCESS**. It may take several minutes before the query has a **SUCCESS** state. You can repeat sending this request several times, until you see the **SUCCESS** state.

```json
{
    "queries": [
        {
            "isInsertInto": false,
            "request": {
                "dbName": "module7:all",
                "sql": "select date_format( timestamp , 'yyyy-MM-dd') AS Day, count(*) AS productViews from demo_system_event_dataset_for_website_global_v1_1 where _experienceplatform.demoEnvironment.brandName IN ('Luma Telco', 'Citi Signal') and eventType = 'commerce.productViews' group by Day limit 10",
                "name": "vangeluw - QS API demo - Citi Signal - Product Views Per Day",
                "description": "vangeluw - QS API demo - Citi Signal - Product Views Per Day"
            },
            "clientId": "5a143b5ae4aa4631a1f3b09cd051333f",
            "state": "SUCCESS",
            "rowCount": 1,
            "errors": [],
            "isCTAS": false,
            "version": 1,
            "id": "8f0d7f25-f7aa-493b-9792-290f884a7e5b",
            "elapsedTime": 217481,
            "updated": "2021-01-20T13:26:51.432Z",
            "client": "API",
            "userId": "A3392DB95FFF08EE0A495E87@techacct.adobe.com",
            "created": "2021-01-20T13:23:13.951Z",
            "_links": {
                "self": {
                    "href": "https://platform-va7.adobe.io/data/foundation/query/queries/8f0d7f25-f7aa-493b-9792-290f884a7e5b",
                    "method": "GET"
                },
                "soft_delete": {
                    "href": "https://platform-va7.adobe.io/data/foundation/query/queries/8f0d7f25-f7aa-493b-9792-290f884a7e5b",
                    "method": "PATCH",
                    "body": "{ \"op\": \"soft_delete\"}"
                },
                "referenced_datasets": [
                    {
                        "id": "60080ace62c49a19490c5870",
                        "href": "https://platform-va7.adobe.io/data/foundation/catalog/dataSets/60080ace62c49a19490c5870"
                    }
                ]
            }
        }
     ]
    },
    "version": 1
}
```

When the state is **SUCCESS**, please continue with the next request.

### 4.7.3.3 Get Query Status

Click on the request named **1.3 QS - Get Query Status** and go to **Headers**. You'll then see this:

![Segmentation](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-e0e86872c51d3675ba193fb728e6691700eb8e94%2Fs3_call.png?alt=media)

Let's focus on this header field:

| Key            | Value                |
| -------------- | -------------------- |
| x-sandbox-name | `--module7sandbox--` |

{% hint style="info" %}
You need to specify the name of the Adobe Experience Platform sandbox you're using. The header field **x-sandbox-name** should be `--module7sandbox--`.
{% endhint %}

Next, click the blue **Send** button to create the segment and view the results of that.

![Segmentation](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-d3dc1477137314b802301511f1ee8a6cce31652d%2Fs3_bodydtl_results.png?alt=media)

When successful the request will return a response similar to the below one.

```json
{
    "isInsertInto": false,
    "request": {
        "dbName": "module7:all",
        "sql": "select date_format( timestamp , 'yyyy-MM-dd') AS Day, count(*) AS productViews from demo_system_event_dataset_for_website_global_v1_1 where _experienceplatform.demoEnvironment.brandName IN ('Luma Telco', 'Citi Signal') and eventType = 'commerce.productViews' group by Day limit 10",
        "name": "vangeluw - QS API demo - Citi Signal - Product Views Per Day",
        "description": "vangeluw - QS API demo - Citi Signal - Product Views Per Day"
    },
    "clientId": "5a143b5ae4aa4631a1f3b09cd051333f",
    "state": "SUCCESS",
    "rowCount": 1,
    "errors": [],
    "isCTAS": false,
    "version": 1,
    "id": "8f0d7f25-f7aa-493b-9792-290f884a7e5b",
    "elapsedTime": 217481,
    "updated": "2021-01-20T13:26:51.432Z",
    "client": "API",
    "userId": "A3392DB95FFF08EE0A495E87@techacct.adobe.com",
    "created": "2021-01-20T13:23:13.951Z",
    "_links": {
        "self": {
            "href": "https://platform-va7.adobe.io/data/foundation/query/queries/8f0d7f25-f7aa-493b-9792-290f884a7e5b",
            "method": "GET"
        },
        "soft_delete": {
            "href": "https://platform-va7.adobe.io/data/foundation/query/queries/8f0d7f25-f7aa-493b-9792-290f884a7e5b",
            "method": "PATCH",
            "body": "{ \"op\": \"soft_delete\"}"
        },
        "referenced_datasets": [
            {
                "id": "60080ace62c49a19490c5870",
                "href": "https://platform-va7.adobe.io/data/foundation/catalog/dataSets/60080ace62c49a19490c5870"
            }
        ]
    }
}
```

When a query reaches the state of **SUCCESS**, the response will also indicate the number of rows retrieved by the query via the **rowCount** property. In our example 10 rows are returned by the query. Let's see in the next section how we can retrieve the 10 rows.

### 4.7.3.4 Retrieve Query Result

The **SUCCESS** response above includes a **referenced\_datasets** property, which points to the implicit dataset which stores the query result. To get access to the result, we use its **href** or **id** property.

Click on the request named **1.4 QS - Get Query Result** and go to **Headers**. You'll then see this:

![Segmentation](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-d6353dbfcefb1285a19c2e31d30a130fd156fae4%2Fs4_call.png?alt=media)

Let's focus on this header field:

| Key            | Value                |
| -------------- | -------------------- |
| x-sandbox-name | `--module7sandbox--` |

> \[!NOTE]
>
> You need to specify the name of the Adobe Experience Platform sandbox you're using. The header field **x-sandbox-name** should be `--module7sandbox--`.

Next, click the blue **Send** button to create the segment and view the results of that.

![Segmentation](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-8eec7d63dcf616ab34bbeda40065f32115994222%2Fs4_bodydtl_results.png?alt=media)

The response of this request will point to the dataset files:

```json
{
    "60080ace62c49a19490c5870": {
        "name": "Demo System - Event Dataset for Website (Global v1.1)",
        "description": "Demo System - Event Dataset for Website (Global v1.1)",
        "enableErrorDiagnostics": false,
        "tags": {
            "adobe/siphon/partition/definition": [
                "day(timestamp, _ACP_DATE)",
                "identity(_ACP_BATCHID)"
            ],
            "aep/siphon/partitions": [
                "_ACP_DATE",
                "_ACP_BATCHID"
            ],
            "acp_granular_plugin_validation_flags": [
                "identity:enabled",
                "profile:enabled"
            ],
            "adobe/siphon/buffered-promotion-recency": [
                "live"
            ],
            "adobe/siphon/use-buffered-promotion": [
                "true"
            ],
            "adobe/pqs/table": [
                "demo_system_event_dataset_for_website_global_v1_1"
            ],
            "aep/siphon/expire-snapshot-timestamp": [
                "1611141272703"
            ],
            "acp_granular_validation_flags": [
                "requiredFieldCheck:enabled"
            ],
            "acp_validationContext": [
                "enabled"
            ],
            "adobe/siphon/table/format": [
                "iceberg"
            ],
            "unifiedProfile": [
                "enabled:true",
                "enabledAt:2021-01-20 10:49:51"
            ],
            "unifiedIdentity": [
                "enabled:true"
            ]
        },
        "namespace": "ACP",
        "state": "DRAFT",
        "imsOrg": "907075E95BF479EC0A495C73@AdobeOrg",
        "sandboxId": "62cd9f38-8529-4b05-8d9f-388529db0540",
        "lastBatchId": "01EWFQZ15XRNNB1FPKPW5ETRVP",
        "lastBatchStatus": "success",
        "lastSuccessfulBatch": "01EWFQZ15XRNNB1FPKPW5ETRVP",
        "version": "1.0.6",
        "created": 1611139790698,
        "updated": 1611149266031,
        "createdClient": "750e24ee855b4ac18ccc4f4817f96ee1",
        "createdUser": "3A260B485E909A170A495E76@techacct.adobe.com",
        "updatedUser": "acp_foundation_dataTracker@AdobeID",
        "viewId": "60080ace62c49a19490c5871",
        "fileDescription": {
            "persisted": true,
            "containerFormat": "parquet",
            "format": "parquet"
        },
        "files": "@/dataSets/60080ace62c49a19490c5870/views/60080ace62c49a19490c5871/files",
        "schemaMetadata": {
            "delta": [],
            "gdpr": []
        },
        "schemaRef": {
            "id": "https://ns.adobe.com/experienceplatform/schemas/d9b88a044ad96154637965a97ed63c7b20bdf2ab3b4f642e",
            "contentType": "application/vnd.adobe.xed-full+json;version=1"
        }
    }
}
```

{% hint style="info" %}
More exercises will be added soon to help you interact with the Query Service API.
{% endhint %}