Поделиться через

Создание объектов данных SQL Server с помощью функции RxSqlServerData (учебник по SQL Server и RevoScaleR)

  • Статья

Область применения: SQL Server 2016 (13.x) и более поздних версий

Эта часть 2 входит в состав серии учебников по RevoScaleR, посвященной использованию функций RevoScaleR в SQL Server.

В этом учебнике мы продолжим создавать базу данных: добавим таблицы и загрузим данные. Если администратор базы данных создал базу данных и выполнил вход, как описано в первом уроке, вы сможете добавить таблицы с помощью интегрированной среды разработки R, например RStudio, или встроенного средства, такого как Rgui.

Из R подключитесь к SQL Server и используйте функции RevoScaleR для выполнения следующих задач.

  • Создание таблиц для обучающих данных и прогнозов
  • Загрузка таблиц с данными из локального CSV-файла

Демонстрационные данные моделируют данные о мошенничестве с кредитными картами (набор данных ccFraud), которые разделены на обучающие и оценочные наборы данных. Файл данных включен в RevoScaleR.

Для выполнения этих задач используйте интегрированную среду разработки R или Rgui. Не забудьте использовать исполняемые файлы R, найденные в этом расположении: C:\Program Files\Microsoft\R Client\R_SERVER\bin\x64 (либо Rgui.exe, если вы используете это средство, либо интегрированная среда разработки, указывающая на C:\Program Files\Microsoft\R Client\R_SERVER). Наличие клиентской рабочей станции R с этими исполняемыми файлами считается необходимым условием для этого учебника.

Создание таблицы с данными для обучения

  1. Храните строку подключения к базе данных в переменной R. Ниже приведены два примера допустимых строк подключения ODBC для SQL Server: один с использованием имени для входа SQL и второй для встроенной проверки подлинности Windows.

    Измените имя сервера, имя пользователя и пароль на нужные.

    Имя входа SQL

    sqlConnString <- "Driver=SQL Server;Server=<server-name>; Database=RevoDeepDive;Uid=<user_name>;Pwd=<password>"

    Проверка подлинности Windows

    sqlConnString <- "Driver=SQL Server;Server=<server-name>;Database=RevoDeepDive;Trusted_Connection=True"

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

    sqlFraudTable <- "ccFraudSmall"

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

  3. Можно также указать параметр rowsPerRead, который управляет тем, сколько строк данных считывается в каждом пакете.

    sqlRowsPerRead = 5000

    Хотя этот параметр является необязательным, его установка может повысить эффективность вычислений. Большинство расширенных аналитических функций в RevoScaleR и MicrosoftML обрабатывают данные в виде блоков. Параметр rowsPerRead определяет количество строк в каждом блоке.

    Возможно, потребуется поэкспериментировать с этим параметром, чтобы найти правильный баланс. Если значение слишком велико, доступ к данным может оказаться слишком медленным, если недостаточно памяти для обработки данных в блоках такого размера. И наоборот: в некоторых системах, если значение параметра rowsPerRead слишком мало, возможно снижение производительности.

    В качестве начального значения используйте размер пакетной обработки по умолчанию, определенный экземпляром ядра СУБД, для управления количеством строк в каждом блоке (5 000 строк). Сохраните это значение в переменной sqlRowsPerRead.

  4. Определите переменную для нового объекта источника данных и передайте ранее определенные аргументы в конструктор RxSqlServerData. Обратите внимание, что при этом происходит только создание, но не заполнение объекта источника данных. Загрузка данных выполняется отдельно.

    sqlFraudDS <- RxSqlServerData(connectionString = sqlConnString,
   table = sqlFraudTable,
   rowsPerRead = sqlRowsPerRead)

Создание таблицы с данными для оценки

Аналогичным образом вы создадите таблицу, содержащую данные для оценки.

  1. Создайте переменную R sqlScoreTableдля хранения имени таблицы, используемой для оценки.

    sqlScoreTable <- "ccFraudScoreSmall"

  2. Передайте эту переменную в качестве аргумента в функцию RxSqlServerData , чтобы определить еще один объект источника данных sqlScoreDS.

    sqlScoreDS <- RxSqlServerData(connectionString = sqlConnString,
   table = sqlScoreTable, rowsPerRead = sqlRowsPerRead)

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

