SQL HIVE 转换

Question

我正在尝试将一段 SQL 代码转换为 HiveQL，但它没有按预期工作。

请在下面找到我尝试转换的 SQL 代码片段：

SQL 代码：

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;

转换后的 HIVE 查询：

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;

感谢与此相关的所有帮助。我认为为 XML 路径转换的代码在 HIVE 中不起作用，因为我在单独的行中获取多个 UITID（键）和信息，而不是每个 UITID 仅一条记录。

谢谢你，

维斯瓦纳特·西塔拉曼