Uso de sequências

Sequências são usadas para gerar números únicos para sessões e instruções, incluindo instruções simultâneas. Elas podem ser usadas para gerar valores para uma chave primária ou qualquer coluna que requeira um valor único.

Importante

O Snowflake não garante a geração de números sequenciais sem lacunas. Os números gerados não são necessariamente contíguos.

Neste tópico:

Semântica da sequência

As sequências do Snowflake usam a seguinte semântica:

  • Todos os valores gerados por uma sequência são globalmente únicos, desde que o sinal do intervalo da sequência não mude (por exemplo, alterando o tamanho do passo). Consultas simultâneas nunca observam o mesmo valor, e os valores dentro de uma única consulta são sempre distintos.

  • A mudança do intervalo da sequência de positivo para negativo (por exemplo, de 1 para -1), ou vice-versa, pode resultar em duplicatas. Por exemplo, se a(s) primeira(s) consulta(s) retornar(em) valores de sequência 1, 2 e 3, e se o intervalo for alterado de 1 para -1, então os próximos valores gerados incluem 2 e 1, que foram gerados anteriormente.

  • O Snowflake pode calcular o próximo valor para uma sequência assim que o número da sequência atual for utilizado, em vez de esperar até que o próximo número da sequência seja solicitado.

    Uma consequência disto é que um comando ALTER SEQUENCE ... SET INCREMENT ... pode não afetar a próxima operação que utiliza a sequência. Para obter um exemplo, consulte Efeitos da inversão da direção de uma sequência.

  • Cada valor de sequência gerado reserva valores adicionalmente, dependendo do intervalo da sequência, também chamado de “passo”. Os valores reservados vão desde o valor da sequência até

    <value>  +  (sign(<step>) * abs(<step>))  -  (sign(<step>) * 1)

    (inclusive).

    Assim, se o valor 100 for gerado:

    • Com um passo de 2, os valores 100 e 101 são reservados.

    • Com um passo de 10, os valores 100 até 109 são reservados.

    • Com um passo de -5, os valores 96 até 100 são reservados.

    Um valor reservado nunca é gerado pela sequência desde que o passo/intervalo nunca seja modificado.

  • Os valores gerados por uma sequência são maiores que o valor máximo produzido por uma instrução anterior (ou menores que o valor mínimo se o tamanho do passo for negativo) se o seguinte for verdadeiro:

    • A sequência não possui a propriedade NOORDER.

    • A instrução anterior foi concluída e uma confirmação foi recebida antes de enviar a instrução atual.

    Este comportamento não se mantém se o sinal do intervalo for alterado (positivo para negativo ou negativo para positivo).

Não há garantia de que os valores de uma sequência sejam contíguos (sem lacunas) ou que os valores da sequência sejam atribuídos em uma determinada ordem. De fato, não há outra forma de atribuir valores de uma sequência a linhas em uma ordem específica além de utilizar instruções de uma única linha (isto ainda não oferece nenhuma garantia sobre lacunas).

Um valor de sequência pode representar um número inteiro complemento de dois de 64 bits (-2^63 a 2^63 - 1). Se a representação interna do próximo valor de uma sequência exceder este intervalo (em qualquer direção), resulta um erro e a consulta falha. Note que isto pode resultar na perda destes valores sequenciais.

Nesta situação, você deve usar um valor de incremento menor (em magnitude) ou criar uma nova sequência com um valor inicial menor. Como podem ocorrer lacunas, a representação interna do próximo valor pode exceder o intervalo permissível mesmo que os valores da sequência retornada estejam todos dentro do intervalo permissível. O Snowflake não oferece uma garantia explícita sobre como evitar este erro, mas o Snowflake suporta objetos em sequência que fornecem valores únicos e corretos. Um objeto de sequência criado com um valor inicial de 1 e um valor de incremento de 1 é extremamente improvável de esgotar o intervalo permissível de valores de sequência.

Referência a sequências

currval sem suporte

