CODEWITHSUNDEEP
sqlsql-serverhivehue
DigiLearner
DigiLearner

Reputation: 79

Hive query for finding table name which contain a particular column

I have written following query in SQL Server to view Table and column names

select * from information_schema.COLUMNS where column_name like '%name%'

Is there any similar query which can be written in Hive for similar result? If not then how can I find list of tables in a particular Database which contains a particular column?

Upvotes: 0

Views: 2186

Answers (1)

Naveen Kumar
Naveen Kumar

Reputation: 2006

I don't think there is any option available in Hive. You can use shell scripting to get the same output. Something like this:

output=""
hive -S -e 'show databases' | while read database
do
   eval "hive -S -e 'show tables in $database'" | while read line
   do
     if eval "hive -S -e 'describe $database.$line'" | grep -q "<column_name"; then
          eval "hive -S -e 'show columns in $database.$line'" | while read column
          do
             output="$output$database.$line,$column"'\n'
          done
     fi
   done
done
echo -e "$output"

Upvotes: 1

Related Questions