Data analysis - DA SQL

Cx_Oracle: как подружить Python и SQL Cx_Oracle - библиотека в питоне, которая позволяет работать с Oracle Database, предоставляет прямой доступ к базе данных и поддерживает PL/SQL. Взаимодействие с БД через питон может быть полезно во многих случаях. Например, можно автоматизировать периодическую выгрузку данных по типовым скриптам с последующим сохранением данных эксель. Или наоборот, загружать данные в БД из экселя в 2-3 клика, минуя множество формальностей через встроенные инструменты Oracle. Или же это может вообще упростить обмен данными между двумя разными СУБД. По сути можно придумать что угодно что можно автоматизировать/оптимизировать, фантазия тут безгранична 🟠Теперь к делу. Установка: pip install cx_Oracle 🟠Самая важная вещь после установки - подключиться к базе данных Oracle через Python. Для этого потребуются следующие данные: ▶️hostname ▶️port ▶️sid ▶️role ▶️user ▶️password Если они известны - хорошо. Если нет - можно их получить (всё кроме пароля) в JSON файле через кнопку "Export Connections" в самом SQL Developer. Скрипт для подключения выглядит следующим образом: import cx_Oracle dsn = cx_Oracle.makedsn("localhost", 1521, sid="xe") user = "SYS" password = "1234" mode = cx_Oracle.SYSDBA connection = cx_Oracle.connect( user=user, password=password, dsn=dsn, mode=mode) 🟠После этого необходимо создать курсор: cursor = connection.cursor() 🟠С его помощью потом будут выполняться SQL-запросы. Например, так будет выглядеть вставка данных в таблицу: cursor.execute(""" INSERT INTO users (user_id, name, AGE, email) VALUES (1, 'Иван', 30, 'адрес почты') """) connection.commit() А вот так выборка данных с последующим сохранением их в эксель: cursor.execute("select * from users") # Названия столбцов columns = [col[0] for col in cursor.description] # Данные data = cursor.fetchall() df = pd.DataFrame(data, columns=columns) output_file = r"ПУТЬ\output.xlsx" df.to_excel(output_file, index=False, engine="openpyxl") 🟠Ну и конечно же не забываем закрывать курсор и соединение после выполнения всех операций с БД: cursor.close() connection.close()

Python в Tableau Поговорим немного подробнее про ранее упомянутый Python в Tableau. 🟠Как уже было сказано ранее, один из способов подключить питон к этому инструменту - через TabPy. Один из способов подключения: Выполнить в командной строке: pip install tabpy Произойдет самая обычная установка через pip. Затем запуск TabPy через командную строку: tabpy TabPy запустится на localhost (по умолчанию порт 9004). Убедиться, что все работает, можно через Ссылка скрыта В самом Tableau это всё проверяется через меню: Help > Settings and Performance > Manage External Service Connection Server: localhost Port: 9004 🟠Перед тем, как писать на Python, надо подключиться к источнику данных в Tableau (например, Excel, CSV, база данных, Google Sheets и т.д.). После подключения можно создавать вычисляемые поля, фильтры, группировки и другие элементы на основе этих данных. При создании такого поля Tableau передает данные из него в Python-код. 🟠Теперь что касаемо самого питона. В общем виде скрипт в Tableau выглядит так: SCRIPT_REAL("код Python", аргументы) ▶️В Python надо прописывать return, т.к. код должен возвращать результат, который будет использован в Tableau. Результат должен быть списком (list), длина которого соответствует количеству строк в данных. ▶️После python-кода указываются аргументы. По сути, это те поля, которые берутся из источника данных Напомню также и про основные типы возвращаемых значений из скрипта: SCRIPT_REAL - числа с запятой SCRIPT_INT - целые числа SCRIPT_STR - строки SCRIPT_BOOL - логические значения 🟠Пара примеров. Допустим, есть данные о продажах, и их надо перевести из долларов в рубли и классифицировать на "Высокие" и "Низкие". Конвертируем: SCRIPT_REAL(" return [x * 80 for x in _arg1] ", SUM([Sales])) Классифицируем: SCRIPT_STR(" return ['High' if x > 1000 else 'Low' for x in _arg1] ", SUM([Sales])) После этого можно добавлять полученные поля в Tableau и работать с ними: например, визуализировать. Ну или что-то более сложное: SCRIPT_INT(" from sklearn.cluster import KMeans import numpy as np data = np.array(_arg1).reshape(-1, 1) kmeans = KMeans(n_clusters=3).fit(data) return kmeans.labels_.tolist() ", SUM([Sales])) Это - кластеризация данных с помощью метода KMeans из библиотеки scikit-learn. Сначала всё импортируем, далее передаем массив из источника в переменную и изменяем этот массив на двумерный массив (так того требует KMeans), потом вызываем KMeans из scikit-learn для 3 кластеров и после всего этого возвращаем список из кластеров через return. В итоге полученный список можно использовать в Tableau для визуализации.

