Snappydata SQL WITH statement

Question

I am using Snappydata to run some queries, and use the sql with statement:

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

This sql code runs fine when running in local mode, but when I submit the compiled jar file to Snappydata cluster, it throws an error saying the table "APP.X" does not exist,

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

any idea why this happens?

score 2 · Answer 1 · answered Sep 12 '17 at 07:33

This is a known limitation due to Approximate Query Processing (AQP) rule resolution that tries to resolve the name prematurely. We saw this when running TPC-DS queries. It is fixed on latest AQP master and fix will be available in 1.0. For now there are two options:

a) Change to normal sub-query variant:

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) Or if not using AQP queries, then you can move out snappydata-aqp jar from product jars directory

Thanks very much for the clarification, I guess for now I'll just change the SQL statement. — user3230153, Sep 14 '17 at 22:34

score 0 · Answer 2 · answered Sep 11 '17 at 22:30

0

try adding FROM 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

answered Sep 11 '17 at 22:30

Ferdinand Gaspar

2,043
1
8
17

Thanks for the response, I forgot to add this "FROM x" when typing the code for this question, now corrected. – user3230153 Sep 11 '17 at 22:33
thanks for the pointer, unfortunately this does no apply to my case, as the with statement does not require creating the table before hand, I am actually suspecting the "WITH STATEMENT" is not properly supported in Snappydata – user3230153 Sep 11 '17 at 22:49

Snappydata SQL WITH statement

2 Answers2