摘要：它主要是為了允許在中使用基于的機(jī)器學(xué)習(xí)，但是它可以與任何庫(kù)或框架一起使用。已經(jīng)通過(guò)啟用服務(wù)器通過(guò)使用的機(jī)器學(xué)習(xí)服務(wù)在中執(zhí)行腳本，添加到其高級(jí)分析擴(kuò)展，現(xiàn)在稱為機(jī)器學(xué)習(xí)服務(wù)。存儲(chǔ)的實(shí)例必須安裝具有的機(jī)器學(xué)習(xí)服務(wù)要在中使用執(zhí)行腳本，應(yīng)運(yùn)行或。

Python是SQL Server 2017的新功能。它主要是為了允許在SQL
Server中使用基于Python的機(jī)器學(xué)習(xí)，但是它可以與任何Python庫(kù)或框架一起使用。為了提供可能的例子，Hitendra展示了如何安全地使用該功能來(lái)提供智能應(yīng)用程序緩存，其中SQL
Server可以自動(dòng)指示數(shù)據(jù)何時(shí)更改以觸發(fā)緩存刷新。

MS SQL Server 2017已經(jīng)通過(guò)啟用SQL服務(wù)器通過(guò)“使用Python的機(jī)器學(xué)習(xí)服務(wù)”在TSQL中執(zhí)行Python腳本，添加到其高級(jí)分析擴(kuò)展，現(xiàn)在稱為“機(jī)器學(xué)習(xí)服務(wù)”。這基本上提供了一種數(shù)據(jù)庫(kù)程序員可以直接從Python傳遞數(shù)據(jù)的方法。這樣做的有用性不僅限于為數(shù)據(jù)分析提供機(jī)器學(xué)習(xí)功能，因?yàn)镻ython具有許多易于使用的模塊和框架來(lái)解決許多問(wèn)題，例如使用數(shù)據(jù)結(jié)構(gòu)執(zhí)行大量計(jì)算工作，用于分析的圖形處理，網(wǎng)絡(luò)操作，數(shù)據(jù)庫(kù)操作，網(wǎng)絡(luò)操作或基于本地/網(wǎng)絡(luò)的文件系統(tǒng)操作。顯然，其中許多在中間件方面做得最好，但是在數(shù)據(jù)庫(kù)系統(tǒng)中，有時(shí)候直接與外部系統(tǒng)通信，而不是依靠外部進(jìn)程通過(guò)輪詢數(shù)據(jù)源來(lái)執(zhí)行任務(wù)更方便。如果在數(shù)據(jù)庫(kù)或數(shù)據(jù)層中有一個(gè)這樣的解決方案，并且不提供任何安全性問(wèn)題時(shí)，這不是問(wèn)題。

在這里，我們將嘗試演示在Advanced Analytics Extension中使用Python的示例，顯示數(shù)據(jù)庫(kù)如何觸發(fā)外部進(jìn)程來(lái)對(duì)作為參數(shù)提供的數(shù)據(jù)執(zhí)行活動(dòng)。這是為了考慮安全性，數(shù)據(jù)可靠性和事務(wù)響應(yīng)時(shí)間的問(wèn)題。

通過(guò)從SQL調(diào)用Python腳本而不是依賴于中間件，可以更容易地完成某些任務(wù)。特別是在數(shù)據(jù)庫(kù)中事件發(fā)起任務(wù)的情況下。任務(wù)可能包括

通過(guò)TCP / HTTP / SOAP向網(wǎng)絡(luò)系統(tǒng)發(fā)送數(shù)據(jù)或從基于網(wǎng)絡(luò)的系統(tǒng)接收數(shù)據(jù)。

利用本地平臺(tái)資源，如文件系統(tǒng)，網(wǎng)絡(luò)或GPU。

通過(guò)使用通用數(shù)據(jù)格式（如JSON，XML或YAML）構(gòu)建一個(gè)或多個(gè)系統(tǒng)之間的實(shí)時(shí)集成。

通過(guò)與外部應(yīng)用程序通信生成數(shù)據(jù)或文件。

當(dāng)然也難免存在一些潛在的缺點(diǎn)

