Анализ данных на языке sql уровень 2. Анализ данных с использованием R и SQL. Что дает использование реляционной базы данных с языком R

Быстродействующее сочетание: DB2 10.5 with BLU Acceleration и R

Грант Хатчисон (Grant Hutchison)
Опубликовано 11.06.2014

R — это язык программирования с открытым исходным кодом, который отлично подходит для анализа данных и для работы с графикой. Изначально R был разработан сотрудниками Оклендского университета Россом Айхэкой (Ross Ihaka) и Робертом Джентлменом (Robert Gentleman). Айхэка и Джентлмен хотели создать язык, который помог бы им в процессе преподавания студентам вводного курса по статистике. Язык R был спроектирован под влиянием языка S, который был создан компанией Bell Labs в 1970-е годы. R считается предметно-ориентированным языком, поскольку он был разработан преимущественно для анализа данных — и справляется с этой задачей очень хорошо. R можно использовать как в виде интерактивной среды, так и в виде скриптов и моделей, встраиваемых в пакеты и интегрируемых с другими программными модулями.

Язык R можно использовать для анализа данных из множества различных источников данных, включая внешние файлы или базы данных. В данной статье объясняется, как подключаться к данным, как запрашивать данные и как обновлять данные, которые находятся на серверах реляционных баз данных. Вы также узнаете, почему технология DB2 v10.5 with BLU Acceleration — это отличный выбор для аналитики. И, наконец, в статье объясняется, каким образом среда BLU Acceleration for Cloud упрощает загрузку и анализ наборов данных при использовании языка R.

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

• CRAN: Comprehensive R Archive Network
• CSV: Comma-separated values
• DSN: Data source name
• ODBC: Open Database Connectivity
• RODBC: Open Database Connectivity for R
• SIMD: Single instruction multiple data
• SQL: Structured Query Language

Внешние данные

В языке R создание объектов и управление ими осуществляются в рамках одной области памяти. В большинстве случаев для задач анализа данных требуется, чтобы данные были доступны в виде кадра данных (data frame). Кадр данных можно рассматривать как двумерный массив гетерогенных данных или как таблицу в оперативной памяти (in-memory table). Если данные уже существуют в виде текстового файла с разделителями, то пользователь среды R может загрузить данные в память для анализа с помощью одной из множества функций вида read.xxx() , например, функции read.csv() для CSV-файлов. Точно так же, если кадр данных R необходимо "экстернализировать" в виде файла, можно воспользоваться множеством функций вида write.xxx() .

Язык R позволяет сохранять кадры данных на диске в виде объектов (т.е. за пределами своей памяти) с помощью встроенных функций saveRDS() , save() и save.image() . Затем эти объекты можно считывать обратно в память для дальнейшего анализа. Тем не менее такой механизм персистентности имеет большое количество недостатков, в том числе ограниченные размеры пространства для кадров данных. Кроме того, существуют проблемы, связанные с использованием текстовых файлов для анализа данных; они требует значительных усилий для очистки данных и при передаче наборов данных другим пользователям.

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

Что дает использование реляционной базы данных с языком R?

R можно использовать для исследования данных и для создания прогнозирующих моделей. Серверы реляционных баз данных предназначены для обработки больших объемов данных и позволяют обеспечить непротиворечивость данных для одновременно работающих пользователей. Поскольку данные в реляционных базах данных обычно хранятся в нормализованном виде, при выполнении задач разведочного анализа данных (exploratory data analysis, EDA) вам с большой вероятностью придется применить определенные навыки в области SQL для объединения релевантных атрибутов в нескольких таблицах. Если вы работаете совместно с администратором баз данных или с аналитиком данных, хорошо владеющим навыками работы с реляционными БД, вы можете создавать вспомогательные представления в режиме "только для чтения", чтобы ускорить анализ исходных данных. Работая с данными в среде DB2, вы можете воспользоваться инструментом IBM Data Studio или веб-консолью среды IBM BLU Acceleration for Cloud для исследования схемы базы данных или для задания новых представлений с целью упрощения доступа к данным из своих R-скриптов.

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

DB2 with BLU Acceleration

Продукт DB2 10.5 with BLU Acceleration оптимизирован для аналитических задач и поэтому является отличным выбором для анализа данных с помощью R. В пользу решения DB2 with BLU Acceleration можно привести множество аргументов, включая следующие.

• Простота
  • Анализ данных можно выполнять сразу после их загрузки. Функциональность BLU оптимизирована для аналитики, что избавляет вас от необходимости создания индексов или выполнения каких-либо задач по настройке.
• Производительность
  • BLU использует столбцовую (columnar) модель хранения, что сокращает количество чтений с диска и минимизирует объем памяти, используемый для поддержки очень быстрой обработки запросов.
  • Применяемые по умолчанию усовершенствованные методы сжатия данных сокращают использование памяти и дискового пространства.

В отличие от среды R, продукт DB2 with BLU Acceleration исследует все аппаратные возможности своего сервера и оптимизирует использование ресурсов. Например, когда это возможно, DB2 задействует SIMD-команды процессора на нескольких ядрах. Кроме того, DB2 использует различные механизмы кэширования памяти для уменьшения количества чтений данных с диска. Такая оптимизация осуществляется автоматически. В настоящее время эта платформа также доступна в облаке — в виде решения BLU Acceleration for Cloud.

BLU Acceleration for Cloud

BLU Acceleration for Cloud — это основанный на веб-технологиях сервер баз данных, оптимизированный для достижения максимальной простоты. Всего за несколько минут вы можете создать таблицы, загрузить данные и приступить к анализу. Пользователям предоставляются соответствующие инструменты (IBM Data Studio и IBM InfoSphere Data Architect) для упрощения создания и для сопровождения моделей и объектов баз данных (таких как таблицы). После создания схемы вы можете использовать веб-консоль решения BLU Acceleration for Cloud для загрузки своих данных. Предусмотрено множество способов загрузки данных, в том числе локальные файлы, сервисы облачного хранения (например, Amazon S3), IBM InfoSphere DataStage. В веб-консоли можно выполнять анализ с использованием Excel, SQL, Cognos Business Insight (BI), а также скриптов и моделей на R.

