Bucketing does now work with CTAS

[#1]

I am trying to create a table with Bucketing from another table using select but it fails. Here is my query

create table tmp CLUSTERED BY (key) INTO 256 BUCKETS as select * from link_table limit 10;

And I get the below error

FAILED: SemanticException [Error 10068]: CREATE-TABLE-AS-SELECT does not support partitioning in the target table

link_table is already bucketed. I have also set the property to enforce bucketing.

I am not sure if bucketing is supported with CTAS. Is there a way I can get this working?

[#2]

@Alex Raj

CTAS has these 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.

Refer to the link for more details:-

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-CreateTableAsS...

[#3]

If I create a plain target table would I be able to alter the table later? What I mean is, create a plain table and then alter the table to add the buckets? If yes, how?

[#4]

@Alex Raj Can you please accept the answer if it resolved the issue?

[#5]

@Alex Raj:

You can use the below command to alter the table to add buckets:-

ALTER TABLE table_name CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name, ...)]INTO num_buckets BUCKETS

Please note that the above command will only modify Hive's metadata, and will NOT reorganize or reformat existing data.

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

[#6]

@Vani

https://cwiki.apache.org/confluence/display/Hive/DynamicPartitions

But here it states that , we can create partition table from CTAS by specifying the schema including partitioning columns in the create-clause. Can we create bucketed table by specifying the schema?

CREATE TABLE T (key int, value string) PARTITIONED BY (ds string, hr int) AS SELECT key, value, ds, hrFROM srcpart ;