SE の雑記

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

Serverless SQL プールで Azure ストレージの診断ログを検索する際の考慮事項

leave a comment

Frontdoor の診断設定で取得した、Azure ストレージ上のアクセスログを Synapse Analytics の Serveless SQL プールで検索する際にいくつかの考慮点があったため、その内容をまとめておきたいと思います。

重複項目についての考慮

Frontdoor の診断設定でアクセスログを取得した際のフォーマットについては Access log に記載されています。

Azure ストレージに取得したアクセスログ (PT1H.json) には、次ような情報が出力されています。

image

前述のフォーマットには記載されていないのですが、properties には、同名で errorInfo / ErrorInfo という 2 種類の項目が出力されています。

Serverless SQL プールの既定の照合順序については 照合順序を選択する に記載されていますが、デフォルトでは 「SQL_Latin1_General_CP1_CI_AS」が設定されており、新規にデータベースを作成せずに master で検索を行うと大文字 / 小文字の区別が行われません。

そのため、PT1H.json で出力されている項目名に合わせて、OPENROWSET を記載すると項目名の重複でエラーが発生します。

The column ‘ErrorInfo’ was specified multiple times for ‘T2’.

 

ErrorInfo の出力を行うためには、次のいずれかの対応を実施する必要があります。

  1. OPENROWSET で出力する際の項目名を変更する
  2. データベースの照合順序を「_CS」で設定されているデータベースを作成し、そのデータベース上で実行する

「1.」の OPENROWSET を実行する場合の項目名の設定変更については、単純に出力名を変更する方法となります。

OPENROWSET で JSON を検索する際には WITH 句で出力名を定義することができますが、この中で出力名を変更することができますので、次のように出力時に列名を変更します。

errorInfo2 varchar(255) '$.properties.errorInfo',
ErrorInfo varchar(255) '$.properties.ErrorInfo',

 

「2.」の方法は新しい照合順序で作成したデータベースで OPENROWSET を実行する方法です。

前述のとおり master では、「SQL_Latin1_General_CP1_CI_AS」の照合順序が設定されているため、大文字 / 小文字の区別が行われません。

Synapse Analytics でサポートされている照合順序については、照合順序のサポート に記載されていますが、Serverless SQL プールでは、広く照合順序のサポートが行われていますので「_CS」の照合順序を使用したデータベースを作成することで、大文字 / 小文字の列名が異なる列名として認識されます。

CREATE DATABASE FrontdoorLog COLLATE Japanese_XJIS_140_CS_AS_UTF8;
USE FrontdoorLog;

 

このようなデータベースを作成して、そのデータベース上でクエリを実行することで、大文字 / 小文字の区別が行われるようになりますので、この照合順序の場合は次の指定でもエラーは発生しません。

errorInfo varchar(255) '$.properties.errorInfo',
ErrorInfo varchar(255) '$.properties.ErrorInfo',

 

照合順序の設定は検索時の文字列比較にも影響しますので、大文字 / 小文字の区別を行う照合順序が適切かは検討が必要ですが、JSON の定義をそのまま使用する場合には、照合順序の変更で対応することができます。

 

最新のログファイルのアクセスの考慮

OPENROWSET では、末尾の「**」を指定することで、サブフォルダーを対象とした検索を行うことができます。

