做网站的团队,电商网站开发分享,邯郸最新通知今天,新手做哪类网站Python和SQL Server 2017的强大功能
原文来自#xff1a;https://www.red-gate.com/simple-talk/sql/sql-development/power-python-sql-server-2017/
Python是SQL Server 2017的新版本。它的主要目的是允许在SQL Server中使用基于Python的机器学习#xff0c;但它可以使用的…Python和SQL Server 2017的强大功能
原文来自https://www.red-gate.com/simple-talk/sql/sql-development/power-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向基于网络的系统发送数据或接收数据。
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分发版的库)
Net 4.5.2
我们的示例Web UI 的asp.netMVC
ASP.Net WebAPI封装了我们的示例解决方案的缓存存储。
下面是示例解决方案缓存系统的图形表示:
WebApplication提供了一个用户界面来读取和更新数据。
在我们的示例缓存存储解决方案中使用ASP构建RESTful.Cache应用程序。Net WebAPI2它的内容类型是JSON。http - get操作从本地缓存(静态集合)提供数据。
SQL Server 2017(CPT)是一个带有以下特征的数据库服务器
TransDB OLTP数据库繁忙的处理事务。
Cacher代理数据库执行Python脚本执行启用“启用外部脚本”选项启用脚本执行。指的是Microsoft.Doc:外部脚本支持服务器配置选项。
服务代理是一个可靠的SQL服务器消息传递框架它可以帮助桥梁缓存代理和TransDB。通过缓存代理接收消息可以处理以更新缓存。
Python是SQL 2017(CPT)数据库系统的集成脚本语言。
解决方案的架构
在我们的解决方案中我们将在RESTful中缓存实体的产品类型名称。缓存应用程序和WebApplication将有一个函数来创建新产品类型条目并从restful. Cache中读取。
先决条件
顺便说一下我们需要考虑一些先决条件和更多的信息。
1、CacheDB托管的SQL实例必须安装有Python的机器学习服务
2、要在CacheDB中执行带有TSQL的Python脚本应该运行SQL服务MSSQLLaunchpad或SQL Server Launchpad。指的是Microsoft.Net:微软机器学习服务
3、启用SP_Configure的外部脚本执行请参考Microsoft.Doc:外部脚本支持服务器配置选项
4、TransDB和Cacher托管环境应该有一个在其实例上创建的服务代理端点如果它们在两个不同的SQL实例上独立托管那么每个实例都应该有自己的端点。
5、TransDB和Cacher数据库应该启用代理。指的是微软。Technet:如何:在数据库中激活服务代理消息传递
6、
.NET Applications
WebApplication有两个主要的MVC操作;使用HTTP谓词POST更新TransDB中的新实体以及用HTTP谓词返回从缓存返回产品类型列表的另一个操作。
RESTful.Cache有两种操作方法一种是使用带有HTTP谓词POST的新添加的实体产品类型更新缓存另一种是从本地缓存获取所有缓存的产品类型。
对于我们的示例解决方案这两个应用程序都驻留在IIS下的单独应用程序池标识中以保证应用程序安全。但是对于实际的系统实现托管环境可以是一个局域网或internet环境中的一个单独的web服务器。
RESTful.Cache授权规则只有两个服务帐户来处理HTTP请求。
abc \ WebApp_SVC和abc \ CacherAgent_SVC。abc\ CacherAgent_SVC服务帐户允许SQL中的Python脚本通过HTTP到达应用程序以刷新缓存。
abc\ WebApp_SVC用户使用具有授权规则模式的web应用程序以允许访问 RESTful.Cache应用程序。
SQL数据库和服务代理
OLTP数据库TransDB有一些对象包括表、存储过程和服务代理对象。
对于我们的目的,过程UpdateProductType更新ProductType表与新记录和AcknowledgeProductTypeCache过程的激活过程是CacheIntegration队列,它接收来自目标确认处理消息时,即从Cacher数据库。它还处理异常并在CacheIntegrationError表中记录这些异常。
更多关于服务经纪人的信息可以在Microsoft.DOC:SQL Server Service Broker找到。
对于我们的示例解决方案TransDB是一个源数据库它在创建一个新的ProductType记录时创建更新缓存消息这是一个执行动作的消息因为它有UpdateMessage消息类型一个CacheIntegration合同以发送带有CacheSource服务的消息以针对数据库。该服务有一个CacheQueue它由服务代理组件使用以执行可靠的消息传递。ToCacheTarget路由具有将消息传递到目标的信息。
为了消除增加事务处理时间的任何机会以及避免事务数据库中其余数据的安全风险我们将使用一个名为Cacher数据库的代理数据库在我们的示例解决方案中分离缓存更新过程。服务代理消息传递基础设施将有助于连接TransDB和Cacher数据库基于事件的消息处理将使我们能够更新驻留在基于网络的系统上的缓存存储。当更新消息到达时Cacher数据库扮演代理的角色以执行缓存刷新。它通过执行Python脚本更新缓存。
隐藏自己的数据库有:
1、CacheLog和CacheIntegrationError表跟踪缓存刷新时的记录并记录缓存刷新过程中可能出现的任何错误。
2、PerformCacheUpdate过程通过服务代理接收来自TransDB的传入消息。如果消息的类型是UpdateMessage那么它将执行另一个过程UpdateWebCache它执行Python脚本执行。
a、UpdateWebCache过程的执行结果被保存在一个表变量中然后在消息会话结束时插入到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代码。
类似地这里是授权和授予端点连接到在TransDB数据库的SQL实例中的Cacher的SQL实例服务帐户[identity]的代码。
Python脚本
这里是Python脚本文本作为TSQL变量 updatecache中的字符串保存。它有一个带有逻辑的UpdateCache方法可以执行一个HTTP POST调用rest。通过传递一个具有名称和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.使用结果集(作为dbo . updatecachelog)的UpdateWebCache;有一个用户定义的表类型dbo.UpdateCacheLog它有助于保持底层列的顺序避免在从接收的数据结构生成结果集的过程中出现任何不匹配。另一种方法是在Python内和结果集中构建一个映射的列结构。
数据库安全
TransDB是一个OLTP数据库我们不希望任何对系统的攻击有任何安全漏洞因此在我们的示例解决方案方法中这样的数据库可以托管在没有安装“机器学习服务”的SQL实例上。Cacher是一个能够到达基于网络的系统的代理因此可以停留在安装机器学习服务的SQL实例上。这两个SQL实例都可以有一个单独的服务帐户标识它被授权仅为一个特定的端口连接到服务代理端点。另一种安全认证通信的方法是使用证书。对于服务代理端点授权请参考Microsoft.Technet:如何:允许使用证书(transact - sql)提供服务代理网络访问。
所有组件放在一起
在将所有组件放置就绪后下面是我们的web应用程序它允许我们创建一个新的ProductType并使用RESTful HTTP调用从刷新缓存中列出相同的产品类型。在墙的后面有管理数据和缓存的组件在前端应用程序是看不见的。
结论
电子商务、医疗保健等应用程序可以从良好的缓存实现中获益。通过扩展我们熟悉的技术的使用我们可以得到一个易于维护的解决方案而无需学习新的框架或特性。我们的示例解决方案满足了我们的需要。
当通过OLTP事务之一创建或修改数据时系统会刷新基于网络的缓存系统来读取数据。它能够使用异步事件刷新缓存接近实时。这不会影响原始事务的性能。
它可以通过HTTP在事务和缓存系统之间绘制一条安全界限以便将数据保存在OLTP数据库中。
它启用了一个最小的监视功能;缓存日志和异常日志可以进一步增强以构建管理控制台。
使用服务代理消息传递组件时当异步消息处理发生时解决方案可以灵活地触发或到达基于网络的系统。换句话说数据库与SQL服务代理消息集成基于接收到的数据执行一个操作来获取或发送数据到外部的外部系统这些外部系统位于数据层之外。
通过使用服务代理消息传递隔离外部系统在专用数据库内触发事件有助于确保OLTP数据库的事务和数据。
此项目的源代码在githib中可用。
