The power of Python and SQL Server 2017

the power of Python and SQL Server 2017

Python is the new SQL Server 2017. Its main purpose is to allow the use of machine-oriented learning in SQL Server, but it can be used far more than this, with any Python library or framework. Providing an example of what is possible, Hitendra shows how to use this feature to safely provide a smart application cache that triggers a cache refresh when SQL Server can automatically display data changes.

MS SQL Server 2017 adds its advanced analytics extensions, now known as "machine learning services," by enabling SQL Server to execute Python scripts within TSQL through the machine learning service with Python. This basically provides a way for a database programmer to be able to pass data directly from Python. This is not limited to the effectiveness of providing machine learning data analysis functionality, because Python has many readily available modules and frameworks to solve many problems, such as performing a large number of computations and data structures, profiling, network operations, database operations, network operations, or local/network-based file system operations. Obviously, these are best in middleware, but in a database system, sometimes it is easier to communicate directly to external systems rather than relying on external processes to perform tasks by polling the data source. This makes sense when it's not such a problem there is a solution at the database or data layer when it does not provide any security issues.

here , we will try to demonstrate an example using the Python extension in Advanced analytics, which shows that the database can trigger an external process to execute an active data as a parameter. This is to take into account security issues, data reliability and transaction response times.

with Example of Python

Some tasks can be done more easily by calling a The Python script is from SQL instead of relying on middleware. This is especially the case in which the task is made by an event database. Tasks may include

1. send data , or receive tcp/http/soap from a network-based system.
2. leverage local platform resources , such as file systems, networks, or GPUs.
3. build the real ?? Integration between one or more systems by using a common data format such as JSON, XML, or YAML.
4. Communication with data or files generated by external applications.

naturally , there are some potential drawbacks.

1. If you use Python needs to be online, and it is possible that data must be kept secure and may be accidentally shared on the internet. Any Internet access must be carefully supervised by the network.
2. security risk exposure by allowing execution The Python script is enabled on the server through external script execution.
3. resource-intensive Python scripts on the same server can affect the performance of large OLTP systems that are being traded.

weighing these pros and cons , there seems to be a time when Python can play a useful role if it can minimize risk. As a case , Let's consider how to use python to build a data caching system layer used by an application.

Sample Solution Cache

cached data can be an effective way to improve application performance. The storage overhead cost of caching , we can make useful performance gains face-to-face to such as talkative with database, network communication and high resource consumption by the database when facing duplicate queries. When we build a cache infrastructure, we face a common problem when the cached content is refreshed. We tend to use simple solutions to rebuild the cache after a certain interval of time. However , this is very inefficient. It is best to refresh the cache when the data changes, and only refresh what has changed. We can access real-time data when it is created, updated or deleted. There are many tools and frameworks available to resolve refresh issues, but they suffer from problems with how to determine the data that has changed and commit after the modification. The database is most capable of doing this.

for our cache system , available sources, we will restrict everything in the Microsoft stack except for Python itself.

• Microsoft SQL Server (CPT)
  • Service proxy to isolate the transaction database.
  • Python executable script update cache via HTTP (Python 3.5 executable library from Python distribution)
• . 4.5.2 Net
  • Asp. NET MVC sample Web UI
  • Asp. The net WEBAPI Encapsulation cache stores our sample solution.

here is a diagram of our sample solution caching system :

webapplication provides a user interface to read and update data.

• restful.cache application In our example cache storage solution is ASP. NET WebAPI2, its content type JSON. the Http-get operation provides locally cached data (a static collection).
• MS SQL Server (CPT) is a database server
  • Transdb OLTP database, busy processing transactions.
  • Hide Yourself Agent Execution The Python script executes the database and enables script execution with the options enabled by the external script. Refers to Microsoft. Doug : The external script enables the server configuration option.
  • Service Agent A reliable messaging framework for SQL Server, AIDS Bridge cacher-agent Transdb. The received message processing cacher-agent updates the cache.
• Python is the integrated scripting language and SQL 2017 Database System (CPT).

the architecture of the solution

in our solution , we will cache the entity product type name for restful. The cache application and WebApplication will have a function to create a new product type entry from Restful.cache and read.

Prerequisites

In addition , there are some prerequisites and some more information that we need to consider.

