Materialized View API
For Canner Enterprise v1.x.x Documentation, we only provide Chinese version available.
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 中的 Config 頁面取得{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 | 設定欄位條件,可以進行多個條件設定 | columnNmae: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 ."
}