Create partitioned table from non partitioned table

Question

Suppose I have internal orc non partitioned table in Hive:

CREATE TABLE IF NOT EXISTS non_partitioned_table(
        id STRING,
        company STRING,
        city STRING,
        country STRING,
        )
    STORED AS ORC;

Is it possible somehow create parquet partitioned table this way via cte like statement?

create partitioned_table PARTITION ON (date STRING) like non_partitioned_table;
alter table partitioned_table SET FILEFORMAT PARQUET;

This create statement doesn't work.

So basically I need to add column and make table partitioned by this column. I know that I can create table through the simple create table statement, but I need to do it within CREATE TABLE LIKE and the altered somehow

OneCricketeer OneCricketeer · Accepted Answer · 2017-10-26T19:48:36

Your table doesn't have a date column to begin with, so you're going to have to make a new one.

You might be able to ALTER TABLE non_partitioned_table ADD PARTITION, but haven't tried that myself. If you want to try it, I would suggest the partition location be outside of the existing HDFS directory.

Anyways, the CREATE-TABLE-LIKE DDL does not support PARTITIONED BY

 CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
 LIKE existing_table_or_view_name
 [LOCATION hdfs_path];

You need to copy the DESCRIBE TABLE schema from the first, then alter it and add the PARTITIONED BY, and optionally specify STORED AS. (SET FILEFORMAT PARQUET doesn't change the data type in-place).

Then, if you want the data in the new table, you need to INSERT OVERWRITE TABLE

Create partitioned table from non partitioned table

1 Answers