Краткое руководство. Использование структур данных, типов данных и объектов при работе с R и машинным обучением SQL

Применимо к: SQL Server 2016 (13.x) и более поздних Управляемый экземпляр SQL Azure

В этом кратком руководстве вы узнаете, как использовать структуры и типы данных при применении R в Службах машинного обучения SQL Server или Кластерах больших данных. Вы узнаете о том, как перемещать данные между R и SQL Server, и о типичных проблемах, возникающих при этом процессе.

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

В этом кратком руководстве вы узнаете, как использовать структуры и типы данных при работе с R в службах SQL Server R Services. Вы узнаете о том, как перемещать данные между R и SQL Server, и о типичных проблемах, возникающих при этом процессе.

В этом кратком руководстве вы узнаете, как использовать структуры и типы данных при работе с R в Службах машинного обучения управляемого экземпляра SQL Azure. Вы узнаете о том, как перемещать данные между R и Управляемым экземпляром SQL, и о типичных проблемах, возникающих при этом процессе.

Основные проблемы, о которых следует знать в самом начале, таковы:

  • Иногда типы данных не совпадают
  • Могут иметь место неявные преобразования
  • Иногда требуются операции приведения и преобразования.
  • R и SQL используют разные объекты данных.

Предварительные требования

Для работы с этим кратким руководством необходимо следующее.

  • Инструмент для выполнения SQL-запросов, содержащих сценарии R. В этом кратком руководстве используется Azure Data Studio.

Всегда возвращайте кадр данных

Когда скрипт возвращает результаты из R в SQL Server, он должен возвращать данные в виде data.frame. Любой другой тип объекта, создаваемого в сценарии (список, коэффициент, вектор или двоичные данные) нужно преобразовать в кадр данных, если необходимо вывести его в результатах хранимой процедуры. К счастью существует несколько функций R, поддерживающих изменение других объектов на кадр данных. Вы даже можете сериализовать двоичную модель и возвратить ее в кадре данных. Мы сделаем это позже в этом кратком руководстве.

Давайте сначала поэкспериментируем с некоторыми базовыми объектами R — векторами, матрицами и списками, и посмотрим, как преобразование в кадр данных изменяет выходные данные, передаваемые в SQL Server.

Сравните эти два сценария "Hello World" на языке R. Сценарии выглядят почти одинаково, однако первый сценарий возвращает один столбец с тремя значениями, а второй — три столбца, каждый из которых содержит по одному значению.

Пример 1

EXECUTE sp_execute_external_script
       @language = N'R'
     , @script = N' mytextvariable <- c("hello", " ", "world");
       OutputDataSet <- as.data.frame(mytextvariable);'
     , @input_data_1 = N' ';

Пример 2

EXECUTE sp_execute_external_script
        @language = N'R'
      , @script = N' OutputDataSet<- data.frame(c("hello"), " ", c("world"));'
      , @input_data_1 = N'  ';

Определение схемы и типов данных

Почему результаты настолько отличаются?

Обычно ответ можно найти, использовав команду R str(). Добавьте функцию str(object_name) в скрипте R для получения сведений о схеме данных возвращаемого объекта R в виде информационного сообщения.

Чтобы понять, почему результаты примера 1 и 2 так сильно отличаются, вставьте строку str(OutputDataSet) в конце определения переменной @script в каждой инструкции следующим образом:

Пример 1 с добавленной функцией str

EXECUTE sp_execute_external_script
        @language = N'R'
      , @script = N' mytextvariable <- c("hello", " ", "world");
      OutputDataSet <- as.data.frame(mytextvariable);
      str(OutputDataSet);'
      , @input_data_1 = N'  '
;

Пример 2 с добавленной функцией str

EXECUTE sp_execute_external_script
  @language = N'R', 
  @script = N' OutputDataSet <- data.frame(c("hello"), " ", c("world"));
    str(OutputDataSet);' , 
  @input_data_1 = N'  ';

Теперь просмотрите текст на вкладке Сообщения, в котором можно определить причину такой разницы.

Результаты примера 1

STDOUT message(s) from external script:
'data.frame':	3 obs. of  1 variable:
$ mytextvariable: Factor w/ 3 levels " ","hello","world": 2 1 3

Результаты примера 2

