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

Все SQL-команды можно разделить на такие группы:

DQL (Data Query Language). Язык запросов к данным. К этой группе относится одна, всем известная команда - SELECT. Благодаря ей, мы запрашиваем нужные нам данные из базы.

DDL (Data Definition Language). Язык определения данных. Благодаря командам из этой группы, мы создаём, изменяем и удаляем различные объекты в нашей базе данных - базы данных, схемы, таблицы.
Примеры команд: CREATE, DROP, ALTER, TRUNCATE.

DML (Data Manipulation Language). Язык манипуляции данных. Как понятно из названия, мы используем эту группу команд для внесения изменений в наших данных. Мы можем записывать данные в таблицы, изменять значения в нужных колонках, удалять строки в таблицах.
Примеры команд: INSERT, UPDATE, DELETE.

DCL (Data Control Language). Язык контроля данных. Команды из этой группы используются для управления доступом к объектам в базе данных и к самим данным. Благодаря этим командам мы можем предоставлять и отзывать разрешения для пользователей базы данных.
Примеры команд: GRANT, REVOKE, DENY.

TCL (Transaction Control Language). Язык управления транзакциями (более подробно о транзакциях напишу отдельный пост в рубрике о теории баз данных).
Примеры команд: COMMIT, ROLLBACK, SAVE POINT.

Завтра выступлю на митапе и расскажу про построение сквозной аналитики на базе Google Cloud. Я уже выступал с этой темой, но в этот раз немного расширил контент. Так что приглашаю всех:)

Всем привет!

В этот вторник ждем всех на митап

🔥 Доклад про построение маркетинговой аналитики на GCP

@MLinMarketing

🔸19:00 - 20:00

🎤 Денис Соловьев, Data Engineer at Ciklum

📋 Построение маркетинговой аналитики на базе Google Cloud

📋 Рассмотрим преимущества создания маркетинговой аналитики с использованием облачных технологий. Поговорим о том, на что стоит обратить внимание перед построением решения. Посмотрим на примеры аналитических архитектур для решения маркетинговых задач. Разберём организацию DWH на базе Google BigQuery. Затронем BigQuery ML и какие задачи можно решать с его помощью.

— — —

🗓 05 октября, начало в 19:00 мск, Вторник

🌐 ОНЛАЙН

Регистрация на мероприятие тут

Добавляйте в календарь, ссылка придет на почту перед началом митапа

Ещё одна универсальная шпаргалка про порядок выполнения SQL-операций.

Логический порядок выполнения операторов "под капотом":

1. FROM (выбор таблицы)
2. JOIN (комбинация с подходящими по условию данными из других таблиц)
3. WHERE (фильтрация строк)
4. GROUP BY (агрегирование данных)
5. HAVING (фильтрация агрегированных данных)
6. SELECT (возврат результирующего датасета)
7. DISTINCT (выбор только уникальных значений)
8. ORDER BY (сортировка)


И статья с объяснением на примерах.

» Сегодня смотрим доклад про построение маркетинговой аналитика

Спикеры: Денис Соловьев, Data Engineer at Ciklum

Начинаем через 20 минут. Ссылочка на вход у всех на почте.
Регистрация тут

Подключайтесь!

🧞‍♂️ ВИДЕО с ML in Marketing Meetup
@MLinMarketing

📋 Тема: Построение маркетинговой аналитики на базе Google Cloud

📋 Описание: Рассмотрим преимущества создания маркетинговой аналитики с использованием облачных технологий. Поговорим о том, на что стоит обратить внимание перед построением решения. Посмотрим на примеры аналитических архитектур для решения маркетинговых задач. Разберём организацию DWH на базе Google BigQuery. Затронем BigQuery ML и какие задачи можно решать с его помощью.

🎤 Спикер: Денис Соловьев, Data Engineer at Ciklum
- автор телеграм канала про аналитику @smart_data_channel

🎬 Видео

📥 Презентация

— — —
Кстати, вопросы по докладу можно задавать тут же в комментариях ;)

⚡️Масштабное независимое исследование онлайн-курсов по аналитике ⚡️

Мы с моими коллегами из компании твердо решили узнать все-все самое важное об онлайн образовании по теме аналитики и data science. Об онлайн образовании говорят повсеместно, курсы чрезвычайно распространены, ведь профессии в IT-сфере сейчас очень популярны. Думаю, что огромная часть аудитории данного канала либо прошла, либо собирается пройти курсы, связанные с анализом данных.

Прошу вас пройти опрос и оставить ваше искреннее мнение о той школе, курс в которой вы прошли. Хорошее, плохое, главное, не безразличное!