На рис. 1 показаны два различных сценария работы пользователя языка R с данными, которые хранятся в среде BLU Acceleration for Cloud. Аналитик данных может использовать свою среду R, например, RStudio, для извлечения данных и их анализа на своем собственном компьютере. В качестве альтернативного варианта аналитик может через браузер создавать и исполнять R-скрипты на облачном сервере.

Рисунок 1. Исследование данных с использованием R (дистанционное)

Диаграмма на рис. 2 представляет пример среды R, интегрированной в среду BLU Acceleration for Cloud. Решение BLU Acceleration for Cloud предоставляет для R среду исполнения и хранилище данных в облачной инфраструктуре. BLU Acceleration for Cloud предоставляет веб-консоль, которая позволяет всего за несколько минут загрузить данные и выполнить анализ. Анализ данных может производиться с использованием средств SQL, BI-инструментов, R-скриптов и R-моделей. В вашем распоряжении имеются как простой редактор R-скриптов, так и возможность запустить среду RStudio непосредственно из своего браузера. На рис. 2 показан пример такой интеграции, в том числе графические результаты анализа данных по переписи населения США. Для создания новых скриптов следует нажать на знак (+), подсвеченный в левом верхнем углу окна на рис. 2. Управление R-скриптами осуществляется в облаке в рамках вашего собственного рабочего пространства. Вы также можете импортировать скрипты, чтобы работать с ними в облаке.

Рисунок 2. Исследование данных в облаке с использованием R

BLU Acceleration for Cloud — план Trial

Лучший способ приобрести опыт использования R в работе с базами данных — воспользоваться пробной версией BLU Acceleration for Cloud Trial Plan в рамках программы открытого бета-тестирования. Для начала перейдите на веб-сайт BLU Acceleration for Cloud . Нажмите на кнопку Try BLU Acceleration Now ; вы будете направлены на страницу Plans and Pricing . План Solo предусматривает инициализацию аналитической среды BLU у выбранного вами поставщика облачных сервисов (IBM SoftLayer или Amazon Web Services), а план Trial мгновенно предоставит вам доступ к собственной среде на 5 часов.

На рис. 3 показан виджет BLU Acceleration for для плана Trial. Нажмите на кнопку start . Начнется инициализация вашей системы; когда ваш сервер будет готов, нажмите на Start BLU Acceleration . Теперь вы можете войти в веб-консоль с идентификатором пользователя bluadmin и предоставленным паролем. Чтобы проверить интеграцию R, перейдите в область Developing R Scripts и создайте тестовый скрипт с использованием кода, показанного ниже в листинге 1. Поскольку вы инициализировали новую среду, вам, вероятно, будет предложено установить R и RStudio (эта процедура занимает не более двух минут). В листинге 1 вы увидите, что в скрипт загружается пакет с именем bluR. Этот пакет (на данный момент он доступен только в рамках BLU Acceleration for Cloud) использует в качестве интерфейса доступа к данным RODBC.

Рисунок 3. BLU Acceleration for Cloud — план Trial

Типы данных и соображения по проектированию

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

R имеет весьма ограниченный набор базовых типов данных: character, numeric (целые числа и числа с плавающей точкой двойной точности), logical, raw (двоичные данные). Символьные векторы в R просто отображаются на SQL-данные типа CHARACTER или VARCHAR . Числовые типы данных сервера баз данных обычно отображаются на типы данных INTEGER и DOUBLE в среде R. Поскольку тип данных logical в R не имеет очевидного эквивалента среди типов данных SQL, этот тип обычно отображается в таблицах базы данных на текстовое поле. При работе с данными временных рядов необходимо тщательно продумывать, как эти данные будут отображаться между R и сервером баз данных.

С точки зрения статистических методов непрерывные и категорийные данные обрабатываются совершенно по-разному. Пользователи R знают об этих различиях и кодируют категорийные данные с помощью такого типа данных, как factor После извлечения данных из базы данных следует учитывать, как нужно обращаться с этими данным — как с данными в непрерывной форме или как с категорийными данными. Если данные действительно являются категорийными, то соответствующую переменную необходимо перекодировать или выполнить приведение с помощью функции factor() .

В таблице 1 приведено сопоставление терминов между R и реляционной СУБД.

Таблица 1. Сравнение терминологии (R и RDBMS)

Варианты подключения

IBM Netezza и R

IBM Netezza Analytics — это встраиваемая специализированная платформа для углубленной аналитики. Аппаратно-программные комплексы (appliance) Netezza поддерживают несколько различных способов использования R, в том числе анализ непосредственно в базе данных и анализ на стороне клиента. с информацией по использованию R на платформе Netezza.

Язык R не имеет встроенных возможностей доступа к реляционным базам данных, однако на сегодняшний день существует большое количество интерфейсов доступа к данным, которые можно получить от поставщиков или на сайте CRAN (Comprehensive R Archive Network).

Далее вы узнаете, как использовать RJDBC или RODBC для обращения из среды R к данным, хранящимся в серверах баз данных DB2. Если вы планируете обращаться к другим серверам баз данных, вы можете воспользоваться этими же интерфейсами или изучить возможности других пакетов для доступа к конкретным базам данных.

Соединение с помощью RJDBC

