Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

How to get columns of list of tables in hive?

avatar
New Contributor

i am trying to get the list of tables and columns using a single query. may i know how can i do that ?

I am looking for something like

ex: 'select * from dbc.columns where tables like 'E%'

How do we achive that in hive?

1 REPLY 1

avatar
Explorer

@Ravi teja

You can get this information from the metastore database:

hive=> SELECT "TBL_NAME", "COLUMN_NAME", "TYPE_NAME" FROM "TBLS" , "COLUMNS_V2" WHERE "TBL_ID"="CD_ID" UNION SELECT "TBL_NAME", "PKEY_NAME", "PKEY_TYPE" FROM "PARTITION_KEYS" p , "TBLS" t WHERE p."TBL_ID"=t."TBL_ID" ORDER BY "TBL_NAME";

  TBL_NAME  | COLUMN_NAME | TYPE_NAME 
------------+-------------+-----------
 tableA     | aaa         | string
 tableA     | bbb         | string
 tableB     | foo         | string
 tableB     | bar         | int
 tableC     | cola        | string
 tableD     | colb        | string
(6 rows)