Буду признателен коллегам владельцам каналов по аналитике за репост. Разумеется, результатами опроса мы вскоре с вами поделимся в виде симпатичного дашборда 🤓

➡️ Ссылка на опрос

p.s. Любые комменты по опросу тоже приветствуются

🧑‍🎓 Матрица компетенций BI-аналитика
Сделал матрицу компетенций, она родилась за год большой работы по менторству BI-аналитиков и «сериала» с Русланом. С радостью и гордостью хочу поделиться ей с комьюнити. Получилось круто.

Матрица будет полезна и новичкам — есть подсветка проседающих навыков и ссылки на учебные материалы. И компаниям — для составления планов развития сотрудников.

Необходимо оценить себя по 68 навыкам из 6 направлений, которые важны BI-аналитику на мой взгляд. Каждый навык имеет уровень «прокачки» от 1 до 4 и описание, с примером ожиданий знаний от уровня. Но это только пример, при сомнениях, оцените навык по ощущениям от «джун» до «лид».

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

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

🔗 Ссылка
#избранное

Всем привет. Давно не писал на канале, так что буду исправляться.

Мы продолжаем тему SQL и баз данных, и сегодня я хочу рассказать о 4-х типах объектов, которые встречаются в большинстве СУБД: base table (таблица), temporary table (временная таблица), view (представление), materialized view (материализованное представление).

Решил написать небольшую шпаргалку с описанием этих объектов и в каких случаях какой объект использовать или не использовать.


Base table - классическая таблица. На логическом уровне это выделенное пространство на диске или в памяти, где хранятся данные, которые мы загрузили в эту таблицу.
Базовая таблица является основой для создания других объектов в базе данных.


Temporary table - временная таблица, которая, как правило, существует в рамках одной сессии подключения к базе данных. Простыми словами, если вы подключились к базе данных, создали временную таблицу, использовали её для своих нужд и разорвали соединение, то в рамках следующей сессии этой таблицы уже не будет.

Когда использовать: временные таблицы могут быть полезными, если вы часто обращаетесь к данным большой базовой таблицы (base table) в рамках одной сессии. Это, в свою очередь, может замедлять скорость работы и потреблять большое количество вычислительных ресурсов, что сильно снижает эффективность. Поэтому, вы можете создать временную таблицу на базе основной для хранения промежуточных результатов и писать запросы уже к ней, что может сильно повысить производительность.

Когда не использовать: временные таблицы лучше не использовать для сложной логики запросов и вычисляемых полей. Для этих задач больше подходят views (представления).


View - по сути, это хранимый SQL-запрос, который содержит в себе направления к данным, которые хранятся в основных таблицах. Т.е. представление хранит не данные сами по себе, а направление к ним.

Когда использовать:
- для хранения сложной логики запросов к основным таблицам. Например, для джоинов часто используемых таблиц, для вычисляемых столбцов и метрик. Представления устраняют избыточную работу для получения данных из базовых таблиц: вместо того, чтобы постоянно описывать ту же самую логику в запросах к основным таблицам, мы можем писать запросы ко view, которые уже содержат эту логику;
- для предоставления доступа только к определённым полям в основной таблице для групп пользователей. Представления являются хорошей опцией для того, чтобы обезопасить данные от нежелательного доступа. Например, вы хотите предоставить информацию о работниках, но не хотите предоставлять информацию об их зарплате.

Когда не использовать: со временем базовые таблицы могут становиться всё больше, и сложный запрос во view становится всё медленее, что делает анализ данных очень долгим и неудобным. Чтобы устранить просадку в производительности, мы можем материализовать результаты запроса, который хранится в представлении. Для этой задачи и существуют materialized views.

Materialized views - специальный тип представлений, который так же, как и стандартное представление является хранимым запросом. Но в отличие от стандартного представления материализованное хранит уже данные, а не просто направление к ним. Т.е. результат запроса материализуется и становится похожим на обычную таблицу, к которой мы можем писать запросы. Процесс материализации и обновления представления происходит в заданный интервал времени на основе изменений в базовых таблицах, к которым это представление обращается.

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

Когда не использовать: нужно понимать, что любая материализация данных требует дополнительных ресурсов хранения, поэтому не нужно, например, создавать материализованные представления, чтобы повысить скорость выполнения запроса по сравнению со стандартным представлением, если использование стандартного представления вполне приемлемо и не снижает вашу эффективность.
Также нужно отталкиваться от особенностей конкретной СУБД. Например, в Snowflake обновление
материализованного представления, является автоматическим процессом, который требует дополнительных расходов. Поэтому, чем чаще изменяется базовая таблица, тем чаще происходит автоматическое обновление представления и тем больше расходов вы понесёте на их поддержку. Поэтому, ко всему нужно подходить с умом.


