翻譯：Python和SQL Server 2017的強大功能

標籤：資料格式   網路操作   地方   gpu   儲存   好的   alt   margin   dataframe   

Python和SQL Server 2017的強大功能

Python是SQL Server 2017的新版本。它的主要目的是允許在SQL Server中使用基於Python的機器學習，但它可以使用的遠不止這些，還有任何Python庫或架構。為了提供一個可能的樣本，Hitendra展示了如何安全地使用該特性來提供智能應用程式緩衝，其中SQL Server可以在資料更改觸發緩衝重新整理時自動顯示。

SQL Server 2017已經增加了它的進階分析擴充功能，現在被稱為“機器學習服務”，它允許SQL Server通過Python的“機器學習服務”在TSQL中執行Python指令碼。這基本上提供了一種方法，可以讓資料庫程式員可以將資料直接傳遞給Python。這不是有限的有效性提供機器學習資料分析的功能,因為Python有許多隨時可用的模組和架構來解決許多問題,如執行大量計算與資料結構、圖形處理進行分析,網路操作、資料庫操作、網路操作或本地/基於網路檔案系統操作。顯然，其中很多都是在中介軟體中做得最好的，但是在資料庫系統中，有很多時候，直接與外部系統進行直接通訊是比較方便的，而不是依靠外部流程來通過輪詢資料來源來執行任務。當不需要在資料庫或資料層中有這樣的解決方案時，當它不提供任何安全問題時，這是有意義的。

在這裡，我們將嘗試示範在進階分析擴充功能中使用Python的一個樣本，它展示了一個資料庫如何觸發一個外部流程，以執行作為參數提供的資料的活動。這是為了考慮安全性、資料可靠性和事務回應時間的問題。

用例為Python

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

1 .工作通過TCP /HTTP/ SOAP向基於網路的系統發送資料或接收資料。

二。利用本地平台資源，如檔案系統、網路或GPU。

3 .項目使用通用資料格式(如JSON、XML或YAML)構建一個或多個系統之間的即時整合。

4。通過與外部應用程式通訊產生資料或檔案。

當然，很少有潛在的不利因素

1 .工作如果您對Python的使用需要internet訪問，那麼有一個風險，即必須保證安全的資料可能會在internet上意外地共用。任何互連網接入都必須經過網路的嚴格監管。

二。允許通過“啟用外部指令碼執行”在伺服器上執行Python指令碼，從而暴露安全風險。

3 .項目在同一伺服器上的資源密集型Python指令碼可以影響大型OLTP系統中進行中的事務的效能。

權衡這些優點和缺點時，如果Python能夠最大限度地降低風險，那麼它仍然可以發揮有益的作用。作為一個例子，讓我們考慮一下如何使用Python來構建應用程式層使用的資料緩衝系統。

樣本解決方案緩衝

快取資料可能是提高應用程式效能的一種有效方法。在緩衝的儲存開銷上，我們可以在面對類似於資料庫的chatty網路通訊，以及在面對重複查詢時資料庫的高資源消耗時獲得有用的效能收益。當我們構建緩衝基礎設施時，我們面臨的常見問題是何時重新整理緩衝的內容。在一定的時間間隔後，我們傾向於採用簡單的重建緩衝的方法。然而，這是非常低效的。當資料發生變化時重新整理緩衝，並僅重新整理已更改的內容，這是更好的做法。當資料被建立、更新或刪除時，我們可以做到這一點。有許多工具和架構可以解決重新整理問題，但是它們遇到了如何確定資料中發生了什麼變化以及更改何時發生的問題。資料庫最適合做這些工作。

對於我們的緩衝系統，它可以在這裡提供，我們將把自己限制在Microsoft堆棧上，以防止Python本身。

?Microsoft SQL Server 2017(CPT)

?服務代理來隔離交易資料庫。

?Python執行指令碼,可以通過HTTP緩衝更新(Python 3.5可執行庫從蟒蛇分布)

?。4.5.2淨

?ASP。我們的樣本Web UI的網路MVC

?ASP。Net WebAPI封裝了我們的樣本解決方案的緩衝儲存。

下面是樣本解決方案緩衝系統的圖形表示:

?WebApplication提供了一個使用者介面來讀取和更新資料。

?RESTful。在我們的樣本緩衝儲存解決方案中，使用ASP構建緩衝應用程式。Net WebAPI2，它的內容類型是JSON。http - get操作從本機快取(靜態集合)提供資料。

?SQL Server 2017(CPT)是一個帶有a的資料庫伺服器

?TransDB OLTP資料庫,忙著處理事務。

?Cacher代理資料庫執行Python指令碼執行指令碼執行啟用“啟用外部指令碼”選項開啟。指的是微軟。Doc:外部指令碼支援伺服器配置選項。

?服務代理,SQL server的可靠的訊息傳遞架構,艾滋病橋Cacher-Agent TransDB。通過緩衝代理接收訊息可以處理以更新緩衝。

?Python是SQL 2017(CPT)資料庫系統的整合指令碼語言。

