SYSHADOOP.HCAT_TABLES view

The SYSHADOOP.HCAT_TABLES view shows the Db2 DATALAKE 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 SYSHADOOP.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.

Table 1. SYSHADOOP.HCAT_TABLES view
Column	Type	Description
TABSCHEMA	VARCHAR(128)	The Db2 schema in which the table resides.
TABNAME	VARCHAR(128)	The name of the Db2 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_TABLE  | org.apache.hadoop.hive.ql.io.    | org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat | s3a://odfbucket/gosalesdw/burst_table      |
|              | parquet.MapredParquetInputFormat |                                                                |                                            |
| BURST_TABLE2 | org.apache.hadoop.hive.ql.io.    | org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat | s3a://odfbucket/gosalesdw/burst_table2     |
|              | parquet.MapredParquetInputFormat |                                                                |                                            |
+--------------+----------------------------------+----------------------------------------------------------------+--------------------------------------------+