Без долгих вступлений, сразу к делу.

Знаете ли вы, что в Snowflake можно создавать объекты с пустыми именами? Например:

CREATE DATABASE "";
CREATE SCHEMA ""."";
CREATE TABLE ""."".""("" NUMBER);

Это работает на момент публикации и потенциально создаёт массу проблем для внешних систем, которые не ожидают такого поворота. Также это обычно приводит в восторг админов DWH.

Более интересные и практичные советы под катом.

Бесплатный automatic clustering

Многие знают о возможности указать ключи для автоматической кластеризации данных в таблице:

CREATE TABLE <name> ... CLUSTER BY ( <expr1> [ , <expr2> ... ] );

Это позволяет Snowflake более эффективно хранить данные в микро-партициях и заметно ускорить чтение, если в запросе присутствует соответствующий фильтр.

Но немногие знают о том, что почти аналогичного результата можно добиться "бесплатно", если загружать и удалять данные в таблице исключительно небольшими блоками, которые организованы в строгом соответствии с желаемым ключом. 

Например, вместо загрузки всей таблицы целиком в одной большой транзакции:

COPY INTO my_events FROM 's3://my_events/*/*.csv'

Лучше разделить входящие данные на партиции и выполнить несколько маленьких транзакций, по одной на каждую дату:

COPY INTO my_events FROM 's3://my_events/2022-01-01/*.csv'
COPY INTO my_events FROM 's3://my_events/2022-01-02/*.csv'
COPY INTO my_events FROM 's3://my_events/2022-01-03/*.csv'

В этом случае вы получите таблицу, которая кластеризована естественным образом, не потратив ни копейки на повторную перезапись микро-партиций. В зависимости от объема данных на аккаунте, эта простая техника поможет сэкономить сотни, тысячи, десятки тысяч долларов.

Проверить результат можно при помощи функции SYSTEM$CLUSTERING_INFORMATION, а также в профиле выполнения запроса через сравнение "Partitions scanned" и "Partitions total". Чем меньше партиций читает запрос, тем лучше.

Invalid views

В некоторых случаях объекты VIEW могут сломаться и перестать работать, даже если их SQL TEXT абсолютно корректен. К сожалению, при изменениях в объектах TABLE Snowflake не обновляет VIEW, которые от них зависят.

На практике это приводит к тому, что пользователи получают ошибки, которые можно исправить только ручным пересозданием VIEW. Например:

CREATE TABLE my_table(id NUMBER);
CREATE OR REPLACE VIEW my_view AS SELECT * FROM my_table;

SELECT * FROM my_view;
-- it works

Пока всё замечательно. Теперь добавим ещё одну колонку в таблицу и сломаем VIEW:

ALTER TABLE my_table ADD name VARCHAR;

SELECT * FROM my_view;
-- it fails: view declared 1 column(s), but view query produces 2 column(s)

Пересоздание VIEW исправляет ситуацию:

CREATE OR REPLACE VIEW my_view AS SELECT * FROM my_table;

SELECT * FROM my_view;
-- it works again

Закономерно встает вопрос, как же находить такие VIEW и автоматизировать их пересоздание с минимальными затратами?

Методом проб и ошибок, удалось найти способ, который не требует использования INFORMATION_SCHEMA, активного WAREHOUSE и хитрых процедур. Этот способ заключается в том, чтобы проверять все VIEW через команду .describe() в Snowflake Python Connector.

Она позволяет спланировать запрос, но не выполнять его. Если объект VIEW сломан, то команда вернёт исключение, на которое можно отреагировать пересозданием VIEW. Все эти операции "бесплатны".

Иерархия ролей

Документация Snowflake упоминает о пользе создания грамотной иерархии ролей для управления доступами, но она не приводит конкретных примеров. Из-за этого многие начинающие администраторы не уделяют этому должного внимания на старте, совершают много ошибок и быстро запутываются по мере роста сложности.

