CODEWITHSUNDEEP
hadoophivehiveql
Tom J Muthirenthi
Tom J Muthirenthi

Reputation: 3340

How to write a query to find all tables in HIVE db that have a specific column name

I've got a database with about 100 tables and I need to build a join query to get specific data from two of them. I know one but not the other. Basically I need something like:

select <tables> from <HIVE_database> where exists table.column name;

How can I do this?

Upvotes: 0

Views: 5750

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31716

You could write a shell script to search for the column among all tables.

The first line gives you all the table names. It is passed to read command and output of describe from each table is searched for the column name.

$hive -e 'show tables in <HIVE_database>'  | \
while read line
do
 echo "TABLE NAME : $line"
  eval "hive -e 'describe <HIVE_database>.$line'" | grep "<column_name>" 
done

Upvotes: 1

Related Questions