この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
本エントリはSnowflake Advent Calender 2019のエントリです。
今回の13日目は、ちょっと細かいですが使ってみて便利だったSQL構文について紹介します。
ALTER TABLE SWAPとは？
一言でいうと、「2つのテーブルを一発で入れ替えるSQL文」です。
データベース運用において、ある集計テーブルを更新する際、古いデータが入っているテーブルとは別に新しい集計データを持つテーブルを作成しておいて、テーブルの入れ替え操作を行う事があります。この方法をとると、データ利用ユーザがテーブルを参照した際にデータが空、もしくは不完全な状態を回避することができます。
基本的には多くのRDBMSで使えるALTER TABLE RENAME文を使い、以下の手順でテーブルの名前を入れ替えることが多いかと思います。
- テーブルA を テーブルA_tmp など、テーブルB以外の名前に一旦リネーム
- テーブルB を テーブルA にリネーム
- テーブルA_tmp を テーブルB にリネーム
しかしこの方法、手数が多く必要で、正直面倒です。そこで、メーカーが固有のSQL文を実装し、テーブル操作の負担を軽減させています。MySQLにはRENAME TABLE文がありますが、それと同じように、Snowflakeには固有のALTER TABLE SWAPがある、という訳です。
テーブルのスワップ(入れ替え)を実施する
では実際にテーブルのスワップを確認します。今回は、Snowflakeのコマンドラインクライアントツール"SnowSQL"から操作しました。
まず、SnowSQLから以下のSQL文を実行し、実験用のテーブルAとテーブルBを作成します。
CREATE TABLE table_a (
id NUMBER(2),
data VARCHAR(12)
);
CREATE TABLE table_b CLONE table_a;
INSERT INTO table_a VALUES (1, 'あいう'), (2, 'えおか');
INSERT INTO table_b VALUES (1, 'ABC'), (2, 'DEF');
SELECT * FROM table_a;
+----+--------+
| ID | DATA |
|----+--------|
| 1 | あいう |
| 2 | えおか |
+----+--------+
SELECT * FROM table_b;
+----+------+
| ID | DATA |
|----+------|
| 1 | ABC |
| 2 | DEF |
+----+------+では、この2つのテーブルを入れ替えます。
ALTER TABLE table_a SWAP WITH table_b;
+----------------------------------+
| status |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+結果を確認します。2テーブルが入れ替わっています。
SELECT * FROM table_a;
+----+------+
| ID | DATA |
|----+------|
| 1 | ABC |
| 2 | DEF |
+----+------+
SELECT * FROM table_b;
+----+--------+
| ID | DATA |
|----+--------|
| 1 | あいう |
| 2 | えおか |
+----+--------+ついでに実験
ALTER TABLE SWAP文は、内部的にはテーブルのメタ情報はそのままに、瞬間的に中のデータ(マイクロパーティション)を入れ替えているとのことですが、これに関連していくつか気になる点を確認します。
ビューとの関連性を確認する
テーブルにはビューが設定されていることが多いのですが、これが入れ替えでどうなるか確認します。
テーブルA、テーブルBのデータを元に戻し、テーブルAにビューを設定したところで再度テーブルの入れ替えを行います。
CREATE VIEW v_table_a AS SELECT data FROM table_a;
SELECT * FROM v_table_a;
+--------+
| DATA |
|--------|
| あいう |
| えおか |
+--------+
ALTER TABLE table_a SWAP WITH table_b;
+----------------------------------+
| status |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
SELECT * FROM v_table_a;
+------+
| DATA |
|------|
| ABC |
| DEF |
+------+ビューの参照先は、メタ情報が変わってないので、引き続きテーブルAを参照しています(=テーブルBには変わりませんでした)。運用上はこちらの方が都合が良いですよね。
テーブル構造が異なる場合は？
テーブル構造、例えばカラム構造が異なるテーブルもスワップできるか、さらにそこにビューが設定されてる場合はどうなる？ということで調べました。
カラム数が異なるテーブルCを作成し、念の為、テーブルAと、テーブルAに設定されているビューを確認します。
CREATE TABLE table_c (
id NUMBER(2),
data VARCHAR(12),
data2 VARCHAR(12)
);
INSERT INTO table_c VALUES (1, 'イロハ', 'ニホヘ'), (2, 'トチリ', 'ヌルヲ');
SELECT * FROM table_c;
+----+--------+--------+
| ID | DATA | DATA2 |
|----+--------+--------|
| 1 | イロハ | ニホヘ |
| 2 | トチリ | ヌルヲ |
+----+--------+--------+
SELECT * FROM table_a;
+----+--------+
| ID | DATA |
|----+--------|
| 1 | あいう |
| 2 | えおか |
+----+--------+
SELECT * FROM v_table_a;
+--------+
| DATA |
|--------|
| あいう |
| えおか |
+--------+さて、ここでテーブルAとテーブルCをスワップさせてみるのですが…。
ALTER TABLE table_a SWAP WITH table_c;
+----------------------------------+
| status |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
SELECT * FROM table_a;
+----+--------+--------+
| ID | DATA | DATA2 |
|----+--------+--------|
| 1 | イロハ | ニホヘ |
| 2 | トチリ | ヌルヲ |
+----+--------+--------+
SELECT * FROM table_c;
+----+--------+
| ID | DATA |
|----+--------|
| 1 | あいう |
| 2 | えおか |
+----+--------+なんと、テーブル構造が異なってもスワップできてしまいました。
このようなテーブルにビューはどう対応するのでしょうか？
SELECT * FROM v_table_a;
+--------+
| DATA |
|--------|
| イロハ |
| トチリ |
+--------+…なるほど、ビューに仕込まれたSQLの条件を満たすのであれば、参照元テーブルの構造が異なっても問題ないようですね。柔軟性高いです。
まとめ
- ALTER TABLE SWAP文は、テーブルのスワップ(入れ替え)を行うSQL文
- メタ情報はそのままに、データだけを入れ替えるので、ビューの参照先が変わることはない
- テーブルの構造が変わってもスワップ可能、ビューも設定SQLが動作できれば利用可能
個人的にはかなり便利に感じた構文です、上記挙動を知っておいて損は無いと思います。
明日の14日目は、たまちゃんによるパフォーマンス系のエントリをお伝えする予定です。お楽しみに！
24