В следующий раз сделаю похожий пост про партиции и индексы.

Сегодня хочу рассказать про партиционирование и индексы в базах данных.

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

Когда использовать:
- для более гибкого использования физического хранилища данных. Например, мы можем хранить отдельные партиции на разных серверах и для хранения редко запрашиваемых данных использовать более дешёвую технологию хранения на отдельном сервере;
- для гибкого управления данных в таблицах. Мы можем быстро добавлять и удалять данные на уровне партиций;
- для ускорения выполнения SQL-запросов к таблице. Партиционирование может существенно повысить скорость запросов, так как движок будет сканировать отдельные партиции, в которых лежат нужные для запроса данные, а не всю таблицу.
Вернёмся к нашему примеру с датой заказа: например, у нас есть таблица orders, и мы пишем запрос к ней, запрашивая только заказы за 2021-12-15:

SELECT * FROM orders WHERE order_date = '2021-12-15';

Так как наша таблица партиционирована по дате заказа, движок просканирует только одну партицию "2021-12-15", а не всю таблицу (в случае, если партиционирование не используется). Скорость выполнения такого запроса вырастет во много раз.

Когда не использовать: не нужно использовать партиционирование на небольших таблицах. Если говорить о традиционных реляционных СУБД, создание и поддержка партиций является не самой тривиальной задачей, и, если таблица небольшая, выгода от партиций будет намного меньше затрат на их создание и поддержку. Более того, на небольших таблицах партиционирование может даже снижать производительность запросов.

Индексы - на логическом* уровне это отсортированные ключи колонки таблицы (для которой и создаётся индекс), которые имеют указатели на местонахождение строк основной таблицы со значением колонки в индексе. Если сильно упростить, то индекс в базах данных очень похож на индекс в конце книг. Когда вы открываете индекс на последних страницах книги, у вас есть список ключевых слов, отсортированных от А до Я. Для каждого слова есть номера страниц, где это слово упоминается, что облегчает поиск. По такому же принципу работают индексы и в БД.
Важно сказать о том, что индексы в большей степени используются в традиционных реляционных СУБД (таких как PostgreSQL, MySQL, Microsoft SQL Server и т.д.). Современные колоночные решения, такие как Google BigQuery, Snowflake или Amazon Redshift используют специальный слой метаданных, который решает задачу повышения эффективности поиска и скорости выполнения запросов.

Когда использовать:
- на больших таблицах;
- когда увеличение скорости выполнения запросов за счёт индексов имеет больший профит, чем затраты на хранение и поддержку индексов;
- на часто используемых полях в фильтрах для часто используемых запросов.
Предположим, у нас есть большая таблица orders, и мы часто, запрашивая данные и з неё, фильтруем данные по полю segment (сегмент клиента, который совершил заказ). Например, мы пишем такой запрос:

SELECT * FROM orders WHERE segment = 'Corporate';

Мы можем создать индекс на столбец "segment", чтобы увеличить скорость выполнения запроса. Например, в PostgreSQL индекс можно создать так:

CREATE INDEX segment_idx on orders (segment);

Т.е. создавать индексы - быстро и просто.
Индексы могут быть также составными - включать несколько столбцов.

Когда не использовать:
- на небольших таблицах. Индексы требуют дополнительного пространства для их хранения. Поэтому, если скорость выполнения запросов без индекса вас вполне устраивает, то и не нужно использовать дополнительные ресурсы для хранения индексов;
- на колонках с большим количеством null-значений;
- на часто обновляющихся таблицах. Частые операции вставки и обновления полей таблиц повышают нагрузку на СУБД, так как значения нужно записать/обновить в двух местах - в основной таблице и в индексе. Если вы часто производите удаления в основной таблице, то индекс становится фрагментированным (т.е. включает в себя пустые "листы"), что приводит к его неэффективности;
- на колнках с низкой кардинальностью (низким количеством уникальных значений). Например, если ваша колонка, на которую вы создаёте индекс, включает в себя только True/False значения, создание индекса не принесёт вам много профита.

* Я не просто в определении индекса написал "на логическом уровне". Логический уровень - это абстракция, которая позволяет описать объект обобщённо и как можно проще без углубления в детали. Есть ещё физический уровень, который описывает, чем на самом деле "под капотом" являются индексы. На физическом уровне для создания индексов используются специальные структуры данных: как правило, это B+tree или hash-таблицы. О структурах данных я тоже напишу, но сейчас я хочу двигаться от логического уровня к физическому для упрощения восприятия.

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

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

