Azure SQL Edge でストリーミングによるデータ取り込みを行う場合の注意点 at SE の雑記

SQL Server は Azure IoT Edge 上で容易に展開することができる Azure SQL Edge が提供されています。

この SQL Edge は、x64 だけでなく、ARM64 で動作させることができるため、ARM の CPU が使用されているデバイス上で動作させることができる SQL Server として利用することができます。

私は Raspberry Pi 4 を IoT Edge デバイスとして登録をして、Azure から SQL Edge の展開をしていますが、Azure SQL Edge はコンテナーイメージとして公開が行われていますので、コンテナーが動作する環境であれば、様々な環境に展開することが可能です。

Azure SQL Edge の検証環境としては、

を使用して、温度センサーのデータを SQL Edge にストリーミングで取り込むようなシナリオができる環境を準備しています。

センサーデータをストリーミングで取り込んでいたところ、ある条件に当てはまると、センサーデータを取り込んでいるテーブルが肥大化するという事象が発生しそうでしたので、情報を残しておきたいと思います。

今回は検証用の物理環境を準備していますが、Azure SQL Edge は x64 のイメージも提供されていますので、IoT 向けの物理環境が用意できなくても、Ubuntu 仮想マシン上で Azure IoT Edge を実行するで、基盤となる環境を用意し、IoT Edge Module として、Simulated Temperature Sensor を展開することで、仮想環境上でも動作の確認をすることができます。

クイックスタート:初めての IoT Edge モジュールを Linux 仮想デバイスにデプロイするが参考になり、温度センサーのシミュレーターについては SimulatedTemperatureSensor で C# のソースコードが公開されています。
Python を使用した場合の温度センサーのシミュレーターについてはチュートリアル: Linux コンテナーを使用して Python IoT Edge モジュールを開発してデプロイするから確認することができます。

Contents

1 SQL Server ベースの環境でセンサーデータのストリーミング処理の参考情報
2 発生していた問題
3 ストリーミングジョブのデータ投入方法
4 問題の回避方法
- 4.1 追記

SQL Server ベースの環境でセンサーデータのストリーミング処理の参考情報

SQL Server ベースの環境でセンサーデータのストリーミング処理を実施する場合にはいくつかのパターンがあるかと思います。

シンプルな構成は、Azure SQL Edge を使用したものです。
Azure SQL Edge でのデータストリーミングに記載されていますが、Azue SQL Edge には、T-SQL ストリーミングという、T-SQL でストリーミングのジョブを定義して、ストリーミングデータを処理することができます。

ストリーム入力 : Edge ハブ / Kafka
ストリーム出力 : Edge ハブ / SQL

に対応しており、Edge ハブに送信されたデータを、データベース内のテーブルに書き込むことができます。

Azure SQL Edge は、Azure Strean Analytics on IoT Edge に近い機能が含まれているモジュールとなると思います。

今回は触れていませんが、次の機能を組み合わせることでも IoT Edge 上で SQL Server ベースの環境のセンサーデータのストリーミング処理を実現することはできるのではないでしょうか。

SQL Edge のストリーミングの設定については、次のドキュメントが参考になります。

SQL Edge でストリーミングの設定を行うと、SQL Edge モジュール上に入力 (シンク) を作成することができ、次のようなルーティング設定でメッセージを SQL Edge で定義したストリーミングの入力に連携することができます。

"SQLEde": "FROM /messages/* INTO BrokeredEndpoint(\"/modules/AzureSQLEdge/inputs/TemperatureMeasurements\")"

送信されたメッセージは、Azure SQL Edge のストリーミングジョブとして処理をすることができ「Select * INTO TemperatureMeasurementsOutput from TemperatureMeasurementsInput」というような形で、送信されたメッセージを、テーブルに格納するということができます。

発生していた問題

今回、センダーデータの取り込みを次のようなテーブルに取り込んでいました。

CREATE TABLE [dbo].[TemperatureMeasurements_TBL](
	[collectdate] [datetime2](6) NULL,
	[room_temperature] [numeric](10, 7) NULL,
	[room_humidity] [numeric](10, 7) NULL
) ON [PRIMARY]
GO

CREATE INDEX [Index_TemperatureMeasurements_TBL] ON [dbo].[TemperatureMeasurements_TBL]
(
	[collectdate] ASC
) ON [PRIMARY]
GO

ヒープのテーブルに、非クラスター化インデックスを設定しているだけのシンプルなテーブルに温度と湿度を格納していました。

このような構造になっているテーブルに対してセンサーデータの取り込みを行っていると「データ取り込み時に大量の未使用の領域が確保され、テーブルサイズが肥大化する」という現象が発生していました。

次のようなクエリを実行して、テーブルの格納状況を確認してみます。


