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.

Oozie Workflow Error when using Hive set commands in hive scripts

Highlighted

Oozie Workflow Error when using Hive set commands in hive scripts

Explorer

Hi All,

I am trying to create an oozie workflow which references a hive script which in turn is trying to create some tables.

Some of the tables are used for Acid Transactions and therefore in the beginning of the code I specified these settings

set hive.execution.engine=tez;

set hive.support.concurrency = true;

set hive.enforce.bucketing = true;

set hive.exec.dynamic.partition.mode = nonstrict;

set hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;

set hive.compactor.initiator.on = true; set hive.compactor.worker.threads = 1;

When I try to run that oozie job it throws this error.

5758-oozie-error.png

But it works fine when i try to load another hql file which just create non ORC/ACID table and uses no set commands.

So it seems oozie fails when we use set commands in hql file.

I was wondering if there is a way i an can set hive properties using oozie.

I tried using properties option but it fails again!

5757-oozie-error2.png

I am using Hue version

Hue™ 3.9.0 - The Hadoop UI

Also how can I run parallel hive scripts, I know in the old version of Hue we have a option to use Fork but I couldn't find this in Hue 3.9.0

5759-oozie-error3.png

Thanks for your help guys!


oozie-error.png
6 REPLIES 6
Highlighted

Re: Oozie Workflow Error when using Hive set commands in hive scripts

@Parinita Kothari

Can you please share the complete job.log? Also, what is the DDL for creating the Hive table?

Highlighted

Re: Oozie Workflow Error when using Hive set commands in hive scripts

Explorer

Hi @Sindhu,

Please see the logs below

