SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

パーティションのスイッチを使用した Columnstore インデックスへのデータ追加

leave a comment

先日開催されていた MSC 2011 の裏で開催されていたエンタープライズ データベース アカデミーで SQLCAT 多田さんのセッションで方法について紹介があり、興味があったので調べてみました。

パーティショニングはあまり詳しくないので方法として間違っているかもしれないですが、その点はあらかじめご了承ください。

■パーティションのスイッチを使用した Columnstore インデックスへのデータ追加


Columnstoreインデックスが設定されているテーブルに対してはデータの追加の更新をすることができません。
image

そのため、Columnstore インデックスが設定されているテーブルに対して直接データを更新したい場合は、

  1. Columnstore インデックスの削除
  2. データの追加 / 更新
  3. Columnstore インデックスの作成

という手順が必要になってきます。

Columnstore インデックスが設定されているテーブルは通常、集計やレポートのために使用する大容量のデータが格納されているものになるかと思います。

データの更新が必要になった場合、夜間に上記のような作業をして、データのメンテナンスをするというケースが必要になることもあるかもしれないですね。

Columnstore インデックスが設定されているテーブルへの更新 (UPDATE) はできないのですが、データの追加 (INSERT 相当) に関してはパーティションのスイッチをすることでパーティション単位で Columnstore インデックスが設定されているテーブルへデータを追加することが可能です。

今回は 疑似個人情報データ生成サービス を使用させていただき作成した疑似個人情報を使用してスイッチのテストをしてみたいと思います。

このサービスではデータに特定の範囲の乱数を設定することが可能です。
そこで、疑似個人情報を乱数を使用してハッシュパーティショニングをしてみます。
作成されたデータの 1~5の乱数をハッシュとして設定しています。

今回は、

  • PersonalInfo
  • PersonalInfo_Temp

という 2 種類のテーブルを作成しています。

これらのテーブルを以下の設定でパーティショニングしています。
image

PersonalInfo にはハッシュが 1 ~ 4 のデータを RANGE RIGHT でパーティショニングして各ファイルグループに格納しています。
ワークテーブル (更新系のデータを格納) である PersonalInfo_Temp にはパーティションは設定せずにハッシュが 5 のデータをスイッチするファイルグループに格納しています。

PersonalInfo の各パーティションのデータの格納状況を以下のクエリを実行して確認してみます。
# 今回のテーブルはヒープ + Columnstore インデックスの構成です。

SELECT
    OBJECT_NAME(si.object_id) AS [Object Name]
    ,COALESCE(si.name, N’Heap’) AS [Index Name]
    ,sp.data_compression_desc
    ,ps.name AS [PartitionSchme Name]
    ,pf.name AS [PartitionFunction Name]
    ,ds.name AS [FileGroup]
    ,dds.destination_id AS [Partition No]
    ,pr.value
    ,COALESCE(sp.rows, 0) AS [rows]
FROM
    sys.destination_data_spaces AS dds
    LEFT JOIN
        sys.partition_schemes AS ps
    ON
        dds.partition_scheme_id = ps.data_space_id
    LEFT JOIN
        sys.indexes AS si
    ON
        ps.data_space_id = si.data_space_id
    LEFT JOIN
        sys.data_spaces AS ds
    ON
        dds.data_space_id = ds.data_space_id
    LEFT JOIN
        sys.partitions AS sp
    ON
        si.object_id = sp.object_id
        AND
        si.index_id = sp.index_id
        AND
        dds.destination_id = sp.partition_number
    LEFT JOIN
        sys.partition_functions AS pf
    ON
        ps.function_id = pf.function_id
    LEFT JOIN
        sys.partition_range_values AS pr
    ON
        sp.partition_number = pr.boundary_id
        AND
        ps.function_id = pr.function_id
ORDER BY
    si.index_id
    ,dds.destination_id

image

PARITION1~4 にデータが分散されて格納されているのが確認できますね。

このような環境を使用していると Hash が 5 のデータを Columnstore インデックスが設定されている PersonalInfo テーブルにパーティションをスイッチすることで追加ができます。

それでは以下のようにパーティションをスイッチしてみたいと思います。
# 事前に必要となるパーティションは作成済みです。
image

現状、PersonalInfo には PARTITION5 のファイルグループを使用したパーティションは存在していませんので、スキーマとファンクションを変更してワークテーブルがスイッチできる領域を作成します。
# スイッチするためには同じファイルグループを使用している必要がありますので。

ALTER PARTITION SCHEME PersonalInfo_PartitionScheme
NEXT USED [PARTITION5]

これで、PersonalInfo のパーティションが以下のように変更されました。
image

パーティションのスキーマが変更され、ファイルグループが追加されます。
先ほど使用したクエリで、パーティションの状態を確認してみます。
image

この状態では境界値が設定されていない状態ですので、SPLIT をして境界値を設定します。

ALTER PARTITION FUNCTION PersonalInfo_PartitionFunc()
SPLIT RANGE (5)

このクエリを実行しようとすると以下のエラーとなります。
image

境界値として 5 を設定する場合、今まで使用していた PARTITION4 (最後のファイルグループ) を分割することになると思うのですがColumnstore インデックスが作成されている場合、ファイルグループ追加前の最後のパーティションに値が入っていると SPLIT ができないようです。
青い色がついているのがデータが格納されているパーティションになります。
# Columnstore インデックスがない場合は、上述のクエリでパーティションの設定を変更することができます。
image

Columnstore インデックスを使用する場合、常に空きを一つ確保しておく必要があるようですね。
そのため、パーティション #4 に値を格納する前の状態で以下のパーティション構成を設定しておく必要があるようです。
image

この後、パーティション #5 にはデータをスイッチしますのでスイッチ前にパーティション #6 を作成しておく必要があります。
image

そのため、今回の場合はスライディングウインドウ (パーティションスイッチによるデータの移動) をする前にはこの状態を起点とし、
image

以下のクエリを実行して、パーティション #6 を作成してからスイッチをすることになりそうです。

ALTER PARTITION SCHEME PersonalInfo_PartitionScheme
NEXT USED [PARTITION6]

ALTER PARTITION FUNCTION PersonalInfo_PartitionFunc()
SPLIT RANGE (6)

image

この状態であれば先ほどのエラーは発生しません。

後はパーティションをスイッチすると Columnstore インデックスが作成されているテーブルにデータを追加することが可能です。
image

ALTER TABLE PersonalInfo_Temp
SWITCH TO Personalinfo PARTITION 5

ただし、スイッチ時にはスイッチ元のテーブルにも Columnstore インデックスを作成しておく必要があります。
image

そのため、スイッチの直前には更新系のテーブルにもデータの変更はかけられなくなります。

また、インデックスの状態だけでなく、スイッチ元のテーブルに対象のパーティションのデータのみが含まれているという保証をする必要があります。
image
データの保障に関しては以下のようなクエリを実行し、スイッチ元のテーブルのデータには特定のデータのみが含まれることを保証します。

ALTER TABLE PersonalInfo_Temp
ADD CONSTRAINT PersonalInfo_Temp_ck1 CHECK(Hash = 5)

ここまで準備ができているとスイッチすることが可能です。
image

先ほどまでは PARTITION5 のデータは 0 件でしたがスイッチをすることで Columnstore インデックスが設定されている状態でデータの追加ができていることが確認できますね。

この手のデータマネージメントは本当の奥が深いですね。

Share

Written by Masayuki.Ozawa

10月 12th, 2011 at 8:27 pm

Posted in SQL Server

Tagged with , ,

Leave a Reply