question Re: HCatStorer is not overwriting a Hive table. or How to overwrite a hive table using HCatStorer ? in Archives of Support Questions (Read Only)

HCatStorer is not overwriting a Hive table. or How to overwrite a hive table using HCatStorer ?

PentaReddy — Fri, 16 Dec 2016 14:45:34 GMT

Re: HCatStorer is not overwriting a Hive table. or How to overwrite a hive table using HCatStorer ?

aervits — Fri, 16 Dec 2016 14:50:28 GMT

it's easier to drop a table and recreate, delete from a table and truncate are not supported by API

set hcat.bin /usr/bin/hcat;
sql drop table if exists codes;
sql create table codes(code string, description string, total_emp int, salary int);
a = load 'sample_07' using org.apache.hive.hcatalog.pig.HCatLoader();
b = load 'sample_08' using org.apache.hive.hcatalog.pig.HCatLoader();
c = join b by code, a by code;d = foreach c generate $0 as code, $1 as description, $2 as total_emp, $3 as salary;
store d into 'codes' using org.apache.hive.hcatalog.pig.HCatStorer();

Re: HCatStorer is not overwriting a Hive table. or How to overwrite a hive table using HCatStorer ?

PentaReddy — Fri, 16 Dec 2016 14:55:50 GMT

Thanks for you response, The above code is writing back the data to another table after the transformation.however, i don't want to delete the data/ drop the table and i want to overwrite the same table after my transformation in pig.

Re: HCatStorer is not overwriting a Hive table. or How to overwrite a hive table using HCatStorer ?

aervits — Fri, 16 Dec 2016 15:16:38 GMT

then you need a temporary table to store the intermediary data. This is ugly but works, there's probably a better way but it's late where I am 🙂

set hcat.bin /usr/bin/hcat;
a = load 'codes' using org.apache.hive.hcatalog.pig.HCatLoader();
b = foreach a generate $0 as code, $1 as description, $2 as total_emp, $3 as salary;
sql drop table if exists codes_temp;
sql create table codes_temp(code string, description string, total_emp int, salary int);
store b into 'codes_temp' using org.apache.hive.hcatalog.pig.HCatStorer();
sql drop table if exists codes;
sql create table codes(code string, description string, total_emp int, salary int);
c = load 'codes_temp' using org.apache.hive.hcatalog.pig.HCatLoader();
d = foreach c generate $0 as code, $1 as description, $2 as total_emp, $3 as salary;
store d into 'codes' using org.apache.hive.hcatalog.pig.HCatStorer();
sql drop table if exists codes_temp;

Re: HCatStorer is not overwriting a Hive table. or How to overwrite a hive table using HCatStorer ?

aervits — Fri, 16 Dec 2016 15:26:06 GMT

@Praveen PentaReddy I think I understand what you mean now, you may have run into a bug. What version of Pig and HCatalog are you running? I just tested on Sandbox with HDP 2.5 and it works

set hcat.bin /usr/bin/hcat;
a = load 'codes' using org.apache.hive.hcatalog.pig.HCatLoader();
b = foreach a generate $0 as code, $1 as description, $2 as total_emp, $3 as salary;
store b into 'codes' using org.apache.hive.hcatalog.pig.HCatStorer();

my codes table contained 823 rows, after this execution, it contains 1646 as expected

per hcatalog wiki

You can write to a non-partitioned table simply by using HCatStorer. The contents of the table will be overwritten:

store z into 'web_data' using org.apache.hive.hcatalog.pig.HCatStorer();

https://cwiki.apache.org/confluence/display/Hive/HCatalog+LoadStore#HCatalogLoadStore-HCatStorer

Re: HCatStorer is not overwriting a Hive table. or How to overwrite a hive table using HCatStorer ?

PentaReddy — Fri, 16 Dec 2016 23:56:11 GMT

Thanks for your replay.

Should i need to add the first step(set command bin/hcat) every time when i launch Hcat.

I am using Hadoop 2.7.1.2.4.2.0-258 (version)

Apache Pig version 0.15.0.2.4.2.0-258 (rexported) - verison

Hive 1.2.1000.2.4.2.0-258 - version,

Re: HCatStorer is not overwriting a Hive table. or How to overwrite a hive table using HCatStorer ?

aervits — Sat, 17 Dec 2016 00:08:48 GMT

@Praveen PentaReddy

that's interesting, do you have another environment to test your script on? Like I said I have no issues on my end. The answer to your question about set command, I answered that in the following thread https://community.hortonworks.com/questions/1954/hcatbin-is-not-defined-define-it-to-be-your-hcat-s.html

If my answer was useful and resolved your issue, please accept it as best.

Re: HCatStorer is not overwriting a Hive table. or How to overwrite a hive table using HCatStorer ?

PentaReddy — Sat, 17 Dec 2016 00:13:18 GMT

Yaah.. i have another version and let me know test on it and will get back with you with the result.

