Эта статья, скорее для ознакомления и хотелось бы получить советы по данной работе.
Итак, Excel-файл весит 500+ мегабайт, состоит из сотен тысяч строк, десятков листов и формул, которые «протягиваются» по 30+ столбцам — это не работа, а страдание. Именно с таким «монстром» я столкнулся, когда в компании собрались данные из разных отделов в один файл.
Вкратце структура файла — Лист «Массив» (Data_Lake — в левой части 34 столбца с которым работают специалисты и на котором отрабатывают основные формулы и правая часть с 46 столбцами, куда подтягиваются сырые данные, с которыми будет производиться обработка). И множеством листов со справочниками, правками.
Открытие этого Excel‑файла занимает 10 минут, а если обновить хотя бы часть формул — можно идти пить чай. Работать с такими данными просто невозможно, особенно если тебе нужно анализировать их, строить отчёты или готовить выгрузки. Поэтому решил попробовать все перевести на PostgreSQL.
Для этого всего лишь требовалось переписать формулы с Excel на SQL. Хорошо, что большинство формул это условия ЕСЛИ, ИЛИ.
Вот самая простая формула:
«=ВПР(AN228087&"-"&AX228087;'Мэппинг'!A:E;4;ЛОЖЬ)»И соответствующая ей функция на SQL:
CREATE OR REPLACE FUNCTION statia_po_shety()
RETURNS void AS $$
BEGIN
WITH data AS (
SELECT
m."id",
ms."Статья" AS "Статья УУ по Счету"
FROM "Массив" m
LEFT JOIN "cvi" cvi ON m."id" = cvi."id"
LEFT JOIN "Мэппинг счетов" ms
ON ms."ОБЪЕД" = COALESCE(cvi."Счет Дт", '') || '-' || COALESCE(cvi."Счет Кт", '')
)
UPDATE "Массив" m
SET "Статья УУ по Счету" = d."Статья УУ по Счету"
FROM data d
WHERE m."id" = d."id";
END;
$$ LANGUAGE plpgsql;
Эта функция запускается по триггеру, когда происходит вставка или изменение одной строки, чтобы не обновлять весь массив данных.
А вот и «монстр» из Excel
Это лишь часть формулы, но, чтобы вы понимали масштаб:
=ЕСЛИ(ЕСНД(ВПР(AL228133;'Мэппинг регистратор'!$A:$C;2;ЛОЖЬ);
ЕСНД(ВПР($A228133;'Правки БУ'!A:Q;ПОИСКПОЗ(K$1;'Правки БУ'!$A$1:$Q$1;0);ЛОЖЬ);
ЕСЛИ(ИЛИ(ЕСНД(M228133;0)="!Не брать";
ЕСНД(N228133;0)="!Не брать";
ЕСНД(O228133;0)="!Не брать";
ЕСНД(R228133;0)="!Не брать";
ЕСНД(S228133;0)="!Не брать");"!Не брать"; и т.д. Полная формула занимает десятки строк и комбинирует ЕСЛИ, ВПР, ИЛИ, ПСТР, ЕПУСТО и прочие «прелести» Excel. Протяжка по 300 000 строк делает такой расчёт абсолютно нерабочим.
Как переписал это в PostgreSQL
Создана функция update_statya_uu(), которая:
Объединяет все необходимые данные во временную таблицу
temp_aggПоочерёдно применяет логику
IF...THEN, аналогичнуюЕСЛИиВПРв ExcelВыполняет обновления только при необходимости
Пример фрагмента функции:
CREATE OR REPLACE FUNCTION update_statya_uu()
RETURNS void AS $$
DECLARE
rec RECORD;
result_text TEXT;
BEGIN
CREATE TEMP TABLE temp_agg AS
SELECT
m.*,
c."Документ" AS cvi_document,
c."Организация" AS cvi_organization,
c."Тип документа.1" AS cvi_tip_dok_1,
m."Тип документа" AS m_tip_dok,
c."Субконто Кт" AS cvi_subkonto_kt,
c."Субконто2 Кт" AS cvi_subkonto2_kt,
c."Субконто3 Кт" AS cvi_subkonto3_kt,
c."Субконто1 Дт" AS cvi_subkonto1_dt,
c."Субконто2 Дт" AS cvi_subkonto2_dt,
c."Счет Кт" AS cvi_schet_kt,
c."Счет Дт" AS cvi_schet_dt
FROM "Массив" m
LEFT JOIN "cvi" c ON m.id = c.id;
-- Проходим по временной таблице по одной записи и вычисляем значение для "Статья УУ"
FOR rec IN SELECT * FROM temp_agg LOOP
р
SELECT "Статьи затрат УУ" INTO result_text
FROM "Мэппинг регистратор"
WHERE "Регистратор" = rec.cvi_document
LIMIT 1;
IF FOUND THEN
UPDATE "Массив" SET "Статья УУ" = result_text WHERE id = rec.id;
CONTINUE;
END IF;
SELECT "Статья УУ" INTO result_text
FROM "Правки БУ"
WHERE "Документ БУ Ключ" = rec."Key"
LIMIT 1;
IF FOUND THEN
UPDATE "Массив" SET "Статья УУ" = result_text WHERE id = rec.id;
CONTINUE;
END IF;
IF rec."Статья УУ по затрате" = '!Не брать' OR
rec."Статья УУ по запчастям" = '!Не брать' OR
rec."Статья УУ по НГ" = '!Не брать' OR
rec."Статья УУ по Счету" = '!Не брать' OR
rec."Статья по Тип док." = '!Не брать' THEN
UPDATE "Массив" SET "Статья УУ" = '!Не брать' WHERE id = rec.id;
CONTINUE;
END IF;Логика последовательная: от приоритетных правил к более общим, с проверкой условий, похожих на Excel-формулы.
Производительность
🔄 Обработка 300 000 строк: ~2 минуты
📉 Время обновления сократилось в 10 раз
⛓ Работает по триггеру для обновлений отдельных строк
В конечно итоге, работа по переносу заняла пару недель и вот часть структуры:
И на все столбцы, всех таблицы у нас 57 триггеров
Но перенести этот массив было полбеды. Из-за специфики, специалистам надо прогонять 300 тысяч строк каждый день. И тут вступил в дело Python.
Общая схема ETL-процесса:
Excel (.xlsx) → DataFrame → Pickle → PostgreSQL (таблицы cvi, Массив) → JOIN → Data_Lake → Приведение типов
1. Предобработка: загружаем Excel → DataFrame
df = pd.read_excel(excel_file_path)
df.to_pickle(pickle_file_path)Как показала практика .pkl читается быстрее
Обрабатываем столбец "Дата" — Excel часто сохраняет даты в виде чисел, и это надо исправить:
df['Дата'] = pd.to_datetime('1899-12-30') + pd.to_timedelta(df['Дата'], unit='D')2. Подготовка базы: создаём схему и таблицу
CREATE SCHEMA IF NOT EXISTS "public";
CREATE TABLE IF NOT EXISTS "cvi" (...);*cvi - таблицы куда поступают сырые данные (правая часть Data_Lake)
Поля автоматически приводятся к varchar(255), а даты — к date. Если нужен более точный тип — можно легко адаптировать.
3. Очистка таблиц перед загрузкой
TRUNCATE TABLE "Массив" RESTART IDENTITY CASCADE;
TRUNCATE TABLE "cvi" RESTART IDENTITY CASCADE;*Массив - результат обработки с cvi (левая часть Data_Lake)
Это нужно, чтобы избежать дублирования, т.к. обновляется всё.
4. Параллельная загрузка данных в PostgreSQL
CSV или bulk-загрузка подошли бы, но мы выбрали psycopg2 + executemany с параллельной обработкой чанков:
chunk_size = 6000
max_workers = 8Для каждого чанка — отдельный поток:
with ThreadPoolExecutor(max_workers=max_workers) as executor:
futures = [executor.submit(load_chunk, chunk) for chunk in chunks]Дальше создается витрина Data_Lake (витрина):
CREATE TABLE "Data_Lake" AS
SELECT m.*, c.*
FROM "Массив" m
JOIN "cvi" c ON m.id = c.id;И производится перевод в нужный формат данных (из text в numeric(12,2)), если надо.
В конечно итоге с момента загрузки сырых данные (300 тысяч строк, в 46 столбцах) в таблицу cvi, срабатыванием триггеров и формированием витрины данных (Data_Lake с 78 столбцами в 300 тысяч строк) заняло у меня:
Дополнительно:
добавляются еще связи
ETL реализовать в Apache Airflow
добавить веб-интерфейс для дашборда
Вроде на этом все. Естественно буду благодарен за советы по улучшению и оптимизации работы.