시퀀스 사용하기

시퀀스를 사용하면 동시 실행 문 등의 세션 및 문에서 고유 번호를 생성할 수 있습니다. 그리고 고유 값이 필요한 기본 키 또는 열에 대한 값을 생성하기 위해서도 사용할 수 있습니다.

중요

Snowflake는 간격이 없는 시퀀스 번호가 생성됨을 보장하지 않습니다. 생성된 숫자는 값이 지속적으로 증가하지만(또는 단계 크기가 음수인 경우에는 값이 감소함), 반드시 연속적일 필요는 없습니다.

이 항목의 내용:

시퀀스 의미 체계

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

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

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

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

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

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

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

    까지(포함)입니다.

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

    • 2 단계를 통해 100101 값이 예약됩니다.

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

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

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

  • 시퀀스에 의해 생성된 값은 이전 문에 의해 생성된 최대값보다 큽니다(또는 단계 크기가 음수인 경우에는 최소값보다 작음). , 이전 문이 완료되고 승인이 수신된 후에 현재 문을 제출해야 합니다.

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

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

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

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

시퀀스 참조하기

currval 미지원

많은 데이터베이스에서는 currval 시퀀스 참조가 제공되지만, Snowflake는 제공하지 않습니다. 다른 시스템에서 currval 은 일반적으로 테이블 간의 기본-외래 키 관계를 생성하기 위해 사용되며, 첫 번째 문은 키를 생성하는 시퀀스를 사용하여 단일 행을 정보 테이블에 삽입합니다. 이후의 문은 currval 을 사용하여 차원 테이블에 행을 삽입하여 정보 테이블의 키를 참조합니다.

이러한 패턴은 Snowflake 모범 사례를 위반하는 것이며, 소규모의 단일 행 쿼리가 아닌 대량 쿼리가 기본으로 사용되어야 합니다. 중첩된 하위 쿼리에서는 다중 테이블 INSERT 및 시퀀스 참조를 사용하여 동일한 작업의 성능을 향상할 수 있습니다. 자세한 예는 이 항목의 비정규화된 데이터 수집 및 정규화하기 를 참조하십시오.

식으로써의 시퀀스

시퀀스는 쿼리에서 seq_name.NEXTVAL 형식의 식으로 액세스할 수 있습니다. 시퀀스가 발생할 때마다 고유 값 세트가 생성됩니다. 이러한 방식은 시퀀스의 NEXTVAL 에 대한 다중 참조에서 각 행에 대해 동일한 값을 반환하는 다른 여러 데이터베이스의 방식과 다릅니다.

예를 들어, 다음 쿼리는 ab 열에 대한 고유 값을 반환합니다.

CREATE OR REPLACE SEQUENCE seq1;

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

동일한 시퀀스 값이 생성된 열 2개를 반환하려면 중첩된 하위 쿼리 및 뷰를 사용합니다.

CREATE OR REPLACE SEQUENCE seq1;

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

중첩된 하위 쿼리는 하위 쿼리에서 반환된 행의 개수만큼 고유한 시퀀스 값을 생성합니다(따라서 여러 조인이 포함된 쿼리 블록에서 시퀀스 참조는 조인된 오브젝트가 아닌 쿼리 블록의 출력을 참조). 나중에 연결된 행이 필터링되면 생성된 이러한 값이 표시되지 않을 수 있으며, 시퀀스 열 또는 인라인 뷰가 여러 번 참조되는 경우에는 값이 두 번(위의 예에서와 같이) 표시될 수 있습니다.

참고

다중 테이블 삽입의 경우 삽입 값은 VALUES 절 및 SELECT 입력 모두에 제공될 수 있습니다.

  • SELECT 입력에서 별칭이 지정된 시퀀스 값을 참조하는 VALUES 절에는 동일한 값이 수신됩니다.

  • NEXTVAL 시퀀스에 대한 직접 참조가 포함된 VALUES 절은 고유 값을 수신합니다.

대조적으로, Oracle에서는 VALUES 절에서만 시퀀스를 참조할 수 있습니다.

테이블 함수로써의 시퀀스

시퀀스 참조가 있는 중첩 쿼리는 이해하기 어렵고 세부적인 경우가 많으므로, 공유 참조(행의 두 열이 동일한 시퀀스 값을 수신해야 하는 경우)를 위해서는 추가적인 수준의 쿼리 중첩이 필요합니다. 중첩 쿼리 구문을 간소화하기 위해 Snowflake는 다음 예에서와 같이 GETNEXTVAL 테이블 함수를 사용하여 시퀀스를 생성할 수 있는 추가적인 방법을 제공합니다.

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;

