CODEWITHSUNDEEP
hadoophive
Mohit.kc
Mohit.kc

Reputation: 83

How can I find a particular column name within all tables in Hive.?

How can I find a particular column name within all tables in Hive?

I ran this query in hive: select table_name,column_name from retail.columns where column_name like '%emp%'; (retail is a database).

But it's giving:

error FAILED: SemanticException Line 0:-1 Table not found 'columns'

I tried query: select distinct table_name from default.columns where column_name = 'emp' (default is mine database). But it's also giving error.

I searched about these, I got which query I wrote it's for SQL databases.

But I want to search in hive database? How to get in hive?

The same question has been asked before but I feel things might have changed and there might be direct solution:

How can you search for all tables with a given column name and return which tables have this column name in Hadoop/Hive?

Searching Tables and Columns in Hive

Upvotes: 6

Views: 24831

Answers (5)

muk
muk

Reputation: 79

metastore table: "TBLS","SDS","COLUMNS_V2"

to query columns of table:

select tc.* from "TBLS" tt 
inner join "SDS" ts on tt."SD_ID" = ts."SD_ID" 
inner join "COLUMNS_V2" tc on tc."CD_ID" = ts."CD_ID"
where tt."TBL_NAME"='test_fenqu'
order by "INTEGER_IDX"

Upvotes: 0

Devender Goyal
Devender Goyal

Reputation: 1778

I have written this below simplified shell script to get the result:

Step 1: Replace the <DB_NAME>, <COLUMN_NAME> in below script and run:

   while read line
   do
    if eval "hive -S -e 'describe <DB_NAME>.$line'"| grep -q "<COLUMN_NAME>"; then
        output="${output}  <DB_NAME>.$line"'\n';
    fi
   done < <(eval "hive -S -e 'show tables in <DB_NAME>'")

Step 2: Run the below command

echo -e "Required table name:\n\n $output"

Note: Remember to clear the variable output in case you are running multiple times.

output=""

Upvotes: 0

Poonam Tekchandani
Poonam Tekchandani

Reputation: 1

I believe this query will help you if you know little bit name of your column :

select table_name,column_name from information_schema.columns
where column_name like '%lead%'

Upvotes: -1

hduser
hduser

Reputation: 7

Here's the query you can use on the metastore:

select TBL_NAME, COLUMN_NAME, TYPE_NAME from TBLS left join COLUMNS_V2 on CD_ID = TBL_ID where COLUMN_NAME like 'column';

where 'column' is the column name you're looking for.

Upvotes: -2

Abhishek Anand
Abhishek Anand

Reputation: 76

below shell script will give you desired result:

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
  output="Required table name: $database.$line"'\n';
else
output=""'\n';

fi
echo -e "$output"
 done
done

Upvotes: 4

Related Questions