Muitos bancos de dados fornecem uma referência de sequência currval; no entanto, Snowflake não o faz. currval em outros sistemas é normalmente usado para criar relações de chave primária/estrangeira entre tabelas: uma primeira instrução insere uma única linha na tabela de fatos usando uma sequência para criar uma chave. Instruções subsequentes inserem linhas nas tabelas de dimensões usando currval para se referir à chave da tabela de fatos.

Este padrão é contrário às práticas recomendadas da Snowflake – consultas em massa devem ser preferidas em relação a consultas pequenas de uma única linha. A mesma tarefa pode ser melhor realizada usando-se INSERT multitabelas e referências de sequência em subconsultas aninhadas. Para um exemplo detalhado, consulte Ingestão e normalização de dados desnormalizados (neste tópico).

Sequências como expressões

As sequências podem ser acessadas em consultas como expressões do formulário seq_name.NEXTVAL. Cada ocorrência de uma sequência gera um conjunto de valores distintos. Isto é diferente do que muitos outros bancos de dados fornecem, onde múltiplas referências a NEXTVAL de uma sequência retornam o mesmo valor para cada linha.

Por exemplo, a seguinte consulta retorna valores distintos para as colunas a e b:

CREATE OR REPLACE SEQUENCE seq1;

SELECT seq1.NEXTVAL a, seq1.NEXTVAL b FROM DUAL;
Copy

Para retornar duas colunas com o mesmo valor sequencial gerado, use subconsultas e exibições aninhadas:

CREATE OR REPLACE SEQUENCE seq1;

SELECT seqRef.a a, seqRef.a b FROM (SELECT seq1.NEXTVAL a FROM DUAL) seqRef;
Copy

Subconsultas aninhadas geram tantos valores de sequência distintos quanto as linhas retornadas pela subconsulta (assim, uma referência de sequência em um bloco de consulta com várias junções refere-se não a nenhum dos objetos unidos, mas à saída do bloco de consulta). Estes valores gerados podem não ser observados se as linhas associadas forem filtradas posteriormente, ou os valores podem ser observados duas vezes (como no exemplo acima) se a coluna de sequência ou a exibição em linha forem referenciadas várias vezes.

Nota

Para inserção multitabelas, os valores inseridos podem ser fornecidos tanto nas cláusulas VALUES como na entrada SELECT:

  • Cláusulas VALUES referindo-se a um valor de sequência com alias da entrada SELECT recebem o mesmo valor.

  • Cláusulas VALUES contendo uma referência direta a uma sequência NEXTVAL recebem valores distintos.

Em contraste, a Oracle restringe as referências de sequência somente a cláusulas VALUES.

Sequências como funções de tabela

Consultas aninhadas com referências de sequência são frequentemente difíceis de entender e prolixas – qualquer referência compartilhada (onde duas colunas de uma linha devem receber o mesmo valor de sequência) requer um nível adicional de aninhamento de consultas. Para simplificar esta sintaxe de consulta aninhada, o Snowflake fornece um método adicional para gerar sequências usando a função de tabela GETNEXTVAL, como no exemplo a seguir:

CREATE OR REPLACE SEQUENCE seq1;

CREATE OR REPLACE TABLE foo (n NUMBER);

INSERT INTO foo VALUES (100), (101), (102);

SELECT n, s.nextval FROM foo, TABLE(GETNEXTVAL(seq1)) s;
Copy

GETNEXTVAL é uma função especial da tabela de 1 linha que gera um valor único (e une este valor) a outros objetos na instrução SELECT. Uma chamada para GETNEXTVAL deve ter um alias; caso contrário, os valores gerados não podem ser referenciados. Várias colunas podem se referir a um valor gerado acessando este alias. O alias GETNEXTVAL contém um atributo também chamado NEXTVAL.

