Exemplos de casos de uso comuns do Script Snowflake¶

Você pode escrever blocos anônimos e procedimentos armazenados que usam elementos da linguagem de script Snowflake, tipos de dados e variáveis para soluções que abordam casos de uso comuns. Este tópico inclui exemplos de código de script do Snowflake para alguns casos de uso comuns.

Atualização de dados da tabela com entrada do usuário¶

O exemplo a seguir cria um procedimento armazenado que atualiza os dados da tabela com a entrada do usuário. Ele usa um loop FOR para iterar sobre as linhas em um RESULTSET para a tabela. O loop FOR contém variáveis de vinculação de lógica condicional. <label-snowscript_variables_binding> com base na entrada do usuário, determine as atualizações exatas executadas pelo procedimento armazenado.

O exemplo usa os seguintes dados:

CREATE OR REPLACE TABLE bonuses (
  emp_id INT,
  performance_rating INT,
  salary NUMBER(12, 2),
  bonus NUMBER(12, 2)
);

INSERT INTO bonuses (emp_id, performance_rating, salary, bonus) VALUES
  (1001, 3, 100000, NULL),
  (1002, 1, 50000, NULL),
  (1003, 4, 75000, NULL),
  (1004, 4, 80000, NULL),
  (1005, 5, 120000, NULL),
  (1006, 2, 60000, NULL),
  (1007, 5, 40000, NULL),
  (1008, 3, 140000, NULL),
  (1009, 1, 95000, NULL);

SELECT * FROM bonuses;

+--------+--------------------+-----------+-------+
| EMP_ID | PERFORMANCE_RATING |    SALARY | BONUS |
|--------+--------------------+-----------+-------|
|   1001 |                  3 | 100000.00 |  NULL |
|   1002 |                  1 |  50000.00 |  NULL |
|   1003 |                  4 |  75000.00 |  NULL |
|   1004 |                  4 |  80000.00 |  NULL |
|   1005 |                  5 | 120000.00 |  NULL |
|   1006 |                  2 |  60000.00 |  NULL |
|   1007 |                  5 |  40000.00 |  NULL |
|   1008 |                  3 | 140000.00 |  NULL |
|   1009 |                  1 |  95000.00 |  NULL |
+--------+--------------------+-----------+-------+

O procedimento armazenado a seguir usa um loop FOR para iterar sobre as linhas em um RESULTSET para a tabela bonuses. Ele aplica o bônus como uma porcentagem especificada do salário de cada funcionário com a classificação de desempenho especificada. O procedimento armazenado usa lógica condicional para aplicar o bônus somente aos funcionários com a classificação de desempenho especificada. Ele também usa as entradas (bonus_percentage e performance_value) como variáveis de vinculação.

CREATE OR REPLACE PROCEDURE apply_bonus(bonus_percentage INT, performance_value INT)
  RETURNS TEXT
  LANGUAGE SQL
AS
DECLARE
  -- Use input to calculate the bonus percentage
  updated_bonus_percentage NUMBER(2,2) DEFAULT (:bonus_percentage/100);
  --  Declare a result set
  rs RESULTSET;
BEGIN
  -- Assign a query to the result set and execute the query
  rs := (SELECT * FROM bonuses);
  -- Use a FOR loop to iterate over the records in the result set
  FOR record IN rs DO
    -- Assign variable values using values in the current record
    LET emp_id_value INT := record.emp_id;
    LET performance_rating_value INT := record.performance_rating;
    LET salary_value NUMBER(12, 2) := record.salary;
    -- Determine whether the performance rating in the record matches the user input
    IF (performance_rating_value = :performance_value) THEN
      -- If the condition is met, update the bonuses table using the calculated bonus percentage
      UPDATE bonuses SET bonus = ( :salary_value * :updated_bonus_percentage )
        WHERE emp_id = :emp_id_value;
    END IF;
  END FOR;
  -- Return text when the stored procedure completes
  RETURN 'Update applied';
END;

Note: If you use Snowflake CLI, SnowSQL, the Classic Console, or the execute_stream or execute_string method in Python Connector code, use this example instead (see Using Snowflake Scripting in Snowflake CLI, SnowSQL, and Python Connector):

CREATE OR REPLACE PROCEDURE apply_bonus(bonus_percentage INT, performance_value INT)
  RETURNS TEXT
  LANGUAGE SQL