1. Where SQL instance Cachedb is held must be installed "machine learning" technical service with Python
2. Perform a The Python script cachedb tsql,sql service mssqllaunchpad or SQL Server launcher should run. Refers to Microsoft. NET : Microsoft Machine Learning Services
   1. make an external script execute sp_configure, please refer to Microsoft. Doug : External scripts enable server configuration options

sp_configure " external scripting enabled ", 1; Re-configuration;

2. The TRANSDB and Cacher hosting environment should have an instance of the service proxy endpoint created on it, if these are managed independently in two different SQL instances then each instance should have its own endpoint.
3. The Transdb and Cacher databases should have proxies enabled. Refers to Microsoft. Technology : How to activate service Proxy message delivery in a database

Change database transdb set Enable_broker; Go Change database cachedb set Enable_broker; Go

Web Applications

WebApplication has two major MVC actions; one updates a new entity Transdb HTTP verb post and another action returns the product List type with the HTTP verb from the cache.

Restful. Caching has two ways of doing this, one updating the cache with the new entity product type with the HTTP verb, and another locally cached product type for all caches.

for our sample solution , two applications reside in IIS in a single application pool identity hold application. But for the actual system realization, can the personal network server hosts the environment in the intranet or the Internet environment.

Restful. Caching authorization rules only two service accounts handle HTTP requests.

ABC \ WEBAPP_SVC and ABC \ Cacheragent_svc. The ABC \ CACHERAGENT_SVC service account allows Python scripts to flush caches in SQL applications over HTTP.

WebApplication's ABC \ WEBAPP_SVC users in authorization rules allow access to restful ways. Cache the application.

SQL database and service proxy

The OLTP database Transdb has several objects, including tables, stored procedures, and service proxy objects.

for our purposes , the Process updateproducttype Update ProductType table with the new record and the Acknowledgeproducttypecache process activation process is cacheintegration queue, It receives a message from the target acknowledgment processing, that is, from the Cacher database. It also handles exceptions, which are in cacheintegrationerror tables and logs.

more information can be found in the service agent Microsoft. Doug : SQL Server service Proxy

for our sample solution , Transdb source database, create update cache message when creating a new ProductType record, a message to perform an action, it has updatemessage message type, Cacheintegration Contracts and Cachesource services send messages to the target database. The service used has a Cachequeue service Proxy component that performs reliable message delivery. The Tocachetarget route has the message delivered to the destination.

eliminate any opportunity to increase transaction processing time and to avoid any security risks in the rest of the data in the transactional database, we will decouple the cache update process by using a proxy database called hiding itself in our sample database solution. The Service Broker messaging infrastructure will help TRANSDB and Cacher databases and connections, and event-based message processing will enable us to update the cache storage residing on the network system. The Cacher database plays an agent role in order to perform a cache refresh when an update message arrives. It updates the cache by executing a python script. .

to hide your own database are :

1. Cachelog and Cacheintegrationerror tables that track any errors that may occur during cache refreshes and records during cache refreshes.
2. The performcacheupdate process receives incoming messages from TRANSDB through the service agent. If the type of message is Updatemessage then it executes another program,Updatewebcache, executing the python script.
   1. The execution results of the Updatewebcache program are saved in a table variable, and then the end of the Cachelog table is inserted into the message dialog.
   2. The process also ends the conversation when you receive a message that has an error or an end message type , the error type, and the Exception log is written in the Cacheintegrationerror table.
   3. The Updatewebcache process extracts these value text from the passed-in XML message ID and name as parameters and embedded in the Python script. The result set of the script execution is a structured upddatecachelog type table.

Hide your Service proxy object , the primary updatemessage message type and cacheintegration transdb contract, Cachequeue activation process is called perfomcacheupdate, Services are called Cachetarget, Route transdb information for service Cacheservice and endpoint addresses.

for our sample solution , the maximum Queue reader sets the database for the queue to 1. This can be increased if necessary, for example, if the data modification is high, you need to increase the cache refresh rate.

Service Proxy Endpoint

Our solution , where the database resides in the same instance, is to send and receive messages using the same service proxy endpoint.

But if we want the personal host db instance then each The service account for the SQL instance should have a service proxy endpoint. And the SQL instance should have permission to allow messages to be sent to each other's endpoints. The authorization and approval of the connection can be used with the following group TSQL commands. Note that there is a sender and receiver on the other side of the messaging infrastructure, as described earlier, if the SQL instance sender and receiver are part of the process, then each instance should have its own processes identity. Represents each SQL Server that is running in its own identity.