Пакет RJDBC основан на интерфейсе DBI (database interface), широко применяемом в сообществе R. Пакет DBI содержит виртуальные классы, за реализацию которых отвечает обеспечивающий драйвер. RJDBC использует для обмена данными между R и сервером баз данных сочетание JDBC-совместимого драйвера базы данных и среды JRE (Java Runtime Environment). Мы будем использовать для поддержания обеспечивающего соединения JDBC-драйвер от IBM (тип 4). Этот JDBC-драйвер IBM можно использовать для обращения к базам данных семейства DB2, включая DB2 for Linux, Unix, Windows, DB2 for z/OS, DB2 i. Если в вашей среде R еще не установлен пакет RJDBC, установите его из репозитория CRAN командой install.packages("RJDBC") .

В листинге 1 пакет RJDBC загружает R-объект, представляющий JDBC-драйвер. Маршрут к файлу db2jcct4.jar должен быть описан в вашей переменной CLASSPATH ; а в JAVA_PATH должно быть указано положение действующей JRE. Функция dbConnect() выделяет серверу баз данных объект channel (канал или соединение). Соединение устанавливается с базой данных с именем SAMPLEDB, которая расположена на сервере с именем хоста blueforcloud.imdemocloud.com .Обратите внимание, что для соединения требуется пароль, однако в данном скрипте он не сохранен.

Листинг 1. BLU for Cloud — R-скрипт

library(bluR) # Установить соединение с сервером баз данных BLU (локальное соединение, # поскольку среда R исполняется на том же сервере) samplescon <- bluConnect("SAMPLEDB", "", "") # Создать простой запрос данных в виде строковой переменной query<-paste("select * from DB2INST1.US_FUEL_ECONOMY_AUGUST_2013") # Создать кадр данных R на основе SQL-оператора cars <- bludf(samplescon, query) # Вывести на печать характеристики кадра данных и некоторые данные из первой строки nrow(cars) ncol(cars) print (cars, row.names=FALSE) # Выполнить визуализацию в виде коробчатой диаграммы boxplot(COMB_FE_CONVENTIONAL_FUEL ~ CYL, cars, names = levels(cars$CYL), main="Fuel Consumption - 2013", xlab = "Number of Cylinders", ylab = "Miles/Gallon (mpg)") # Закрыть соединение с сервером BLU bluClose(samplescon)

Потратьте несколько минут на работу с планом Trial для продукта BLU Acceleration for Cloud и со скриптом, показанным в листинге 1. В остальной части этой статьи будет изложена дополнительная информация по обращению к данным из R.

В листинге 2 показано, как с помощью функции dbSendQuery() отправить SQL-запрос серверу через объект соединения. Реальные данные возвращаются клиенту только при исполнении функции fetch() . В этом сценарии возвращаются все строки из результирующего набора, поскольку второму параметру присвоено значение -1.

Листинг 2. RJDBC — извлечение данных

query <- paste("select * from db2inst1.us_fuel_economy_august_2013") # Отправить запрос серверу баз данных rs <- dbSendQuery(conn, query) # Извлечь все строки данных df <- fetch(rs, -1)

В листинге 3 кадр данных df содержит результаты запроса. Назначение этого скрипта состоит в том, чтобы определить типы автомобилей с наилучшей топливной экономичностью и соответствующую модель. Результаты показывают, что согласно данным нашей таблицы по 1165 автомобилям наилучшую комбинированную топливную экономичность имеют автомобили Toyota Prius.

Листинг 3. RJDBC — отключение

# Удалить все результаты с отсутствующими данными df <- na.omit(df) cat ("There are", nrow(df), "fuel economy results available with ") cat (ncol(df), "different variables.\n") # Найти в кадре данных наилучшие показатели по потреблению топлива best_fe <- max(df$COMB_FE_CONVENTIONAL_FUEL,na.rm=TRUE) cat("\nCar(s) with the best fuel consumption of",best_fe,"miles/gallon.\n\n") print (df,row.names=FALSE) # Отключиться от сервера баз данных dbDisconnect(conn) ---- OUTPUT from Script There are 1165 fuel economy results available with 18 different variables. Car(s) with the best fuel consumption of 50 miles/gallon. MODEL_YEAR MFR_NAME DIVISION CARLINE 2013 Toyota TOYOTA PRIUS 2013 Toyota TOYOTA PRIUS c

Если из таблицы базы данных возвращаются значения NULL, они отображаются в кадре данных R как недоступные (NA). Функция na.omit() в первой строке листинга 3 удаляет все наблюдения с отсутствующими значениями. Переменные кадра данных R извлекаются из DB2 в виде строк символов в верхнем регистре. Чтобы высвободить ресурсы базы данных на сервере, не забывайте выполнять функцию dbDisconnect() в конце своих R-скриптов.

Можно также связать объекты R с SQL-операторами с помощью функции dbSendQuery() (листинг 4).

Листинг 4. RJDBC — использование параметров

mfr <- "BMW" query <- paste("select * from db2inst1.us_fuel_economy_august_2013 where mfr_name=?") # Отправить серверу баз данных запрос с подстановкой параметра rs <- dbSendQuery(conn, query, mfr) df.bmw <- fetch(rs, -1) # Преобразовать переменную в тип данных factor df.bmw$CYL <- factor(df.bmw$CYL) # Создать коробчатую диаграмму по числу цилиндров boxplot(COMB_FE_CONVENTIONAL_FUEL ~ CYL, df.bmw, names = levels(df.bmw$CYL), main="Fuel Consumption - 2013", xlab = "Number of Cylinders", ylab = "Miles/Gallon (mpg)")

В листинге 4 значение manufacturer не является частью текста запроса; оно отсылается в составе запроса при исполнении функции dbSendQuery() .

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

Рисунок 4. Пример коробчатой диаграммы

Соединение с помощью RODBC

RODBC – это более эффективный и более быстрый интерфейс доступа к данным DB2 для пользователей R. Пакет RODBC доступен в репозитории CRAN и используется многими участниками сообщества R. Если пакет RODBC еще не установлен в вашей среде R, установите его командой install.packages("RODBC") .

Интерфейс ODBC первоначально был разработан корпорацией Microsoft в начале 1990-х гг. За прошедшее с тех пор время он стал официальным стандартом для доступа к данным под общим названием SQL/CLI (Call Level Interface). Среда ODBC состоит из компонента ODBC Driver Manager и ODBC-совместимого драйвера для конкретного сервера баз данных. В операционной системе Windows компонент ODBC Driver Manager встроен в саму платформу, однако в случае Linux или других платформ компонент ODBC Driver Manager следует установить отдельно.

Для соединения с базой данных при посредстве RODBC-драйвера необходимо указать расположение сервера, имя базы данных и информацию для доступа (например, имя пользователя и пароль). Имя базы данных обычно задается в виде DSN-имени ODBC. DSN-имя – это подробная ссылка на базу данных, которая может быть как локальной, так и удаленной относительно клиентского компьютера. DSN можно рассматривать как псевдоним базы данных - он не обязан соответствовать подлинному имени базы данных, заданному на сервере.

Для создания DSN-имен на платформе Windows применяется инструмент ODBC Data Source Administrator, доступный в меню Control Panel->Administration Tools. На рис. 5 показаны несколько DSN-имен, определенных на этом клиентском компьютере.

Рисунок 5. Windows-инструмент ODBC Data Source Administrator

Если вы планируете использовать R и DB2 Express-C for Windows с локальными базами данных, то DSN-имена для ваших баз данных уже должны существовать в системе. Если вы хотите обращаться к удаленной базе данных, то необходимо выполнить следующие действия: получить подробности соединения от поставщика сервера баз данных, каталогизировать это соединение и задать DSN-имя в инструменте ODBC Data Source Administrator.

Кроме того, существует которым вы можете воспользоваться, если хотите обойтись без DSN-имен.

Пакет RODBC предоставляет несколько функций, имеющих отношение к соединениям (таблица 2).

Таблица 2. RODBC — функции соединения и метаданных

Функция	Описание / назначение	Информация на входе	Информация на выходе
odbcDataSources ()	Предоставляет список доступных DSN-имен.	Не требуется	Символьный вектор DSN-имен.
odbcConnect (dsn, uid, pwd, ...)		dsn="DSN_name", uid="USERID", pwd="password" - другие необязательные параметры
odbcDriverConnect (connection ="", ...)	Устанавливает соединение с сервером баз данных.	connection string. Значения для DSN, идентификатора пользователя и пароля должны быть предоставлены в одной строке. См. пример в .	Объект channel, представляющий активное соединение с базой данных.
odbcGetInfo (channel)	Предоставляет подробную информацию об активном соединении с базой данных.	Объект channel, представляющий активное соединение с сервером баз данных.	Именованный символьный вектор, описывающий соединение, включая тип ODBC-драйвера и уровень соответствия стандартам API.

Установив соединение (channel ) с нашей базе данных, мы можем использовать функции метаданных для исследования поддерживаемых типов данных, определений таблиц и заданных в них столбцов.

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

Таблица 3. RODBC — функции метаданных базы данных и таблиц

Функция	Описание / назначение	Информация на входе	Информация на выходе
sqlTypeInfo (channel, ...)	Предоставляет информацию о поддерживаемых типах данных ODBC-базы данных	channel	Кадр данных поддерживаемых типов данных и их характеристик.
sqlTables (channel, ...)	Предоставляет описание объектов табличного вида, определенных в рамках базы данных.	channel рекомендуемые необязательные параметры: "schema=", "tableType="	Кадр данных, содержащий подробности о таблицах, о представлениях и других объектах табличного вида в базе данных.
sqlColumns (channel, sqtable, ...)	Предоставляет описание столбцов, определенных в рамках таблицы.	channel, table name	Кадр данных, содержащий подробности об именах столбцов и о других атрибутах таблицы.

Когда вы определите, с какой таблицей вы собираетесь работать, воспользуйтесь функцией sqlFetch() для извлечения данных в R. Пакет RODBC отобразит эти данные на соответствующий тип данных R, исходя из заданных отображений, которые содержатся в выходной информации функции getSqlTypeInfo("DB2/NT") .

Рассмотрим простой R-скрипт, который устанавливает соединение с базой данных с использованием DSN-имени и задает некоторую базовую информацию. Программа определит количество табличных объектов и столбцов в указанной таблице. Функция sqlFetch() извлекает все данные в R и показывает первую строку, которая была возвращена в кадре данных.

В листинге 5 функция odbcConnect() и функции метаданных показывают, что в схеме "DB2INST1" имеется 27 таблиц и что в таблице fuel economy задано 18 столбцов. Функция sqlFetch() запрашивает содержимое таблицы и создает кадр данных с именем cars . Первые четыре переменные первого наблюдения в кадре данных демонстрируются пользователю; затем соединение с сервером базы данных закрывается с помощью функции odbcCloseAll() .

Листинг 5. RODBC — установление соединения и использование функций метаданных

library(RODBC) dsn.name <- "blusamp" user.name <- "granthut" con1 <- odbcConnect(dsn=dsn.name,uid=user.name,pwd) table.list <- sqlTables(con1,tableType="TABLE", schema="DB2INST1") cat("There are", nrow(table.list), "tables in the DB2INST1 schema.\n") table.name <- "DB2INST1.US_FUEL_ECONOMY_AUGUST_2013" col.list <- sqlColumns(con1,table.name) cat("There are", nrow(col.list), "columns defined in", table.name,"\n") # Отобразить одну строку из таблицы cars <- sqlFetch(con1, table.name) print (cars, row.names=FALSE) # Закрыть соединение odbcCloseAll() cat("Database connections are closed.\n") ---- OUTPUT from Script There are 27 tables in the DB2INST1 schema. There are 18 columns defined in DB2INST1.US_FUEL_ECONOMY_AUGUST_2013 MODEL_YEAR MFR_NAME DIVISION CARLINE 2013 BMW BMW 135i Convertible Database connections are closed.

