HCATALOG_TABLE_LIST

A concise list of all tables contained in all Hive schemas and databases available through the HCatalog Connector. See Using the HCatalog Connector in Integrating with Apache Hadoop.

Column Name Data Type Description

TABLE_SCHEMA_ID

INTEGER

Internal ID number for the schema containing the table

TABLE_SCHEMA

VARCHAR(128)

Name of the Vertica Analytic Database schema through which the table is available

HCATALOG_SCHEMA VARCHAR(128) Name of the Hive schema or database containing the table
TABLE_NAME VARCHAR(128) The name of the table
HCATALOG_USER_NAME VARCHAR(128) Name of Hive user used to access the table

Privileges

No explicit permissions are required; however, users see only the records that correspond to schemas they have permissions to access.

Notes

  • Querying this table results in one call to HiveServer2 for each Hive schema defined using the HCatalog Connector. This means that the query usually takes longer than querying other system tables.
  • Querying this table is faster than querying HCATALOG_TABLES. Querying HCATALOG_TABLE_LIST only makes one HiveServer2 call per HCatalog schema versus one call per table for HCATALOG_TABLES.

Example

The following example demonstrates defining a new HCatalog schema then querying HCATALOG_TABLE_LIST. Note that one table defined in a different HCatalog schema also appears. HCATALOG_TABLE_LIST lists all of the tables available in any of the HCatalog schemas:

=> CREATE HCATALOG SCHEMA hcat WITH hostname='hcathost' 
-> HCATALOG_SCHEMA='default' HCATALOG_DB='default' HCATALOG_USER='hcatuser';
CREATE SCHEMA
=> \x
Expanded display is on.
=> SELECT * FROM v_catalog.hcatalog_table_list;
-[ RECORD 1 ]------+------------------
table_schema_id    | 45035996273748980
table_schema       | hcat
hcatalog_schema    | default
table_name         | weblogs
hcatalog_user_name | hcatuser
-[ RECORD 2 ]------+------------------
table_schema_id    | 45035996273748980
table_schema       | hcat
hcatalog_schema    | default
table_name         | tweets
hcatalog_user_name | hcatuser
-[ RECORD 3 ]------+------------------
table_schema_id    | 45035996273748980
table_schema       | hcat
hcatalog_schema    | default
table_name         | messages
hcatalog_user_name | hcatuser
-[ RECORD 4 ]------+------------------
table_schema_id    | 45035996273864948
table_schema       | hiveschema
hcatalog_schema    | default
table_name         | weblogs
hcatalog_user_name | hcatuser