Python和SQL Server 2017的功能

  

翻译：

Python和SQL Server 2017的功能

                             

Python对SQL Server 2017是新的。它的主要目的是允许在SQL Server中使用基于Python的机器学习，但它比Python库或框架要多得多。提供什么是可能的一个例子，Hitendra展示了如何安全使用的功能提供智能应用程序缓存，SQL Server会自动显示在数据变化时触发一个缓存刷新。

MS SQL Server 2017增加了其先进的分析扩展，现在被称为“机器学习服务，通过启用SQL服务器执行Python脚本在TSQL通过机器学习Python的服务。这基本上提供了一种数据库程序员可以直接将数据传递到Python的方法。这个用处不限于提供机器学习能力的数据分析，因为Python有很多现成的模块和框架解决很多问题如数据结构进行大量的计算工作，图形处理分析、网络操作、数据库操作、网络操作或本地/网络文件系统操作。显然，其中很多都是在中间件中完成的，但在数据库系统中，有时更容易与外部系统直接通信，而不是依靠外部进程来轮询数据源来执行任务。当数据库或数据层中没有这样的解决方案，并且当它不提供任何安全关注时，这不是一个问题。

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

python的用例

通过调用来自SQL的Python脚本而不是依赖中间件，可以更容易地完成一些任务。尤其是在数据库中的事件引发任务的情况下。任务可能包括

1、通过TCP/IP向基于网络的系统发送数据或接收数据。

2、利用本地平台资源，如文件系统、网络或图形处理器GPU。

3，建立实时集成一个或多个系统之间采用通用数据格式，如XML或JSON、YAML。

4、通过与外部应用程序通信来生成数据或文件。

自然，潜在的不利因素很少。

1。如果你使用Python需要接入互联网，是有风险的，必须保持安全的数据可能会意外地在互联网上共享。任何互联网接入都必须受到网络的仔细管理。

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

3.resource-intensive Python脚本在同一台服务器上可以影响大的OLTP系统正在进行的交易的表现

权衡这些优点和缺点，有时似乎有机会Python可以发挥有益的作用，如果它可以最大限度地减少风险。作为一个例子，让我们考虑如何使用Python构建一个数据缓存系统，供应用程序层使用。

缓存的示例解决方案

缓存数据是提高应用程序性能的有效途径。在对缓存的存储开销的成本，我们可以使有用的性能增益，面对与数据库健谈的网络通讯之类的东西，和高资源消耗的数据库的时候，面对重复查询。当我们构建缓存基础结构时，我们面临着刷新缓存内容的常见问题。我们倾向于采用在一定时间间隔之后重建缓存的简单解决方案。然而，这是非常低效的。当数据更改时，刷新缓存更好，只刷新已更改的内容。在创建、更新或删除数据时，我们可以在接近实时的情况下做到这一点。有很多工具和框架可以用来解决刷新的问题，但是它们遇到的问题是如何确定数据中的变化以及何时发生了更改。数据库是最好的地方，所有能够做到这一点。

我们的缓存系统， 起源就在这里，我们将限制微软堆栈的一切除了Python本身。

·微软SQL Server 2017（CPT）

经合经纪人隔离事务数据库。

·python 执行脚本可以更新缓存的HTTP（Python 3.5执行从长长的发行库）

Net 4.5.2

·我们的示例web用户界面

·ASP。NET WebAPI封装缓存存储我们的例解。

下面是示例解决方案缓存系统的图形表示：

 

Web应用提供了一个用户界面，读取和更新数据。

RESTful。缓存应用程序在我们的例子中的高速缓存存储解决方案缓存应用建立在ASP .NET webapi2，其内容类型是 JSON.HTTP-GET操作提供本地缓存的数据（静态采集）。

MS SQL Server 2017 （CPT）是一个数据库服务器

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

        Cacher  代理数据库执行Python脚本执行，执行脚本启用外部脚本启用”选项打开。指的是 microsoft.doc：外部脚本启用服务器配置选项。

      服务代理，一个可靠的SQL Server的通信框架，辅助桥Cacher-Agent 和TransDB 。通过 Cacher-Agent 代理收到消息可以处理更新缓存。

Python是使用SQL 2017（CPT）的数据库系统的集成脚本语言。

 