GETNEXTVAL은 SELECT 문의 다른 오브젝트에 고유한 값을 생성하고 이 값을 조인하는 특수한 단일 행 테이블 함수입니다. GETNEXTVAL에 대한 호출은 별칭을 지정해야 하며, 그렇지 않으면 생성된 값을 참조할 수 없습니다. 이 별칭에 액세스하면 생성된 값을 여러 열이 참조할 수 있습니다. GETNEXTVAL 별칭에는 NEXTVAL 속성도 포함되어 있습니다.

GETNEXTVAL 테이블 함수를 사용하면 여러 테이블을 조인할 때 정밀하게 시퀀스 생성을 제어할 수 있습니다. FROM 절에서 값이 생성되는 위치는 오브젝트의 순서에 따라 결정됩니다. FROM 절에서 GETNEXTVAL 앞에 나열된 모든 오브젝트 사이의 조인 결과에 따라 시퀀스 값이 생성됩니다. 그리고 결과 행이 오른쪽에 있는 오브젝트에 조인됩니다. GETNEXTVAL와 FROM 절의 다른 모든 오브젝트 사이에는 암묵적인 래터럴 종속성이 있습니다. 조인은 GETNEXTVAL 인근에서 재정렬되지 않을 수 있습니다. 일반적으로 오브젝트의 순서는 쿼리 의미 체계에 영향을 주지 않으므로 이는 SQL에서 예외입니다.

t1, t2, t3t4 테이블이 포함된 다음 예를 생각해 보십시오.

CREATE OR REPLACE SEQUENCE seq1;

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

이 쿼리에서는 t1t2 에 조인하고 결과에 대한 고유 값을 생성한 후 t3t4 에 대한 결과 관계를 조인합니다. 내부 조인은 연결되므로 사후 시퀀스 관계인 t3t4 간의 조인 순서는 지정되지 않습니다.

참고

이러한 의미 체계는 까다로울 수 있습니다. 혼동을 피하기 위해 가능하고 적합한 경우 FROM 절의 마지막에 GETNEXTVAL을 사용하는 것이 좋습니다.

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

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

열 기본 식

열 기본 식은 시퀀스 참조일 수 있습니다. 삽입 문에서 열을 생략하거나 삽입 또는 업데이트 문에서 값을 DEFAULT로 설정하면 행에 대한 새 시퀀스 값이 생성됩니다.

예:

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

시퀀스를 열 기본값으로 사용하는 경우의 이점은 다른 위치에서 시퀀스를 참조할 수 있고 시퀀스를 여러 열과 여러 테이블의 기본값을 사용할 수 있다는 점입니다. 시퀀스의 이름을 열의 기본 식으로 지정한 후 기본값을 사용하여 테이블의 삽입/업데이트 시도를 삭제하면 식별자를 찾을 수 없다는 메시지와 함께 오류가 발생합니다.

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

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

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

    • 기본 키 고유 식별자: id

    • 문자열 열 2개: firstNamelastName

  • 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);
    
  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\',
         }
       ]
     }
    ]'
    );
    
  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
    );
    
  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 |
    +----+------+--------+----------------------+
    

보시다시피, people.idcontact.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     |
 +----+------+--------+----------------------+

시퀀스 변경하기

시퀀스 방향 반전 효과의 이해

다음 예에서는 시퀀스의 방향을 반전하면 어떻게 되는지를 보여줍니다.

또한 시퀀스 값의 사전 계산으로 인해 ALTER SEQUENCE 명령을 실행한 후 시퀀스를 두 번째로 사용한 후에만 ALTER SEQUENCE 명령이 적용되는 것처럼 보일 수도 있다는 점도 보여줍니다.

시퀀스를 만들어 테이블의 열에 대한 기본값으로 사용합니다.

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

j 의 시퀀스 값을 표시합니다.

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

시퀀스 값을 표시합니다. ALTER SEQUENCE 다음에 삽입된 첫 번째 행의 값은 -1 이 아니라 4 입니다. ALTER SEQUENCE 다음에 삽입된 두 번째 행에서는 새 단계 크기를 고려합니다.

SELECT * FROM test_seq_wrap_low ORDER BY i;
+---+---+
| I | J |
|---+---|
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 0 |
+---+---+
맨 위로 이동