MS SQL to HIVE code conversion

See more:

SQL

SQL-Server

hadoop

big-data

, +

I'm trying to convert a piece of SQL code to HiveQL, and it's not working as expected.

Please find below the code snippet in SQL that I'm attempting to convert:

SQL Code:

UPDATE
   C 
SET
   C.prod_l = P.prod_l, C.numprod = P.numprod, C.prod_cng = P.prod_cng 
FROM
   [cnc].dbo.[c_cnc_analysis] C 
   LEFT JOIN
      (
         SELECT
            X.*,
            Len(prod_l) - Len(Replace(prod_l, ' ~ ', '  ')) + 1 AS NumProd,
            CASE
               WHEN
                  Len(prod_l) - Len(Replace(prod_l, ' ~ ', '  ')) + 1 = 1 
               THEN
                  0 
               ELSE
                  1 
            END
            AS PROD_CNG 
         FROM
            (
               SELECT DISTINCT
                  ST2.uitid,
                  Substring((
                  SELECT
                     ' ~ ' + ST1.product_id AS [text()] 
                  FROM
                     (
                        SELECT
                           [uitid],
                           [product_id] 
                        FROM
                           dbo.[c_cnc_dedup_bse] 
                        GROUP BY
                           [uitid],
                           [product_id]
                     )
                     ST1 
                  WHERE
                     ST1.uitid = ST2.uitid 
                  ORDER BY
                     ST1.uitid FOR xml path ('')), 4, 1000 ) [PROD_L] 
                  FROM
                     (
                        SELECT
                           [uitid],
                           [product_id] 
                        FROM
                           dbo.[c_cnc_dedup_bse] 
                        GROUP BY
                           [uitid],
                           [product_id]
                     )
                     ST2
            )
            X
      )
      P 
      ON C.uitid = P.uitid;

Converted HIVE Query:

create 
or replace view prd_temp as 
SELECT
   `UITID`,
   `PRODUCT_ID` 
FROM
   `C_CNC_DEDUP_BSE` 
GROUP BY
   `UITID`,
   `PRODUCT_ID`;
create 
or replace view prd_temp2 as 
SELECT
   `UITID`,
   `PRODUCT_ID` 
FROM
   `C_CNC_DEDUP_BSE` 
GROUP BY
   `UITID`,
   `PRODUCT_ID`;
create 
or replace view prd_temp3 as 
SELECT
   st1.`uitid`,
   concat(' ~ ', st1.`PRODUCT_ID`) AS `text()` 
FROM
   prd_temp ST1 
   left join
      prd_temp2 st2 
      on ST1.`UITID` = ST2.`UITID` 
where
   st1.`UITID` = st2.`UITID` 
ORDER BY
   ST1.`UITID`;
create 
or replace view prd_temp4 as 
SELECT
   st1.`uitid`,
   concat_ws('''', `text()`) 
FROM
   prd_temp3 ST1 
ORDER BY
   ST1.`UITID`;
create 
or replace view st2 as 
SELECT DISTINCT
   `UITID`,
   SUBSTRING(`_c1` , 4, 1000) as `PROD_L` 
FROM
   prd_temp4;
create 
or replace view x as 
SELECT
   *,
   LENGTH(PROD_L) - LENGTH(REPLACE(PROD_L, ' ~ ', '  ')) + 1 as NumProd,
   CASE
      WHEN
         LENGTH(PROD_L) - LENGTH(REPLACE(PROD_L, ' ~ ', '  ')) + 1 = 1 
      then
         0 
      ELSE
         1 
   END
   as PROD_CNG 
from
   ST2;
create table C_CNC_ANALYSIS1 as 
select
   c.*,
   P.numprod as numprod,
   p.prod_cng as prod_cng,
   p.prod_l as prod_l 
from
   `C_CNC_ANALYSIS` C 
   LEFT JOIN
      X P 
      ON C.UITID = P.UITID ;
SELECT
   * 
from
   c_cnc_analysis1 limit 100;

Appreciate all the help with this. I think the code converted for the XML path is not working in HIVE, since I'm getting multiple UITIDs (key) and the information in separate rows rather than just one single record per UITID.

Thank You,

Viswanath Sitaraman

What I have tried:

Tried to convert XML Path Code to HIVE