摘要：它主要是為了允許在中使用基于的機器學習，但是它可以與任何庫或框架一起使用。已經通過啟用服務器通過使用的機器學習服務在中執行腳本，添加到其高級分析擴展，現在稱為機器學習服務。存儲的實例必須安裝具有的機器學習服務要在中使用執行腳本，應運行或。

Python是SQL Server 2017的新功能。它主要是為了允許在SQL
Server中使用基于Python的機器學習，但是它可以與任何Python庫或框架一起使用。為了提供可能的例子，Hitendra展示了如何安全地使用該功能來提供智能應用程序緩存，其中SQL
Server可以自動指示數據何時更改以觸發緩存刷新。

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

在這里，我們將嘗試演示在Advanced Analytics Extension中使用Python的示例，顯示數據庫如何觸發外部進程來對作為參數提供的數據執行活動。這是為了考慮安全性，數據可靠性和事務響應時間的問題。

通過從SQL調用Python腳本而不是依賴于中間件，可以更容易地完成某些任務。特別是在數據庫中事件發起任務的情況下。任務可能包括

通過TCP / HTTP / SOAP向網絡系統發送數據或從基于網絡的系統接收數據。

利用本地平臺資源，如文件系統，網絡或GPU。

通過使用通用數據格式（如JSON，XML或YAML）構建一個或多個系統之間的實時集成。

通過與外部應用程序通信生成數據或文件。

當然也難免存在一些潛在的缺點

如果您使用Python需要互聯網訪問，那么存在必須保持安全的數據可能會被意外地通過互聯網共享的風險。任何互聯網訪問必須由網絡仔細監管。

允許通過“啟用外部腳本執行”在服務器上執行Python腳本來暴露安全風險。

相同服務器上的資源密集型Python腳本可能會影響大型OLTP系統上正在進行的事務的性能。

衡量這些優點和缺點，似乎有時候Python可以發揮有用的作用，如果可以最小化風險。作為一個例子，讓我們考慮一下我們如何使用Python構建數據緩存系統供應用層使用。

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

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

Microsoft SQL Server 2017（CPT）

服務代理隔離事務數據庫。

Python執行腳本，可以通過HTTP更新緩存（Python 3.5可執行文件與Anaconda發行版中的庫）

.Net 4.5.2

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

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

以下是我們的示例解決方案緩存系統的圖示：

WebApplication提供用于讀取和更新數據的用戶界面。

我們示例緩存存儲解決方案中的RESTful.Cache應用程序是使用ASP.Net WebAPI2構建的，其內容類型為JSON。 HTTP-GET操作從本地緩存（靜態集合）傳送數據。

MS SQL Server 2017（CPT）是一個數據庫服務器

TransDB OLTP數據庫，處理事務繁忙。

Cacher執行Python腳本執行的代理數據庫，啟用了啟用“外部腳本啟用”選項的腳本執行。 請參閱Microsoft.Doc：啟用外部腳本的服務器配置選項。

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

Python是使用SQL 2017（CPT）的數據庫系統的集成腳本語言

在我們的解決方案中，我們將在RESTful.Cache應用程序中緩存實體“產品類型名稱”，并且WebApplication將具有創建新產品類型條目并從RESTful.Cache讀取的功能。

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

存儲CacheDB的SQL實例必須安裝“具有Python的機器學習服務”

要在CacheDB中使用TSQL執行Python腳本，應運行SQL Service MSSQLLaunchpad或SQL Server Launchpad。 請參閱Microsoft.Net：Microsoft機器學習服務

使用SP_Configure啟用外部腳本執行，請參閱Microsoft.Doc：啟用外部腳本的服務器配置選項

sp_configure "external scripts enabled", 1; 

RECONFIGURE;

TransDB和Cacher托管的環境應該在其實例上創建一個Service Broker端點，如果這兩個端點獨立托管在兩個不同的SQL實例上，那么每個實例應該有自己的端點。

TransDB和Cacher數據庫應該啟用代理。 請參閱Microsoft.Technet：如何：激活數據庫中的Service Broker消息傳遞.aspx)

ALTER DATABASE TransDB SET ENABLE_BROKER;

GO

ALTER DATABASE CacheDB SET ENABLE_BROKER;
GO

WebApplication有兩個主要的MVC動作; 一個使用HTTP動詞POST更新TransDB中的一個新實體，另一個使用HTTP動詞GET從緩存返回產品類型列表的操作。

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

對于我們的示例解決方案，這兩個應用程序都在IIS中托管在各個應用程序池標識下，以保護應用程序安全 但是對于實際的系統實現，托管環境可以是內部網或互聯網環境中的單個Web服務器。

RESTful.Cache授權規則只有兩個服務帳戶來處理HTTP請求，即

abc WebApp_SVC和abc CacherAgent_SVC。 abc CacherAgent_SVC服務帳戶允許SQL中的Python腳本通過HTTP到達應用程序來刷新緩存。

abc WebApp_SVC用戶用于具有授權規則模式的WebApplication，以允許訪問RESTful.Cache應用程序。

OLTP數據庫TransDB有幾個對象，包括表，存儲過程和Service Broker對象。

CacheIntegrationError表。

有關Service Broker的更多信息，請訪問Microsoft.DOC：SQL Server Service Broker

對于我們的示例解決方案，TransDB是創建新的ProductType記錄時創建更新緩存消息的源數據庫，執行操作的消息，它具有UpdateMessage消息類型，CacheIntegration合同將CacheSource服務發送到目標數據庫。該服務具有CacheQueue，由Service Broker組件用于執行可靠的消息傳遞。 ToCacheTarget路由具有將消息傳遞到其目標的信息。

