この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
※本エントリは、Snowflakeをより使いこなそう！ Advent Calendar 2021の6日目の記事となります。
さがらです。
Snowflakeのタイムトラベル機能で、データベースやスキーマなど上位オブジェクトで設定された内容がテーブルなど下位オブジェクトにどう影響を及ぼすか、確かめてみました。
振り返り：タイムトラベル機能って？
テーブル、スキーマ、データベース、について定義された期間の間の任意の時点で履歴データにアクセスできる機能です。対象のデータが変更されていようと削除されていようと、コマンド１つでアクセスして復元可能です。
Snowflakeをより使いこなそう！ Advent Calendar 2021でも2日目の記事でタイムトラベルでよく使いそうなコマンドをまとめていますので、こちらも是非ご覧ください。
本エントリで書くこと
上述の記事を書く中で、気になったことがありました。
それは、データベースなどの上位オブジェクトでタイムトラベルの設定をすると、その下位オブジェクトにあたるスキーマとテーブルなどにどう影響が及ぶのかということです。
より具体的には、以下3つのパターンでどういう影響があるのかを本エントリでまとめていきたいと思います。
- その１：テーブルに設定したあと、スキーマの値を変更する
- テーブルの値に影響があるか？
- その２：テーブルに設定したあと、データベースの値を変更する
- テーブルの値に影響があるか？
- その３：スキーマに設定したあと、データベースの値を変更する
- スキーマの値に影響があるか？
前提
タイムトラベルのデフォルト値を決めるパラメータであるDATA_RETENTION_TIME_IN_DAYSは、デフォルト値そのままの1とします。
この状態で、データベース・スキーマ・テーブルを以下のコマンドで作成します。（スキーマは自動で作られるPUBLICスキーマを使用します。）
※検証３つとも、それぞれ行う前にこのコマンドを一通り実行して初期値の状態から検証します。
use role sysadmin;
create or replace database timetravel_test;
create or replace table sample_table (no int);各検証コマンドを実施後、各オブジェクトのretention_timeの内容を見て、値に変化があるかどうかを確認します。
その１：テーブルに設定したあと、スキーマの値を変更する
その１－１：テーブルに長期間の設定をしたあと、スキーマにはより短期間の設定をする
sample_tableテーブルの保持期間を「90日」に変更してみます。
この状態で、スキーマの保持期間を「30日」に変更してみます。 その後でテーブルの保持期間を見ると、値は変わらず90日のままでした。
その１－２：テーブルに短期間の設定をしたあと、スキーマにはより長期間の設定をする
sample_tableテーブルの保持期間を「10日」に変更してみます。
この状態で、スキーマの保持期間を「30日」に変更してみます。 その後でテーブルの保持期間を見ると、値は変わらず10日のままでした。
その２：テーブルに設定したあと、データベースの値を変更する
その２－１：テーブルに長期間の設定をしたあと、データベースにはより短期間の設定をする
sample_tableテーブルの保持期間を「90日」に変更してみます。
この状態で、データベースの保持期間を「30日」に変更してみます。 その後でテーブルの保持期間を見ると、値は変わらず90日のままでした。
その２－２：テーブルに短期間の設定をしたあと、データベースにはより長期間の設定をする
sample_tableテーブルの保持期間を「10日」に変更してみます。
この状態で、データベースの保持期間を「30日」に変更してみます。 その後でテーブルの保持期間を見ると、値は変わらず10日のままでした。
その３：スキーマに設定したあと、データベースの値を変更する
その３－１：スキーマに長期間の設定をしたあと、データベースにはより短期間の設定をする
PUBLICスキーマの保持期間を「90日」に変更してみます。
この状態で、データベースの保持期間を「30日」に変更してみます。 その後でスキーマの保持期間を見ると、値は変わらず90日のままでした。
その３－２：スキーマに短期間の設定をしたあと、データベースにはより長期間の設定をする
PUBLICスキーマの保持期間を「10日」に変更してみます。
この状態で、データベースの保持期間を「30日」に変更してみます。 その後でスキーマの保持期間を見ると、値は変わらず10日のままでした。
なのですが、その３－１とその３－２の検証の画像を見て気づいた方もしるかもしれませんが、デフォルト値から変えていないINFORMATION_SCHEMAはこの操作によって値が変わっているのです。
ということで追加検証「その４」として、データベースとテーブルを作成した後、いきなり最上位オブジェクトであるデータベースの設定値だけ変更して、スキーマとテーブルにどう変更があるかを見てみます。
その４：データベースの設定値だけ変更する
下図のコマンドを全て実行して、データベースとテーブルを作り直します。デフォルト値なのでretention_timeはすべてのオブジェクトで「１」です。
この状態で、データベースの保持期間を「30日」に変更してみます。 その後でスキーマの保持期間を見ると、値は変わり、1日から30日となりました。
加えてテーブルの保持期間を見ると、値は変わり、1日から30日となりました。
なんと、個別にコマンドを実行してretention_timeを変更していない場合、データベースなど上位オブジェクトに設定した値は、下位オブジェクトであるスキーマとテーブルも変更されました！
念のため、追加検証「その５」データベースとテーブルを作成した後、スキーマの値だけ変更してテーブルの値が変化するかも確認しておきます。
検証その５：スキーマの設定値だけ変更する
下図のコマンドを全て実行して、データベースとテーブルを作り直します。デフォルト値なのでretention_timeはすべてのオブジェクトで「１」です。
この状態で、スキーマの保持期間を「30日」に変更してみます。 その後でテーブルの保持期間を見ると、値は変わり、1日から30日となりました。
やはり、個別にコマンドを実行してretention_timeを変更していない場合、下位オブジェクトは影響を受けるようですね。
まとめ
今回の検証を通して、以下のことがわかりました。
- 事前に個別にコマンドを実行して
retention_timeを変更していない場合、上位オブジェクトでの変更は下位オブジェクトにも反映される- データベースへの変更値が、スキーマとテーブルにも反映される
- スキーマへの変更値が、テーブルにも反映される
- 一方で、個別にコマンドを実行して
retention_timeを変更している場合、上位オブジェクトでの変更は下位オブジェクトに反映されない- これは、短期間⇒長期間への値変更、長期間⇒短期間への値変更、どちらの場合でも変わらない
検証後の感想
正直、私もこんな結果になるとは思っていませんでした…
「これは何をしても下位オブジェクトの値変わらなそうだなー」と思って検証していたら、alterコマンドで設定を変更していないINFORMATION_SCHEMAの値が変わっていることに気づいて「あれ！？」となってしまいました。笑
この辺りはまさにやってみないと分からないことでしたので、私自身もとても勉強になる検証でしたね。
次回
Snowflakeをより使いこなそう！ Advent Calendar 2021、次回の7日目では、「Snowflakeでテストデータの生成に便利なGENERATORを試してみた」というタイトルで執筆します。お楽しみに！
15
