Skip to main content
Version: v2

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.

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

Error Handling

Common Errors have the following forms.

StatusDescription
400Parameter setting error, for example, the Materialized View does not exist in Column
401Authorization failed, such as incorrectly filling in Token information or reading unauthorized Workspace
404The 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

NameDescriptionSample
{workspace_sql_name}Fill in the SQL Name in Workspace
test_21512
{materialized_view_sql_name}Fill in the SQL Name of the Materialized Viewasia_customer
  • How to obtain {workspace_sql_name}: On the Workspace page, click the Share button to get this information.

    1_mview_api

  • How to obtain {materialized_view_sql_name}: On the Workspace page, filter out the Materialized View and get it.

    2_mview_api

Query Parameters

Provides four Query Parameters that can add to the URL to filter data.

NameDescriptionSampleDefault Value
limitthe number of returned datavalue, such as 100Null, means to return all data
offsetThe number of skipped data, the first item is 0Numeric value, such as 0Null means not to skip the data
filtersSet column conditions, you can set multiple conditionscolumnName:operator:valueNull, means no field filter conditions
columnNamesSelected fields, if there are multiple columns that need to be separated by ","column1,column2Null means select all fields

In filters, the operator values can use are as follows.

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

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

NameValueDescription
limit5Return 5 records
offset0Skip the 0th data
filterscustkey:GT:4filter for custkey field values greater than 4
columnNamescustkey,name,addressselect 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.

NameValueDescription
filterscustkey:LT:6filter for custkey field values less than 6
filtersnationkey:EQ:3filter 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 ."
}