如果您使用Python需要互聯(lián)網(wǎng)訪問(wèn)，那么存在必須保持安全的數(shù)據(jù)可能會(huì)被意外地通過(guò)互聯(lián)網(wǎng)共享的風(fēng)險(xiǎn)。任何互聯(lián)網(wǎng)訪問(wèn)必須由網(wǎng)絡(luò)仔細(xì)監(jiān)管。

允許通過(guò)“啟用外部腳本執(zhí)行”在服務(wù)器上執(zhí)行Python腳本來(lái)暴露安全風(fēng)險(xiǎn)。

相同服務(wù)器上的資源密集型Python腳本可能會(huì)影響大型OLTP系統(tǒng)上正在進(jìn)行的事務(wù)的性能。

衡量這些優(yōu)點(diǎn)和缺點(diǎn)，似乎有時(shí)候Python可以發(fā)揮有用的作用，如果可以最小化風(fēng)險(xiǎn)。作為一個(gè)例子，讓我們考慮一下我們?nèi)绾问褂肞ython構(gòu)建數(shù)據(jù)緩存系統(tǒng)供應(yīng)用層使用。

緩存數(shù)據(jù)可以提高應(yīng)用程序的性能。以緩存的存儲(chǔ)開(kāi)銷為代價(jià)，當(dāng)遇到與數(shù)據(jù)庫(kù)的聊天網(wǎng)絡(luò)通信以及數(shù)據(jù)庫(kù)面臨重復(fù)查詢時(shí)資源消耗高的情況下，我們可以獲得有用的性能提升。當(dāng)我們構(gòu)建緩存基礎(chǔ)架構(gòu)時(shí)，我們面臨著什么時(shí)候刷新緩存的內(nèi)容的常見(jiàn)問(wèn)題。我們傾向于在一定時(shí)間間隔之后采用重建緩存的簡(jiǎn)單解決方案。然而，這是非常低效的。當(dāng)數(shù)據(jù)更改時(shí)刷新緩存更好，只刷新改變的內(nèi)容。在創(chuàng)建，更新或刪除數(shù)據(jù)時(shí)，我們可以實(shí)時(shí)接近實(shí)時(shí)。有許多工具和框架可用于解決刷新問(wèn)題，但是它們受到如何確定數(shù)據(jù)發(fā)生變化以及何時(shí)發(fā)生更改的問(wèn)題。數(shù)據(jù)庫(kù)是最好的所有能夠做到這一點(diǎn)。

對(duì)于我們這里提供的緩存系統(tǒng)，我們將把自己限制在微軟堆棧中，以防止Python本身。

Microsoft SQL Server 2017（CPT）

服務(wù)代理隔離事務(wù)數(shù)據(jù)庫(kù)。

Python執(zhí)行腳本，可以通過(guò)HTTP更新緩存（Python 3.5可執(zhí)行文件與Anaconda發(fā)行版中的庫(kù)）

.Net 4.5.2

ASP.Net MVC為我們的示例Web UI

ASP.Net WebAPI封裝緩存存儲(chǔ)為我們的示例解決方案。

以下是我們的示例解決方案緩存系統(tǒng)的圖示：

WebApplication提供用于讀取和更新數(shù)據(jù)的用戶界面。

我們示例緩存存儲(chǔ)解決方案中的RESTful.Cache應(yīng)用程序是使用ASP.Net WebAPI2構(gòu)建的，其內(nèi)容類型為JSON。 HTTP-GET操作從本地緩存（靜態(tài)集合）傳送數(shù)據(jù)。

MS SQL Server 2017（CPT）是一個(gè)數(shù)據(jù)庫(kù)服務(wù)器

TransDB OLTP數(shù)據(jù)庫(kù)，處理事務(wù)繁忙。

Cacher執(zhí)行Python腳本執(zhí)行的代理數(shù)據(jù)庫(kù)，啟用了啟用“外部腳本啟用”選項(xiàng)的腳本執(zhí)行。 請(qǐng)參閱Microsoft.Doc：?jiǎn)⒂猛獠磕_本的服務(wù)器配置選項(xiàng)。

