先日開催されていた MSC 2011 の裏で開催されていたエンタープライズ データベース アカデミーで SQLCAT 多田さんのセッションで方法について紹介があり、興味があったので調べてみました。
パーティショニングはあまり詳しくないので方法として間違っているかもしれないですが、その点はあらかじめご了承ください。
■パーティションのスイッチを使用した Columnstore インデックスへのデータ追加
Columnstoreインデックスが設定されているテーブルに対してはデータの追加の更新をすることができません。
そのため、Columnstore インデックスが設定されているテーブルに対して直接データを更新したい場合は、
- Columnstore インデックスの削除
- データの追加 / 更新
- Columnstore インデックスの作成
という手順が必要になってきます。
Columnstore インデックスが設定されているテーブルは通常、集計やレポートのために使用する大容量のデータが格納されているものになるかと思います。
データの更新が必要になった場合、夜間に上記のような作業をして、データのメンテナンスをするというケースが必要になることもあるかもしれないですね。
Columnstore インデックスが設定されているテーブルへの更新 (UPDATE) はできないのですが、データの追加 (INSERT 相当) に関してはパーティションのスイッチをすることでパーティション単位で Columnstore インデックスが設定されているテーブルへデータを追加することが可能です。
今回は 疑似個人情報データ生成サービス を使用させていただき作成した疑似個人情報を使用してスイッチのテストをしてみたいと思います。
このサービスではデータに特定の範囲の乱数を設定することが可能です。
そこで、疑似個人情報を乱数を使用してハッシュパーティショニングをしてみます。
作成されたデータの 1~5の乱数をハッシュとして設定しています。
今回は、
- PersonalInfo
- PersonalInfo_Temp
という 2 種類のテーブルを作成しています。
これらのテーブルを以下の設定でパーティショニングしています。
PersonalInfo にはハッシュが 1 ~ 4 のデータを RANGE RIGHT でパーティショニングして各ファイルグループに格納しています。
ワークテーブル (更新系のデータを格納) である PersonalInfo_Temp にはパーティションは設定せずにハッシュが 5 のデータをスイッチするファイルグループに格納しています。
PersonalInfo の各パーティションのデータの格納状況を以下のクエリを実行して確認してみます。
# 今回のテーブルはヒープ + Columnstore インデックスの構成です。
SELECT |
PARITION1~4 にデータが分散されて格納されているのが確認できますね。
このような環境を使用していると Hash が 5 のデータを Columnstore インデックスが設定されている PersonalInfo テーブルにパーティションをスイッチすることで追加ができます。
それでは以下のようにパーティションをスイッチしてみたいと思います。
# 事前に必要となるパーティションは作成済みです。
現状、PersonalInfo には PARTITION5 のファイルグループを使用したパーティションは存在していませんので、スキーマとファンクションを変更してワークテーブルがスイッチできる領域を作成します。
# スイッチするためには同じファイルグループを使用している必要がありますので。
ALTER PARTITION SCHEME PersonalInfo_PartitionScheme |
これで、PersonalInfo のパーティションが以下のように変更されました。
パーティションのスキーマが変更され、ファイルグループが追加されます。
先ほど使用したクエリで、パーティションの状態を確認してみます。
この状態では境界値が設定されていない状態ですので、SPLIT をして境界値を設定します。
ALTER PARTITION FUNCTION PersonalInfo_PartitionFunc() |
境界値として 5 を設定する場合、今まで使用していた PARTITION4 (最後のファイルグループ) を分割することになると思うのですがColumnstore インデックスが作成されている場合、ファイルグループ追加前の最後のパーティションに値が入っていると SPLIT ができないようです。
青い色がついているのがデータが格納されているパーティションになります。
# Columnstore インデックスがない場合は、上述のクエリでパーティションの設定を変更することができます。
Columnstore インデックスを使用する場合、常に空きを一つ確保しておく必要があるようですね。
そのため、パーティション #4 に値を格納する前の状態で以下のパーティション構成を設定しておく必要があるようです。
この後、パーティション #5 にはデータをスイッチしますのでスイッチ前にパーティション #6 を作成しておく必要があります。
そのため、今回の場合はスライディングウインドウ (パーティションスイッチによるデータの移動) をする前にはこの状態を起点とし、
以下のクエリを実行して、パーティション #6 を作成してからスイッチをすることになりそうです。
ALTER PARTITION SCHEME PersonalInfo_PartitionScheme ALTER PARTITION FUNCTION PersonalInfo_PartitionFunc() |
この状態であれば先ほどのエラーは発生しません。
後はパーティションをスイッチすると Columnstore インデックスが作成されているテーブルにデータを追加することが可能です。
ALTER TABLE PersonalInfo_Temp |
ただし、スイッチ時にはスイッチ元のテーブルにも Columnstore インデックスを作成しておく必要があります。
そのため、スイッチの直前には更新系のテーブルにもデータの変更はかけられなくなります。
また、インデックスの状態だけでなく、スイッチ元のテーブルに対象のパーティションのデータのみが含まれているという保証をする必要があります。
データの保障に関しては以下のようなクエリを実行し、スイッチ元のテーブルのデータには特定のデータのみが含まれることを保証します。
ALTER TABLE PersonalInfo_Temp |
先ほどまでは PARTITION5 のデータは 0 件でしたがスイッチをすることで Columnstore インデックスが設定されている状態でデータの追加ができていることが確認できますね。
この手のデータマネージメントは本当の奥が深いですね。