시퀀스 사용하기¶

시퀀스 의미 체계¶

Snowflake 시퀀스에서 현재 사용 중인 의미 체계는 다음과 같습니다.

• (예: 단계 크기 변경으로) 시퀀스 간격의 부호가 변경되는 경우를 제외하고 시퀀스에서 생성되는 모든 값은 전역에서 고유합니다. 동시 쿼리에서는 절대로 값이 동일하지 않으며 단일 쿼리 내의 값은 항상 고유합니다.

• 시퀀스 간격을 양수에서 음수로(예: 1 에서 -1 로) 변경하거나 그 반대로 변경하면 중복이 발생할 수 있습니다. 예를 들어, 첫 번째 쿼리가 시퀀스 값 1, 2, 3 을 반환하고 간격이 1 에서 -1 로 변경되면 다음으로 생성되는 몇 개의 값에는 이전에 생성되었던 2 와 1 이 포함됩니다.

• Snowflake는 다음 시퀀스 번호가 요청될 때까지 기다리지 않고 현재 시퀀스 번호가 사용되는 즉시 시퀀스의 다음 값을 계산할 수 있습니다.

  그 결과, ALTER SEQUENCE ... SET INCREMENT ... 명령이 시퀀스를 사용하는 다음 작업에 영향을 미치지 않을 수 있습니다. 예를 보려면 시퀀스 방향 반전 효과의 이해 를 참조하십시오.

• 생성된 각 시퀀스 값에서는 시퀀스 간격에 따라 추가 값을 예약하며, 이를 “단계”라고도 합니다. 예약된 값의 범위는 시퀀스 값에서

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

  까지(포함)입니다.

  그러므로 100 값이 생성된 경우:

  • 2 단계를 통해 100 및 101 값이 예약됩니다.

  • 10 단계를 통해 100 ~ 109 값이 예약됩니다.

  • -5 단계를 통해 96 ~ 100 값이 예약됩니다.

  단계/간격이 수정되는 경우를 제외하고 예약된 값은 절대로 시퀀스에 의해 생성되지 않습니다.

• 시퀀스에 의해 생성된 값은 이전 문에 의해 생성된 최대값보다 큽니다(또는 단계 크기가 음수인 경우에는 최소값보다 작음). 단, 다음 사항이 참이어야 합니다.

  • 시퀀스에 NOORDER 속성이 없습니다.

    NOORDER를 지정하면 값이 오름차순으로 표시되도록 보장되지 않습니다.

    예를 들어 시퀀스에 START 1 INCREMENT 2가 있는 경우 생성된 값은 1, 3, 101, 5, 103 등이 될 수 있습니다.

    NOORDER는 여러 삽입 작업을 동시에 수행해야 하는 경우(예: 여러 클라이언트가 여러 INSERT 문을 실행하는 경우) 성능을 향상시킬 수 있습니다.

  • 현재 문을 제출하기 전에 이전 문이 완료되었으며 승인이 수신되었습니다.

  이 동작은 간격의 부호가 변경되는 경우(양수에서 음수 또는 음수에서 양수로) 유지되지 않습니다.

시퀀스의 값이 연속적(간격 없음)이거나 시퀀스 값이 특정 순서대로 할당된다는 보장은 없습니다. 사실, 시퀀스의 값을 지정된 순서로 행에 할당하기 위한 방법은 단일 행 문을 사용하는 방법이 유일합니다(이 경우에도 간격이 보장되지 않음).

시퀀스 값은 64비트 2의 보수 정수(-2^63 ~ 2^63 - 1)를 나타낼 수 있습니다. 시퀀스에서 다음 값의 내부 표현이 이 범위(방향에 관계없이)를 초과하면 오류가 발생되고 쿼리가 실패합니다. 이로 인해 이러한 시퀀스 값이 손실 될 수 있음에 유의하십시오.

이러한 경우에는 더 작은(크기) 증분 값을 사용하거나 시작 값이 더 작은 새 시퀀스를 생성해야 합니다. 간격이 발생할 수 있으므로, 반환되는 시퀀스 값이 모두 허용 범위 이내인 경우에도 다음 값의 내부 표현은 허용 범위를 초과할 수 있습니다. Snowflake는 이러한 오류를 방지하기 위한 방법에 대한 명시적인 보장을 하지 않지만, 고유 값을 올바르게 제공하는 시퀀스 오브젝트를 지원합니다. 시작 값이 1 이고 증분 값이 1 인 시퀀스 오브젝트는 시퀀스 값의 허용 범위를 초과할 가능성이 극히 작습니다.

시퀀스 참조하기¶

시퀀스를 사용하여 기본 열 값 생성하기¶

테이블에서 시퀀스를 사용하여 테이블 열에 대한 기본 키를 생성할 수 있습니다. 이러한 작업을 편리하게 수행하기 위해 제공되는 도구는 다음과 같습니다.

비정규화된 데이터 수집 및 정규화하기¶

2개의 테이블(people 및 contact)이 있는 스키마를 생각해 보겠습니다.

• people 테이블에 포함되는 항목은 다음과 같습니다.

  • 기본 키 고유 식별자: id

  • 문자열 열 2개: firstName 및 lastName

• contact 테이블에 포함되는 항목은 다음과 같습니다.

  • 기본 키 고유 식별자: id

  • 이 연락처 항목을 개인과 연결하는 외래 키: p_id

  • 문자열 열 2개:

    • c_type: 연락처 타입(예: ‘이메일’ 또는 ‘전화’)

    • data: 실제 연락처 정보

이러한 형식의 데이터는 수집을 위해 또는 반정형 데이터를 처리하는 동안 비정규화되는 경우가 많습니다.

이 예에서는 JSON 데이터를 수집하고, 원하는 데이터를 추출하기 위해 비정규화를 수행하며, 이 데이터를 테이블에 삽입할 때 데이터를 정규화하는 방법을 살펴봅니다. 그리고 전체 테이블 행에서 의도한 관계를 유지하는 동시에 행에 대한 고유 식별자를 생성하는 것이 중요합니다. Snowflake는 시퀀스를 사용하여 이를 수행합니다.

1. 우선, 예에서 사용되는 테이블 및 시퀀스를 설정합니다.

   -- 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. 그리고 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. 그리고 JSON을 구문 분석 및 평면화하고, 각 개인과 연락처 항목에 대한 고유 식별자를 생성하며, 개인과 연락처 항목 사이의 관계를 유지하는 동시에 데이터를 삽입합니다.

   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. 이를 통해 생성되는 데이터는 다음과 같습니다(고유 IDs는 변경될 수 있음).

   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

보시다시피, people.id 와 contact.p_id 사이에서 행이 연결 및 조인됩니다.

추가적인 데이터가 추가된 경우 새로운 행은 계속해서 고유 IDs를 수신합니다. 예:

 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

시퀀스 변경하기¶

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 |
+---+---+