A função de tabela GETNEXTVAL adicionalmente permite um controle preciso sobre a geração de sequências quando muitas tabelas são unidas. A ordem dos objetos na cláusula FROM determina onde os valores são gerados. Os valores sequenciais são gerados para o resultado das junções entre todos os objetos listados antes de GETNEXTVAL na cláusula FROM. As linhas resultantes são então juntadas aos objetos à direita. Há uma dependência lateral implícita entre GETNEXTVAL e todos os outros objetos na cláusula FROM. As junções não podem ser reordenadas em torno de GETNEXTVAL. Esta é uma exceção em SQL, pois normalmente a ordem dos objetos não afeta a semântica da consulta.

Considere o seguinte exemplo com as tabelas t1, t2, t3 e t4:

CREATE OR REPLACE SEQUENCE seq1;

SELECT t1.*, t2.*, t3.*, t4.*, s.NEXTVAL FROM t1, t2, TABLE(GETNEXTVAL(seq1)) s, t3, t4;
Copy

Esta consulta unirá t1 a t2, gerará um valor único do resultado e então unirá a relação resultante contra t3 e t4. A ordem das junções entre a relação pós-sequência, t3, e t4 não é especificada porque as junções internas são associativas.

Nota

Esta semântica pode ser complicada. Recomendamos usar GETNEXTVAL no final da cláusula FROM, quando possível e apropriado, para evitar confusão.

Uso de sequências para criar valores de coluna padrão

As sequências podem ser usadas em tabelas para gerar chaves primárias para colunas de tabelas. As seguintes ferramentas fornecem uma maneira simples de fazer isso.

Expressões padrão de coluna

A expressão padrão da coluna pode ser uma referência de sequência. Omitir a coluna em uma instrução de inserção ou definir o valor como DEFAULT em uma instrução de inserção ou atualização irá gerar um novo valor de sequência para a linha.

Por exemplo:

CREATE OR REPLACE SEQUENCE seq1;

CREATE OR REPLACE TABLE foo (k NUMBER DEFAULT seq1.NEXTVAL, v NUMBER);

-- insert rows with unique keys (generated by seq1) and explicit values
INSERT INTO foo (v) VALUES (100);
INSERT INTO foo VALUES (DEFAULT, 101);

-- insert rows with unique keys (generated by seq1) and reused values.
-- new keys are distinct from preexisting keys.
INSERT INTO foo (v) SELECT v FROM foo;

-- insert row with explicit values for both columns
INSERT INTO foo VALUES (1000, 1001);

SELECT * FROM foo;

+------+------+
|    K |    V |
|------+------|
|    1 |  100 |
|    2 |  101 |
|    3 |  100 |
|    4 |  101 |
| 1000 | 1001 |
+------+------+
Copy

A vantagem de usar sequências como valor padrão de coluna é que a sequência pode ser referenciada em outros locais, e até mesmo ser o valor padrão para várias colunas e em várias tabelas. Se uma sequência for nomeada como a expressão padrão de uma coluna e, em seguida, descartar qualquer tentativa de inserir/atualizar a tabela usando o valor padrão, ocorrerá um erro de que o identificador não pode ser encontrado.

Ingestão e normalização de dados desnormalizados

Considere um esquema com duas tabelas, people e contact:

  • A tabela people contém:

    • Um identificador único de chave primária: id

    • Duas colunas de cadeia de caracteres: firstName e lastName

  • A tabela contact contém:

    • Um identificador único de chave primária: id

    • Uma chave estrangeira vinculando esta entrada de contato a uma pessoa: p_id

    • Duas colunas de cadeia de caracteres:

      • c_type: O tipo de contato (por exemplo, “e-mail” ou “telefone”).

      • data: As informações de contato propriamente ditas.

Dados neste formato são frequentemente desnormalizados para ingestão ou durante o processamento de dados semiestruturados.

