SE の雑記

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

Elastic Scale API を PowerShell から使用してみる

leave a comment

先日、Azure SQL Database Elastic Scale プレビューを更新 /  最新の Elastic Scale クライアント ライブラリへのアップグレード で Elastic Scale API の新しいバージョンが提供されていたのは知っていたのですが、私はコード書くのが苦手で えろす師匠 と えろす師匠の放ったオオアリクイ  によって、この業界から抹殺される寸前なので、ちょっと頑張って、PowerShell から Elastic Scale API を使ってみました。

Elastic Scale は Azure SQL Database の Elastic Scale の概要 で日本語のドキュメントが公開されていますので、こちらから情報を確認するとよいかなと。

Azure SQL Database Elastic Scale Client Library 使い方簡易まとめ もとても参考になります。

PowerShell から Elastic Scale API を使用するときに参考となる情報は、Azure SQL Database Elastic Scale: Shard Elasticity が参考になります。

 

■環境の構築

上記の PowerShell のスクリプトでは、SQL Database の DB 作成も含まれていますが、今回は手動で作成しています。

今回は以下の DB を作成しています。

CREATE DATABASE ShardMapManagerDb (SERVICE_OBJECTIVE='basic')
CREATE DATABASE ShardDB01 (SERVICE_OBJECTIVE='basic')
CREATE DATABASE ShardDB02 (SERVICE_OBJECTIVE='basic')

 

ShardMapManagerDb を GSM (Global Shad Map) のデータベースとして、使用します。

# Elastic Scale の用語については、Elastic Scale 用語集 が参考になります。

ShardDB01 / 02 が実際にデータを格納しているデータベースとなります。

ShardDB01 には、以下のクエリを実行して、1～5 のデータが格納されている状態にします。

CREATE TABLE ShardTable(Col1 int)
INSERT INTO ShardTable VALUES(1), (2), (3), (4), (5)

ShardDB02 には、以下のクエリを実行して、11～15 のデータが格納されている状態にします。

CREATE TABLE ShardTable(Col1 int)
INSERT INTO ShardTable VALUES(11), (12), (13), (14), (15)

 

これらのデータを使用して、Elastic Scale を使用します。

 

最初に GSM を作成するために以下のスクリプトを実行します。

#Elastic Scale の DLL をロードできる場所に配置
Add-Type -path C:\ElasticScale\Microsoft.Azure.SqlDatabase.ElasticScale.Client.dll
$SqlServerName = "<サーバー名>.database.windows.net"
$ShardMapManagerDb = "ShardMapManagerDb"
$ShardDB01 = "ShardDB01"
$ShardDB02 = "ShardDB02"
$UserName = "<ログイン>"
$Password = "<パスワード>"
$RangeShardMapName = "ElasticScaleShardMap" #シャード名
$KeyType = [int] #int をシャードキーとして使用
# ShardMapManager の作成
[Type]$ShardMapManagementFactoryType = [Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.ShardMapManagerFactory]
$SmmConnectionString = "Server=$SqlServerName; Initial Catalog=$ShardMapManagerDb; User ID=$UserName; Password=$Password"
$CreateMode = [Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.ShardMapManagerCreateMode]::ReplaceExisting
$ShardMapManager = $ShardMapManagementFactoryType::CreateSqlShardMapManager($SmmConnectionString, $CreateMode)

 

これにより、GSM にテーブル等のオブジェクトが作成されます。

作成されたオブジェクトを削除したい場合などは以下のクエリを実行してオブジェクト削除をするとよいかと。

# 管理用の API があるので、本来は シャード マップの管理 の内容で削除したほうがよいかと思いますが。 

SELECT
CASE type
	WHEN 'U' THEN
		'DROP TABLE '
	WHEN 'P' THEN
		'DROP PROCEDURE '
	WHEN 'FN' THEN
		'DROP FUNCTION '
END + SCHEMA_NAME(schema_id) + '.' + name
FROM sys.objects
WHERE
schema_id = schema_id('__ShardManagement')
AND
type IN('U', 'P', 'FN')
ORDER BY type ASC

 

この段階では、「__ShardManagement.ShardMapManagerGlobal」にバージョンの情報が格納された状態となります。

image

次に int を使用した範囲シャードを使うことを以下のスクリプトで定義します。

