Data analysis - DA SQL

Получившиеся визуализации из предыдущего поста

Matplotlib в Python Довольно популярная библиотека, с помощью которой проводят визуализацию данных. Очень часто используется в связке с Pandas, SciPy и Numpy. Не очень сложна в освоении, требует от себя по большей части только лишь знания основных команд: ▶️Plot - вводные данные для графика, а именно: - X: Данные для оси X - y: Данные для оси Y. - fmt: Строка формата, которая определяет стиль линии и маркеров - label: Метка для легенды (Legend) ▶️Hist - используется для построения гистограмм, которые показывают распределение данных. Параметры: - x: Данные для построения гистограммы. - bins: Количество интервалов (бинов) или их границы. - color: Цвет столбцов. - alpha: Прозрачность столбцов. - edgecolor: Цвет границ столбцов. - density: Если True, гистограмма нормализуется (площадь под гистограммой равна 1). ▶️Bar - используется для построения столбчатых диаграмм. Параметры: - x: Категории (метки по оси X). - height: Высота столбцов (значения по оси Y). - width: Ширина столбцов. - color: Цвет столбцов. - edgecolor: Цвет границ столбцов. - label: Метка для легенды. ▶️Pie - используется для создания круговых диаграмм. Параметры: - x: Массив значений, которые определяют размер каждой части (доли). - labels: Массив строк, которые задают метки для каждой части. - colors: Массив цветов для каждой части. - autopct: Формат отображения процентов на диаграмме. - explode: Массив, который "выделяет" части диаграммы, отодвигая их от центра. - shadow: Добавляет тень под диаграммой (если True). - startangle: Угол начала построения диаграммы (по умолчанию 0 градусов). ▶️Legend - отображение названия графика (рядом с ним) ▶️Xlabel - название оси X ▶️Ylabel - название оси У ▶️Title - общий заголовок визуализации ▶️Grid - сеточная разметка ▶️Show - вывод визуализации 🟠Примеры построения: Линейный график: import matplotlib.pyplot as plt import numpy as np # Данные x = np.linspace(0, 10, 100) # 100 равномерных точек от 0 до 10 y = np.sin(x) # синус от этих точек # Создание графика plt.plot(x, y, label='sin(x)') plt.xlabel('Ось X') plt.ylabel('Ось Y') plt.title('Пример линейного графика') plt.legend() plt.grid(True) plt.show() Диаграмма: import matplotlib.pyplot as plt # Данные categories = ['A', 'B', 'C', 'D'] values = [10, 20, 15, 25] # Создание графика plt.bar(categories, values, color='blue') plt.xlabel('Категории') plt.ylabel('Значения') plt.title('Пример столбчатой диаграммы') plt.show() Круговая диаграмма: import matplotlib.pyplot as plt # Данные labels = ['A', 'B', 'C', 'D'] sizes = [15, 30, 45, 10] # Процентные доли colors = ['gold', 'lightcoral', 'lightskyblue', 'lightgreen'] explode = (0, 0.1, 0, 0) # Выделяем вторую часть (B) # Создание круговой диаграммы plt.pie(sizes, explode=explode, labels=labels, colors=colors, autopct='%1.1f%%', shadow=True, startangle=140) # Добавление заголовка plt.title('Пример круговой диаграммы') # Отображение plt.show() Гистограмма: import matplotlib.pyplot as plt import numpy as np # Данные data = np.random.randn(1000) # Создание графика plt.hist(data, bins=30, color='green', alpha=0.7) plt.xlabel('Значения') plt.ylabel('Частота') plt.title('Пример гистограммы') plt.show()

Продуктовая информация в банковском хранилище данных Продолжение экскурса по банковским данным. По сравнению с клиентскими данными, данные по продуктам хранятся немного иначе. Так как продуктов в банке куда меньше по сравнению с клиентами (всего около нескольких сотен), а также новые продукты появляются намного реже, чем новые клиенты, то информация по продуктам хранится в таблицах-справочниках - таких полу-динамических таблицах, где просто содержится описание продукта с привязкой к product_id. Это делает продукты проще по структуре, так как у продуктов есть только один атрибут, который часто мелькает в хранилище данных в самых разных таблицах - продуктовый ID (по которому уже за дополнительной расшифровкой идут в справочник). 🟠Есть 2 наиболее важные вещи, на которые надо обращать внимание при работе с продуктами - это продуктовые связи и продуктовая специфика. ✅Продуктовые связи. В банках продукты связаны следующим образом (наиболее часто используемые связи): ▶️Клиент-продукт ▶️Счёт-продукт ▶️Договор-продукт ▶️Точка продаж-продукт Говоря в контексте Data Vault (а именно эта схема будет в банке), основную архитектуру продуктовых данных можно представить так: ▶️Продуктовый хаб (техническая информация, включающая product_id) ▶️Вышеупомянутые связи, упакованные в link-таблицы (1 или несколько, зависит от инженера) ▶️И сателлит, описывающий бизнес сущности продуктов (он же справочник) ✅Продуктовая специфика. Здесь же крайне важно понимать бизнесовый контекст того, как работает какой продукт. Например, мы хотим добыть какую-нибудь финансовую информацию по депозиту клиента-физ лица. Что мы знаем о депозите? У депозита есть ставка - идем в таблицу с договорами и смотрим на ставку. У депозита есть процентный расход со стороны банка и процентный доход со стороны клиента - идем в таблицу с проводками и смотрим на счета и размер расхода-дохода. У депозита есть остаток - идем в таблицу со счетами и смотрим на остаток. Так что при работе с продуктами рекомендую детально ознакомиться как и со структурой конкретно вашего банковского хранилища, так и с экономическим принципом работы того или иного продукта.

