Materialized View Refresh API

This topic explains how to use the Materialized View Refresh API.

In this topic:

Introduction

The following endpoints enable you to refresh the Materialized Views:

Endpoints

Refresh a Materialized View

Use this endpoint to refresh the identified materialized view asynchronously.

Request URL

PUT /v1/{accesskey}/admin/materializedviews/{materializedViewID}/refresh

Retrieving the status of Materialized View refresh

Use this endpoint to retrieve the progress or result of latest materialized view refresh run.

Parameter Type Default Value Required? Description
limit Integer Default 1 if unspecified or < 1. No

To allow fetching more than one of the latest refresh executions (always ordered by most recent first).

Request URL

GET /v1/{accesskey}/admin/materializedviews/{materializedViewID}/refresh

Response

A successful response returns details about the specified materialzed view.

The status indicates:

  • SUCCEEDED: Job run completed successfully.

  • FAILED: Job run failed. additionalInfo holds more details of the reason for failure.

  • STOPPED: Job run was stopped.

[
  {
    "viewName": "TEST_CUSTOMER_ADDRESS_JOIN_MV",
    "runTS": 1618227796080,
    "actualStartTS": 1618227796081,
    "runDurationSeconds": 0,
    "cpuUsedSeconds": 0,
    "status": "FAILED",
    "additionalInfo": "ORA-01031: insufficient privileges\nORA-06512: at \"SYS.DBMS_SNAPSHOT_KKXRCA\", line 3160\nORA-06512: at \"SYS.DBMS_SNAPSHOT_KKXRCA\", line 3140\nORA-06512: at \"SYS.DBMS_SNAPSHOT_KKXRCA\", line 3247\nORA-06512: at \"SYS.DBMS_SNAPSHOT_KKXRCA\", line 3287\nORA-06512: at \"SYS.DBMS_SNAPSHOT\", line 16\nORA-06512: at line 1\n"
  },
  {
    "viewName": "TEST_CUSTOMER_ADDRESS_JOIN_MV",
    "runTS": 1617265885573,
    "actualStartTS": 1617265890696,
    "runDurationSeconds": 0,
    "cpuUsedSeconds": 0,
    "status": "SUCCEEDED",
    "additionalInfo": null
  }
]

Error codes

For successful responses, we get a 20x response code. In case of a failed response, we get one of the following error codes:

  • 401 : Unauthorized

  • 403 : Forbidden

  • 404 : Not Found

  • 422 : Unprocessable Entity (already exists, illegal input/parameter, invalid format/object)

  • 500 : Internal Error

Sample Error Response

{
  "errorMessage": "No materialized view found with ID = CUST_ORDER_JOINS_CHEDULED",
  "httpstatus": "404"
}

Logging

Currently, no explicit job logs are added for materialized view refresh events. However, refresh executions and the execution information (time to complete and result) can be viewed using the GET refresh status API.

When a job stage completion trigger triggered such a refresh, the time for this refresh to complete would have added that much time to the (DW or Analytics) job completion time.

Learn more

Materialized Views

Materialized View Metadata API