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.

Does alter table partition concatenate work on external hive tables?

Highlighted

Does alter table partition concatenate work on external hive tables?

Explorer

I think the question above says it all, but to add more background, we have pig scripts landing data in ORC files hourly and are ending up with numerous small files in a partition directory. alter table partition concatenate appears to run (we end up with different file names in the partition folder), but doesn't seem to be actually concatenating into fewer files. I'm wondering if the problem is the fact that these are external tables.... but the fact that files within the partition are getting renamed suggests it's not.

Thoughts?

6 REPLIES 6
Highlighted

Re: Does alter table partition concatenate work on external hive tables?

Guru

@Vincent Romeo concatenate orc files on external tables works. did you try using mr vs tez ? if you launch your concatenate in Hive, could you put some debug on (hive --hiveconf hive.root.logger=INFO,console) and let's have a look at the logs?

Highlighted

Re: Does alter table partition concatenate work on external hive tables?

New Contributor

We have a requirement to concatenate huge number of small ORC files into small number of big ORC files, through this post I was encouraged to try it on our external table and it just worked fine.

This is a test that we ran on our dev environment, we created 100 small ORC files and then used the concatenate command it turned them into 5 big files (next we'll try to figure out how to influence the number of files/size)

Our config:

HDP: 2.5.3

Highlighted

Re: Does alter table partition concatenate work on external hive tables?

Hey @Harsha Mohan, I am trying to do the same it is concatenating upto 2 files and after several runs it is converting to 1 big file. Do you any other approach that can convert to 1 big file in single run?

Highlighted

Re: Does alter table partition concatenate work on external hive tables?

We've just been trying the same thing and find that Hive does not support CONCATENATE on external tables:

ALTER TABLE DD_EXT_ORC CONCATENATE;
FAILED: SemanticException org.apache.hadoop.hive.ql.parse.SemanticException: Concatenate/Merge can only be performed on managed tables

Maybe it's a limitation that was introduced in more recent versions of Hive. I tested using HDP-2.6.3.0 which has Hive 1.2.1000.

There's a workaround mentioned here (https://stackoverflow.com/questions/47794614/how-to-merge-orc-files-for-external-tables) which works when I try this:

ALTER TABLE DD_EXT_ORC SET TBLPROPERTIES('EXTERNAL'='FALSE');
ALTER TABLE DD_EXT_ORC CONCATENATE;
ALTER TABLE DD_EXT_ORC SET TBLPROPERTIES('EXTERNAL'='TRUE');

It's a little unsatisfactory (needs a TRY/FINALLY to ensure the final line is executed but don't think Hive-SQL has exception handling) so I would love to hear from anybody who has a better solution... Perhaps there's a hidden Hive parameter allowing CONCATENATE on external tables.

Re: Does alter table partition concatenate work on external hive tables?

Note that later versions of Hive (3.0+) appear not to support this workaround. To be marked as internal a table must be managed, hence transactional=true, but once a table is marked as transactional it cannot be reverted. See https://community.hortonworks.com/questions/129969/can-we-change-transactionalfalse-from-transaction...

Highlighted

Re: Does alter table partition concatenate work on external hive tables?

New Contributor

My first statement(

ALTER TABLE DD_EXT_ORC SET TBLPROPERTIES('EXTERNAL'='FALSE');

) fails with the following error :

 

Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Unable to alter table. Table test failed strict managed table checks due to the following reason: Table is marked as a managed table but is not transactional.

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