Бөлісу құралы:


Руководство по Python. Изучение и визуализация данных

Область применения: SQL Server 2017 (14.x) и более поздних версий Управляемый экземпляр SQL Azure

Во второй части этой серии руководств вы изучите образец данных и создадите несколько графиков. Далее вы узнаете, как сериализовать графические объекты на Python, а затем десериализовать эти объекты и создать графики.

Работая с этой статьей, вы узнаете о следующем.

  • Изучение образца данных
  • Создание диаграмм с помощью языка Python в T-SQL

В первой части были установлены необходимые компоненты и восстановлена демонстрационная база данных.

В третьей части вы узнаете, как создавать функции из необработанных данных с помощью функции Transact-SQL. Затем вы вызовите эту функцию из хранимой процедуры, чтобы создать таблицу, содержащую значения характеристик.

В четвертой части вы научитесь загружать модули и вызывать необходимые функции для создания и обучения модели с помощью хранимой процедуры SQL Server.

Из пятой части вы узнаете, как ввести в эксплуатацию модели, которые были обучены и сохранены в соответствии с инструкциями в четвертой части.

Изучение данных

Во-первых, уделите несколько минут для просмотра схемы данных, так как мы внесли некоторые изменения, чтобы упростить использование данных в базе данных работы такси в Нью-Йорке

  • В исходном наборе данных для идентификаторов такси и записей о поездках использовались отдельные файлы. Мы присоединили два исходных набора данных к столбцам medallion, hack_license и pickup_datetime.
  • Исходный набор данных имел много файлов и был довольно большим. Мы сократили выборку, чтобы получить 1% от первоначального количества записей. Текущая таблица данных содержит 1 703 957 строк и 23 столбца.

Идентификаторы такси

В столбце medallion представлены уникальные идентификационные номера такси.

В столбце hack_license содержатся номера лицензий таксистов (без указания имен).

Записи о поездках и оплате

Каждая запись о поездке включает сведения о местах посадки и высадки, а также о расстоянии поездки.

Каждая запись об оплате включает такие сведения, как тип оплаты, общий размер платежа и размер чаевых.

Последние три столбца можно использовать для различных задач машинного обучения. Столбец tip_amount содержит непрерывный ряд числовых значений и может использоваться в качестве столбца меток для регрессионного анализа. Столбец tipped содержит значения "Да" или "Нет" и используется для двоичной классификации. Столбец tip_class содержит несколько меток классов и поэтому может использоваться в качестве метки для задач многоклассовой классификации.

Все значения, используемые для столбцов меток, основаны на столбце tip_amount с применением следующих бизнес-правил:

  • Столбец меток tipped может принимать значения 0 или 1

    Если tip_amount> 0, tipped = 1; в противном случае tipped = 0

  • Столбец меток tip_class может принимать значения от 0 до 4

    Класс 0: tip_amount = $0

    Класс 1: tip_amount> $0 и tip_amount<= $5

    Класс 2: tip_amount> $5 и tip_amount<= $10

    Класс 3: tip_amount> $10 и tip_amount<= $20

    Класс 4: tip_amount> $20

Создание диаграмм с помощью языка Python в T-SQL

Разработка решения для обработки и анализа данных обычно связана с большим числом операций по анализу и визуализации данных. Так как визуализация является эффективным средством для представления распределения данных и выбросов, в Python имеется много пакетов для визуализации данных. Модуль matplotlib является одной из наиболее популярных библиотек для визуализации и включает множество функций для создания гистограмм, точечных диаграмм, блочных диаграмм и других графических средств просмотра данных.

В этом разделе вы научитесь работать с графиками с использованием хранимых процедур. Вместо того чтобы открывать изображение на сервере, вы сохраняете объект Python plot как данные типа varbinary, а затем записываете их в файл, которым можно поделиться и просматривать в любом другом месте.

Создание графика в виде данных типа varbinary

