impala - 如何判断一个Impala对象的名字对应一个 View

Question

Impala 中有没有一种方法可以确定 SHOW TABLES 返回的对象名称是否存在？对应于表或 View ，因为:

• 该语句只返回对象名称，不返回类型
• SHOW CREATE VIEW只是 SHOW CREATE TABLE 的别名(相同的结果，没有 View /表的区别)
• DESCRIBE没有提供任何关于项目类型的线索

理想情况下，我想使用单个操作列出所有表 + View 及其类型，而不是先检索表 + View ，然后再调用每个名称以确定对象的类型。

(请注意问题是关于 Impala，而不是 Hive)

Answer 1

您可以使用describe formatted 来了解对象的类型

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                 |

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

表类型是表类型:MANAGED_TABLE， View 是表类型:VIRTUAL_VIEW

其他方法是查询metastore 数据库(如果可以)以了解 Impala(或 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 |
+--------+-------------+-------+------------------+-----------+-----------+-------+----------+---------------+------------------------------------------------------------+---------------------------+----------------+