Create a materialized view

Creates a materialized view from a SQL SELECT and materializes its initial contents.

POST /v1/tenants/{tenant}/sql/materialized-views

Save a query result as a viewAn on-demand view needs refreshes to reflect later source changes.

Define the view querySupply SELECT, source table and refresh mode.
Materialize the initial resultStore the rows produced by that query.
Inspect rows and refresh timeUse the counts and timestamp to verify creation.

Before you start

Use your database bearer token.

Connection setup

Create and populate the source table before creating its view. The example reads demo.people.

Path parameters

Field	Type	Required
`tenant`	`string`	Required

Headers

Field	Type	Required	Description
`x-oc-db-user`	`string`	Optional	Acting database user (role, grant and row-policy checks). Send with x-oc-db-key.
`x-oc-db-key`	`string`	Optional	API key of the x-oc-db-user user.

Request body

application/json

Limits and accepted values

refresh_mode is "on_demand" (default;
stale until refreshed) or "incremental" (maintained on every write;
only filter/projection views and COUNT/SUM/AVG rollups qualify, otherwise 422).
source_schema optionally names the base table as "namespace.table" and must agree with the query's FROM.
auto=true chooses incremental when the query shape allows it and on_demand otherwise. 409 when the name already exists.

Field	Type	Required
`name`	`string`	Required
`query`	`string`	Required
`refresh_mode`	`string`	Optional
`source_schema`	`string`	Optional

rows_materialized reports the initial stored rows and refresh_ts identifies the refresh. An on_demand view becomes stale until refreshed; choosing it does not keep the view current automatically.

Response statuses

HTTP status reference

Status	What to do
200	OK
400	Correct the request before retrying.
401	Check the credential and Authorization header.
402	Check feature access or available credits.
403	Check tenant access, roles and permissions.
409	Resolve the conflict before retrying.
413	Split or reduce the request body.
415	Send the required Content-Type.
422	Correct the values or unsupported combination.
429	Honor Retry-After when present; back off.
500	Check the outcome before retrying a write.
501	Check deployment support for this operation.
502	Check service availability and the write outcome.
503	Honor Retry-After when present; check write outcomes.

Request example

curl --include --request POST "${OC_BASE_URL}/v1/tenants/${OC_TENANT}/sql/materialized-views" \
  --header "Authorization: Bearer ${OC_TOKEN}" \
  --header "Content-Type: application/json" \
  --data-binary '{
  "name": "demo_scores",
  "query": "SELECT id, score FROM demo.people",
  "source_schema": "demo.people",
  "refresh_mode": "on_demand"
}'

Response example

Examples use synthetic values to show the response format.

Response status

{
  "name": "demo_scores",
  "rows_materialized": 3,
  "bytes_written": 128,
  "refresh_ts": 1793318400000
}

Illustrative materialization counts and timestamp; refresh before reading current on-demand results.

Download the OpenAPI specification for the complete response schemas.

Before you startCopy Anchor Link

Path parametersCopy Anchor Link

HeadersCopy Anchor Link

Request bodyCopy Anchor Link

Read the resultCopy Anchor Link

Response statusesCopy Anchor Link

Before you start

Path parameters

Headers

Request body

Read the result

Response statuses