為了消除任何增加事務處理時間的機會以及避免事務數據庫中其余數據的任何安全風險，我們將通過使用我們的示例解決方案中名為Cacher數據庫的代理數據庫來解除緩存更新過程。 Service Broker消息傳遞基礎設施將有助于連接TransDB和Cacher數據庫，基于事件的消息處理將使我們能夠更新駐留在基于網絡的系統上的緩存存儲。 Cacher數據庫正在扮演代理角色，以便在更新消息到達時執行緩存刷新。它通過執行Python腳本更新緩存。

Cacher數據庫具有：

CacheLog和CacheIntegrationError表，以跟蹤緩存何時被刷新，并且具有在緩存刷新過程中可能發生的任何錯誤的記錄。

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

UpdateWebCache過程的執行結果保存在表變量中，然后在消息對話結束時插入到CacheLog表中。

當接收到的消息具有錯誤或結束消息類型時，過程也會結束會話，并且在錯誤類型上，將異常日志寫入CacheIntegrationError表中。

UpdateWebCache過程從作為參數傳遞的傳入XML消息中提取Id和Name，并將這些值嵌入到Python腳本文本中。腳本執行結果集是類型為UpddateCacheLog的結構化表。

Cacher的Service Broker對象，主要是UpdateMessage消息類型和CacheIntegration契約與TransDB的相同，CacheQueue有一個稱為PerfomCacheUpdate的激活過程，一個名為CacheTarget的服務，該路由具有有關TransDB服務CacheService和端點地址的信息。

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

對于我們的解決方案，數據庫托管在同一個實例上，因此兩者都使用相同的Service Broker Endpoint來發送和接收消息。

但是，如果我們要在單個實例上托管數據庫，那么每個SQL實例的服務帳戶都應該有一個Service Broker端點。 并且這兩個SQL實例都應該有權限允許將消息發送到對方的端點。 連接的授權和授予可以通過以下TSQL命令集完成。 請注意，在消息傳遞基礎結構中，有一個發送方，另一方是接收方，正如所提到的，如果SQL實例是發送方和接收方的一部分，則每個實例都應該有自己的進程標識。 下圖顯示了每個SQL Server在其自身身份下運行的方式。

這是用于在Cacher數據庫的SQL實例中授權和授予端點連接到TransDB的SQL實例服務帳戶[identity]的SQL代碼。

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

同樣，這里是用于授權和授予端點連接到Cache SQL數據庫SQL實例服務帳戶[identity]的代碼。

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

這是Python腳本文本，在TSQL變量@UpdateCache中保存為字符串。 它具有具有邏輯的UpdateCache方法，通過傳遞具有作為輸入參數接收的Name和Id字段的數據對象來對RESTful.Cache執行HTTP POST調用。 它接收一個JSON對象，并將其作為方法的輸出結果返回給調用者。

在腳本結束時，返回的對象被轉換為數組，因此可以將其結構化為SQL結果。

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腳本時，有一些值得注意的事情。

我們可以編寫一個連續的腳本或將它們分組到我們在這個解決方案中所做的方法中。或者，我們可以創建一個內聯類或創建一個包，并在命令提示符下使用PIP命令在python中導入它們。

在這個MST的CPT版本中，import語句只能在它放置的范圍內導入包，因此我們可以注意到ImportCache中存在導入請求import語句，并且import語句導入熊貓存在于腳本的頂部在腳本的最后。

方法UpdateCache的輸出對象立即轉換為數組，這樣pandas.DataFrame可以將對象轉換成數據結構，SQL Server可以輕松地將其解釋為具有行和列的表。

分配給OutputDataSet對象的數據結構在SQL Server的TSQL執行上下文中可用。

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

TransDB是一個OLTP數據庫，我們不希望對系統發生任何安全漏洞，因此，通過我們的示例解決方案，這種數據庫可以托管在未安裝“機器學習服務”的SQL實例上。 Cacher是能夠到達基于網絡的系統的代理，因此可以保留在安裝機器學習服務的SQL實例上。 兩個SQL實例都可以具有多帶帶的服務帳戶身份，該身份已被授權僅連接到特定端口的Service Broker端點。 安全認證通信的另一種方法是使用證書。 對于Service Broker端點授權，請參閱Microsoft.Technet：如何：通過使用證書（Transact-SQL）允許服務代理程序網絡訪問.aspx)以獲取更多詳細信息。

放置所有組件后，我們的WebApplication允許我們創建一個新的ProductType，并通過RESTful HTTP調用從刷新的緩存中列出相同的產品類型。 在墻后面有管理數據的組件，高速緩存對前端應用程序是不可見的。

諸如電子商務，醫療電子治理等應用可以從良好的緩存實現中受益。通過擴展我們熟悉的技術的使用，我們可以獲得易于維護的解決方案，而無需學習新框架或功能的成本。

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

當通過其中一個OLTP事務創建或修改數據時，系統刷新基于網絡的緩存系統進行讀取訪問。

它能夠使用異步事件來刷新緩存，靠近實時。這不會影響原始交易的表現。

它可以通過HTTP在事務和緩存系統之間繪制一條安全線，以保持數據在OLTP數據庫中的安全。

它具有最小的監控功能;一個緩存日志和異常日志，可以進一步加強構建管理控制臺。

使用Service Broker消息組件，解決方案足夠靈活，可以在異步消息處理發生時觸發或到達基于網絡的系統。換句話說，數據庫與SQL Service Broker消息集成在一起，并且基于接收到的數據，執行一個動作來獲取或發送數據到外部數據層以外的系統。

通過使用服務代理消息隔離外部系統觸發專用數據庫內的事件有助于保護OLTP數據庫的事務和數據。

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