Настройка клиента обработки и анализа данных для разработки на R в SQL Server

Область применения: SQL Server 2016 (13.x), SQL Server 2017 (14.x) и SQL Server 2019 (15.x), SQL Server 2019 (15.x) — Linux

Интеграция R доступна в SQL Server 2016 и более поздних версиях, если включить параметр для языка R во время установки служб SQL Server 2016 R Services и служб машинного обучения SQL Server (в базе данных).

Примечание

В настоящее время эта статья относится только к SQL Server 2016 (13.x), SQL Server 2017 (14.x), SQL Server 2019 (15.x) и SQL Server 2019 (15.x) и только для Linux.

Чтобы разрабатывать и развертывать решения R для SQL Server, установите Microsoft R Client на рабочей станции разработки для получения библиотеки RevoScaleR и других библиотек R. Библиотека RevoScaleR, которая также требуется на удаленном экземпляре SQL Server, координирует вычислительные запросы между обеими системами.

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

Чтобы проверить установку, можно использовать встроенное средство RGUI, как описано в этой статье, либо связать библиотеки с RStudio или любой другой интегрированной средой разработки, которой вы обычно пользуетесь.

Часто используемые инструменты

Независимо от того, являетесь ли вы разработчиком на R, который мало знаком с SQL, или разработчиком на SQL, который мало знаком с R и анализом в базе данных, для использования всех возможностей анализа в базе данных вам потребуется как средство разработки R, так и редактор запросов T-SQL, например SQL Server Management Studio (SSMS).

Для простых сценариев разработки R можно использовать исполняемый файл RGUI, входящий в базовый дистрибутив R в MRO и SQL Server. В этой статье приводятся инструкции по использованию RGUI как для локальных, так и для удаленных сеансов R. Для повышения производительности следует использовать полнофункциональную интегрированную среду разработки, например RStudio или Visual Studio.

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

1. Установка пакетов R

Пакеты R Microsoft доступны в нескольких продуктах и службах. На локальной рабочей станции рекомендуется установить Microsoft R Client. R Client предоставляет RevoScaleR, MicrosoftML, SQLRUtils и другие пакеты R.

1. Скачайте Microsoft R Client.

2. В мастере установки примите или измените путь установки по умолчанию, примите или измените список компонентов и примите условия лицензии Microsoft R Client.

   После завершения установки появится экран приветствия с описанием продукта и списком документации.

3. Создайте системную переменную среды MKL_CBWR для получения согласованных выходных данных из вычислений Intel Math Kernel Library (MKL).

   • На панели управления выберите Система и безопасность>Система>Расширенные параметры системы>Переменные среды.
   • Создайте системную переменную с именем MKL_CBWR и значением AUTO.

2. Обнаружение исполняемых файлов

Найдите и выведите список файлов в папке установки, чтобы убедиться, что установлены R.exe, RGUI и другие пакеты.

1. В проводнике откройте папку %ProgramFiles%\Microsoft\R Client\R_SERVER\bin, чтобы проверить расположение R.exe.

2. Откройте вложенную папку x64, чтобы проверить расположение RGUI. Это средство будет использоваться в следующем шаге.

3. Откройте папку %ProgramFiles%\Microsoft\R Client\R_SERVER\library, чтобы просмотреть список пакетов, установленных с помощью R Client, включая RevoScaleR, MicrosoftML и другие.

3. Запуск RGUI

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

1. Откройте %ProgramFiles%\Microsoft\R Client\R_SERVER\bin\x64 и дважды щелкните RGui, чтобы запустить сеанс R через командную строку R.

   При запуске сеанса R из папки программ Майкрософт автоматически загрузятся несколько пакетов, включая RevoScaleR.

2. В командной строке введите print(Revo.version), чтобы получить сведения о версии пакета RevoScaleR. Пакет RevoScaleR должен иметь версию 9.2.1 или 9.3.0.

3. В командной строке R введите search(), чтобы получить список установленных пакетов.

   

4. Получение разрешений SQL

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

