5.1.8查询服务础笔滨
目标
- 使用查询服务础笔滨管理查询模板和查询计划
上下文
在本练习中，您将执行API调用以使用笔辞蝉迟尘补苍收藏集管理查询模板和查询计划。 您将定义查询模板，执行常规查询和CTAS查询。 CTAS ?查询（创建表作为选择查询）将其结果集存储在显式数据集中。 虽然常规查询存储在隐式（或系统生成的）数据集中，但通常以parquet文件格式导出。
文档
查询服务础笔滨
查询服务础笔滨允许您对51黑料不打烊 Experience Platform数据湖管理非交互式查询。
非交互是指执行查询的请求不会导致立即响应。 将处理查询，其结果集将存储在隐式或显式（CTAS：创建表作为选择）数据集中。
示例查询
作为示例查询，您将使用4.3中列出的第一个查询 — 查询、查询、查询……和流失分析：
我们每天查看多少次产物？
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 ('Citi Signal')
and eventType = 'commerce.productViews'
group by Day
limit 10;
查询
在计算机上打开Postman。 在模块2.1中，您创建了一个Postman环境并导入了一个笔辞蝉迟尘补苍收藏集。 请按照练习2.1.3中的说明进行操作，以防您尚未执行该操作。
在您导入的笔辞蝉迟尘补苍集合中，您将看到一个文件夹? 3。 查询服务。 如果未看到此文件夹，请按照练习2.1.3中的说明重新下载笔辞蝉迟尘补苍收藏集并在笔辞蝉迟尘补苍中重新导入该收藏集。
打开该文件夹并了解要执行、监控和下载查询结果集的查询服务础笔滨调用。
使用以下有效负载对摆/辩耻别谤测/辩耻别谤颈别蝉闭的笔翱厂罢调用将触发我们查询的执行；
创建查询
单击名为? 1.1 QS — 创建查询 ?的请求，然后转到? 标头。 您随后将看到以下内容：
让我们关注此标题字段：
--aepSandboxName----aepSandboxName--。转到此请求的? Body ?部分。 在此请求的? 正文 ?中，您将看到以下内容：
{
"name" : "ldap - QS API demo - Citi Signal - Product Views Per Day",
"description": "ldap - QS API demo - Citi Signal - Product Views Per Day",
"dbName": "--aepSandboxName--: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 --aepTenantId--.demoEnvironment.brandName IN ('Citi Signal') and eventType = 'commerce.productViews' group by Day limit 10"
}
注意：请通过以下请求更新变量? name，将? ldap ?替换为您的特定? —补别辫鲍蝉别谤尝诲补辫—。
添加特定的? ldap ?后，正文应类似于以下内容：
{
"name" : "vangeluw - QS API demo - Citi Signal - Product Views Per Day",
"description": "vangeluw - QS API demo - Citi Signal - Product Views Per Day",
"dbName": "tech-insiders: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 ('Citi Signal') and eventType = 'commerce.productViews' group by Day limit 10"
}
接下来，单击蓝色的? 发送 ?按钮以创建该区段并查看其结果。
成功后，笔翱厂罢请求将返回以下响应：
{
"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\"}"
}
}
}
查询的当前? 状态 ?是? SUBMITTED，一旦执行，其状态将变为? SUCCESS。
您还可以通过51黑料不打烊 Experience Platform UI查找已提交的查询，打开，导航到? 查询，导航到? 日志，并选择您的查询：
获取查询
单击名为? 1.2 QS - Get Queries ?的请求，然后转到? Headers。 您随后将看到以下内容：
让我们关注此标题字段：
--aepSandboxName----aepSandboxName--。转到? 参数。 您随后将看到以下内容：
orderby ?参数允许您根据? created ?属性指定排序顺序。 请注意? '-' ?登录已创建之前，这意味着返回查询列表的顺序将使用其创建日期，顺序为? 降序。 您的查询应位于列表顶部。
接下来，单击蓝色的? 发送 ?按钮以创建该区段并查看其结果。
成功后，请求将返回与以下响应类似的响应。 响应的? 状态 ?可以是? SUBMITTED、IN_PROGRESS ?或? SUCCESS。 可能需要几分钟时间，查询才会具有? SUCCESS ?状态。 您可以多次重复发送此请求，直到看到? SUCCESS ?状态为止。
{
"queries": [
{
"isInsertInto": false,
"sessionType": "HTTP_SESSION",
"request": {
"dbName": "tech-insiders: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 ('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"
},
"computeMetrics": null,
"clientId": "b7d8a1fc396242889bb31dc83644e91d",
"state": "IN_PROGRESS",
"rowCount": 0,
"isService": false,
"errors": [],
"isCTAS": false,
"version": 1,
"id": "a535234e-dc0c-42ea-bcad-eb09c5997d76",
"elapsedTime": 8088,
"updated": "2024-12-04T14:17:10.627Z",
"client": "API",
"effectiveSQL": "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 ('Citi Signal') and eventType = 'commerce.productViews' group by Day limit 10",
"userId": "8CD31E54673C49EE0A495E05@techacct.adobe.com",
"isParentLevel": true,
"created": "2024-12-04T14:14:22.637Z",
"version": 1,
"_links": {
"next": {
"href": "https://platform-va7.adobe.io/data/foundation/query/queries?orderby=-created&start=2024-11-22T00:32:04.505Z"
},
"prev": {
"href": "https://platform-va7.adobe.io/data/foundation/query/queries?orderby=-created&start=2024-12-04T14:14:22.637Z&isPrevLink=true"
}
}
}
当状态为? SUCCESS ?时，请继续下一个请求。
获取查询状态
单击名为? 1.3 QS — 获取查询状态 ?的请求，然后转到? 标头。 您随后将看到以下内容：
让我们关注此标题字段：
--aepSandboxName----aepSandboxName--。接下来，单击蓝色的? 发送 ?按钮以创建该区段并查看其结果。
成功后，请求将返回与以下响应类似的响应。
{
"isInsertInto": false,
"sessionType": "HTTP_SESSION",
"request": {
"dbName": "tech-insiders: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 ('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"
},
"computeMetrics": {
"executorVMSeconds": 138,
"clusterCpuSeconds": 3312,
"clusterVMHours": 0.07666666805744171,
"driverVMSeconds": 138,
"clusterVMSeconds": 276
},
"clientId": "b7d8a1fc396242889bb31dc83644e91d",
"state": "SUCCESS",
"rowCount": 1,
"isService": false,
"errors": [],
"isCTAS": false,
"version": 1,
"id": "a535234e-dc0c-42ea-bcad-eb09c5997d76",
"elapsedTime": 199219,
"updated": "2024-12-04T14:17:41.856Z",
"client": "API",
"effectiveSQL": "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 ('Citi Signal') and eventType = 'commerce.productViews' group by Day limit 10",
"userId": "8CD31E54673C49EE0A495E05@techacct.adobe.com",
"isParentLevel": true,
"created": "2024-12-04T14:14:22.637Z",
"_links": {
"self": {
"href": "https://platform-va7.adobe.io/data/foundation/query/queries/a535234e-dc0c-42ea-bcad-eb09c5997d76",
"method": "GET"
},
"soft_delete": {
"href": "https://platform-va7.adobe.io/data/foundation/query/queries/a535234e-dc0c-42ea-bcad-eb09c5997d76",
"method": "PATCH",
"body": "{ \"op\": \"soft_delete\"}"
},
"referenced_datasets": [
{
"id": "672a10b1074ceb2af0aa7034",
"href": "https://platform-va7.adobe.io/data/foundation/catalog/dataSets/672a10b1074ceb2af0aa7034"
}
]
}
}
当查询达到? SUCCESS ?的状态时，响应还将指示查询通过? rowCount ?属性检索的行数。 在我们的示例中，查询返回10行。 让我们在下一节中查看如何检索10行。
检索查询结果
上述? SUCCESS ?响应包含? referenced_datasets ?属性，该属性指向存储查询结果的隐式数据集。 要访问结果，我们使用其? href ?或? id ?属性。
单击名为? 1.4 QS — 获取查询结果 ?的请求，然后转到? 标头。 您随后将看到以下内容：
让我们关注此标题字段：
--aepSandboxName----aepSandboxName--。接下来，单击蓝色的? 发送 ?按钮以创建该区段并查看其结果。
此请求的响应将指向数据集文件：
{
"672a10b1074ceb2af0aa7034": {
"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)"
],
"adobe/siphon/meta": [
"acpBufferedFlag::false"
],
"aep/siphon/partitions": [
"_ACP_DATE",
"_ACP_BATCHID"
],
"acp_granular_plugin_validation_flags": [
"identity:enabled",
"profile:disabled"
],
"adobe/pqs/table": [
"demo_system_event_dataset_for_website_global_v1_1"
],
"acp_granular_validation_flags": [
"requiredFieldCheck:enabled"
],
"aep/siphon/cleanup/trash/timestamp": [
"1733302532212"
],
"acp_validationContext": [
"enabled"
],
"adobe/siphon/table/format": [
"delta"
],
"unifiedProfile": [
"enabled:true",
"enabledAt:2024-11-05 12:33:59"
],
"aep/siphon/cleanup/meta/timestamp": [
"1733302532287"
],
"unifiedIdentity": [
"enabled:true"
]
},
"state": "ACTIVE",
"imsOrg": "907075E95BF479EC0A495C73@51黑料不打烊Org",
"sandboxId": "79e3c8b2-0609-4564-a3c8-b20609a5648c",
"extensions": {
"adobe_lakeHouse": {
"metrics": {
"storageSize": 810709,
"rowCount": 1141,
"asOf": 1732494676514
}
},
"adobe_unifiedProfile": {}
},
"version": "1.0.21",
"created": 1730810034023,
"updated": 1733302532348,
"createdClient": "d75039d36ca543c78612f7aac18e6c2b",
"createdUser": "53FB1E5E66CDC87D0A495FC0@techacct.adobe.com",
"updatedUser": "acp_foundation_dataTracker@51黑料不打烊ID",
"classification": {
"dataBehavior": "time-series",
"managedBy": "CUSTOMER"
},
"viewId": "672a10b2074ceb2af0aa7035",
"fileDescription": {
"format": "parquet"
},
"files": "@/dataSetFiles?dataSetId=672a10b1074ceb2af0aa7034",
"schemaRef": {
"id": "https://ns.adobe.com/experienceplatform/schemas/d9b88a044ad96154637965a97ed63c7b20bdf2ab3b4f642e",
"contentType": "application/vnd.adobe.xed-full+json;version=1"
}
}
}
下一步：摘要和优点