Powerful features of Python and SQL Server 2017
Python is a new feature of SQL Server 2017. It is primarily intended to allow Python-based machine learning in SQL Server, but it can be used with any Python library or framework. To provide a possible example, Hitendra shows how to safely use this feature to provide smart application caching, where SQL Server can automatically indicate when data changes to trigger a cache refresh.
MS SQL Server 2017 has implemented a Python script in tsql with a "machine learning Service using Python" by enabling SQL Server to add to its advanced analytics extension, now known as the Machine Learning service. This basically provides a way for a database programmer to pass data directly from Python. This usefulness is not limited to providing machine learning capabilities for data analysis, because Python has many easy-to-use modules and frameworks to solve many problems, such as the use of data structures to perform a large amount of computational work, graphical processing for analysis, network operations, database operations, network operations, or local/ The file system operation of the network. Obviously, many of them do best in terms of middleware, but in database systems, it is sometimes easier to communicate directly with external systems rather than relying on external processes to perform tasks by polling the data source. This is not a problem if there is one such solution in the database or data tier, and no security issues are provided.
Here, we will try to demonstrate an example of using Python in Advanced Analytics extension to show how the database triggers an external process to perform activities on the data provided as parameters. This is to consider security, data reliability, and transaction response time issues.
Python's use case
Some tasks can be done more easily by invoking Python scripts from SQL instead of relying on middleware. This is especially the case in which the event initiates a task in the database. Tasks may include
1. Send data to the network system via TCP/HTTP/SOAP or receive data from a network-based system.
2. Leverage local platform resources, such as file systems, networks, or GPUs.
3. Build real-time integration between one or more systems by using a common data format, such as Json,xml or YAML.
4. Generate data or files by communicating with external applications.
Of course, there are few potential drawbacks.
1. If you use Python to require Internet access, there is a risk that data that must be kept secure may be accidentally shared over the Internet. Any Internet access must be carefully monitored by the network.
2. Allow the execution of Python scripts on the server through "Enable external scripting" to expose the security risk.
3. Resource-intensive Python scripts on the same server can affect the performance of ongoing transactions on large OLTP systems.
By measuring these advantages and disadvantages, it seems that python can sometimes play a useful role if the risk can be minimized. As an example, let's consider how we can use Python to build a data caching system for the supply layer.
Caching Sample Solutions
Caching data can improve the performance of your application. At the expense of the cache's storage cost, we can gain useful performance gains when encountering chat network traffic with the database and high resource consumption when the database is facing duplicate queries. When we build our cache infrastructure, we face frequently asked questions about when to flush cached content. We tend to adopt a simple solution that rebuilds the cache after a certain interval of time. However, this is very inefficient. Refreshes the cache better when the data changes, only refreshes the changed content. When creating, updating, or deleting data, we can do this in near real-time situations. There are many tools and frameworks that can be used to resolve refresh issues, but they are subject to how to determine when data changes and when changes occur. The database is the best that all can do.
For the caching system we provide here, we will limit ourselves to the Microsoft stack to prevent Python itself.
? Microsoft SQL Server (CPT)
O Service agent isolates the transaction database.
Opython execute script, can update cache via HTTP (Python 3.5 executables and libraries in Anaconda release)
?. Net 4.5.2
Oasp.net
MVC for our sample web UI
Oasp.net
The WEBAPI encapsulated cache is stored as our sample solution.
Here's a diagram of our sample solution cache system:
The WebApplication provides a user interface to read and update data.
? The Restful.cache application in our sample cache storage solution is using ASP.
WebAPI2 is built with a content type of JSON. The Http-get operation transmits data from a local cache (a static collection).
? MS SQL Server (CPT) is a database server
Otransdb OLTP database, handling transactions busy.
Ocacher executes the proxy database executed by the Python script, enabling the script execution of the "External script enable" option. See Microsoft.doc: Enable server configuration options for external scripting.
Oservice Broker is a reliable messaging framework for SQL Servers that helps bridge cacher-agent and Transdb. You can process cacher-agent received messages to update the cache.
? Python is an integrated scripting language for database systems that use SQL (CPT).
The architecture of the solution
In our solution, we will cache the entity "Product type name" in the Restful.cache application, and WebApplication will have the ability to create a new product type entry and Restful.cache read.
Conditions
In addition, there are some prerequisites and some information we need to consider.
1.SQL instance, where Cachedb hosting must have "hemp and python Root Learning service" installed
2. To execute a python script in cachedb using TSQL, you should run SQL Service mssqllaunchpad or SQL Server Launchpad. See Microsoft.NET
: Microsoft Machine Learning Services
3. To enable external script execution using sp_configure, see Microsoft.doc: Enable server configuration options for external scripts
sp_configure ' external scripts enabled ', 1;
RECONFIGURE;
4. The managed Environment for the TRANSDB and Cacher should have its instance to create the Service Broker endpoint, and if this is managed independently in two different SQL instances then each instance should have its own endpoint.
5. The Transdb and Cacher databases should have proxies enabled. See microsoft.technet: How to: Activate Service Broker message delivery in a database
6.
ALTER DATABASE transdb SET enable_broker;
GO
ALTER DATABASE cachedb SET enable_broker;
GO
. NET application
The WebApplication has two major MVC actions; One uses the HTTP verb post to update a new entity in Transdb, and the other uses an HTTP verb get to return a list of product types from the cache.
Restful.cache has two ways of doing this, using the HTTP verb post to update the cache for the newly added entity product type, and the other to get all the cached product types from the local cache.
For our sample solution, both applications are hosted under individual application pool identities in IIS to protect application security but for actual system implementations, the managed environment can be a single Web server in an intranet or Internet environment.
The Restful.cache authorization rule has only two service accounts to handle HTTP requests, i.e.
ABC \ WEBAPP_SVC and ABC \ Cacheragent_svc. The ABC \ CACHERAGENT_SVC service account allows Python scripts in SQL to reach the application via HTTP to flush the cache.
The ABC \ WEBAPP_SVC user is used by WebApplication with authorization rule mode to allow access to the Restful.cache application. SQL database and Service proxy
The OLTP database Transdb has several objects, including tables, stored procedures, and Service Broker objects.
For our purposes, the Updateproducttype process updates the ProductType table with new records, and the Acknowledgeproducttypecache process is the cacheintegration queue activation process. The acknowledgement from the target is received from the Cacher database when the message is processed. It also handles exceptions, if any, and records them in the Cacheintegrationerror table.
For more information about Service Broker, please visit the Microsoft.DOC:SQL Server Service Broker
For our sample solution, Transdb is the source database that creates the update cache message when the new ProductType record is created, the message that performs the action, it has the Updatemessage message type, The cacheintegration contract sends the Cachesource service to the target database. The service has cachequeue, which is used by Service Broker components to perform reliable messaging. The Tocachetarget route has the information to pass the message to its destination.
To eliminate any chance of increasing transaction processing time and to avoid any security risks to the rest of the data in the transactional database, we will de-cache the update process by using the proxy database named Cacher database in our sample solution. The Service Broker messaging infrastructure will help connect the TRANSDB and Cacher databases, and event-based message processing will enable us to update cache storage residing on a network-based system. The Cacher database is the role that hits the cache refresh agent when the latest message arrives on the order. It updates the cache by executing a python script.
The Cacher database has:
1.CacheLog and Cacheintegration error tables to track when the cache is refreshed and has a record of any errors that may occur during the cache refresh process.
2. The performcacheupdate process from receiving incoming messages TRANSDB through a service proxy. If the type of the message is updatemessage, then it executes another procedure Updatewebcache, executing the python script.
The execution results of the A.updatewebcache procedure are saved in the table variable and then inserted into the Cachelog table at the end of the message conversation ...
B. When the received message has an error or end message type, the procedure ends the dialog and writes the exception log to the Cacheintegrationerror table on the error type.
3. The Updatewebcache program extracts the identities and names, passes the incoming XML messages as parameters, and embeds these values in the Python script text. The script execution result set is a structured table of type Upddatecachelog.
The Cacher service proxy object, mainly the Updatemessage message type and cacheintegration contract is the same transdb,cachequeue there is a process called activation, perfomcacheupdate, One called the service Cachetarget and the route has approximately information Transdb service Cacheservice and endpoint addresses.
For our sample solution, the maximum Queue reader for the database queue is set to 1. If necessary, you can increase this, for example, if the data is modified very high, and you need to increase the cache refresh rate.
Service Proxy Endpoint
For our solution, the database is hosted on the same instance, so both use the same service Broker endpoint to send and receive messages.
However, if we are to host the database on a single instance, then the service account for each SQL instance should have a services broker endpoint. And all two SQL instances should have permissions to allow messages to be sent to each other's endpoints. The authorization and grant of the connection can be done through the following TSQL command set. Note that in the messaging infrastructure, there is one sender and the other is the receiver, as mentioned, if the SQL instance is part of the sender and receiver, then each instance should have its own process identity. Shows how each SQL Server runs under its own identity.
This is the SQL code used to authorize and grant endpoint connections to TRANSDB SQL Instance service account [identity] in the SQL instance of the Cacher database.
ALTER AUTHORIZATION on Endpoint::servicebrokerendpoint to [Abc\transdb_svc]
GO
GRANT CONNECT on Endpoint::servicebrokerendpoint to [Abc\transdb_svc]
GO
Similarly, here is the code for authorizing and granting the endpoint connection to the cache's SQL Instance service account [Identity] in the SQL instance of the TRANSDB database.
ALTER AUTHORIZATION on Endpoint::servicebrokerendpoint to [Abc\cacheragent_svc]
GO
GRANT CONNECT on Endpoint::servicebrokerendpoint to [Abc\cacheragent_svc]
GO
Python script
This is the Python script text, which is saved as a string in the TSQL variable @updatecache. It has a logical Updatecache method that performs an HTTP post call to Restful.cache by passing a data object that has a name and ID field that is received as an input parameter. It receives a JSON object and returns it to the caller as the output of the method.
At the end of the script, the returned object is converted to an array, so it can be structured as a SQL result.
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)
‘;
There are some notable things to be aware of when using Python scripts in SQL Server.
1. We can write a sequential script or group them into the methods we have done in this solution. Alternatively, we can create an inline class or create a package and import them in Python using the pip command at a command prompt.
2. In the CPT version of this MST, the import statement only imports the package within the scope it is placed in, so we can note that the import statement exists in Importcache, and import statements are imported into the panda at the top of the script at the end of the script.
3. The output object of method Updatecache is converted to an array immediately, so pandas. Dataframe can convert an object into a data structure, and SQL Server can easily interpret it as a table with rows and columns.
4. The data structure assigned to the Outputdataset object is available in the TSQL execution context of SQL Server.
5. Last line of the program dbo. Updatewebcache,with RESULT sets (as TYPE dbo. Updatecachelog); Has a user-defined table type dbo. Updatecachelog, which helps maintain the order of the underlying columns and avoids any mismatches that occur during the generation of a result set from the received data structure. Another approach is to build a mapped column structure in Python and result sets.
Database security
Transdb is an OLTP database that we do not want to have any security vulnerabilities to the system, so through our sample solution, this database can be hosted on a SQL instance that does not have the machine learning service installed. Cacher is a proxy that can reach a network-based system, so it can remain on the SQL instance where the Machine learning service is installed. Two SQL instances can have a separate service account identity that is authorized to connect only to the Service Broker endpoint for a specific port. Another way to secure authenticated communications is to use a certificate. For Service Broker endpoint authorization, see microsoft.technet: How to: Allow Service agent network access by using certificates (Transact-SQL) for more details.
All components are put together
After placing all the components, our webapplication allows us to create a new producttype and list the same product type from the refreshed cache through restful HTTP calls. There are components behind the wall that manage data, and caching is not visible to front-end applications.
Conclusion
Applications such as e-commerce, medical e-governance, etc. can benefit from a good cache implementation. By extending the use of our familiar technologies, we can get easy-to-maintain solutions without the cost of learning new frameworks or features.
Our sample solutions are in line with what we need
? When data is created or modified through one of the OLTP transactions, the system refreshes the network-based caching system for read access.
? It can use asynchronous events to flush the cache, near real-time. This does not affect the performance of the original transaction.
? It can draw a security thread between the transaction and the cache system via HTTP to keep the data secure in the OLTP database.
? It has the smallest monitoring function; A cache log and exception Log can further enhance the build management console.
? Using the Service Broker message component, the solution is flexible enough to trigger or reach a network-based system when asynchronous message processing occurs. In other words, the database is integrated with the SQL Service broker message and, based on the received data, performs an action to fetch or send the data to a system other than the external data tier.
? Isolating external systems by using service proxy messages triggers events within a dedicated database to help protect the transactions and data of the OLTP database. (https://www.red-gate.com/simple-talk/sql/sql-development/power-python-sql-server-2017/)
Powerful features of Python and SQL Server 2017