AS
$$
DECLARE
  -- Use input to calculate the bonus percentage
  updated_bonus_percentage NUMBER(2,2) DEFAULT (:bonus_percentage/100);
  --  Declare a result set
  rs RESULTSET;
BEGIN
  -- Assign a query to the result set and execute the query
  rs := (SELECT * FROM bonuses);
  -- Use a FOR loop to iterate over the records in the result set
  FOR record IN rs DO
    -- Assign variable values using values in the current record
    LET emp_id_value INT := record.emp_id;
    LET performance_rating_value INT := record.performance_rating;
    LET salary_value NUMBER(12, 2) := record.salary;
    -- Determine whether the performance rating in the record matches the user input
    IF (performance_rating_value = :performance_value) THEN
      -- If the condition is met, update the bonuses table using the calculated bonus percentage
      UPDATE bonuses SET bonus = ( :salary_value * :updated_bonus_percentage )
        WHERE emp_id = :emp_id_value;
    END IF;
  END FOR;
  -- Return text when the stored procedure completes
  RETURN 'Update applied';
END;
$$
;

Para executar o procedimento armazenado, especifique a porcentagem de bônus e a classificação de desempenho. Por exemplo, chame o procedimento armazenado e aplique um bônus de 3% para funcionários com uma classificação de desempenho de 5:

CALL apply_bonus(3, 5);

Execute uma consulta para mostrar os resultados:

SELECT * FROM bonuses;

+--------+--------------------+-----------+---------+
| EMP_ID | PERFORMANCE_RATING |    SALARY |   BONUS |
|--------+--------------------+-----------+---------|
|   1001 |                  3 | 100000.00 |    NULL |
|   1002 |                  1 |  50000.00 |    NULL |
|   1003 |                  4 |  75000.00 |    NULL |
|   1004 |                  4 |  80000.00 |    NULL |
|   1005 |                  5 | 120000.00 | 3600.00 |
|   1006 |                  2 |  60000.00 |    NULL |
|   1007 |                  5 |  40000.00 | 1200.00 |
|   1008 |                  3 | 140000.00 |    NULL |
|   1009 |                  1 |  95000.00 |    NULL |
+--------+--------------------+-----------+---------+

Filtro e coleta de dados¶

O exemplo a seguir cria um procedimento armazenado que filtra e coleta os dados em uma tabela. O procedimento insere linhas usando os dados coletados em outra tabela para rastrear tendências históricas.

O exemplo usa os seguintes dados, que rastreiam a propriedade e as configurações das máquinas virtuais (VMs):

CREATE OR REPLACE TABLE vm_ownership (
  emp_id INT,
  vm_id VARCHAR
);

INSERT INTO vm_ownership (emp_id, vm_id) VALUES
  (1001, 1),
  (1001, 5),
  (1002, 3),
  (1003, 4),
  (1003, 6),
  (1003, 2);

CREATE OR REPLACE TABLE vm_settings (
  vm_id INT,
  vm_setting VARCHAR,
  value NUMBER
);

INSERT INTO vm_settings (vm_id, vm_setting, value) VALUES
  (1, 's1', 5),
  (1, 's2', 500),
  (2, 's1', 10),
  (2, 's2', 600),
  (3, 's1', 3),
  (3, 's2', 400),
  (4, 's1', 8),
  (4, 's2', 700),
  (5, 's1', 1),
  (5, 's2', 300),
  (6, 's1', 7),
  (6, 's2', 800);

CREATE OR REPLACE TABLE vm_settings_history (
  vm_id INT,
  vm_setting VARCHAR,
  value NUMBER,
  owner INT,
  date DATE
);

Suponha que uma empresa queira rastrear os dados nesta tabela ao longo do tempo quando os valores das configurações excedem limites específicos. O seguinte procedimento armazenado coleta e filtra os dados na tabela vm_settings e, em seguida, insere linhas na tabela vm_settings_history quando as seguintes condições forem atendidas:

Um vm_setting com um valor de s1 é definido mais baixo que 5.
Um vm_setting com um valor de s2 é definido mais alto que 500.

As linhas inseridas na tabela vm_settings_history inclui todos os valores da coluna da tabela vm_settings, juntamente com o emp_id do empregado que possui o VM e a data atual.

CREATE OR REPLACE PROCEDURE vm_user_settings()
  RETURNS VARCHAR
  LANGUAGE SQL
