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.

Help converting Teradata SQL to Hive

Solved Go to solution

Help converting Teradata SQL to Hive

Contributor

Can anyone help converting this Teradata SQL to something that will run in Hive? Currently it fails with the error "SemanticException Line 0:-1 Both left and right aliases encountered in JOIN 'Week_End'" and my understanding of SQL is too limited to understand how to refactor the query.

(SELECT
S.Calendar_Date Calendar_Date
,S.Foll_Monday Foll_Monday
,W.Week_Id Snapshot_Id
FROM
(
SELECT calendar_date
,CASE WHEN day_of_week = 1 THEN calendar_date + 1
WHEN day_of_week = 3 THEN calendar_date + 6
WHEN day_of_week = 4 THEN calendar_date + 5
WHEN day_of_week = 5 THEN calendar_date + 4
WHEN day_of_week = 6 THEN calendar_date + 3
WHEN day_of_week = 7 THEN calendar_date + 2
ELSE calendar_date END AS Foll_Monday
FROM td_etl.calendar )
S LEFT JOIN td_etl.dim_cal W
ON S.Foll_Monday BETWEEN W.Week_Start AND W.Week_End) FM 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Help converting Teradata SQL to Hive

Master Collaborator

Without knowing your schema, here is a rough take:

(SELECT
S.Calendar_Date Calendar_Date
,S.Foll_Monday Foll_Monday
,W.Week_Id Snapshot_Id
FROM
(
SELECT calendar_date,
(
CASE day_of_week
  WHEN 1 THEN calendar_date + 1
  WHEN 3 THEN calendar_date + 6
  WHEN 4 THEN calendar_date + 5
  WHEN 5 THEN calendar_date + 4
  WHEN 6 THEN calendar_date + 3
  WHEN 7 THEN calendar_date + 2
  ELSE calendar_date
END
) Foll_Monday
FROM td_etl.calendar )
S LEFT JOIN td_etl.dim_cal W
ON S.Foll_Monday BETWEEN W.Week_Start AND W.Week_End) FM
5 REPLIES 5
Highlighted

Re: Help converting Teradata SQL to Hive

Master Collaborator

Without knowing your schema, here is a rough take:

(SELECT
S.Calendar_Date Calendar_Date
,S.Foll_Monday Foll_Monday
,W.Week_Id Snapshot_Id
FROM
(
SELECT calendar_date,
(
CASE day_of_week
  WHEN 1 THEN calendar_date + 1
  WHEN 3 THEN calendar_date + 6
  WHEN 4 THEN calendar_date + 5
  WHEN 5 THEN calendar_date + 4
  WHEN 6 THEN calendar_date + 3
  WHEN 7 THEN calendar_date + 2
  ELSE calendar_date
END
) Foll_Monday
FROM td_etl.calendar )
S LEFT JOIN td_etl.dim_cal W
ON S.Foll_Monday BETWEEN W.Week_Start AND W.Week_End) FM

Re: Help converting Teradata SQL to Hive

Expert Contributor

I don't think this left join with a non equi condition will work with Hive.

Check the documentation I wrote for a workaround:

https://wiki.hortonworks.com/display/IK/Hive+non+equi+left+join

Re: Help converting Teradata SQL to Hive

New Contributor

agree with Sourygna. Deepesh, did you try this.? I dont think hive currently supports non equi joins.

Sourygna workaround should work here.

Re: Help converting Teradata SQL to Hive

Master Collaborator

No, I did not test this. I basically just converted the case statement. Sourygna's point is right, the non-equi join will have to be transformed in order for this to work in Hive.

Re: Help converting Teradata SQL to Hive

In this case, if we look at the <intent> of the query it is possible that it can be rewritten to not need a non-equi-join.

If you can make this Assumption about the Date Dimension:

A Date Value in the date dimension (such as 'Foll_Monday' in the query) will always be between the Week_Start and the Week_End values.

Then you should be able to rewrite the query to not use a non-equi join. The second table in the join would become:

(SELECT
S.Calendar_DateCalendar_Date
,S.Foll_MondayFoll_Monday
,W.Week_Id, Snapshot_Id
FROM
(
SELECT calendar_date,
(
CASE day_of_week
WHEN 1 THEN calendar_date +1
WHEN 3 THEN calendar_date +6
WHEN 4 THEN calendar_date +5
WHEN 5 THEN calendar_date +4
WHEN 6 THEN calendar_date +3
WHEN 7 THEN calendar_date +2
ELSE calendar_date
END
) Foll_Monday
FROM td_etl.calendar)  S 
   inner join ( 
       select <NaturalDateValue>, Week_Id, week_Start, Week_End from td_etl.dim_cal ) W
    on S.foll_monday = W.<NaturalDateValue>

Give that a try.