В дополнение к посту для общего понимания наглядные схемы "звездочка" (рис 1️⃣), "снежинка" (рис 2️⃣), data vault (рис 3️⃣). Нам, аналитикам, все эти схемы хоть и можно посмотреть для общего развития, но важно другое. Важно их понимать не с точки зрения того, как они выглядят графически. На практике рисовать такое, по большей части, и не нужно (разве что можно сделать какой-нибудь текстовый справочник с описанием таблиц и связей): ▶️если БД простая, то она будет интуитивно понятна и без всяких схем; ▶️если БД сложная (как реальный data vault, который схематично мог бы выглядеть куда сложнее этого теоретического примера), то её графическая схема была бы просто нечитабельной. Эти схемы важно понимать с точки зрения характера таблиц, которые в них содержатся: гораздо приятнее понимать, что перед тобой, например, таблица связей из Data Vault, чем этого не знать и тратить время на поиск той инфы в ней, которая не может содержаться там даже теоретически.

Про схемы DWH/БД Ранее я рассказывал про основы проектирования БД. Если вкратце: есть магазин, и есть у него БД. У этой БД 2 типа таблиц: таблица фактов (сколько, когда, кем и кому было продано), и таблица измерений (описательная: что за клиент/продавец/товар скрывается за каждым ID). 🟠Вышеописанная схема называется звездочка. Самая примитивная схема. Сейчас, как мне кажется, ее можно упоминать только в контексте раскрытия основных принципов проектирования, либо использовать в очень простых проектах. Серьезные БД/DWH на ней не строятся. 🟠Есть еще схема снежинка - более сложная звездочка. Точно так же есть факты и измерения, но измерения более сложны. Если в звездочке мы можем по prod_id из фактов перейти в измерение и посмотреть, что это за prod_id (описание товара), то в снежинке мы можем продолжить этот путь по, условно, category_id и посмотреть описание товарной категории в отдельной таблице 🟠И есть Data Vault. Наверное, можно сказать, что она сложнее снежинки. С ней уже можно столкнуться на практике. Например, в банках. Принцип таков. Есть таблицы: хаб, сателлит и связь. В хабе хранится только техническая информация (ID, источник, дата загрузки...). В сателлите хранится описание (прямо как в звезде или в снежинке): что за товар или клиент скрывается за конкретным ID. В связи хранится информация о том, какой ID к какому ID можно соотнести. То есть. Есть у нас клиенты. Есть у клиентов договора. Клиенты могут заключить 1 или больше договоров. И допустим, что 1 договор могут заключить 1 или более клиентов (какой-нибудь коллективный договор). В таблице связей как раз хранится информация о том, у каких клиентов какие договора имеются. Сделаны эти связующие таблицы для реализации принципа связи "многие ко многим".

