SQL

Python和SQL Server 2017的强大功能

SQL Server 2017已经增加了其高级分析扩展，现在称为“机器学习服务”，它允许SQL Server通过“使用Python的机器学习服务”在TSQL中执行Python脚本。这基本上提供了一种方式，数据库程序员可以直接从Python传递数据。这不是有限的有效性提供机器学习数据分析的功能,因为Python有许多随时可用的模块和框架来解决许多问题,如执行大量计算与数据结构、图形处理进行分析,网络操作、数据库操作、网络操作或本地/基于网络文件系统操作。显然，其中很多都是在中间件中完成的，但是在数据库系统中，有时候直接与外部系统进行直接通信比较方便，而不是依赖外部进程来通过轮询数据源来执行任务。这是有意义的，因为在数据库或数据层中没有这样的解决方案，并且当它没有提供任何安全问题时，这是没有问题的。

在这里，我们将尝试展示一个在高级分析扩展中使用Python的例子，展示了一个数据库如何触发一个外部进程来对作为参数提供的数据执行一个活动。这是为了考虑安全性、数据可靠性和事务响应时间的问题。

用例为Python

通过从SQL调用Python脚本而不是依赖于中间件，可以更容易地完成一些任务。这是一个特别的情况，任务是由数据库中的一个事件发起的。任务可能包括

1.如果您使用Python需要internet访问，那么有可能会在internet上意外地共享数据，而这些数据必须是安全的。任何互联网接入都必须受到网络的严格监管。

2.通过允许通过“外部脚本执行”来执行服务器上的Python脚本，从而暴露了安全风险。

3.同一台服务器上的资源密集型Python脚本可以影响大型OLTP系统上正在进行的事务的性能。

考虑到这些优点和缺点，如果Python能够在最小化风险的情况下发挥有益的作用，似乎还有一些情况。作为一个例子，让我们考虑如何使用Python构建一个应用层使用的数据缓存系统。

示例解决方案缓存

数据缓存是提高应用程序性能的一种有效方式。对于缓存的存储开销来说，当面对与数据库通信时的网络通信，以及在面对重复查询时数据库的高资源消耗时，我们可以获得有用的性能收益。当我们构建缓存基础设施时，我们面临的问题是何时刷新缓存的内容。在一定的时间间隔之后，我们倾向于采用简单的重建缓存的解决方案。然而，这是非常低效的。在数据更改时刷新缓存更好，只刷新更改的内容。我们可以在数据创建、更新或删除的时候进行实时处理。有许多工具和框架可用来解决refresh的问题，但是它们会遇到如何确定数据中发生了什么变化以及何时提交更改的问题。数据库是所有能够做到这一点的最佳位置。

对于我们的缓存系统，它在源代码中是可用的，我们将把自己限制在微软的堆栈中，而不使用Python本身。

软SQL服务器2017年(CPT)

   服务代理来隔离事务数据库。

Python来执行脚本，它可以通过HTTP来更新缓存(从Anaconda发行版中使用库的Python 3.5可执行文件)

4.5.2净

   我们的示例Web UI的ASP.Net MVC

   为我们的示例解决方案封装缓存存储的网络 ASP.WebAPI。

.下面是我们的示例解决方案缓存系统的图示:

  WebApplication提供了一个用户界面来读取和更新数据。

RESTful。在我们的示例缓存存储解决方案中，缓存应用程序是用ASP.Net WebAPI2构建的。它的内容类型是JSON。http-get操作从本地缓存(静态集合)提供数据。

SQL Server 2017(CPT)是一个带有a的数据库服务器

   TransDB OLTP数据库，繁忙的处理事务。

   Cacher是一个执行Python脚本执行的代理数据库，启用了启用了“外部脚本”选的脚本执行。指的是微软。Doc:外部脚本启用了服务器配置选项。

   服务代理是一种可靠的SQL server消息传递框架，它有助于连接缓存代理和TransDB。缓存代理接收到的消息可以被处理以更新缓存。

Python是SQL 2017(CPT)的数据库系统的集成脚本语言。

解决方案的体系结构

我们的解决方案是在RESTful中缓存实体的产品类型名称。缓存应用程序和WebApplication将有一个函数来创建新的产品类型条目，并从restful.Cache中读取。

先决条件

顺便提一下，我们需要考虑一些先决条件和更多信息。

1.在托管CacheDB的SQL实例中，必须使用Python安装的“机器学习服务”