Re: HCatStorer is not overwriting a Hive table. or How to overwrite a hive table using HCatStorer ?

PentaReddy — Sat, 17 Dec 2016 02:29:32 GMT

Per your last conversation it looks like HcatStorer is not overwriting the data, rather it is appending the data.

In your last test, you were having the record count of 823 before the transformation and after the transformation you stored back the results and the result count is 1646.(which is nothing but appending the data).

My expectations are below.

Read the records of 823 and do transformation on it and update back the result and once updated the result count should be 823(overwriting the data with new transformation data) and not 1643.

Is this kind of overwriting the data is possible through HCatStorer ? if not is there any other alternatives ways of overwriting the data in pig ( Eg :- PigStorage or any other functions).

Re: HCatStorer is not overwriting a Hive table. or How to overwrite a hive table using HCatStorer ?

PentaReddy — Sat, 17 Dec 2016 06:19:18 GMT

Any Help highly appreciated.

Re: HCatStorer is not overwriting a Hive table. or How to overwrite a hive table using HCatStorer ?

aervits — Sat, 17 Dec 2016 11:25:37 GMT

Is my workaround with a temporary table not good enough for your use case? Save output of your transformation to a temp table, drop and create your original table and save your temp result back.

Re: HCatStorer is not overwriting a Hive table. or How to overwrite a hive table using HCatStorer ?

PentaReddy — Sun, 18 Dec 2016 13:44:11 GMT

I think that's the only solution i think is the best at the moment using temporary table. Does Overwrite exists in Pig? Like Hive Overwrite table data.

Re: HCatStorer is not overwriting a Hive table. or How to overwrite a hive table using HCatStorer ?

aervits — Mon, 19 Dec 2016 01:11:52 GMT

According to the wiki it exists but in our mutual tests, we were not able to fit your use case. Perhaps you'd like to open an enhancement Jira. I do like integration of Pig and HCatalog though I would argue instead of spending hours to figure it out, consider looking into Spark as it's under a heavy development and benefits from a wider community. If my workaround fits your needs, please consider accepting my answer as best.

Re: HCatStorer is not overwriting a Hive table. or How to overwrite a hive table using HCatStorer ?

PentaReddy — Tue, 20 Dec 2016 04:30:20 GMT

Yes, This is testing only and i am doing it out of my curiosity. Thanks you very much for your valuable responses.

Could you let me know how to open an enhancement Jira request ?

Re: HCatStorer is not overwriting a Hive table. or How to overwrite a hive table using HCatStorer ?

aervits — Tue, 20 Dec 2016 04:33:42 GMT

@Praveen PentaReddy create an Apache Jira account and open a ticket on https://issues.apache.org/jira/browse/pig/?selectedTab=com.atlassian.jira.jira-projects-plugin:summary-panel apache pig jira page.

Re: HCatStorer is not overwriting a Hive table. or How to overwrite a hive table using HCatStorer ?

PentaReddy — Tue, 20 Dec 2016 04:58:14 GMT

Thanks, i have raised an enhancement Jira.

Re: HCatStorer is not overwriting a Hive table. or How to overwrite a hive table using HCatStorer ?

aervits — Tue, 20 Dec 2016 08:44:24 GMT

Can you paste a link to your Jira and accept my answer as workaround. Thanks

Re: HCatStorer is not overwriting a Hive table. or How to overwrite a hive table using HCatStorer ?

PentaReddy — Wed, 21 Dec 2016 02:28:46 GMT

https://issues.apache.org/jira/browse/PIG-5079

Re: HCatStorer is not overwriting a Hive table. or How to overwrite a hive table using HCatStorer ?

cstanca — Wed, 21 Dec 2016 04:10:07 GMT

@Praveen PentaReddy

Thanks for submitting the JIRA ticket. I reviewed Artem's response and it seems that until the enhancement is implemented, his response is still the best option as of right now.

Re: HCatStorer is not overwriting a Hive table. or How to overwrite a hive table using HCatStorer ?

aervits — Fri, 30 Dec 2016 05:00:16 GMT

@Praveen PentaReddy

to close the loop on this, turns out append is the default behavior and if you read the comments in https://issues.apache.org/jira/browse/HIVE-6897 you can see that it is not advisable to force an overwrite of a table via HCatalog. So to turn the feature off completely and not promote "bad" behavior I did the following

