Блог

У меня проблема в том, что в наших представлениях Snowflake DWH были построены на основе верхних представлений (и снова поверх представлений). Если я сейчас пытаюсь изменить базовую таблицу, из которой получено первое представление, я боюсь что-то сломать.

Вот пример:

У нас есть таблица с REGISTRATIONS для нескольких стран. На основе этой таблицы создается представление, в котором отображаются только данные из US ( REGISTRATIONS_US ). Это представление используется для создания представления, содержащего данные США, скажем, из 2021 ( REGISTRATIONS_US_2021 ). Это представление снова используется для построения представления, содержащего данные из США в 2021 году за определенный месяц, скажем, июль ( REGISTRATIONS_US_202106 ). Также может случиться, что представление используется для создания более одного представления (см., Например REGISTRATIONS_US_NYC , В таблице, представленной ниже).

Если бы я сейчас изменил таблицу регистраций, это повлияло бы на все связанные представления. Поэтому мне нужно выяснить, как связаны представления.

Единственная информация, которая у меня есть, доступна в INFORMATION_SCHEMA :

Я хотел бы получить следующее решение:

Я предполагаю, что это должно быть как-то возможно с использованием рекурсивного CTE. Но я просто не могу себе представить, как, поскольку большинство примеров рекурсивных CTE, которые можно найти в Google, уже показывают иерархию в таблице на основе идентификаторов (типичный пример менеджера / сотрудника).

Я надеюсь, что мой вопрос понятен, и кто-нибудь может помочь.

GET_OBJECT_REFERENCES может использоваться для отслеживания зависимостей между объектами:

Возвращает список объектов, на которые ссылается указанный объект. Ввод в настоящее время ограничен именем представления.

 select * 
from table(get_object_references(
             database_name=>'...', 
             schema_name=>'public', 
             object_name=>'REGISTRATIONS_US_NYC')
          );
 

Редактировать:

ОБЪЕКТ_ЗАВИСИМОСТИ:

Зависимость объекта означает, что для работы с объектом объект, с которым выполняется операция, должен ссылаться на метаданные для себя или ссылаться на метаданные по крайней мере для одного другого объекта.

Подготовка:

 CREATE OR REPLACE TABLE REGISTRATIONS(
  market STRING,
  year INT,
  month_id INT,
  city STRING
);

CREATE VIEW REGISTRATIONS_US AS
SELECT * 
FROM REGISTRATIONS
WHERE market = 'US';

CREATE VIEW REGISTRATIONS_US_2021 AS
SELECT * FROM REGISTRATIONS_US WHERE year = 2021;

CREATE VIEW REGISTRATIONS_US_202106 AS
SELECT * FROM REGISTRATIONS_US_2021 WHERE month_id = '202106';

CREATE VIEW REGISTRATIONS_US_NYC AS
SELECT * FROM REGISTRATIONS_US WHERE city = 'NYC';
 

Запрос (источник: запрос представления OBJECT_DEPENDENCIES):

 with recursive referenced_cte
(object_name_path, referenced_object_name, referenced_object_domain, referencing_object_domain, referencing_object_name, referenced_object_id, referencing_object_id)
    as
      (
        select referenced_object_name || '-->' || referencing_object_name as object_name_path,
               referenced_object_name, referenced_object_domain, referencing_object_domain, referencing_object_name, referenced_object_id, referencing_object_id
          from snowflake.account_usage.object_dependencies referencing
          where true
            and referenced_object_name = 'REGISTRATIONS' and referenced_object_domain='TABLE'

        union all

        select object_name_path || '-->' || referencing.referencing_object_name,
              referencing.referenced_object_name, referencing.referenced_object_domain, referencing.referencing_object_domain, referencing.referencing_object_name,
              referencing.referenced_object_id, referencing.referencing_object_id
          from snowflake.account_usage.object_dependencies referencing join referenced_cte
            on referencing.referenced_object_id = referenced_cte.referencing_object_id
            and referencing.referenced_object_domain = referenced_cte.referencing_object_domain
      )

  select object_name_path, referenced_object_name, referenced_object_domain, referencing_object_name, referencing_object_domain
    from referenced_cte;
 

Вывод: