Современные библиотеки R и/или Python делают SQL устаревшим?

Вопрос или проблема

Я работаю в офисе, где SQL Server является основой всего, что мы делаем, от обработки данных до их очистки и трансформации. Мой коллега специализируется на написании сложных функций и хранимых процедур для методической обработки входящих данных, чтобы их можно было стандартизировать и использовать в отчетах, визуализациях и аналитических проектах. Прежде чем начать работать здесь, у меня было очень мало опыта с SQL, кроме написания самых простых запросов. Большинство моих подготовительных работ по анализу выполнялось в R. Мой начальник настаивает на том, чтобы я улучшил свои навыки SQL, хотя, похоже, очень мало заданий, которые нельзя выполнить более эффективно и с гораздо меньшим количеством строк кода с использованием таких пакетов R, как dplyr, data.table и tidyr (по имени нескольких). Мой вопрос заключается в том, имеет ли это смысл?

Несколько недель назад я столкнулся с задачей получить список имен столбцов для каждой строки в таблице, соответствующей определенным критериям, и соединить их в вектор строк. Сроки были очень сжатыми, и в тот момент я испытывал некоторую затрудненность и не мог полностью понять проблему. Я спросил своего начальника, который в свою очередь попросил моего коллегу написать скрипт TSQL для решения проблемы. Пока он работал над этим, я придумал способ сделать это в R, написав довольно простую функцию и применив ее к фрейму данных. Мой коллега вернулся со своим скриптом примерно через два часа. Он содержал как минимум 75 строк и состоял из двух вложенных циклов for. Я попросил его сообщить, когда скрипт будет выполнен, и он сказал, что это займет несколько часов. В то время как мой скрипт на R смог обработать примерно 45 000 записей за 30 секунд.

Правильно ли я предполагаю, что R гораздо лучше подходит для очистки и трансформации данных? Может быть, разработчик SQL в моем офисе просто некомпетентен? Мне интересно, есть ли у кого-то, кто работал и с R, и с SQL (или с Python и SQL), мысли на этот счет.

R и SQL – это два совершенно разных инструмента. SQL – это язык, который вы можете использовать для запроса данных, хранящихся в базах данных, как вы уже заметили. Преимущества SQL по сравнению с R в основном заключаются в том, что у вас есть сервер базы данных (MS SQL, Oracle, PostgreSQL, MySQL и т. д.).

Большинство, если не все, современные серверы баз данных позволяют нескольким пользователям запрашивать данные из одного источника данных и вставлять, обновлять и удалять данные в одних и тех же таблицах, одновременно обеспечивая согласованность данных. Это крайне важно, например, для записи банковской транзакции. Можете ли вы представить, чтобы вести банк на R? Вот для чего нужны серверы баз данных. Они обеспечивают свойства ACID для выполняемых процедур в базе данных. ACID обозначает атомарность, согласованность, изоляцию и долговечность (см. описание ACID на википедии). R – это платформа для одного пользователя, где все происходит в оперативной памяти. Поэтому, если ваш компьютер перестанет работать в середине крупной операции, ваши данные не будут сохранены. Вы также единственный человек, который имеет доступ к данным. Чтобы было ясно, R не считается альтернативой серверам баз данных и/или SQL.

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

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

В моем опыте есть конкретные случаи использования для SQL и R/Python. SQL отлично подходит для хранения важных для бизнеса данных и для предоставления возможности нескольким людям получать доступ, изменять, вставлять и удалять данные в централизованной среде. Для одноразовой трансформации данных R и Python отличные. Если ваша трансформация данных должна выполняться периодически, вам нужно будет портировать ваш скрипт R/Python в SQL.

Эти два инструмента, на самом деле, даже нельзя сравнивать. SQL – это язык, предназначенный для доступа к данным, R – это язык, предназначенный для работы с данными.

SQL не является эффективным инструментом для трансформации данных, потому что трудно видеть промежуточные шаги, и когда он выдает ошибки, это, как правило, не касается формы/качества/структуры ваших данных.

Мой рабочий процесс обычно выглядит так:

Получить сырые данные из SQL-запроса (в R)
Создать рутину трансформации
Если возможно, переписать SQL-запрос, чтобы выполнить трансформацию, которую я выполнил в R

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

Библиотека dbplyr имеет правильный подход: пишите все в R (используя tidyverse) и позволяйте библиотеке компилировать код R в низкоуровневый SQL непосредственно перед выполнением.

Поскольку не всякая трансформация переводима, другой подход заключается в том, что SQL Server позволяет вызывать фрагменты кода R из SQL-команд “select”.

