Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: The Cloudera Community will undergo maintenance on Saturday, August 17 at 12:00am PDT. See more info here.

How to show tables based on the table owner?

How to show tables based on the table owner?

New Contributor

Hi All,

 

Is there a way to show tables based on the table owner in Impala?

3 REPLIES 3

Re: How to show tables based on the table owner?

Rising Star

Hi @lonetiger

What do you mean by table owner ?
If you use apache sentry just login to impala-shell by the user concerned (owner) and execute

show tables;

and you'll see just this user tables

Good luck.

Re: How to show tables based on the table owner?

New Contributor

@AcharkiMed - Thanks for responding to the post.

 

To explain long story short... We are using MicroStrategy Reporting tool with Apache Impala which creates tables while running the reports. Some times if the report fails the drop tables doestnt get executed and it results in junk tables in impala and they never get dropped unless we explicitly drop them.

 

So I wanted to list out the tables which are created by a db login so that I can go ahead drop all the tables which are created by a seperate login.

 

Let me know if you need more info.

 

 

Re: How to show tables based on the table owner?

Rising Star

Hi @lonetiger

You can do it by two kind of scripts:
1- show all tables:

SHOW TABLES;

get the list and run this query on all tables:

DESCRIBE FORMATTED tableX;

Then you can get the results and extract the owner, if it's the desired one, drop the table.

2- Connect with your hive metastore DB and get the table list of the owner you want

SELECT "TBL_NAME"
FROM   "TBLS"
WHERE  "OWNER" = 'ownerX';

Then drop them.

Good luck.