need to know about the recursive view in snowflake

Question

I'm new to snowflake. And I need to know about the recursive view in snowflake. Can someone explain it with an example. And why recursive views are used. Non-Materialized view only

I need a really good example. Thank u in advance

CREATE OR REPLACE TABLE employees (title VARCHAR, employee_ID INTEGER, manager_ID INTEGER);

INSERT INTO employees (title, employee_ID, manager_ID) VALUES
    ('President', 1, NULL),  -- The President has no manager.
        ('Vice President Engineering', 10, 1),
            ('Programmer', 100, 10),
            ('QA Engineer', 101, 10),
        ('Vice President HR', 20, 1),
            ('Health Insurance Analyst', 200, 20);

CREATE or replace RECURSIVE VIEW employee_hierarchy_02 (title, employee_ID, manager_ID, "MGR_EMP_ID (SHOULD BE SAME)", "MGR TITLE") AS (
      -- Start at the top of the hierarchy ...
      SELECT title, employee_ID, manager_ID, NULL AS "MGR_EMP_ID (SHOULD BE SAME)", 'President' AS "MGR TITLE"
        FROM employees
        WHERE title = 'President'
      UNION all
      -- ... and work our way down one level at a time.
      SELECT employees.title, 
             employees.employee_ID, 
             employees.manager_ID, 
             employee_hierarchy_02.employee_id AS "MGR_EMP_ID (SHOULD BE SAME)", 
             employee_hierarchy_02.title AS "MGR TITLE"
        FROM employees INNER JOIN employee_hierarchy_02
        WHERE employee_hierarchy_02.employee_ID = employees.manager_ID
);

what the above really means

So is the question “explain this example” or “provide better than this”? What do you not understand? Have you read the recursive cte docs, they explain it really nicely — Simeon Pilgrim, Aug 02 '23 at 15:03
@SimeonPilgrim im not getting it thats y..can u expalin it.. why the recursive view is used in the above example — dona, Aug 02 '23 at 15:12
To generate a recursive relationship between rows. Aka relate managers to those managed. — Simeon Pilgrim, Aug 02 '23 at 15:18
The purpose is to build the DAG from the edges in your rows, so you can answer hierarchical questions. — Simeon Pilgrim, Aug 02 '23 at 15:37
ok thanks.. i need some example other than the above..@SimeonPilgrim — dona, Aug 02 '23 at 15:39
Lets turn this around the other way. What is the problem you are trying to solve by more examples. Because normally one has an actual problem, and the you use a tool like recursive view/cte to solve those. If you are just learning read the recursive cte examples, and play with them in a trail account and experience what it is doing. Why a person would use this tool is to solve hiearchal problems — Simeon Pilgrim, Aug 02 '23 at 15:44

score 0 · Accepted Answer · answered Aug 03 '23 at 04:53

so here is the above example but swapped around to a CTE form:

First we have a table of data, it's just 6 rows of data, but if you look at how it white space formatted you can see a hierarchical structure, just like might happen in a work place:

with recursive employees (title, employee_ID, manager_ID) as (
    select * from values
    ('President', 1, NULL),  -- The President has no manager.
        ('Vice President Engineering', 10, 1),
            ('Programmer', 100, 10),
            ('QA Engineer', 101, 10),
        ('Vice President HR', 20, 1),
            ('Health Insurance Analyst', 200, 20)
)

next we have the actual recursive part of the CTE, this is like a WHILE loop in other programming languages, I have added extra column to track the recursion, which starts at zero, so each to that goes up, the recursion is doing another loop.

, employee_hierarchy_02 (title, employee_ID, manager_ID, "MGR_EMP_ID (SHOULD BE SAME)", "MGR TITLE", recursive_level) AS (
    -- Start at the top of the hierarchy ...
    SELECT 
        title, 
        employee_ID, 
        manager_ID, 
        NULL AS "MGR_EMP_ID (SHOULD BE SAME)", 
        'President' AS "MGR TITLE",
        0 as recursive_level
    FROM employees
    WHERE title = 'President'
        
    UNION all
      
    -- ... and work our way down one level at a time.
    SELECT 
        e.title, 
        e.employee_ID, 
        e.manager_ID, 
        h.employee_id AS "MGR_EMP_ID (SHOULD BE SAME)", 
        h.title AS "MGR TITLE",
        h.recursive_level + 1 as recursive_level
    FROM employees as e
    INNER JOIN employee_hierarchy_02 as h
    WHERE h.employee_ID = e.manager_ID
)
select * from employee_hierarchy_02

this gives:

this shows the 0'th layer of the recursion (the first part of the UNION ALL block as the only row that title is President, which we can see in the data there is only one of.

Then the two people reporting to the president are pulled in in loop 1, then the 3 people reporting to those 2 people are pulled in loop 2. Loop 3 finds no people that report to those 3 people so the looping stops.

Now, if I understand your question is not the above "how does it work" but "why would you do this. Well you want want to know how to allocate the count of employees, or sum or salary for each manager and need to know where/how to allocate the values. or other stuff... really I have never used it, and when I think of it as a solution, I tend to avoid it if I can. But it is a tool, and it's there.

need to know about the recursive view in snowflake

1 Answers1