HCAT_COLUMNS

The HCAT_COLUMNS view shows the column definitions for all Hadoop 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 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. HCAT_COLUMNS
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.
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 Big SQL 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         |
    ...           ...           ...           ...           ...         ...         ...                   ... 
+-----------+-------------+------------+-------------+--------------+----------+------------------------+-----------+