版本：v1

Materialized View API

Chinese Version Only

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 ."
}

Authorization​

Error Handling​

URL​

Materialized View API​

Path Parameters​

Query Parameters​

範例 1: 設定回傳所有資料​

範例 2: 設定回傳資料筆數、欄位及篩選條件​

範例 3: 設定多個欄位篩選條件​

Set Required Filters​

Authorization

Error Handling

URL

Materialized View API

Path Parameters

Query Parameters

範例 1: 設定回傳所有資料

範例 2: 設定回傳資料筆數、欄位及篩選條件

範例 3: 設定多個欄位篩選條件

Set Required Filters