Este exemplo ilustra a ingestão de dados JSON, desnormalizando-os para extrair os dados desejados e normalizando os dados à medida que são inseridos em tabelas. Ao mesmo tempo, é importante criar identificadores únicos nas linhas, mantendo as relações pretendidas entre as linhas de tabelas. Realizamos isto com sequências.

  1. Primeiro, configuramos as tabelas e sequências utilizadas no exemplo:

    -- primary data tables
    
    CREATE OR REPLACE TABLE people (id number, firstName string, lastName string);
    CREATE OR REPLACE TABLE contact (id number, p_id number, c_type string, data string);
    
    -- sequences to produce primary keys on our data tables
    
    CREATE OR REPLACE SEQUENCE people_seq;
    CREATE OR REPLACE SEQUENCE contact_seq;
    
    -- staging table for json
    
    CREATE OR REPLACE TABLE input (json variant);
    
    Copy
  2. A seguir, inserimos dados da tabela json:

    INSERT INTO input SELECT parse_json(
    '[
     {
       firstName : \'John\',
       lastName : \'Doe\',
       contacts : [
         {
           contactType : \'phone\',
           contactData : \'1234567890\',
         }
         ,
         {
           contactType : \'email\',
           contactData : \'jdoe@acme.com\',
         }
        ]
       }
    ,
      {
       firstName : \'Mister\',
       lastName : \'Smith\',
       contacts : [
         {
           contactType : \'phone\',
           contactData : \'0987654321\',
         }
         ,
         {
           contactType : \'email\',
           contactData : \'msmith@acme.com\',
         }
         ]
       }
     ,
       {
       firstName : \'George\',
       lastName : \'Washington\',
       contacts : [
         {
           contactType : \'phone\',
           contactData : \'1231231234\',
         }
         ,
         {
           contactType : \'email\',
           contactData : \'gwashington@acme.com\',
         }
       ]
     }
    ]'
    );
    
    Copy
  3. Então, analisamos e nivelamos o JSON, geramos identificadores únicos para cada pessoa e entrada de contato e inserimos os dados preservando as relações entre pessoas e entradas de contato:

    INSERT ALL
      WHEN 1=1 THEN
        INTO contact VALUES (c_next, p_next, contact_value:contactType, contact_value:contactData)
      WHEN contact_index = 0 THEN
        INTO people VALUES (p_next, person_value:firstName, person_value:lastName)
    
    SELECT * FROM
    (
      SELECT f1.value person_value, f2.value contact_value, f2.index contact_index, p_seq.NEXTVAL p_next, c_seq.NEXTVAL c_next
      FROM input, LATERAL FLATTEN(input.json) f1, TABLE(GETNEXTVAL(people_seq)) p_seq,
        LATERAL FLATTEN(f1.value:contacts) f2, table(GETNEXTVAL(contact_seq)) c_seq
    );
    
    Copy
  4. Isto produz os seguintes dados (IDs únicas podem mudar):

    SELECT * FROM people;
    
    +----+-----------+------------+
    | ID | FIRSTNAME | LASTNAME   |
    |----+-----------+------------|
    |  1 | John      | Doe        |
    |  2 | Mister    | Smith      |
    |  3 | George    | Washington |
    +----+-----------+------------+
    
    SELECT * FROM contact;
    
    +----+------+--------+----------------------+
    | ID | P_ID | C_TYPE | DATA                 |
    |----+------+--------+----------------------|
    |  1 |    1 | phone  | 1234567890           |
    |  2 |    1 | email  | jdoe@acme.com        |
    |  3 |    2 | phone  | 0987654321           |
    |  4 |    2 | email  | msmith@acme.com      |
    |  5 |    3 | phone  | 1231231234           |
    |  6 |    3 | email  | gwashington@acme.com |
    +----+------+--------+----------------------+
    
    Copy

Como você pode ver, as linhas estão vinculadas e podem ser unidas, entre people.id e contact.p_id.