解决方案的体系结构

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

前提条件

除此之外，还有一些先决条件和一些我们需要考虑的信息。

1.SQL实例，CacheDB 托管必须有“机器学习Python的安装服务

2.用TSQL在CacheDB执行Python脚本，SQL服务MSSQLLaunchpad或SQL Server启动应该在运行。参考微软.NET：微软机器学习服务

3.使SP_Configure外部脚本的执行，是指Microsoft.Doc:外部脚本启用服务器配置选项

 

	sp_configure 'external scripts enabled', 1;    RECONFIGURE;

4.TransDB和 Cacher 托管环境应该有一个它的实例创建Service Broker端点，如果这些被单独存储在两个不同的SQL实例，每个实例应该自己的端点。

5.TransDB和 Cacher 数据库应该有经纪人启用。参考微软TechNet：如何激活数据库中的ServiceBroker消息传递

6.

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

网络应用

Web应用程序主要有两个MVC的行动；一个更新HTTP动词后TransDB一个新的实体与另一个行动来回报从HTTP动词缓存产品类型列表中得到。

 

RESTful.Cache 有两种操作方法，一种是用新的添加的实体产品类型用HTTP谓词来更新缓存，另一种方法是从本地缓存中获取所有缓存的产品类型。

 

对于我们的示例解决方案，两个应用程序都驻留在IIS中，在单独的应用程序池身份下保持应用程序的安全。但对于实际的系统实现，宿主环境可以是一个单独的服务器在一个局域网或互联网环境。

缓存授权规则只有两个服务帐户来处理HTTP请求，即

 

abc\WebApp_SVC and abc\CacherAgent_SVC. The abc\CacherAgent_SVC 服务帐户允许在SQL的Python脚本，达到使用HTTP刷新缓存。

The abc\WebApp_SVC用户是Web应用程序拥有授权规则模式允许访问缓存应用的宁静。

SQL数据库和服务代理

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

 

对于我们而言，程序 UpdateProductType产品类型表与一个新的记录和AcknowledgeProductTypeCache程序更新产品类型表的CacheIntegration 队列的激活程序，它接收从目标确认消息时正在处理即从藏库。它也处理的例外的话，并记录这些在CacheIntegrationError 表。

在服务代理的更多信息，可以发现在Microsoft.DOC:：SQL Server服务代理

 

我们的例解， TransDB 是源数据库创建更新缓存的消息时，一个新的产品类型的记录被创建，一个消息来执行一个动作，那它有UpdateMessage 消息类型，一CacheIntegration合同与 CacheSource服务目标数据库发送消息。有一个CacheQueue的服务是由服务代理组件用来实现可靠的消息传递。的ToCacheTarget r路线已将消息传递到目标信息。

消除任何机会增加事务处理的时间以及避免在交易数据库中的数据的其他任何安全风险，我们会将缓存更新过程通过使用一个数据库代理，叫我们例解Cacher数据库。Service Broker消息基础设施将有助于连接TransDB和Cacher数据库，基于消息处理的事件将使我们能够更新缓存存储驻留在网络系统。公章数据库执行缓存刷新更新消息到达时播放以代理人的角色。它通过执行Python脚本来更新缓存。

 

公章的数据库：

1.CacheLog和CacheIntegrationError 表，跟踪当缓存被刷新，有可能在缓存刷新过程中发生的任何错误记录。

2.PerformCacheUpdate 程序通过服务代理从 TransDB接收传入的消息。如果消息的类型UpdateMessage ,然后执行另一个程序，UpdateWebCache，执行Python脚本执行。

a.UpdateWebCache程序的执行结果表中的变量，然后插在 CacheLog 表在邮件会话结束时举行。

b.程序也结束会话时接收到的信息有错误或结束的消息类型，对错误类型、异常日志写在CacheIntegrationError表。

3.UpdateWebCache 程序从输入的XML消息传递的参数提取的ID和名称并将这些值在Python脚本文本。脚本执行的结果集是一个类 UpddateCacheLog.结构表。

 

公章的服务代理对象，主要 UpdateMessage消息类型和CacheIntegration 合同与TransDB相同，CacheQueue有活化过程称为 PerfomCacheUpdate，一种名为CacheTarget，和路由有TransDB服务缓存服务和端点地址信息。

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