Service Broker是SQL服務(wù)器的可靠消息傳遞框架，有助于橋接Cacher-Agent和TransDB。 可以處理Cacher-Agent接收到的消息來(lái)更新緩存。

Python是使用SQL 2017（CPT）的數(shù)據(jù)庫(kù)系統(tǒng)的集成腳本語(yǔ)言

在我們的解決方案中，我們將在RESTful.Cache應(yīng)用程序中緩存實(shí)體“產(chǎn)品類型名稱”，并且WebApplication將具有創(chuàng)建新產(chǎn)品類型條目并從RESTful.Cache讀取的功能。

除此之外，還有一些先決條件和一些我們需要考慮的信息。

存儲(chǔ)CacheDB的SQL實(shí)例必須安裝“具有Python的機(jī)器學(xué)習(xí)服務(wù)”

要在CacheDB中使用TSQL執(zhí)行Python腳本，應(yīng)運(yùn)行SQL Service MSSQLLaunchpad或SQL Server Launchpad。 請(qǐng)參閱Microsoft.Net：Microsoft機(jī)器學(xué)習(xí)服務(wù)

使用SP_Configure啟用外部腳本執(zhí)行，請(qǐng)參閱Microsoft.Doc：?jiǎn)⒂猛獠磕_本的服務(wù)器配置選項(xiàng)

sp_configure "external scripts enabled", 1; 

RECONFIGURE;

TransDB和Cacher托管的環(huán)境應(yīng)該在其實(shí)例上創(chuàng)建一個(gè)Service Broker端點(diǎn)，如果這兩個(gè)端點(diǎn)獨(dú)立托管在兩個(gè)不同的SQL實(shí)例上，那么每個(gè)實(shí)例應(yīng)該有自己的端點(diǎn)。

TransDB和Cacher數(shù)據(jù)庫(kù)應(yīng)該啟用代理。 請(qǐng)參閱Microsoft.Technet：如何：激活數(shù)據(jù)庫(kù)中的Service Broker消息傳遞.aspx)

ALTER DATABASE TransDB SET ENABLE_BROKER;

GO

ALTER DATABASE CacheDB SET ENABLE_BROKER;
GO

WebApplication有兩個(gè)主要的MVC動(dòng)作; 一個(gè)使用HTTP動(dòng)詞POST更新TransDB中的一個(gè)新實(shí)體，另一個(gè)使用HTTP動(dòng)詞GET從緩存返回產(chǎn)品類型列表的操作。

RESTful.Cache有兩種操作方法，一種是使用HTTP動(dòng)詞POST更新新添加的實(shí)體產(chǎn)品類型的緩存，另一種用于從本地緩存獲取所有緩存的產(chǎn)品類型。

對(duì)于我們的示例解決方案，這兩個(gè)應(yīng)用程序都在IIS中托管在各個(gè)應(yīng)用程序池標(biāo)識(shí)下，以保護(hù)應(yīng)用程序安全 但是對(duì)于實(shí)際的系統(tǒng)實(shí)現(xiàn)，托管環(huán)境可以是內(nèi)部網(wǎng)或互聯(lián)網(wǎng)環(huán)境中的單個(gè)Web服務(wù)器。

RESTful.Cache授權(quán)規(guī)則只有兩個(gè)服務(wù)帳戶來(lái)處理HTTP請(qǐng)求，即

abc WebApp_SVC和abc CacherAgent_SVC。 abc CacherAgent_SVC服務(wù)帳戶允許SQL中的Python腳本通過(guò)HTTP到達(dá)應(yīng)用程序來(lái)刷新緩存。

abc WebApp_SVC用戶用于具有授權(quán)規(guī)則模式的WebApplication，以允許訪問(wèn)RESTful.Cache應(yīng)用程序。

OLTP數(shù)據(jù)庫(kù)TransDB有幾個(gè)對(duì)象，包括表，存儲(chǔ)過(guò)程和Service Broker對(duì)象。

CacheIntegrationError表。

有關(guān)Service Broker的更多信息，請(qǐng)?jiān)L問(wèn)Microsoft.DOC：SQL Server Service Broker

