Creating a new Partitioned table from existing table

I want to create a new partitioned table sales_per_year from another existing table sales. Since my table is huge with many columns, I am looking for a way to avoid mentioning the table schema in the Create table command. That's why I have tried with CTAS.

I tried running this command :

create table default.sales_per_year
partitioned by (ordermonthyear date)
as select * from default.sale_withcommas;

But it failed with the following error

Error while compiling statement: FAILED: SemanticException [Error 10068]: CREATE-TABLE-AS-SELECT does not support partitioning in the target table

I got to know that this inevitable because with CTAS there is a restriction that the target table can't be partitioned. https://riptutorial.com/hive/example/11427/create-table

I then created the table sales_per_year separately

create table default.sales_per_year
as select * from default.sale_withcommas;

But now if I have to create the partition based on ordermonthyear, how can that be achieved? If I alter the table and add the partition it expects a value for the partition.

alter table default.sales_dummy
add partition (ordermonthyear='value');

Solution

you should create a new partitioned table individually, then insert into it from old table,hive don't support ctas partitioned table