Чтобы подключиться к экземпляру SQL Server для выполнения сценариев и передачи данных, необходимо иметь допустимое имя входа на сервере базы данных. Можно использовать либо имя входа SQL, либо встроенную проверку подлинности Windows. Обычно рекомендуется использовать встроенную проверку подлинности Windows, но в некоторых случаях проще использовать имя входа SQL, особенно если сценарий содержит строки подключения к внешним данным.

У учетной записи, используемой для выполнения кода, должно быть разрешение на чтение для баз данных, с которыми вы работаете, а также специальное разрешение EXECUTE ANY EXTERNAL SCRIPT. Большинству разработчиков также требуются разрешения на создание хранимых процедур и на запись данных в таблицы, содержащие данные обучения или данные оценки.

Попросите администратора базы данных настроить следующие разрешения для учетной записи в базе данных, в которой используется R:

• EXECUTE ANY EXTERNAL SCRIPT для запуска скрипта R на сервере.
• Привилегии db_datareader для выполнения запросов, используемых для обучения модели.
• db_datawriter для записи данных обучения и данных оценки.
• db_owner для создания таких объектов, как хранимые процедуры, таблицы и функции. Разрешение db_owner также потребуется для создания примеров баз данных и тестовых баз данных.

Если для кода требуются пакеты, которые по умолчанию не установлены в SQL Server, обратитесь к администратору базы данных, чтобы установить необходимые пакеты. SQL Server представляет собой защищенную среду, и существуют ограничения на места установки пакетов. Дополнительные сведения см. в статье Installing New R Packages on SQL Server(Установка новых пакетов R в SQL Server).

5. Проверка подключения

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

При выполнений следующих действий используется демонстрационная база данных NYCTaxi_Sample и проверка подлинности Windows.

1. Откройте RGUI на клиентской рабочей станции. Например, перейдите к папке ~\Program Files\Microsoft SQL Server\140\R_SERVER\bin\x64 и дважды щелкните файл RGui.exe, чтобы запустить его.

2. RevoScaleR загрузится автоматически. Проверьте работоспособность RevoScaleR, выполнив следующую команду: print(Revo.version).

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

   # Define a connection. Replace server with a valid server name.
   connStr <- "Driver=SQL Server;Server=<your-server-name>;Database=NYCTaxi_Sample;Trusted_Connection=true"
   
   # Specify the input data in a SQL query.
   sampleQuery <-"SELECT DISTINCT TOP(100) tip_amount FROM [dbo].nyctaxi_sample ORDER BY tip_amount DESC;"
   
   # Define a remote compute context based on the remote server.
   cc <-RxInSqlServer(connectionString=connStr)
   
   # Execute the function using the remote compute context.
   rxSummary(formula = ~ ., data = RxSqlServerData(sqlQuery=sampleQuery, connectionString=connStr), computeContext=cc)
   

   Результаты:

   Этот скрипт подключается к базе данных на удаленном сервере, предоставляет запрос, создает инструкцию cc контекста вычисления для удаленного выполнения кода, а затем предоставляет функцию RevoScaleR rxSummary для возврата статистической сводки результатов запроса.

     Call:
   rxSummary(formula = ~., data = RxSqlServerData(sqlQuery = sampleQuery, 
       connectionString = connStr), computeContext = cc)
   
   Summary Statistics Results for: ~.
   Data: RxSqlServerData(sqlQuery = sampleQuery, connectionString = connStr) (RxSqlServerData Data Source)
   Number of valid observations: 100 
   
   Name       Mean   StdDev   Min Max ValidObs MissingObs
   tip_amount 63.245 31.61087 36  180 100      0     
   

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

   # Return the current compute context.
   rxGetComputeContext()
   
   # Revert to a local compute context.
   rxSetComputeContext("local")
   rxGetComputeContext()
   
   # Switch back to remote.
   connStr <- "Driver=SQL Server;Server=<your-server-name>;Database=NYCTaxi_Sample;Trusted_Connection=true"
   cc <-RxInSqlServer(connectionString=connStr)
   rxSetComputeContext(cc)
   rxGetComputeContext()
   

