Error while creating a table with 'LIKE' clause

[#1]

I am trying this

create table emp1 LIKE emp CLUSTERED BY () INTO 4 BUCKETS STORED AS ORC;

Its giving a parse exception :

1. rg.apache.ambari.view.hive.client.HiveInvalidQueryException:Errorwhile compiling statement: FAILED:ParseException line 4:42 missing EOF at 'CLUSTERED' near 'emp'[ERROR_STATUS]
2. org.apache.ambari.view.hive.client.HiveInvalidQueryException:Errorwhile compiling statement: FAILED:ParseException line 4:42 missing EOF at 'CLUSTERED' near 'emp'[ERROR_STATUS]
3. at org.apache.ambari.view.hive.client.Utils.verifySuccess(Utils.java:46)

[#2]

@sanjeevan mahajan

I don't think you can define other attributes while creating table with "LIKE" option, in theory with "LIKE" it will copy the existing table definition and other attributes. Its better to use alter table after creating it with "LIKE".

EDITED: I think you can change no. of buckets with alter command if you already defined it previously.

[#3 · Accepted solution]

You will need to specify the column you are clustering on, and then achieve it in multiple statements:

CREATE TABLE emp1 LIKE emp;
ALTER TABLE emp1 SET FILEFORMAT ORC;
ALTER TABLE emp1 CLUSTERED BY (empId) INTO 4 BUCKETS;