STDOUT message(s) from external script:
'data.frame':	1 obs. of  3 variables:
$ c..hello..: Factor w/ 1 level "hello": 1
$ X...      : Factor w/ 1 level " ": 1
$ c..world..: Factor w/ 1 level "world": 1

Как видно, небольшие изменения в синтаксисе R сильно повлияли на схему результатов. Мы не будем объяснять причину. Подробные сведения о различиях в типах данных R представлены в статье Структуры данных в учебнике "Advanced R" Хэдли Викхема (Hadley Wickham).

Сейчас помните, что необходимо проверить ожидаемые результаты при приведении объектов R в кадры данных.

Совет

Также можно использовать функции идентификации R, такие как is.matrix и is.vector, чтобы получить сведения о внутренней структуре данных.

Неявное преобразование объектов данных

Каждый объект данных R имеет собственные правила обработки значений в сочетании с другими объектами данных, если объекты данных имеют одинаковое количество измерений или если любой объект данных содержит разнородные типы данных.

Сначала создайте небольшую таблицу тестовых данных.

CREATE TABLE RTestData (col1 INT NOT NULL)

INSERT INTO RTestData
VALUES (1);

INSERT INTO RTestData
VALUES (10);

INSERT INTO RTestData
VALUES (100);
GO

Например, предположим, что вы выполняете следующую инструкцию для умножения матриц с помощью R. Вы умножаете матрицу с одним столбцом и тремя значения на массив с четырьмя значениями и в результате ожидаете получить матрицу 4x3.

EXECUTE sp_execute_external_script
    @language = N'R'
    , @script = N'
        x <- as.matrix(InputDataSet);
        y <- array(12:15);
    OutputDataSet <- as.data.frame(x %*% y);'
    , @input_data_1 = N' SELECT [Col1]  from RTestData;'
    WITH RESULT SETS (([Col1] int, [Col2] int, [Col3] int, Col4 int));

Один столбец с тремя значениями преобразуется в матрицу с одним столбцом. Так как матрица представляет собой особый вид массива в R, массив y неявно приводится к матрице с одним столбцом, чтобы обеспечить соответствие двух аргументов.

Результаты

Col1 Col2 Col3 Col4
12 13 14 15
120 130 140 150
1200 1300 1400 1500

Однако обратите внимание, что происходит при изменении размера массива y.

execute sp_execute_external_script
   @language = N'R'
   , @script = N'
        x <- as.matrix(InputDataSet);
        y <- array(12:14);
   OutputDataSet <- as.data.frame(y %*% x);'
   , @input_data_1 = N' SELECT [Col1]  from RTestData;'
   WITH RESULT SETS (([Col1] int ));

На этот раз код R вернет одно значение в качестве результата.

Результаты

Col1
1542

Почему? Так как в этом случае два аргумента могут обрабатываться как векторы одинаковой длины, код R возвращает скалярное произведение в виде матрицы. Это ожидаемое поведение в соответствии с правилами линейной алгебры. Тем не менее могут возникнуть проблемы, если нисходящее приложение ожидает, что выходная схема вывода не должна изменяться.

Совет

Получили сообщения об ошибках? Убедитесь, что хранимая процедура выполняется в контексте базы данных, содержащей таблицу, а не в контексте базы данных master или другой базы данных.

Кроме того, мы не рекомендуем использовать временные таблицы для этих примеров. Некоторые клиенты R будут завершать подключение между обработкой пакетов, удаляя временные таблицы.

Слияние или перемножение столбцов разной длины

R обеспечивает большую гибкость для работы с векторами различных размеров и для объединения структур в виде столбцов в кадры данных. Списки векторов могут выглядеть как таблица, но они не подчиняются всем правилам, управляющим таблицами базы данных.

Например, следующий скрипт определяет числовой массив, длина которого равна 6, и сохраняет его в переменной R df1. Затем числовой массив объединяется с целыми числами из таблицы RTestData, которая содержит 3 (три) значения, для формирования нового кадра данных — df2.

EXECUTE sp_execute_external_script
    @language = N'R'
    , @script = N'
               df1 <- as.data.frame( array(1:6) );
               df2 <- as.data.frame( c( InputDataSet , df1 ));
               OutputDataSet <- df2'
    , @input_data_1 = N' SELECT [Col1]  from RTestData;'
    WITH RESULT SETS (( [Col2] int not null, [Col3] int not null ));