[Type]$ShardMapManagerType = [Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.ShardMapManager]
$CreateRangeShardMapMethodGeneric = $ShardMapManagerType.GetMethod("CreateRangeShardMap")
$CreateRangeShardMapMethodTyped = $CreateRangeShardMapMethodGeneric.MakeGenericMethod($KeyType)
$params = @($RangeShardMapName)
$RangeShardMap = $CreateRangeShardMapMethodTyped.Invoke($ShardMapManager, $params)

 

これにより「__ShardManagement.ShardMapsGlobal」に範囲シャードを使用することが定義されます。

image

 

今回は使用していませんが、シャードに参照テーブル等の定義を設定する場合は以下のようなスクリプトを実行する必要があります。

# 今回はマージやスプリットを使用しないので特に定義していません。

# シャードに参照テーブルの情報を設定する場合は以下を設定する
# $SchemaInfo = New-Object Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.Schema.SchemaInfo
# $SchemaInfo.Add((New-Object Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.Schema.ShardedTableInfo("ShardTable","ShardKey")))
# $SchemaInfo.Add((New-Object Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.Schema.ReferenceTableInfo("ReferenceTable")))
# $ShardMapManager.GetSchemaInfoCollection().Add($RangeShardMapName, $SchemaInfo)

 

 

次にシャードを追加します。

# Shard の追加
$ShardLocation = New-Object Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.ShardLocation($SqlServerName, $ShardDB01)
[Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.Shard]$ShardReference = $null
$ShardMapReturn = $RangeShardMap.CreateShard($ShardLocation)

 

これにより、シャードに含めるデータベース (サーバー) が「__ShardManagement.ShardsGlobal」に定義されます。

image

このタイミングでシャードのデータベースに LSM (Local Sard Map) が作成されます。

image

ここまで実行すると LSM 「__ShardManagement.ShardMapsLocal」「__ShardManagement.ShardsLocal」「__ShardManagement.ShardMapManagerLocal」に情報が格納された状態となります。

# 「__ShardManagement.ShardMappingsLocal」以外のテーブルに情報が格納されています。

最後にどの範囲でシャードをするかを指定するため、以下のスクリプトを実行します。

$RangeLow = 1
$RangeHigh = 10
$InputShard = $RangeShardMap.GetShard($ShardLocation)
$InputRange = New-Object Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.Range[$KeyType]($RangeLow, $RangeHigh)
$RangeShardMap.CreateRangeMapping($InputRange, $InputShard)

 

これにより、「__ShardManagement.ShardMappingsGlobal」「__ShardManagement.ShardMappingsLocal」にデータが登録されます。

image

以下を実行して、DB02 の情報を GSM に追加し、LSM を作成します。

# Shard の追加
$ShardLocation = New-Object Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.ShardLocation($SqlServerName, $ShardDB02)
[Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.Shard]$ShardReference = $null
$ShardMapReturn = $RangeShardMap.CreateShard($ShardLocation)
# RangeMapping の追加
$RangeLow = 11
$RangeHigh = 20
$InputShard = $RangeShardMap.GetShard($ShardLocation)
$InputRange = New-Object Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.Range[$KeyType]($RangeLow, $RangeHigh)
$RangeShardMap.CreateRangeMapping($InputRange, $InputShard)

 

以下を実行して、シャードの情報を確認します。

$RangeShardMap.GetMappings()
$RangeShardMap.GetShards()

 

image

 

■クエリの実行

それでは、クエリを実行してみたいと思います。

まずはマルチシャードクエリから。

# 接続文字列を String Builder 使ているものがあるのは、備忘録としてです。

