1

Suppose I have a non-transactional table in Hive named 'ccm'. It has hundreds of columns and one partition field.

I know how to create a copy with "create table abc like ccm' but I would like abc to be bucketed, ORC, and have transaction support set on via TBLPROPERTIES.

I do not want to mention all the columns in ccm when I compose the HQL.

Can I do this?

mck
  • 40,932
  • 13
  • 35
  • 50
Mark Ginsburg
  • 2,139
  • 4
  • 17
  • 31

1 Answers1

-1

This answer may have the correct way to proceed in your case, and it also explains some limitation of the method used.

Create hive table using "as select" or "like" and also specify delimiter

So, from the example, you should add the missing parts:

  • CLUSTER BY
  • TBLPROPERTIES ("transactional"="true")

I have some doubts that you can achieve exactly your expected results but i would consider it as a step forward

Marco Massetti
  • 539
  • 4
  • 12
  • The target table is partitioned; the message I get when I run this: `hive> create table test clustered by (acctnum) into 50 buckets stored as ORC tblproperties('transactional'='true') as select * from ccm;` is: `FAILED: SemanticException [Error 10068]: CREATE-TABLE-AS-SELECT does not support partitioning in the target table` – Mark Ginsburg Jan 29 '21 at 23:25
  • That was my impression as well, i think that the cluster by here is unusable. If i find another way I'll post it here. At the moment, i don't have any remarkable method – Marco Massetti Jan 29 '21 at 23:30
  • Have you tried create with CTAS and once created alter the table with ALTER ddl? – ebeb Jan 30 '21 at 01:23
  • My intention is to create a table "like" a large table example, but to make this table transactional. I am failing because the large example table is partitioned. – Mark Ginsburg Feb 01 '21 at 19:26