2023/11/24 修正、追加していますので、ご注意ください。
2023/12/26 追記しました。
SQLServerの共通言語ランタイム(CLR)を利用したライブラリを作成するなかで、JSONデータを取り扱うCLRを作成しようと考えて、そのサンプルとして天気予報のJSONデータを取得し処理した例をまとめました。
なので、ダイレクトマーケティングです。
作成したライブラリはまだ未公開ですのでご注意ください。
また、目的はCLRライブラリの利用事例の紹介なので、CLRライブラリの作成や天気予報のJSONデータの説明や取得方法については別の記事を参照してください。
参考にした記事:
内容
-
CLRライブラリを用いて気象庁ホームページからJSONデータを取得
-
CLRライブラリを用いてJSONデータをテーブルの形に処理
CLRライブラリについて
今回使用するライブラリは2つです。
- WebAccessライブラリ
URLを与えて呼び出すとURLから取得したデータをテーブル値の形で返すユーザ定義関数CLR_WebAccess_GetResultFromURL
を拡張するライブラリです。
また、URLに情報を含める際に補助するための関数群
CLR_WebAccess_ConcatPartToURL
CLR_WebAccess_ConcatArgToURL
CLR_WebAccess_EscapeUriString
を拡張します。
- JSONAccessライブラリ
JSONデータをオブジェクト的に表現するユーザ定義型CLR_JSONAccess_JSONValue
を拡張するライブラリです。
また、JSONのオブジェクトや配列をテーブル値として展開する関数
CLR_JSONAccess_JSONValueList
を拡張します。
作業手順
データベースにCLRをインストール
対象とするデータベースにCLRをインストールします。
CLRはデータベースの単位でインストールされますので、複数のデータベースで利用する場合はそれぞれにインストールを行う必要があります。
インストール後のDLLファイルは不要ですので、削除できます。
- CLR機能の有効化
EXEC sp_configure 'clr enabled' , '1';
GO
RECONFIGURE;
GO
-
データベースの信頼の変更2023/11/24 修正
permission_set = External_Access
のWebAccessライブラリを実行するためにはデータベースのTRUSTWORTHY
プロパティをON
にする必要があります。
(ほかの方法もあるようですが、不勉強のため知りません。)
意味としては「データベース内のものは(悪意のあるものでも)何でも信頼する」ということですから、セキュリティが低下することになりますので注意して行ってください。
参考:
-- ALTER DATABASE [**DB名**] SET TRUSTWORTHY ON;
--GO
-
証明書の登録 2023/11/24 追加
TRUSTWORTHY ON
ができないDBにCLRを追加する必要があり、少し勉強しました。
結果として次のことがわかりました。- CLRのアセンブリが証明書で署名されていて証明書がSQLServerに登録されているならば、
TRUSTWORTHY ON
は必要ない。 - CLRのコードからビルドする段階(VisualStudioなど)でアセンブリに署名をする。
- 一つの署名につき一つの非対称キーを作成し、一つのひもつけたログインを
master
データベースに追加する。 - ログイン登録後に
CREATE ASSEMBLY
などでCLRを登録する。(通常のCLR登録)
- CLRのアセンブリが証明書で署名されていて証明書がSQLServerに登録されているならば、
CLRのコードに署名する部分については、ここでは省略します。
SQLServerでの証明書の登録の手順を以下に記します。
USE [master]
GO
-- 非対称キーの作成
CREATE ASYMMETRIC KEY (非対称キーの名前)
FROM EXECUTABLE FILE = 'D:\(署名されたDLLファイル)';
-- 非対称キーはmasterデータベースに登録されます
-- 非対称キーにひもついたログインの作成
CREATE LOGIN (ログインの名前)
FROM ASYMMETRIC KEY (非対称キーの名前);
-- ログインはデータウェアハウスに登録されます
-- ログインに権限を追加
GRANT UNSAFE ASSEMBLY TO (ログインの名前);
GO
CREATE ASYMMETRIC KEY
文では実行ファイル(DLLファイル)から証明書を取り出して非対称キーに変換しています。
CREATE LOGIN
文では非対称キーとひもついたログインを作成しています。
GRANT UNSAFE ASSEMBLY
文ではログインで許可する権限を指定しています。
この操作は、証明書毎に異なる名前(非対称キー名、ログイン名)で行う必要があります。
参考:
GRANT UNSAFE ASSEMBLY
文はGRANT EXTERNAL ACCESS ASSEMBLY
として権限を変えてもいいのですが、clr strict security
が設定されている(sp_configre 'clr strict security'
でconfig_value=1
となっている)場合、すべてのCLRをUNSAFEとして扱われます。
また、同じ証明書では同じ権限になるため、UNSAFE ASSEMBLY
と指定するほうが便利そうです。
(2023/11/24 追加ここまで)
- WebAccessライブラリのインストール
CREATE ASSEMBLY CLRWebAPIAccess
FROM '<サーバのDLLファイルの場所>\CLRWebAPIAccess.dll'
WITH permission_set = External_Access;
GO
CREATE FUNCTION CLR_WebAccess_GetResultFromURL(@vURLAddress nvarchar(max))
RETURNS TABLE(
url nvarchar(max)
,isSuccessed bit
,result nvarchar(max)
,errorMessage nvarchar(max)
)
AS
EXTERNAL NAME
CLRWebAPIAccess.[nsWebAPIAccess.ClsCLRWebAPIAccess].GetResultFromURL;
GO
CREATE FUNCTION CLR_WebAccess_ConcatPartToURL(
@vURLBase nvarchar(max)
, @vPart nvarchar(max))
RETURNS nvarchar(max)
AS
EXTERNAL NAME
CLRWebAPIAccess.[nsWebAPIAccess.ClsCLRWebAPIAccess].ConcatPartToUrl;
GO
CREATE FUNCTION CLR_WebAccess_ConcatArgToURL(
@vURLBase nvarchar(max)
, @vArgKey nvarchar(max)
, @vArgValue nvarchar(max))
RETURNS nvarchar(max)
AS
EXTERNAL NAME
CLRWebAPIAccess.[nsWebAPIAccess.ClsCLRWebAPIAccess].ConcatArgToUrl;
GO
CREATE FUNCTION CLR_WebAccess_EscapeUriString(@vString nvarchar(max))
RETURNS nvarchar(max)
AS
EXTERNAL NAME
CLRWebAPIAccess.[nsWebAPIAccess.ClsCLRWebAPIAccess].EscapeUriString;
GO
CREATE ASSEMBLYのときにpermission_set = External_Access
としているのはインターネットにアクセスするために必要だからです。
- JSONAccessライブラリのインストール
CREATE ASSEMBLY CLRJSONAccess
FROM '<サーバのDLLファイルの場所>\CLRJSONAccess.dll'
WITH permission_set = safe;
GO
-- ユーザ定義型
Create Type dbo.CLR_JSONAccess_JSONValue
External name
CLRJSONAccess.[nsJSONAccess.UDTJSONValue];
go
-- ユーザ定義型を対象にしたテーブル値関数
Create Function dbo.CLR_JSONAccess_JSONValueList(
@json CLR_JSONAccess_JSONValue
)
returns table(
ListIndex int
,ItemName nvarchar(max)
,ItemValue CLR_JSONAccess_JSONValue
)
AS EXTERNAL NAME
CLRJSONAccess.[nsJSONAccess.ClsCLRJSONAccess].List;
GO
テストスクリプトの実行
- WebAccess単体のテスト
テストとして国土地理院のジオコーディング(住所から緯度経度への変換)を行ってみます。
declare @urlbase nvarchar(max);
declare @url nvarchar(max);
declare @resultstr nvarchar(max);
declare @address nvarchar(max);
-- APIのためのURLを作成
set @address='東京都文京区1-1';
set @urlbase='https://msearch.gsi.go.jp/';
set @url=dbo.CLR_WebAccess_ConcatPartToURL(
@urlbase
,'/address-search/AddressSearch');
set @url=dbo.CLR_WebAccess_ConcatArgToURL(
@url
,'q'
,dbo.CLR_WebAccess_EscapeUriString(@address));
select
@address as 対象住所
, dbo.CLR_WebAccess_EscapeUriString(@address) as [エスケープ処理後住所];
select @url as 対象URL;
-- 作業用の一時テーブルが残っていたら削除する
if object_id('tempdb..#テスト', N'U') is not null
drop table #テスト;
-- 一時テーブルにデータを保存
select
*
into #テスト
from dbo.CLR_WebAccess_GetResultFromURL(@url);
-- 一時テーブルの内容
select
*
from #テスト;
select
@resultstr=A.result
from #テスト A;
select
@resultstr as 検索結果;
drop table #テスト;
結果の一部:
検索結果 |
---|
[{"geometry":{"coordinates":[139.752655,35.707817],"type":"Point"},"type":"Feature","properties":{"addressCode":"","title":"東京都文京区"}}] |
- JSONAccess単体のテスト
-- 作業用のテーブルを削除する
if OBJECT_ID('TestJSONTable') is not null
drop table dbo.TestJSONTable;
-- 動作テストに使うテーブルを作成
create table dbo.TestJSONTable
( id int identity(1,1)
, name nvarchar(20)
, JSONvalue dbo.CLR_JSONAccess_JSONValue
);
-- 変数をユーザ定義型で宣言
declare @arraynumber dbo.CLR_JSONAccess_JSONValue;
-- CASTでユーザ定義型に内容を設定して作成する
set @arraynumber =
cast('{"abc":123,"cde":"aaa","fgh":1.23}'
as dbo.CLR_JSONAccess_JSONValue);
-- ユーザ定義型の情報を表示
select
@arraynumber.Member('abc').AsInt as [名前abcの内容]
, @arraynumber.NameOfMember(1) as [2番目の要素の名前]
, @arraynumber.Item(2).AsFloat as [3番目の要素の値]
;
-- ユーザ定義型を含むテーブルへの挿入
insert into dbo.TestJSONTable
(
name
,jsonvalue)
values
(
'abc'
,cast('[1,3.14,2e2]' as dbo.CLR_JSONAccess_JSONValue))
,(
'def'
,@arraynumber);
-- ユーザ定義型を展開する関数
select
A.id
, A.name
, A.jsonvalue.TypeName as [タイプ] -- 元のテーブルの中のユーザ定義型
, B.ListIndex as [要素番号] -- ここからはユーザ定義型を展開したもの
, B.ItemName as [要素の名前]
, B.ItemValue.AsString as [要素の内容(文字列)]
, B.ItemValue.AsFloat as [要素の内容(浮動小数点値)]
from
dbo.TestJSONTable A
cross apply
dbo.CLR_JSONAccess_JSONValueList(A.jsonvalue) B;
-- 元のテーブルのユーザ定義型を展開する関数
drop table dbo.TestJSONTable;
結果:
名前abcの内容 | 2番目の要素の名前 | 3番目の要素の値 |
---|---|---|
123 | cde | 1.23 |
id | name | タイプ | 要素 番号 |
要素の 名前 |
内容 文字列 |
内容 浮動小数点値 |
---|---|---|---|---|---|---|
1 | abc | Array | 0 | NULL | NULL | NULL |
1 | abc | Array | 1 | NULL | 3.14 | 3.14 |
1 | abc | Array | 2 | NULL | 2e2 | 200 |
2 | def | Object | 0 | abc | 123 | 123 |
2 | def | Object | 1 | cde | aaa | NULL |
2 | def | Object | 2 | fgh | 1.23 | 1.23 |
気象庁JSONデータを使ったテスト
気象庁JSONデータの取得、処理を行います。
- 気象概要の予報のJSON
declare @result nvarchar(max);
declare @json dbo.CLR_JSONAccess_JSONValue;
-- 気象庁からJSONデータを取得
select
@result = result
from dbo.CLR_WebAccess_GetResultFromURL(
'https://www.jma.go.jp/bosai/forecast/data/overview_forecast/'
+'230000.json'
);
select @result;
-- JSONデータをオブジェクト化
set @json=cast(@result as dbo.CLR_JSONAccess_JSONValue);
-- JSONオブジェクトの内容を表示
-- テーブル値関数CLR_JSONAccess_JSONValueList(JSONObject)で
-- 配列、オブジェクトの一覧をテーブル化する
select
A.ListIndex -- 要素番号(0から始まる連番でjsonでの並び順)
,A.ItemName -- オブジェクトの要素の名前
,A.ItemValue.AsString
-- 要素のオブジェクト("abc"のような定数もオブジェクトとして扱う)
from dbo.CLR_JSONAccess_JSONValueList(@json) A;
-- 意味が分かるように整列とラベル付け
-- 配列やオブジェクトの要素は.Item(0)として要素番号でアクセスするか
-- オブジェクトの場合は.Member('text')として名前でアクセスする
-- 要素の内容は数値の場合
-- .AsInt(int型として)、
-- .AsBigInt(bigint型として)、
-- .AsDecimal(decimal(18,9)型として)、
-- .AsReal(real型として)、
-- .AsFloat(float型として)、
-- .AsBit(bit型として)で返される
-- 文字列の場合は.AsString(nvarchar(max)として)で返される
select
@json.Item(0).AsString as 発表機関
, cast(@json.Member('reportDatetime').AsString
as datetimeoffset) as 発表日時
, @json.Member('targetArea').AsString as 対象地域
, @json.Member('headlineText').AsString as 予報文要約
, @json.Member('text').AsString as 予報文
;
当初JSONAccessライブラリのテストスクリプトにする予定だったので、説明のコメントが多いです。
- 気象予報のJSON
declare @json dbo.CLR_JSONAccess_JSONValue;
-- 予報のデータ取得
select
@json=cast(result as dbo.CLR_JSONAccess_JSONValue)
from dbo.CLR_WebAccess_GetResultFromURL(
'https://www.jma.go.jp/bosai/forecast/data/forecast/130000.json'
);
-- 各データの種類で分割
declare @直近予報 dbo.CLR_JSONAccess_JSONValue = @json.Item(0);
declare @発表機関1 nvarchar(10)
= @直近予報.Member('publishingOffice').AsString;
declare @発表日時1 datetimeoffset
= cast(@直近予報.Member('reportDatetime').AsString
as datetimeoffset);
declare @三日天気予報 dbo.CLR_JSONAccess_JSONValue
= @直近予報.Member('timeSeries').Item(0);
declare @三日降雨確率 dbo.CLR_JSONAccess_JSONValue
= @直近予報.Member('timeSeries').Item(1);
declare @最低最高気温 dbo.CLR_JSONAccess_JSONValue
= @直近予報.Member('timeSeries').Item(2);
declare @週間予報 dbo.CLR_JSONAccess_JSONValue = @json.Item(1);
declare @発表機関2 nvarchar(10)
= @週間予報.Member('publishingOffice').AsString;
declare @発表日時2 datetimeoffset
= cast(@週間予報.Member('reportDatetime').AsString
as datetimeoffset);
declare @週間天気 dbo.CLR_JSONAccess_JSONValue
= @週間予報.Member('timeSeries').Item(0);
declare @週間気温 dbo.CLR_JSONAccess_JSONValue
= @週間予報.Member('timeSeries').Item(1);
declare @週間気温平年 dbo.CLR_JSONAccess_JSONValue
= @週間予報.Member('tempAverage');
declare @週間降水平年 dbo.CLR_JSONAccess_JSONValue
= @週間予報.Member('precipAverage');
-- 直近三日天気予報
select
@発表機関1 as 発表機関
,@発表日時1 as 発表日時
,T時間帯.対象日時
,T地域区分.地域コード
,T地域区分.地域名
,T天候分類コード.天候分類コード
,T天候.天候
,T風向風速.風向風速
,T波の高さ.波の高さ
from
(select
A.ListIndex as 時間帯番号
,cast(A.ItemValue.AsString as datetimeoffset) as 対象日時
from
dbo.CLR_JSONAccess_JSONValueList(
@三日天気予報.Member('timeDefines')) A
) T時間帯
cross join
(select
A.ListIndex as 地域区分番号
,A.ItemValue.Member('area').Member('code').AsString as 地域コード
,A.ItemValue.Member('area').Member('name').AsString as 地域名
from
dbo.CLR_JSONAccess_JSONValueList(@三日天気予報.Member('areas')) A
) T地域区分
left outer join
(select
A.ListIndex as 地域区分番号
,B.ListIndex as 時間帯番号
,B.ItemValue.AsString as 天候分類コード
from
dbo.CLR_JSONAccess_JSONValueList(@三日天気予報.Member('areas')) A
outer apply
dbo.CLR_JSONAccess_JSONValueList(A.ItemValue.Member('weatherCodes')) B
) T天候分類コード
on T時間帯.時間帯番号=T天候分類コード.時間帯番号
and T地域区分.地域区分番号=T天候分類コード.地域区分番号
left outer join
(select
A.ListIndex as 地域区分番号
,B.ListIndex as 時間帯番号
,B.ItemValue.AsString as 天候
from
dbo.CLR_JSONAccess_JSONValueList(@三日天気予報.Member('areas')) A
outer apply
dbo.CLR_JSONAccess_JSONValueList(A.ItemValue.Member('weathers')) B
) T天候
on T時間帯.時間帯番号=T天候.時間帯番号
and T地域区分.地域区分番号=T天候.地域区分番号
left outer join
(select
A.ListIndex as 地域区分番号
,B.ListIndex as 時間帯番号
,B.ItemValue.AsString as 風向風速
from
dbo.CLR_JSONAccess_JSONValueList(@三日天気予報.Member('areas')) A
outer apply
dbo.CLR_JSONAccess_JSONValueList(A.ItemValue.Member('winds')) B
) T風向風速
on T時間帯.時間帯番号=T風向風速.時間帯番号
and T地域区分.地域区分番号=T風向風速.地域区分番号
left outer join
(select
A.ListIndex as 地域区分番号
,B.ListIndex as 時間帯番号
,B.ItemValue.AsString as 波の高さ
from
dbo.CLR_JSONAccess_JSONValueList(@三日天気予報.Member('areas')) A
outer apply
dbo.CLR_JSONAccess_JSONValueList(A.ItemValue.Member('waves')) B
) T波の高さ
on T時間帯.時間帯番号=T波の高さ.時間帯番号
and T地域区分.地域区分番号=T波の高さ.地域区分番号
;
-- 中略 -------------------------------------------
-- 向こう一週間の7日間合計降水量の平年値(最高値、最低値)
select
@発表機関2 as 発表機関
,@発表日時2 as 発表日時
,T地域降水.地域コード
,T地域降水.地域名
,T地域降水.七日間合計降水量最低値
,T地域降水.七日間合計降水量最高値
from
(select
A.ListIndex as 地域区分番号
,A.ItemValue.Member('area').Member('code').AsString as 地域コード
,A.ItemValue.Member('area').Member('name').AsString as 地域名
,A.ItemValue.Member('min').AsString as 七日間合計降水量最低値
,A.ItemValue.Member('max').AsString as 七日間合計降水量最高値
from
dbo.CLR_JSONAccess_JSONValueList(@週間降水平年.Member('areas')) A
) T地域降水
;
スクリプトが長いので省略しています。
結果の一部:
発表機関 | 発表日時 | 対象日時 | 地域 コード |
地域名 | 最低 気温 |
最低 気温 上限 |
最低 気温 下限 |
最高 気温 |
最高 気温 上限 |
最高 気温 下限 |
---|---|---|---|---|---|---|---|---|---|---|
気象庁 | 2023-11-13 17:00:00 +09:00 |
2023-11-15 00:00:00 +09:00 |
44132 | 東京 | 9 | 10 | 8 | 17 | 19 | 16 |
気象庁 | 2023-11-13 17:00:00 +09:00 |
2023-11-15 00:00:00 +09:00 |
44172 | 大島 | 11 | 13 | 10 | 19 | 20 | 17 |
気象庁 | 2023-11-13 17:00:00 +09:00 |
2023-11-15 00:00:00 +09:00 |
44263 | 八丈島 | 14 | 15 | 12 | 20 | 21 | 19 |
気象庁 | 2023-11-13 17:00:00 +09:00 |
2023-11-15 00:00:00 +09:00 |
44301 | 父島 | 20 | 21 | 19 | 25 | 27 | 24 |
個人的にJSONAccessでcross apply
やouter apply
を使って、より下位の要素にアクセスできるのが便利だと思います。
apply
の使用例:
select
A.ListIndex as 地域区分番号
,B.ListIndex as 時間帯番号
,B.ItemValue.AsString as 天候分類コード
from
dbo.CLR_JSONAccess_JSONValueList(@三日天気予報.Member('areas')) A
outer apply
dbo.CLR_JSONAccess_JSONValueList(A.ItemValue.Member('weatherCodes')) B
上記の例に該当するJSONの構造:
[
{
"publishingOffice": "気象庁",
"reportDatetime": "2021-03-06T17:00:00+09:00",
"timeSeries": [
{ "//": "= [@三日天気予報]の保持する位置",
"timeDefines": [
"2021-03-06T17:00:00+09:00",
"2021-03-07T00:00:00+09:00",
"2021-03-08T00:00:00+09:00"
],
"areas": [ "= [A.ListIndex]はこの配列の中の要素の番号]"
{ "//": "= [A.ItemValue]の保持する位置",
"area": {
"name": "東京地方",
"code": "130010"
},
"weatherCodes": [
"= [B.ListIndex]はこの配列の中の要素の番号]"
"111", "= [B.ItemValue]の保持する位置"
"201",
"200"
],
"以下":"略"
}]
}]
}
}
最後に
JSONAccessライブラリ、WebAccessライブラリは現段階(2023/11/14)では未公開のライブラリですが、そのうち公開するつもり(商品として販売になるかもしれませんが)です。
公開した際にはこの記事に情報を追加します。
2023/12/26 追記
JSONAccessライブラリ、WebAccessライブラリを含むライブラリを販売することになりました。
- JSONAccessライブラリ
- WebAccessライブラリ
おまけ
上記のジオコーディングでJSONが返ってくるので、それをJSONAccessライブラリで処理した例を示します。
declare @urlbase nvarchar(max);
declare @url nvarchar(max);
declare @resultstr nvarchar(max);
declare @address nvarchar(max);
set @address='東京都文京区1-1';
set @urlbase='https://msearch.gsi.go.jp/';
set @url=dbo.CLR_WebAccess_ConcatPartToURL(
@urlbase
,'/address-search/AddressSearch');
set @url=dbo.CLR_WebAccess_ConcatArgToURL(
@url
,'q'
, dbo.CLR_WebAccess_EscapeUriString(@address));
select
@address as 対象住所
, dbo.CLR_WebAccess_EscapeUriString(@address) as [エスケープ処理後住所];
select @url as 対象URL;
if object_id('tempdb..#テスト', N'U') is not null
drop table #テスト;
select * into #テスト from dbo.CLR_WebAccess_GetResultFromURL(@url);
select * from #テスト;
select @resultstr=A.result from #テスト A;
select @resultstr as 検索結果;
-- JSONをGeography型に変換処理
-- APIの答えのJSONからCLR_JSONAccess_JSONValue型を作成
declare @json dbo.CLR_JSONAccess_JSONValue
=cast(@resultstr as dbo.CLR_JSONAccess_JSONValue);
-- 座標部分を取り出す
declare @coor dbo.CLR_JSONAccess_JSONValue
=@json.Item(0).Member('geometry').Member('coordinates');
-- 座標の緯度経度からgeography型を作成
declare @pt geography
=geography::Point(
@coor.Item(1).AsDecimal
,@coor.Item(0).AsDecimal
,4326);
-- geography型の内容を表示
select @pt.STAsText() as [文京区1-1の緯度経度座標];
-- JSONデータからJSONのオブジェクトを作成
declare @locatejson dbo.CLR_JSONAccess_JSONValue = cast(
'{
"Tokyo": {
"coordinates": [139.691710, 35.689500]
, "srid": 4326
}
, "London": {
"coordinates": [-0.125740, 51.508530]
, "srid": 4326
}
, "NewYork": {
"coordinates": [-75.499900 43.000350]
, "srid": 4326
}
}' as dbo.CLR_JSONAccess_JSONValue);
-- 副クエリの中でgeography型を作成し、その点から@ptまでの距離を求める
select
locate.Id
, locate.Name
, locate.geographicalpoint.STAsText() as point
, locate.geographicalpoint.STDistance(@pt) / 1000 as distanceKM
from (
SELECT
A.ListIndex as ID
,A.ItemName as Name
,geography::Point(
A.ItemValue.Member('coordinates').Item(1).AsDecimal
,A.ItemValue.Member('coordinates').Item(0).AsDecimal
,A.ItemValue.Member('srid').AsInt
) as geographicalpoint
FROM
dbo.CLR_JSONAccess_JSONValueList(@locatejson) A
) locate;
drop table #テスト;
こっちの方が、Geography型の変換もあって、面白かったかも。