# Multi shard Query
$SmmConnectionString = "Server=$SqlServerName; Initial Catalog=$ShardMapManagerDb; User ID=$UserName; Password=$Password"
[Type]$ShardMapManagementFactoryType = [Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.ShardMapManagerFactory]
$LoadPolicy = [Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.ShardMapManagerLoadPolicy]::Lazy
[Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.ShardMapManager]$ShardMapManager = $null
$ShardMapManagementFactoryType::TryGetSqlShardMapManager($SmmConnectionString, $LoadPolicy, [ref]$ShardMapManager)
<#
$LoadPolicy = [Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.ShardMapManagerLoadPolicy]::Lazy
[Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.ShardMapManager]$ShardMapManager = $null
$ShardMapManager = $ShardMapManagementFactoryType::GetSqlShardMapManager($SmmConnectionString, $LoadPolicy)
#>
[Type]$ShardMapManagerType = [Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.ShardMapManager]
$TryGetRangeShardMapMethodGeneric = $ShardMapManagerType.GetMethod("TryGetRangeShardMap")
$TryGetRangeShardMapMethodTyped = $TryGetRangeShardMapMethodGeneric.MakeGenericMethod($KeyType)
$params = @($RangeShardMapName, $null)
$TryGetRangeShardMapMethodTyped.Invoke($ShardMapManager, $params)
$RangeShardMap = $params[1]
$Stringbuilder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$Stringbuilder.psbase.UserID = "$UserName"
#$Stringbuilder.psbase.UserID = "$UserName@$SqlServerName" # サーバー名を指定する必要がある場合 (Data Source が使えないため)
$Stringbuilder.psbase.Password = $Password
$Stringbuilder.ConnectionString
$MultiShardConnection = New-Object Microsoft.Azure.SqlDatabase.ElasticScale.Query.MultiShardConnection($RangeShardMap.GetShards(), $Stringbuilder.ConnectionString)
$MultiShardCommand = $MultiShardConnection.CreateCommand()
$MultiShardCommand.CommandText = "SELECT * FROM ShardTable"
$MultiShardCommand.CommandType = [System.Data.CommandType]::Text
$MultiShardCommand.ExecutionOptions = [Microsoft.Azure.SqlDatabase.ElasticScale.Query.MultiShardExecutionOptions]::IncludeShardNameColumn
# 最終列にシャードの情報を追加
$MultiShardCommand.ExecutionPolicy = [Microsoft.Azure.SqlDatabase.ElasticScale.Query.MultiShardExecutionPolicy]::CompleteResults
# PartialResults : 部分的にシャードにアクセスできれば結果を返す / CompleteResults : すべてのシャードにアクセスできれば結果を返す
$data = $MultiShardCommand.ExecuteReader()
while($data.read()){
    "{0} {1}" -f $data["`$ShardName"], $data["Col1"]
}
$data.Close()
$data.Dispose()

 

マルチシャードクエリとして実行した場合、複数のシャードに同じクエリを実行したものを UNION された結果が取得されるため、以下のような結果を取得できます。

# どのシャードの結果が先に帰っているかの保証はないはずです。

image

 

シングルシャードに対してのクエリは以下のようになります。

最初に「OpenConnectionForKey」で範囲シャードのキーを渡すこと度で、対象のシャードのデータが格納されている接続を取得することができます。

$SingleShardConnection = $RangeShardMap.OpenConnectionForKey(1,$Stringbuilder.ConnectionString)
$SingleShardCommand = $SingleShardConnection.CreateCommand()
$SingleShardCommand.CommandText = "SELECT *,DB_NAME() AS dbname FROM ShardTable"
$SingleShardCommand.CommandType = [System.Data.CommandType]::Text
$data = $SingleShardCommand.ExecuteReader()
while($data.read()){
    "{0} {1}" -f $data["dbname"], $data["Col1"]
}
$data.Close()
$data.Dispose()

 

image

 

キーを変えると以下のような結果になります。

$SingleShardConnection = $RangeShardMap.OpenConnectionForKey(14,$Stringbuilder.ConnectionString)
$SingleShardCommand = $SingleShardConnection.CreateCommand()
$SingleShardCommand.CommandText = "SELECT *,DB_NAME() AS dbname FROM ShardTable"
$SingleShardCommand.CommandType = [System.Data.CommandType]::Text
$data = $SingleShardCommand.ExecuteReader()
while($data.read()){
    "{0} {1}" -f $data["dbname"], $data["Col1"]
}
$data.Close()
$data.Dispose()

image

Elastic Scale は SQL Database だけでなく、SQL Server でも使用することが可能です。

image

 

 

最後にスクリプトの全体はこちらに