Другой метод установления соединения с базами данных DB2 при посредстве RODBC предусматривает применение функции odbcDriverConnect() . В листинге 6 обратите внимание на то, что db.name — это реальное имя базы данных на сервере баз данных, которое не обязательно совпадает с DSN-именем, созданным на клиентском компьютере.

Листинг 6. RODBC — метод прямого соединения

driver.name <- "{IBM DB2 ODBC DRIVER}" db.name <- "SAMPLEDB" host.name <- "bluforcloud.imdemocloud.com" port <-"50001" user.name <-"granthut" # Используем полную строку соединения для установления соединения с базой данных SAMPLE con.text <- paste("DRIVER=",driver.name, ";Database=",db.name, ";Hostname=",host.name, ";Port=",port, ";PROTOCOL=TCPIP", ";UID=", user.name, ";PWD=",pwd,sep="") con1 <- odbcDriverConnect(con.text)

Извлечение данных

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

Как показано в таблице 4, функция sqlQuery() позволяет отправить серверу баз данных любой допустимый SQL-запрос. Если вы имеете полномочия для применения операции DROP к таблицам или для удаления данных, то функции sqlDrop() и sqlClear() также доступны.

Таблица 4. RODBC — функции извлечения и удаления данных

Функция	Описание / назначение	Информация на входе	Информация на выходе
sqlQuery (channel, query, ...)	Исполняет SQL-запрос на сервере баз данных и возвращает результаты.	channel, query рекомендуемые опции: errors=FALSE (помогает фиксировать возможные ошибки)	Кадр данных результирующего набора. Данные будут отображены на совместимые типы данных R.
sqlDrop (channel, sqtable, ...)	Удаляет содержимое и определение таблицы из базы данных.	channel, table	Обратите внимание, что эта функция попытается выполнить операцию DROP TABLE.
sqlClear (channel, sqtable, ...)	Удаляет все строки из таблицы в базе данных.	channel, table	Обратите внимание, что эта функция попытается выполнить операцию TRUNCATE TABLE.

Весьма универсальную функцию sqlQuery() можно использовать для инициирования SQL-операторов на языке DML (Data Manipulation Language), таких как SELECT, INSERT, UPDATE, DELETE, и для инициирования SQL-операторов на языке DDL (Data Definition Language), таких как CREATE TABLE. SQL-оператор в листинге 7 содержит ошибку. Тип данных указан некорректно - фраза INTEGR должна была бы иметь вид INTEGER. К счастью, в нашем распоряжении имеется функция odbcGetErrMsg() для получения и демонстрации подробного сообщения об ошибке. Эта функция очень полезна при редактировании и тестировании R-скриптов.

Листинг 7. RODBC — диагностика ошибок

res <- sqlQuery(con1,"CREATE TABLE TESTDATA (c1 INTEGR)", errors=FALSE) if (res == -1){ cat ("An error has occurred.\n") msg <- odbcGetErrMsg(con1) print (msg) } else { cat ("Table was created successfully.\n") } ---- OUTPUT from Script An error has occurred. "42704 -204 SQL0204N \"INTEGR\" is an undefined name. SQLSTATE=42704\r\n" " ERROR: Could not SQLExecDirect "CREATE TABLE TESTDATA (c1 INTEGR)""

Хранение данных

Сохранение данных в реляционных БД обычно производится с помощью SQL-оператора INSERT. Если у вас есть кадр данных R и вы хотите поместить эти данные на хранение в реляционную базу данных, воспользуйтесь функцией sqlSave() . По умолчанию эта функция создает новую таблицу с теми же данными, которые содержатся в переменной кадра данных. Имена столбцов также будут совпадать с именами столбцов в кадре данных. При желании вы можете не сохранять имена строк R из кадра данных в сохраненной таблице, поскольку в рамках вашего набора данных обычно имеется естественный ключ.

Функция sqlSave() заполняет таблицу данными из кадра данных при помощи SQL-оператора INSERT. Если вы хотите сохранить большой набор данных, может быть предпочтительнее создать внешний файл данных с разделителями, а затем применить высокоскоростной загрузчик конкретной базы данных для публикации данных и их передачи в коллективное пользование другим аналитикам данных. После того как данные будут сохранены в реляционной базе данных, вам больше не придется беспокоиться о поддержании их целостности и доступности — сервер баз данных возьмет это на себя.

В листинге 8 функция sqlSave() создает и наполняет новую таблицу. Поскольку кадр данных имеет имя CLASSMARKS, таблица будет иметь такое же имя. В этом сценарии параметр safer разрешает скрипту заменить существующую таблицу с таким же именем. По умолчанию при попытке сохранить данные в существующую таблицу возвращается ошибка.

Листинг 8. RODBC — сохранение данных

tab.name <- "CLASSMARKS" NAMES <- c("Bob","Mary","Fred") MARKS <- c(78,88,91) # Создать кадр данных с результатами теста и именами CLASSMARKS <- data.frame (NAMES,MARKS,stringsAsFactors=FALSE) # Создать новую таблицу и наполнить ее данными из кадра данных CLASSMARKS sqlSave(con1, CLASSMARKS, rownames=FALSE,safer=FALSE) NEWCLASS <- sqlFetch(con1,tab.name) cat("Mean mark for the class is", mean(NEWCLASS[,"MARKS"]),"\n") ---- OUPUT from Script Mean mark for the class is 85.66667

Кроме того, можно модифицировать данные в существующих таблицах с помощью RODBC-функции sqlUpdate() . В качестве более эффективной альтернативы я рекомендую использовать SQL-операторы UPDATE, или просто сохранить новые данные во временной таблице с помощью функции sqlSave() , а затем выполнить SQL-оператор UPSERT для этих таблиц. Для функции sqlUpdate() требуется параметр index, служащий для однозначной идентификации каждой строки в существующей таблице.

