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.

Query producing the results in Hive but returns no data when executed in spark using Hive context.

Highlighted

Query producing the results in Hive but returns no data when executed in spark using Hive context.

New Contributor

Below query when executed in Hive CLI produces result but not through spark using Hivecontext.

val s = """select cast(date as String) as date_key,
field1,
field2,
field3,
field4,
COUNT(CASE WHEN m.field5 in ('Core','A') THEN a.strRXNUM END) AS result1,
COUNT(CASE WHEN m.field6 in ('Advanced','D') THEN a.strRXNUM END) AS resul2,
COUNT(CASE WHEN m.field7 in ('Custom Core','g') THEN a.strRXNUM END) AS result3,
COUNT(CASE WHEN m.field8 in ('Unassigned') THEN a.strRXNUM END) AS result4,
COUNT(a.strRXNUM) AS result4
from
$targetdb.table1 a left join $sourcedb.table2 m
ON
a.strTHERAPY_TYPE = m.strTherapyType
inner join
$sourcedb.table3 b
on
cast(a.dtshipment_date as string)=cast(b.thedate as string)
where a.date between '2015-01-01' and date_sub(current_date(),1)
group by
cast(date as String),
field1,
field2,
field3,
field4 """

val df=Hivectx.sql(s)

2 REPLIES 2

Re: Query producing the results in Hive but returns no data when executed in spark using Hive context.

Expert Contributor

What does Hivectx.sql(s).show() give?

Does Hivectx.sql('show tables').show() gives expected output?

How are you giving values to $targetdb,$sourcedb?

Re: Query producing the results in Hive but returns no data when executed in spark using Hive context.

New Contributor

Hive ctx..sql(s).show was not giving expected output i.e a blank table.

Passed values to source and target db through arguments with spark submit.

I was able to find the issue-Worked after I Converted dtshipment date and thedate both to timestamp for camparison.

While running the query in hive cli it was able to compare dtshipment_date(date type) with thedate(timestamp) and give the records on join but through spark sql it was not giving same output until I converted both to timestamp.

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