How can I generate Spark SQL from Oracle PL/SQL using ANTLR 4

[George Boukis]

Given a Parse Tree/AST which was generated based on input from PL/SQL grammar, what is the best approach to automatically convert PL/SQL to Spark SQL source code?

I have generated the lexer and parser based on the Oracle PL/SQL grammar from link below. I am able to walk the tree using a listener.

https://github.com/antlr/grammars-v4

Here are few PL/SQL  examples which need to be converted to Spark SQL for phase 1 , potentially data frames ( phase 2).

Using AND condition

SELECT *
FROM suppliers
WHERE state = 'California'
AND supplier_id <= 750;

Joining Tables

SELECT suppliers.suppler_name, orders.order_id
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id
WHERE suppliers.state = 'California';

Group By Clause

SELECT expression1, expression2, ... expression_n, 
       aggregate_function (aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;

Regards,

George 

[Norman Dunbar]

Hi George,

are you aware that none of the examples you gave are actually Oracle's PL/SQL? They are valid examples of normal SQL though.

In PL/SQL you have the following constructs:

SELECT *
INTO ...

FROM suppliers
WHERE state = 'California'
AND supplier_id <= 750;

The INTO list is either a list of PL/SQL variables or a record/object type previously declared in the PL/SQL code. Each of the examples given require an INTO clause to be valid PL/SQL code.

I've not looked at the PL/SQL grammar (yet) but if it accepts your examples, then I'm afraid that the grammar is incorrect.

Sorry that I can't help with Spark SQL, I'm afraid that I have never heard of it until now. PL/SQL, I use every day at work.


Cheers,
Norm.

--
Sent from my Android device with K-9 Mail. Please excuse my brevity.

[Oscar Fernández Sierra]

Perpaps this can be a useful tool....

https://www.jooq.org/translate/

Translate SQL From One Dialect to Another — While SQL has been standardized numerous times, its implementation does tend to vary, so this tool provides an interesting way to see the differences on your own queries.