Hive - Create Table statement with 'select query' and 'partition by' commands

Question

I want to create a Partitioned Table in Hive. I know to create a table structure first with the help of "Create table ... Partitioned by" command and then insert the data into the table using "Insert Into Table" command

But what I am trying to do is to combine these two commands into a single query like below but it is throwing errors.

CREATE TABLE test_extract AS
SELECT 
*
FROM master_extract 
PARTITION BY (year string
,month string)
;

Both Year and Month are two separate columns in the master_extract table.

Is there any way to achieve something like this ?

Answer 1

No, this is not possible, because Create Table As Select (CTAS) has restrictions:

The target table cannot be a partitioned table.
The target table cannot be an external table.
The target table cannot be a list bucketing table.

You can create table separately and then insert overwrite it.

Answer 2

There has been some development since this question was originally asked and answered. As per hive documentation: Starting with Hive 3.2.0, CTAS statements can define a partitioning specification for the target table (HIVE-20241).

You can also see the related ticket here. It has been resolved back in July 2018.

Therefore if your hive is of 3.2.0 or higher, then you can simply do

CREATE TABLE test_extract PARTITIONED BY (year string, month string) AS
SELECT 
    col1,
    col2, 
    year,
    month
FROM master_extract