04-29-2015 12:51 PM
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.
05-01-2015 08:51 AM
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.
05-06-2015 12:43 PM
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?
05-06-2015 01:51 PM
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?
05-07-2015 08:14 AM
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?
05-07-2015 11:54 AM
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';
05-11-2015 09:37 AM
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.