【SQLServer2016】地味に嬉しい新機能(JSON対応,セキュア化,パラレル化)

この記事はエムティーアイAdventCalendar2016の18日目の記事です。
投稿者は毎日DBを触ってる3年目エンジニアです。(分かる人だけ特定できる情報)

つい昨日まで、
「SQLServer2016の機能(インメモリOLTP+列インデックス)を使った集計爆速化の記事を書こう～」
と思っていたのですが…
ベンチマーク結果を公開するのはSQLServerの利用規約違反だそうなので、

2016新機能の中で、**「目玉機能じゃなけどこれは嬉しいぞ！」**と思ったものを使って紹介します。

#この記事を読むと分かること
①JSONからの入力/JSONへの出力
②セキュリティに関する新機能
③パラレル化によるINSERT～SELECTの高速化

#エディションと検証環境
今回はMicrosoftの[Visual Studio Dev Essentials]に登録して、
SQLServer2016 DveloperEditionを使います。

このエディションは開発用途には使えませんが、
SQLServerのすべての機能を使うことができます。

また、これに登録すると特典として**12ヶ月の間、Azureが$25/月まで無料で使える(!!!!)**ので、
Windows Server2012R2 Standard F2s(2コア、メモリ4GB、SSD)
を立ち上げてそこにインストールしています。
※SQLServer2016は皆さんお使いのWindows7では動かないので注意！
(ローカルにインストールしてから気付いた...)

#①JSONからの入力/JSONへの出力
ついにSQLServerがJSONに対応しました！
(このままSQLとNoSQLの境目が無くなっていくのでは…ｗ)
mysqlではJSON型が実装され、JSONをそのままJSON型として取り込むことができるようですが、
ここでは

• JSONを文字列で受け取って、要素を各カラムへ分けて挿入
• 普通のテーブルのSELECT結果をJSON形式で出力

の２つをやってみます。

今回はテスト用にこんなUserテーブルを使います(適当です)

なかなか使えるサンプルデータが見つからず、下記サイトで作りました。
http://www.databasetestdata.com/#

##JSONからの入力

3人分のJSONデータを文字列で定義して、UserテーブルにInsertしてみます。

insertJson.sql

DECLARE @str nvarchar(max) = 
	N'{"jsonfile":[
				{
					"UserId": 1001,
					"UserName": "Danyka",
					"Country": "Mongolia",
					"Password": "6905a362cb3993a2144f603ccb7fb2096038",
					"Email": "[e-mail]"
				},
				{
					"UserId": 1002,
					"UserName": "Ludie",
					"Country": "Fiji",
					"Password": "8bb3ffe1961b98030c2d8d969dc08f5869ba",
					"Email": "[e-mail]"
				},
				{
					"UserId": 1003,
					"UserName": "Jayson",
					"Country": "Suriname",
					"Password": "25a04f83bda548ab0b9604cfe1a5f65f8444",
					"Email": "[e-mail]"
				}
			]
		}'
		
INSERT [Test].[dbo].[User] 
OUTPUT inserted.*
SELECT * FROM
OPENJSON(@str,'$.jsonfile')
WITH (
	UserId int,
	UserName varchar(50),
	Country varchar(50),
	Password varchar(50),
	Email varchar(50)
)

実行結果↓

簡単ですね。各カラムにキチンと分けて格納されてます！

##JSONへの出力
次はこのテーブルをJSON形式でSELECTします。
WHERE句で絞って1件だけ取り出してみましょう。

selectJson.sql

SELECT *
FROM [Test].[dbo].[User]
WHERE UserId = 1
FOR JSON AUTO

実行結果↓

JSON形式でSELECTできました！

JSONを扱うWebシステムにとっては非常に魅力的ですね。
速度の面が気になりますが、まだまだ進化していくでしょう。

#②セキュリティに関する新機能

セキュリティに関する新機能が多く追加されましたが、
その中で「動的データマスク」を試そうと思います。

##動的データマスク

これは、カラムごとにデータをマスクして、管理者以外にはデータが見えないようにする機能です。
内部的なデータの暗号化ではありません。

これは、テーブル作成時にカラムごとに設定が必要です。
マスクのタイプがいくつかあり、データによって使い分けると良いでしょう。

• default⇒データを"xxxx"としてマスク
• email⇒アドレス形式の文字列をマスク
• random⇒数値データを乱数にマスク
• partial⇒文字データを任意の文字列にマスク