Партиционирование в SQL Для начала, что такое партиционирование? Партиционирование – это разделение одной (часто большой) таблицы на разные части, которые сохраняются отдельно. При этом, с точки зрения логики, все эти части являются составными для "главной" таблицы. Например, есть у нас таблица с транзакциями с 2020 по 2025 год. Такую таблицу можно разбить на 6 разных частей: по одной за каждый год + одну за нынешний 2025. 🟠Зачем это нужно? ▶️Во-первых, ради производительности. Бывают ситуации, когда нам нужно узнать, какие были транзакции, условно, в 2022 году. И чтобы не нагружать запрос построчным чтением из огромной таблицы, можно обратиться к ее части в 5 раз меньшей по объему. ▶️Во-вторых, ради управления/организации данных. Из примера очевидно, что 2020-2024 года - архивные. Значит, всю текущую информацию мы будем складывать уже в секцию с 2025 годом. Также можно работать с архивными данными без опаски "навредить" текущим, и наоборот. ▶️В-третьих, можно вообще хранить эти части на разных серверах, тем самым более эффективно используя ресурсы. 🟠Какое партиционирование бывает и как оно пишется на SQL? Партиционирование делится на 2 большие категории: горизонтальное и вертикальное. ✅ГОРИЗОНТАЛЬНОЕ ПАРТИЦИОНИРОВАНИЕ Данные разделяются по строкам. Пример выше как раз относится к горизонтальному партиционированию, когда данные могут быть разделены по дате. Горизонтальное партиционирование бывает: ▶️По диапазону (Range). Данные разделяются на основе диапазона значений в определенном столбце. CREATE TABLE users ( user_id INT, name VARCHAR(100), age INT, email VARCHAR(100) ) PARTITION BY RANGE (age) ( PARTITION p0 VALUES LESS THAN (18), -- младше 18 лет PARTITION p1 VALUES LESS THAN (30), -- от 18 до 29 лет PARTITION p2 VALUES LESS THAN (50), -- от 30 до 49 лет PARTITION p3 VALUES LESS THAN (100), -- от 50 до 99 лет ) ▶️По списку (List). Данные разделяются на основе конкретных значений в столбце. CREATE TABLE users ( user_id INT, name VARCHAR(100), region VARCHAR(50) ) PARTITION BY LIST (region) ( PARTITION p_north VALUES IN ('Север'), PARTITION p_south VALUES IN ('Юг'), PARTITION p_east VALUES IN ('Восток'), PARTITION p_west VALUES IN ('Запад') ) ▶️По хэшу (Hash). Данные распределяются по партициям на основе хэша. То есть: есть user_id. Если его прописать в PARTITION, а потом указать кол-во партиций, то таблица будет равномерно разделена на указанное число частей: CREATE TABLE users ( user_id INT, name VARCHAR(100), age INT ) PARTITION BY HASH (user_id) PARTITIONS 4 🟠Важное отступление. Вы, наверное, заметили, что при всех этих видах партиционирования мы только лишь создаем пустую таблицу и дальше пишем разные PARTITION BY. Откуда берутся данные? Чтобы партиционирование заработало, нужно вставить данные в таблицу с прописанным PARTITION BY, например, при помощи INSERT. Данные автоматически распределятся, и нужно будет только выбрать их: SELECT * FROM sales PARTITION (p2) ✅ВЕРТИКАЛЬНОЕ ПАРТИЦИОНИРОВАНИЕ Данные разделяются по столбцам. Например, редко используемые столбцы могут быть вынесены в отдельную таблицу. Оно осуществляется немного по-другому. Как пример: -- Всё вместе CREATE TABLE products ( product_id INT PRIMARY KEY, name VARCHAR(100), price DECIMAL(10, 2), description TEXT, category VARCHAR(50), created_at DATETIME, updated_at DATETIME ) -- Разделение CREATE TABLE products_main ( product_id INT PRIMARY KEY, name VARCHAR(100), price DECIMAL(10, 2), category VARCHAR(50) ) CREATE TABLE products_additional ( product_id INT PRIMARY KEY, description TEXT, created_at DATETIME, updated_at DATETIME, FOREIGN KEY (product_id) REFERENCES products_main(product_id) ) Важно при разделении столбцов прописать внешние и первичные ключи, по которым в будущем можно будет связывать эти таблицы при необходимости при помощи join.

Из практики. Делал сегодня тестовые задания на Senior аналитика в один крупный банк. Ограничение по времени - 1 час. Кому по силам выполнить всё за 60 минут?

Обзор Apache Hive Теперь после того, как был разобран Hadoop, можно перейти к разбору Apache Hive, т.к. вещи это взаимосвязанные - Apache Hive работает на базе Hadoop. Apache Hive — это система управления базами данных (СУБД). Она предоставляет SQL-подобный интерфейс, называемый HiveQL, который позволяет пользователям работать с данными, которые хранятся в Hadoop. Само собой, при этом сохраняются все принципы: данные хранятся в HDFS и обрабатываются с помощью MapReduce. 🟠Что происходит, когда пользователь запускает SQL-скрипт в Hive? Он "трансформируется" в своего рода задание для MapReduce (и не только). MapReduce, в свою очередь, в соответствии с полученным заданием, обрабатывает данные, которые хранятся на серверах в Hadoop. Ну и в конце всего этого пользователь получает результат. 🟠Некоторые отличия HiveQL от SQL (помимо уже вышеупомянутых): ▶️HiveQL поддерживает стандартные типы данных (как в SQL), а также сложные типы: массивы (ARRAY) - список значений одного типа; структуры (STRUCT) - группа полей разных типов; ассоциативные массивы (MAP) - пары ключ-значение; ▶️В Hive более урезанная поддержка ACID-транзакций, а также хуже и более "костыльно" работают UPDATE и DELETE. ▶️Т.к. Hive оптимизирован для пакетной обработки данных, запросы могут выполняться медленнее. ▶️Hive поддерживает множество форматов файлов, включая CSV, JSON, ORC, Parquet и т.д. Это позволяет работать с полуструктурированными и неструктурированными данными. Помимо этого, такие форматы, как ORC и Parquet, обеспечивают сжатие данных и ускорение выполнения запросов. ▶️HiveQL поддерживает особые функции: можно написать код, например, на Java или Python, и интегрировать его в SQL-скрипт.

