SQL Server 2019 CLR with Azure Cognitive Services Using C#.NET

Jiayi Yang Azure • C#.Net • CLR • Cognitive Services • ML • Python • SQL Server 2019年3月7日

--Clr enabled
exec sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
exec sp_configure 'clr enabled', 1;  
GO  
RECONFIGURE;  
GO  

USE [master]
GO

--Check clr strict security
SELECT * FROM sys.configurations WHERE name LIKE 'clr strict security';

--Create Demo database
CREATE DATABASE [AzureFace]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'AzureFace', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AzureFace.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'AzureFace_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AzureFace_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [AzureFace] SET COMPATIBILITY_LEVEL = 150
GO
ALTER DATABASE [AzureFace] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [AzureFace] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [AzureFace] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [AzureFace] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [AzureFace] SET ARITHABORT OFF 
GO
ALTER DATABASE [AzureFace] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [AzureFace] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [AzureFace] SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF)
GO
ALTER DATABASE [AzureFace] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [AzureFace] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [AzureFace] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [AzureFace] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [AzureFace] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [AzureFace] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [AzureFace] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [AzureFace] SET  DISABLE_BROKER 
GO
ALTER DATABASE [AzureFace] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [AzureFace] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [AzureFace] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [AzureFace] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [AzureFace] SET  READ_WRITE 
GO
ALTER DATABASE [AzureFace] SET RECOVERY FULL 
GO
ALTER DATABASE [AzureFace] SET  MULTI_USER 
GO
ALTER DATABASE [AzureFace] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [AzureFace] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
ALTER DATABASE [AzureFace] SET DELAYED_DURABILITY = DISABLED 
GO
USE [AzureFace]
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = Off;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = Primary;
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = On;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = Primary;
GO
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = Off;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = Primary;
GO
USE [AzureFace]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [AzureFace] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO


--Create Asymmetric key
USE [master]
GO

--DROP ASYMMETRIC KEY AzureFaceKey

CREATE ASYMMETRIC KEY AzureFaceKey
FROM EXECUTABLE FILE = 'C:\SQLClrDemo\AzureFace\AzureFace\bin\Debug\AzureFace.dll'
GO

--Check Asymmetric key exists.
select *
from sys.asymmetric_keys

--Create login from Asymmetric key
CREATE LOGIN AzureFaceKeyLogin
FROM ASYMMETRIC KEY AzureFaceKey 
GO

--Grant access
--If not grant to UNSAFE,get this error.
--So must grant to UNSAFE by first.

--Msg 10327, Level 14, State 1, Line 143
--CREATE ASSEMBLY for assembly 'AzureFace' failed because assembly 'AzureFace' is not trusted. 
--The assembly is trusted when either of the following is true: the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission, 
--or the assembly is trusted using sp_add_trusted_assembly.
USE [master]
GO

GRANT UNSAFE ASSEMBLY TO AzureFaceKeyLogin
GO

--Create User from login
USE [AzureFace]
GO

CREATE USER AzureFaceKeyLogin FOR LOGIN AzureFaceKeyLogin
GO

--If permission set to SAFE,get this SecurityException error.
--Msg 6522, Level 16, State 1, Line 46
--A .NET Framework error occurred during execution of user-defined routine or aggregate "Fn_AzureFace": 
--System.Security.SecurityException: Request for the permission of type 'System.Net.WebPermission, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
--System.Security.SecurityException: 
--   at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
--   at System.Security.CodeAccessPermission.Demand()
--   at System.Net.HttpWebRequest.CheckConnectPermission(Uri uri, Boolean needExecutionContext)
--   at System.Net.HttpWebRequest..ctor(Uri uri, ServicePoint servicePoint)
--   at System.Net.HttpRequestCreator.Create(Uri Uri)
--   at System.Net.WebRequest.Create(Uri requestUri, Boolean useUriBase)
--   at UserDefinedFunctions.Fn_AzureFace(String _FaceAPIUrl, String _FaceAPIKey, String _ImageUrl)

