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 the Share 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")

1. The beginning must conform to -- Canner API, and the middle must contain space characters
2. Set the Column and filter conditions to use double quotes "
3. For filter conditions, please refer to the operations that the filters parameter can perform in Query Parameters above. e.g., eq, lt, gt, etc.
4. 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 that filters in Query Parameters need to have a filter condition that the value of the field custkey is greater than "nationkey": Indicates that filters in Query Parameters require a filter condition with the field nationkey 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 ."
}