CODEWITHSUNDEEP
hadoophivehiveql
diazazar
diazazar

Reputation: 542

Is it possible to count the number of partitions?

I am working on a test in which I must find out the number of partitions of a table and check if it is right. If I use show partitions TableName I get all the partitions by name, but I wish to get the number of partitions, like something along the lines show count(partitions) TableName (which retuns OK btw.. so it's not good) and get 12 (for ex.).
Is there any way to achieve this??

Upvotes: 5

Views: 23753

Answers (8)

Manoj Kumar G
Manoj Kumar G

Reputation: 502

I know this post is very old, but still if anyone is looking for the answer, here is the query.

select count(distinct <partition_column>) as partition_count from <db>.<table_name>;

Upvotes: 0

Rahul
Rahul

Reputation: 767

By using the below command, you will get the all partitions and also at the end it shows the number of fetched rows. That number of rows means number of partitions

SHOW PARTITIONS [db_name.]table_name [PARTITION(partition_spec)];

< failed pictoral example >

Upvotes: 1

Prash
Prash

Reputation: 1

I used following.
beeline -silent --showHeader=false --outputformat=csv2 -e 'show partitions <dbname>.<tablename>' | wc -l

Upvotes: 0

Himanshu Shrimalve
Himanshu Shrimalve

Reputation: 19

In scala you can do following:

sql("show partitions <table_name>").count()

Upvotes: 0

Viswa Murali
Viswa Murali

Reputation: 65

Using Hive CLI

$ hive --silent -e "show partitions <dbName>.<tableName>;" | wc -l

--silent is to enable silent mode

-e tells hive to execute quoted query string

Upvotes: 6

djmdata
djmdata

Reputation: 108

You can use the WebHCat interface to get information like this. This has the benefit that you can run the command from anywhere that the server is accessible. The result is JSON - use a JSON parser of your choice to process the results.

In this example of piping the WebHCat results to Python, only the number 24 is returned representing the number of partitions for this table. (Server name is the name node).

curl -s 'http://*myservername*:50111/templeton/v1/ddl/database/*mydatabasename*/table/*mytablename*/partition?user.name=*myusername*' | python -c 'import sys, json; print len(json.load(sys.stdin)["partitions"])'
24

Upvotes: 0

Sathish
Sathish

Reputation: 5

Use the following syntax:

show create table <table name>;

Upvotes: -3

Carter Shanklin
Carter Shanklin

Reputation: 3047

You could use:

select count(distinct <partition key>) from <TableName>;

Upvotes: 3

Related Questions