2


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

psmith
  • 1,769
  • 5
  • 35
  • 60

2 Answers2

1
for i in `hive -e 'show databases;'`; 
do
    echo $i;
    hive --database=$i -e 'show tables;' | tee -a output.txt;
done
  • Welcome to the SO. It is nice to give little explanation to your answer instead of giving only code block. – Mahib Dec 07 '18 at 19:46
0

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?

OneCricketeer
  • 179,855
  • 19
  • 132
  • 245