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

MIKE used Ask the Experts™
Hive. How can I search for all tables in a database that contain a Column Name?
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

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 ( 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

I'm wondering if your version allows:

 WHERE COLUMN_NAME IN ('columnNameSearch')
     AND TABLE_SCHEMA='YourDatabase';

Open in new window

Data Engineer
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