Некоторые будут говорить, что основы Computer Science - это база, без которой никуда, а некоторые, что они не заканчивали Computer Science, но при этом успешно работают в индустрии.

Решил сделать опрос, чтобы посмотреть, в каком отношении распределяются мнения)

После опроса напишу своё видение на этот счёт.

Как я и ожидал, результаты опроса получились далеко неоднозначными. Около половины опрошенных считает, что нужно глубоко погружаться в теорию Computer Science, чтобы быть хорошим специалистом, и что без этой базы никуда. Другая половина либо считает по-другому, либо сомневается, либо имеет более развёрнутое мнение на этот счёт.

Спасибо всем за мнение и комментарии!


Теперь хочу выразить своё видение по этому поводу и написать несколько тезисов, к которым я пришёл на базе своего опыта, опыта коллег, результатов опроса и ваших комментариев.

- Уровень зарплаты зависит от меры влияния на бизнес. Т.е. не так важно, как много вы знаете и умеете с технической стороны, более важно - какую бизнес-ценность вы приносите. Вы можете хорошо знать теорию Computer Science, но плохо представлять конечную бизнес-задачу. А именно решение бизнес-задач приносит деньги компаниям и, соответственно, вам. Поэтому, я считаю, что знания Computer Science уходят на второй план по сравнению с навыком бизнес-ориентированности. Не забываем про soft-skills😉

- Понравился комментарий @dimoobraznii, что начинающему специалисту самое важное - побыстрее найти работу и получить опыт на реальных проектах. После того, как уже понял, как всё устроено и закрепился в индустрии - начинать учить что-то сложнее по типу алгоритмов, структур данных и внутренностей баз данных.
Со своей стороны, сделаю ещё несколько уточнений. Считаю, что сразу начать копаться в Computer Science - хороший вариант для студентов, которые после школы поступили на соответствующий факультет в ВУЗ. Если нет проблем с финансами и хочется спокойно закончить универ, то это вариант в 21-22 года с хардкорными фундаментальными знаниями прийти в IT и надолго здесь остаться.
Если же вы не студент Computer Science, хотите перейти в IT из другой сферы и вам нужно побыстрее найти работу и зарабатывать деньги - с углублением в Computer Science на первом этапе вы потратите очень много времени, за которое индустрия и требования в ней могут сильно поменяться.

- Знания Computer Science более ценны для on-premise решений и менее ценны в облаке. Как мы знаем, on-premise решение - это когда мы всё делаем самостоятельно: покупаем сервера, устанавливаем OS, строим физическую сеть, устанавливаем нужный софт, настраиваем репликацию, шардирование, мониторинг и т.д. Всё это требует больших усилий, и знания архитектуры ЭВМ, внутренностей баз данных, компьютерных сетей и распределённых систем могут очень помочь. В облаке, напротив, огромную часть задач с нас снимает облачный провайдер, и нам, преимущественно, достаточно знать особенности отдельных сервисов и как ими пользоваться.

Главный вывод: знать Computer Science круто и полезно, но учить его стоит тогда, когда вы уже умеете решать бизнес-задачи, на верхнем уровне понимаете, из каких компонентов состоит IT-решение (будь-то аналитическое или какое-то другое) и какие инструменты можно использовать.
Разберитесь, как строятся архитектуры для аналитических решений, научитесь хорошо писать SQL-запросы и оптимизировать их. Разберитесь, как строятся DWH и какие best-practices есть в ETL. Научитесь писать рабочий код. Поковыряйте какое-то облако (AWS, Azure, GCP, Yandex Cloud, что угодно). С этим всем можно решить большинство бизнес-задач.
А дальше можно углубляться в теорию и разбирать, как работает процессор:)

Жизненный цикл запросов и план выполнения запроса на примере PostgreSQL - Часть 1

Всем привет.

Давно не публиковал полезности на канале. Сегодня хочу немного рассказать о жизненном цикле запросов и плане их выполнения.

Если не сильно усложнять, то жизненный цикл запроса можно разделить на 3 основных этапа:

1) Парсинг запроса. На этом этапе парсер проверяет синтаксис запроса, компилирует SQL в более подходящий для компьютера вид на основе хранимых в системе правил и отправляет запрос к базе данных.

2) Оптимизация и построение плана выполнения запроса. На этом этапе формируются планы-кандидаты, для которых расчитывается стоимость (query cost). На основе стоимости планировщик выбирает оптимальный план для выполнения запроса.

3) Непосредственное выполнение запроса. На этом этапе запрос выполняется, согласно выбранному плану, и возвращает результат пользователю.


