CODEWITHSUNDEEP
hadoophive
psmith
psmith

Reputation: 1813

how do I list all columns in all tables in all databases in Hive


I need to make a list of all columns in all tables from all databases in my Hive.

I need a row like:

Database.table.column column_type comment

Obviously I can use 

show databases;

and for each database I can use

show tables;

and for each table use:

describe table_name;

Obviously I could use Hive_metastore database that is somewhere in my Postgress database, but i have no accesss there.

But I'd like to have one bash script that would loop over databases->tables->columns and get the details.

I have started with generating a list of databases:

hive -e 'show databases;' | tee databases.txt

Than I was going to loop over the databases and make a list of tables:

#!/bin/bash

for i in `cat databases.txt`;
# w i mamy liste baz
  do
   hive -e 'show tables where database = $i;' | tee tables.txt

done

But I feel in guts that it's not the right way... Can you help?

Regards
Pawel

Upvotes: 2

Views: 11906

Answers (2)

Rajib Bahar
Rajib Bahar

Reputation: 37

for i in `hive -e 'show databases;'`; 
do
    echo $i;
    hive --database=$i -e 'show tables;' | tee -a output.txt;
done

Upvotes: 1

OneCricketeer
OneCricketeer

Reputation: 191973

Replace show tables where database = $i

With use $i; show tables;. And you should be using tee -a

But I agree, the metastore would be the best option here

Worth mentioning your UUOC

Looping through the content of a file in Bash?

Upvotes: 0

Related Questions