Использование хранимых процедур

Хранимые процедуры — это программные модули, управляемые и исполняемые сервером баз данных. Поскольку данные расположены на одном сервере с программой, хранимые процедуры могут оказаться весьма эффективными и выполнять некоторые задачи гораздо быстрее, чем приложения на клиентской стороне. Хранимые процедуры DB2 могут быть написаны с использованием различных языков программирования, таких как Java, C, PL/SQL и SQL PL. Если у вас имеются готовые хранимые процедуры или если вашу аналитическую работу можно упростить либо улучшить с помощью хранимых процедур, их можно легко вызвать из R-скрипта при посредстве RJDBC или RODBC.

В листинге 9 хранимая процедура GETMEDIAN вызывается с помощью функции sqlQuery() . В этом сценарии возвращаемые данные представляют собой кадр данных, содержащий единственное значение. Интересующий учебный предмет передается в хранимую процедуру как входной параметр. Какого-либо доступного механизма для извлечения нескольких наборов результатов или выходных параметров из хранимой процедуры DB2 на данный момент не существует.

Листинг 9. RODBC — хранимые процедуры

# Вызов хранимой процедуры для получения средней отметки (название предмета передается во входной переменной subject) subject <-"MATH" median <- sqlQuery(con1,"CALL GETMEDIAN (subject)") print (median)

Заключение

Язык R — это мощный инструмент с открытым исходным кодом для анализа данных, который помогает исследовать данные и создавать прогнозирующие модели. Когда объем ваших данных превысит предельные возможности памяти R, вы сможете прибегнуть к такому способу, как загрузка данных в какой-либо сервер баз данных, например, в IBM DB2 with BLU Acceleration или в IBM BLU Acceleration for Cloud. В этой статье мы исследовали преимущества использования R с базами данных вместо анализа данных в текстовых файлах с разделителями. Кроме того, вы познакомились с использованием пакетов RJDBC и RODBC для анализа больших данных, хранящихся в DB2.

Power Query - это инструмент интеграции данных в Excel из внешних источников.
Power Query позволяет искать и открывать данные из различных источников доступных онлайн и через корпоративные сети. Он умеет загружать в Excel данные разных типов, форматов и структур, а так же из совершенно разных источников:

Вики источник

Power Query позволяет импортировать внешние (семи)-структурированные источники данных и обрабатывать их в Excel. Пример, ниже показывает крупнейшие города в северной и южной Америках на карте в соответствии с их населением.

(кликабельно)

Импорт и анализ писем в Outlook

Так же имеется возможность импортировать письма, как источник данных, и анализировать их в Outlook. Пример ниже демонстрирует импорт почты и создает гистограмму по числу писем от человека, то есть с кем чаще всего происходит переписка.

(кликабельно)

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

Минусы : Не годится для «обычных» пользователей, сложен в освоении, достаточно медлителен. Нет возможности разделения доступа, ограничения на размер файлов/записей etc.

Дополнительные материалы

• Deep Dive into the Query language (видео)
• Подборка видео раз и два
• Подборка статей от Microsoft

Скачать

MS Power BI

Power BI - это инструмент создания интерактивных бизнес отчетов с возможностью совместной работы, визуализации и интерактивной работы.

Основные возможности:

• Быстрая разработка информативных бизнес отчетов и панелей (в сети) – с возможностью взаимодействия и исследования данных.
• Автоматическое обновление BI-отчетов и визуализации, при изменении данных
• Поддержка языка запросов, в том числе и Power Query. Возможность взаимодействия между участниками на уровне запросов.
• Создание каталога данных с индексами для поиска.
• Язык запросов близкий к естественному (для бизнес-аналитика) и возможность интерактивной работы.
• Поддержка мобильных устройств

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

Пример с запросами и объединением источников: комбинирование источников из Нью-Йоркской фондовой биржи и финансового индекса S&P 500.

(кликабельно)

Плюсы : новый современный продукт, дружелюбный интерфейс, легок в освоении, онлайн решение.

Минусы : решение «сырое» (некоторые компоненты могут работать нестабильно), не работает с OLAP кубами, урезанный функционал в сравнении с конкурентами.

Дополнительные материалы:

• Вводное видео в Power BI
• Пример применения Power BI для финансового анализа
• Обзор возможностей (видео)
• Анализ истории World Cup history с помощью Excel и Power BI

Pyramid Analytics

Pyramid Analytics - облачная платформа бизнес-аналитики; имеет три ключевых компонента:

• Интеллектуальный анализ данных - Data Discovery
• Интерактивная работа с данными и визуализацией - Dashboards
• Представление данных аудитории - Publisher

Примеры отчета с гео-визуализацией и возможностью исследования данных.

Вводное видео:

(youtube-канал Pyramid Analytics)

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

PyramidAnalytics - самый мощный инструмент из используемых нами.

Плюсы : легок в освоении, работает с огромным количеством источников, очень широкая функциональность.

Минусы : цена.

Компоненты аналитики MS SQL server (MDS, SSIS, SSAS)

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


Master Data Services - процессы и инструменты управления мастер-данными компании. Подробнее .
(Мастер-данные - это данные бизнеса: о клиентах, продуктах, услугах, персонале, технологиях, материалах etc.)

SQL Server Integration Services - миграция и интеграция данных. Подробнее .

SQL Server Analysis Services OLAP и data mining внутри SQL сервера. Подробнее .

Главный инструмент - мозг

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