AS
DECLARE
  -- Declare a cursor and a variable
  c1 CURSOR FOR SELECT * FROM vm_settings;
  current_owner NUMBER;
BEGIN
  -- Open the cursor to execute the query and retrieve the rows into the cursor
  OPEN c1;
  -- Use a FOR loop to iterate over the records in the result set
  FOR record IN c1 DO
    -- Assign variable values using values in the current record
    LET current_vm_id NUMBER := record.vm_id;
    LET current_vm_setting VARCHAR := record.vm_setting;
    LET current_value NUMBER := record.value;
    -- Assign a value to the current_owner variable by querying the vm_ownership table
    SELECT emp_id INTO :current_owner
      FROM vm_ownership
      WHERE vm_id = :current_vm_id;
    -- If the record has a vm_setting equal to 's1', determine whether its value is less than 5
    IF (current_vm_setting = 's1' AND current_value < 5) THEN
      -- If the condition is met, insert a row into the vm_settings_history table
      INSERT INTO vm_settings_history VALUES (
        :current_vm_id,
        :current_vm_setting,
        :current_value,
        :current_owner,
        SYSDATE());
    -- If the record has a vm_setting equal to 's2', determine whether its value is greater than 500
    ELSEIF (current_vm_setting = 's2' AND current_value > 500) THEN
      -- If the condition is met, insert a row into the vm_settings_history table
      INSERT INTO vm_settings_history VALUES (
        :current_vm_id,
        :current_vm_setting,
        :current_value,
        :current_owner,
        SYSDATE());
    END IF;
  END FOR;
  -- Close the cursor
  CLOSE c1;
  -- Return text when the stored procedure completes
  RETURN 'Success';
END;

CREATE OR REPLACE PROCEDURE vm_user_settings()
  RETURNS VARCHAR
  LANGUAGE SQL
AS
$$
DECLARE
  -- Declare a cursor and a variable
  c1 CURSOR FOR SELECT * FROM vm_settings;
  current_owner NUMBER;
BEGIN
  -- Open the cursor to execute the query and retrieve the rows into the cursor
  OPEN c1;
  -- Use a FOR loop to iterate over the records in the result set
  FOR record IN c1 DO
    -- Assign variable values using values in the current record
    LET current_vm_id NUMBER := record.vm_id;
    LET current_vm_setting VARCHAR := record.vm_setting;
    LET current_value NUMBER := record.value;
    -- Assign a value to the current_owner variable by querying the vm_ownership table
    SELECT emp_id INTO :current_owner
      FROM vm_ownership
      WHERE vm_id = :current_vm_id;
    -- If the record has a vm_setting equal to 's1', determine whether its value is less than 5
    IF (current_vm_setting = 's1' AND current_value < 5) THEN
      -- If the condition is met, insert a row into the vm_settings_history table
      INSERT INTO vm_settings_history VALUES (
        :current_vm_id,
        :current_vm_setting,
        :current_value,
        :current_owner,
        SYSDATE());
    -- If the record has a vm_setting equal to 's2', determine whether its value is greater than 500
    ELSEIF (current_vm_setting = 's2' AND current_value > 500) THEN
      -- If the condition is met, insert a row into the vm_settings_history table
      INSERT INTO vm_settings_history VALUES (
        :current_vm_id,
        :current_vm_setting,
        :current_value,
        :current_owner,
        SYSDATE());
    END IF;
  END FOR;
  -- Close the cursor
  CLOSE c1;
  -- Return text when the stored procedure completes
  RETURN 'Success';
END;
$$;

Execute o procedimento armazenado:

CALL vm_user_settings();

Você pode ver os dados que o procedimento inseriu na tabela vm_settings_history executando a seguinte consulta:

SELECT * FROM vm_settings_history ORDER BY vm_id;

+-------+------------+-------+-------+------------+
| VM_ID | VM_SETTING | VALUE | OWNER | DATE       |
|-------+------------+-------+-------+------------|
|     2 | s2         |   600 |  1003 | 2024-04-01 |
|     3 | s1         |     3 |  1002 | 2024-04-01 |
|     4 | s2         |   700 |  1003 | 2024-04-01 |
|     5 | s1         |     1 |  1001 | 2024-04-01 |
|     6 | s2         |   800 |  1003 | 2024-04-01 |
+-------+------------+-------+-------+------------+