SE の雑記

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

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

leave a comment

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 モジュールを開発してデプロイする から確認することができます。

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」というような形で、送信されたメッセージを、テーブルに格納するということができます。

image

 

発生していた問題

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

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

 

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

image

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

 

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

image

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

 

 

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

Written by Masayuki.Ozawa

4月 10th, 2022 at 4:59 pm

Posted in Azure SQL Edge

Tagged with

Leave a Reply

Share via
Copy link
Powered by Social Snap