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.

Was expecting 'hdfs path for hive table' to be bucket file.

Highlighted

Was expecting 'hdfs path for hive table' to be bucket file.

New Contributor

Hi, A quick help on below issue would be much helpful.

We have a Hive managed table (say TableA) which was initially enabled with ACID property and later decided to disable (Alter Table set tblproperties ('transactional'='false')). For a business case, we have to copy all the records from TableA to TableB (Bucketed as TableA but not enabled for ACID).

  1. When I try to run a quick query as “Insert into TableB Select * from TableA” its success
  2. When I try to run the query as “Insert into TableB Select t.a1, t.a2, t.a3 From TableA t” it fails.

The error says "Was expecting '<hdfs path for hive table>' to be bucket file." The need to have such query as Option2 is, we use Informatica Developer which would internally trigger HQL as mentioned in Option2.

Any help is appreciated.

Thanks.

4 REPLIES 4

Re: Was expecting 'hdfs path for hive table' to be bucket file.

Can you explain more on the target table properties? Is it partitioned? Is it bucketed?

Re: Was expecting 'hdfs path for hive table' to be bucket file.

New Contributor

Non-partitioned. Just bucketed so as to initially enable ACID. Both TableA and TableB are of similar structure. The only difference I see is TableA has TBLPROPERTIES (transactional = false) where TableB did not. I tried to alter TableB by setting the transactional as false so as to have both table's properties in-sync, still the same error.

Thanks.

Re: Was expecting 'hdfs path for hive table' to be bucket file.

Have transactional property of both the tables to true and then try inserting from table B to Table A. It should work.

Re: Was expecting 'hdfs path for hive table' to be bucket file.

New Contributor

@Bala Vignesh N V

I cannot do that at this point since we rolled back ACID property due to one of the limitation we have with Informatica Developer on ACID enabled tables. Thanks.

I did similar exercise on a test table and once after enabling ACID on both tables, am able to backup all records. But I cannot have this is as a solution due to the complexity we have with Informatica Developer.

Don't have an account?
Coming from Hortonworks? Activate your account here