2016-07-13 13:14:45,840  INFO ActionStartXCommand:520 - SERVER[vhdp0002.swe1.unibet.com] USER[parkot] GROUP[-] TOKEN[] APP[Unica_One-off_Load] JOB[0000013-160707120100026-oozie-oozi-W] ACTION[0000013-160707120100026-oozie-oozi-W@:start:] Start action [0000013-160707120100026-oozie-oozi-W@:start:] with user-retry state : userRetryCount [0], userRetryMax [0], userRetryInterval [10]
2016-07-13 13:14:45,845  INFO ActionStartXCommand:520 - SERVER[vhdp0002.swe1.unibet.com] USER[parkot] GROUP[-] TOKEN[] APP[Unica_One-off_Load] JOB[0000013-160707120100026-oozie-oozi-W] ACTION[0000013-160707120100026-oozie-oozi-W@:start:] [***0000013-160707120100026-oozie-oozi-W@:start:***]Action status=DONE
2016-07-13 13:14:45,846  INFO ActionStartXCommand:520 - SERVER[vhdp0002.swe1.unibet.com] USER[parkot] GROUP[-] TOKEN[] APP[Unica_One-off_Load] JOB[0000013-160707120100026-oozie-oozi-W] ACTION[0000013-160707120100026-oozie-oozi-W@:start:] [***0000013-160707120100026-oozie-oozi-W@:start:***]Action updated in DB!
2016-07-13 13:14:45,951  INFO WorkflowNotificationXCommand:520 - SERVER[vhdp0002.swe1.unibet.com] USER[-] GROUP[-] TOKEN[-] APP[-] JOB[0000013-160707120100026-oozie-oozi-W] ACTION[0000013-160707120100026-oozie-oozi-W@:start:] No Notification URL is defined. Therefore nothing to notify for job 0000013-160707120100026-oozie-oozi-W@:start:
2016-07-13 13:14:45,951  INFO WorkflowNotificationXCommand:520 - SERVER[vhdp0002.swe1.unibet.com] USER[-] GROUP[-] TOKEN[-] APP[-] JOB[0000013-160707120100026-oozie-oozi-W] ACTION[] No Notification URL is defined. Therefore nothing to notify for job 0000013-160707120100026-oozie-oozi-W
2016-07-13 13:14:45,979  INFO ActionStartXCommand:520 - SERVER[vhdp0002.swe1.unibet.com] USER[parkot] GROUP[-] TOKEN[] APP[Unica_One-off_Load] JOB[0000013-160707120100026-oozie-oozi-W] ACTION[0000013-160707120100026-oozie-oozi-W@hive2-f7c3] Start action [0000013-160707120100026-oozie-oozi-W@hive2-f7c3] with user-retry state : userRetryCount [0], userRetryMax [0], userRetryInterval [10]
2016-07-13 13:14:48,560  INFO Hive2Credentials:520 - SERVER[vhdp0002.swe1.unibet.com] USER[parkot] GROUP[-] TOKEN[] APP[Unica_One-off_Load] JOB[0000013-160707120100026-oozie-oozi-W] ACTION[0000013-160707120100026-oozie-oozi-W@hive2-f7c3] connecting to jdbcUrl: jdbc:hive2://vhdp0002.swe1.unibet.com:10010/default;principal=hive/vhdp0002.swe1.unibet.com@UNIBET.COM
2016-07-13 13:14:52,162  INFO Hive2ActionExecutor:520 - SERVER[vhdp0002.swe1.unibet.com] USER[parkot] GROUP[-] TOKEN[] APP[Unica_One-off_Load] JOB[0000013-160707120100026-oozie-oozi-W] ACTION[0000013-160707120100026-oozie-oozi-W@hive2-f7c3] Trying to get job [job_1467298952538_1461], attempt [1]
2016-07-13 13:14:52,186  INFO Hive2ActionExecutor:520 - SERVER[vhdp0002.swe1.unibet.com] USER[parkot] GROUP[-] TOKEN[] APP[Unica_One-off_Load] JOB[0000013-160707120100026-oozie-oozi-W] ACTION[0000013-160707120100026-oozie-oozi-W@hive2-f7c3] checking action, hadoop job ID [job_1467298952538_1461] status [RUNNING]
2016-07-13 13:14:52,188  INFO ActionStartXCommand:520 - SERVER[vhdp0002.swe1.unibet.com] USER[parkot] GROUP[-] TOKEN[] APP[Unica_One-off_Load] JOB[0000013-160707120100026-oozie-oozi-W] ACTION[0000013-160707120100026-oozie-oozi-W@hive2-f7c3] [***0000013-160707120100026-oozie-oozi-W@hive2-f7c3***]Action status=RUNNING
2016-07-13 13:14:52,188  INFO ActionStartXCommand:520 - SERVER[vhdp0002.swe1.unibet.com] USER[parkot] GROUP[-] TOKEN[] APP[Unica_One-off_Load] JOB[0000013-160707120100026-oozie-oozi-W] ACTION[0000013-160707120100026-oozie-oozi-W@hive2-f7c3] [***0000013-160707120100026-oozie-oozi-W@hive2-f7c3***]Action updated in DB!
2016-07-13 13:14:52,198  INFO WorkflowNotificationXCommand:520 - SERVER[vhdp0002.swe1.unibet.com] USER[-] GROUP[-] TOKEN[-] APP[-] JOB[0000013-160707120100026-oozie-oozi-W] ACTION[0000013-160707120100026-oozie-oozi-W@hive2-f7c3] No Notification URL is defined. Therefore nothing to notify for job 0000013-160707120100026-oozie-oozi-W@hive2-f7c3
2016-07-13 13:15:17,970  INFO CallbackServlet:520 - SERVER[vhdp0002.swe1.unibet.com] USER[-] GROUP[-] TOKEN[-] APP[-] JOB[0000013-160707120100026-oozie-oozi-W] ACTION[0000013-160707120100026-oozie-oozi-W@hive2-f7c3] callback for action [0000013-160707120100026-oozie-oozi-W@hive2-f7c3]
2016-07-13 13:15:18,237  INFO Hive2ActionExecutor:520 - SERVER[vhdp0002.swe1.unibet.com] USER[parkot] GROUP[-] TOKEN[] APP[Unica_One-off_Load] JOB[0000013-160707120100026-oozie-oozi-W] ACTION[0000013-160707120100026-oozie-oozi-W@hive2-f7c3] Trying to get job [job_1467298952538_1461], attempt [1]
2016-07-13 13:15:18,391  INFO Hive2ActionExecutor:520 - SERVER[vhdp0002.swe1.unibet.com] USER[parkot] GROUP[-] TOKEN[] APP[Unica_One-off_Load] JOB[0000013-160707120100026-oozie-oozi-W] ACTION[0000013-160707120100026-oozie-oozi-W@hive2-f7c3] action completed, external ID [job_1467298952538_1461]
2016-07-13 13:15:18,410  WARN Hive2ActionExecutor:523 - SERVER[vhdp0002.swe1.unibet.com] USER[parkot] GROUP[-] TOKEN[] APP[Unica_One-off_Load] JOB[0000013-160707120100026-oozie-oozi-W] ACTION[0000013-160707120100026-oozie-oozi-W@hive2-f7c3] Launcher ERROR, reason: Main class [org.apache.oozie.action.hadoop.Hive2Main], exit code [2]
2016-07-13 13:15:18,509  INFO ActionEndXCommand:520 - SERVER[vhdp0002.swe1.unibet.com] USER[parkot] GROUP[-] TOKEN[] APP[Unica_One-off_Load] JOB[0000013-160707120100026-oozie-oozi-W] ACTION[0000013-160707120100026-oozie-oozi-W@hive2-f7c3] ERROR is considered as FAILED for SLA
2016-07-13 13:15:18,569  INFO ActionStartXCommand:520 - SERVER[vhdp0002.swe1.unibet.com] USER[parkot] GROUP[-] TOKEN[] APP[Unica_One-off_Load] JOB[0000013-160707120100026-oozie-oozi-W] ACTION[0000013-160707120100026-oozie-oozi-W@Kill] Start action [0000013-160707120100026-oozie-oozi-W@Kill] with user-retry state : userRetryCount [0], userRetryMax [0], userRetryInterval [10]
2016-07-13 13:15:18,573  INFO ActionStartXCommand:520 - SERVER[vhdp0002.swe1.unibet.com] USER[parkot] GROUP[-] TOKEN[] APP[Unica_One-off_Load] JOB[0000013-160707120100026-oozie-oozi-W] ACTION[0000013-160707120100026-oozie-oozi-W@Kill] [***0000013-160707120100026-oozie-oozi-W@Kill***]Action status=DONE
2016-07-13 13:15:18,574  INFO ActionStartXCommand:520 - SERVER[vhdp0002.swe1.unibet.com] USER[parkot] GROUP[-] TOKEN[] APP[Unica_One-off_Load] JOB[0000013-160707120100026-oozie-oozi-W] ACTION[0000013-160707120100026-oozie-oozi-W@Kill] [***0000013-160707120100026-oozie-oozi-W@Kill***]Action updated in DB!
2016-07-13 13:15:18,701  INFO WorkflowNotificationXCommand:520 - SERVER[vhdp0002.swe1.unibet.com] USER[-] GROUP[-] TOKEN[-] APP[-] JOB[0000013-160707120100026-oozie-oozi-W] ACTION[0000013-160707120100026-oozie-oozi-W@Kill] No Notification URL is defined. Therefore nothing to notify for job 0000013-160707120100026-oozie-oozi-W@Kill
2016-07-13 13:15:18,702  INFO WorkflowNotificationXCommand:520 - SERVER[vhdp0002.swe1.unibet.com] USER[-] GROUP[-] TOKEN[-] APP[-] JOB[0000013-160707120100026-oozie-oozi-W] ACTION[0000013-160707120100026-oozie-oozi-W@hive2-f7c3] No Notification URL is defined. Therefore nothing to notify for job 0000013-160707120100026-oozie-oozi-W@hive2-f7c3
2016-07-13 13:15:18,702  INFO WorkflowNotificationXCommand:520 - SERVER[vhdp0002.swe1.unibet.com] USER[-] GROUP[-] TOKEN[-] APP[-] JOB[0000013-160707120100026-oozie-oozi-W] ACTION[] No Notification URL is defined. Therefore nothing to notify for job 0000013-160707120100026-oozie-oozi-W