Далее я кратко опишу конкретный пример трехступенчатой иерархии ролей, которая показала отличные результаты на практике.

  • Tier 1: роли, которые дают привилегии на конкретные объекты через GRANTS и FUTURE GRANTS.

    Например: "роль позволяет читать все таблицы в схеме XXX", "роль позволяет использовать warehouse YYY", "роль даёт доступ на запись в таблицы ZZ1 и ZZ2".

  • Tier 2: роли, которые объединяют одну или несколько T1 ролей в бизнес-функцию.

    Например: "роль финансовый аналитик", "роль разработчик BI", "роль внешний аудитор".

  • Tier 3: роли, которые объединяют одну или несколько T2 ролей и назначают их конкретному пользователю.

    Например: "Алиса - бизнес аналитик", "Боб - внешний аудитор", "Виктор - бизнес аналитик в проекте ААА, но также администратор в проекте BBB".

Создание большинства типов ролей в этой системе удобно автоматизировать.

Например, можно создавать отдельные T1 роли для каждой схемы с FUTURE GRANTS на:

  • владение всеми объектами в схеме (OWNERSHIP);

  • чтение всех объектов в схеме (READ);

  • запись во все объекты в схеме (WRITE);

Дополнительно можно создавать T1 роли для каждого WAREHOUSE с правами на USAGE и OPERATE.

Наконец, T3 роли могут создаваться и назначаться для каждого пользователя автоматически. Вручную остаётся настроить только T2 бизнес роли и назначить их конкретным пользователям.

Временные затраты на управление такой системой приближаются к нулю. Также ей очень рады аудиторы и безопасники, которым не нужно распутывать сложный клубок из костылей и подпорок.

Управление пакетами для Java & Python UDF

В настоящий момент Snowflake активно развивает концепцию UDF функций, которые позволяют выполнять почти произвольный код, написанный на Java или Python, прямо внутри WAREHOUSE, не вынимая данные наружу.

Потенциально это крайне мощный инструмент, но его использование поднимает ряд практических вопросов, один из которых - как управлять пакетами (.JAR, .WHL)?

Одно из простых и удачных решений - относиться к пакетам как к еще одному типу объектов внутри Snowflake, которые зависят от STAGE и обновляются вместе с FUNCTION в рамках одного и того же CI/CD процесса.

Например, если вы храните описания объектов в Git, то последовательность их применения будет следующая:

  1. Создать DATABASE.

  2. Создать SCHEMA.

  3. Создать STAGE (internal) для пакетов.

  4. Загрузить .JAR / .WHL файлы в STAGE.

  5. Создать FUNCTION, который зависит от загруженных файлов.

Другими словами, управление пакетами должно происходить не "до", не "после", не "вручную сбоку", а строго между созданием STAGE и созданием FUNCTION. В этом случае все будет работать без ошибок.

У этого процесса есть одна техническая особенность - как понять, что уже существующие файлы в STAGE нуждаются в обновлении? У Snowflake есть стандартная команда LIST, которая позволяет получить список файлов в STAGE, а также их MD5 суммы.

Но проблема в том, что эти MD5 суммы считаются не от оригинального файла, а от зашифрованного файла, что не подходит для сравнения. Чтобы сохранить оригинальный MD5, можно дополнительно загружать в STAGE пустые файлы, которые содержат хеш в своём имени.

Или, если пакетов пока немного, то их можно целиком перезаписывать при каждом вызове CI/CD.

SnowDDL: open source инструмент для управлений схемой объектов

Когда я только начинал работать со Snowflake, то был удивлен отсутствием полноценных декларативных инструментов для работы со схемой объектов. У самых популярных вариантов (schemachange и Terraform) есть существенные пробелы, и даже сам вендор рекомендует использовать их совместно, что довольно неудобно.

Чтобы немного исправить эту ситуацию, мной был создан и выложен в открытый доступ новый нативный инструмент - SnowDDL (docs). Его основные особенности:

  1. Отсутствие "состояния".

  2. Возможность откатывать изменения.

  3. Поддержка ALTER COLUMN в тех случаях, когда это возможно.

  4. Встроенная иерархия ролей, которая описана выше в этой статье.

  5. Невалидные VIEW обновляются автоматически.

  6. Упрощение Code Review за счет разделения операций на условно "опасные" и условно "безопасные".

  7. Возможность создания несколько изолированных "окружений" для каждого разработчика в рамках одного Snowflake аккаунта.

  8. Простое и явное управление зависимостями между объектами.

  9. Управление пакетами для UDF функций, которое описано выше в этой статье.

