CODEWITHSUNDEEP
hiveimpalahue
H2019
H2019

Reputation: 84

I have a database full of many databases and Tables on Hive, now how could I search and find the column of interest?

Long in short that I realized that Hue(Hive/Impala) is not like Microsoft SQL server that you run the following to look for the Table of Interest.

Select * from information_schema.columns where column_name like '%The_Table_of_Interest%'

1st scenario: Imagine that I know what my Database is and I target my attention to the right table by searching through the table and find the column of interest.

2nd scenario: I don't know even what database I need to look for the right table and as a result the column of interest.

I realized that in Hue, there is no option to look for a column. All I can see is Table Search! Hue Snapshot

Having said that for the two above scenarios there should be a way to find the column of interest.

Scenario 2 is of course difficult to approach, however the 1st one looks a bit easier.

Now, I did my research and came of with running some code in Shell Command Line might be helpful to find the target column. However, that require some further investigation in the layer that I am not quite familiar.(Speaking of Metaset, etc.)

Therefore, here is my question.

Assume we are discussing the 1st scenario, now how can I search and find the columns while you have no knowledge about the tables at all. I can not take guesses and try every tables to find the right one to find the column that I am looking for. What would you suggest, and what is your strategy to approach? Thank you in advance. :)

Upvotes: 0

Views: 237

Answers (1)

Matt Andruff
Matt Andruff

Reputation: 5155

Good Day H2019

Here are some commands that should help you out to explore the different tables that you have access to:

Find a table or a database

show tables like 'ben*'

Look at the table definition

show create table <table>;

Get table information

describe my_table_01;

Get even more information

describe extended table_name

Get more information in a pretty format

describe formatted table_name;

If you have access to Apache Ranger I also find it useful to look into tables permissions. (And see who's using what)

Apache Atlas if you use it it helpful to see where data comes from.(It keeps data lineage information and may help to give you an understanding of how things work)

Don't forget you can look at HDFS to find databases, tables if they're in /hive/warehouse/. This can also be helpful to understand when things are created.

Upvotes: 1

Related Questions