Support Questions

Find answers, ask questions, and share your expertise

How to get columns of list of tables in hive?

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

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)


Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.