外文轉譯：Python和SQL Server 2017的強大功能

標籤：上下文   .post   script   poi   變化   分享   最小   ges   應用   

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的樣本，顯示資料庫如何觸發外部進程來對作為參數提供的資料執行活動。這是為了考慮安全性，資料可靠性和事務回應時間的問題。 

 

Python的用例

通過從SQL調用Python指令碼而不是依賴於中介軟體，可以更容易地完成某些任務。特別是在資料庫中事件發起任務的情況下。任務可能包括：

1. 通過TCP / HTTP / SOAP向網路系統發送資料或從基於網路的系統接收資料。
2. 利用本地平台資源，如檔案系統，網路或GPU。
3. 通過使用通用資料格式（如JSON，XML或YAML）構建一個或多個系統之間的即時整合。
4. 通過與外部應用程式通訊產生資料或檔案。

當然，很少有潛在的缺點

1. 如果您使用Python需要互連網訪問，那麼存在必須保持安全的資料可能會被意外地通過互連網共用的風險。任何互連網訪問必須由網路仔細監管。
2. 允許通過“啟用外部指令碼執行”在伺服器上執行Python指令碼來暴露安全風險。
3. 相同伺服器上的資源密集型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讀取的功能。

條件

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

SQL執行個體，其中CacheDB託管必須有“麻與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訊息傳遞

 

ALTER DATABASE TransDB SET ENABLE_BROKER;   GO   ALTER DATABASE CacheDB SET ENABLE_BROKER;   GO

.NET應用程式

該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應用程式。

SQL資料庫和服務代理

OLTP資料庫TransDB有幾個對象，包括表，預存程序和Service Broker對象。

 

為了我們的目的，UpdateProductType過程使用新記錄更新ProductType表，AcknowledgeProductTypeCache過程是CacheIntegration隊列的啟用過程，當處理訊息時即從Cacher資料庫接收來自目標的確認。它還處理異常，如果有的話，並將其記錄在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和CacheIntegration錯誤表，以跟蹤緩衝何時被重新整理，並且具有在緩衝重新整理過程中可能發生的任何錯誤的記錄。

所述PerformCacheUpdate過程從接收傳入訊息TransDB通過服務代理。如果訊息的類型是UpdateMessage，那麼它將執行另一個過程UpdateWebCache，執行Python指令碼執行。

該UpdateWebCache程式的執行結果被在於然後擷取插入在表變數保持CacheLog在訊息對話結束表。

當接收到的訊息具有錯誤或結束訊息類型時，過程也會結束對話，並且在錯誤類型上，將異常日誌寫入CacheIntegrationErro r表中。

該UpdateWebCache程式提取標識和名稱，從作為參數傳遞傳入的XML訊息，並在Python指令碼文本嵌入這些值。指令碼執行結果集是類型為UpddateCacheLog的結構化表。

 

 

該cacher的的服務代理對象，主要是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 [abc\TransDB_SVC]   GO   GRANT CONNECT ON ENDPOINT::ServiceBrokerEndpoint TO [abc\TransDB_SVC]   GO

類似地，這裡是用於授權和授予端點串連到Cache的SQL執行個體服務帳戶[Identity]的代碼，在TransDB資料庫的SQL執行個體中。

ALTER AUTHORIZATION ON ENDPOINT::ServiceBrokerEndpoint TO [abc\CacherAgent_SVC]   GO   GRANT CONNECT ON ENDPOINT::ServiceBrokerEndpoint TO [abc\CacherAgent_SVC]   GO

Python指令碼

這是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） 更多細節。

所有組件放在一起

放置所有組件後，我們的WebApplication允許我們建立一個新的ProductType，並通過RESTful HTTP調用從重新整理的緩衝中列出相同的產品類型。在牆後面有管理資料的組件，快取對前端應用程式是不可見的。

 

結論

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

我們的樣本解決方案符合我們所需要的

當通過其中一個OLTP事務建立或修改資料時，系統重新整理基於網路的緩衝系統進行讀取存取。

它能夠使用非同步事件來重新整理緩衝，靠近即時。這不會影響原始交易的表現。

它可以通過HTTP在事務和緩衝系統之間繪製一條安全線，以保持資料在OLTP資料庫中的安全。

它具有最小的監控功能; 一個緩衝日誌和異常日誌，可以進一步加強構建管理主控台。

使用Service Broker訊息組件，解決方案足夠靈活，可以在非同步訊息處理髮生時觸發或到達基於網路的系統。換句話說，資料庫與SQL Service Broker訊息整合在一起，並且基於接收到的資料，執行一個動作來擷取或發送資料到外部資料層以外的系統。

通過使用服務代理訊息隔離外部系統觸發專用資料庫內的事件有助於保護OLTP資料庫的事務和資料。

 

（本帖轉譯於Hitendra Patel2017年8月04日發佈於redgote 樞紐上的文章：Python和SQL Server 2017的強大功能

  地址連結：https://www.scarydba.com/2017/06/20/database-fundamentals-3-whats-database/）

 

 

外文轉譯：Python和SQL Server 2017的強大功能