Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2017 and Python Basics



By:
Overview

Python is a widely used high-level interpreted programming language for general-purpose programming. Python has a design philosophy that emphasizes code readability and a syntax that allows programmers to express concepts in fewer lines of code than might be used in languages such as C++ or Java.

Some of the widely used python based libraries are Numpy, Pandas, Scikit and others. Scikit is a machine learning library for the Python programming language. NumPy is a library for the Python programming language, adding support for large, multi-dimensional arrays and matrices, along with a large collection of high-level mathematical functions to operate on these arrays. Pandas is a Python package providing fast, flexible, and expressive data structures designed to make working with relational or labeled data both easy and intuitive. It aims to be the fundamental high-level building block for doing practical, real world data analysis in Python. Python has a community based development model, and it contains many powerful data processing libraries like R. Python and R are the two of the most widely used programming languages for data science.

SQL Server Machine Learning Services installs an open source distribution of Python, as well as packages provided by Microsoft that support distributed and/or parallel processing. The architecture is designed such that external scripts using Python run in a separate process from SQL Server. Machine Learning Services integrates the Python language with SQL Server, which helps in performing analytics close to the data and eliminate the costs and security risks associated with data movement.

The traditional data analytics methodology relies on transporting and transforming data from OLTP databases > Data Warehouses > Data Marts using PowerShell for administration, SQL Server Integration Services (SSIS) for ETL, SQL Server Analysis Services (SSAS) for multi-dimensional / in-memory analytics, and SQL Server Reporting Services (SSRS) for reporting. Data manipulation using set based operations and mathematical algebra has been the best possible solution with T-SQL on data stored in OLTP databases. Using Python with T-SQL extends the power of data science, statistical computing, machine learning and other advanced predictive analytics capabilities to OLTP systems.

This tutorial is intended to help experienced T-SQL Developers, DBAs, Data Analysts and Data Science enthusiasts to start using Python language with T-SQL. This enables data science and analytics tasks closest to the actual data, without the need to follow a traditional BI methodology of transporting and transforming data across repositories. The tutorial is structured in five lessons, with each lesson focused on explaining corresponding key points as mentioned below.

Lesson 1: Python in the SQL Server Ecosystem

  • What Python in SQL Server means for developers, DBAs, data analysts and data scientists
  • Python with T-SQL compared to Python versus T-SQL
  • Applications of R in SQL Server
  • Python tools in the SQL Server Ecosystem
  • Python and SQL Server Database Engine Integration Architecture

Lesson 2: Installing Machine Learning Services

  • Install SQL Server 2017 RC2 with Machine Learning Services
  • Install VS 2017 with Python tools
  • Explore Python installation and tools

Lesson 3: Basic Python Concepts

  • Python version, Libraries, Datasets
  • Variables, Comments, Printing Data
  • Arithmetic, Operators, Loops

Lesson 4: Python with T-SQL

  • Using sp_execute_external_scripts
  • Reading data in Python from T-SQL
  • Returning manipulated data from Python to T-SQL

Lesson 5: Data Analysis with Python

  • Graphical analysis with Python
  • Using Python scripts in a stored procedure
  • Useful Resources

By the end of this tutorial, you should be able to develop basic Python scripts that read data from OLTP databases and apply graphical analysis. So, let's get started with the first lesson to understand the influence of Python in SQL Server and its impact on the SQL Server community.






More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools