The Power of Python and SQL Server 2017（第六周翻译）

Python和SQL Server 2017的强大功能

Python是SQL Server 2017的新版本。它的主要目的是允许在SQL Server中使用基于python的机器学习，但它的使用远远不止于此，使用任何Python库或框架，为了提供一个可能的例子，Hitendra展示了如何安全地使用该特性来提供智能应用程序缓存，当数据更改触发缓存刷新时，SQL服务器可以自动指示。

SQL Server 2017已经增加了它的高级分析扩展，现在被称为“机器学习服务”， 通过让SQL server通过Python的“机器学习服务”在TSQL中执行Python脚本。这基本上提供了一种方法，可以让数据库程序员可以将数据直接传递给Python。这并不局限于为数据分析提供机器学习能力，因为Python有许多可用的模块和框架来解决许多问题，比如在数据结构中执行繁重的计算工作，用于分析、网络操作、数据库操作、web操作或本地网络文件系统操作的图形处理。显然，其中很多都是在中间件中做得最好的，但是在数据库系统中，有很多时候，直接与外部系统进行直接通信是比较方便的，而不是依靠外部流程来通过轮询数据源来执行任务。当不需要在数据库或数据层中有这样的解决方案时，当它不提供任何安全问题时，这是有意义的。

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

用例为Python

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

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

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

3、 使用通用数据格式(如JSON、XML或YAML)构建一个或多个系统之间的实时集成。

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

当然，很少有潜在的不利因素

1、 如果您对Python的使用需要internet访问，那么有一个风险，即必须保证安全的数据可能会在internet上意外地共享。任何互联网接入都必须经过网络的严格监管。

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

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

权衡这些优点和缺点时，如果Python能够最大限度地降低风险，那么它仍然可以发挥有益的作用。作为一个例子，让我们考虑一下如何使用Python来构建应用程序层使用的数据缓存系统。

示例解决方案缓存

缓存数据可能是提高应用程序性能的一种有效方法。在缓存的存储开销上，我们可以在面对类似于数据库的chatty网络通信，以及在面对重复查询时数据库的高资源消耗时获得有用的性能收益。当我们构建缓存基础设施时，我们面临的常见问题是何时刷新缓存的内容。在一定的时间间隔后，我们倾向于采用简单的重建缓存的方法。然而，这是非常低效的。当数据发生变化时刷新缓存，并仅刷新已更改的内容，这是更好的做法。当数据被创建、更新或删除时，我们可以做到这一点。有许多工具和框架可以解决刷新问题，但是它们遇到了如何确定数据中发生了什么变化以及更改何时发生的问题。数据库最适合做这些工作。

对于我们的缓存系统，它可以在这里提供，我们将把自己限制在Microsoft堆栈上，以防止Python本身。

微软SQL服务器2017年(CPT)

用于隔离事务数据库的服务代理。

Python执行脚本，该脚本可以在HTTP上更新缓存(Python 3.5可执行从Anaconda分发版的库)

4.5.2

ASP。我们的示例Web UI的网络MVC

ASP。Net WebAPI封装了我们的示例解决方案的缓存存储。

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

 

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

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

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

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

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

服务代理是一个可靠的SQL服务器消息传递框架，它可以帮助桥梁缓存代理和反式数据库。通过缓存代理接收消息可以处理以更新缓存。

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

 解决方案的架构

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

 先决条件

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

1、 缓存数据库托管的SQL实例必须安装有Python的机器学习服务

2、 要在缓存数据库中执行带有TSQL的Python脚本，应该运行SQL服务MS SQL Launchpad或SQL Server Launchpad。指的是微软。Net:微软机器学习服务

3、 启用SP配置的外部脚本执行，请参考微软。Doc:外部脚本支持服务器配置选项

 

 

 4、  反式数据库和隐藏托管环境应该有一个在其实例上创建的服务代理端点，如果它们在两个不同的SQL实例上独立托管，那么每个实例都应该有自己的端点。