Примечание.

Функция использует разные аргументы для определения источника данных на основе всей таблицы и для источника данных на основе запроса. Это обусловлено тем, что ядро СУБД SQL Server должно по-разному подготавливать запросы. Далее в этом учебнике вы узнаете, как создать объект источника данных на основе запроса SQL.

Загрузка данных в таблицы SQL с помощью языка R

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

Пакет RevoScaleR содержит функции, относящиеся к типам источников данных. Для текстовых данных используйте RxTextData для создания объекта источника данных. Имеются также дополнительные функции для создания объектов источников данных на основе Hadoop, ODBC и других типов данных.

Примечание.

Для задач, описываемых в этом разделе, требуются разрешения на выполнение DDL в базе данных.

Загрузка данных в таблицу для обучения

  1. Создайте переменную R ccFraudCsvи присвойте переменной путь к CSV-файлу, который содержит образцы данных. Этот набор данных предоставляется в RevoScaleR. "sampleDataDir" — это ключевое слово в функции rxGetOption.

    ccFraudCsv <- file.path(rxGetOption("sampleDataDir"), "ccFraudSmall.csv")

    Обратите внимание на вызов rxGetOption, который является методом GET, связанным с rxOptions в RevoScaleR. Используйте эту служебную программу, чтобы задать и вывести параметры, связанные с локальными и удаленными контекстами вычисления. К этим параметрам относятся общий каталог по умолчанию, число процессоров (ядер), используемых при вычислениях и т. д.

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

  2. Определите переменную для хранения новых данных и используйте функцию RxTextData , чтобы указать источник текстовых данных.

    inTextData <- RxTextData(file = ccFraudCsv,      colClasses = c(
    "custID" = "integer", "gender" = "integer", "state" = "integer",
    "cardholder" = "integer", "balance" = "integer",
    "numTrans" = "integer",
    "numIntlTrans" = "integer", "creditLine" = "integer",
    "fraudRisk" = "integer"))

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

  3. На этом этапе может потребоваться приостановить момент и просмотреть базу данных в SQL Server Management Studio. Обновите список таблиц в базе данных.

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

  4. Вставьте данные, вызвав функцию rxDataStep.

    rxDataStep(inData = inTextData, outFile = sqlFraudDS, overwrite = TRUE)

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

    Всего строк записано: 10000, Общее время: 0,466Строк считано: 10000, Всего строк обработано: 10000, Общее время фрагмента: 0,577 секунды

  5. Обновите список таблиц. Чтобы убедиться, что каждая переменная имеет правильные типы данных и успешно импортирована, вы также можете щелкнуть правой кнопкой мыши таблицу в SQL Server Management Studio и выбрать 1000 строк.

Загрузка данных в таблицу для анализа

  1. Повторите те же действия, чтобы загрузить в базу данных набор данных для оценки.

    Сначала укажите путь к исходному файлу.

    ccScoreCsv <- file.path(rxGetOption("sampleDataDir"), "ccFraudScoreSmall.csv")

  2. Используйте функцию RxTextData , чтобы получить данные и сохранить их в переменной inTextData.

    inTextData <- RxTextData(file = ccScoreCsv,      colClasses = c(
    "custID" = "integer", "gender" = "integer", "state" = "integer",
    "cardholder" = "integer", "balance" = "integer",
    "numTrans" = "integer",
    "numIntlTrans" = "integer", "creditLine" = "integer"))

  3. Вызовите функцию rxDataStep , чтобы перезаписать текущую таблицу, используя новую схему и данные.

    rxDataStep(inData = inTextData, sqlScoreDS, overwrite = TRUE)

    • Аргумент inData определяет используемый источник данных.

    • Аргумент outFile указывает таблицу в SQL Server, в которой требуется сохранить данные.

    • Если таблица уже существует и параметр overwrite не используется, результаты будут вставлены без усечения.

Если соединение установлено успешно, вы увидите сообщение, информирующее о завершении и указывающее время, необходимое для записи данных в таблицу:

Всего строк записано: 10000, Общее время: 0,384Строк считано: 10000, Всего строк обработано: 10000, Общее время фрагмента: 0,456 секунды

Дополнительные сведения о функции rxDataStep

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

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

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

Запрос и изменение данных SQL Server