[IMPALA-783] Suggestion: SHOW CREATE VIEW to complement SHOW CREATE TABLE

• Type: New Feature
• Status: Resolved
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: Impala 1.2.3
• Fix Version/s: Impala 2.5.0
• Component/s: Frontend
• Labels: ramp-up
• Target Version: Product Backlog
• Assignee: Tim Armstrong
• Reporter: John Russell
• Created: 03/Feb/14 23:16
• Updated: 09/May/18 16:32
• Resolved: 20/Jan/16 05:00

Description

If you loop through all the output lines from SHOW TABLES and do a SHOW CREATE TABLE on each one, you end up with errors like so for any views:

ERROR: AnalysisException: SHOW CREATE TABLE not supported on VIEW: sandbox.maritime_provinces
Could not execute command: show create table `sandbox`.`maritime_provinces`

MySQL has a SHOW CREATE VIEW command to go along with SHOW CREATE TABLES:

https://dev.mysql.com/doc/refman/5.6/en/show-create-view.html

That seems like a logical complement for Impala too, to avoid having to go do an entirely different road (do DESCRIBE FORMATTED, parse out the view creation text).

That still leaves the question of how one would know ahead of time when to do SHOW CREATE TABLE vs. SHOW CREATE VIEW, since there is no SHOW VIEWS statement, and SHOW TABLES prints both tables and views with no indication of which is which. MySQL has a variation SHOW FULL TABLES which gives more verbose output, identifying which items are tables and which are views:

https://dev.mysql.com/doc/refman/5.6/en/show-tables.html

Let's consider this FULL keyword or some equivalent syntax for Impala too. (Also OK would be some modifier for SHOW TABLES to cause it to list only tables, or only views, or both at the same time.)

Sub-Tasks

• Impala Doc: SHOW CREATE VIEW: Closed