Materialized View API
Authorization
使用 Materialized View API 前,需要在 Canner Enterprise 先建立 Personal Access Token 並將生成的 Token 複製放置於 Header 中。
Name | Format | Sample |
---|---|---|
Authorization | format("Token %s", PAT) | Token asdsafgwg4gregregergergregerg32 |
Error Handling
常見的 Error 有以下幾種形式。
Status | Description |
---|---|
400 | 參數設定錯誤,例如 Column 不存在此 Materialized View |
401 | 授權失敗,例如 Token 資訊填寫錯誤或是讀取未授權的 Workspace |
404 | 找不到該資源,例如沒有該 Workspace 或 Materialized View |
URL
在 Canner Enterprise 的 URL 中後面加上 /v1/data
URL 範例
http://13.231.123.245/v1/data
Materialized View API
HTTP Request
GET http://13.231.123.245/v1/data/{workspace_sql_name}/materializedView/{materialized_view_sql_name}`
Path Parameters
Name | Description | Sample |
---|---|---|
{workspace_sql_name} | 填入 Workspace 中的 SQL Name | test_21512 |
{materialized_view_sql_name} | 填入 Materialized View 的 SQL Name | asia_customer |
{workspace_sql_name}
取得方式: 在 Workspace 頁面中,點擊Share
按鈕能取得該資訊。
{materialized_view_sql_name}
取得方式: 在 Workspace 頁面中,先篩選出 Materialized View,並可取得。
Query Parameters
提供四種 Query Parameters 可以加在 URL 中,進行資料的篩選。
Name | Description | Sample | Default Value |
---|---|---|---|
limit | 回傳的資料數量 | 數值,如100 | Null,代表回傳所有資料 |
offset | 略過資料筆數,第一項為 0 | 數值,如0 | Null,代表不略過資料 |
filters | 設定欄位條件,可以進行多個條件設定 | columnName:operator:value | Null,代表不設定欄位篩選條件 |
columnNames | 選取的欄位,若有多個欄位需要以 "," 進行分隔 | column1,column2 | Null,代表選取所有欄位 |
在 filters
中,可以使用的 operator
值如下
Operator Name | Description | Sample |
---|---|---|
EQ | = | column1:EQ:aa |
LT | < | column2:LT:123 |
LTE | <= | column2:LTE:123 |
GT | > | column2:GT:123 |
GTE | >= | column2:GTE:123 |
IS | is ; use for null | column2:IS:null |
IS_NOT | is not ; use for null | column2:IN_NOT:null |
範例 1: 設定回傳所有資料
Request 範例
GET http://13.231.123.245/v1/data/test_21512/materializedView/asia_customer
Response 範例
我們可以取得此張 Materialized View 的全部資料,資料以 JSON 格式進行回傳。
[
{
"custkey": 1,
"name": "Customer#000000001",
"address": "IVhzIApeRb ot,c,E",
"nationkey": 15,
"phone": "25-989-741-2988",
"acctbal": 711.56,
"mktsegment": "BUILDING",
"comment": "to the even, regular platelets. regular, ironic epitaphs nag e"
},
{
"custkey": 2,
"name": "Customer#000000002",
"address": "XSTf4,NCwDVaWNe6tEgvwfmRchLXak",
"nationkey": 13,
"phone": "23-768-687-3665",
"acctbal": 121.65,
"mktsegment": "AUTOMOBILE",
"comment": "l accounts. blithely ironic theodolites integrate boldly: caref"
},
{
"custkey": 3,
"name": "Customer#000000003",
"address": "MG9kdTD2WBHm",
"nationkey": 1,
"phone": "11-719-748-3364",
"acctbal": 7498.12,
"mktsegment": "AUTOMOBILE",
"comment": " deposits eat slyly ironic, even instructions. express foxes detect slyly. blithely even accounts abov"
},
{
"custkey": 4,
"name": "Customer#000000004",
"address": "XxVSJsLAGtn",
"nationkey": 4,
"phone": "14-128-190-5944",
"acctbal": 2866.83,
"mktsegment": "MACHINERY",
"comment": " requests. final, regular ideas sleep final accou"
},
{
"custkey": 5,
"name": "Customer#000000005",
"address": "KvpyuHCplrB84WgAiGV6sYpZq7Tj",
"nationkey": 3,
"phone": "13-750-942-6364",
"acctbal": 794.47,
"mktsegment": "HOUSEHOLD",
"comment": "n accounts will have to unwind. foxes cajole accor"
},
{
"custkey": 6,
"name": "Customer#000000006",
"address": "sKZz0CsnMD7mp4Xd0YrBvx,LREYKUWAh yVn",
"nationkey": 20,
"phone": "30-114-968-4951",
"acctbal": 7638.57,
"mktsegment": "AUTOMOBILE",
"comment": "tions. even deposits boost according to the slyly bold packages. final accounts cajole requests. furious"
},
{
"custkey": 7,
"name": "Customer#000000007",
"address": "TcGe5gaZNgVePxU5kRrvXBfkasDTea",
"nationkey": 18,
"phone": "28-190-982-9759",
"acctbal": 9561.95,
"mktsegment": "AUTOMOBILE",
"comment": "ainst the ironic, express theodolites. express, even pinto beans among the exp"
},
{
"custkey": 8,
"name": "Customer#000000008",
"address": "I0B10bB0AymmC, 0PrRYBCP1yGJ8xcBPmWhl5",
"nationkey": 17,
"phone": "27-147-574-9335",
"acctbal": 6819.74,
"mktsegment": "BUILDING",
"comment": "among the slyly regular theodolites kindle blithely courts. carefully even theodolites haggle slyly along the ide"
},
{
"custkey": 9,
"name": "Customer#000000009",
"address": "xKiAFTjUsCuxfeleNqefumTrjS",
"nationkey": 8,
"phone": "18-338-906-3675",
"acctbal": 8324.07,
"mktsegment": "FURNITURE",
"comment": "r theodolites according to the requests wake thinly excuses: pending requests haggle furiousl"
},
{
"custkey": 10,
"name": "Customer#000000010",
"address": "6LrEaV6KR6PLVcgl2ArL Q3rqzLzcT1 v2",
"nationkey": 5,
"phone": "15-741-346-9870",
"acctbal": 2753.54,
"mktsegment": "HOUSEHOLD",
"comment": "es regular deposits haggle. fur"
}
]
範例 2: 設定回傳資料筆數、欄位及篩選條件
Request 範例
使用 asia_customer
此張 Materialized View,設定以下參數作為範例說明
Name | Value | Description |
---|---|---|
limit | 5 | 回傳 5 筆資料 |
offset | 0 | 略過第 0 筆資料 |
filters | custkey:GT:4 | 篩選 custkey 欄位值大於 4 |
columnNames | custkey,name,address | 選取 custkey,name,address 欄位 |
URL 範例
GET http://13.231.123.245/v1/data/test_21512/materializedView/asia_customer?limit=5&offset=0&filters=custkey:GT:4&columnNames=custkey,name,address
Response 範例
[
{
"custkey": 5,
"name": "Customer#000000005",
"address": "KvpyuHCplrB84WgAiGV6sYpZq7Tj"
},
{
"custkey": 6,
"name": "Customer#000000006",
"address": "sKZz0CsnMD7mp4Xd0YrBvx,LREYKUWAh yVn"
},
{
"custkey": 7,
"name": "Customer#000000007",
"address": "TcGe5gaZNgVePxU5kRrvXBfkasDTea"
},
{
"custkey": 8,
"name": "Customer#000000008",
"address": "I0B10bB0AymmC, 0PrRYBCP1yGJ8xcBPmWhl5"
},
{
"custkey": 9,
"name": "Customer#000000009",
"address": "xKiAFTjUsCuxfeleNqefumTrjS"
}
]
範例 3: 設定多個欄位篩選條件
使用 asia_customer
此張 Materialized View,設定以下參數作為範例說明
Name | Value | Description |
---|---|---|
filters | custkey:LT:6 | 篩選 custkey 欄位值小於 6 |
filters | nationkey:EQ:3 | 篩選 nationkey 欄位值等於 3 |
URL 範例
GET http://13.231.123.245/v1/data/test_21512/materializedView/asia_customer?filters=custkey:LT:6&filters=nationkey:EQ:3
Response範例
[
{
"custkey": 5,
"name": "Customer#000000005",
"address": "KvpyuHCplrB84WgAiGV6sYpZq7Tj",
"nationkey": 3,
"phone": "13-750-942-6364",
"acctbal": 794.47,
"mktsegment": "HOUSEHOLD",
"comment": "n accounts will have to unwind. foxes cajole accor"
}
]
Set Required Filters
撰寫 Materialized View 的 SQL Statement 時,可以透過 Comment 設定 Materialized View API 的必填參數。進行 Request 時,filters
這個參數也需要符合 Comment 中的篩選條件,才能成功獲取資料。
SQL Comment 撰寫格式及條件
撰寫格式需完全符合以下格式
-- Canner API: required("column1:eq", "column2")
- 開頭需符合
-- Canner API
,中間需包含空格字符 - 設定 Column 及篩選條件需使用雙引號
“
- 篩選條件可參考上方,Query Parameters 中
filters
參數可執行的操作。例如 eq,lt,gt 等 - 所有條件僅能撰寫在一行註解中,若有多行註解,僅有第一行註解會有效執行,其他則無效
實際範例
在 Canner Enterprise 中建立 Materialized View,並撰寫以下 SQL Comment。
-- Canner API: required("custkey:gt", "nationkey")
select *
from customer_86444
Limit 100
此範例代表在發送 Request 時,Query Parameters 中的 filters
需要設定與 SQL Comment 中相同的篩選條件,才能成功取得資料。
"custkey:gt"
: 代表 Query Parameters 中的filters
需要帶有欄位custkey
的值大於的篩選條件"nationkey"
: 代表 Query Parameters 中的filters
需要帶有欄位nationkey
的值的篩選條件
Request 範例
設定 Query Parameters 中的 filters
為 custkey:gt:4
以及 nationkey:eq:2
,代表篩選欄位 custkey
大於 4 且欄位 nationkey
等於 2 的資料。
http://13.231.123.245/v1/data/test_21512/materializedView/test12?filters=custkey:gt:4&filters=nationkey:eq:2
Response 範例
[
{
"custkey": 17,
"name": "Customer#000000017",
"address": "izrh 6jdqtp2eqdtbkswDD8SG4SzXruMfIXyR7",
"nationkey": 2,
"phone": "12-970-682-3487",
"acctbal": 6.34,
"mktsegment": "AUTOMOBILE",
"comment": "packages wake! blithely even pint"
},
{
"custkey": 39,
"name": "Customer#000000039",
"address": "nnbRg,Pvy33dfkorYE FdeZ60",
"nationkey": 2,
"phone": "12-387-467-6509",
"acctbal": 6264.31,
"mktsegment": "AUTOMOBILE",
"comment": "tions. slyly silent excuses slee"
},
{
"custkey": 47,
"name": "Customer#000000047",
"address": "b0UgocSqEW5 gdVbhNT",
"nationkey": 2,
"phone": "12-427-271-9466",
"acctbal": 274.58,
"mktsegment": "BUILDING",
"comment": "ions. express, ironic instructions sleep furiously ironic ideas. furi"
},
{
"custkey": 72,
"name": "Customer#000000072",
"address": "putjlmskxE,zs,HqeIA9Wqu7dhgH5BVCwDwHHcf",
"nationkey": 2,
"phone": "12-759-144-9689",
"acctbal": -362.86,
"mktsegment": "FURNITURE",
"comment": "ithely final foxes sleep always quickly bold accounts. final wat"
},
{
"custkey": 92,
"name": "Customer#000000092",
"address": "obP PULk2LH LqNF,K9hcbNqnLAkJVsl5xqSrY,",
"nationkey": 2,
"phone": "12-446-416-8471",
"acctbal": 1182.91,
"mktsegment": "MACHINERY",
"comment": ". pinto beans hang slyly final deposits. ac"
}
]
若未設定 Query Parameters 中的 filters
參數,則無法成功獲取資料。
{
"code": "GENERIC_USER_ERROR",
"message": "Required filters and request filters not matched: custkey:GT, nationkey and ."
}