SYSHADOOP.HCAT_COLUMNS view

The SYSHADOOP.HCAT_COLUMNS view shows the column definitions for all DATALAKE tables as 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_COLUMNS 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_COLUMNS 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.
COL_NAME VARCHAR(128) The name of the column as defined in Hive.
COL_TYPE VARCHAR(4000) The Hive data type of the column.
COL_COMMENT VARCHAR(4000) The column comment on the Hive column. Db2 stores extended column metadata in the Hive comment field by using the following syntax: 
/*@...*/
. This field can also include comments that you provide.
PARTITION VARCHAR(1) Contains Y if the column is a partition column. Contains N if the column is not a partition column.

Examples

Find the column definitions of the tables in the GOSALESDW schema:
SELECT * FROM SYSHADOOP.HCAT_COLUMNS WHERE TABSCHEMA='GOSALESDW';
A portion of the output is in the following example:

+-----------+-------------+------------+-------------+--------------+----------+------------------------+-----------+
| TABSCHEMA | TABNAME     | HIVESCHEMA | HIVETAB     | COL_NAME     | COL_TYPE | COL_COMMENT            | PARTITION |
+-----------+-------------+------------+-------------+--------------+----------+------------------------+-----------+
| GOSALESDW | BURST_TABLE | GOSALESDW  | BURST_TABLE | recipients   | string   | /*@type=varchar(300)*/ | N         |
| GOSALESDW | BURST_TABLE | GOSALESDW  | BURST_TABLE | country_code | int      | [NULL]                 | N         |
    ...           ...           ...           ...           ...         ...         ...                   ... 
+-----------+-------------+------------+-------------+--------------+----------+------------------------+-----------+