Основное про Tableau Tableau — это довольно объемный и популярный инструмент для визуализации данных и бизнес-аналитики (BI). Он позволяет создавать интерактивные дашборды, отчеты и визуализации на основе данных из различных источников. 🟠Первое, что бросается в глаза - это его способность подключаться к огромному количеству различных источников данных для создания визуализаций. Он поддерживает такие источники, как: Excel, CSV, базы данных (Oracle, MySQL, PostgreSQL), облачные хранилища (Google BigQuery, Amazon Redshift, Snowflake), веб-API и другие. Подключение к разным источникам довольно простое и интуитивно понятное: к файлам подключение происходит просто через встроенное меню; для баз данных же надо знать название сервера, порт, название базы данных, юзернейм и пароль. 🟠Еще можно писать SQL запросы к БД через Tableau. Для этого, опять же, в интерфейсе существует Custom SQL - такая опция, которая открывает поле для написания SQL запроса. Выгрузку, которая получится из этого скрипта, естественно, можно визуализировать. 🟠Также стоит упомянуть, что Tableau совместим с Python. Интеграция происходит через TabPy. Очень полезно, когда нужна визуализация чего-то, что нужно рассчитать: прогнозирование, кластеризация, стат анализ и т.п. Перед написанием самого скрипта нужно заранее прописать в Tableau, что этот скрипт будет возвращать: SCRIPT_REAL - числа с запятой SCRIPT_INT - целые числа SCRIPT_STR - строки SCRIPT_BOOL - логические значения Подробнее про Python в Tableau напишу чуть позднее. 🟠Также в Tableau существует два основных режима работы с данными: Live (прямое подключение) и Extract (извлечение данных). В чем разница? ▶️Основной принцип Live состоит в том, что в этом режиме Tableau подключается к источнику данных и выполняет запросы к нему в реальном времени. Данные не сохраняются в Tableau, а извлекаются непосредственно из источника при каждом взаимодействии с визуализацией. Это полезно, если данные часто обновляются, либо данные слишком большие для извлечения, либо не предоставляется возможным выделить место для их хранения и т.п. Но при этом источник данных должен быть всегда доступен. ▶️Принцип Extract в том, что в этом режиме Tableau извлекает данные из источника и сохраняет их в локальном файле (формат .hyper). Этот файл хранится на вашем компьютере или на Tableau Server. Полезно, если сам источник данных работает медленно или вообще недоступен - тогда из локальной копии данные можно быстрее извлечь; либо если бывают ситуации, когда данные нужно предварительно обработать перед визуализацией. Из недостатков: нужно место для хранения файла; данные могут стать неактуальными; размер выборки в целом меньше, чем в Live. 🟠С помощью Tableau можно строить столбчатые диаграммы, линейные графики, круговые диаграммы, карты, таблицы... Но по большей части он полезен для создания дашбордов - интерактивных страниц, на которых объединены несколько визуализаций. Также удобной фишкой является то, что дашбордами можно делиться и использовать их совместно. Одна из проблем, с которой вы, скорее всего, будете сталкиваться - оптимизация дашбордов. Что делать, чтобы они не тормозили? ▶️В случае, если данные обновляются нечасто, лучше будет использовать режим Extract ▶️По возможности выборку надо делать как можно меньше (как и с уменьшением самих данных, так и с помощью агрегации) ▶️Еще поможет упрощение самой визуализации: чем меньше элементов и чем более подходящий формат (например, не надо использовать графики там, где нужна гистограмма) - тем лучше ▶️Немаловажным будет еще и оптимизация SQL-запросов (если источник данных - БД). Чем проще - тем лучше. Промежуточные вычисления также можно кэшировать с помощью Extract.