grunt> sql alter table codeZ set TBLPROPERTIES ('immutable' = 'true');
2016-12-29 20:49:51,924 [main] INFO  org.apache.pig.tools.grunt.GruntParser - Going to run hcat command: alter table codeZ set TBLPROPERTIES ('immutable' = 'true');
OK
Time taken: 2.041 seconds
grunt> a = load 'sample_07' using org.apache.hive.hcatalog.pig.HCatLoader();
2016-12-29 20:51:00,125 [main] INFO  hive.metastore - Trying to connect to metastore with URI thrift://sandbox.hortonworks.com:9083
2016-12-29 20:51:00,163 [main] INFO  hive.metastore - Connected to metastore.
grunt> b = load 'sample_08' using org.apache.hive.hcatalog.pig.HCatLoader();
grunt> c = join b by code, a by code;
grunt> d = foreach c generate $0 as code, $1 as description, $2 as total_emp, $3 as salary;
grunt> store d into 'codeZ' using org.apache.hive.hcatalog.pig.HCatStorer();
2016-12-29 20:52:26,894 [main] ERROR org.apache.pig.tools.grunt.Grunt - ERROR 6000:
<line 5, column 0> Output Location Validation Failed for: 'codeZ More info to follow:
org.apache.hive.hcatalog.common.HCatException : 2003 : Non-partitioned table already contains data : default.codez
Details at logfile: /home/guest/pig_1483044536026.log
grunt> quit
2016-12-29 20:56:25,336 [main] INFO  org.apache.pig.Main - Pig script completed in 7 minutes, 29 seconds and 397 milliseconds (449397 ms)
[guest@sandbox ~]$ less /home/guest/pig_1483044536026.log

and snippet from log

ERROR 6000:
<line 5, column 0> Output Location Validation Failed for: 'codeZ More info to follow:
org.apache.hive.hcatalog.common.HCatException : 2003 : Non-partitioned table already contains data : default.codez


org.apache.pig.impl.logicalLayer.FrontendException: ERROR 1002: Unable to store alias d
        at org.apache.pig.PigServer$Graph.registerQuery(PigServer.java:1778)
        at org.apache.pig.PigServer.registerQuery(PigServer.java:707)
        at org.apache.pig.tools.grunt.GruntParser.processPig(GruntParser.java:1075)
        at org.apache.pig.tools.pigscript.parser.PigScriptParser.parse(PigScriptParser.java:505)
        at org.apache.pig.tools.grunt.GruntParser.parseStopOnError(GruntParser.java:231)
        at org.apache.pig.tools.grunt.GruntParser.parseStopOnError(GruntParser.java:206)
        at org.apache.pig.tools.grunt.Grunt.run(Grunt.java:66)
        at org.apache.pig.Main.run(Main.java:566)
        at org.apache.pig.Main.main(Main.java:178)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.hadoop.util.RunJar.run(RunJar.java:233)
        at org.apache.hadoop.util.RunJar.main(RunJar.java:148)
Caused by: org.apache.pig.impl.plan.VisitorException: ERROR 6000:
<line 5, column 0> Output Location Validation Failed for: 'codeZ More info to follow:
org.apache.hive.hcatalog.common.HCatException : 2003 : Non-partitioned table already contains data : default.codez
        at org.apache.pig.newplan.logical.visitor.InputOutputFileValidatorVisitor.visit(InputOutputFileValidatorVisitor.java:95)
        at org.apache.pig.newplan.logical.relational.LOStore.accept(LOStore.java:66)
        at org.apache.pig.newplan.DepthFirstWalker.depthFirst(DepthFirstWalker.java:64)
        at org.apache.pig.newplan.DepthFirstWalker.depthFirst(DepthFirstWalker.java:66)
        at org.apache.pig.newplan.DepthFirstWalker.depthFirst(DepthFirstWalker.java:66)
        at org.apache.pig.newplan.DepthFirstWalker.depthFirst(DepthFirstWalker.java:66)
        at org.apache.pig.newplan.DepthFirstWalker.walk(DepthFirstWalker.java:53)
        at org.apache.pig.newplan.PlanVisitor.visit(PlanVisitor.java:52)
        at org.apache.pig.newplan.logical.relational.LogicalPlan.validate(LogicalPlan.java:212)
        at org.apache.pig.PigServer$Graph.compile(PigServer.java:1851)
        at org.apache.pig.PigServer$Graph.access$300(PigServer.java:1527)
        at org.apache.pig.PigServer.execute(PigServer.java:1440)
        at org.apache.pig.PigServer.access$500(PigServer.java:118)
        at org.apache.pig.PigServer$Graph.registerQuery(PigServer.java:1773)
        ... 14 more
Caused by: org.apache.hive.hcatalog.common.HCatException : 2003 : Non-partitioned table already contains data : default.codez
        at org.apache.hive.hcatalog.mapreduce.FileOutputFormatContainer.handleDuplicatePublish(FileOutputFormatContainer.java:206)
        at org.apache.hive.hcatalog.mapreduce.FileOutputFormatContainer.checkOutputSpecs(FileOutputFormatContainer.java:121)
        at org.apache.hive.hcatalog.mapreduce.HCatBaseOutputFormat.checkOutputSpecs(HCatBaseOutputFormat.java:65)
        at org.apache.pig.newplan.logical.visitor.InputOutputFileValidatorVisitor.visit(InputOutputFileValidatorVisitor.java:69)

in other words, it is not advisable to overwrite via HCatStorer as Hive handles append/overwrite. The only workaround here is to use a temporary table as I suggested earlier.