HCAT_TABLES

The HCAT_TABLES view shows the Db2 Big SQL Hadoop tables that are defined by the Hive catalogs. The contents of this view might not be available if the Hive metastore is unavailable.

When you access the HCAT_TABLES view, you are querying the Hive metastore for each table that is returned. If you filter the set of tables that you want to view by TABSCHEMA and TABNAME, you can improve the access time to this view.

If impersonation is enabled, access to the underlying schema and table locations is given to the connected user. If that user does not have the correct read and execute privileges on any underlying location, an error is returned while accessing that location when the view is being populated. An insufficient privileges error is also returned if a WHERE clause is used to list the tables that belong to the connected user, because the WHERE clause predicate is applied after all rows from HCAT_TABLES are collected.

Table 1. HCAT_TABLES
Column	Type	Description
TABSCHEMA	VARCHAR(128)	The Db2 Big SQL schema in which the table resides.
TABNAME	VARCHAR(128)	The name of the Db2 Big SQL table.
HIVESCHEMA	VARCHAR(128)	The name of the Hive schema in which the table resides as defined in the Hive metastore.
HIVETAB	VARCHAR(128)	The name of the table as defined in the Hive metastore.
TABLE_TYPE	VARCHAR(128)	The type of the table as define in Hive, such as EXTERNAL_TABLE.
CRTIME	INT	The time of creation in number of milliseconds since 1970 UTC.
ACCESS_TIME	INT	The Hive access time in number of milliseconds since 1970 UTC.
OWNER	VARCHAR(767)	The name of the owner of the table.
INPUTFORMAT	VARCHAR(4000)	The name of the input format class.
OUTPUTFORMAT	VARCHAR(4000)	The name of the output format class.
LOCATION	VARCHAR(4000)	The location of the data in the distributed file system.
COMPRESSED	CHAR(1)	An flag that indicates whether the data is compressed. Valid values are Y or N.
SERDE_NAME	VARCHAR(128)	The name of the SerDe that is used for the table.
SERDE_LIB	VARCHAR(4000)	The name of the SerDe class that is used for the table.

Examples

Find some of the table definitions for the GOSALESDW schema:

SELECT TABNAME, INPUTFORMAT,OUTPUTFORMAT,LOCATION 
  FROM SYSHADOOP.HCAT_TABLES WHERE TABSCHEMA='GOSALESDW' FETCH FIRST 2 ROWS ONLY;

The portion of the output looks like the following sample:


+------------+------------------------------+-------------------------------------------------+------------------------------------------------------------------+
| TABNAME    | INPUTFORMAT                  | OUTPUTFORMAT                                    | LOCATION                                                         |
+------------+------------------------------+-------------------------------------------------+------------------------------------------------------------------+
| BURST_TABL | org.apache.hadoop.mapred.Tex | org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextO | hdfs://myhost.com:9000/apps/hive/warehouse/gosalesdw.db/b        |
| E          | tInputFormat                 | utputFormat                                     | urst_table                                                       |
| BURST_TABL | org.apache.hadoop.mapred.Tex | org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextO | hdfs://myhost.com:9000/apps/hive/warehouse/gosalesdw.db/b        |
| E2         | tInputFormat                 | utputFormat                                     | urst_table2                                                      |
+------------+------------------------------+-------------------------------------------------+------------------------------------------------------------------+