例のように、パスの末尾に /* を指定することで、フォルダーをスキャンするようサーバーレス SQL プールに指示できます: https://sqlondemandstorage.blob.core.windows.net/csv/population/**

これで、最新のログファイルまでアクセスを行うと、次のようなエラーが発生します。

— Parser 1.0

Cannot bulk load because the file "https://xxxxxxx.blob.core.windows.net/insights-logs-frontdooraccesslog/resourceId=/SUBSCRIPTIONS/xxxxxx/RESOURCEGROUPS/xxxx/PROVIDERS/MICROSOFT.CDN/PROFILES/xxxx/y=2024/m=04/d=21/h=12/m=00/PT1H.json" could not be opened. Operating system error code 12(The access code is invalid.).

— Parser 2.0

Statement ID: {0132E29C-F604-4F3B-9B46-1BE224093406} | Query hash: 0xCB25BA14E1B8CE2B | Distributed request ID: {03DC102D-45F5-4320-8208-9DE22A414D51}. Total size of data scanned is 13320 megabytes, total size of data moved is 1 megabytes, total size of data written is 0 megabytes. 2 rows were rejected during query execution. Error encountered while parsing data: ‘waitIOCompletion error. HRESULT = 0x8007000C(offset = 0, bytes requested = 1048576).’. Underlying data description: file ‘https://xxxxxx.blob.core.windows.net/insights-logs-frontdooraccesslog/resourceId=/SUBSCRIPTIONS/xxxx/RESOURCEGROUPS/xxxxx/PROVIDERS/MICROSOFT.CDN/PROFILES/xxxx/y=2024/m=04/d=21/h=12/m=00/PT1H.json’.

単体で最新のファイルを検索した場合は、上記のエラーが発生する可能性は低いのですが、複数のファイルを対象として検索した場合には、上記のエラーが発生する可能性が高いです。

この挙動については、追加可能なファイルに対するクエリの実行 に記載が行われています。

クエリで使用されている CSV ファイルは、クエリの実行中に変更しないでください。 長時間実行されるクエリでは、SQL プールによって読み取りが再試行されたり、ファイルの一部が読み取られたり、ファイルが複数回読み取られたりする場合もあります。 ファイル コンテンツを変更すると、結果が不正確になる可能性があります。 したがって、クエリの実行中にファイルの変更時刻の変更が検出された場合、SQL プールのクエリは失敗します。

最新のファイルについては、検索実行時から更新される可能性が高く、最新のファイルに変更が発生した場合は上記のエラーが発生します。

これを回避するには、次のような対応の実施を検討する必要があります。

  1. 最新のファイルを検索対象から除外する
  2. 「ROWSET_OPTIONS = ‘{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}’」を指定する

 

「1.」の場合は filepath 関数を使用して、最新のファイルを対象から除外して検索を行います。

OPENROWSET で対象の除外が難しいですが、WHERE 句で次のような条件を指定して、エラーが発生した最新のファイルを除外することで、エラーを回避します。

jsonrows.filepath() NOT LIKE 'https://xxxx.blob.core.windows.net/insights-logs-frontdooraccesslog/resourceId=/SUBSCRIPTIONS/xxxx/RESOURCEGROUPS/xxxxx/PROVIDERS/MICROSOFT.CDN/PROFILES/xxxxx/y=2024/m=04/d=21/h=12/%'

 

「2.」については、「ROWSET_OPTIONS = ‘{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}’」を指定することで、ファイル変更チェックを無効にします。

このオプションを OPENROWSET に指定することで、ファイル変更チェックが無効となりエラーの発生が抑えられます。OPENROWSET のオプションの詳細については、次の情報で確認できます。

これらのいずれかの方法を使用することで、エラーを回避することができます。

 

サンプルのクエリ

Azure ストレージに保存した、Frontdoor のアクセスログを検索するクエリのサンプルが次となります。

Parser 2.0 の場合は、OPENROWSET で NVARCHAR が 4000 までしか指定できないため、Parser 1.0 で NVARCHAR(MAX) を指定しているバージョンで作成しています。

複数回、検索を実行するのであれば CETAS を使用して、検索に必要なデータを Perquet で保存してからそちらを検索することも検討したほうが良いかと思いますが、下記のようなクエリでベースとなるデータは作成できるかと。

 

/*
CREATE DATABASE FrontdoorLog COLLATE Japanese_XJIS_140_CS_AS_UTF8;
USE FrontdoorLog;
*/

SELECT
	referer, 
    COUNT(*) AS cnt
