Hadoop Hive Task on SSIS return error code 64

user_12229911 0
Viewed 503

I'm trying to run Hive query using Hadoop Hive Task in SSIS 2015. The query that I tried to run was:

create table if not exists table_a
as
select * from (
    select
    code,
    md,
    name,
    region,
    dingestdate,
    max(dingestdate) over (partition by kode_maindealer) as dingestdate_latest
    from dev_stg.stg_ahmsdnssls_xls_master_data_region
) as test1
where dingestdate = dingestdate_latest

This query run ok in Hue - Hive Editor and in Hive CLI, but return error in SSIS. The error messages:

[Hadoop Hive Task] Information: Hadoop job job_1561011012251_25231 exit value is 64
[Hadoop Hive Task] Error: Hadoop job job_1561011012251_25231 exit value is not 0
Task Hadoop Hive Task failed

Have someone experiencing this with SSIS?

cloudera etl hive hadoop ssis
2 Answers

I've solved the issue by splitting the query into 2 parts:

create table if not exists table_a (
code int,
md varchar(150),
name varchar(150),
region varchar(50),
dingestdate timestamp, 
dingestdate_latest timestamp
)
row format delimited fields terminated by '|' 
stored as parquet 
tblproperties("parquet.compression"="gzip")
;

and

insert overwrite table table_a
select * from ( 
select 
code, 
md, 
name, 
region, 
dingestdate, 
max(dingestdate) over (partition by code) as dingestdate_latest 
from table1 ) as test1 where dingestdate = dingestdate_latest
;

and finally the query runs without error in SSIS.

I'll try the 'Execute Process Task' on SSIS too, to see if it also works.

Thank you.

user_122299110
user_70312300
Related