Как только что закончивший вуз, планирующий сделать карьеру в области науки о данных, я думал, что знание Python — это основной навык программирования, который мне потребуется для начала успешной карьеры. Я предпочитал что-то кроме SQL и всегда любил манипулировать данными в python.
Однако мои методы работы изменились, как только я получил некоторый опыт работы в отрасли. Я понял, почему SQL по-прежнему остается одним из широко используемых языков и, что более важно, где он подходит для работы с данными.
В этом блоге мы рассмотрим важность SQL в науке о данных и рассмотрим стратегию его эффективного использования.
SQL для науки о данных
Один из ключевых уроков в моей карьере состоял в том, чтобы отделить себя от технологий (или языков программирования) и больше сосредоточиться на эффективном решении проблем. Как специалист по данным, моя работа обычно связана с изучением наборов данных и построением моделей машинного обучения. Хотя Python хорошо подходит для разработки модели машинного обучения, SQL можно использовать для исследования и разработки функций, что является важным шагом перед созданием любой модели по следующим причинам.
- Во-первых, манипулирование данными в блокнотах Jupyter с использованием Python может быть очень медленным для больших наборов данных с данными объемом в терабайты.
- Мы могли бы ускорить манипулирование данными, используя Python с такими технологиями, как Spark, на облачных платформах. Однако использование этих технологий имеет свою цену. Они хороши, если у организации есть аппетит и, что более важно, желание использовать такие передовые технологии; в противном случае инструменты требуют настройки и управления инфраструктурой для поддержки проекта, что влечет за собой дополнительные расходы.
- Напротив, у большинства организаций уже есть базы данных/хранилища, содержащие наборы данных. Традиционно вычислительная мощность таких систем коррелирует с размером наборов данных, т. е. локальные системы баз данных/хранилищ с большими наборами данных будут иметь возможность обрабатывать данные и, следовательно, иметь значительную вычислительную мощность.
- В настоящее время, если мы говорим об облачных вычислениях, базы данных/хранилища на большинстве платформ предлагают изоляцию хранения от вычислений. Следовательно, манипулирование данными в этих системах может быть выполнено с минимальным увеличением затрат.
Теперь, когда мы понимаем, какое место SQL занимает в проекте по науке о данных, давайте рассмотрим выгодную стратегию написания сложных запросов для создания важных функций.
Разделяй и властвуй
Когда я был студентом, моим самым большим заблуждением было использование SQL только как набора операторов выбора и объединения. Я всегда полагался на языки программирования, такие как python, C++ или даже java, чтобы манипулировать данными по своему усмотрению. Однако, работая над реальными проблемами, я осознал важность SQL в решении сложных вопросов, и одна из стратегий, которая мне пригодилась, заключалась в том, чтобы разделять и властвовать.
Есть несколько способов, которыми мы можем использовать эту стратегию в наших интересах. В этом блоге мы рассмотрим две из таких практик:
- Общие табличные выражения (CTE)
- Временные таблицы
Эти объекты полезны несколькими способами, как описано ниже:
- Вместо того, чтобы решать всю проблему сразу, что может быть сложно, мы разбиваем сложный запрос на небольшие фрагменты, которые легко решить. Этого можно легко добиться в SQL, используя CTE и временные таблицы.
- Задача специалиста по обработке и анализу данных состоит не только в том, чтобы проводить эксперименты и проверять гипотезы, но и в том, чтобы иметь возможность реализовать модель машинного обучения. Это означает, что мы должны иметь возможность легко разрабатывать и поддерживать код. Сопровождение сценариев может быть проще, если код удобен для чтения. Как и в случае с CTE и временными таблицами, мы разбиваем сложный запрос на небольшие более удобные для чтения запросы; сценарии просты в обслуживании и, что более важно, сотрудничают и передаются другим специалистам по данным.
Теперь, когда мы понимаем важность стратегии, давайте рассмотрим каждый объект более подробно.
Общие табличные выражения (CTE)
Обычное табличное выражение — один из самых удобных объектов SQL, которые мне нравится использовать. CTE — это временный набор результатов, который мы можем создать в SQL для простоты использования. Это способствует решению следующих проблем:
Например, ниже мы можем увидеть образец CTE для одной из задач HackerRank.
- Напишите запрос, чтобы вывести hacker_id, имя и общее количество задач, созданных каждым учеником. Отсортируйте результаты по общему количеству задач в порядке убывания.
- Если более одного ученика создали одинаковое количество задач, отсортируйте результат по hacker_id.
- Если более одного учащегося создали одинаковое количество задач, а их количество меньше максимального количества созданных задач, то эти учащиеся исключаются из результатов.
В приведенном выше SQL мы создаем 2 CTE следующим образом.
- Во-первых, мы используем STUDENT_CHALLENGE, чтобы получить количество испытаний на одного хакера.
- Затем мы создаем CHALLENGE_COUNT_ONE, который использует предыдущий CTE для подсчета задач, поскольку нам нужно удалить учащихся с таким же количеством задач. Мы используем результат в условии фильтра ближе к концу запроса.
Как мы видим, мы можем использовать CTE для пошагового построения сложных запросов. Это помогает сделать код модульным и удобным для чтения.
Прежде чем мы сможем построить наши CTE, мы должны понять правила их определения. Вот список правил для общих табличных выражений на сервере SQL.
Временные таблицы
Еще один способ решить сложную проблему с подходом «разделяй и властвуй» — использовать временные таблицы. Как следует из названия, временные таблицы — это физические таблицы, созданные в базе данных, но с ограниченным жизненным циклом. Для проблемы HackerRank выше мы можем решить ту же проблему следующим образом:
Из приведенного выше SQL мы можем заметить, что скрипт теперь разделен на три небольших запроса, каждый компонент которых легко понять.
Одна важная вещь, которую нужно знать при использовании временных таблиц, — это их достоверность. Видимость временных таблиц зависит от их вида следующим образом:
- Временная таблица, созданная внутри хранимой процедуры, видна только до тех пор, пока эта хранимая процедура выполняется.
- Локальные временные таблицы видны в текущем сеансе и автоматически удаляются в конце.
- С другой стороны, глобальные временные таблицы видны всем активным сеансам. Они автоматически удаляются, когда завершается создавший их сеанс, и все остальные сеансы, ранее использовавшие временную таблицу, перестают использовать таблицы.
Чтобы узнать больше о временных таблицах, ознакомьтесь со следующим документом.
CTE против временной таблицы
Теперь, когда мы изучили CTE и временные таблицы, давайте обсудим, когда использовать одно вместо другого. Эмпирическое правило, которое я использую при выборе типа объекта, заключается в следующем:
Случай 1. Обычно общие табличные выражения работают лучше, чем временные таблицы. Например, для приведенных выше примеров среднее время выполнения запросов составляет 158 мс для CTE по сравнению с 173,2 мс при использовании временной таблицы.
- Мы оценили CTE и временные таблицы в одном и том же наборе данных, чтобы получить достоверную оценку производительности.
- Кроме того, мы усредняем время выполнения для десяти запусков, чтобы удалить шум.
- Наконец, мы очищаем кеш после каждого запуска, чтобы получить точную оценку производительности.
- Из приведенных выше запросов также следует отметить, что там, где CTE требует чтения IOPS, временные таблицы являются физическими таблицами, хранящимися в базе данных, им требуется как чтение, так и запись IOPS.
Случай 2. Хотя CTE, как правило, работают лучше на базовом уровне, у временных таблиц тоже есть преимущества. Обычно промежуточные наборы результатов могут иметь большое значение при сложных запросах к большим наборам данных. Более того, несколько таких промежуточных наборов часто необходимо соединить друг с другом для получения желаемых результатов. При наличии таких сложных объединений разделение набора данных становится критически важным для оптимизации производительности.
Поскольку CTE не являются физическими таблицами, их нельзя секционировать. Следовательно, в таких случаях временные таблицы работают лучше благодаря секционированию.
Случай 3. При создании набора функций для задачи машинного обучения или конвейеров для проекта по обработке данных чаще всего нам приходится повторять часть запросов несколько раз. В таких случаях становится оптимальным получить результаты базового запроса один раз и использовать результат в сочетании с другими данными, а не запускать один и тот же запрос несколько раз.
Поскольку область CTE находится в пределах одного выполнения инструкции SELECT, INSERT, UPDATE, DELETE или MERGE, запросы необходимо писать и выполнять снова, если это требуется для другой функции или части конвейера данных. Однако с временными таблицами область действия связана с текущим сеансом и может быть расширена на всю базу данных в зависимости от типа временной таблицы.
Заключение
Вообще говоря, разработчики/специалисты по данным могут писать CTE и временные таблицы вместе для достижения оптимальной производительности. Оба объекта имеют свои преимущества и становятся мощными при одновременном использовании. Кроме того, обе конструкции просты в сопровождении и делают запросы удобными для чтения.
В этом блоге мы рассмотрели использование CTE и временных таблиц, чтобы разделить проблему на подзадачи и решить их по отдельности. В следующем блоге мы рассмотрим способы оптимизации производительности запросов с помощью таких методов, как секционирование и индексирование.
Читайте другие похожие блоги
Узнать больше о Data Joey
Облачная платформа данных нового поколения, которая развертывается непосредственно в вашей облачной учетной записи.
