Amazon Redshift announces support for hierarchical data queries with Recursive CTE
Amazon Redshift, a fully-managed cloud data warehouse, now adds support for Recursive Common Table Expression (CTE). Starting today, you can use a Recursive CTE on your Redshift cluster to query hierarchical data such as organizational charts where employees reports to other employees (managers), or multi-level product orders where a product consists of many components, which in turn consist of other components.
Amazon Redshift, a fully-managed cloud data warehouse, now adds support for Recursive Common Table Expression (CTE). Starting today, you can use a Recursive CTE on your Redshift cluster to query hierarchical data such as organizational charts where employees reports to other employees (managers), or multi-level product orders where a product consists of many components, which in turn consist of other components.
A Recursive CTE is a common table expression that references itself. In order to use a Recursive CTE, you first define a SELECT subquery which returns a result set which is the initial set of the recursion (“Anchor Clause”), then another SELECT subquery that references the CTE name in its FROM clause (“Recursive Clause”). The Recursive Clause then contains a WHERE clause that terminates its own execution.
To get started and learn more about Recursive CTE, visit our documentation.
Recursive CTE support is now available in all commercial regions. Please refer to the AWS Region Table for Amazon Redshift availability.