Счета бухгалтерского учета, баланс и финансовые результаты в банковском DWH Сегодня речь пойдет по большей части не про корпоративные финансы, а про способы организации такой информации в банковском хранилище данных. Если кратко, то с финансовой точки зрения основная логика такая: 🟠Баланс: Активы (разного рода по типу денежных средств, ценных бумаг, кредитов и т.п.) = Обязательства (например средства клиентов, выпущенные облигации, налоговые обязательства и т.п.) + Капитал (акционерный капитал, нераспределенная прибыль и т.п.) 🟠Финансовый результат: Всякого рода доходы минус всякого рода расходы (процентные, операционные, комиссионные + прибыль/убыток по прочей деятельности, если есть (например, страхование), + прибыль/убыток по операциям с фин. инструментами и так далее) 🟠Под всё вышеперечисленное существует план счетов бухгалтерского учета: каждая статья из баланса и фин реза имеет свой счет в бухгалтерии и свою логику формирования. Теперь о том, как это всё организовано в хранилище данных. ▶️Первое, о чем надо знать - характер информации в отчетности разный. Если с точки зрения баланса нам важны остатки на дату, то с точки зрения фин реза нам важны сами операции за период. ▶️Второе - это то, что информация поступает в хранилище из первоисточников в виде транзакций. То есть она представляет из себя следующее: кто-то кому-то перевел тысячу рублей, другой кто-то эту тысячу получил, третьему заплатили комиссию. По факту это (условно говоря) 3 разные операции, 3 разные строки в таблицах, и 3 разных счета согласно плану счетов. Это что касательно фин реза. Касательно баланса логика примерно та же, только нужно помнить, что смотрим мы в конечном итоге на остатки на дату (ну или на средние остатки, которые считаются по специальной интересной формуле) ▶️Третье - это то, что вся такая информация (транзакция + счёт) в основном хранится в таблице бухгалтерских проводок - как раз там, где сводится дебет с кредитом. В контексте схемы DWH это - классическая таблица фактов. Если же посмотреть на это с точки зрения Data Vault - то это некий симбиоз из Link-таблицы и сателлита, поскольку для полноценной работы с проводками нужна будет информация из обоих таблиц. Но скорее всего, учитывая это, под эти цели будет сделана специальная витрина данных, в которой будет отражена вся нужная информация. ▶️Четвертое - что это всё нужно консолидировать или разбивать в зависимости от потребностей (например, для получения отчетности нам нужна консолидация). Также можно узнать много разной финансовой информации, не собирая отчетность полностью, а работая отдельно с нужными счетами. ▶️Ну и в заключение. Есть свои нюансы при консолидации таких данных из бухгалтерских счетов в статьи отчетности: это учет и валютной переоценки, и дочерних компаний/филиалов, и транзакции, которые отобразились задним числом, и ручные корректировки (они встречаются сплошь и рядом), и стандарты учета МСФО или РСБУ и еще много всякого.

Обзор Apache Impala Apache Impala представляет собой платформу для написания SQL запросов к Hadoop. По сути, это аналог Hive, но с некоторыми отличиями. Так как я ранее рассказывал про Hive (и про Hadoop в том числе), выделю главные сходства и отличия Impala от Hive. ✅Сходства ▶️И Impala, и Hive - SQL-платформы для написания запросов к системе хранения больших данных Hadoop. ▶️Обе платформы - части экосистемы Apache (как и Hadoop), что позволяет им легко взаимодействовать в рамках экосистемы и поддерживать одни и те же форматы данных ▶️Поддерживают интеграцию с BI-инструментами ⛔️Различия ▶️Impala использует стандартный SQL, тогда как Hive - специальный диалект SQL - HiveQL. ▶️Impala используется в основном для чтения данных, тогда как Hive - для записи и обновления данных. ▶️Impala, в отличие от Hive, пропускает MapReduce и берет данные напрямую из HDFS, что делает выполнение запросов быстрее. ▶️Impala оптимизирована для выполнения запросов в реальном времени, тогда как Hive лучше подходит для пакетной обработки данных. ⚡️Подводя итоги, можно сказать, что Impala используется в тех случаях, когда нам нужно что-то проанализировать, прочитать, построить отчеты, визуализировать... Hive используется тогда, когда с данными нужно взаимодействовать не на уровне чтения, а именно: извлекать, преобразовывать, загружать (иными словами, для ETL-процессов)