Чтобы заполнить кадр данных, R повторно использует элементы, полученные из RTestData, столько раз, сколько требуется в соответствии с числом элементов в массиве df1.

Результаты

Col2 Col3
1 1
10 2
100 3
1 4
10 5
100 6

Помните, что кадр данных только выглядит как таблица и на самом деле является списком векторов.

Приведение или преобразование данных

R и SQL Server используют разные типы данных, поэтому при выполнении запроса в SQL Server для получения данных и их передаче в среду выполнения R обычно выполняется неявное преобразование. Кроме того, преобразования выполняются при возвращении данных из R в SQL Server.

  • SQL Server передает данные из запроса в процесс R, которым управляет служба панели запуска, и преобразует их во внутреннее представление для повышения эффективности.
  • Среда выполнения R загружает данные в переменную data.frame и выполняет собственные операции с данными.
  • Ядро СУБД возвращает данные в SQL Server по защищенному внутреннему соединению и представляет данные в контексте типов данных SQL Server.
  • Вы можете получить данные путем подключения к SQL Server с помощью клиентской или сетевой библиотеки, которая может выполнять запросы SQL и обрабатывать табличные наборы данных. Это клиентское приложение может и по-другому влиять на данные.

Чтобы увидеть, как это работает, выполните подобный запрос в хранилище данных AdventureWorksDW. Это представление возвращает данные продаж, используемые для создания прогнозов.

USE AdventureWorksDW
GO

SELECT ReportingDate
         , CAST(ModelRegion as varchar(50)) as ProductSeries
         , Amount
           FROM [AdventureWorksDW].[dbo].[vTimeSeries]
           WHERE [ModelRegion] = 'M200 Europe'
           ORDER BY ReportingDate ASC

Примечание

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

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

EXECUTE sp_execute_external_script
       @language = N'R'
      , @script = N' str(InputDataSet);
      OutputDataSet <- InputDataSet;'
      , @input_data_1 = N'
           SELECT ReportingDate
         , CAST(ModelRegion as varchar(50)) as ProductSeries
         , Amount
           FROM [AdventureWorksDW].[dbo].[vTimeSeries]
           WHERE [ModelRegion] = ''M200 Europe''
           ORDER BY ReportingDate ASC ;'
WITH RESULT SETS undefined;

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

После того, как запрос начнет действовать, просмотрите результаты выполнения функции str, чтобы увидеть, как R обрабатывает входные данные.

Результаты

STDOUT message(s) from external script: 'data.frame':    37 obs. of  3 variables:
STDOUT message(s) from external script: $ ReportingDate: POSIXct, format: "2010-12-24 23:00:00" "2010-12-24 23:00:00"
STDOUT message(s) from external script: $ ProductSeries: Factor w/ 1 levels "M200 Europe",..: 1 1 1 1 1 1 1 1 1 1
STDOUT message(s) from external script: $ Amount       : num  3400 16925 20350 16950 16950
  • Столбец datetime был обработан с использованием типа данных R POSIXct.
  • Текстовый столбец "ProductSeries" определен в качестве коэффициента, то есть категориальной переменной. Строковые значения обрабатываются как коэффициенты по умолчанию. Если передать строку в R, она преобразуется в целое число для внутреннего использования и сопоставляется со строками на выходе.

Сводка

Даже в этих коротких примерах можно увидеть, как следует проверять последствия преобразования данных при передаче запросов SQL в качестве входных данных. Так как некоторые типы данных SQL Server не поддерживаются в R, следуйте приведенным ниже рекомендациям, чтобы избежать ошибок:

  • Проверяйте данные заранее, а также проверяйте столбцы и значения в схеме, которые могут вызвать проблему при передаче в код R.
  • Указывайте столбцы из источника входных данных по отдельности. Не используйте SELECT *. Определите способ обработки каждого столбца.
  • Во избежание непредвиденных ситуаций во время подготовки входных данных при необходимости выполняйте явные приведения.
  • Избегайте передачи столбцов данных (например, идентификаторов GUID или идентификаторов GUID строк). Они вызывают ошибки и не требуются для моделирования.

Дополнительные сведения о поддерживаемых и неподдерживаемых типах данных см. в статье Библиотеки и типы данных R.

Дальнейшие действия

Дополнительные сведения о написании расширенных функций R с использованием машинного обучения SQL см. в этом кратком руководстве: