How to determine the name of an Impala object corresponds to a view

user_446279 0
Viewed 283

Is there a way in Impala to determine whether an object name returned by SHOW TABLES corresponds to a table or a view since:

  • this statement only return the object names, without their type
  • SHOW CREATE VIEW is just an alias for SHOW CREATE TABLE (same result, no view/table distinction)
  • DESCRIBE does not give any clue about the type of the item

Ideally I'd like to list all the tables + views and their types using a single operation, not one to retrieve the tables + views and then another call for each name to determine the type of the object.

(please note the question is about Impala, not Hive)

impala
1 Answers

You can use describe formatted to know the type of an object

impala-shell> CREATE TABLE table2(
  id INT,
  name STRING
);

impala-shell> CREATE VIEW view2 AS SELECT * FROM table2;

impala-shell> DESCRIBE FORMATTED table2;

+------------------------------+--------------------------------------------------------------------+----------------------+
| name                         | type                                                               | comment              |
+------------------------------+--------------------------------------------------------------------+----------------------+
| Retention:                   | 0                                                                  | NULL                 |
| Location:                    | hdfs://quickstart.cloudera:8020/user/hive/warehouse/test.db/table2 | NULL                 |
| Table Type:                  | MANAGED_TABLE                                                      | NULL                 |
+------------------------------+--------------------------------------------------------------------+----------------------+

impala-shell> DESCRIBE FORMATTED view2;

+------------------------------+-------------------------------+----------------------+
| name                         | type                          | comment              |
+------------------------------+-------------------------------+----------------------+
| Protect Mode:                | None                          | NULL                 |
| Retention:                   | 0                             | NULL                 |
| Table Type:                  | VIRTUAL_VIEW                  | NULL                 |
| Table Parameters:            | NULL                          | NULL                 |
|                              | transient_lastDdlTime         | 1601632695           |
|                              | NULL                          | NULL                 |
| # Storage Information        | NULL                          | NULL                 |

+------------------------------+-------------------------------+----------------------+

In the case of the table type is Table Type: MANAGED_TABLE and for the view is Table Type: VIRTUAL_VIEW

Other way is querying metastore database (if you can) to know about metadata in Impala(or Hive)

mysql> use metastore;
mysql> select * from TBLS;
+--------+-------------+-------+------------------+-----------+-----------+-------+----------+---------------+------------------------------------------------------------+---------------------------+----------------+
| TBL_ID | CREATE_TIME | DB_ID | LAST_ACCESS_TIME | OWNER     | RETENTION | SD_ID | TBL_NAME | TBL_TYPE      | VIEW_EXPANDED_TEXT                                         | VIEW_ORIGINAL_TEXT        | LINK_TARGET_ID |
+--------+-------------+-------+------------------+-----------+-----------+-------+----------+---------------+------------------------------------------------------------+---------------------------+----------------+
|   9651 |  1601631971 |  9331 |                0 | anonymous |         0 | 27996 | table1   | MANAGED_TABLE | NULL                                                       | NULL                      |           NULL |
|   9652 |  1601632121 |  9331 |                0 | anonymous |         0 | 27997 | view1    | VIRTUAL_VIEW  | SELECT `table1`.`id`, `table1`.`name` FROM `test`.`table1` | SELECT * FROM table1      |           NULL |
|   9653 |  1601632676 |  9331 |                0 | cloudera  |         0 | 27998 | table2   | MANAGED_TABLE | NULL                                                       | NULL                      |           NULL |
|   9654 |  1601632695 |  9331 |                0 | cloudera  |         0 | 27999 | view2    | VIRTUAL_VIEW  | SELECT * FROM test.table2                                  | SELECT * FROM test.table2 |           NULL |
+--------+-------------+-------+------------------+-----------+-----------+-------+----------+---------------+------------------------------------------------------------+---------------------------+----------------+
user_85714980
Related