Using T-SQL Query to access WEB API and EXCEL Sheet

Jiayi Yang OLE Automation • OLE DB • SQL Server 2019年11月12日

/*************************
OLEDB EXCEL WorkSheet
*************************/

--これは、サーバー内の既存のOLEDBプロバイダーを操作するのに役立つMicrosoft SQL Serverのストアドプロシージャです。 
--この例では、サーバーに現在あるすべてのプロバイダーを取得しています。 この時点では操作はありません。 このストアドプロシージャは、マスタデータベースの下に存在します。
EXEC sp_MSset_oledb_prop
GO

--アフィニティマスクや回復間隔などの一部の構成オプションは、詳細オプションとして指定されています。 
--デフォルトでは、これらのオプションは表示および変更には使用できません。 
--それらを使用可能にするには、ShowAdvancedOptions構成オプションを1に設定します。
--今回の場合、高度なオプションである「Ad Hoc Distributed Queries」を表示します。
EXEC sp_configure 'Show Advanced Options', 1
RECONFIGURE 
GO

--既定では、SQL ServerはOPENROWSETおよびOPENDATASOURCEを使用したアドホック分散クエリを許可しません。 
--このオプションを1に設定すると、SQL Serverはアドホックアクセスを許可します。 
--このオプションが設定されていないか、0に設定されている場合、SQL Serverはアドホックアクセスを許可しません。 
--アドホック分散クエリは、OPENROWSETおよびOPENDATASOURCE関数を使用して、OLE DBを使用するリモートデータソースに接続します。 
--OPENROWSETおよびOPENDATASOURCEは、頻繁にアクセスされないOLE DBデータソースを参照するためにのみ使用してください。 
--複数回アクセスされるデータソースについては、リンクサーバーを定義します。
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO

--[AllowInProcess]を選択すると、SQL Serverはプロバイダーのインスタンス化を許可するか、プロバイダーをIn Processサーバーとして実行できるようにします。 
--このオプションが設定されていない場合、デフォルトの動作では、プロバイダーはSQL Serverプロセスの外部で実行できます。
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'AllowInProcess', 1
GO

--パラメーター化されたクエリでSQLプレースホルダー（ '？'で表される）を許可します。
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'DynamicParameters', 1
GO

/*************************
OPENROWSET(T-SQL)を使用例
*************************/

--PersonTblのWorkSheetの表示
SELECT * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.16.0','Excel 12.0 Xml;
Database=C:\PolyBase_EXCEL\Person.xlsx',
[PersonTbl$])

--SalesTblのWorkSheetの表示
SELECT * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.16.0','Excel 12.0 Xml;
Database=C:\PolyBase_EXCEL\Person.xlsx', 
[SalesTbl$])

--Excel名前定義の表示
SELECT * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.16.0',
'Excel 12.0 Xml;
Database=C:\PolyBase_EXCEL\Person.xlsx',
NameList)

--指定したWorkSheetのカラム条件付きの表示
SELECT * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.16.0',
'Excel 12.0 Xml;
Database=C:\PolyBase_EXCEL\Person.xlsx',
'SELECT * FROM [SalesTbl$] WHERE Total > 30')

--複数WorkSheetでJoinして、さらにカラム条件付きの表示(名前定義も実行可能)
SELECT * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.16.0',
'Excel 12.0 Xml;
Database=C:\PolyBase_EXCEL\Person.xlsx',
'SELECT tbl2.[Name],tbl1.[Total]
FROM [SalesTbl$] as tbl1 
INNER JOIN [PersonTbl$] as tbl2
ON tbl1.[PersonID] = tbl2.[PersonID]
WHERE tbl1.Total > 30')

--Excelファイルを書き出す(SQL ServerのテーブルからExport)
INSERT INTO OPENROWSET(
'Microsoft.ACE.OLEDB.16.0',
'Excel 12.0 Xml;Database=C:\PolyBase_EXCEL\MyExport.xlsx', 
'SELECT * FROM [Sheet1$]')
SELECT * FROM [NorthwindJ].[dbo].[社員]