5、 反式数据库和隐藏数据库应该启用代理。指的是微软。科技网:如何:在数据库中激活服务代理消息传递

 

 

   网络应用

Web应用程序有两个主要的MVC操作;使用HTTP谓词POST更新反式数据库中的新实体，以及用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数据库反式数据库有一些对象，包括表、存储过程和服务代理对象。

  对于我们的目的来说，过程更新产品类型更新产品类型表和一个新记录，并且确认产品类型缓存过程是缓存集成队列的激活过程，它接收来自目标的确认信息，即从隐藏数据库中处理消息。它还处理异常，并在缓存集成错误表中记录这些异常

 

        更多关于服务经纪人的信息可以在微软找到。DOC:SQL Server Service Broker

   对于我们的示例解决方案,反式数据库是一个创建更新缓存消息的源数据库创建一个新的产品类型记录时,一个消息来执行一个动作,它有更新消息类型的缓存集成合同发送一条消息,该消息带有缓存源服务目标数据库。服务有一个缓存队列，服务代理组件使用它来执行可靠的消息传递。缓存目标路由具有将消息传递到目标的信息

为了消除增加事务处理时间的任何机会，以及避免事务数据库中其余数据的安全风险，我们将使用一个名为隐藏数据库的代理数据库在我们的示例解决方案中分离缓存更新过程。服务代理消息传递基础设施将有助于连接反式数据库和隐藏数据库，基于事件的消息处理将使我们能够更新驻留在基于网络的系统上的缓存存储。当更新消息到达时，隐藏数据库扮演代理的角色，以执行缓存刷新。它通过执行Python脚本更新缓存。

      隐藏自己的数据库有:

1、       缓存的日志和隐藏的整合纠错表，跟踪缓存刷新时的记录，并记录缓存刷新过程中可能出现的任何错误。

2、       执行缓存更新过程通过服务代理接收来自反式数据库的传入消息。如果消息的类型是更新消息，则它执行另一个过程，更新Web缓存，它执行Python脚本执行。

a、      更新Web缓存过程的执行结果被保存在一个表变量中，然后在消息会话结束时插入到缓存日志表中。

b、      当接收到的消息有错误或结束消息类型时，该过程也会结束对话，并且在错误类型上，在缓存集成错误表中写入异常日志。

3、         更新Web缓存过程从传入的XML消息中提取Id和名称作为参数，并在Python脚本文本中嵌入这些值。脚本执行结果集是更新缓存日志的结构化表

  隐藏的服务代理对象，主要更新消息类型和缓存集成契约与反式数据库相同，缓存队列有一个名为perform缓存更新的激活过程，一个名为Cache Target的服务，该路由有关于反式数据库的服务缓存服务和端点地址的信息。

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

服务代理端点

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

 

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

这是在隐藏数据库的SQL实例中授权和授予端点连接到反式数据库的SQL实例服务账户的SQL代码。

 

类似地，这里是授权和授予端点连接到在反式数据库的SQL实例中的隐藏的SQL实例服务帐户的代码。

 

Python脚本

这里是Python脚本文本，作为TSQL变量@ update缓存中的字符串保存。它有一个带有逻辑的更新高速缓存方法来执行一个HTTP POST调用以RESTful。通过传递一个具有名称和Id字段的数据对象来缓存，该数据对象作为输入参数接收。它接收一个JSON对象并将其作为输出结果返回给调用者。

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

 

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

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

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

3、方法更新缓存的输出对象立即被转换为数组，以这种方式处理熊猫。数据框架可以将对象转换为数据结构，SQL server可以轻松地将其解释为带有行和列的表。

4、 分配给输出数据及对象的数据结构由SQL server在TSQL执行上下文中提供。

5、 最后一行程序dbo。使用结果集(作为类型dbo)更新Web缓存。更新缓存的日志);有一个用户定义的表类型dbo。更新缓存日志，这有助于保持底层列的顺序，避免在从接收的数据结构生成结果集的过程中出现任何不匹配。另一种方法是在Python内和结果集中构建一个映射的列结构。

