How to get the CREATE query of an existing materialized view in Hive?

Question

I want to extract the entire create query of a materialized view present in Hive. How can I do that? I have the underlying query for data but I want the create query to include storage characteristics, etc.

Answer 1

Two options -

1. use describe formatted mv_name anad then refer to the 'view original text' line for view SQL.
   xx is the name of materialized view

2. if you are using hue, then you can use table browser to check MV definition/SQL used to create it.

Here is a screenshot on how to get the SQL - xx is the name of materialized view. Create statement below.

CREATE MATERIALIZED VIEW xx AS SELECT * FROM activity;

2.1 Click on 'i' and then click on 'Table Browser'.

2.2 Click on 'view sql' to see view query.