How to get metadata of hive tables, columns, views

Question

Please help me out how get metadata of hive tables, columns, views, constraint keys and comment columns.

Answer 1

Use Hcatlog.

Maven dependency:

    <dependency>
        <groupId>org.apache.hive.hcatalog</groupId>
        <artifactId>hive-webhcat-java-client</artifactId>
        <version>1.2.1</version>
    </dependency>

Sample code:

HiveConf hcatConf = new HiveConf();

hcatConf.setVar(HiveConf.ConfVars.METASTOREURIS, connectionUri);
hcatConf.set("hive.metastore.local", "false");

HCatClient client = null;
HCatTable hTable = null;

try {
    client = HCatClient.create(hcatConf);
    hTable = client.getTable(databaseName, tableName);
    System.out.println(hTable.getLocation());
    System.out.println(hTable.getInputFileFormat());
    System.out.println(hTable.getOutputFileFormat());
    // other properties
} catch (HCatException hCatEx) {
    LOG.error("Not able to connect to hive. Caused By;", hCatEx);
}

Check available methods of HCatTable.

Answer 2

It can be viewed by this simple query describe formatted table_name

Answer 3

If you access to hive metastore(rdbm systems like mysql, postgresql etc), you will be able to access complete information. Contact your cluster admin who has configured the hive metastore if you do not have the access.

Answer 4

if you have Hue available you can go to Metastore Tables from the top menu Data Browsers. There you can find metadata for all schemas available for you. From Hive you can try:
USE DB_NAME;
DESCRIBE FORMATTED TABLE_NAME;
or
DESCRIBE EXTENDED TABLE_NAME;