HCAT_DESCRIBETAB function
The SYSHADOOP.HCAT_DESCRIBETAB function returns a textual description of the definition of a Datalake table as defined by the Hive metastore.
Syntax
Description
-
schema
- The schema in which the table resides. table-name
- The name of the Datalake table.
Examples
The following example shows the definition of the table EXAMPLES."My Table" and the subsequent
HCAT_DESCRIBETAB
function:
CREATE DATALAKE TABLE EXAMPLES."My Table" (C1 INT NOT NULL COMMENT 'Main id' PRIMARY KEY, C2 REAL NULL) STORED AS PARQUET LOCATION 'DB2REMOTE://odfdefault//examples/mytable' TBLPROPERTIES ('external.table.purge'='true');VALUES(SYSHADOOP.HCAT_DESCRIBETAB('EXAMPLES', 'My Table'));
+-----------------------------------------------------------------------------------------------+
| 1 |
+-----------------------------------------------------------------------------------------------+
| Hive schema : examples |
| Hive name : my table |
| Type : EXTERNAL_TABLE |
| Table params : |
| EXTERNAL = TRUE |
| biginsights.sql.constraints = [{"v":1,"type":"pk","name":"SQL1687858083999","cols":["C1"]}] |
| biginsights.sql.metadata = {"v":1,"source":"BIGSQL","name":"My Table","version":"4.0"} |
| biginsights.sql.table.location = DB2REMOTE://odfdefault//examples/mytable |
| bucketing_version = 2 |
| external.table.purge = true |
| transient_lastDdlTime = 1687858085 |
| SerDe : null |
| SerDe lib : org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe |
| SerDe params : |
| serialization.format = 1 |
| Location : s3a://odfbucket/examples/mytable |
| Inputformat : org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat |
| Outputformat : org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat |
| Columns : |
| Name : c1 |
| Type : int |
| Comment : Main id/\*@notnull=true\*/ |
| Name : c2 |
| Type : float |
| Comment : null |
+-----------------------------------------------------------------------------------------------+
If there is at least one row of data that can be returned, you can also query the function in a
SELECT statement. The following example finds information about the MRK_PROD_SURVEY_TARG_FACT table
in schema
GOSALESDW:
SELECT SYSHADOOP.HCAT_DESCRIBETAB('GOSALESDW','MRK_PROD_SURVEY_TARG_FACT') FROM gosalesdw.mrk_prod_survey_targ_fact fetch first 1 rows only;
+------------------------------------------------------------------------------------------------+
| 1 |
+------------------------------------------------------------------------------------------------+
| Hive schema : gosalesdw |
| Hive name : mrk_prod_survey_targ_fact |
| Type : EXTERNAL_TABLE |
| Table params : |
| EXTERNAL = TRUE |
| biginsights.sql.metadata = {"v":1,"source":"BIGSQL","version":"4.0"} |
| biginsights.sql.table.location = DB2REMOTE://odfdefault//gosalesdw/mrk_prod_survey_targ_fact |
| bucketing_version = 2 |
| external.table.purge = true |
| transient_lastDdlTime = 1687952339 |
| SerDe : null |
| SerDe lib : org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe |
| SerDe params : |
| field.delim = |
| serialization.format = 1 |
| Location : s3a://odfbucket/gosalesdw/mrk_prod_survey_targ_fact |
| Inputformat : org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat |
| Outputformat : org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat |
| Columns : |
| Name : month_key |
| Type : int |
| Comment : null |
| Name : product_key |
| Type : int |
| Comment : null |
| Name : product_survey_key |
| Type : int |
| Comment : null |
| Name : product_topic_target |
| Type : double |
| Comment : null |
+------------------------------------------------------------------------------------------------+