CODEWITHSUNDEEP
hadoop
silentshadow
silentshadow

Reputation: 83

How to create multiple partitions using alter command in hive?

I have 2 partitions on a table as below:

hive> show partitions alt_part;
OK
yop=2011
yop=2013

Now I want to add an inner partition to year 2013. But I'm getting the below error.

hive> alter table alt_part add partition(yop=2013,mop=9); FAILED: SemanticException Partition spec {yop=2013,mop=9} contains non-partition columns

Please correct me if I'm doing some mistake.

I have also tried with location....

hive>alter table alt_part add partition(yop=2013,mop=9) location '/user/revathi-prac/partitions/dec21/yop=2013/mop=9';

but I still have the same issue...

Upvotes: 2

Views: 15315

Answers (2)

madbitloman
madbitloman

Reputation: 826

There are one way to do it without spending time on figuring out why your data is not loaded as well.

If you have your files outside /user/hive/warehouse I would highly recommend to use external tables. 

CREATE EXTERNAL TABLE cars (
        vin BIGINT,
        model STRING,
        colour STRING
        ) 
        PARTITIONED BY (year STRING, month STRING)
        ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
        LOCATION '/user/revathi-prac/';

Now partitioning can be relatively easy done:

ALTER TABLE cars ADD PARTITION (year=2015, month=12)
LOCATION '/user/revathi-prac/2015/12'

Specifying proper location will save you time whe ndealing with automatically created folders by hive like year=2015/month=12 and it is easier to loop through all of your subfolders via bash or python.

Also you NEED to create a proper partition before altering the table. You can not create table by partition year only and then trying to add a new non-existing partitions like month.

Hope that helps!

Upvotes: 4

Durga Viswanath Gadiraju
Durga Viswanath Gadiraju

Reputation: 3956

Here is the example of creating partitions at multiple levels. Have you defined both yop and mop as part of your create table command. You can paste the syntax of create table command by running show create table alt_part and paste the output.

hive> CREATE TABLE `order_items`(
    >   `order_item_id` int, 
    >   `order_item_order_id` int, 
    >   `order_item_order_date` string, 
    >   `order_item_product_id` int, 
    >   `order_item_quantity` smallint, 
    >   `order_item_subtotal` float, 
    >   `order_item_product_price` float)
    > partitioned by (year int, month int);
OK
Time taken: 0.195 seconds
hive> alter table order_items add partition (year=2013, month=1);
OK
Time taken: 0.407 seconds
hive> show partitions order_items;
OK
year=2013/month=1
Time taken: 0.551 seconds, Fetched: 1 row(s)
hive>

Upvotes: 0

Related Questions