impala recursive

Impala supports recursive queries using Common Table Expressions (CTEs) and the WITH RECURSIVE syntax.

To use recursive queries in Impala, you need to define a CTE that specifies the initial set of rows and the recursive logic to be applied to those rows. The WITH RECURSIVE syntax allows you to specify the recursive logic as part of the CTE definition.

Here's an example of a recursive query in Impala:

WITH RECURSIVE cte AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n+1 FROM cte WHERE n<10
)
SELECT * FROM cte;

This query defines a CTE called `cte` with an initial row containing the value 1. The recursive logic is defined in the second SELECT statement, which adds 1 to the `n` column for each row where `n` is less than 10. The `UNION ALL` operator combines the results of the initial query and the recursive query.

The final SELECT statement simply selects all rows from the `cte` CTE.

Note that in order to use recursive queries in Impala, you must enable the `recursion` query option by setting it to `true`. This can be done at the session level or the query level:

SET recursion=true;

or

SELECT /*+ recursion */ * FROM my_table;