Azure数据库SparkSQL支持递归查询吗？

Question

我正在将数据从Server移动到Azure数据湖gen2，并使用递归查询转换SQL查询。

下面是一个使用CTE (公共表表达式)递归的SQL查询示例

 WITH RECURSIVE AS BOM
          (SELECT p.MItemId AS RootPartNumber,
                  p.MItemId AS PartNumber,
                  NULL AS ParentPartNumber,
                  0    AS BomLevel,
                  1.0  AS Quantity
           FROM   PartItem p

           UNION ALL
           SELECT BOM.RootPartNumber,
                 CAST(BSM.ChildItem AS string) AS PartNumber,
                 CAST(DB.PartNumber AS string) AS ParentPartNumber,
                 BOM.BomLevel + 1  as BomLevel,
                 BSM.Quantity AS Quantity 
           FROM  PartItemBomList BSM
           INNER JOIN BOM  ON BOM.PartNumber = BSM.ParentItem
           INNER JOIN PartItem p           ON p.MItemId = BSM.ChildItem
           WHERE BSM.IsDeleted = 0 
  )
  SELECT * FROM BOM

我试图更改将递归嵌入到FROM子句中的查询，如下所示，但没有成功。

 SELECT * FROM 
          (SELECT p.MItemId AS RootPartNumber,
                  p.MItemId AS PartNumber,
                  NULL AS ParentPartNumber,
                  0    AS BomLevel,
                  1.0  AS Quantity
           FROM   PartItem p
           WHERE p.PartType =    'Cloud-OrderableAssembly' 
           UNION ALL
           SELECT BOM.RootPartNumber,
                 CAST(BSM.ChildItem AS string) AS PartNumber,
                 CAST(DB.PartNumber AS string) AS ParentPartNumber,
                 BOM.BomLevel + 1  as BomLevel,
                 BSM.Quantity AS Quantity 
           FROM  PartItemBomList BSM
           INNER JOIN BOM  ON BOM.PartNumber = BSM.ParentItem
           INNER JOIN PartItem p           ON p.MItemId = BSM.ChildItem
           WHERE BSM.IsDeleted = 0 
  ) as BOM

这里是我从Azure Databricks会话中得到的错误。

SQL语句中的错误: AnalysisException: Table或view未找到: BOM；第16行pos 22

Answer 1

问题就在这里

INNER JOIN BOM  ON BOM.PartNumber = BSM.ParentItem

这是内部查询，据我所知，BOM是在外部定义的，因此这部分查询运行BOM不存在。

如果我是您，我可以通过直接在SQL上运行来修复下面的查询。下面引用BOM的方式是不正确的

SELECT p.MItemId AS RootPartNumber,
                  p.MItemId AS PartNumber,
                  NULL AS ParentPartNumber,
                  0    AS BomLevel,
                  1.0  AS Quantity
           FROM   PartItem p
           WHERE p.PartType =    'Cloud-OrderableAssembly' 
           UNION ALL
           SELECT BOM.RootPartNumber,
                 CAST(BSM.ChildItem AS string) AS PartNumber,
                 CAST(DB.PartNumber AS string) AS ParentPartNumber,
                 BOM.BomLevel + 1  as BomLevel,
                 BSM.Quantity AS Quantity 
           FROM  PartItemBomList BSM
           INNER JOIN BOM  ON BOM.PartNumber = BSM.ParentItem
           INNER JOIN PartItem p           ON p.MItemId = BSM.ChildItem
           WHERE BSM.IsDeleted = 0