SnowflakeのSequenceを試してみた

大阪オフィスの玉井です。

Snowflakeには、Snowflake Universityというコンテンツがあり、そこでSnowflakeの学習をすることができます。その中で、個人的に気になったSequenceという機能があるので、ご紹介します。

Sequenceとは

シーケンスは、同時ステートメントを含むセッションおよびステートメント全体で一意の番号を生成するために使用されます。主キーまたは一意の値を必要とする列の値を生成するために使用できます。

シーケンスの使用 — Snowflake Documentationより

シンプルに説明すると、全体で一意となる数値（ID）を生成してくれます。説明だけだと、ユースケースがさっぱり浮かばないと思うので、実際に触ってみましょう。

やってみた

「著者」「本」という２つのテーブル、そしてその2テーブルを関連付けるためのテーブル、これら3つのテーブルを作成します。データベース的には、極めて普通の作業内容ですが、IDの作成にSequenceを使用します。

著者テーブルを作成する

まずは著者テーブルを作成します。

// 新しいデータベースを作成し、それをを使用するように設定
CREATE DATABASE LIBRARY_CARD_CATALOG COMMENT = 'Essentials Lesson 9 ';
USE DATABASE LIBRARY_CARD_CATALOG;

// Authorテーブルを作成
CREATE OR REPLACE TABLE AUTHOR (
   AUTHOR_UID NUMBER 
  ,FIRST_NAME VARCHAR(50)
  ,MIDDLE_NAME VARCHAR(50)
  ,LAST_NAME VARCHAR(50)
);

// Authorテーブルに最初の2人の著者データを格納
INSERT INTO AUTHOR(AUTHOR_UID,FIRST_NAME,MIDDLE_NAME, LAST_NAME) 
Values
(1, 'Fiona', '','Macdonald')
,(2, 'Gian','Paulo','Faleschini');

2人の著者データを追加するところを見てみましょう。最初の値にユニークIDを追加しています。しかし、これは今後データを長期運用していくことを考えると、同じように手動でIDを追加していくのは現実的じゃないですよね。

そこで、今回の主役であるSequenceを使います。これを設定することで、今後追加されるレコードに対して、自動的にユニークIDを付与することができます。

WebUIでSequenceを作成する

Database画面から、Sequenceを付与したいデータベースを開き、Sequenceタブを選択して、Createを選択すると、Sequenceの設定画面が開きます。

Initial Valueはカウントする初期値、Intervalは1カウントでいくつ加算（減算）するかの値を入れます。今回は普通に1スタートで、1ずつ増えるSequenceを作ります。

nextval

唐突ですが、上記のSequenceを作成した後、下記クエリを実行してみます。

SELECT SEQ_AUTHOR_UID.nextval

もう一度同じクエリを実行してみます。

さらにもう一度実行すると、NEXTVALは3になります。

次に下記クエリを実行してみます。

select SEQ_AUTHOR_UID.nextval
,SEQ_AUTHOR_UID.nextval

Sequenceは<Sequenceの名前>.NEXTVALという形でアクセスすることができます。これは、実際に呼び出すたびにカウントされた新しい値が表示されます（そのまま「次の値」という感じですね）。

ちなみにAS句を使うこともできます。

ちなみに公式ドキュメント（本エントリ最下部にリンク記載）曰く、他DBによくあるcurrval関数とは似て非なるもの…とのことです。

Many databases provide a currval sequence reference; however, Snowflake does not. currval in other systems is typically used to create primary-foreign key relationships between tables — a first statement inserts a single row into the fact table using a sequence to create a key. Subsequent statements insert rows into the dimension tables using currval to refer to the fact table’s key.

This pattern is contrary to Snowflake best practices — bulk queries should be preferred over small, single-row queries. The same task can be better accomplished using multi-table INSERT and sequence references in nested subqueries.

著者テーブルでSequenceを使う

著者テーブルのIDをSequenceに代替してみます。

USE DATABASE LIBRARY_CARD_CATALOG;

