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?
Refer to the link for more details:-
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?
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.
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
, value string) PARTITIONED BY (ds string, hr
SELECT key, value, ds, hr
FROM srcpart ;