jason-jz-zhu/databathing

More SQL Parsing!

Parse SQL into JSON so we can translate it for other datastores!

See changes

Problem Statement

After converting from sql to spark, data engineers need to write the spark code for ETL pipeline instead of using YAML(SQL) which can improve the performance of ETL job, but it still makes the ETL development longer than before.

Then we have one question: can we have a solution which can have both good calculation performance (Spark) and quick to develop (YAML - SQL)?

YES, we have !!!

Objectives

We plan to combine the benefits from Spark and YAML (SQL) to create the platform or library to develop the ETL pipeline.

Project Status

May 2022 - There are over 900 tests. This parser is good enough for basic usage, including:

• SELECT feature
• FROM feature
• INNER JOIN and LEFT JOIN feature
• ON feature
• WHERE feature
• GROUP BY feature
• HAVING feature
• ORDER BY feature
• AGG feature
• WINDOWS FUNCTION feature (SUM, AVG, MAX, MIN, MEAN, COUNT)
• ALIAS NAME feature
• WITH STATEMENT feature

Install

pip install databathing

Generating Spark Code

You may also generate PySpark Code from the a given SQL Query. This is done by the Pipeline, which is in Version 1 state (May2022).

>>> from databathing import Pipeline
>>> pipeline = Pipeline("SELECT * FROM Test WHERE info = 1")
'final_df = Test\\\n.filter("info = 1")\\\n.selectExpr("a","b","c")\n\n'

Contributing

In the event that the databathing is not working for you, you can help make this better but simply pasting your sql (or JSON) into a new issue. Extra points if you describe the problem. Even more points if you submit a PR with a test. If you also submit a fix, then you also have my gratitude.

Please follow this blog to update verion - https://circleci.com/blog/publishing-a-python-package/

Run Tests

See the tests directory for instructions running tests, or writing new ones.

Version Changes

Version 1

May 2022

Features and Functionalities - PySpark Version

• SELECT feature
• FROM feature
• INNER JOIN and LEFT JOIN feature
• ON feature
• WHERE feature
• GROUP BY feature
• HAVING feature
• ORDER BY feature
• AGG feature
• WINDOWS FUNCTION feature (SUM, AVG, MAX, MIN, MEAN, COUNT)
• ALIAS NAME feature
• WITH STATEMENT feature

databathing's Issues

Join on multiple conditions fails

Hi!

I recently discovered your project and it looks awesome!

I see that Joins are parsed only when there is one ON condition. For example, the below query fails:

sql = """
SELECT 
    t1.id as id, 
    t1.val as t1_val,
    t2.val as t1_val
FROM Test t1
LEFT JOIN Test t2
ON t1.id = t2.id AND t1.id2 = t2.id2
"""

Pipeline(sql).parse()

with the below error:

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
Cell In[54], line 26
      1 sql = """
      2 SELECT DISTINCT
      3   farr.measurementperiodid,
   (...)
     13   farr.measurementperiodid = ? AND farr.sourcesystemid IN (122, 162)
     14 """
     16 sql = """
     17 SELECT 
     18     t1.id as id, 
   (...)
     23 ON t1.id = t2.id AND t1.id2 = t2.id2
     24 """
---> 26 transiled_pyspark = databathing.Pipeline(sql.replace("?", "1")).parse()
     27 print(transiled_pyspark)

File [c:\Users\DP266XJ\repos\sql2pyspark\.venv\lib\site-packages\databathing\pipeline.py:47](file:///C:/Users/DP266XJ/repos/sql2pyspark/.venv/lib/site-packages/databathing/pipeline.py:47), in Pipeline.parse(self)
     45     self.gen_with_pipeline(self.parsed_json_whole_query)
     46     final_ans += self.with_ans
---> 47 self.gen_last_pipeline(self.parsed_json_whole_query)
     48 final_ans += self.last_ans
     49 return final_ans
...
     40         from_stmt['inner join']['value']+".alias(\""+from_stmt['inner join']['name']+"\")", 
     41         "col(\""+str(from_stmt['on']['eq'][0])+"\")" + "==" + "col(\""+str(from_stmt['on']['eq'][1])+"\")" , 
     42         'inner')

KeyError: 'eq'

Is there a workaround for this?

Thank you!