Snappydata SQL WITH语句

Question

我正在使用Snappydata运行一些查询，并使用sql with语句：

WITH x AS (
  SELECT DISTINCT col_a, col_b
  FROM table_a
)
INSERT INTO table_b
SELECT x.col_a, x.col_b
FROM x
  JOIN table_c c ON x.col_a = c.col_a and x.col_b = c.col_b

这段sql代码在本地模式下运行良好，但是当我将编译后的jar文件提交到Snappydata集群时，它抛出一个错误，指出表"APP.X“不存在。

org.apache.spark.sql.TableNotFoundException: Table 'APP.X' not found;

知道为什么会这样吗？

Answer 1

这是由于近似查询处理(AQP)规则解析试图过早解析名称造成的已知限制。我们在运行TPC-DS查询时看到了这一点。它被修复在最新的AQP主机上，修复将在1.0中可用。目前有两种选择：

a)改成正规子查询变量：

INSERT INTO table_b
SELECT x.col_a, x.col_b
FROM (SELECT DISTINCT col_a, col_b FROM table_a) x
  JOIN table_c c 
  ON x.col_a = c.col_a 
  AND x.col_b = c.col_b

b)或者如果不使用AQP查询，则可以从产品jars目录中移出snappydata-aqp jar

Answer 2

尝试从x添加

WITH x AS (
  SELECT DISTINCT col_a, col_b
    FROM table_a
)

INSERT 
  INTO table_b
SELECT x.col_a, 
       x.col_b
  FROM x
  JOIN table_c c 
    ON x.col_a = c.col_a 
   AND x.col_b = c.col_b