How can I get the final select statement of a CREATE VIEW DDL statement?

[rorynormaness]

Hello again!

Apologies if this is a basic question, but I am struggling to get the final select statement of a query.

Suppose I have a create-view DDL definition which runs as follows:

CREATE VIEW `my_project.my_dataset.my_object` AS
            WITH YT                           AS
            (
                            SELECT          yt_col1,
                                            [     1,                                             2] AS ytarr
                            FROM            `my_project.my_dataset.my_yt` y1
                            LEFT OUTER JOIN `my_project.my_dataset.my_yt2` y2
                            ON              y1.yt_col1 = y2.yt_col2
            )
            ,
            DV AS
            (
                            SELECT          dv_col1
                            FROM            `my_project.my_dataset.my_dv` d1
                            LEFT OUTER JOIN `my_project.my_dataset.my_dv2` d2
                            ON              d1.dv_col1 = d2.dv_col2
            )
            ,
            YTDV AS
            (
                            SELECT          *
                            FROM            YT y
                            LEFT OUTER JOIN DV d
                            ON              y.yt_col1 = d.dv_col1
            )
SELECT          YT.yt_col1,
                YTDV.dv_col1,
                gdn_col1
FROM            YT
LEFT OUTER JOIN DV
ON              YT.yt_col1 = DV.dv_col1
LEFT OUTER JOIN `my_project.my_dataset.my_gdn` GDN
ON              GDN.gdn_col1 = YT.yt_col1
LEFT OUTER JOIN YTDV
ON              YTDV.yt_col1 = GDN.gdn_col1
CROSS JOIN      UNNEST(YT.ytarr) Test_Date
UNION ALL
SELECT '1',
       '2',
       '2'
FROM   YT

I want to traverse the expression to get the final select of the expression, which (in my head, at least!) is this part:

SELECT          YT.yt_col1,
                YTDV.dv_col1,
                gdn_col1
FROM            YT
LEFT OUTER JOIN DV
ON              YT.yt_col1 = DV.dv_col1
LEFT OUTER JOIN `my_project.my_dataset.my_gdn` GDN
ON              GDN.gdn_col1 = YT.yt_col1
LEFT OUTER JOIN YTDV
ON              YTDV.yt_col1 = GDN.gdn_col1
CROSS JOIN      UNNEST(YT.ytarr) Test_Date
UNION ALL
SELECT '1',
       '2',
       '2'
FROM   YT

Please tell me if I need to think about this differently, and how I might achieve this with sqlglot?

Thanks so much!
Rory

[tobymao]

please read this https://github.com/tobymao/sqlglot/blob/main/posts/ast_primer.md

[rorynormaness]

Hey @tobymao - I've read it about 4 times already - please can you tell me which part I should read to achieve this, and I will continue to try and do it myself?

I understand I need to be using the root scope for this task, but the root scope has the other CTEs in it, and I don't know how to filter out all preceding scopes from the root scope.

[tobymao]

nodes of a tree section. print out the tree with repr() and then traverse it with args.

[rorynormaness]

Thanks - I'll give this a go!