ここでは、先ほどのUserテーブルのうち、
[Password]と[Email]のカラムをマスクするテーブルを作ります。

createUserMaskTable.sql

USE [Test]
CREATE TABLE [dbo].[UserMask](
	[UserId] [int] NOT NULL,
	[UserName] [varchar](50) NULL,
	[Country] [varchar](50) NULL,
	[Password] [varchar](50) MASKED WITH (FUNCTiON = 'default()') NULL,
	[Email] [varchar](50) MASKED WITH (FUNCTiON = 'email()') NULL,
) ON [PRIMARY]

まずは管理者権限を持っているsaユーザでSELECTします。
実行結果↓

管理者なので普通にデータが読み取れてますね。
次に、読み取り権限のみのユーザ[ReadUser]を作成したので、それでログインしてSELECTします。

実行結果↓

[Password]と[Email]がマスクされてます！！！！！
ただ、どの権限を持ったユーザがマスクされるのかを厳密に設定する方法までは分かりませんでした。
(SQLServerの権限周りはただでさえヤヤコシイので、あまり複雑にはしたくないですが・・・)

##その他のセキュリティ機能
ざっと調べたところこんな感じです。

• 行レベルセキュリティにより、ユーザに対して参照できる行を制限できる。
• Always Encyptedによって内部的にデータを暗号化してセキュアに通信できる。
• 暗号化によるCPUのオーバーヘッドが軽減された。
• インメモリOLTPを使ったテーブルも暗号化できるようになった。
• テンポラルテーブルを作成してテーブルへの操作履歴(監査証跡)を保存できる。

これだけ豊富な機能があれば、
セキュリティ的に厳しい要件が求められるシステムでも
対応できるような気がします。

#③パラレル化によるINSERT～SELECTの高速化
クエリのパラレル化(マルチスレッド化)は以前からある機能で、
2014の時点で、既に[SELECT]や[SELECT INTO]のパラレル化は可能でした。

ですが、[INSERT～SELECT]については
SELECT部分はマルチでも、INSERT部分はシリアル処理で行われ、
マルチコアを活かしきれていなかったようです。

SQLSerer2016ではINSERT部分もパラレル化されるようなので試してみます。

ここでは適当な数値を持ったレコードを1280万行用意して、
それぞれのカラムを3乗しながら別テーブルにINSERTするクエリを実行してみます。

パラレル化するにはINSERT句に"WITH (TABLOCK)"を付けるだけです。
シリアルとパラレルで実行プランの違いを見てみましょう。

まずはシリアルなSQL

serialInsert.sql

USE [Test]
INSERT [dbo].[DestTable]
SELECT 
	ColA * ColA * ColA,
	ColB * ColB * ColB,
	ColC * ColC * ColC,
	ColD * ColD * ColD,
	ColE * ColE * ColE
FROM [dbo].[SrcTable]

次にパラレル化されたSQL

parallelInsert.sql

USE [Test]
INSERT [dbo].[DestTable] WITH(TABLOCK) 
SELECT 
	ColA * ColA * ColA,
	ColB * ColB * ColB,
	ColC * ColC * ColC,
	ColD * ColD * ColD,
	ColE * ColE * ColE
FROM [dbo].[SrcTable]

実行プランに**[Parallelism]**というそれっぽい処理があります！！

詳細は割愛しますが、実行時間が1/2~1/3程度に短縮されました！！

注意点として、このパラレル化を使うには
インデックスの無いヒープ、又はクラスター化列ストアインデックスのみのテーブルであることが条件です。

#まとめ
なんだか雑多な記事になってしまいました…

データベースの使い道は多岐にわたっていて
バッチ処理がメインのDWHもあれば
リアルタイム処理をするOLTPもあります。

セキュリティ要件が厳しいシステムもあれば、
今回紹介したJSONなどでWebとラフにやり取りした場合もあるのかもしれません。

データベースの進化はあまり目立ちませんが、
様々な用途に対応できるように裾野を広げていて、
着実に便利に高速になっているなと実感しました。

#参考資料
SQLServer2016 自習書(PDFで無料で落とせる&超分かりやすい公式ドキュメントです)
https://www.microsoft.com/ja-jp/cloud-platform/products-SQL-Server-2016-Evaluate.aspx