SHOW ALTER TABLE MATERIALIZED VIEW

Description

This command is used to view the execution of creating materialized views submitted by CREATE-MATERIALIZED-VIEW statement.

This statement is equivalent to SHOW ALTER TABLE ROLLUP;

SHOW ALTER TABLE MATERIALIZED VIEW
[FROM database]
[WHERE]
[ORDER BY]
[LIMIT OFFSET]

• database: view jobs under specified database. If not specified, use the current database.

• WHERE: support filtering the results, currently only the following columns can be filtered:

  • TableName: only equivalent filtering is supported.
  • State：Only equivalent filtering is supported.
  • Createtime/FinishTime: support =, >=, <=, >, <, !=
• ORDER BY: support ordering result sets by any column.
• LIMIT: support page turning and query in combination with ORDER BY.

Description of return results:

mysql> show alter table materialized view\G
*************************** 1. row ***************************
          JobId: 11001
      TableName: tbl1
     CreateTime: 2020-12-23 10:41:00
     FinishTime: NULL
  BaseIndexName: tbl1
RollupIndexName: r1
       RollupId: 11002
  TransactionId: 5070
          State: WAITING_TXN
            Msg:
       Progress: NULL
        Timeout: 86400
1 row in set (0.00 sec)

• JobId：Unique ID of a job.
• TableName：Base table name
• CreateTime/FinishTime：job create time and finish time.
• BaseIndexName/RollupIndexName：Base table name and materialized view name.
• RollupId：Unique ID of materialized view.
• TransactionId：See description of State field.

• State：Job status.

  • PENDING：preparing for job.

  • WAITING_TXN：

    Before officially generating materialized view data, wait for the running load job on the current table to finish. And theTransactionId field is the ID of the job currently waiting for. When the load jobs before this ID have been completed, the job will start actually.

  • RUNNING：job in running.
  • FINISHED：job finished successfully.
  • CANCELLED：job failed.
• Msg: error message
• Progress：job progress. The progress here is expressed as Number of tablet completed/total number of tablet. The materialized views are created by the granularity of tablet.
• Timeout：timeout period of job, in seconds.

Example

1. View materialized view jobs under database example_db

   SHOW ALTER TABLE MATERIALIZED VIEW FROM example_db;

Keywords

SHOW, ALTER, TABLE, MATERIALIZED, VIEW

​