in Hive SQL, create table based on columns from another table with partition key

Question

I'd like to create a new table based on columns from an existed table, add a new partition col in hive.

And I'd like to achieve the goal in hive sql.

Is there any other way than the following sql or using ETL tools like kettle.

create table if not exists table_name(
col1,
col2,
col3,
……,
coln
)partitioned by dt;

in which col1 to coln are from the already existed older table, and dt, the partition key is newly added.

Because the older table is too big, may has hundreds of columns, listing them would be tiring.

However, the following sql shows syntax error:

create table if not exists table_name like older_table_name partitioned by dt;

So I'd like to know is there any other better way to solve this in hive sql? thank you.

Nishu Tayal · Accepted Answer · 2016-05-05 10:00:08Z

1

If all the columns and dt columnYou can use following ways to create the partitioned table:

CREATE TABLE newtable (col1 int, col2 string) PARTITIONED BY (dt string) AS
SELECT col1 , col2, dt FROM oldtable WHERE dt is not null;

answered May 5, 2016 at 10:00

Nishu Tayal

20.9k8 gold badges53 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1