question Error while creating a table with 'LIKE' clause in Support Questions

Error while creating a table with 'LIKE' clause

sanjeevanmahaja — Fri, 22 Apr 2016 17:00:32 GMT

I am trying this

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

Its giving a parse exception :

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

Re: Error while creating a table with 'LIKE' clause

jyadav — Fri, 22 Apr 2016 17:39:03 GMT

@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.

Re: Error while creating a table with 'LIKE' clause

jpp — Fri, 22 Apr 2016 23:28:42 GMT

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;