Add-Type -path C:\ElasticScale\Microsoft.Azure.SqlDatabase.ElasticScale.Client.dll
$SqlServerName = "<サーバー名>.database.windows.net"
$ShardMapManagerDb = "ShardMapManagerDb"
$ShardDB01 = "ShardDB01"
$ShardDB02 = "ShardDB02"
$UserName = "<ログイン>"
$Password = "<パスワード>"
$RangeShardMapName = "ElasticScaleShardMap" #シャード名
$KeyType = [int] #int をシャードキーとして使用
# ShardMapManager の作成
[Type]$ShardMapManagementFactoryType = [Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.ShardMapManagerFactory]
$SmmConnectionString = "Server=$SqlServerName; Initial Catalog=$ShardMapManagerDb; User ID=$UserName; Password=$Password"
$CreateMode = [Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.ShardMapManagerCreateMode]::ReplaceExisting
$ShardMapManager = $ShardMapManagementFactoryType::CreateSqlShardMapManager($SmmConnectionString, $CreateMode)
[Type]$ShardMapManagerType = [Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.ShardMapManager]
$CreateRangeShardMapMethodGeneric = $ShardMapManagerType.GetMethod("CreateRangeShardMap")
$CreateRangeShardMapMethodTyped = $CreateRangeShardMapMethodGeneric.MakeGenericMethod($KeyType)
$params = @($RangeShardMapName)
$RangeShardMap = $CreateRangeShardMapMethodTyped.Invoke($ShardMapManager, $params)
# シャードに参照テーブルの情報を設定する場合は以下を設定する
# $SchemaInfo = New-Object Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.Schema.SchemaInfo
# $SchemaInfo.Add((New-Object Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.Schema.ShardedTableInfo("ShardTable","ShardKey")))
# $SchemaInfo.Add((New-Object Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.Schema.ReferenceTableInfo("ReferenceTable")))
# $ShardMapManager.GetSchemaInfoCollection().Add($RangeShardMapName, $SchemaInfo)
# Shard の追加
$ShardLocation = New-Object Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.ShardLocation($SqlServerName, $ShardDB01)
[Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.Shard]$ShardReference = $null
$ShardMapReturn = $RangeShardMap.CreateShard($ShardLocation)
# RangeMapping の追加
$RangeLow = 1
$RangeHigh = 10
$InputShard = $RangeShardMap.GetShard($ShardLocation)
$InputRange = New-Object Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.Range[$KeyType]($RangeLow, $RangeHigh)
$RangeShardMap.CreateRangeMapping($InputRange, $InputShard)
# Shard の追加
$ShardLocation = New-Object Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.ShardLocation($SqlServerName, $ShardDB02)
[Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.Shard]$ShardReference = $null
$ShardMapReturn = $RangeShardMap.CreateShard($ShardLocation)
# RangeMapping の追加
$RangeLow = 11
$RangeHigh = 20
$InputShard = $RangeShardMap.GetShard($ShardLocation)
$InputRange = New-Object Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.Range[$KeyType]($RangeLow, $RangeHigh)
$RangeShardMap.CreateRangeMapping($InputRange, $InputShard)
# シャードのマッピング情報を取得
$RangeShardMap.GetMappings()
$RangeShardMap.GetShards()
# Multi shard Query
$SmmConnectionString = "Server=$SqlServerName; Initial Catalog=$ShardMapManagerDb; User ID=$UserName; Password=$Password"
[Type]$ShardMapManagementFactoryType = [Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.ShardMapManagerFactory]
$LoadPolicy = [Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.ShardMapManagerLoadPolicy]::Lazy
[Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.ShardMapManager]$ShardMapManager = $null
$ShardMapManagementFactoryType::TryGetSqlShardMapManager($SmmConnectionString, $LoadPolicy, [ref]$ShardMapManager)
<#
$LoadPolicy = [Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.ShardMapManagerLoadPolicy]::Lazy
[Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.ShardMapManager]$ShardMapManager = $null
$ShardMapManager = $ShardMapManagementFactoryType::GetSqlShardMapManager($SmmConnectionString, $LoadPolicy)
#>
[Type]$ShardMapManagerType = [Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.ShardMapManager]
$TryGetRangeShardMapMethodGeneric = $ShardMapManagerType.GetMethod("TryGetRangeShardMap")
$TryGetRangeShardMapMethodTyped = $TryGetRangeShardMapMethodGeneric.MakeGenericMethod($KeyType)
$params = @($RangeShardMapName, $null)
$TryGetRangeShardMapMethodTyped.Invoke($ShardMapManager, $params)
$RangeShardMap = $params[1]
$Stringbuilder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$Stringbuilder.psbase.UserID = "$UserName"
#$Stringbuilder.psbase.UserID = "$UserName@$SqlServerName" # サーバー名を指定する必要がある場合 (Data Source が使えないため)
$Stringbuilder.psbase.Password = $Password
$Stringbuilder.ConnectionString
$MultiShardConnection = New-Object Microsoft.Azure.SqlDatabase.ElasticScale.Query.MultiShardConnection($RangeShardMap.GetShards(), $Stringbuilder.ConnectionString)
$MultiShardCommand = $MultiShardConnection.CreateCommand()
$MultiShardCommand.CommandText = "SELECT * FROM ShardTable"
$MultiShardCommand.CommandType = [System.Data.CommandType]::Text
$MultiShardCommand.ExecutionOptions = [Microsoft.Azure.SqlDatabase.ElasticScale.Query.MultiShardExecutionOptions]::IncludeShardNameColumn
# 最終列にシャードの情報を追加
$MultiShardCommand.ExecutionPolicy = [Microsoft.Azure.SqlDatabase.ElasticScale.Query.MultiShardExecutionPolicy]::CompleteResults
# PartialResults : 部分的にシャードにアクセスできれば結果を返す / CompleteResults : すべてのシャードにアクセスできれば結果を返す
$data = $MultiShardCommand.ExecuteReader()
while($data.read()){
    "{0} {1}" -f $data["`$ShardName"], $data["Col1"]
}
$data.Close()
$data.Dispose()
$SingleShardConnection = $RangeShardMap.OpenConnectionForKey(14,$Stringbuilder.ConnectionString)
$SingleShardCommand = $SingleShardConnection.CreateCommand()
$SingleShardCommand.CommandText = "SELECT *,DB_NAME() AS dbname FROM ShardTable"
$SingleShardCommand.CommandType = [System.Data.CommandType]::Text
$data = $SingleShardCommand.ExecuteReader()
while($data.read()){
    "{0} {1}" -f $data["dbname"], $data["Col1"]
}
$data.Close()
$data.Dispose()

 