CURSOR в SQL Ранее были упомянуты курсоры в SQL. Что это такое? Курсор — это инструмент, который позволяет обрабатывать результаты запроса построчно, а не сразу всем набором. То есть если обычный селект возвращает нам сразу много строк из таблицы, то тот же самый селект, упакованный в курсор, будет возвращать нам каждую строку по отдельности. Курсор обычно используется в наборе с переменными (куда сохраняется результат) и с циклами (которые перебирают каждую строку). В общем, на практике это выглядит как-то так: DECLARE -- Объявляем курсор CURSOR emp_cursor IS SELECT COUNTRY_id FROM hr.COUNTRIES where region_id = 20; -- переменная для хранения v_name hr.COUNTRIES.COUNTRY_id%TYPE; BEGIN -- Открываем курсор OPEN emp_cursor; -- Запускаем цикл LOOP -- Выбираем данные в переменную FETCH emp_cursor INTO v_name; -- Выходим из цикла, если данных больше нет EXIT WHEN emp_cursor%NOTFOUND; -- Выводим DBMS_OUTPUT.PUT_LINE('Код страны: ' || v_name); -- Выходим из цикла END LOOP; -- Закрываем курсор CLOSE emp_cursor; END; Формат вывода будет следующим (точные данные зависят от таблицы): Код страны: AR Код страны: BR Код страны: CA Код страны: MX Код страны: US 🟠Зачем это нужно? Курсоры часто применяются в процедурах и функциях. Например, у нас написана процедура, которая в качестве входного параметра (IN) принимает значения COUNTRY_id (из приведенного выше примера). И нам нужно в нее передавать значения COUNTRY_id, где region_id = 20. Без курсора и цикла мы бы, как вариант, сначала написали запрос, чтоб получить список COUNTRY_id: SELECT COUNTRY_id FROM hr.COUNTRIES where region_id = 20 а затем вручную передавали бы по очереди эти значения в процедуру: BEGIN PROCEDURE_NAME('AR'); END; С курсором и циклом все было бы иначе: CREATE OR REPLACE PROCEDURE PROCEDURE_NAME(p_region_id IN NUMBER) IS CURSOR emp_cursor IS SELECT COUNTRY_id FROM hr.COUNTRIES where region_id = p_region_id; и дальше прописывается какая-нибудь логика процедуры Мы бы создали процедуру, где входной параметр p_region_id использовался бы в where region_id = p_region_id. Вызывалась бы такая процедура так: BEGIN PROCEDURE_NAME(20); END; P.S. Курсоры, конечно, могут применяться и для других задач, но наиболее часто они встречаются в процедурах и функциях. Вне их контекста курсоры практически всегда можно заменить самыми обычными операциями, которые нам привычнее и, самое главное, быстрее построчной обработки.

Циклы в PL/SQL: LOOP, WHILE, FOR Циклы используются для выполнения повторяющихся операций. Полезны, когда данные надо обрабатывать построчно, но работают циклы медленно. 🟠В чем различие между LOOP, WHILE и FOR? ▶️LOOP выполняется бесконечно, пока не будет явно указано условие выхода с помощью EXIT DECLARE counter NUMBER := 1; BEGIN LOOP DBMS_OUTPUT.PUT_LINE('counter: ' || counter); counter := counter + 1; EXIT WHEN counter > 5; END LOOP; END; ▶️WHILE выполняется, пока условие истинно. Если условие ложно с самого начала, цикл не выполнится ни разу DECLARE counter NUMBER := 1; BEGIN WHILE counter 10000 THEN v_bonus := emp_rec.salary * 0.15; ELSIF MONTHS_BETWEEN(SYSDATE, emp_rec.hire_date) > 60 THEN v_bonus := emp_rec.salary * 0.10; ELSE v_bonus := emp_rec.salary * 0.05; END IF; UPDATE employees SET bonus = v_bonus WHERE employee_id = emp_rec.employee_id; END LOOP; COMMIT; END; Для начала, что делает этот скрипт? Он устанавливает размер бонуса к зарплате сотрудника: если зп > 10000, то бонус = 15%; если стаж работы в компании > 5 лет, то бонус = 10%. Для всех остальных случаев бонус = 5%. После этого таблица обновляется. 🟠Почему эта задача решается с помощью цикла, а не обычных операций со множествами? Можно было бы просто написать что-то вроде этого: UPDATE employees SET bonus = CASE WHEN salary > 10000 THEN salary * 0.15 WHEN MONTHS_BETWEEN(SYSDATE, hire_date) > 60 THEN salary * 0.10 ELSE salary * 0.05 END; В целом, можно справиться с ней и обычными множествами. Но теперь усложним задачу. Вернемся к скрипту с циклом. Представим, что вдобавок ко всему написали функцию, которая имеет входные параметры employee_id и v_bonus и которая проверяет на основании этих данных, действительно ли сотруднику положено такое вознаграждение. В случае с циклом вызов этой функции можно было бы прописать после END IF и перед UPDATE. Со множествами уже сложнее. А если подобных функций будет 10? Вот в таких ситуациях циклы могут выручить. 🟠Почему именно цикл FOR, а не LOOP или WHILE? В случае с LOOP надо было бы создавать курсор; задумываться над выходом из цикла, писать разные условия по типу "выходим из цикла, когда в таблице закончились строки". В примере с FOR это всё определяется сразу после BEGIN. С WHILE похожая ситуация: создание курсора, продумывание условий по выходу из цикла.