5. Получите сведения о переменных в источнике данных, включая имя и тип.

   rxGetVarInfo(data = inDataSource)
   

   Результаты содержат 23 переменные.

6. Создайте точечную диаграмму, чтобы определить наличие зависимостей между двумя переменными.

   # Set the connection string. Substitute a valid server name for the placeholder.
   connStr <- "Driver=SQL Server;Server=<your database name>;Database=NYCTaxi_Sample;Trusted_Connection=true"
   
   # Specify a query on the nyctaxi_sample table.
   # For variables on each axis, remove nulls. Use a WHERE clause and <> to do this.
   sampleQuery <-"SELECT DISTINCT TOP 100 * from [dbo].[nyctaxi_sample] WHERE fare_amount <> '' AND  tip_amount <> ''"
   cc <-RxInSqlServer(connectionString=connStr)
   
   # Generate a scatter plot.
   rxLinePlot(fare_amount ~ tip_amount, data = RxSqlServerData(sqlQuery=sampleQuery, connectionString=connStr, computeContext=cc), type="p")
   

   На следующем снимке экрана показаны входные данные и выходные данные в виде точечной диаграммы.

   

6. Связывание инструментов с R.exe

Для поддержки долговременных и серьезных проектов разработки следует установить интегрированную среду разработки (IDE). Средства SQL Server и встроенные инструменты R не предназначены для интенсивной разработки на R. Существующий рабочий код можно развернуть как хранимую процедуру для выполнения на SQL Server.

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

RStudio

В RStudio можно настроить среду для использования библиотек и исполняемых файлов R, которые соответствуют своим эквивалентам на удаленном экземпляре SQL Server.

1. Проверьте версии пакетов R, установленные на SQL Server. Дополнительные сведения см. в статье Получение сведений о пакете R.

2. Установите Microsoft R Client, чтобы добавить RevoScaleR и другие пакеты R, включая базовый дистрибутив R, используемый экземпляром SQL Server. Выберите версию на том же уровне или ниже (пакеты поддерживают обратную совместимость), которая предоставляет те же версии пакетов, что и на сервере. Чтобы просмотреть версии пакетов, установленных на сервере, обратитесь к разделу Просмотр всех установленных пакетов R.

3. В RStudio обновите путь R так, чтобы он указывал на среду R, предоставляющую RevoScaleR, Microsoft R Open и другие пакеты Майкрософт. Поищите %ProgramFiles%\Microsoft\R Client\R_SERVER\bin\x64.

4. Закройте, а затем откройте RStudio.

При повторном открытии RStudio исполняемый файл R из R Client станет подсистемой R по умолчанию.

Инструменты R для Visual Studio (RTVS)

Если у вас еще нет предпочтительной интегрированной среды разработки для R, рекомендуется воспользоваться инструментами R для Visual Studio.

• Скачивание инструментов R для Visual Studio (RTVS)
• Инструкции по установке — инструменты RTVS доступны в нескольких версиях Visual Studio.
• Начало работы с Инструментами R для Visual Studio

Подключение к SQL Server из RTVS

В этом примере используется Visual Studio 2017 Community Edition с установленной рабочей нагрузкой обработки и анализа данных.

1. В меню Файл выберите пункт Создать, а затем — Проект.

2. На левой панели находится список предварительно установленных шаблонов. Выберите R, а затем Проект R. В поле Имя введите dbtest и нажмите ОК.

   Visual Studio создает папку проекта и файл скрипта по умолчанию Script.R.

3. В первой строке файла скрипта введите .libPaths(), а затем нажмите клавиши CTRL+ВВОД.

   В интерактивном окне R должен появиться текущий путь к библиотеке R.

4. Выберите меню Инструменты R и выберите Windows, чтобы просмотреть список других связанных с R окон, которые можно открыть в рабочей области.

   • Просмотрите справку по пакетам в текущей библиотеке, нажав клавиши CTRL+3.
   • Просмотрите переменные R в обозревателе переменных, нажав клавиши CTRL+8.

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

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

• Руководство по использованию функций RevoScaleR R с данными SQL Server
• Пошаговое руководство по обработке и анализу данных