データ追加を Elastic Scale API を使用して実施する場合はこちらに。

# ExecuteNonQuery をマルチシャードクエリとして実行できないみたいですね。

[Type]$ShardMapManagementFactoryType = [Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.ShardMapManagerFactory]
$LoadPolicy = [Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.ShardMapManagerLoadPolicy]::Lazy
[Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.ShardMapManager]$ShardMapManager = $null
$ShardMapManagementFactoryType::TryGetSqlShardMapManager($SmmConnectionString, $LoadPolicy, [ref]$ShardMapManager)
[Type]$ShardMapManagerType = [Microsoft.Azure.SqlDatabase.ElasticScale.ShardManagement.ShardMapManager]
$TryGetRangeShardMapMethodGeneric = $ShardMapManagerType.GetMethod("TryGetRangeShardMap")
$TryGetRangeShardMapMethodTyped = $TryGetRangeShardMapMethodGeneric.MakeGenericMethod($KeyType)
$params = @($RangeShardMapName, $null)
$TryGetRangeShardMapMethodTyped.Invoke($ShardMapManager, $params)
$RangeShardMap = $params[1]
$Stringbuilder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$Stringbuilder.psbase.IntegratedSecurity = $true
# 初期データの追加
$SingleShardConnection = $RangeShardMap.OpenConnectionForKey(1,$Stringbuilder.ConnectionString)
$SingleShardCommand = $SingleShardConnection.CreateCommand()
$SingleShardCommand.CommandType = [System.Data.CommandType]::Text
$SingleShardCommand.CommandText = "CREATE TABLE ShardTable(Col1 int);"
$SingleShardCommand.CommandText += "INSERT INTO ShardTable VALUES(1), (2), (3), (4), (5)"
$SingleShardCommand.ExecuteNonQuery()
$SingleShardConnection = $RangeShardMap.OpenConnectionForKey(11,$Stringbuilder.ConnectionString)
$SingleShardCommand = $SingleShardConnection.CreateCommand()
$SingleShardCommand.CommandType = [System.Data.CommandType]::Text
$SingleShardCommand.CommandText = "CREATE TABLE ShardTable(Col1 int);"
$SingleShardCommand.ExecuteNonQuery()
$SingleShardCommand.CommandText = "INSERT INTO ShardTable VALUES(11), (12), (13), (14), (15)"
$SingleShardCommand.ExecuteNonQuery()
Share

Written by Masayuki.Ozawa

4月 7th, 2015 at 10:13 pm

Posted in PowerShell,SQL Database

Tagged with ,

«
»

Leave a Reply