// Sequenceを3からカウントするように再作成する（Authorテーブルに新しいデータを追加できるように）
CREATE OR REPLACE SEQUENCE "LIBRARY_CARD_CATALOG"."PUBLIC"."SEQ_AUTHOR_UID" 
START 3 
INCREMENT 1 
COMMENT = '行を追加するたびにこれを使用して、AUTHOR_UIDを入力する';

// 残りの著者データを追加し、数字を入力する代わりにnextval関数を使用する
INSERT INTO AUTHOR(AUTHOR_UID,FIRST_NAME,MIDDLE_NAME, LAST_NAME) 
Values
(SEQ_AUTHOR_UID.nextval, 'Laura', 'K','Egendorf')
,(SEQ_AUTHOR_UID.nextval, 'Jan', '','Grover')
,(SEQ_AUTHOR_UID.nextval, 'Jennifer', '','Clapp')
,(SEQ_AUTHOR_UID.nextval, 'Kathleen', '','Petelinsek');

著者テーブルをプレビューしてみます。

Sequenceを3から開始しているのは、最初に2人分のレコードを手動追加しているからです（だから次のデータUIDは3から開始する必要がある）。nextvalを使うことで、いちいち数字を管理しなくても自動でUIDが付与されるようにしています。

本（Book）テーブルを作成する

次に「本（Book）テーブル」を作成します。

USE DATABASE LIBRARY_CARD_CATALOG;

// Bookテーブル用に新しいSequenceを作成
CREATE OR REPLACE SEQUENCE "LIBRARY_CARD_CATALOG"."PUBLIC"."SEQ_BOOK_UID" 
START 1 
INCREMENT 1 
COMMENT = '行を追加するたびにこれを使用して、BOOK_UIDに値を入力する';

// Bookテーブルを作成し、テーブルにデータが追加されるたびに、NEXTVALをデフォルト値として使用する
CREATE OR REPLACE TABLE BOOK
( BOOK_UID NUMBER DEFAULT SEQ_BOOK_UID.nextval
 ,TITLE VARCHAR(50)
 ,YEAR_PUBLISHED NUMBER(4,0)
);

// Bookテーブルにデータを追加
INSERT INTO BOOK(TITLE,YEAR_PUBLISHED)
VALUES
 ('Food',2001)
,('Food',2006)
,('Food',2008)
,('Food',2016)
,('Food',2015);

BookテーブルへのINSERT時、BOOK_UIDフィールドに何も値を指定しません。これは、最初に設定しているSequenceが自動的にUIDを付与してくれるためです。便利ですね。

データは下記の通り。

「Relationshipsテーブル」を作成する

最後に、著者テーブルと本テーブル（「Relationshipsテーブル」）を結ぶためのテーブルを作成します。

USE DATABASE LIBRARY_CARD_CATALOG;

// Relationshipsテーブルを作成
CREATE TABLE BOOK_TO_AUTHOR
(  BOOK_UID NUMBER
  ,AUTHOR_UID NUMBER
);

//既知のリレーションシップのデータを追加する
INSERT INTO BOOK_TO_AUTHOR(BOOK_UID,AUTHOR_UID)
VALUES
 (1,1) // 2001年の本とFiona Macdonaldが紐づく
,(1,2) // 2001年の本とGian Paulo Faleschiniが紐づく
,(2,3) // 2006年の本とLaura K Egendorfが紐づく
,(3,4) // 2008年の本とJan Groverが紐づく
,(4,5) // 2016年の本とJennifer Clappが紐づく
,(5,6);// 2015年の本とKathleen Petelinsekが紐づく

今回作成する「Relationshipsテーブル」は、「多対多テーブル」と呼ぶこともあります。

著者と本、それぞれのテーブルで使用したSequenceを使用します。最初からSequenceでIDを振っているので、それを関連付けるのも、Sequenceで一発（何件データがあろうが）で済むというわけですね。

おわりに

Sequenceをうまく使って、単純作業は無くしていきましょう。

参考資料