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脚本而不是依赖于中间件,可以更容易地完成某些任务。特别是在数据库中事件发起任务的情况下。任务可能包括
当然也难免存在一些潜在的缺点
衡量这些优点和缺点,似乎有时候Python可以发挥有用的作用,如果可以最小化风险。作为一个例子,让我们考虑一下我们如何使用Python构建数据缓存系统供应用层使用。
缓存数据可以提高应用程序的性能。以缓存的存储开销为代价,当遇到与数据库的聊天网络通信以及数据库面临重复查询时资源消耗高的情况下,我们可以获得有用的性能提升。当我们构建缓存基础架构时,我们面临着什么时候刷新缓存的内容的常见问题。我们倾向于在一定时间间隔之后采用重建缓存的简单解决方案。然而,这是非常低效的。当数据更改时刷新缓存更好,只刷新改变的内容。在创建,更新或删除数据时,我们可以实时接近实时。有许多工具和框架可用于解决刷新问题,但是它们受到如何确定数据发生变化以及何时发生更改的问题。数据库是最好的所有能够做到这一点。
对于我们这里提供的缓存系统,我们将把自己限制在微软堆栈中,以防止Python本身。
以下是我们的示例解决方案缓存系统的图示:
在我们的解决方案中,我们将在RESTful.Cache应用程序中缓存实体“产品类型名称”,并且WebApplication将具有创建新产品类型条目并从RESTful.Cache读取的功能。
除此之外,还有一些先决条件和一些我们需要考虑的信息。
sp_configure 'external scripts enabled', 1;
RECONFIGURE;
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数据库具有:
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 [abc\TransDB_SVC]
GO
GRANT CONNECT ON ENDPOINT::ServiceBrokerEndpoint TO [abc\TransDB_SVC]
GO
同样,这里是用于授权和授予端点连接到Cache SQL数据库SQL实例服务帐户[identity]的代码。
ALTER AUTHORIZATION ON ENDPOINT::ServiceBrokerEndpoint TO [abc\CacherAgent_SVC]
GO
GRANT CONNECT ON ENDPOINT::ServiceBrokerEndpoint TO [abc\CacherAgent_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脚本时,有一些值得注意的事情。
TransDB是一个OLTP数据库,我们不希望对系统发生任何安全漏洞,因此,通过我们的示例解决方案,这种数据库可以托管在未安装“机器学习服务”的SQL实例上。 Cacher是能够到达基于网络的系统的代理,因此可以保留在安装机器学习服务的SQL实例上。 两个SQL实例都可以具有单独的服务帐户身份,该身份已被授权仅连接到特定端口的Service Broker端点。 安全认证通信的另一种方法是使用证书。 对于Service Broker端点授权,请参阅Microsoft.Technet:如何:通过使用证书(Transact-SQL)允许服务代理程序网络访问.aspx)以获取更多详细信息。
放置所有组件后,我们的WebApplication允许我们创建一个新的ProductType,并通过RESTful HTTP调用从刷新的缓存中列出相同的产品类型。 在墙后面有管理数据的组件,高速缓存对前端应用程序是不可见的。
诸如电子商务,医疗电子治理等应用可以从良好的缓存实现中受益。通过扩展我们熟悉的技术的使用,我们可以获得易于维护的解决方案,而无需学习新框架或功能的成本。
我们的示例解决方案符合我们所需要的
The source code for this project is available here in githib. https://github.com/hi10p/SQL17Python