SQL to Hive Query Conversion

[#1]

Hi Team,

I have below requirement to convert the SQL server queries into hive. But i am not sure how to achive that as there is no pivot and unpivot available in hive.

could you please help me with the hive equivalent queries for the below SQL queries?

Query 1
-----------------

SELECT x,y,z
FROM dbo.tbl1 UNPIVOT (split FOR check IN (1, y2, y3, y4, y5, y6, y7, y8, y9, y10)) AS splnpivot

Query 2
----------------

SELECT *
FROM
(SELECT a,b,c FROM tbl2) SRC
PIVOT (
SUM(TOTAL_AMOUNT) FOR cbn_TYPE IN ([ONE TQ FOUR], [going loss], [COSTS LEAVING team sales], [profit], [check money])) AS PIV;

Query 3
--------------

NULLIF (ISNULL(abc.Tc, 0) + ISNULL(abc.YR, 0), 0);

Thanks in advance,

Wasim

[#2 · Accepted solution]

In Hive, you can achieve a similar result as the UNPIVOT operation in SQL Server by using the LATERAL VIEW and lateral VIEW OUTER explode functions to split the columns into rows. Here's how you can convert your SQL Server query to Hive:

SELECT x, check AS y, split AS z
FROM dbo.tbl1
LATERAL VIEW OUTER explode(array(1, y2, y3, y4, y5, y6, y7, y8, y9, y10)) tbl AS split;

In this Hive query:

• LATERAL VIEW OUTER explode is used to split the values from columns y2 to y10 into separate rows.
• The AS clause assigns aliases to the columns, where split corresponds to the values from the UNPIVOTed columns, check corresponds to the column name (y), and x remains unchanged.

This query will produce a result similar to the UNPIVOT operation in SQL Server, where the values from columns y2 to y10 are split into separate rows along with their corresponding x and y values.

In Hive, you can achieve a similar result as the PIVOT operation in SQL Server by using conditional aggregation along with CASE statements. Here's how you can convert your SQL Server query to Hive:

SELECT *
FROM (
  SELECT a, b, c, cbn_TYPE
  FROM tbl2
) SRC
LEFT JOIN (
  SELECT
    a,
    SUM(CASE WHEN cbn_TYPE = 'ONE TQ FOUR' THEN TOTAL_AMOUNT ELSE 0 END) AS ONE_TQ_FOUR,
    SUM(CASE WHEN cbn_TYPE = 'going loss' THEN TOTAL_AMOUNT ELSE 0 END) AS going_loss,
    SUM(CASE WHEN cbn_TYPE = 'COSTS LEAVING team sales' THEN TOTAL_AMOUNT ELSE 0 END) AS COSTS_LEAVING_team_sales,
    SUM(CASE WHEN cbn_TYPE = 'profit' THEN TOTAL_AMOUNT ELSE 0 END) AS profit,
    SUM(CASE WHEN cbn_TYPE = 'check money' THEN TOTAL_AMOUNT ELSE 0 END) AS check_money
  FROM tbl2
  GROUP BY a
) PIV
ON SRC.a = PIV.a;

In this Hive query:

• We first create an intermediate result set (PIV) that calculates the sums for each cbn_TYPE using conditional aggregation (SUM with CASE statements).

• The LEFT JOIN is used to combine the original source table (SRC) with the aggregated result (PIV) based on the common column a.

• The result will have columns a, b, c, and the pivoted columns ONE_TQ_FOUR, going_loss, COSTS_LEAVING_team_sales, profit, and check_money, similar to the PIVOT operation in SQL Server.

This query essentially performs a manual pivot operation in Hive by using conditional aggregation to calculate the sums for each cbn_TYPE and then joining the results back to the original table.

In Hive, you can use the CASE statement to achieve the same result as the SQL Server expression NULLIF(ISNULL(abc.Tc, 0) + ISNULL(abc.YR, 0), 0). Here's the equivalent Hive query:

SELECT
  CASE
    WHEN (abc.Tc IS NULL AND abc.YR IS NULL) OR (abc.Tc + abc.YR = 0) THEN NULL
    ELSE abc.Tc + abc.YR
  END AS result
FROM your_table AS abc;

In this Hive query:

• We use the CASE statement to conditionally calculate the result.

• If both abc.Tc and abc.YR are NULL, or if their sum is equal to 0, we return NULL.

• Otherwise, we return the sum of abc.Tc and abc.YR.

This query replicates the behavior of the NULLIF(ISNULL(abc.Tc, 0) + ISNULL(abc.YR, 0), 0) expression in SQL Server, providing a Hive-compatible solution for achieving the same result.