2.在CacheDB中执行带有TSQL的Python脚本，SQL服务MSSQLLaunchpad或SQL Server Launchpad应该运行。指的是微软。网络:微软机器学习服务

3.使用spconfigure启用外部脚本执行，请参阅Microsoft。Doc:外部脚本启用了服务器配置选项

   4.TransDB和Cacher托管的环境应该有一个在其实例上创建的服务代理端点，如果这些服务在两个不同的SQL实例上独立运行，那么每个实例都应该有自己的端点。

   5.他的TransDB和Cacher数据库应该启用了代理。指的是微软。技术网:如何:在数据库中激活服务代理消息传递

 净应用

web应用程序有两个主要的MVC操作;一个是用HTTP动词POST更新TransDB中的一个新实体，另一个动作是用HTTP动词GET从缓存返回产品类型的列表。

 

ESTful缓存有两种操作方法，一种是使用HTTP谓词POST更新的实体产品类型，另一种是从本地缓存获取所有缓存的产品类型。

 

对于我们的示例解决方案，这两个应用程序都在IIS中托管，以保证应用程序的安全。但是对于实际的系统实现来说，托管环境可以是内部网或internet环境中的单个web服务器。

 

RESTful缓存授权规则只有两个服务帐户来处理HTTP请求。abc \ WebApp_SVC和abc \ CacherAgent_SVC。abc cacheragentsvc服务帐户允许SQL中的Python脚本通过HTTP到达应用程序，以刷新缓存。

abc webappsvc用户是为WebApplication提供授权规则模式来允许访问RESTful的。缓存应用程序

 

SQL数据库和服务代理

OLTP数据库TransDB有一些对象，包括表、存储过程和服务代理对象。

 

对于我们的目的,过程UpdateProductType更新ProductType表与新记录和AcknowledgeProductTypeCache过程的激活过程是CacheIntegration队列,它接收来自目标确认处理消息时,即从Cacher数据库。它还可以处理异常，并在cache集成错误表中记录这些异常

 

更多关于服务代理的信息可以在微软DOC:SQL Server服务代理中找到。

 

对于我们的示例解决方案，TransDB是一个源数据库，它在创建一个新的ProductType记录时创建更新缓存消息，一个用于执行操作的消息，因为它具有UpdateMessage消息类型，一个CacheIntegration合同，用来向数据库发送带有CacheSource服务的消息。该服务有一个CacheQueue，该服务由服务代理组件使用，以执行可靠的消息传递。ToCacheTarget路由有信息将消息传递给目标。

 

为了消除增加事务处理时间和避免事务数据库中其他数据的任何安全风险的可能性，我们将通过在示例解决方案中使用一个名为Cacher数据库的代理数据库来对缓存更新过程进行解耦。服务代理消息传递基础设施将有助于连接跨数据库和Cacher数据库，基于事件的消息处理将使我们能够更新基于网络的系统的缓存存储。Cacher数据库正在扮演代理的角色，以便在更新消息到达时执行缓存刷新。它通过执行Python脚本更新缓存。

隐藏自己的数据库有:

1.CacheLog和cache集成错误表，以跟踪缓存刷新时的情况，并记录缓存刷新过程中可能发生的任何错误。

2.PerformCacheUpdate过程通过服务代理接收来自TransDB的传入消息。如果消息的类型是UpdateMessage，那么它将执行另一个过程——UpdateWebCache，它执行Python脚本的执行。

a.UpdateWebCache过程的执行结果保存在一个表变量中，然后在消息对话结束时插入到CacheLog表中。

b.当接收到的消息有错误或结束消息类型时，该过程也结束对话，并且，在错误类型中，异常日志被写入cache完整性ationerror表中。

3.UpdateWebCache过程从传入的XML消息中提取Id和名称，并将这些值嵌入Python脚本文本中。脚本执行结果集是类型UpddateCacheLog类型的结构化表。

Cacher的服务代理对象，主要是UpdateMessage消息类型和CacheIntegration合同与TransDB相同，CacheQueue有一个名为PerfomCacheUpdate的激活过程，一个名为CacheTarget的服务，该路由有关于TransDB的服务CacheService和端点地址的信息。

对于我们的示例解决方案，对于两个数据库队列，最大队列读取器设置为1。如果需要的话，这可以增加，例如，如果数据修改是高的，并且需要增加缓存刷新率。

服务代理端点