--CREATE ASSEMBLY AzureFace FROM 'C:\SQLClrDemo\AzureFace\AzureFace\bin\Debug\AzureFace.dll' WITH PERMISSION_SET = SAFE;
--GO

CREATE ASSEMBLY [AzureFace] FROM 'C:\SQLClrDemo\AzureFace\AzureFace\bin\Debug\AzureFace.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

--Change UNSAFE to External access
--Can use SAFE/EXTERNAL/UNSAFE
USE [master]
GO

GRANT EXTERNAL ACCESS ASSEMBLY TO AzureFaceKeyLogin
GO

USE [AzureFace]
GO
select *
from sys.assembly_files
go

--Create function
USE [AzureFace]
GO

CREATE FUNCTION [dbo].[Fn_AzureFace](@FaceAPIUrl nvarchar(max),@FaceAPIKey nvarchar(max),@ImageUrl nvarchar(max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [AzureFace].[UserDefinedFunctions].[Fn_AzureFace]
GO

DECLARE @strFaceAPIEndpoint nvarchar(max) = 'https://eastus.api.cognitive.microsoft.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
DECLARE @strFaceAPIKey nvarchar(max) = '<your_APIKey>'
DECLARE @strImageUrl nvarchar(max) = 'https://www.mavnmodels.com/wp-content/uploads/2018/07/97404b293a78eb40740838d2e3938b9a-700-1250x500_t.jpg'

--SELECT [AzureFace].[dbo].Fn_AzureFace(@strFaceAPIUrl,@strFaceAPIKey,@strImageUrl) as AzureFaceAPIResults

--DECLARE @JSONDocument nvarchar(4000) = (SELECT [AzureFace].[dbo].Fn_AzureFace(@strFaceAPIUrl,@strFaceAPIKey,@strImageUrl))
--SELECT ISJSON(@JSONDocument) as IsValidJSON
 
SELECT * FROM OPENJSON(replace((SELECT [AzureFace].[dbo].Fn_AzureFace(@strFaceAPIUrl,@strFaceAPIKey,@strImageUrl)), '''', '"'))
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'
)

--Delete Login and Asymmetric key
DROP LOGIN AzureFaceKeyLogin
GO

DROP ASYMMETRIC KEY AzureFaceKey
GO

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'AzureFace'
GO
use [AzureFace];
GO
use [master];
GO
USE [master]
GO
ALTER DATABASE [AzureFace] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO

DROP DATABASE [AzureFace]
GO

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

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

--Clr enabled

exec sp_configure 'show advanced options', 1;

RECONFIGURE;

exec sp_configure 'clr enabled', 1;

RECONFIGURE;

USE [master]

--Check clr strict security

SELECT * FROM sys.configurations WHERE name LIKE 'clr strict security';

--Create Demo database

CREATE DATABASE [AzureFace]

CONTAINMENT = NONE

ON PRIMARY

( NAME = N'AzureFace', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AzureFace.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )

LOG ON

( NAME = N'AzureFace_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AzureFace_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )

ALTER DATABASE [AzureFace] SET COMPATIBILITY_LEVEL = 150

ALTER DATABASE [AzureFace] SET ANSI_NULL_DEFAULT OFF

ALTER DATABASE [AzureFace] SET ANSI_NULLS OFF

ALTER DATABASE [AzureFace] SET ANSI_PADDING OFF

ALTER DATABASE [AzureFace] SET ANSI_WARNINGS OFF

ALTER DATABASE [AzureFace] SET ARITHABORT OFF

ALTER DATABASE [AzureFace] SET AUTO_CLOSE OFF

ALTER DATABASE [AzureFace] SET AUTO_SHRINK OFF

ALTER DATABASE [AzureFace] SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF)

ALTER DATABASE [AzureFace] SET AUTO_UPDATE_STATISTICS ON

ALTER DATABASE [AzureFace] SET CURSOR_CLOSE_ON_COMMIT OFF

ALTER DATABASE [AzureFace] SET CURSOR_DEFAULT GLOBAL

ALTER DATABASE [AzureFace] SET CONCAT_NULL_YIELDS_NULL OFF

ALTER DATABASE [AzureFace] SET NUMERIC_ROUNDABORT OFF

ALTER DATABASE [AzureFace] SET QUOTED_IDENTIFIER OFF

ALTER DATABASE [AzureFace] SET RECURSIVE_TRIGGERS OFF

ALTER DATABASE [AzureFace] SET DISABLE_BROKER

ALTER DATABASE [AzureFace] SET AUTO_UPDATE_STATISTICS_ASYNC OFF

ALTER DATABASE [AzureFace] SET DATE_CORRELATION_OPTIMIZATION OFF

ALTER DATABASE [AzureFace] SET PARAMETERIZATION SIMPLE

ALTER DATABASE [AzureFace] SET READ_COMMITTED_SNAPSHOT OFF

ALTER DATABASE [AzureFace] SET READ_WRITE

ALTER DATABASE [AzureFace] SET RECOVERY FULL

ALTER DATABASE [AzureFace] SET MULTI_USER

ALTER DATABASE [AzureFace] SET PAGE_VERIFY CHECKSUM

ALTER DATABASE [AzureFace] SET TARGET_RECOVERY_TIME = 60 SECONDS

ALTER DATABASE [AzureFace] SET DELAYED_DURABILITY = DISABLED

USE [AzureFace]

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = Off;

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = Primary;

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = On;

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = Primary;

ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = Off;

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = Primary;

USE [AzureFace]

IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [AzureFace] MODIFY FILEGROUP [PRIMARY] DEFAULT

--Create Asymmetric key

USE [master]

--DROP ASYMMETRIC KEY AzureFaceKey

CREATE ASYMMETRIC KEY AzureFaceKey

FROM EXECUTABLE FILE = 'C:\SQLClrDemo\AzureFace\AzureFace\bin\Debug\AzureFace.dll'

--Check Asymmetric key exists.

select *

from sys.asymmetric_keys

--Create login from Asymmetric key

CREATE LOGIN AzureFaceKeyLogin

FROM ASYMMETRIC KEY AzureFaceKey

--Grant access

--If not grant to UNSAFE,get this error.

--So must grant to UNSAFE by first.

--Msg 10327, Level 14, State 1, Line 143

--CREATE ASSEMBLY for assembly 'AzureFace' failed because assembly 'AzureFace' is not trusted.

--The assembly is trusted when either of the following is true: the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission,

--or the assembly is trusted using sp_add_trusted_assembly.

USE [master]

GRANT UNSAFE ASSEMBLY TO AzureFaceKeyLogin

--Create User from login

USE [AzureFace]

CREATE USER AzureFaceKeyLogin FOR LOGIN AzureFaceKeyLogin

--If permission set to SAFE,get this SecurityException error.

--Msg 6522, Level 16, State 1, Line 46

--A .NET Framework error occurred during execution of user-defined routine or aggregate "Fn_AzureFace":

--System.Security.SecurityException: Request for the permission of type 'System.Net.WebPermission, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

--System.Security.SecurityException:

-- at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

-- at System.Security.CodeAccessPermission.Demand()

-- at System.Net.HttpWebRequest.CheckConnectPermission(Uri uri, Boolean needExecutionContext)

-- at System.Net.HttpWebRequest..ctor(Uri uri, ServicePoint servicePoint)

-- at System.Net.HttpRequestCreator.Create(Uri Uri)

-- at System.Net.WebRequest.Create(Uri requestUri, Boolean useUriBase)

-- at UserDefinedFunctions.Fn_AzureFace(String _FaceAPIUrl, String _FaceAPIKey, String _ImageUrl)

--CREATE ASSEMBLY AzureFace FROM 'C:\SQLClrDemo\AzureFace\AzureFace\bin\Debug\AzureFace.dll' WITH PERMISSION_SET = SAFE;

--GO

CREATE ASSEMBLY [AzureFace] FROM 'C:\SQLClrDemo\AzureFace\AzureFace\bin\Debug\AzureFace.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS;

--Change UNSAFE to External access

--Can use SAFE/EXTERNAL/UNSAFE

USE [master]

GRANT EXTERNAL ACCESS ASSEMBLY TO AzureFaceKeyLogin

USE [AzureFace]

select *

from sys.assembly_files

--Create function

USE [AzureFace]

CREATE FUNCTION [dbo].[Fn_AzureFace](@FaceAPIUrl nvarchar(max),@FaceAPIKey nvarchar(max),@ImageUrl nvarchar(max))

RETURNS [nvarchar](max) WITH EXECUTE AS CALLER

EXTERNAL NAME [AzureFace].[UserDefinedFunctions].[Fn_AzureFace]

DECLARE @strFaceAPIEndpoint nvarchar(max) = 'https://eastus.api.cognitive.microsoft.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

DECLARE @strFaceAPIKey nvarchar(max) = '<your_APIKey>'

DECLARE @strImageUrl nvarchar(max) = 'https://www.mavnmodels.com/wp-content/uploads/2018/07/97404b293a78eb40740838d2e3938b9a-700-1250x500_t.jpg'

--SELECT [AzureFace].[dbo].Fn_AzureFace(@strFaceAPIUrl,@strFaceAPIKey,@strImageUrl) as AzureFaceAPIResults

--DECLARE @JSONDocument nvarchar(4000) = (SELECT [AzureFace].[dbo].Fn_AzureFace(@strFaceAPIUrl,@strFaceAPIKey,@strImageUrl))

--SELECT ISJSON(@JSONDocument) as IsValidJSON

SELECT * FROM OPENJSON(replace((SELECT [AzureFace].[dbo].Fn_AzureFace(@strFaceAPIUrl,@strFaceAPIKey,@strImageUrl)), '''', '"'))

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'

)

--Delete Login and Asymmetric key

DROP LOGIN AzureFaceKeyLogin

DROP ASYMMETRIC KEY AzureFaceKey

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'AzureFace'

use [AzureFace];

use [master];

USE [master]

ALTER DATABASE [AzureFace] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

USE [master]

DROP DATABASE [AzureFace]

USE [AzureFace]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PersonFaceInfo](
	[faceid] [uniqueidentifier] NOT NULL,
	[faceAttributes_gender] [varchar](20) NOT NULL,
	[faceAttributes_smile] [float] NOT NULL,
	[faceAttributes_age] [float] NOT NULL,
	[faceAttributes_glasses] [varchar](20) NOT NULL,
	[faceAttributes_emotion_anger] [float] NOT NULL,
	[faceAttributes_emotion_contempt] [float] NOT NULL,
	[faceAttributes_emotion_disgust] [float] NOT NULL,
	[faceAttributes_emotion_fear] [float] NOT NULL,
	[faceAttributes_emotion_happiness] [float] NOT NULL,
	[faceAttributes_emotion_neutral] [float] NOT NULL,
	[faceAttributes_emotion_sadness] [float] NOT NULL,
	[faceAttributes_emotion_surprise] [float] NOT NULL,
	[faceRectangle_Top] [int] NOT NULL,
	[faceRectangle_left] [int] NOT NULL,
	[faceRectangle_width] [int] NOT NULL,
	[faceRectangle_height] [int] NOT NULL,
 CONSTRAINT [PK_PersonFaceInfo] PRIMARY KEY CLUSTERED 
(
	[faceid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

USE [AzureFace]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[PersonFaceInfo](

[faceid] [uniqueidentifier] NOT NULL,

[faceAttributes_gender] [varchar](20) NOT NULL,

[faceAttributes_smile] [float] NOT NULL,

[faceAttributes_age] [float] NOT NULL,

[faceAttributes_glasses] [varchar](20) NOT NULL,

[faceAttributes_emotion_anger] [float] NOT NULL,

[faceAttributes_emotion_contempt] [float] NOT NULL,

[faceAttributes_emotion_disgust] [float] NOT NULL,

[faceAttributes_emotion_fear] [float] NOT NULL,

[faceAttributes_emotion_happiness] [float] NOT NULL,

[faceAttributes_emotion_neutral] [float] NOT NULL,

[faceAttributes_emotion_sadness] [float] NOT NULL,

[faceAttributes_emotion_surprise] [float] NOT NULL,

[faceRectangle_Top] [int] NOT NULL,

[faceRectangle_left] [int] NOT NULL,

[faceRectangle_width] [int] NOT NULL,

[faceRectangle_height] [int] NOT NULL,

CONSTRAINT [PK_PersonFaceInfo] PRIMARY KEY CLUSTERED

(

[faceid] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

CREATE PROCEDURE [dbo].[usp_GetRandomPicUrl] (
@strSearchKeyWord nvarchar(max),
@output nvarchar(max) OUTPUT)
AS
EXEC sp_execute_external_script
 @language = N'Python'
,@script = N'
import os
from urllib import request as req
from urllib import error
from urllib import parse
import bs4

keyword = inputvalue
urlKeyword = parse.quote(keyword)
url = ''https://www.google.com/search?hl=jp&q='' + urlKeyword + ''&btnG=Google+Search&tbs=0&safe=off&tbm=isch''
headers = {"User-Agent": "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:47.0) Gecko/20100101 Firefox/47.0",}
request = req.Request(url=url, headers=headers)
page = req.urlopen(request)
html = page.read().decode(''utf-8'')
html = bs4.BeautifulSoup(html, "html.parser")
elems = html.select(''.rg_meta.notranslate'')
counter = 0
outputbin = ""
for ele in elems:
    ele = ele.contents[0].replace(''\"'','''').split('','')
    eledict = dict()
    for e in ele:
        num = e.find('':'')
        eledict[e[0:num]] = e[num+1:]
    imageURL = eledict[''ou'']
    try:
        outputbin = outputbin + "|" + imageURL + "|"
    except UnicodeEncodeError:
        continue
    except error.HTTPError:
        continue
    except error.URLError:
        continue
outputbin = outputbin[1:]
outputbin = outputbin[:-1]
',
@params = N'@inputvalue nvarchar(max),@outputbin nvarchar(max) output',
@inputvalue = @strSearchKeyWord,
@outputbin = @output OUTPUT
GO

CREATE PROCEDURE [dbo].[usp_GetRandomPicUrl] (

@strSearchKeyWord nvarchar(max),

@output nvarchar(max) OUTPUT)

EXEC sp_execute_external_script

@language = N'Python'

,@script = N'

import os

from urllib import request as req

from urllib import error

from urllib import parse

import bs4

keyword = inputvalue

urlKeyword = parse.quote(keyword)

url = ''https://www.google.com/search?hl=jp&q='' + urlKeyword + ''&btnG=Google+Search&tbs=0&safe=off&tbm=isch''

headers = {"User-Agent": "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:47.0) Gecko/20100101 Firefox/47.0",}

request = req.Request(url=url, headers=headers)

page = req.urlopen(request)

html = page.read().decode(''utf-8'')

html = bs4.BeautifulSoup(html, "html.parser")

elems = html.select(''.rg_meta.notranslate'')

counter = 0

outputbin = ""

for ele in elems:

ele = ele.contents[0].replace(''\"'','''').split('','')

eledict = dict()

for e in ele:

num = e.find('':'')

eledict[e[0:num]] = e[num+1:]

imageURL = eledict[''ou'']

try:

outputbin = outputbin + "|" + imageURL + "|"

except UnicodeEncodeError:

continue

except error.HTTPError:

continue

except error.URLError:

continue

outputbin = outputbin[1:]

outputbin = outputbin[:-1]

@params = N'@inputvalue nvarchar(max),@outputbin nvarchar(max) output',

@inputvalue = @strSearchKeyWord,

@outputbin = @output OUTPUT

USE [AzureFace]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[Function_StrSplit]
(
	@RowData varchar(4000),
	@SplitOn varchar(5)
)
RETURNS @RtnValue table
(
	Id int identity(1,1),
	Data varchar(1000)
)
AS
BEGIN
	--@SplitOn in @RowData
	WHILE (CHARINDEX(@SplitOn,@RowData)>0)
	BEGIN
		--Insert the first data into @RtnValue table
		INSERT INTO @RtnValue(Data)
			SELECT Data = LTRIM(RTRIM(SUBSTRING(@RowData,1,CHARINDEX(@SplitOn,@RowData)-1)))
		
		--Get the next @RowData
		SET @RowData = SUBSTRING(@RowData,CHARINDEX(@SplitOn,@RowData)+LEN(@SplitOn),LEN(@RowData))
	END
	
	--No @SplitOn
	INSERT INTO @RtnValue (Data)
		SELECT Data = LTRIM(RTRIM(@RowData))
			
	RETURN
END


GO

USE [AzureFace]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE FUNCTION [dbo].[Function_StrSplit]

(

@RowData varchar(4000),

@SplitOn varchar(5)

)

RETURNS @RtnValue table

(

Id int identity(1,1),

Data varchar(1000)

)

BEGIN

--@SplitOn in @RowData

WHILE (CHARINDEX(@SplitOn,@RowData)>0)

BEGIN

--Insert the first data into @RtnValue table

INSERT INTO @RtnValue(Data)

SELECT Data = LTRIM(RTRIM(SUBSTRING(@RowData,1,CHARINDEX(@SplitOn,@RowData)-1)))

--Get the next @RowData

SET @RowData = SUBSTRING(@RowData,CHARINDEX(@SplitOn,@RowData)+LEN(@SplitOn),LEN(@RowData))

END

--No @SplitOn

INSERT INTO @RtnValue (Data)

SELECT Data = LTRIM(RTRIM(@RowData))

RETURN

END

USE [AzureFace]
GO

DECLARE @strSearchKeyWord nvarchar(max) = 'female face model'
DECLARE @outputUrl nvarchar(max) = ''
DECLARE @tblFaceUrl table (FaceUrl varchar(max))
DECLARE @strFaceUrl varchar(max) = ''

DECLARE @strFaceAPIEndpoint nvarchar(max) = 'https://eastus.api.cognitive.microsoft.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
DECLARE @strFaceAPIKey nvarchar(max) = '<your_APIKey>'

EXECUTE [dbo].[usp_GetRandomPicUrl] 
   @strSearchKeyWord
  ,@output = @outputUrl OUTPUT

INSERT INTO @tblFaceUrl([FaceUrl])
SELECT [data] FROM [dbo].[Function_StrSplit](@outputUrl,'|')
WHERE [data] != ''
AND RIGHT([data],4) = '.jpg' 

IF EXISTS(SELECT 1 FROM master.dbo.syscursors WHERE cursor_name = 'cur_faceurl') DEALLOCATE cur_faceurl
DECLARE cur_faceurl CURSOR FOR
SELECT [FaceUrl]
FROM @tblFaceUrl
		
OPEN cur_faceurl
FETCH NEXT FROM cur_faceurl INTO @strFaceUrl

WHILE (@@FETCH_STATUS = 0 and @@ERROR = 0)
		BEGIN
			INSERT INTO [PersonFaceInfo](
			[faceid],
			[faceAttributes_gender],
			[faceAttributes_smile],
			[faceAttributes_age],
			[faceAttributes_glasses],
			[faceAttributes_emotion_anger],
			[faceAttributes_emotion_contempt],
			[faceAttributes_emotion_disgust],
			[faceAttributes_emotion_fear],
			[faceAttributes_emotion_happiness],
			[faceAttributes_emotion_neutral],
			[faceAttributes_emotion_sadness],
			[faceAttributes_emotion_surprise],
			[faceRectangle_Top],
			[faceRectangle_left],
			[faceRectangle_width],
			[faceRectangle_height])
			SELECT 
			[faceid],
			[faceAttributes_gender],
			[faceAttributes_smile],
			[faceAttributes_age],
			[faceAttributes_glasses],
			[faceAttributes_emotion_anger],
			[faceAttributes_emotion_contempt],
			[faceAttributes_emotion_disgust],
			[faceAttributes_emotion_fear],
			[faceAttributes_emotion_happiness],
			[faceAttributes_emotion_neutral],
			[faceAttributes_emotion_sadness],
			[faceAttributes_emotion_surprise],
			[faceRectangle_Top],
			[faceRectangle_left],
			[faceRectangle_width],
			[faceRectangle_height]
			FROM OPENJSON(replace((SELECT [AzureFace].[dbo].Fn_AzureFace(@strFaceAPIUrl,@strFaceAPIKey,@strFaceUrl)), '''', '"'))
			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')

			FETCH NEXT FROM cur_faceurl INTO @strFaceUrl
		END

IF EXISTS(SELECT 1 FROM master.dbo.syscursors WHERE cursor_name = 'cur_faceurl') 
		BEGIN
			CLOSE cur_faceurl
			DEALLOCATE cur_faceurl
		END

USE [AzureFace]

DECLARE @strSearchKeyWord nvarchar(max) = 'female face model'

DECLARE @outputUrl nvarchar(max) = ''

DECLARE @tblFaceUrl table (FaceUrl varchar(max))

DECLARE @strFaceUrl varchar(max) = ''

DECLARE @strFaceAPIEndpoint nvarchar(max) = 'https://eastus.api.cognitive.microsoft.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

DECLARE @strFaceAPIKey nvarchar(max) = '<your_APIKey>'

EXECUTE [dbo].[usp_GetRandomPicUrl]

@strSearchKeyWord

,@output = @outputUrl OUTPUT

INSERT INTO @tblFaceUrl([FaceUrl])

SELECT [data] FROM [dbo].[Function_StrSplit](@outputUrl,'|')

WHERE [data] != ''

AND RIGHT([data],4) = '.jpg'

IF EXISTS(SELECT 1 FROM master.dbo.syscursors WHERE cursor_name = 'cur_faceurl') DEALLOCATE cur_faceurl

DECLARE cur_faceurl CURSOR FOR

SELECT [FaceUrl]

FROM @tblFaceUrl

OPEN cur_faceurl

FETCH NEXT FROM cur_faceurl INTO @strFaceUrl

WHILE (@@FETCH_STATUS = 0 and @@ERROR = 0)

BEGIN

INSERT INTO [PersonFaceInfo](

[faceid],

[faceAttributes_gender],

[faceAttributes_smile],

[faceAttributes_age],

[faceAttributes_glasses],

[faceAttributes_emotion_anger],

[faceAttributes_emotion_contempt],

[faceAttributes_emotion_disgust],

[faceAttributes_emotion_fear],

[faceAttributes_emotion_happiness],

[faceAttributes_emotion_neutral],

[faceAttributes_emotion_sadness],

[faceAttributes_emotion_surprise],

[faceRectangle_Top],

[faceRectangle_left],

[faceRectangle_width],

[faceRectangle_height])

SELECT

[faceid],

[faceAttributes_gender],

[faceAttributes_smile],

[faceAttributes_age],

[faceAttributes_glasses],

[faceAttributes_emotion_anger],

[faceAttributes_emotion_contempt],

[faceAttributes_emotion_disgust],

[faceAttributes_emotion_fear],

[faceAttributes_emotion_happiness],

[faceAttributes_emotion_neutral],

[faceAttributes_emotion_sadness],

[faceAttributes_emotion_surprise],

[faceRectangle_Top],

[faceRectangle_left],

[faceRectangle_width],

[faceRectangle_height]

FROM OPENJSON(replace((SELECT [AzureFace].[dbo].Fn_AzureFace(@strFaceAPIUrl,@strFaceAPIKey,@strFaceUrl)), '''', '"'))