(Matplotlib

Взаимодействие между R и python

Интерактивные веб-графики в plotly

Бонус-инструмент

В процессе написания статьи Microsoft анонсировала покупку инструмента аналитики от компании Datazen , поэтому будет полезным его кратко упомянуть.

Datazen - это мульти-платформенное решение, позволяющее построить отчетность, независимо от платформы (PC, iOS, Android, Windows Phone). Отличительной чертой продукта является широкая интеграция и возможность полноценной работы аналитической платформы через мобильные устройства.

Плюсы : поддерживает широкий спектр платформ, поддерживается и развивается MS, работает с большим количеством источников.

Минусы : Не работает с OLAP напрямую (необходимо создавать запросы MDX самостоятельно), имеет ряд ограничений (начиная от возможностей интерфейса и заканчивая настройкой функциональности), не продается отдельно, идет в комплекте с MS SQL Enterprise Edition.

В следующей статье мы рассмотрим Datazen и Pyramid Analytics в деталях, а также разберемся как на их основе создавать отчетность.

Ирина Никифорова получила PhD по социологии в Технологическом институте Джорджии. В настоящее время она является научным сотрудником НИУ ВШЭ. Она также является доцентом Кафедры методов и технологий социологических исследований факультета социологии кампуса НИУ ВШЭ в Санкт-Петербурге.

Аннотация:

Программы с открытым кодом, такие как система управления базами данных MySQL и язык программирования R, предназначенный для статистических вычислений, стали стандартными средствами анализа данных в академических и прикладных исследованиях. Эти инструменты могут использоваться в различных операционных системах, включая Windows, Linux и Mac OS. Данный курс предназначен для исследователей, обладающих навыками работы с R и специализирующихся в сфере статистического анализа данных в различных дисциплинах, которые хотели бы также освоить базы данных MySQL, методы их интеграции с R и способы разрешения возникающих при этом технических проблем.

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

Предполагается, что слушатели курса знакомы с языком программирования R и владеют навыками статистического анализа в R, а также имеют опыт работы с базами данными. Для участия в курсе требуется иметь личный ноутбук с установленными программами R, Excel и SPSS.

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

• Программа курса

Структура курса:

• Семинар 1: Принципы работы с базами данных и системы управления базами данных; установка и основы использования MySQLи SQLite
• Семинар 2: Навигация по базам данных: основы языка SQL и формирование запросов.
• Семинар 3: Комплексные запросы в SQL
• Семинар 4: Управление данными и совместимость
• Семинар 5: Интеллектуальный анализ данных в R.

• Bessant, Conrad, Shadforth, Ian, & Oakley, Darren. (2009). Building Bioinformatics Solutions: with Perl, R and MySQL . Oxford, UK: Oxford University Press.
• Spector, Phil. (2008). Data Manipulation with R (Use R!) . New York: Springer.
• Torgo, Luis. (2010). Data Mining with R: Learning with Case Studies . Chapman & Hall/CRC.
• Welling, Luke, & Thomson, Laura. (2003). MySQL Tutorial . Indianapolis, Indiana, USA: MySQL Press.

Если вы хотите стать специалистом по обработке и анализу данных, без SQL не обойтись. Пользоваться этим языком могут не только аналитики или учёные, но и все, кто может потратить несколько дней на изучение SQL запросов :

SQL для анализа данных? Что это? Какая от него польза?

SQL расшифровывается как Structured Query Language , что переводится как «язык структурированных запросов ».

Но я люблю другое описание: это Excel на стероидах. Вот как выглядит один и тот же набор данных в Excel и SQL :

Как мы видим, SQL и Excel довольно похожи. Оба представляют данные в виде двухмерной таблицы со столбцами и строками. Оба предельно структурированы и просты для понимания.

Но между ними есть и ряд отличий:

1. Первое - это производительность . Excel отлично работает с небольшими объёмами данных, но когда у вас 100 000 строк, использование формул становится долгим и неэффективным занятием.
2. Второе - это способ взаимодействия с базой данных . Excel - это, в первую очередь, графический интерфейс пользователя (GUI ). Он позволяет прокручивать таблицу, вводить формулы, курсором необходимые области. В SQL нет графического интерфейса - здесь используются так называемые SQL-запросы .

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

При анализе данных вы будете использовать SQL для довольно простых задач: суммирования, объединения массивов данных, простых статистических и математических методов. Но с SQL ваша работа станет эффективнее, и вы сможете использовать более крупные массивы данных, чем раньше.

А как насчёт Python, R или bash?

Когда вы начнёте использовать эти языки для анализа данных, то поймёте, что Python и R хороши для одного, а SQL для другого. Основное различие этих языков - в синтаксисе, «функциях » и производительности. Ну а теперь давайте перейдём к практической части!

Шаг 0 – установите SQL-систему управления базами данных

Я буду использовать postgreSQL . Есть и другие виды СУБД SQL . Но все они похожи, поэтому если выучить postgreSQL , адаптация к другому языку займёт всего несколько часов (или даже минут).

На данный момент у вас должно быть три вещи:

1. Сервер данных с доступом через терминал или iTerm ;
2. PostgreSQL , установленный на ваш сервер;
3. Установленный на компьютер Pgadmin4 (или SQL Workbench ).

Примечание : в качестве инструмента для работы с SQL я буду использовать SQL Workbench .

Шаг 1 – Зайдите в свою базу данных SQL через командную строку

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

1. Откройте Терминал (или iTerm ):
2. Подключитесь к серверу через SSH .

В моём случае я ввожу:

ssh tomi@[мой_ip_адрес]

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

psql -U tomi -d postgres

psql это сама команда, -U указывает на имя пользователя (в моём случае это “tomi” ), а -d указывает название базы данных (в моём случае postgres , как и у вас ). Приглашение командной строки должно измениться на следующее:

Postgres=>

Готово! Теперь у вас есть полный доступ к базе данных SQL .

1. Для проверки SQL запроса можете ввести:

dt


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

Примечание : базы данных SQL также называют «реляционными базами данных ».

Шаг 2 – Загружаем данные

В этом разделе мы будем работать с небольшим набором данных под названием zoo («зоопарк »). Можете скачать его отсюда в текстовом формате .tsv .

Но давайте продолжим с SQL запроса примера :

1) Создайте таблицу :

