Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Bucketing does now work with CTAS

Highlighted

Bucketing does now work with CTAS

Expert Contributor

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?

5 REPLIES 5

Re: Bucketing does now work with CTAS

Contributor

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

Re: Bucketing does now work with CTAS

Expert Contributor

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?

Re: Bucketing does now work with CTAS

Contributor

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

Re: Bucketing does now work with CTAS

Contributor

@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

Re: Bucketing does now work with CTAS

New Contributor

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