對(duì)于我們的示例解決方案，TransDB是創(chuàng)建新的ProductType記錄時(shí)創(chuàng)建更新緩存消息的源數(shù)據(jù)庫(kù)，執(zhí)行操作的消息，它具有UpdateMessage消息類型，CacheIntegration合同將CacheSource服務(wù)發(fā)送到目標(biāo)數(shù)據(jù)庫(kù)。該服務(wù)具有CacheQueue，由Service Broker組件用于執(zhí)行可靠的消息傳遞。 ToCacheTarget路由具有將消息傳遞到其目標(biāo)的信息。

為了消除任何增加事務(wù)處理時(shí)間的機(jī)會(huì)以及避免事務(wù)數(shù)據(jù)庫(kù)中其余數(shù)據(jù)的任何安全風(fēng)險(xiǎn)，我們將通過(guò)使用我們的示例解決方案中名為Cacher數(shù)據(jù)庫(kù)的代理數(shù)據(jù)庫(kù)來(lái)解除緩存更新過(guò)程。 Service Broker消息傳遞基礎(chǔ)設(shè)施將有助于連接TransDB和Cacher數(shù)據(jù)庫(kù)，基于事件的消息處理將使我們能夠更新駐留在基于網(wǎng)絡(luò)的系統(tǒng)上的緩存存儲(chǔ)。 Cacher數(shù)據(jù)庫(kù)正在扮演代理角色，以便在更新消息到達(dá)時(shí)執(zhí)行緩存刷新。它通過(guò)執(zhí)行Python腳本更新緩存。

Cacher數(shù)據(jù)庫(kù)具有：

CacheLog和CacheIntegrationError表，以跟蹤緩存何時(shí)被刷新，并且具有在緩存刷新過(guò)程中可能發(fā)生的任何錯(cuò)誤的記錄。

PerformCacheUpdate過(guò)程通過(guò)Service Broker從TransDB接收傳入消息。如果消息的類型是UpdateMessage，那么它將執(zhí)行另一個(gè)過(guò)程UpdateWebCache，執(zhí)行Python腳本執(zhí)行。

UpdateWebCache過(guò)程的執(zhí)行結(jié)果保存在表變量中，然后在消息對(duì)話結(jié)束時(shí)插入到CacheLog表中。

當(dāng)接收到的消息具有錯(cuò)誤或結(jié)束消息類型時(shí)，過(guò)程也會(huì)結(jié)束會(huì)話，并且在錯(cuò)誤類型上，將異常日志寫(xiě)入CacheIntegrationError表中。

UpdateWebCache過(guò)程從作為參數(shù)傳遞的傳入XML消息中提取Id和Name，并將這些值嵌入到Python腳本文本中。腳本執(zhí)行結(jié)果集是類型為UpddateCacheLog的結(jié)構(gòu)化表。

Cacher的Service Broker對(duì)象，主要是UpdateMessage消息類型和CacheIntegration契約與TransDB的相同，CacheQueue有一個(gè)稱為PerfomCacheUpdate的激活過(guò)程，一個(gè)名為CacheTarget的服務(wù)，該路由具有有關(guān)TransDB服務(wù)CacheService和端點(diǎn)地址的信息。

對(duì)于我們的示例解決方案，數(shù)據(jù)庫(kù)隊(duì)列的最大隊(duì)列讀取器設(shè)置為1。 如果需要，可以增加這一點(diǎn)，例如，如果數(shù)據(jù)修改很高，并且您需要增加緩存刷新率。

對(duì)于我們的解決方案，數(shù)據(jù)庫(kù)托管在同一個(gè)實(shí)例上，因此兩者都使用相同的Service Broker Endpoint來(lái)發(fā)送和接收消息。

