CODEWITHSUNDEEP
sqlhadoopclouderaimpala
acvbasql
acvbasql

Reputation: 109

Impala - Find what tables have a specific column

In Impala, is there a way to check which tables in the database contain a specific column name?

Something like:

select tablename, columnname
from dbc.columns
where databasename = 'mydatabasename'
and columnname like '%findthis%'
order by tablename

The above query works in a teradata environment, but throws an error in Impala.

Thanks,

Upvotes: 3

Views: 8639

Answers (1)

Ramesh
Ramesh

Reputation: 1484

Impala shares the metastore with Hive. Unlike traditional RDBMS, Hive metadata is stored in a separate database. In most cases it is in MySQL or Postgres. If you have access to the metastore database, you can run SELECT on table TBLS to get the details about the tables and COLUMNS_V2 to get the details about columns.

If you do not have access to the metastore, the only option is to describe each table to get the column names. If you have a lot of databases and tables, you could write a shell script to get the list of tables using "show tables" and loop around the tables to describe them using "desc tablename".

Upvotes: 7

Related Questions