CREATE TABLE zoo (animal varchar(10), uniq_id integer PRIMARY KEY, water_need integer);

2) Проверьте, что таблица точно была создана :

У нас есть новая таблица данных: zoo!

3) Загружаем данные!

Скопируйте запрос:

INSERT INTO zoo (animal,uniq_id,water_need) VALUES ("elephant",1001,500), ("elephant",1002,600), ("elephant",1003,550), ("tiger",1004,300), ("tiger",1005,320), ("tiger",1006,330), ("tiger",1007,290), ("tiger",1008,310), ("zebra",1009,200), ("zebra",1010,220), ("zebra",1011,240), ("zebra",1012,230), ("zebra",1013,220), ("zebra",1014,100), ("zebra",1015,80), ("lion",1016,420), ("lion",1017,600), ("lion",1018,500), ("lion",1019,390), ("kangaroo",1020,410), ("kangaroo",1021,430), ("kangaroo",1022,410);

Если всё прошло успешно, вы получите следующее сообщение:

INSERT 0 22

Самый важный оператор SQL: SELECT

Настало время изучить главный оператор языка запросов SQL . Вот он:

SELECT * FROM имя_таблицы;

Мы будем пользоваться SELECT каждый раз, когда нужно будет прочитать, отфильтровать, преобразовать и суммировать данные. Для начала выберем (английское слово select переводится именно «выбрать» ) всё, что есть в таблице zoo .

SELECT * FROM zoo;

Оператор вернул мне всю таблицу.

SELECT * FROM zoo;

SELECT - основной оператор, сообщающий SQL , что мы хотим прочитать данные из таблицы.
* - указывает на то, что мы хотим выбрать все столбцы.
FROM сообщает SQL , что мы собираемся указать таблицу для чтения данных.
zoo это название таблицы. Его можно заменить на любое другое, если у вас есть несколько таблиц.
; это синтаксис, обязательный для SQL . Каждый запрос должен заканчиваться точкой с запятой. Если случайно её пропустить, SQL будет ждать продолжения запроса, и ничего не выведет на экран.

Рассмотренное выше выражение мы будем регулярно использовать при работе со сложными запросами SQL .

Выбор столбцов

Знак * можно заменить на названия столбцов. Рассмотрим в качестве примера следующий запрос:

SELECT animal, water_need FROM zoo;

Получаем именно то, что ожидали: на экране есть столбцы “animal” и “water_need” , но уже нет “uniq_id” .

Аналогичным образом можно выбирать любые столбцы, достаточно указать их имена, разделённые запятой. Можно сделать и что-то вроде этого:

SELECT animal, animal, animal FROM zoo;

Один и тот же столбец повторится несколько раз… Но так как смысла в этом мало, делать так не стоит.

Показываем несколько первых строк данных - инструкция LIMIT

На данный момент мы работаем с таблицей данных, состоящей, из 22 строк. Чтобы ограничить выборку несколькими первыми строками, воспользуемся инструкцией LIMIT – небольшим «расширением » к базовому запросу:

SELECT * FROM zoo LIMIT 10;

На экран будет выведено только 10 первых строк.

Фильтрация строк - инструкция WHERE

Используя инструкцию WHERE , можно выбрать конкретные строки, основываясь на их значениях. SQL запроса пример:

SELECT * FROM zoo WHERE animal = "elephant";

SELECT * FROM zoo –» это «базовый запрос »
WHERE –» говорит SQL , что вы хотите что-то отфильтровать.
animal = "elephant" –» animal - это название столбца, в котором мы ищем нужное значение, а elephant – само значение. В SQL всегда необходимо добавлять столбец, в котором мы ищем нужное значение.
; –» Не забываем про точку с запятой!

Самопроверка #1

Это вводная статья, поэтому первое задание тоже будет довольно простым:

Выберите первых трёх зебр (zebra ) из таблицы zoo .

Решение этой задачи будет, по сути, итогом сегодняшней статьи.

Готовы?

Вот моё решение:

SELECT * FROM zoo WHERE animal = "zebra" LIMIT 3;

Согласитесь, SQL - это просто .

И ещё кое-что: синтаксис…

1. Все SQL запросы должны заканчиваться точкой с запятой (; ). Если вы случайно её пропустите, SQL будет считать, что запрос не закончен, и на экран ничего не выведется. Например:

postgres=> SELECT * FROM zoo postgres->

Как-то не очень… Вот так будет лучше:

Postgres=> SELECT * FROM zoo;

1. Язык SQL не чувствителен к регистру символов, когда дело касается ключевых слов (SELECT , WHERE , LIMIT и т.д .). Например:

SELECT * FROM zoo;

работает так же, как и

Select * from zoo;

Чувствительность к регистру имён таблиц, столбцов и их значений зависит от настроек. В нашей ситуации (при использовании postgreSQL ) имена таблиц и столбцов не чувствительны к регистру, в отличие от значений полей. Например:

SELECT * FROM zoo WHERE animal = "elephant"; –» РАБОТАЕТ SELECT * FROM ZOO WHERE ANIMAL = ‘elephant’; –» РАБОТАЕТ SELECT * FROM ZOO WHERE ANIMAL = "ELEPHANT"; –» НЕ РАБОТАЕТ

Обратите внимание, что большинство людей в языке запросов SQL пишут ключевые слова заглавными буквами (SELECT, WHERE, LIMIT, etc… ), что повышает читаемость кода.

Заключение

Мы сделали первый шаг к использованию SQL для анализа данных! Поздравляю! Теперь вы можете писать основные запросы. Но это только начало!

Перевод статьи “SQL for Data Analysis – Tutorial for Beginners – ep1 ” был подготовлен дружной командой проекта Сайтостроение от А до Я.