I’m trying to create a query in Athena that solves this problem:
I have records that look like this
{'id': 'a', 'children': ['b','c']}
which create a hierarchical structure, like a tree but with indeterminate children.
I have more than one root, that is, more than one element that is not children of anyone.
I want to get the complete structure for one of them. How can I do that using a SQL query? I’ve seen that recursive queries are not allowed in Athena.
Advertisement
Answer
What you want to achieve is called “recursive queries” or “recursive CTEs” (common table expressions). Presto 340 adds experimental support for them, but Athena is based on Presto 0.172 and does not have the feature. Unfortunately, there is no general replacement for the feature.
Without support for the feature in the query engine, you need to pull the parent/child relationships and calculate the result within your app.