跳至主要内容
版本:v4

Materialized View API

Authorization

使用 Materialized View API 前,需要在 Canner Enterprise 先建立 Personal Access Token 並將生成的 Token 複製放置於 Header 中。

NameFormatSample
Authorizationformat("Token %s", PAT)Token asdsafgwg4gregregergergregerg32

Error Handling

常見的 Error 有以下幾種形式。

StatusDescription
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

NameDescriptionSample
{workspace_sql_name}填入 Workspace 中的 SQL Name
test_21512
{materialized_view_sql_name}填入 Materialized View 的 SQL Nameasia_customer
  • {workspace_sql_name} 取得方式: 在 Workspace 頁面中,點擊 Share 按鈕能取得該資訊。

    1_mview_api

  • {materialized_view_sql_name} 取得方式: 在 Workspace 頁面中,先篩選出 Materialized View,並可取得。

    2_mview_api

Query Parameters

提供四種 Query Parameters 可以加在 URL 中,進行資料的篩選。

NameDescriptionSampleDefault Value
limit回傳的資料數量數值,如100Null,代表回傳所有資料
offset略過資料筆數,第一項為 0數值,如0Null,代表不略過資料
filters設定欄位條件,可以進行多個條件設定columnName:operator:valueNull,代表不設定欄位篩選條件
columnNames選取的欄位,若有多個欄位需要以 "," 進行分隔column1,column2Null,代表選取所有欄位

filters 中,可以使用的 operator 值如下

Operator NameDescriptionSample
EQ=column1:EQ:aa
LT<column2:LT:123
LTE<=column2:LTE:123
GT>column2:GT:123
GTE>=column2:GTE:123
ISis ; use for nullcolumn2:IS:null
IS_NOTis not ; use for nullcolumn2: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,設定以下參數作為範例說明

NameValueDescription
limit5回傳 5 筆資料
offset0略過第 0 筆資料
filterscustkey:GT:4篩選 custkey 欄位值大於 4
columnNamescustkey,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,設定以下參數作為範例說明

NameValueDescription
filterscustkey:LT:6篩選 custkey 欄位值小於 6
filtersnationkey: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")
  1. 開頭需符合 -- Canner API,中間需包含空格字符
  2. 設定 Column 及篩選條件需使用雙引號
  3. 篩選條件可參考上方,Query Parameters 中 filters 參數可執行的操作。例如 eq,lt,gt 等
  4. 所有條件僅能撰寫在一行註解中,若有多行註解,僅有第一行註解會有效執行,其他則無效

實際範例

在 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 中的 filterscustkey: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 ."
}