Service Broker端点

对于我们的解决方案，数据库托管在同一个实例上，因此它们都使用同一个服务代理端点来发送和接收消息。

 

但是，如果我们想在单个实例上托管数据库，那么每个SQL实例的服务帐户应该有一个服务代理端点。这两个SQL实例都应该具有允许将消息发送到对方端点的权限。授权和批准的连接可以做以下TSQL命令。注意，在消息传递基础结构中，有一个发送方和另一个接收方，正如前面提到的，如果SQL实例是发送方和接收方的一部分，那么每个实例都应该有自己的进程标识。下面的图片展示了每个SQL Server在其标识下运行的方式。

 

这是批准和授予的端点连接到transdb的SQL实例的服务帐户[认同]在藏数据库SQL实例的SQL代码。

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

 

 

同样，这是批准和授予的端点连接到Cacher的SQL实例的服务帐户[认同]在transdb数据库SQL实例代码。

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

 

 

Python脚本

这里是Python脚本文本，保存为“UpdateCache TSQL变量字符串。它具有UpdateCache方法与逻辑来执行HTTP POST调用RESTful。通过数据缓存对象的智慧作为输入参数接收的h名称和id字段。它接收一个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脚本时，有几件事是值得注意的。

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

2.在CPT版MS SQL，import语句可以导入包只能在地方范围内的放，因此我们可以看到，进口要求import语句内部存在的方法UpdateCache，import语句导入熊猫存在在上面的脚本，在脚本最后一行。

3.方法UpdateCache输出对象立即转化为一个数组，这样pandas.DataFrame以转换一个对象为一个数据结构，SQL Server可以很容易地解释为行和列的表。

4.数据结构分配到输出集对象是由SQL Server TSQL执行上下文提供。

5.dbo.UpdateWebCache程序的最后一行，与结果集（为 dbo.UpdateCacheLog）；有一个用户定义的表式v这有助于保持底层柱为避免发电机组从接收到的数据结构的结果，过程中的任何不匹配。另一种方法是在Python中和结果集中构建一个映射的列结构。

[医]数据库安全

TransDB是OLTP数据库，我们不需要任何安全漏洞，任何对系统的攻击，因此我们的例子的解决方法，这样的数据库可以托管在一个SQL实例的机器学习服务没有安装。Cacher 是一剂即能达到一个网络系统，所以可以在SQL实例让机器学习服务安装。这两个SQL实例都可以有一个单独的服务帐户标识，它已被授权仅为特定端口连接到服务代理端点。另一种安全认证通信的方法是使用证书。在Service Broker端点授权是指 Microsoft.Technet：如何：允许使用证书服务代理网络接入（Transact-SQL） 详情。

所有组件组装在一起

在把所有的组件的地方，这里是我们的Web应用程序，允许我们用一个RESTful HTTP调用创建一个新的产品类型和刷新缓存相同的产品类型列表。在这里，该里面是管理数据和缓存是应用前端无形成分的墙。

 

结论

应用如电子商务，医疗电子可以从一个良好的缓存实现效益。通过扩展我们熟悉的技术的使用，我们可以得到一个解决方案。易于维护，不需要学习新的框架或特性。

我们的示例解决方案满足了我们的需要，因为

··数据时创建或由一个OLTP事务修改，系统刷新读取网络缓存系统。

··可采用异步事件刷新缓存，近实时。这不会影响原始事务的性能。

··得出安全线的事务处理和缓存系统之间通过HTTP以保持固定在OLTP数据库中的数据。

··是最小的监控功能；高速缓存的日志和异常日志，可以进一步提高建立一个管理控制台。

··与Service Broker消息组件，解决的办法是足够灵活的触发或达到网络系统在异步消息处理事情。换句话说，数据库与SQL服务代理消息传递集成，并基于接收到的数据，执行一个动作来获取或发送数据层以外的外部系统的数据。

··隔离，外部系统触发事件在专门的数据库，使用Service Broker消息有助于交易安全和OLTP数据库中的数据。

在githib这个项目的源代码是可用的。https://github.com/hi10p/sql17python

 

原作者：希滕德拉·帕特尔  

原文链接：

https://www.red-gate.com/simple-talk/sql/sql-development/power-python-sql-server-2017/