Display the SQL definition of a hive view

Question

How to display the view definition of a hive view in its SQL form. Most relational databases supports commands like

SHOW CREATE VIEW viewname;

score 37 · Accepted Answer · answered Jul 07 '14 at 01:34

37

Use show create table.

Read the hive manual for more detail.

answered Jul 07 '14 at 01:34

pensz

1,871
1
13
18

score 6 · Answer 2 · answered Nov 02 '17 at 15:56

6

It is DESCRIBE [EXTENDED|FORMATTED] also. show create table doesn't give enough information sometimes, but DESCRIBE always does;

For a view, DESCRIBE EXTENDED or FORMATTED can be used to retrieve the view's definition. Two relevant attributes are provided: both the original view definition as specified by the user, and an expanded definition used internally by Hive.

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-DescribeTable/View/Column

answered Nov 02 '17 at 15:56

aryndin

591
5
18

1

This command works even if you user does not have access to execute "show create table" command +1. – Gaurav Jan 05 '18 at 07:02
This works for me. SHOW CREATE TABLE gives me only a part of the statement. – Rajitha Fernando May 09 '21 at 03:29

score 2 · Answer 3 · answered Aug 19 '15 at 15:45

2

SHOW CREATE table ;

Even thought its a view's definition that you'd like to get, the 'table' keyword has to be mentioned.

answered Aug 19 '15 at 15:45

Jonathan

144
4
13

score 2 · Answer 4 · edited Jun 20 '20 at 09:12

2

Yeah, You can use SHOW CREATE TABLE to display the CREATE VIEW statement that created a view.

Link for reference: Hive CREATE VIEW

edited Jun 20 '20 at 09:12

Community

1
1

answered Aug 19 '15 at 17:30

Anil

420
2
16

Display the SQL definition of a hive view

4 Answers4