数据库安全

反式数据库是一个OLTP数据库，我们不希望任何对系统的攻击有任何安全漏洞，因此在我们的示例解决方案方法中，这样的数据库可以托管在没有安装“机器学习服务”的SQL实例上。Cacher是一个能够到达基于网络的系统的代理，因此可以停留在安装机器学习服务的SQL实例上。这两个SQL实例都可以有一个单独的服务帐户标识，它被授权仅为一个特定的端口连接到服务代理端点。另一种安全认证通信的方法是使用证书。对于服务代理端点授权，请参考微软。科技网:如何:允许使用证书办理SQL提供服务代理网络访问。

所有组件放在一起

在将所有组件放置就绪后，下面是我们的web应用程序，它允许我们创建一个新的产品类型，并使用RESTful HTTP调用从刷新的缓存中列出相同的产品类型。在墙的后面有管理数据和缓存的组件在前端应用程序是看不见的。

结论

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

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

. 当通过OLTP事务之一创建或修改数据时，系统会刷新基于网络的缓存系统来读取数据。

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

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

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

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

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

此项目的源代码在githib中可用

https://github.com/hi10p/SQL17Python

本文率         订阅我们的双周刊时事通讯     Hiren Sojitra

 

SQL server中的Python是一个大新闻，很好地说明了它是如何受益的

亚当Machanic

这是一个有趣的方法，但我觉得这篇文章缺少一个关键的部分:使用Python来做这个而不是SQLCLR的优点和缺点是什么?我还想知道，作者是如何以及何时建议将消息推送到反式数据库SSB队列中的

帕特尔.希滕德拉

(Hitendrakumar Patel)是一名架构师，

主要从事企业应用程序架构、企业应用程序集成、数据架构、

业务智能以及与来自Microsoft stack的主要技术的性能调优。

他还专门从事研究和开发工具/工具/流程帮助企业系统开发生

命周期的质量,自动化、性能、维护和开发。经常把空闲时间

花在研究和分析技术上。

帕特尔.希滕德拉

 

谢谢你的评论，亚当。

问:使用Python而不是SQLCLR的优点和缺点是什么?

这里有一个非常好的问题，它提倡将SQLCLR和Python或任何其他外部脚本执行在SQL Server中进行比较。但是SQLCLR有它自己的世界，文章只关注SQL Server中的Python。在非常基础的情况下，我们都知道默认SQL的一个限制

组装目录，即在数据库中CLR程序集时，涉及到所有依赖的外部程序集的复杂性。当有人非常熟悉SQL中的Python脚本时，这种复杂性就会被消除。安全、性能等…总是问题任务的SQL是外部系统,这样的区域需要适当的设计方法来实现解决方案。

Q2:如何以及何时将消息推送到反式数据库 SSB队列?

在创建、编辑或删除候选数据时，可以执行数据缓存的最佳事件。通过说，在保存数据事务中，在向目标[Cacher]服务发送消息时，消息在transdb . dbo中排队。Cache Queue SQL-SSB。示例代码[TransDB].[dbo]。[更新产品类型]程序正在执行插入以及准备和发送消息到[Cacher]的CacheTarget服务。

相关文章

还在开发中

为什么我的数据库应用程序如此缓慢?

当您的应用程序运行缓慢时，反射操作将会导致数据库查询。诚然，一些更为奢侈的延迟可以被公平地归咎于缺失的索引或不必要的锁定，但在这出戏中还有其他潜在的反派角色，包括网络和应用程序本身。丹·特纳(Dan Turner)指出，你可以在深入细节之前确定问题的所在，从而节省大量的时间和金钱。

                                                                                                                                                                                                                        帕特尔.  希滕德拉

                                                                                                                                                                                                                       出自  Redgate Hub

                                                                                                                                                                                                       The Power of Python and SQL Server 2017