0

Suppose you are given a .sql file containing 10,000 lines of SQL commands and you were told to convert them to PySpark.

What software would you use?

...the task I want to accomplish:

  • Somehow automate the creation of PySpark from a .sql file.

...your requirements for that task

  • I get that there would most likely be an intermediary step between .sql --> pyspark, so the PySpark conversion is not a priority, the priority is having the .sql file in some sort of 'universal' model.

...what you already know about software available for this purpose

Any help on this would be great!

1

So, assuming such a software doesn't exist (which I am not sure of, spark users may come from SQL and have had such requirements before). What I think would need to be done if one were to write one:

  1. parse the SQL. In a way that gives you a sensible model you can work with. Here is a question where exactly this is asked. https://stackoverflow.com/questions/1394998/parsing-sql-with-python

  2. use the AST to generate corresponding spark commands. I think it would be enough to just output text, but bonus points if you can find a library that lets you construct a spark ast and lets you generate python code from that.

I understand that 2) is not very detailed, but as you correctly mentioned in your answer, getting the SQL in a usable model format is the first and maybe even biggest issue.

4
  • thanks for your response, it is nice to see we are saying similar things. I have found this: github.com/pyparsing/pyparsing/blob/master/examples/… from your post. What are your thoughts?
    – Alex
    Nov 6 '20 at 13:03
  • @Alex looks good, it seems to have structure, which is what seems to be completely lacking for Python-sqlparse. You'll have to try and see if it' already enough for your use case or whether you need to add to / change the grammar. Obviously its currently only a small subset of SQL. From what the answers were, I would have proposed either pyparsing (based on that SQL example), or github.com/TwoLaid/python-sqlparser - definitely try that pyparsing example though.
    – kutschkem
    Nov 6 '20 at 13:10
  • Okay this makes sense. I'm unsure on the AST -> Spark conversion though as I feel like that might be a tricky job.
    – Alex
    Nov 6 '20 at 13:16
  • @Alex It sure is, make sure you first have a good idea how a given SQL statement has to look in spark
    – kutschkem
    Nov 6 '20 at 14:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.