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

[#1]

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.

[#2]

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

[#3]

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.

[#4]

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

[#5]

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