Snowflake视图创建耗时超30分钟的原因及原理咨询

一、Snowflake普通视图的创建机制 #

Snowflake的普通视图属于逻辑视图，创建过程本质是元数据操作，而非数据计算：

• 仅存储视图对应的SQL查询定义，不预计算或存储任何实际数据
• 创建时核心操作：语法合法性校验、访问权限校验（确认用户能读取所有关联表/调用自定义函数）、查询计划的框架解析（仅生成逻辑计划，不会执行查询）
• 正常情况下耗时极短，仅需完成上述轻量级校验步骤

二、本次视图创建超时的可能原因 #

结合提供的SQL语句，超时大概率由以下因素导致：

• 复杂嵌套查询的解析开销：SQL包含多层嵌套CTE，优化器需要逐层展开、解析嵌套结构，生成逻辑计划的过程耗时远超简单查询
• 自定义函数的校验负载：使用了joel_dbt.array_distinct_objects和joel_dbt.array_cat_distinct两个自定义UDF，创建视图时需要校验函数的存在性、调用权限，若UDF内部依赖其他对象，还需递归校验依赖关系
• 半结构化数据操作的解析复杂度：大量使用lateral flatten（数组展开）、array_agg/array_unique_agg（数组聚合）、object_construct_keep_null（对象构造）等操作，优化器需要处理复杂的类型推断、函数兼容性校验，大幅增加解析时间
• 子查询的预评估：SQL中NOT IN子查询涉及从大表中筛选数据，虽然创建视图不执行查询，但优化器会预评估该子查询的潜在复杂度，若源表数据量极大或元数据复杂，会延长解析时间
• 大表元数据读取：关联的relation_1/relation_2/relation_3/relation_4若为超大表、分区/聚类规则复杂，Snowflake读取表元数据的过程会耗时增加

附：对应的SQL代码 #

with
    phone_without_outlier as (
        (
            with
                phone__outlier as (
                    (
                        with
                            outliers__view as (
                                select input::varchar as phone
                                from relation_1
                                where is_outlier = true and feed_id = 'hi'
                                union
                                select phone::varchar as phone
                                from relation_2
                                where allow = true and feed_id = 'hi'
                            )
                        select *
                        from outliers__view
                    )
                ),
                phone_outlier__flattened as (
                    select
                        id,
                        phone_normalized,
                        phone_toll_free_normalized,
                        companies.value::varchar as phone_or_other
                    from relation_3,
                        lateral flatten(input => phones) companies
                ),
                phone__without_outlier as (
                    select
                        id,
                        iff(
                            phone_or_other = phone_normalized, phone_or_other, null
                        ) as phone_normalized,
                        iff(
                            phone_or_other = phone_toll_free_normalized,
                            phone_or_other,
                            null
                        ) as phone_toll_free_normalized,
                        iff(
                            phone_or_other != phone_normalized
                            and phone_or_other != phone_toll_free_normalized,
                            phone_or_other,
                            null
                        ) as other_phone_normalized
                    from phone_outlier__flattened
                    where coalesce(trim(phone_or_other), '') != '' and phone_or_other not in (select distinct phone from phone__outlier)
                ),
                phone_without_outlier__grouped as (
                    select
                        id,
                        array_compact(array_agg(distinct phone_normalized))[0]::varchar
                        as phone_normalized,
                        array_compact(array_agg(distinct phone_toll_free_normalized))[
                            0
                        ]::varchar as phone_toll_free_normalized,
                        joel_dbt.array_distinct_objects(
                            array_compact(
                                array_unique_agg(
                                    iff(
                                        coalesce(trim(other_phone_normalized), '')
                                        != '',
                                        object_construct_keep_null(
                                            'type',
                                            null,
                                            'carrier',
                                            null,
                                            'status',
                                            null,
                                            'status_last_verification_date',
                                            null,
                                            'score',
                                            (
                                                iff(
                                                    other_phone_normalized is not null,
                                                    1,
                                                    0
                                                ) + iff(
                                                    other_phone_normalized like '%+%',
                                                    1,
                                                    0
                                                )
                                            )
                                            / 2,
                                            'priority',
                                            null,
                                            'phone',
                                            other_phone_normalized,
                                            'ddi',
                                            null,
                                            'dnc',
                                            false
                                        ),
                                        null
                                    )
                                )
                            ),
                            'phone',
                            true
                        ) as other_phone_normalized_list
                    from phone__without_outlier
                    group by id
                )
            select *
            from phone_without_outlier__grouped
        )
    )
select
    id,
    phone_normalized,
    phone_toll_free_normalized,
    other_phone_normalized_list,
    (
        iff(phone_normalized is not null, 1, 0)
        + iff(phone_normalized like '%+%', 1, 0)
    ) / 2 as company_phone_score,
    (
        iff(phone_toll_free_normalized is not null, 1, 0)
        + iff(phone_toll_free_normalized like '%+%', 1, 0)
    ) / 2 as company_phone_toll_free_score,
    joel_dbt.array_distinct_objects(
        joel_dbt.array_cat_distinct(
            array_cat(
                array_construct_compact(
                    iff(
                        coalesce(trim(phone_normalized), '')
                        != '',
                        object_construct_keep_null(
                            'type',
                            null,
                            'carrier',
                            null,
                            'status',
                            null,
                            'status_last_verification_date',
                            null,
                            'score',
                            company_phone_score,
                            'priority',
                            null,
                            'phone',
                            phone_normalized,
                            'ddi',
                            null,
                            'dnc',
                            false
                        ),
                        null
                    )
                ),
                array_construct_compact(
                    iff(
                        coalesce(
                            trim(phone_toll_free_normalized), ''
                        )
                        != '',
                        object_construct_keep_null(
                            'type',
                            null,
                            'carrier',
                            null,
                            'status',
                            null,
                            'status_last_verification_date',
                            null,
                            'score',
                            company_phone_toll_free_score,
                            'priority',
                            null,
                            'phone',
                            phone_toll_free_normalized,
                            'ddi',
                            null,
                            'dnc',
                            false
                        ),
                        null
                    )
                )
            ),
            other_phone_normalized_list
        ),
        'phone',
        true
    ) as phones
from relation_4
left join phone_without_outlier using (id)

内容的提问来源于stack exchange，提问作者joel matisa