Materialized View API
Authorization
Before using the Materialized View API, you must create a Personal Access Token in Canner Enterprise and place the generated Token copy in the Header.
Name | Format | Sample |
---|---|---|
Authorization | format("Token %s", PAT) | Token asdsafgwg4gregregergergregerg32 |
Error Handling
Common Errors have the following forms.
Status | Description |
---|---|
400 | Parameter setting error, for example, the Materialized View does not exist in Column |
401 | Authorization failed, such as incorrectly filling in Token information or reading unauthorized Workspace |
404 | The resource cannot be found, such as no such Workspace or Materialized View |
URL
Append /v1/data
to the Canner Enterprise URL
URL example
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} | Fill in the SQL Name in Workspace | test_21512 |
{materialized_view_sql_name} | Fill in the SQL Name of the Materialized View | asia_customer |
How to obtain
{workspace_sql_name}
: On the Workspace page, click theShare
button to get this information.
How to obtain
{materialized_view_sql_name}
: On the Workspace page, filter out the Materialized View and get it.
Query Parameters
Provides four Query Parameters that can add to the URL to filter data.
Name | Description | Sample | Default Value |
---|---|---|---|
limit | the number of returned data | value, such as 100 | Null, means to return all data |
offset | The number of skipped data, the first item is 0 | Numeric value, such as 0 | Null means not to skip the data |
filters | Set column conditions, you can set multiple conditions | columnName:operator:value | Null, means no field filter conditions |
columnNames | Selected fields, if there are multiple columns that need to be separated by "," | column1,column2 | Null means select all fields |
In filters
, the operator
values can use are as follows.
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 |
Example 1: Set to return all data
Request example
GET http://13.231.123.245/v1/data/test_21512/materializedView/asia_customer
Response example
We can get all the data of this Materialized View; the result will return in JSON format.
[
{
"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 slylyregular 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"
}
]
Example 2: Set the number of returned rows, columns, and filter conditions
Request example
Use asia_customer
in this Materialized View, and set the following parameters as an example
Name | Value | Description |
---|---|---|
limit | 5 | Return 5 records |
offset | 0 | Skip the 0th data |
filters | custkey:GT:4 | filter for custkey field values greater than 4 |
columnNames | custkey,name,address | select custkey,name,address columns |
URL example
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 example
[
{
"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"
}
]
Example 3: Set multiple columns filter conditions
Use asia_customer
in this Materialized View, and set the following parameters as an example.
Name | Value | Description |
---|---|---|
filters | custkey:LT:6 | filter for custkey field values less than 6 |
filters | nationkey:EQ:3 | filter nationkey field value equal to 3 |
URL example
GET http://13.231.123.245/v1/data/test_21512/materializedView/asia_customer?filters=custkey:LT:6&filters=nationkey:EQ:3
Response example
[
{
"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
When writing the SQL Statement of Materialized View, you can set the required parameters of Materialized View API through Comment. When making a Request, the filters
parameter also needs to meet the filter conditions in the Comment in order to successfully obtain the data.
SQL Comment writing format and conditions
The writing format must fully comply with the following format
-- Canner API: required("column1:eq", "column2")
- The beginning must conform to
-- Canner API
, and the middle must contain space characters - Set the Column and filter conditions to use double quotes
"
- For filter conditions, please refer to the operations that the
filters
parameter can perform in Query Parameters above. e.g., eq, lt, gt, etc. - All conditions can only be written in one line of comments. If there are multiple lines of comments, only the first line of comments will execute, and the others will be ignored.
Practical example
Create a Materialized View in Canner Enterprise, and write the following SQL Comment.
-- Canner API: required("custkey:gt", "nationkey")
select*
from customer_86444
Limit 100
This example means that when sending a Request, filters
in Query Parameters need to set the same filter conditions as in SQL Comment to successfully obtain data.
"custkey:gt"
: Indicates thatfilters
in Query Parameters need to have a filter condition that the value of the fieldcustkey
is greater than"nationkey"
: Indicates thatfilters
in Query Parameters require a filter condition with the fieldnationkey
value.
Request example
Set filters
in Query Parameters to custkey:gt:4
and nationkey:eq:2
, which means to filter the data whose field custkey
is greater than 4 and the column nationkey
is equal to 2.
http://13.231.123.245/v1/data/test_21512/materializedView/test12?filters=custkey:gt:4&filters=nationkey:eq:2
Response example
[
{
"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. furious"
},
{
"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"
}
]
If the filters
parameter in Query Parameters is not set, users cannot get the result successfully.
{
"code": "GENERIC_USER_ERROR",
"message": "Required filters and request filters not matched: custkey:GT, nationkey and ."
}