question Re: Accessing Hive Metadata in Support Questions

Accessing Hive Metadata

rio — Fri, 16 Sep 2022 09:27:26 GMT

It is always handy to search tables and column in metadata. How do I search for tables in Hive based on th column name?

I know we can do via metadata. I logged into MySQL metadata but did not find a way to search.

I saw a way to search a column name but could not find a way to relate to table name.
select COLUMN_NAME from COLUMNS_V2 where column_name regexp 'repo' ;
I also searched on TBLS table, but there is no column name.

Any help?

Re: Accessing Hive Metadata

sergio.pena — Fri, 01 May 2015 15:51:32 GMT

Here's the query you can use on the metastore:

select TBL_NAME, COLUMN_NAME, TYPE_NAME from TBLS left join COLUMNS_V2 on CD_ID = TBL_ID where COLUMN_NAME like 'column';

where 'column' is the column name you're looking for.

Re: Accessing Hive Metadata

rio — Wed, 06 May 2015 19:11:04 GMT

Seems like not all columns are indexed, how do I index the columns?

Re: Accessing Hive Metadata

sergio.pena — Wed, 06 May 2015 19:43:11 GMT

You can use the following statment:

ALTER TABLE TABLE_NAME ADD INDEX (COLUMN_NAME);

Is the query slow on your system?

Could you paste the the output of the 'EXPLAIN SELECT ...' of your query?

Re: Accessing Hive Metadata

rio — Wed, 06 May 2015 20:51:46 GMT

Query comes out fast. Not all hive columns / tables are displayed. I know there is a table for a column idMfg but when I search it does not display.

Will creating index help here?

Re: Accessing Hive Metadata

sergio.pena — Thu, 07 May 2015 15:14:53 GMT

Indexing won't work to connect two tables. This is used for speed performance when searching data on tables.

I was taking a look at the tables on the metastore, and there are tables like SKEWED_COL_NAMES, PART_COL_PRIVS, etc. Those contain the column name as well.

How is the table you're looking for configured? Is it partitioned? Is it skewed?

Re: Accessing Hive Metadata

rio — Thu, 07 May 2015 18:54:44 GMT

This query is giving me output. what is difference between COLUMNS_OLD and COLUMNS_V2?

select TBL_NAME, COLUMN_NAME, TYPE_NAME from TBLS t left join COLUMNS_OLD c on c.SD_ID = t.SD_ID where COLUMN_NAME regexp 'idorder';

Re: Accessing Hive Metadata

sergio.pena — Mon, 11 May 2015 16:37:32 GMT

COLUMNS_OLD is a deprecated table where columns used to be stored. Hive might have some information there for some reason. You can use both COLUMNS_OLD or COLUMNS_V2 when searching for your column.