Чтобы не делать эту статью слишком длинной, пока на этом остановлюсь.

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

Enjoy!

Комментарии (8)


  1. erley
    01.05.2022 22:02

    Спасибо, очень полезные заметки!


  1. hmpd
    02.05.2022 13:27
    +1

    Спасибо! Насчет пересоздания view: в мире SQL Server считается плохой практикой писать SELECT * во вьюшках, как раз из-за необходимости их пересоздавать. Может быть, и в Snowflake надо указывать конкретные колонки в SELECT вместо *?


    1. wildraid Автор
      02.05.2022 13:56
      +1

      SELECT * - это скорее самый доступный пример того, что может сломать VIEW. Действительно, лучше избегать этой конструкции, независимо от СУБД.

      На практике чаще видим ситуации, когда есть цепочка из нескольких VIEW, и ломается что-то посередине. Например, переименовали таблицу / колонку / функцию, поменяли тип данных.


      1. hmpd
        03.05.2022 10:19

        Да, точно, сломать вьюшки можно по-разному. У RedGate в SQL Prompt есть Find Invalid Objects - так можно найти сломанные view, sp, функции и т. д.

        А в Snowflake можно посмотреть цепочку зависимостей?

        Давно работаете со Snowflake? Пишите ещё.


        1. wildraid Автор
          03.05.2022 13:34
          +1

          Любопытно, но поле "invalid" можно увидеть у команды SHOW MATERIALIZED VIEWS, но не у обычной SHOW VIEWS. Судя по всему, в текущей модели Snowflake сам точно не знает, какие стандартные VIEW стали невалидны до первого запроса к ним. Надеюсь, со временем это исправят.

          Цепочку зависимостей посмотреть можно, но дьявол в деталях:

          1. Табличная функция GET_OBJECT_REFERENCES(), показывает не всё, требует активный WAREHOUSE и тратит деньги.

          2. View OBJECT_DEPENDENCIES, обновляется с задержкой до 3ч (!), тратит деньги.

          После перебора всех вариантов получается, что проверка всех VIEW через .describe() - самое простое и дешёвое решение на текущий момент.

          Со Snowflake работаю недавно, но очень много опыта с другими DWH на больших объёмах. Уже знаю, на что смотреть, и почему не стоит слепо доверять документации и советам вендора. :)


  1. Ranlod
    04.05.2022 10:27

    А как же допустимость дубликатов ID при объявлении PRIMARY KEY? Когда я только начал работать со снежинкой, для меня это было неприятным открытием.

    https://docs.snowflake.com/en/sql-reference/constraints-overview.html

    Snowflake supports defining and maintaining constraints, but does not enforce them, except for NOT NULL constraints, which are always enforced.


    1. wildraid Автор
      04.05.2022 11:32

      Это достаточно частая история для OLAP DWH. Из-за того, как они устроены внутри, проверка PRIMARY KEY обычно потребует обход всех блоков или микро-партиций. Скорость импорта упадет в несколько раз. Есть разные хитрые решения, вроде ReplacingMergeTree в ClickHouse, но это скорее исключение.

      А главное, предположим, что нашли дубль при вставке новых данных. Какие наши действия? Упасть с ошибкой и лежать? Удалить старый ряд в таблице? Почистить входящие данные?

      Всё это можно сделать через MERGE со вложенным COPY INTO, и тонко настроить его под конкретный use case. Отсутствие constraints enforcement - это немного неудобно после работы с OLTP DWH, но не критично.


      1. Ranlod
        04.05.2022 12:13

        Ну как по мне, очевидно, что при указании PRIMARY KEY ты хочешь видеть уникальные ключи и если там есть дубли то ты ожидаешь увидеть ошибку как в классических БД.