Подход 1., 2., 3., упомянутый HEITZ, в моем опыте возможен, но с альтернативой для 3., когда вы записываете свои данные из R (data.table) обратно в MySQL.

Таким образом, полные шаги: MySQL -> data.table -> MySQL.

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

Одним словом, НЕТ. SQL – это мощный, краткий и гибкий способ описать и обобщить структурированные, полуструктурированные и даже неструктурированные данные, когда над ним располагается соответствующий интерпретатор. Кстати, SQL считается почти обязательным для ученых данных. SQL – это краткий и мощный способ выполнения его основных операций:

проекции (select ..)
фильтрация (where ..)
группировка / фильтрация (group by и having)
базовые агрегации (count, sum, avg ..)
соединения

Настоящая сила заключается в комбинировании результатов с помощью встроенных представлений. Когда мне нужно это сделать, я использую один из sqldf, pandasql, pysparkSql/sparkSql или прямое rdbms соединение. Написать то же самое наиболее лаконичным образом с использованием data.table (значительно лучше, чем data.frame) или datatable (лучше, чем pandas) гораздо сложнее, намного сложнее или практически невозможно, в зависимости от сложности запрашиваемых запросов.
Что касается трансформации данных: это совершенно другая история: некоторые операции легко выражаются в SQL, а некоторые – не очень. Однако когда вы включаете UDF, есть больше возможностей того, что можно достичь. Моя текущая задача включает несколько UDF для выполнения таких действий, как нестандартные операции пересечения, нестандартные агрегации и нестандартные методы оценивания.

У меня такая же проблема, как у автора темы, но у меня большой опыт в SQL. SQL уместен периодически, но наша база данных очень регулярна: она растет менее чем на 6000 строк данных в год! Я бы предположил, что общее количество всех доступных разрозненных строк от начала истории менее 700000. Чрезмерная зависимость от SQL для анализа небольших данных – это как передавать волшебный шар. В зависимости от скрипта вы получите совершенно разные результаты на один и тот же вопрос.
Применение аккуратных принципов к SQL критически важно, и минимизация его использования для предварительного анализа имеет ключевое значение. Да, R предпочтителен после того, как вы запускаете SQL для получения обширных данных, но если вы не знаете R, вы можете создать все, что хотите, в Power BI бесплатно и ускорить запросы там в PowerQuery.
SQL – это молоток, но очистка данных – это не гвоздь.
Очевидно, для действительно больших операций с данными начальные таблицы требуют сложных и времязатратных запросов. Вам нужно уметь это кодировать, но как ученый данных я должен быть осторожным, чтобы уменьшить зависимость от SQL и внедрить аккуратные принципы, не изобретая велосипед.

Ответ или решение

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

Основные различия между SQL и библиотеками R/Python

1. Предназначение:

• SQL (Structured Query Language) спроектирован для взаимодействия с реляционными базами данных. Он оптимизирован для выполнения запросов, которые работают с большими объемами данных, обеспечивая безопасность и целостность при параллельном доступе нескольких пользователей.
• R и Python предназначены для анализа и статистической обработки данных. Эти языки используются для манипуляций с данными в памяти, что дает возможность более гибко проводить сложные операции.

2. Производительность:

• SQL-серверы, такие как MS SQL, Oracle или PostgreSQL, используют оптимизацию запросов, кэширование, индексы и другие механизмы, чтобы быстро обрабатывать данные. Как в вашем примере, запросы в SQL могут выполняться медленно, если они не оптимизированы.
• Однако библиотеки R, такие как dplyr, data.table и Python библиотеки вроде pandas, могут в некоторых случаях обрабатывать данные быстрее, особенно когда речь идет о небольших объемах данных, благодаря удобному синтаксису и сильной функциональности.

3. Атомарность и совместная работа:

• SQL обеспечивает атомарность транзакций, целостность и устойчивость данных (принципы ACID). Это делает SQL идеальным решением для критически важных бизнес-приложений.
• R и Python являются однопользовательскими платформами, где данные хранятся в памяти устройства, что делает их недостаточно надежными для долговременной работы с критичными данными.

Когда использовать R/Python и SQL

Для одноразовой обработки данных:

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

Для периодических задач:

• Если требуется периодическая не только обработка, но и хранение данных, SQL будет более подходящим выбором. Использование R или Python в таких случаях подразумевает, что вам нужно будет переводить ваши скрипты обратно в SQL.

Интеграция:

• Современные подходы позволяют использовать R и Python вместе с SQL. Например, библиотека dbplyr в R позволяет писать код на R, который будет преобразован в SQL-запросы, а также различные механизмы, позволяющие интегрировать R-код в SQL-запросы.

Заключение

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

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