CODEWITHSUNDEEP
mysqlapache-spark-sqlwso2-das
Charini Nanayakkara
Charini Nanayakkara

Reputation: 347

WSO2 DAS: SPARK SQL query with UNION producing errors

The following query was attempted to be executed when performing batch analytics with WSO2 DAS using Spark SQL. Tables 'First', 'Middle' and 'Third' are required to be combined and written to table 'All_three'.

INSERT OVERWRITE TABLE All_three 
SELECT SYMBOL, VOLUME FROM First 
UNION 
SELECT SYMBOL, VOLUME FROM Middle 
UNION 
SELECT SYMBOL, VOLUME FROM Third;

Following error is displayed on WSO2 DAS when this query is executed:

ERROR: [1.79] failure: ``limit'' expected but `union' found INSERT OVERWRITE TABLE X1234_All_three SELECT SYMBOL, VOLUME FROM X1234_First UNION SELECT SYMBOL, VOLUME FROM X1234_Middle UNION SELECT SYMBOL, VOLUME FROM X1234_Third ^

Using LIMIT with UNION is not a necessity to the best of my knowledge. Enclosing the SELECT queries in parentheses too was attempted which didn't work. What am I doing wrong here? Thank you in advance!

Upvotes: 1

Views: 611

Answers (2)

Isuru Wijesinghe
Isuru Wijesinghe

Reputation: 86

There exists a problem with the query you mentioned here. Please change the query as below.

INSERT OVERWRITE TABLE All_three select * from ( SELECT SYMBOL, VOLUME FROM First UNION SELECT SYMBOL, VOLUME FROM Middle UNION SELECT SYMBOL, VOLUME FROM Third ) temp;

Actually what we do here is, wrapping the union result into temporary data element called temp and select everything from there. Spark-SQL parser only takes single select element in the insert queries, and at the end of a select query it expects a limit (if available). therefore, you need to wrap the subsequent select statements into one select element. Hope this resolves your issue.

Upvotes: 0

Midhun Siva
Midhun Siva

Reputation: 35

I had the same issue.

Please make sure you had an whitespace in end of each row.

The reason is it is not considering \n as a whitespace like SSMS or other query editors.

So it reads your query without whitespace. Hence, it read like SELECT * FROM FirstUNION not like SELECT * FROM First UNION

My issue was resolved and I hope this helps for you too.

Upvotes: 1

Related Questions