Cheat sheet: Converting Spark SQL to AWS Athena SQL |
submit Data Collection, Web Scraping and Data Pipeline Related Tutorials
| Published Jul 24 '24. Last edited Jun 24 '25 |
For Data Engineers trying to debug #spark data pipelines using #AWS #Athena which is based on open sourced Presto technology, the little check list below will very likely save 30 minutes to one hour of your effort.
For Data Engineers working with Spark data pipelines on AWS, it's a quite common task for convert Spark SQL to AWS Athena SQL because a common scenario is Spark SQL errors out while running Spark job on a cluster, at that point, next step developers take is to test run the Spark SQL statement in AWS Athena to debug it. AWS Athena is AWS wrapper of open-source Presto SQL execution engine. However, converting Spark SQL to Athena SQL is not straight-forward, as there are a number of syntaxes that are specific to AWS Athena SQL and Spark SQL respectively, a successful conversion will require developers to
Here is a list of these syntax differences (this list is work in progress, welcome to comment to add or contribute to it)
| Intent | AWS Athena (Presto) SQL | Databricks Spark SQL |
|---|---|---|
| string replacement | replace |
regexp_replace |
| string trimming spaces | LTRIM and RTRIM |
trim |
| get substring | substr |
substring |
| value of a certain date | date_parse('yyyy-mm-dd', '%Y-%m-%d') |
'yyyy-mm-dd' |
| value of today's date | current_date |
current_date() |
| map function | map_agg (need GROUP BY at end of SQL query, reference1 and reference2 on StackOverflow) |
map (no GROUP BY is needed at end of query, function reference on Databricks) |
escaping column name with . (dot) |
using double quotes, e.g. "col.name" |
using backtick symbol, e.g. `col.name` (reference on StackOverflow) |
use minus to intersect 2 collections |
does not support minus, but support EXCEPT with almost identical functionality |
supports both minus and except |
| convert date to string using a format | date_format (current_timestamp, '%Y_%m_%d') per reference on StackOverflow |
same as AWS Athena SQL |
| concatenate strings with separator | supports CONCAT_WS per AWS doc |
supports CONCAT_WS |
Terms of Use: You are in agreement with our Terms of Services and Privacy Policy. If you have any question or concern to any information published on SaveNowClub, please feel free to write to us at savenowclub@gmail.com