对于我们的解决方案，数据库是在相同的实例上进行的，所以两者都使用相同的服务代理端点来发送和接收消息。

但是，如果我们想要在单个实例上驻留数据库，那么每个SQL实例的服务帐户都应该有一个服务代理端点。而且，这两个SQL实例都应该允许将消息发送到对方的端点。可以使用下面的TSQL命令来完成连接的授权和授予。请注意，在消息传递基础结构中，有一个发送方和另一方是接收者，正如前面提到的，如果SQL实例是发送方和接收方的一部分，那么每个实例都应该有自己的进程标识。下面的图片是每个SQL服务器如何在自己的身份下运行的表示。

Python脚本

下面是Python脚本文本，在TSQL变量@updatecache中保存为字符串。它有一个带有逻辑的UpdateCache方法，可以对rest进行HTTP POST调用。通过传递带有名称和Id字段的数据对象，这些字段作为输入参数接收。它接收一个JSON对象并将其作为方法的输出结果返回给调用者。

在脚本的末尾，返回的对象被转换为一个数组，因此可以将其结构化为SQL结果。

在SQL server中使用Python脚本时，有一些事情值得注意。

1.我们可以编写一个连续的脚本，或者将它们分组到方法中，就像我们在这个解决方案中所做的那样。或者，我们可以创建一个内联类或创建一个包，并在带有PIP命令的命令提示符中使用python来导入它们。

2.CPT的MS SQL版本,导入语句只能导入包范围内放置的地方,因此我们可以注意,导入请求导入语句存在内部方法UpdateCache,导入语句导入大熊猫存在在脚本的最后一行的脚本。

3.方法UpdateCache的输出对象立即被转换为数组，这是熊猫。DataFrame可以将对象转换为数据结构，SQL server可以轻松地将其解释为具有行和列的表。

4.分配给OutputDataSet对象的数据结构可以在SQL server的TSQL执行上下文中提供。

5.最后一行程序dbo。UpdateWebCache，带有结果集(类型为dbo.UpdateCacheLog);有一个用户定义的表类型dbo。UpdateCacheLog可以帮助保持底层列的顺序，避免在从接收到的数据结构生成结果集的过程中出现任何不匹配的情况。另一种方法是在Python和结果集中构建一个映射的列结构。

数据库安全

TransDB是一个OLTP数据库，我们不希望任何针对系统的攻击出现任何安全漏洞，因此在我们的示例解决方案方法中，这样的数据库可以托管在一个SQL实例中，其中没有安装“机器学习服务”。Cacher是一种能够访问基于网络的系统的代理，因此可以保留在安装机器学习服务的SQL实例中。这两个SQL实例都可以有一个单独的服务帐户标识，它已经被授权为特定的端口连接到服务代理端点。安全认证通信的另一种方法是使用证书。对于服务代理端点授权，请参阅Microsoft。技术网:如何:通过使用证书(transact-sql)来允许服务代理网络访问，以获得更多细节。

所有组件放在一起

在将所有组件放置到位之后，下面是我们的WebApplication，它允许我们创建一个新的产品类型，并使用基于rest的HTTP调用从刷新的缓存中列出相同的产品类型。在墙后面有一些组件，它们管理数据和缓存对于前端应用程序是不可见的。

结论

电子商务、医疗保健等应用程序可以从良好的缓存实现中受益。通过扩展我们熟悉的技术的使用，我们可以得到一个易于维护的解决方案，而无需学习新的框架或特性。

 

我们的示例解决方案满足了我们的需求

 

当数据被一个OLTP事务创建或修改时，系统将刷新基于网络的缓存系统以读取访问。

它能够使用异步事件刷新缓存，几乎是实时的。这不会影响原始事务的性能。

     它可以通过HTTP在事务性和缓存系统之间绘制一条安全线，以便在OLTP数据库中保存数据。

它启用了最小的监视功能;缓存日志和异常日志，可以进一步增强以构建管理控制台。

在服务代理消息传递组件中，当异步消息处理发生时，解决方案足够灵活，可以触发或到达基于网络的系统。换句话说，数据库与SQL服务代理消息集成，并基于接收到的数据，执行一个操作来获取或将数据发送到数据层之外的外部系统。

通过使用服务代理消息传递来隔离、外部系统在专用数据库内触发事件，有助于确保OLTP数据库的事务和数据。

  

 

文章来源于作者：Hitendra Patel  04 August 2017