this is The SQL code authorization and approval endpoint connects to an instance of the Transdb SQL Instance service account (identity) in the SQL Cacher database.

Change authorization at endpoint:: Servicebrokerendpoint come [ABC \transdb_svc] Go Grant connection at endpoint:: Servicebrokerendpoint come [ABC \transdb_svc] Go

Similarly , this is an instance of the SQL Transdb database that authorizes and approves the endpoint connection code to hide its own SQL Instance service account (identity).

Change authorization at endpoint:: Servicebrokerendpoint come [ABC \cacheragent_svc] Go Grant connection at endpoint:: Servicebrokerendpoint come [ABC \cacheragent_svc] Go

python script

this is Python script text, @UpdateCache tsql is saved as a string variable. It has a Updatecache method with logic to perform an HTTP post call to restful. The cache is received as an input parameter through a data object name and ID field. Receives a JSON object and returns it to the caller as a method of outputting the result.

at the end of the script, the returned object is converted to an array, so it can be structured as a result of SQL.

Statement @UpdateCache NVARCHAR (max) = N ' Panda as the # of postpartum depression import data structure scheme def updatecache (name, id): Import requests as HTTP # HTTP request packets #演奏HTTP POST, updating the cache HttpRequest = HTTP. Article (http://localhost/RESTful. Cache/producttype/updatecache,{Name: Name, id:id}) Cachelog = Httprequest.json () Back to Cachelog #更新缓存和构建日志元素 Log =[updatecache (' + @ ' name + ', ' + cast (@Id as VARCHAR (10) + ')] #返回数据帧即从SQL表结构 Outputdataset = PND. DataFrame (data = log) ‘;

a few things to note are the Python scripts that use SQL Server.

1. we can write a script or a sequential approach we did in this solution. Alternatively , we can create an inline class or create a package and import the command prompt in the python pip command.
2. For the MS SQL version of CPT, the import statement can only be imported into a package-scoped place, so we can note that the Import request import statement exists inside the method Updatecache, and the import statement imports the giant panda in the script that exists in the last line of the script.
3. Methods for outputting objects Updatecache immediately converts to an array of pandas. Dataframe is able to convert an object into a SQL Server data structure that can be easily understood as a table row and column.
4. assigned to The Outputdataset object's data structure can be used by the TSQL execution context of the SQL Server.
5. The last line of the DBO program. Updatewebcache, result set (dbo. Updatecachelog type); There is a user-defined table dbo type. Updatecachelog helps keep the underlying columns in order and avoids any mismatch in the data structures that are received by the result set generated. Another approach is to build a python and result set in a mapped column structure.

Database Security

Transdb OLTP database, we do not want any security vulnerabilities in the attack system, so our sample solution method, which can be hosted on a SQL DB instance, does not have machine learning services installed. Hide yourself as a proxy and be able to reach a network-based system, so you can install machine learning services in a SQL instance. The SQL instance can have the identity of a personal service account, and the authorized service proxy endpoint connection is available only for a specific port. Another secure communication is through the use of certificates for authentication. The authorization service proxy endpoint references Microsoft. Technology : How to: Allow Service proxy network access to use certificates (Transact-SQL) for more details.

all components are put together

put all the components , this is our webapplication allows us to create a new producttype and list the same product type with restful HTTP calls to flush the cache. The back wall has component management data and the cache is an invisible front-end application.

Conclusion

applications such as e-commerce, medical e-government can benefit from a good caching implementation. By extending the use of technology, we are all familiar with the concept that we can get a solution that is easy to maintain without learning a new framework or feature.

meet our needs , as our sample solutions

• when data is created or modified by a OLTP transactions, the system refreshes the network cache 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 boundary between a security transaction , an OLTP database that guarantees data security through an HTTP caching system.
• it with minimal enable monitoring function ; cache logs and exception logs can be enhanced to further establish a management console.
• with the service proxy messaging component , the solution is flexible enough to trigger or reach the network system when asynchronous message processing occurs. In other words, with SQL Database integration service proxy information and receive-based data, an action is taken to get or send data outside the data layer of the external system.
• isolated , External system triggering events The internal dedicated database uses the messaging service proxy to help secure transaction and OLTP database data.

The source code for this project is available in Githib. Https://github.com/hi10p/SQL17Python

Original link:

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

The power of Python and SQL Server 2017