Hive, How to Search using Column Name for all table in the DB that contain that Column Name?

MIKE
MIKE used Ask the Experts™
on
Hive. How can I search for all tables in a database that contain a Column Name?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
I don't know Apache Hive, but from what I read it stores metadata in a different database (Derby by default) you need to connect to separately, at least with 2.3 (https://stackoverflow.com/questions/20686019/how-to-display-information-schema-using-hive). I could not find anything in the release notes of 3.0 about a change.
ain the repository you should have the standard information_schema views available to retrieve table and column info as with other databases.
NerdsOfTechTechnology Scientist

Commented:
I'm wondering if your version allows:

SELECT DISTINCT TABLE_NAME 
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE COLUMN_NAME IN ('columnNameSearch')
     AND TABLE_SCHEMA='YourDatabase';

Open in new window

Data Engineer
Commented:
Hive metadata is stored in databases like MySQL. If you have access, you can query that.
Otherwise, you need to get that with hive queries like  hive -e "use dbname; describe formatted table_name"
You need to write custom shell script to get this info and check if specified column is there. Of course, the command has to be executed in a loop for all tables. let me know if you need any help further.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial