篩選列層級資料的支援目前僅適用於下列來源:
本教學課程提供如何使用來篩選來源的列層級資料的步驟。 Flow Service API.
本教學課程需要您實際瞭解Adobe Experience Platform的下列元件:
如需如何成功呼叫Platform API的詳細資訊,請參閱以下指南中的 Platform API快速入門.
以下概述篩選來源之列層級資料時應採取的步驟。
在使用API來篩選來源的列層級資料之前,您必須先擷取來源的連線規格詳細資料,以判斷特定來源支援的運運算元和語言。
GET若要擷取指定來源的連線規格,請向 /connectionSpecs
的端點 Flow Service API時輸入來源的屬性名稱,作為查詢引數的一部分。
API格式
GET /connectionSpecs/{QUERY_PARAMS}
參數 | 說明 |
---|---|
{QUERY_PARAMS} |
篩選結果的選用查詢引數。 您可以擷取 Google BigQuery 透過套用 name 屬性和指定 "google-big-query" 以取得搜尋結果。 |
要求
下列要求會擷取下列專案的連線規格: Google BigQuery.
curl -X GET \
'https://platform.adobe.io/data/foundation/flowservice/connectionSpecs?property=name=="google-big-query"' \
-H 'Authorization: Bearer {ACCESS_TOKEN}' \
-H 'x-api-key: {API_KEY}' \
-H 'x-gw-ims-org-id: {ORG_ID}' \
-H 'x-sandbox-name: {SANDBOX_NAME}'
回應
成功的回應會傳回的連線規格 Google BigQuery,包括其支援的查詢語言和邏輯運運算元的資訊。
以下API回應會因簡短原因而截斷。
"attributes": {
"filterAtSource": {
"enabled": true,
"queryLanguage": "SQL",
"logicalOperators": [
"and",
"or",
"not"
],
"comparisonOperators": [
"=",
"!=",
"<",
"<=",
">",
">=",
"like",
"in"
],
"columnNameEscapeChar": "`",
"valueEscapeChar": "'"
}
屬性 | 說明 |
---|---|
attributes.filterAtSource.enabled |
決定查詢的來源是否支援篩選列層級資料。 |
attributes.filterAtSource.queryLanguage |
決定查詢來源支援的查詢語言。 |
attributes.filterAtSource.logicalOperators |
決定可用來篩選來源之列層級資料的邏輯運運算元。 |
attributes.filterAtSource.comparisonOperators |
決定可用來篩選來源之列層級資料的比較運運算元。 請參閱下表以瞭解比較運運算元的詳細資訊。 |
attributes.filterAtSource.columnNameEscapeChar |
決定用於逸出欄的字元。 |
attributes.filterAtSource.valueEscapeChar |
決定寫入SQL查詢時如何包圍值。 |
運算子 | 說明 |
---|---|
== |
依據屬性是否等於提供的值來篩選。 |
!= |
依據屬性是否不等於提供的值來篩選。 |
< |
依據屬性是否小於提供的值來篩選。 |
> |
依據屬性是否大於提供的值來篩選。 |
<= |
依據屬性是否小於或等於提供的值來篩選。 |
>= |
依據屬性是否大於或等於提供的值來篩選。 |
like |
在中使用的篩選器 WHERE 子句以搜尋指定的模式。 |
in |
依據屬性是否在指定範圍內進行篩選。 |
在識別來源支援的邏輯運運算元和查詢語言之後,您可以使用設定檔查詢語言(PQL)來指定您要套用至來源資料的篩選條件。
在下列範例中,條件僅會套用至選取與引數中所列節點型別所提供值相等的資料。
{
"type": "PQL",
"format": "pql/json",
"value": {
"nodeType": "fnApply",
"fnName": "=",
"params": [
{
"nodeType": "fieldLookup",
"fieldName": "city"
},
{
"nodeType": "literal",
"value": "DDN"
}
]
}
}
您可以透過向以下發出GET請求來預覽資料: /explore
的端點 Flow Service API同時提供 filters
作為查詢引數的一部分,並在中指定PQL輸入條件 Base64.
API格式
GET /connections/{BASE_CONNECTION_ID}/explore?objectType=table&object={TABLE_PATH}&preview=true&filters={FILTERS}
參數 | 說明 |
---|---|
{BASE_CONNECTION_ID} |
來源的基本連線ID。 |
{TABLE_PATH} |
您要檢查的資料表的path屬性。 |
{FILTERS} |
您的PQL篩選條件編碼於 Base64. |
要求
curl -X GET \
'https://platform.adobe.io/data/foundation/flowservice/connections/89d1459e-3cd0-4069-acb3-68f240db4eeb/explore?objectType=table&object=TESTFAS.FASTABLE&preview=true&filters=ewogICJ0eXBlIjogIlBRTCIsCiAgImZvcm1hdCI6ICJwcWwvanNvbiIsCiAgInZhbHVlIjogewogICAgIm5vZGVUeXBlIjogImZuQXBwbHkiLAogICAgImZuTmFtZSI6ICJhbmQiLAogICAgInBhcmFtcyI6IFsKICAgICAgewogICAgICAgICJub2RlVHlwZSI6ICJmbkFwcGx5IiwKICAgICAgICAiZm5OYW1lIjogImxpa2UiLAogICAgICAgICJwYXJhbXMiOiBbCiAgICAgICAgICB7CiAgICAgICAgICAgICJub2RlVHlwZSI6ICJmaWVsZExvb2t1cCIsCiAgICAgICAgICAgICJmaWVsZE5hbWUiOiAiY2l0eSIKICAgICAgICAgIH0sCiAgICAgICAgICB7CiAgICAgICAgICAgICJub2RlVHlwZSI6ICJsaXRlcmFsIiwKICAgICAgICAgICAgInZhbHVlIjogIk0lIgogICAgICAgICAgfQogICAgICAgIF0KICAgICAgfQogICAgXQogIH0KfQ==\' \
-H 'Authorization: Bearer {ACCESS_TOKEN}' \
-H 'x-api-key: {API_KEY}' \
-H 'x-gw-ims-org-id: {IMS_ORG}' \
-H 'x-sandbox-name: {SANDBOX_NAME}'
回應
成功的請求會傳回以下回應。
{
"format": "flat",
"schema": {
"columns": [
{
"name": "FIRSTNAME",
"type": "string",
"xdm": {
"type": "string"
}
},
{
"name": "LASTNAME",
"type": "string",
"xdm": {
"type": "string"
}
},
{
"name": "CITY",
"type": "string",
"xdm": {
"type": "string"
}
},
{
"name": "AGE",
"type": "string",
"xdm": {
"type": "string"
}
},
{
"name": "HEIGHT",
"type": "string",
"xdm": {
"type": "string"
}
},
{
"name": "ISEMPLOYED",
"type": "boolean",
"xdm": {
"type": "boolean"
}
},
{
"name": "POSTG",
"type": "boolean",
"xdm": {
"type": "boolean"
}
},
{
"name": "LATITUDE",
"type": "double",
"xdm": {
"type": "number"
}
},
{
"name": "LONGITUDE",
"type": "double",
"xdm": {
"type": "number"
}
},
{
"name": "JOINEDDATE",
"type": "string",
"meta:xdmType": "date-time",
"xdm": {
"type": "string",
"format": "date-time"
}
},
{
"name": "CREATEDAT",
"type": "string",
"meta:xdmType": "date-time",
"xdm": {
"type": "string",
"format": "date-time"
}
},
{
"name": "CREATEDATTS",
"type": "string",
"meta:xdmType": "date-time",
"xdm": {
"type": "string",
"format": "date-time"
}
}
]
},
"data": [
{
"CITY": "MZN",
"LASTNAME": "Jain",
"JOINEDDATE": "2022-06-22T00:00:00",
"LONGITUDE": 1000.222,
"CREATEDAT": "2022-06-22T17:19:33",
"FIRSTNAME": "Shivam",
"POSTG": true,
"HEIGHT": "169",
"CREATEDATTS": "2022-06-22T17:19:33",
"ISEMPLOYED": true,
"LATITUDE": 2000.89,
"AGE": "25"
},
{
"CITY": "MUM",
"LASTNAME": "Kreet",
"JOINEDDATE": "2022-09-07T00:00:00",
"LONGITUDE": 10500.01,
"CREATEDAT": "2022-09-07T17:19:33",
"FIRSTNAME": "Rakul",
"POSTG": true,
"HEIGHT": "155",
"CREATEDATTS": "2022-09-07T17:19:33",
"ISEMPLOYED": false,
"LATITUDE": 2500.89,
"AGE": "42"
},
{
"CITY": "MAN",
"LASTNAME": "Lee",
"JOINEDDATE": "2022-09-14T00:00:00",
"LONGITUDE": 1000.222,
"CREATEDAT": "2022-09-14T05:02:33",
"FIRSTNAME": "Denzel",
"POSTG": true,
"HEIGHT": "185",
"CREATEDATTS": "2022-09-14T05:02:33",
"ISEMPLOYED": true,
"LATITUDE": 123.89,
"AGE": "16"
}
]
}
若要建立來源連線並擷取經過篩選的資料,請向發出POST要求 /sourceConnections
端點時,將篩選條件提供為body引數的一部分。
API格式
POST /sourceConnections
要求
以下請求會建立來源連線,以從中擷取資料 test1.fasTestTable
位置 city
= DDN
.
curl -X POST \
'https://platform.adobe.io/data/foundation/flowservice/sourceConnections' \
-H 'Authorization: Bearer {ACCESS_TOKEN}' \
-H 'x-api-key: {API_KEY}' \
-H 'x-gw-ims-org-id: {ORG_ID}' \
-H 'x-sandbox-name: {SANDBOX_NAME}' \
-H 'Content-Type: application/json' \
-d '{
"name": "BigQuery Source Connection",
"description": "Source Connection for Filter test",
"baseConnectionId": "89d1459e-3cd0-4069-acb3-68f240db4eeb",
"data": {
"format": "tabular"
},
"params": {
"tableName": "test1.fasTestTable",
"filters": {
"type": "PQL",
"format": "pql/json",
"value": {
"nodeType": "fnApply",
"fnName": "=",
"params": [
{
"nodeType": "fieldLookup",
"fieldName": "city"
},
{
"nodeType": "literal",
"value": "DDN"
}
]
}
}
},
"connectionSpec": {
"id": "3c9b37f8-13a6-43d8-bad3-b863b941fedd",
"version": "1.0"
}
}'
回應
成功的回應會傳回唯一識別碼(id
)。
{
"id": "b7581b59-c603-4df1-a689-d23d7ac440f3",
"etag": "\"ef05d265-0000-0200-0000-6019e0080000\""
}
本節提供不同裝載的更多範例以供篩選。
您可以省略初始值 fnApply
適用於只需要一個條件的案例。
{
"type": "PQL",
"format": "pql/json",
"value": {
"nodeType": "fnApply",
"fnName": "like",
"params": [
{
"nodeType": "fieldLookup",
"fieldName": "firstname"
},
{
"nodeType": "literal",
"value": "%s"
}
]
}
}
in
運運算元如需運運算元的範例,請參閱下方的裝載範例 in
.
{
"type": "PQL",
"format": "pql/json",
"value": {
"nodeType": "fnApply",
"fnName": "and",
"params": [
{
"nodeType": "fnApply",
"fnName": "in",
"params": [
{
"nodeType": "fieldLookup",
"fieldName": "firstname"
},
{
"nodeType": "literal",
"value": [
"Ramen",
"John"
]
}
]
}
]
}
}
isNull
運運算元如需運運算元的範例,請參閱下方的裝載範例 isNull
.
{
"type": "PQL",
"format": "pql/json",
"value": {
"nodeType": "fnApply",
"fnName": "isNull",
"params": [
{
"nodeType": "fieldLookup",
"fieldName": "complaint_type"
}
]
}
}
NOT
運運算元如需運運算元的範例,請參閱下方的裝載範例 NOT
.
{
"type": "PQL",
"format": "pql/json",
"value": {
"nodeType": "fnApply",
"fnName": "NOT",
"params": [
{
"nodeType": "fnApply",
"fnName": "isNull",
"params": [
{
"nodeType": "fieldLookup",
"fieldName": "complaint_type"
}
]
}
]
}
}
如需複雜巢狀條件的範例,請參閱下列裝載範例。
{
"type": "PQL",
"format": "pql/json",
"value": {
"nodeType": "fnApply",
"fnName": "and",
"params": [
{
"nodeType": "fnApply",
"fnName": ">=",
"params": [
{
"nodeType": "fieldLookup",
"fieldName": "age"
},
{
"nodeType": "literal",
"value": 20
}
]
},
{
"nodeType": "fnApply",
"fnName": "<=",
"params": [
{
"nodeType": "fieldLookup",
"fieldName": "age"
},
{
"nodeType": "literal",
"value": 30
}
]
},
{
"nodeType": "fnApply",
"fnName": "or",
"params": [
{
"nodeType": "fnApply",
"fnName": "!=",
"params": [
{
"nodeType": "fieldLookup",
"fieldName": "city"
},
{
"nodeType": "literal",
"value": "PUD"
}
]
},
{
"nodeType": "fnApply",
"fnName": "=",
"params": [
{
"nodeType": "fieldLookup",
"fieldName": "joinedDate"
},
{
"nodeType": "literal",
"value": "2020-04-22"
}
]
}
]
}
]
}
}