Обзор Hadoop Hadoop — это платформа, предназначенная для хранения и обработки больших объемов данных. Hadoop основан на двух ключевых компонентах: HDFS (Hadoop Distributed File System) для хранения данных и MapReduce для их обработки. ▶️Принцип работы HDFS: HDFS — это распределенная файловая система, которая позволяет хранить огромные объемы данных на множестве серверов. Сначала данные поступают в систему. Потом данные разделяются. Например, файл на 1 ТБ может быть разбит на много блоков по 128 МБ. Каждый блок хранится на отдельном сервере. При этом каждый блок данных копируется на несколько серверов. Это нужно для надежности: если один сервер выйдет из строя, данные можно будет восстановить из других копий. В Hadoop также еще есть и NameNode — это "главный сервер", который знает, где находятся все блоки данных. Он хранит метаданные (информацию о файлах и их расположении). DataNode — это, в свою очередь, серверы, которые хранят фактические данные (блоки файлов). ▶️Принцип работы MapReduce: MapReduce — это способ обработки больших объемов данных (многими считается устаревшим) на множестве серверов. Он делит задачу на две части: Map и Reduce. Например, мы написали примитивный запрос к данным в Hadoop: хотим посчитать, сколько покупок сделал клиент в нашем магазине за всё время. Сначала считаются данные в каждом из серверов: условно, один сервер хранит информацию о 5 покупках клиента, другой - о 3 покупках, третий - о 10 и т.д. Затем данные суммируются со всех серверов и даётся результат: 5 + 3 + 10 = 18. Стоит еще упомянуть, что данные при MapReduce обрабатываются при помощи так называемой пакетной обработки - то есть параллельно. В теории, данные из одного 128 МБ сервера обработаются примерно за то же время, за сколько и данные из тысячи 128 МБ серверов. 🟠С интерфейсом платформы нельзя взаимодействовать напрямую привычным нам, аналитикам, образом, как, например, в ClickHouse через "ClickHouse-SQL" (про что писал ранее в постах). По сути это просто хранилище, набор серверов, где просто хранятся данные и вычисляются с помощью MapReduce. Для работы с ними через, условно, SQL, требуются отдельные сервисы в экосистеме Apache (например, Hive со своим диалектом SQL). 🟠Зачем нужен Hadoop? В каких случаях стоит отказаться от обычных реляционных баз данных? 1️⃣ Большие объемы данных, с которыми не справятся обычные реляционки (не только с точки зрения хранения, но еще и с точки зрения обработки методом MapReduce). Верно и обратное: если данных мало (например, 500 ГБ), эффективнее тогда будет использовать обычную SQL СУБД. 2️⃣ Hadoop может работать со структурированными, полуструктурированными и неструктурированными данными. Но если данные только структурированные, то SQL СУБД будет лучше. 3️⃣ Если нужна низкая задержка между отправкой и фиксацией данных в БД, а также критически важна точность транзакций, то тут лучше использовать SQL СУБД. 🟠Почему тогда не использовать упомянутый выше ClickHouse? Ведь оба решения предназначены для биг дата, но при этом ClickHouse еще и быстрее. Дело в том, что у них немного разные цели. Основное различие между ними в том, что ClickHouse больше подходит для быстрой аналитики больших структурированных данных. Hadoop выступает больше как место хранения еще бóльших данных (причем не только структурированных, как было сказано ранее) + Hadoop предварительно обрабатывает данные, тогда как ClickHouse уже использует всё готовое. Исходя из всего этого, Hadoop лучше справляется с ETL задачами. Вдобавок, в рамках ETL можно эффективно использовать разные инструменты из экосистемы: Apache Spark, Apache Hive, Apache Pig... Ну и еще Hadoop из-за своей "многосерверной" архитектуры больше ориентирован на масштабируемость, чем ClickHouse.