SQL-скрипты: живите в Snowflake

Snowflake Scripting — это расширение SQL, упрощающее миграцию и разработку хранимых процедур. Здесь вы найдете мои заметки и базовые скрипты, которые помогут вам начать работу.

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

Почему вы хотите писать SQL-скрипты с помощью Snowflake Scripting

Упрощение хранимых процедур

Вы можете упростить существующие сценарии JS с помощью сценариев SQL. Например, проверьте разницу в том, как бы я ответил на этот вопрос о переполнении стека с помощью сценария SQL:

Миграции

Ознакомьтесь с постом Welcome Snowflake Scripting от Маурисио Рохаса на Mobilize. Он углубляется в объявления переменных, передачу переменных, чтение в переменные и условную логику. И они с нетерпением ждут возможности внедрить эти улучшения в свой продукт миграции SnowConvert.

На самом деле Mobilize.Net SnowConvert обновляется, чтобы вы могли начать модернизацию вашего Oracle, Teradata и SQL Server до сценариев Snowflake Scripting. Поэтому мы надеемся, что вам это понравится так же, как и мне.

Базовые скрипты

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

Запустить цикл 200 раз

execute immediate $$
declare
    x int default 0;
begin
    loop
        x := x + 1;
        if (x >= 200) then break;
        end if;
    end loop;
    return x;
end;
$$;

• Мне нравится возможность либо создать процедуру для последующего вызова, либо просто иметь блок execute immediate для интерактивной отладки.
• Выполняется примерно за 200 мс (‹1 мс на итерацию в масштабе).

Выполнение динамических SQL-запросов в цикле

execute immediate $$
declare
    x int default 0;
    res resultset;
begin
    loop
        res := (execute immediate 'select 1 +' || x);
        let cur cursor for res;
        open cur;
        fetch cur into x;
        if (x >= 20) then break;
        end if;
    end loop;
    return x;
end;
$$;

• Snowflake Scripting поддерживает cursor, который используется здесь для получения результатов запроса.
• В скрипте можно execute immediate создавать любые запросы вручную.
• resultset содержит результаты запроса (которые процедура также может вернуть с конвертом table()).
• Выполняется примерно за 1,1 с, что означает, что каждый запрос выполняется за ‹55 мс.

Запустите много вставок

execute immediate $$
declare
    x int default 0;
begin
    create or replace table xxx(i int);
    loop
        x := x + 1;
        execute immediate 'insert into xxx(i) values (' || x || ')';
        if (x >= 20) then break;
        end if;
    end loop;
    return x;
end;
$$;

• Вставка множества отдельных строк не является рекомендуемым шаблоном, и вы можете использовать этот скрипт для оценки того, сколько времени требуется для выполнения 20 последовательных вставок.
• Выполняется примерно за 9,4 с, что показывает, что каждая вставка занимает около 0,5 с, поскольку она сохраняется.

Работа с результатами DESCRIBE TABLE:

Это хранимая процедура для получения списка всех столбцов в таблице для последующего использования в запросе — вместо синтаксиса SELECT * EXCEPT():

create or replace procedure cols_except(table_name varchar, except varchar)
returns varchar
language sql as 
begin
    describe table identifier(:table_name);
    return (
        select listagg("name", ', ') cols_except
        from table(result_scan(last_query_id())) 
        where not array_contains("name"::variant, (split(:except, ','))) 
    );
end;

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

call cols_except('snowflake_sample_data.tpch_sf1.nation', 'N_NAME,N_REGIONKEY'); 

Разыскиваемый синтаксис

Сценарии Snowflake находятся в предварительной версии и ждут ваших отзывов.

Надеюсь, мы скоро увидим расширенный синтаксис для execute immediate, так как приведенные выше скрипты могли бы быть короче с возможностью запрашивать execute immediate '' into и execute immediate (?) with.

Производительность

Я был приятно удивлен производительностью этих скриптов по сравнению с моим предыдущим опытом работы с BigQuery. Если вы хотите провести собственное сравнение или улучшить мои скрипты, вот они:

SQL-скрипты, эквивалентные BigQuery

DECLARE x INT64 DEFAULT 0;
LOOP
  SET x = x + 1;
  IF x >= 200 THEN
    LEAVE;
  END IF;
END LOOP;
SELECT x;
--
DECLARE x INT64 DEFAULT 0;
LOOP
  execute immediate 'select 1 +' || cast(x as string) into x;
  IF x >= 20 THEN
    LEAVE;
  END IF;
END LOOP;
SELECT x;
--
DECLARE x INT64 DEFAULT 0;
create or replace table temp.xxx(i int);
LOOP
  SET x = x + 1;
  execute immediate 'insert into temp.xxx (i) values(?)' using x;
  IF x >= 20 THEN
    LEAVE;
  END IF;
END LOOP;
SELECT x;

Следующие шаги

• Попробуйте шаблоны SQL с синтаксисом open cursor using (v1, v2):

• Изучите расширенные функции, такие как поддержка возврата таблицы и безопасный обмен хранимыми процедурами.
• Проверьте как перебирать результаты команды SHOW на Stack Overflow:

• Проверьте как вернуть таблицу, созданную в памяти при зацикливании:

Документация

• https://docs.snowflake.com/en/developer-guide/snowflake-scripting/index.html
• https://docs.snowflake.com/en/sql-reference-snowflake-scripting.html

Хочу больше?

• Попробуйте это с учетной записью Бесплатная пробная версия Snowflake — вам нужен только адрес электронной почты, чтобы начать.

Я Фелипе Хоффа, специалист по Data Cloud Advocate Snowflake. Спасибо, что присоединились ко мне в этом приключении. Вы можете подписаться на меня в Twitter и LinkedIn. Заходите на reddit.com/r/snowflake, чтобы быть в курсе самых интересных новостей Snowflake.