My .hql script looks like below

use unica;


set hive.execution.engine=tez;
set hive.support.concurrency = true;
set hive.enforce.bucketing = true;
set hive.exec.dynamic.partition.mode = nonstrict;
set hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
set hive.compactor.initiator.on = true;
set hive.compactor.worker.threads = 1;




set -x
--DW_FACT_PUNTER


DROP TABLE unica.DW_FACT_PUNTER;


CREATE TABLE IF NOT EXISTS unica.DW_FACT_PUNTER (TIME_KEY DECIMAL(36,2), PUNTER_KEY DECIMAL(36,2), PRODUCT_KEY DECIMAL(36,2), INDICATOR_KEY DECIMAL(36,2), INDICATOR_VALUE DECIMAL(36,2), INSERT_TIME STRING)  clustered by (TIME_KEY) into 10 buckets stored as orc TBLPROPERTIES ('transactional' = 'true');

Re: Oozie Workflow Error when using Hive set commands in hive scripts

Contributor

Hi @Parinita Kothari, just drop a second hive action on one of the grey boxes on the left and right of your other hive action like this:

5766-snag-160713-0027.png

5767-snag-160713-0028.png

The editor creates the fork and joiner for you automatically.

Highlighted

Re: Oozie Workflow Error when using Hive set commands in hive scripts

Explorer

Thank you @Rene Sluiter

Highlighted

Re: Oozie Workflow Error when using Hive set commands in hive scripts

Contributor

regarding your real problem I'm afraid I can't help you. I just tried it myself and also for me all queries are executed with mapreduce even thought the engine in hive and beeline is configured for tez.

I did manage to set the queue for mapreduce to a different value mapreduce.job.queuename in the same properties section as you tried yours..

'Funny' thing is that when I check the stdout of the joblog I do see my hive.execution.engine=tez in the "Hive Configuration Properties:" section..

Highlighted

Re: Oozie Workflow Error when using Hive set commands in hive scripts

Explorer

Hi @Rene Sluiter,

How about rest of the hive settings for ACID transactions, were you able to set them in oozie?

  1. set hive.support.concurrency =true;
  2. set hive.enforce.bucketing =true;
  3. set hive.exec.dynamic.partition.mode = nonstrict;
  4. set hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
  5. set hive.compactor.initiator.on =true;
  6. set hive.compactor.worker.threads =1;

Regards,

Parinita

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