/*************************
OPENDATASOURCE(T-SQL)を使用例
*************************/
SELECT *
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.16.0',
'Extended Properties=Excel 12.0 Xml;
Data Source=C:\PolyBase_EXCEL\Person.xlsx')...[SalesTbl$]

/*************************
WEB API
*************************/

--アフィニティマスクや回復間隔などの一部の構成オプションは、詳細オプションとして指定されています。 
--デフォルトでは、これらのオプションは表示および変更には使用できません。 
--それらを使用可能にするには、ShowAdvancedOptions構成オプションを1に設定します。
--今回の場合、高度なオプションである「Ole Automation Procedures」を表示します。
EXEC sp_configure 'Show Advanced Options', 1
RECONFIGURE 
GO

--Ole Automation Proceduresオプションを使用して、OLEオートメーションオブジェクトをTransact-SQLバッチ内でインスタンス化できるかどうかを指定します。 
--このオプションは、ポリシーベースの管理またはsp_configureストアドプロシージャを使用して構成することもできます。
--Ole Automation Proceduresオプションは、次の値に設定できます。
--値：0
--定義：OLEオートメーションプロシージャは無効です。 SQL Serverの新しいインスタンスのデフォルト。
--値：1
--定義：OLEオートメーションプロシージャが有効になっています。
--OLEオートメーションプロシージャが有効な場合、sp_OACreateを呼び出すと、OLE共有実行環境が開始されます。 
--現在Ole Automation Proceduresオプションの値は、sp_configureシステムストアドプロシージャを使用して表示および変更できます。
EXEC sp_configure 'Ole Automation Procedures', 1
RECONFIGURE
GO

--変数定義
DECLARE @intToken INT
DECLARE @intRet INT
--PostHeaderの定義
DECLARE @strContentType NVARCHAR(64) = 'application/json'
DECLARE @strAuthHeader NVARCHAR(64) = 'Ocp-Apim-Subscription-Key'
--Azure Face API Key
DECLARE @strApiKey NVARCHAR(32) = '{Azure Face API Key}'
--Azure Face Web API Url
DECLARE @strFaceAPIEndpoint NVARCHAR(max) = 'https://jssugface.cognitiveservices.azure.com/face/v1.0'
DECLARE @strFaceAPIOption NVARCHAR(max) = '/detect?'
	+ 'returnFaceId=true'
	+ '&returnFaceLandmarks=false'
	+ '&returnFaceAttributes=age,gender,headPose,smile,facialHair,glasses,emotion,hair,makeup,occlusion,accessories,blur,exposure,noise'
DECLARE @strFaceAPIUrl NVARCHAR(max) = @strFaceAPIEndpoint + @strFaceAPIOption
--Urlの写真(分析対象)
DECLARE @strImageUrl NVARCHAR(max) = '{"url": "{url}.jpg"}'
--Jsonテーブル(分析した結果をExport用)
DECLARE @tblJson AS TABLE(Json_Table NVARCHAR(MAX))

--OLEオブジェクトのインスタンスの作成
EXEC @intRet = sp_OACreate 'MSXML2.XMLHTTP', @intToken OUT
IF @intRet <> 0 RAISERROR('Unable to open HTTP connection.', 10, 1)

--OLEオブジェクトのメソッドの設定
--Azure Face APIの場合は、Getではなく、Postにする必要
--仕様Url:https://westus.dev.cognitive.microsoft.com/docs/services/563879b61984550e40cbbe8d/operations/563879b61984550f30395236
EXEC @intRet = sp_OAMethod @intToken, 'open', NULL, 'POST', @strFaceAPIUrl, 'false'
--Ocp-Apim-Subscription-Keyプロパティの値に、ApiKeyを指定する
EXEC @intRet = sp_OAMethod @intToken, 'setRequestHeader', NULL, 'Ocp-Apim-Subscription-Key', @strApiKey
--Json形式を指定する
EXEC @intRet = sp_OAMethod @intToken, 'setRequestHeader', NULL, 'Content-type', @strContentType
--Send時にBodyの値にJson形式のイメージURLを指定する
EXEC @intRet = sp_OAMethod @intToken, 'send', NULL, @strImageUrl

