HCAT_DESCRIBETAB function
The HCAT_DESCRIBETAB function returns a textual description of the definition of a DATALAKE table as defined by the Hive metastore. The description is a formatted text string.The description is a formatted text string of type VARCHAR(32672).
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 HADOOP TABLE "My Table" (
C1 INT NOT NULL COMMENT 'Main id' PRIMARY KEY, C2 REAL NULL
)
STORED AS RCFILE;
VALUES(SYSHADOOP.HCAT_DESCRIBETAB('EXAMPLES', 'My Table'));
+-----------------------------------------------------------------------------------------------+
| 1 |
+-----------------------------------------------------------------------------------------------+
| Hive schema : examples |
| Hive name : my_table |
| Type : MANAGED_TABLE |
| Table params : |
| biginsights.sql.constraints = [{"v":1,"type":"pk","name":"SQL1400170612899","cols":["C1"]}] |
| biginsights.sql.metadata = {"v":1,"source":"BIGSQL","name":"My Table","version":"3.0"} |
| transient_lastDdlTime = 1400170612 |
| SerDe : null |
| SerDe lib : org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe |
| SerDe params : |
| serialization.format = 1 |
| Location : hdfs://localhost:9000/user/hive/warehouse/examples.db/my_table |
| Inputformat : org.apache.hadoop.hive.ql.io.RCFileInputFormat |
| Outputformat : org.apache.hadoop.hive.ql.io.RCFileOutputFormat |
| 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 : MANAGED_TABLE |
| Table params : |
| biginsights.sql.metadata = {"source":"BIGSQL","version":"3.0"} |
| transient_lastDdlTime = 1397249586 |
| SerDe : null |
| SerDe lib : org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe |
| SerDe params : |
| field.delim = |
| serialization.format = |
| Location : |
| hdfs://ahost.com:9000/apps/hive/warehouse/gosalesdw.db |
| /mrk_prod_survey_targ_fact |
| Inputformat : org.apache.hadoop.mapred.TextInputFormat |
| Outputformat : org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat |
| 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 |
+-----------------------------------------------------------------------------+