但是，如果我們要在單個(gè)實(shí)例上托管數(shù)據(jù)庫(kù)，那么每個(gè)SQL實(shí)例的服務(wù)帳戶都應(yīng)該有一個(gè)Service Broker端點(diǎn)。 并且這兩個(gè)SQL實(shí)例都應(yīng)該有權(quán)限允許將消息發(fā)送到對(duì)方的端點(diǎn)。 連接的授權(quán)和授予可以通過(guò)以下TSQL命令集完成。 請(qǐng)注意，在消息傳遞基礎(chǔ)結(jié)構(gòu)中，有一個(gè)發(fā)送方，另一方是接收方，正如所提到的，如果SQL實(shí)例是發(fā)送方和接收方的一部分，則每個(gè)實(shí)例都應(yīng)該有自己的進(jìn)程標(biāo)識(shí)。 下圖顯示了每個(gè)SQL Server在其自身身份下運(yùn)行的方式。

這是用于在Cacher數(shù)據(jù)庫(kù)的SQL實(shí)例中授權(quán)和授予端點(diǎn)連接到TransDB的SQL實(shí)例服務(wù)帳戶[identity]的SQL代碼。

    ALTER AUTHORIZATION ON ENDPOINT::ServiceBrokerEndpoint TO [abcTransDB_SVC]
  GO
  GRANT CONNECT ON ENDPOINT::ServiceBrokerEndpoint TO [abcTransDB_SVC]
  GO

同樣，這里是用于授權(quán)和授予端點(diǎn)連接到Cache SQL數(shù)據(jù)庫(kù)SQL實(shí)例服務(wù)帳戶[identity]的代碼。

ALTER AUTHORIZATION ON ENDPOINT::ServiceBrokerEndpoint TO [abcCacherAgent_SVC]
  GO
  GRANT CONNECT ON ENDPOINT::ServiceBrokerEndpoint TO [abcCacherAgent_SVC]
  GO

這是Python腳本文本，在TSQL變量@UpdateCache中保存為字符串。 它具有具有邏輯的UpdateCache方法，通過(guò)傳遞具有作為輸入?yún)?shù)接收的Name和Id字段的數(shù)據(jù)對(duì)象來(lái)對(duì)RESTful.Cache執(zhí)行HTTP POST調(diào)用。 它接收一個(gè)JSON對(duì)象，并將其作為方法的輸出結(jié)果返回給調(diào)用者。

在腳本結(jié)束時(shí)，返回的對(duì)象被轉(zhuǎn)換為數(shù)組，因此可以將其結(jié)構(gòu)化為SQL結(jié)果。

DECLARE @UpdateCache NVARCHAR(MAX) = N"
import pandas as PND #data structure package
def UpdateCache(name,id):
  import requests as HTTP #http request package
  #Perfom HTTP POST to update cache
  httpRequest = HTTP.post( http://localhost/RESTful.Cache/ProductType/UpdateCache ,{ Name :name, Id :id}) 
  cacheLog = httpRequest.json()
  return cacheLog
 
#Update cache and build log element
log = [UpdateCache( "+ @Name+" ,"+ CAST(@Id as VARCHAR(10)) +")]
#Return data frame i.e. table structure from SQL
OutputDataSet = PND.DataFrame(data=log)
";

在SQL Server中使用Python腳本時(shí)，有一些值得注意的事情。

我們可以編寫(xiě)一個(gè)連續(xù)的腳本或?qū)⑺鼈兎纸M到我們?cè)谶@個(gè)解決方案中所做的方法中?；蛘?，我們可以創(chuàng)建一個(gè)內(nèi)聯(lián)類或創(chuàng)建一個(gè)包，并在命令提示符下使用PIP命令在python中導(dǎo)入它們。

在這個(gè)MST的CPT版本中，import語(yǔ)句只能在它放置的范圍內(nèi)導(dǎo)入包，因此我們可以注意到ImportCache中存在導(dǎo)入請(qǐng)求import語(yǔ)句，并且import語(yǔ)句導(dǎo)入熊貓存在于腳本的頂部在腳本的最后。

方法UpdateCache的輸出對(duì)象立即轉(zhuǎn)換為數(shù)組，這樣pandas.DataFrame可以將對(duì)象轉(zhuǎn)換成數(shù)據(jù)結(jié)構(gòu)，SQL Server可以輕松地將其解釋為具有行和列的表。

分配給OutputDataSet對(duì)象的數(shù)據(jù)結(jié)構(gòu)在SQL Server的TSQL執(zhí)行上下文中可用。