--Sendした結果を一時テーブルに保存する
INSERT INTO @tblJson (Json_Table) EXEC sp_OAGetProperty @intToken, 'responseText'

--オブジェクトを廃棄する
EXEC @intRet = sp_OADestroy @intToken

--一時テーブルを確認する
SELECT * FROM @tblJson

--OPENJSONで列の形式を変換し、表示する
SELECT * FROM OPENJSON(replace((SELECT * FROM @tblJson), '''', '"'))
WITH(
faceid uniqueidentifier '$.faceId',
faceAttributes_gender varchar(20) '$.faceAttributes.gender',
faceAttributes_smile float '$.faceAttributes.smile',
faceAttributes_age float '$.faceAttributes.age',
faceAttributes_glasses varchar(20) '$.faceAttributes.glasses',
faceAttributes_emotion_anger float '$.faceAttributes.emotion.anger',
faceAttributes_emotion_contempt float '$.faceAttributes.emotion.contempt',
faceAttributes_emotion_disgust float '$.faceAttributes.emotion.disgust',
faceAttributes_emotion_fear float '$.faceAttributes.emotion.fear',
faceAttributes_emotion_happiness float '$.faceAttributes.emotion.happiness',
faceAttributes_emotion_neutral float '$.faceAttributes.emotion.neutral',
faceAttributes_emotion_sadness float '$.faceAttributes.emotion.sadness',
faceAttributes_emotion_surprise float '$.faceAttributes.emotion.surprise',
faceRectangle_Top int '$.faceRectangle.top',
faceRectangle_left int '$.faceRectangle.left',
faceRectangle_width int '$.faceRectangle.width',
faceRectangle_height int '$.faceRectangle.height'
)

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

/*************************

OLEDB EXCEL WorkSheet

*************************/

--これは、サーバー内の既存のOLEDBプロバイダーを操作するのに役立つMicrosoft SQL Serverのストアドプロシージャです。

--この例では、サーバーに現在あるすべてのプロバイダーを取得しています。この時点では操作はありません。このストアドプロシージャは、マスタデータベースの下に存在します。

EXEC sp_MSset_oledb_prop

--アフィニティマスクや回復間隔などの一部の構成オプションは、詳細オプションとして指定されています。

--デフォルトでは、これらのオプションは表示および変更には使用できません。

--それらを使用可能にするには、ShowAdvancedOptions構成オプションを1に設定します。

--今回の場合、高度なオプションである「Ad Hoc Distributed Queries」を表示します。

EXEC sp_configure 'Show Advanced Options', 1

RECONFIGURE

--既定では、SQL ServerはOPENROWSETおよびOPENDATASOURCEを使用したアドホック分散クエリを許可しません。

--このオプションを1に設定すると、SQL Serverはアドホックアクセスを許可します。

--このオプションが設定されていないか、0に設定されている場合、SQL Serverはアドホックアクセスを許可しません。

--アドホック分散クエリは、OPENROWSETおよびOPENDATASOURCE関数を使用して、OLE DBを使用するリモートデータソースに接続します。

--OPENROWSETおよびOPENDATASOURCEは、頻繁にアクセスされないOLE DBデータソースを参照するためにのみ使用してください。

--複数回アクセスされるデータソースについては、リンクサーバーを定義します。

EXEC sp_configure 'Ad Hoc Distributed Queries', 1

RECONFIGURE

--[AllowInProcess]を選択すると、SQL Serverはプロバイダーのインスタンス化を許可するか、プロバイダーをIn Processサーバーとして実行できるようにします。

--このオプションが設定されていない場合、デフォルトの動作では、プロバイダーはSQL Serverプロセスの外部で実行できます。

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'AllowInProcess', 1

--パラメーター化されたクエリでSQLプレースホルダー（ '？'で表される）を許可します。

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'DynamicParameters', 1

/*************************

OPENROWSET(T-SQL)を使用例

*************************/

--PersonTblのWorkSheetの表示

SELECT *

FROM OPENROWSET('Microsoft.ACE.OLEDB.16.0','Excel 12.0 Xml;