select
    pa.extent_page_id
    , pa.allocated_page_page_id
    , pa.is_allocated
    , pi.page_type_desc
    , pi.slot_count
    , pi.reserved_bytes
    , pi.free_bytes
from
    sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('TemperatureMeasurements_TBL'), NULL, 0, 'DETAILED') AS pa
    OUTER APPLY sys.dm_db_page_info(DB_ID(), 1, pa.allocated_page_page_id , 'DETAILED') AS pi

クエリを実行すると、データページの格納状況が次のようになっていました。

1 件のデータで 1 エクステント (8 ページ) が使用されており、これが連続しているような状態となり、大量の未使用の領域が存在している状態となり、これによりテーブルサイズが肥大化するという状態が発生していました。

ストリーミングジョブのデータ投入方法

Azure SQL Edge にストリーミングジョブを作成して、テーブルにデータを投入する場合、内部的には次のようなクエリでデータ投入が行われているようでした。

insert bulk [dbo].[TemperatureMeasurements_TBL] ([collectdate] DateTime2(6), [room_temperature] Decimal(10,7), [room_humidity] Decimal(10,7)) with (TABLOCK, CHECK_CONSTRAINTS, FIRE_TRIGGERS)

ストリーミングジョブでは、INSERT BULK ステートメントを使用して、受信したメッセージに対して、Bulk Insert を実行しているようです。

次のような処理と等価なイメージでしょうか。

$dt = New-Object System.Data.DataTable
$dt.Columns.Add((New-Object System.Data.DataColumn("collectdate", [System.DateTime])))
$dt.Columns.Add((New-Object System.Data.DataColumn("room_temperature", [System.Decimal])))
$dt.Columns.Add((New-Object System.Data.DataColumn("room_humidity", [System.Decimal])))

$r = $dt.NewRow()
$r["collectdate"] = [datetime](Get-Date)
$r["room_temperature"] = 29
$r["room_humidity"] = 5
$dt.Rows.Add($r)


1..10 | %{
	$constring = "Server=localhost;Integrated Security=true;database=StreamingDB"
	$bc = New-Object System.Data.SqlClient.SqlBulkCopy($constring, ([System.Data.SqlClient.SqlBulkCopyOptions]::TableLock -bor [System.Data.SqlClient.SqlBulkCopyOptions]::CheckConstraints -bor [System.Data.SqlClient.SqlBulkCopyOptions]::FireTriggers))
	$bc.DestinationTableName = "[TemperatureMeasurements_TBL2]"
	$bc.WriteToServer($dt)
}

問題の回避方法

今回発生している問題ですが、SQL Server の Bulk Insert の高速挿入のような動作となっているように見えるのですが、TF692 を設定しても効果はありませんでした。

問題が発生する条件としては、

ヒープ構造のテーブル
Bulk Insert で TABLOCK が使用されている

に合致した場合に、このようなデータ投入が発生するようなのですが、ストリーミングジョブの設定を行う sp_create_streaming_job では TABLOCK を制御するためのオプションがなさそうでした。

ということで、テーブルの構造をヒープからクラスター化インデックスに変更します。


DROP INDEX [Index_TemperatureMeasurements_TBL] ON [dbo].[TemperatureMeasurements_TBL]
GO

CREATE CLUSTERED INDEX [Index_TemperatureMeasurements_TBL] ON [dbo].[TemperatureMeasurements_TBL]
(
	[collectdate] ASC
) ON [PRIMARY]
GO

ヒープではなく、クラスター化インデックスにした場合、次のように最後のページにデータが追加される動作となりました。

ヒープの場合は定期的なテーブルの再構築をすることで領域の肥大化を解消することができますが、今回のスキーマであれば、ヒープの必要はありませんので、クラスター化インデックスで対応することにしました。

追記

この問題について SR で確認をしたところ、設定ベースで回避することはできず、クラスター化インデックスを作成するか、次のようなトリガーを追加して、Bulk Insert を通常の Insert に変換するしかないようでした。

CREATE TRIGGER [dbo].[BulkInsertToSingleInsert]
ON [dbo].[TemperatureMeasurements_TBL_Heap] 
INSTEAD OF INSERT AS  
BEGIN
    INSERT INTO TemperatureMeasurements_TBL_Heap ( 
       collectdate, room_temperature, room_humidity
    )
SELECT
        collectdate, room_temperature, room_humidity
    FROM
        inserted i;
END
GO

ALTER TABLE [dbo].[TemperatureMeasurements_TBL_Heap] ENABLE TRIGGER [BulkInsertToSingleInsert]
GO

Bulk Insert の効率的な実施のためにヒープを使用するケースがありますが、細かな Bulk Insert が実行されるシナリオに関しては、クラスター化インデックスのほうが制御がしやすいこともあるようですね。

月	火	水	木	金	土	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30

SE の雑記