最后一行程序dbo.UpdateWebCache，WITH RESULT SETS（AS TYPE dbo.UpdateCacheLog）;具有用戶定義的表類型dbo.UpdateCacheLog，它有助于保持底層列的順序，并避免在從接收的數(shù)據(jù)結(jié)構(gòu)生成結(jié)果集的過(guò)程中發(fā)生任何不匹配。另一種方法是在Python和結(jié)果集中構(gòu)建映射列結(jié)構(gòu)。

TransDB是一個(gè)OLTP數(shù)據(jù)庫(kù)，我們不希望對(duì)系統(tǒng)發(fā)生任何安全漏洞，因此，通過(guò)我們的示例解決方案，這種數(shù)據(jù)庫(kù)可以托管在未安裝“機(jī)器學(xué)習(xí)服務(wù)”的SQL實(shí)例上。 Cacher是能夠到達(dá)基于網(wǎng)絡(luò)的系統(tǒng)的代理，因此可以保留在安裝機(jī)器學(xué)習(xí)服務(wù)的SQL實(shí)例上。 兩個(gè)SQL實(shí)例都可以具有多帶帶的服務(wù)帳戶身份，該身份已被授權(quán)僅連接到特定端口的Service Broker端點(diǎn)。 安全認(rèn)證通信的另一種方法是使用證書(shū)。 對(duì)于Service Broker端點(diǎn)授權(quán)，請(qǐng)參閱Microsoft.Technet：如何：通過(guò)使用證書(shū)（Transact-SQL）允許服務(wù)代理程序網(wǎng)絡(luò)訪問(wèn).aspx)以獲取更多詳細(xì)信息。

放置所有組件后，我們的WebApplication允許我們創(chuàng)建一個(gè)新的ProductType，并通過(guò)RESTful HTTP調(diào)用從刷新的緩存中列出相同的產(chǎn)品類型。 在墻后面有管理數(shù)據(jù)的組件，高速緩存對(duì)前端應(yīng)用程序是不可見(jiàn)的。

諸如電子商務(wù)，醫(yī)療電子治理等應(yīng)用可以從良好的緩存實(shí)現(xiàn)中受益。通過(guò)擴(kuò)展我們熟悉的技術(shù)的使用，我們可以獲得易于維護(hù)的解決方案，而無(wú)需學(xué)習(xí)新框架或功能的成本。

我們的示例解決方案符合我們所需要的

當(dāng)通過(guò)其中一個(gè)OLTP事務(wù)創(chuàng)建或修改數(shù)據(jù)時(shí)，系統(tǒng)刷新基于網(wǎng)絡(luò)的緩存系統(tǒng)進(jìn)行讀取訪問(wèn)。

它能夠使用異步事件來(lái)刷新緩存，靠近實(shí)時(shí)。這不會(huì)影響原始交易的表現(xiàn)。

它可以通過(guò)HTTP在事務(wù)和緩存系統(tǒng)之間繪制一條安全線，以保持?jǐn)?shù)據(jù)在OLTP數(shù)據(jù)庫(kù)中的安全。

它具有最小的監(jiān)控功能;一個(gè)緩存日志和異常日志，可以進(jìn)一步加強(qiáng)構(gòu)建管理控制臺(tái)。

使用Service Broker消息組件，解決方案足夠靈活，可以在異步消息處理發(fā)生時(shí)觸發(fā)或到達(dá)基于網(wǎng)絡(luò)的系統(tǒng)。換句話說(shuō)，數(shù)據(jù)庫(kù)與SQL Service Broker消息集成在一起，并且基于接收到的數(shù)據(jù)，執(zhí)行一個(gè)動(dòng)作來(lái)獲取或發(fā)送數(shù)據(jù)到外部數(shù)據(jù)層以外的系統(tǒng)。

通過(guò)使用服務(wù)代理消息隔離外部系統(tǒng)觸發(fā)專用數(shù)據(jù)庫(kù)內(nèi)的事件有助于保護(hù)OLTP數(shù)據(jù)庫(kù)的事務(wù)和數(shù)據(jù)。

The source code for this project is available here in githib.
https://github.com/hi10p/SQL17Python