Uso de sequências¶

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.

    NOORDER especifica que não é garantido que os valores estejam em ordem crescente.

    Por exemplo, se uma sequência tiver START 1 INCREMENT 2, os valores gerados poderão ser 1, 3, 101, 5, 103 etc.

    NOORDER pode melhorar o desempenho quando várias operações de inserção precisam ser executadas simultaneamente (por exemplo, quando vários clientes estão executando várias instruções INSERT).

  • 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¶

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.

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¶

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
    );

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

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

ALTER SEQUENCE test_sequence_wraparound_low SET INCREMENT = -4;

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

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