Хранимая процедура возвращает сериализованный объект Python figure в виде потока данных varbinary. Двоичные данные нельзя просматривать напрямую, но можно использовать код Python на клиенте для десериализации и просмотра рисунков, а затем сохранения в файл изображения на клиентском компьютере.

  1. Создайте хранимую процедуру PyPlotMatplotlib.

    В приведенном ниже сценарии:

    • Переменная @query определяет текст запроса SELECT tipped FROM nyctaxi_sample, который передается в блок кода Python в качестве аргумента входной переменной @input_data_1.
    • Сценарий Python довольно прост: объекты matplotlib figure используются для создания гистограммы и точечной диаграммы, и затем эти объекты сериализуются с помощью библиотеки pickle.
    • Графический объект Python сериализуется в тип данных DataFrame библиотеки pandas для вывода.
    DROP PROCEDURE IF EXISTS PyPlotMatplotlib;
    GO
    
    CREATE PROCEDURE [dbo].[PyPlotMatplotlib]
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @query nvarchar(max) =
        N'SELECT cast(tipped as int) as tipped, tip_amount, fare_amount FROM [dbo].[nyctaxi_sample]'
        EXECUTE sp_execute_external_script
        @language = N'Python',
        @script = N'
    import matplotlib
    matplotlib.use("Agg")
    import matplotlib.pyplot as plt
    import pandas as pd
    import pickle
    
    fig_handle = plt.figure()
    plt.hist(InputDataSet.tipped)
    plt.xlabel("Tipped")
    plt.ylabel("Counts")
    plt.title("Histogram, Tipped")
    plot0 = pd.DataFrame(data =[pickle.dumps(fig_handle)], columns =["plot"])
    plt.clf()
    
    plt.hist(InputDataSet.tip_amount)
    plt.xlabel("Tip amount ($)")
    plt.ylabel("Counts")
    plt.title("Histogram, Tip amount")
    plot1 = pd.DataFrame(data =[pickle.dumps(fig_handle)], columns =["plot"])
    plt.clf()
    
    plt.hist(InputDataSet.fare_amount)
    plt.xlabel("Fare amount ($)")
    plt.ylabel("Counts")
    plt.title("Histogram, Fare amount")
    plot2 = pd.DataFrame(data =[pickle.dumps(fig_handle)], columns =["plot"])
    plt.clf()
    
    plt.scatter( InputDataSet.fare_amount, InputDataSet.tip_amount)
    plt.xlabel("Fare Amount ($)")
    plt.ylabel("Tip Amount ($)")
    plt.title("Tip amount by Fare amount")
    plot3 = pd.DataFrame(data =[pickle.dumps(fig_handle)], columns =["plot"])
    plt.clf()
    
    OutputDataSet = plot0.append(plot1, ignore_index=True).append(plot2, ignore_index=True).append(plot3, ignore_index=True)
    ',
    @input_data_1 = @query
    WITH RESULT SETS ((plot varbinary(max)))
    END
    GO
    
  2. Теперь запустите хранимую процедуру без аргументов, чтобы создать график на основе данных, жестко запрограммированных в качестве входного запроса.

    EXEC [dbo].[PyPlotMatplotlib]
    
  3. Результаты должны выглядеть примерно так:

    plot
    0xFFD8FFE000104A4649...
     0xFFD8FFE000104A4649...
     0xFFD8FFE000104A4649...
     0xFFD8FFE000104A4649...
    
  4. Из клиента Python теперь можно подключаться к экземпляру SQL Server, который создал двоичные объекты графиков, и просматривать их.

    Для этого выполните следующий код Python, заменив имя сервера, имя базы данных и учетные данные соответствующим образом (для проверки подлинности Windows замените параметры UID и PWD на Trusted_Connection=True). Убедитесь, что версия Python одинакова на клиенте и на сервере. Также убедитесь, что библиотеки Python на вашем клиенте (например, matplotlib) имеют ту же или более позднюю версию по сравнению с версиями библиотек, установленных на сервере. Чтобы просмотреть список установленных пакетов и их версий, изучите статью Получение сведений о пакете Python.

    %matplotlib notebook
    import pyodbc
    import pickle
    import os
    cnxn = pyodbc.connect('DRIVER=SQL Server;SERVER={SERVER_NAME};DATABASE={DB_NAME};UID={USER_NAME};PWD={PASSWORD}')
    cursor = cnxn.cursor()
    cursor.execute("EXECUTE [dbo].[PyPlotMatplotlib]")
    tables = cursor.fetchall()
    for i in range(0, len(tables)):
        fig = pickle.loads(tables[i][0])
        fig.savefig(str(i)+'.png')
    print("The plots are saved in directory: ",os.getcwd())
    
  5. Если подключение установлено успешно, должно отобразиться сообщение следующего вида:

    Графики сохранены в каталоге: XXXX

  6. Выходной файл создается в рабочем каталоге Python. Чтобы просмотреть график, перейдите в рабочий каталог Python и откройте файл. На следующем рисунке показан график, сохраненный на клиентском компьютере.

    Зависимость суммы чаевых от суммы чека

Следующие шаги

Работая с этой статьей, вы выполните следующие задачи:

  • Изучен образец данных
  • Созданы графики с помощью языка Python в T-SQL