解決方案的架構

在我們的解決方案中，我們將在RESTful中緩衝實體的產品類型名稱。緩衝應用程式和WebApplication將有一個函數來建立新產品類型條目，並從restful. Cache中讀取。

先決條件

順便說一下，我們需要考慮一些先決條件和更多的資訊。

1 .工作CacheDB託管的SQL執行個體必須安裝有Python的機器學習服務

二。要在CacheDB中執行帶有TSQL的Python指令碼，應該運行SQL服務MSSQLLaunchpad或SQL Server Launchpad。參考微軟。Net:微軟機器學習服務

3 .項目啟用SP_Configure的外部指令碼執行，請參考微軟。Doc:外部指令碼支援伺服器配置選項

sp_configure ‘外部指令碼啟用‘，1;重新設定;

4。TransDB和Cacher託管環境應該有一個在其執行個體上建立的服務代理端點，如果它們在兩個不同的SQL執行個體上獨立託管，那麼每個執行個體都應該有自己的端點。

5。TransDB和Cacher資料庫應該啟用代理。參考微軟。Technet:如何:在資料庫中啟用服務代理訊息傳遞

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

。網路應用

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

RESTful。緩衝有兩種操作方法，一種是使用帶有HTTP謂詞POST的新添加的實體產品類型更新緩衝，另一種是從本機快取擷取所有緩衝的產品類型。

對於我們的樣本解決方案，這兩個應用程式都駐留在IIS下的單獨應用程式集區標識中，以保證應用程式安全。但是對於實際的系統實現，託管環境可以是一個區域網路或internet環境中的一個單獨的web伺服器。

RESTful。緩衝授權規則只有兩個服務帳戶來處理HTTP請求。

abc \ WebApp_SVC和abc \ CacherAgent_SVC。abc\ CacherAgent_SVC服務帳戶允許SQL中的Python指令碼通過HTTP到達應用程式以重新整理緩衝。

abc\ WebApp_SVC使用者使用具有授權規則模式的web應用程式，以允許訪問RESTful。緩衝應用程式。

SQL資料庫和服務代理

OLTP資料庫TransDB有一些對象，包括表、預存程序和服務代理對象。

對於我們的目的,過程UpdateProductType更新ProductType表與新記錄和AcknowledgeProductTypeCache過程的啟用過程是CacheIntegration隊列,它接收來自目標確認處理訊息時,即從Cacher資料庫。它還處理異常，並在CacheIntegrationError表中記錄這些異常。

更多關於服務經紀人的資訊可以在微軟找到。DOC:SQL Server Service Broker

 

 

對於我們的樣本解決方案，TransDB是一個來源資料庫，它在建立一個新的ProductType記錄時建立更新緩衝訊息，這是一個執行動作的訊息，因為它有UpdateMessage訊息類型，一個CacheIntegration合約，以發送帶有CacheSource服務的訊息以針對資料庫。該服務有一個CacheQueue，它由服務代理組件使用，以執行可靠的訊息傳遞。ToCacheTarget路由具有將訊息傳遞到目標的資訊。

為了消除增加交易處理時間的任何機會，以及避免交易資料庫中其餘資料的安全風險，我們將使用一個名為Cacher資料庫的代理資料庫在我們的樣本解決方案中分離緩衝更新過程。服務代理訊息傳遞基礎設施將有助於串連TransDB和Cacher資料庫，基於事件的訊息處理將使我們能夠更新駐留在基於網路的系統上的緩衝儲存。當更新訊息到達時，Cacher資料庫扮演代理的角色，以執行緩衝重新整理。它通過執行Python指令碼更新緩衝。

隱藏自己的資料庫有:

1 .工作CacheLog和CacheIntegrationError表，跟蹤緩衝重新整理時的記錄，並記錄緩衝重新整理過程中可能出現的任何錯誤。

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

aUpdateWebCache過程的執行結果被儲存在一個表變數中，然後在訊息會話結束時插入到CacheLog表中。

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

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

Cacher的服務代理對象，主要是UpdateMessage訊息類型和CacheIntegration合約與TransDB相同，CacheQueue有一個名為PerfomCacheUpdate的啟用過程，該服務名為CacheTarget，而且該路由有關於TransDB服務CacheService和端點地址的資訊。

對於我們的樣本解決方案，將最大隊列讀取器設定為1，用於兩個資料庫隊列。如果需要，這可以增加，例如，如果資料修改非常高，您需要增加緩衝重新整理速率。

服務代理端點

對於我們的解決方案，資料庫在同一個執行個體上運行，因此它們都使用相同的服務代理端點來發送和接收訊息。

 

 

但是，如果我們想要在單個執行個體上駐留資料庫，那麼每個SQL執行個體的服務帳戶都應該有一個服務代理端點。而且兩個SQL執行個體都應該允許發送訊息到彼此的端點。可以使用以下一組TSQL命令來完成串連的授權和授予。注意，在訊息傳遞基礎結構中，有一個發送方和另一方是接收方，如前面提到的，如果SQL執行個體是發送方和接收方的一部分，那麼每個執行個體都應該有自己的過程標識。下面的圖片展示了每個SQL伺服器如何根據自己的身份運行。

這是在Cacher資料庫的SQL執行個體中授權和授予端點串連到TransDB的SQL執行個體服務帳戶[identity]的SQL代碼。

1234改變授權端點:ServiceBrokerEndpoint[abc \ TransDB_SVC]去格蘭特在端點串連::ServiceBrokerEndpoint[abc \ TransDB_SVC]去

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

1234改變授權端點:ServiceBrokerEndpoint[abc \ CacherAgent_SVC]去格蘭特在端點串連::ServiceBrokerEndpoint[abc \ CacherAgent_SVC]去

Python指令碼

這裡是Python指令碼文本，作為TSQL變數@ updatecache中的字串儲存。它有一個帶有邏輯的UpdateCache方法，可以執行一個HTTP POST調用rest。通過傳遞一個具有名稱和Id欄位的資料對象來緩衝，該資料對象作為輸入參數接收。它接收一個JSON對象並將其作為輸出結果返回給調用者。

在指令碼的末尾，返回的對象被轉換為一個數組，因此它可以被構造成一個SQL結果。

DECLARE @UpdateCache NVARCHAR(MAX) = N‘import pandas as PND #data structure packagedef 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 elementlog = [UpdateCache( ‘+ @Name+‘ ,‘+ CAST(@Id as VARCHAR(10)) +‘)]#Return data frame i.e. table structure from SQLOutputDataSet = PND.DataFrame(data=log)‘;

在使用SQL server中的Python指令碼時，有一些事情值得注意。

1 .工作我們可以編寫一個連續的指令碼，或者將它們分組到方法中，就像我們在這個解決方案中所做的那樣。或者，我們可以建立一個內聯類或建立一個包，並使用PIP命令在命令提示字元的python中匯入它們。

二。CPT的MS SQL版本,匯入語句只能匯入包範圍內放置的地方,因此我們可以注意,匯入請求匯入語句存在內部方法UpdateCache,匯入語句匯入大熊貓存在在指令碼的最後一行的指令碼。

3 .項目方法UpdateCache的輸出對象立即被轉換為數組，以這種方式表示熊貓。DataFrame可以將對象轉換為資料結構，SQL server可以輕鬆地將其解釋為帶有行和列的表。

4。分配給OutputDataSet對象的資料結構由SQL server在TSQL執行內容中提供。

5。最後一行程式dbo。使用結果集(作為dbo . updatecachelog)的UpdateWebCache;有一個使用者定義的表類型dbo。UpdateCacheLog，它有助於保持底層列的順序，避免在從接收的資料結構產生結果集的過程中出現任何不匹配。另一種方法是在Python內和結果集中構建一個映射的列結構。

資料庫安全

TransDB是一個OLTP資料庫，我們不希望任何對系統的攻擊有任何安全性漏洞，因此在我們的樣本解決方案方法中，這樣的資料庫可以託管在沒有安裝“機器學習服務”的SQL執行個體上。Cacher是一個能夠到達基於網路的系統的代理，因此可以停留在安裝機器學習服務的SQL執行個體上。這兩個SQL執行個體都可以有一個單獨的服務帳戶標識，它被授權僅為一個特定的連接埠串連到服務代理端點。另一種安全認證通訊的方法是使用認證。對於服務代理端點授權，請參考微軟。Technet:如何:允許使用認證(transact - sql)提供服務代理網路訪問。

所有組件放在一起

在將所有組件放置就緒後，下面是我們的web應用程式，它允許我們建立一個新的ProductType，並使用RESTful HTTP調用從重新整理緩衝中列出相同的產品類型。在牆的後面有管理資料和緩衝的組件在前端應用程式是看不見的。

 

結論

電子商務、醫學保健等應用程式可以從良好的緩衝實現中獲益。通過擴充我們熟悉的技術的使用，我們可以得到一個易於維護的解決方案，而無需學習新的架構或特性。

我們的樣本解決方案滿足了我們的需要

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

?它能夠使用非同步事件重新整理緩衝，接近即時。這不會影響原始事務的效能。

?它可以通過HTTP在事務和緩衝系統之間繪製一條安全界限，以便將資料儲存在OLTP資料庫中。

?啟用了最小監控功能;緩衝日誌和異常日誌，可以進一步增強以構建管理主控台。

?使用服務代理訊息傳遞組件，當非同步訊息處理髮生時，解決方案具有足夠的靈活性，可以觸發或到達基於網路的系統。換句話說，資料庫與SQL服務代理訊息整合，基於接收到的資料，執行一個操作來擷取或發送資料到外部的外部系統，這些外部系統位於資料層之外。

?通過使用服務代理訊息傳遞機制，將外部系統觸發到專用資料庫中，這有助於擷取OLTP資料庫的事務和資料。

此項目的原始碼在githib中可用。

 

 

 

 

 

 

翻譯：Python和SQL Server 2017的強大功能