В дополнение к посту. Рис.1️⃣Набор входящих данных (перед первым пунктом) Визуализации из пункта 3: Рис.2️⃣Регрессионный график (точки, "линия тренда" и интервалы) Рис.3️⃣График рассеяния (визуализация взаимосвязи между переменными). Показывает "Категорию" в системе координат в зависимости от Х, У Рис.4️⃣Тепловая карта корреляций между Value, Х, У. Рис.5️⃣Распределение данных по категориям Рис.6️⃣Сколько раз какая категория встречается

Seaborn в Python: обзор и сравнение с Matplotlib Вторая популярная библиотека (и в целом последняя из тех, которые нужно упомянуть для аналитики) для визуализации данных. Создана на основе Matplotlib, то есть является высокоуровневой библиотекой, построенной на основе низкоуровневой (Matplotlib). Также считается ее расширенной версией. Вдобавок можно отметить, что, т.к. Matplotlib - низкоуровневая библиотека, то и работать она будет быстрее, и больше подходит для объемных данных. 🟠В чем же практическое отличие между этими библиотеками? Сравним эти библиотеки наглядно на конкретных примерах. Рисунки и входные данные будут в следующем посте. Сначала зададим данные, которые впоследствии будут использоваться во всех визуализациях: import pandas as pd import numpy as np # Создаем данные np.random.seed(42) x = np.arange(10) y = np.random.randint(1, 20, size=10) categories = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J'] values = np.random.randint(10, 100, size=10) data = pd.DataFrame({'x': x, 'y': y, 'category': categories, 'value': values}) 1️⃣Использование двух библиотек вместе для построения линейного графика: import seaborn as sns import matplotlib.pyplot as plt # Строим график sns.lineplot(x='x', y='y', data=data, marker='o') plt.title('Линейный график (Seaborn)') plt.xlabel('Ось X') plt.ylabel('Ось Y') plt.show() Здесь Matplotlib отвечает за названия осей и визуализации, а также за вывод графика. Seaborn, в свою очередь, отвечает за само построение. Отсюда вытекает особенность: так как Seaborn - производная от Matplotlib, то и работать они могут в связке, и работают достаточно успешно 2️⃣ Сравнение механизма построения линейного графика, столбчатой диаграммы, круговой диаграммы и гистограммы. Matplotlib: import matplotlib.pyplot as plt #главная команда для линейного графика plt.plot(data['x'], data['y'], marker='o') #главная команда для диаграммы plt.bar(data['category'], data['value']) #главная команда для круговой диаграммы plt.pie(data['value'], labels=data['category'], autopct='%1.1f%%') #главная команда для гистограммы plt.hist(data['value'], bins=5, edgecolor='black') Seaborn: import seaborn as sns #главная команда для линейного графика sns.lineplot(x='x', y='y', data=data, marker='o') #главная команда для диаграммы sns.barplot(x='category', y='value', data=data) Seaborn не поддерживает круговые диаграммы напрямую #главная команда для гистограммы sns.histplot(data['value'], bins=5, kde=False) Как мы видим, для самых типичных визуализаций мало различий между Seaborn и Matplotlib (если не принимать во внимание то, что в Seaborn нельзя строить круговые диаграммы). Этих команд будет достаточно для построения самых дефолтных, примитивных графиков/диаграмм/гистограмм. Отсюда вытекает еще одна особенность: если мы захотим их как-то украсить или сделать более информативнее (иными словами - произвести много дополнительных настроек), то тут уже надо будет подключать Matplotlib. Существенные же различия возникают, если мы хотим построить что-то необычнее типичных графиков. 3️⃣Нетипичные визуализации, выполненные в Seaborn, которые сложнее реализовывать в Matplotlib: import seaborn as sns #Регрессионный график sns.regplot(x='x', y='y', data=data) #График рассеяния sns.scatterplot(x='x', y='y', data=data, hue='category') #Тепловая карта sns.heatmap(data=data.corr(), annot=True, cmap='coolwarm') #Распределение по категориям sns.swarmplot(x='category', y='value', data=data) #Диаграмма количества sns.countplot(x='category', data=data)