metastore join TBLS to COLUMNS_V2

[Alex Nastetsky <an...@spryinc.com>]

Hi,

I am trying to run some queries against the metastore, but I am not sure
how to query the list of Hive columns in the COLUMNS_V2 table for a
specific Hive table in the TBLS table.

I want to do something like

select t.tbl_name, c.column_name
from TBLS t
join COLUMNS c
on t.tbl_id = ...

I tried the cd_id field on the COLUMNS_V2 table but it didn't match up. I'm
not sure if there's an intermediate table that needs to be involved or
something else I am missing.

Thanks!

Re: metastore join TBLS to COLUMNS_V2

[Alex Nastetsky <an...@spryinc.com>]

Figured it out (actually by doing the same type of stuff on the metastore
itself, using the information_schema database).

For example, to see which tables have a column called "some_column_name" in
database "some_database", do the following:

select t.tbl_name
from DBS d
join TBLS t
  on d.db_id=t.db_id
join SDS s
  on t.sd_id=s.sd_id
join COLUMNS_V2 c
  on s.cd_id=c.cd_id
where d.name="some_database"
and c.column_name="some_column_name";


On Mon, Aug 25, 2014 at 12:15 PM, Alex Nastetsky <an...@spryinc.com>
wrote:

> Hi,
>
> I am trying to run some queries against the metastore, but I am not sure
> how to query the list of Hive columns in the COLUMNS_V2 table for a
> specific Hive table in the TBLS table.
>
> I want to do something like
>
> select t.tbl_name, c.column_name
> from TBLS t
> join COLUMNS c
> on t.tbl_id = ...
>
> I tried the cd_id field on the COLUMNS_V2 table but it didn't match up.
> I'm not sure if there's an intermediate table that needs to be involved or
> something else I am missing.
>
> Thanks!
>