Se mais dados forem adicionados, novas linhas continuam a receber IDs únicas. Por exemplo:

 TRUNCATE TABLE input;

 INSERT INTO input SELECT PARSE_JSON(
 '[
  {
    firstName : \'Genghis\',
    lastName : \'Khan\',
    contacts : [
      {
        contactType : \'phone\',
        contactData : \'1111111111\',
      }
      ,
      {
        contactType : \'email\',
        contactData : \'gkahn@acme.com\',
      }
   ]
 }
,
 {
    firstName : \'Julius\',
    lastName : \'Caesar\',
    contacts : [
      {
        contactType : \'phone\',
        contactData : \'2222222222\',
      }
      ,
      {
        contactType : \'email\',
        contactData : \'gcaesar@acme.com\',
      }
    ]
  }
 ]'
 );

 INSERT ALL
   WHEN 1=1 THEN
     INTO contact VALUES (c_next, p_next, contact_value:contactType, contact_value:contactData)
   WHEN contact_index = 0 THEN
     INTO people VALUES (p_next, person_value:firstName, person_value:lastName)
 SELECT * FROM
 (
   SELECT f1.value person_value, f2.value contact_value, f2.index contact_index, p_seq.NEXTVAL p_next, c_seq.NEXTVAL c_next
   FROM input, LATERAL FLATTEN(input.json) f1, table(GETNEXTVAL(people_seq)) p_seq,
     LATERAL FLATTEN(f1.value:contacts) f2, table(GETNEXTVAL(contact_seq)) c_seq
 );

 SELECT * FROM people;

 +----+-----------+------------+
 | ID | FIRSTNAME | LASTNAME   |
 |----+-----------+------------|
 |  4 | Genghis   | Khan       |
 |  5 | Julius    | Caesar     |
 |  1 | John      | Doe        |
 |  2 | Mister    | Smith      |
 |  3 | George    | Washington |
 +----+-----------+------------+

 SELECT * FROM contact;

 +----+------+--------+----------------------+
 | ID | P_ID | C_TYPE | DATA                 |
 |----+------+--------+----------------------|
 |  1 |    1 | phone  | 1234567890           |
 |  2 |    1 | email  | jdoe@acme.com        |
 |  3 |    2 | phone  | 0987654321           |
 |  4 |    2 | email  | msmith@acme.com      |
 |  5 |    3 | phone  | 1231231234           |
 |  6 |    3 | email  | gwashington@acme.com |
 |  7 |    4 | phone  | 1111111111           |
 |  8 |    4 | email  | gkahn@acme.com       |
 |  9 |    5 | phone  | 2222222222           |
 | 10 |    5 | email  | gcaesar@acme.com     |
 +----+------+--------+----------------------+
Copy

Alteração de uma sequência

Efeitos da inversão da direção de uma sequência

O exemplo a seguir mostra o que acontece quando se inverte a direção de uma sequência.

Ele também mostra que, devido ao pré-cálculo dos valores da sequência, um comando ALTER SEQUENCE pode parecer ter efeito somente após o segundo uso da sequência após a execução do comando ALTER SEQUENCE.

Crie a sequência e use-a como o valor padrão para uma coluna em uma tabela:

CREATE OR REPLACE SEQUENCE test_sequence_wraparound_low
   START = 1
   INCREMENT = 1
   ;

CREATE or replace TABLE test_seq_wrap_low (
    i int,
    j int default test_sequence_wraparound_low.nextval
    );
Copy

Carregue a tabela:

INSERT INTO test_seq_wrap_low (i) VALUES
     (1),
     (2),
     (3);
Copy

Mostre os valores da sequência na coluna j:

SELECT * FROM test_seq_wrap_low ORDER BY i;
+---+---+
| I | J |
|---+---|
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+---+---+
Copy

Altere o incremento (tamanho do passo) da sequência:

ALTER SEQUENCE test_sequence_wraparound_low SET INCREMENT = -4;
Copy

Insira mais duas linhas:

INSERT INTO test_seq_wrap_low (i) VALUES
    (4),
    (5);
Copy

Mostre os valores da sequência. Observe que a primeira linha inserida após ALTER SEQUENCE tem o valor 4, e não -1. A segunda linha inserida após ALTER SEQUENCE leva em conta o novo tamanho do passo.

SELECT * FROM test_seq_wrap_low ORDER BY i;
+---+---+
| I | J |
|---+---|
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 0 |
+---+---+
Copy