Переходим непосредственно к плану выполнения запроса.
Чтобы увидеть план выполнения запроса, в PostgreSQL существует команда EXPLAIN. Важно отметить, что EXPLAIN не выполняет запрос, а только показывает план его выполнения.
Для того, чтобы отобразить план выполнения запроса, достаточно просто указать EXPLAIN перед основным запросом. Например:

EXPLAIN 
SELECT * FROM retail.orders_fact;

Запрос вернёт нам результат следующего вида:

Seq Scan on orders_fact  (cost=0.00..195.86 rows=9186 width=55)

Как мы видим, наш план выполнения запроса состоит из 1 шага Seq Scan (Sequential Scanning), что означает последовательное сканирование всех строк таблицы. В скобках указаны различные оценки (estimates) выполнения запроса:

- первое число в cost обозначает время, которое проходит, прежде чем начнётся этап вывода данных;

- второе число в cost обозначает общую стоимость выполнения. Общая стоимость выполнения = время начала вывода данных + время окончания вывода данных;

- число в rows указывает на число строк, которое должен обработать запрос;

- число в width отображает количество байт, которое обработает запрос для вывода нужных строк.

Давайте теперь рассмотрим другой пример:

EXPLAIN 
SELECT * FROM retail.product_dim 
WHERE category IN ('Furniture', 'Technology');

Запрос вернёт нам такой результат:

Seq Scan on product_dim  (cost=0.00..58.14 rows=862 width=91) 
-> Filter: ((category)::text = ANY ('{Furniture,Technology}'::text[]))

Как мы видим, оператор WHERE добавил шаг Filter. Здесь важно сказать, что план выполнения запроса, как правило, читается снизу-вверх или справа-налево. В данном случае мы читаем наш план снизу вверх:

1) Первым выполняется Filter, который отбирает только те строки, где категория товара равна или Furniture, или Technology.

2) После фильтрации происходит последовательное сканирование таблицы (отфильтрованных строк).

Теперь давайте сделаем трюк и создадим индекс на столбец category:

CREATE INDEX category_idx on retail.product_dim (category);

И снова выполним запрос:

EXPLAIN 
SELECT * FROM retail.product_dim 
WHERE category IN ('Furniture', 'Technology');

Теперь этот же запрос выдаёт нам такой результат:

Index Scan using category_idx on product_dim  (cost=0.28..41.71 rows=862 width=91) 
   Index Cond: ((category)::text = ANY
   ('{Furniture,Technology}'::text[]))

Как мы видим, теперь вместо 2-х этапов в плане выполнения запроса только один - Index Scan (сканирование индекса).
Index Cond объясняет, почему происходит сканирование индекса, указывая, что в WHERE мы фильтруемся по полю, на которое и создали индекс.
Также можно заметить, что добавление индекса сократило общую стоимость выполнения запроса с 58.14 до 41.71. Здесь мы имеем наглядный пример того, как индексы могут оптимизировать скорость выполнения запроса. Но не забываем, что индексами нужно пользоваться с умом (об этом я писал здесь).

На сегодня всё. Ждите 2 часть🙂

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

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

Я не хочу говорить о том, чьи СМИ говорят правду, а чьи - врут. Если у людей уже сформировалась четкая позиция, то изменить ее крайне тяжело, и я не собираюсь бить себя в грудь и что-то доказывать.

Факт один: гибнут люди как с одной стороны, так и с другой. И это ужасно. От этого проиграют все...

Я знаю, что у меня на канале много русских, которые не безразличны к этому и не хотят войны.

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

Если же кто-то с этим не согласен, просто отпишитесь от меня навсегда.

Давайте остановим войну!

Ребята, меня переполняют негативные эмоции. Я стараюсь из-за всех сил, чтобы мой негатив не перерос в ненависть к русскому народу, так как знаю, что среди вас есть адекватные и умные ребята с развитым критическим мышлением.

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

Вся Украина и запад обозлился на Россию и всячески пытаются изолировать ее от остального мира, вводя санкции, которых до этого не видела ни одна страна.

Я знаю, что вы хотите жить в условиях глобализации бизнеса, расширять границы разума и развивать технологии. Поэтому ещё раз прошу: "Не молчите! Распространяйте информацию всеми возможными способами. Выходите на улицу!" Нужно поломать этот "зомбоящик".

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

Надеюсь, эта война скоро закончится, и я перестану писать о политике и буду писать только о технологиях🙏

На этом я закончу. Уже достаточно сказано. Если бы кто-то знал, насколько мне противно писать об этом, а не о том, для чего люди подписываются на меня. Но я хочу жить в стране, где царит мир и развитие, и я буду помогать этот мир защищать.