FROM(
	SELECT 
			jsonrows.filepath() AS filepath, 
			jsonrows.filename() AS filename, 
			*
	FROM
		OPENROWSET(
			BULK 'https://xxxx.blob.core.windows.net/insights-logs-frontdooraccesslog/resourceId=/SUBSCRIPTIONS/xxxxx/RESOURCEGROUPS/xxxx/PROVIDERS/MICROSOFT.CDN/PROFILES/xxxxx/y=2024/**', 
			FORMAT= 'CSV',
/*
			-- Parser 2.0
			PARSER_VERSION = '2.0',
			FIELDTERMINATOR = '0x09',
			FIELDQUOTE = '0x0b',
			ROWTERMINATOR = '0x0a',
			MAXERRORS=10
			, ROWSET_OPTIONS = '{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}'
		) WITH (doc NVARCHAR(4000)) AS jsonrows
*/
			-- Parser 1.0
			FIELDTERMINATOR = '0x0b',
			FIELDQUOTE = '0x0b',
			MAXERRORS=10
			,ROWSET_OPTIONS = '{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}'
		) WITH (doc NVARCHAR(max)) AS jsonrows
	OUTER APPLY OPENJSON(doc)
	WITH (
		time datetime2 '$.time',
		resourceId varchar(255) '$.resourceId',
		category varchar(50) '$.category',
		operationName varchar(100) '$.operationName',
		trackingReference varchar(255) '$.properties.trackingReference',
		httpMethod varchar(255) '$.properties.httpMethod',
		httpVersion varchar(255) '$.properties.httpVersion',
		requestUri nvarchar(4000) '$.properties.requestUri',
		requestBytes int '$.properties.requestBytes',
		responseBytes int '$.properties.responseBytes',
		userAgent varchar(255) '$.properties.userAgent',
		clientIp varchar(255) '$.properties.clientIp',
		clientPort int '$.properties.clientPort',
		socketIp varchar(255) '$.properties.socketIp',
		timeToFirstByte numeric(10,4) '$.properties.timeToFirstByte',
		timeTaken  numeric(10,4) '$.properties.timeTaken',
		requestProtocol varchar(255) '$.properties.requestProtocol',
		securityProtocol varchar(255) '$.properties.securityProtocol',
		rulesEngineMatchNames varchar(255) '$.properties.rulesEngineMatchNames',
		httpStatusCode varchar(10) '$.properties.httpStatusCode',
		httpStatusDetails varchar(10) '$.properties.httpStatusDetails',
		pop varchar(255) '$.properties.pop',
		cacheStatus varchar(255) '$.properties.cacheStatus',
		errorInfo varchar(255) '$.properties.errorInfo',
		ErrorInfo varchar(255) '$.properties.ErrorInfo',
		endpoint varchar(255) '$.properties.endpoint',
		routingRuleName varchar(255) '$.properties.routingRuleName',
		hostName varchar(255) '$.properties.hostName',
		originUrl nvarchar(255) '$.properties.originUrl',
		originIp varchar(255) '$.properties.originIp',
		originName nvarchar(255) '$.properties.originName',
		referer nvarchar(255) '$.properties.referer',
		clientCountry varchar(255) '$.properties.clientCountry',
		domain varchar(255) '$.properties.domain',
		securityCipher varchar(255) '$.properties.securityCipher',
		securityCurves varchar(255) '$.properties.securityCurves'
	)
--	WHERE 
--		jsonrows.filepath() NOT LIKE 'https://xxxxx.blob.core.windows.net/insights-logs-frontdooraccesslog/resourceId=/SUBSCRIPTIONS/xxxxx/RESOURCEGROUPS/WORDPRESS/PROVIDERS/MICROSOFT.CDN/PROFILES/xxxxx/y=2024/m=04/d=21/h=03/%'
) AS T2
GROUP BY referer
ORDER BY cnt DESC
Share

Written by Masayuki.Ozawa

4月 21st, 2024 at 11:28 pm

Posted in Synapse Analytics

Tagged with

«
»

Leave a Reply