Цели статьи
Понимание архитектуры таблицы позволяет лучше работать с данными. В настоящее время скорость и качество работы с данными - ключевой фактор успеха.
Каждый день нам приходится проектировать таблицы. Это может быть список продуктов, учет продаж в магазине, данные по расходам группы компаний и т.д. Правильное понимание сути таблицы позволяет правильно их проектировать.
Кроме теоретического понимания необходимы практические инструменты, которые помогут частично автоматизировать архитектурный анализ таблицы.
Почему именно таблица? И как она может быть плоской?
Я работаю в сфере бизнес-консалтинга. Со временем мне удалось выделить три "структуры данных", которые играют ключевую роль в нашей сфере:
Плоская таблица - самая фундаментальная структура данных в бизнесе, по моей оценке
Иерархия (дерево) - по сути, это частный случай графа, но имеет много особенностей, поэтому выделяется отдельно. В области бизнеса так удобнее
Граф
Если вы откроете почти любой Excel файл, там будет нечто похожее на таблицы. В Word, Power Point также частенько вставляют таблицы. Реляционные БД - чистые плоские таблицы. Кубические БД - можно логически представить в виде таблицы. Даже иерархии и графы часто физически хранятся в табличном виде. Именно поэтому, в бизнесе, самый большой из трёх китов - плоская таблица. В этой статье мы подробнее рассмотрим именно её.
Термин "плоская таблица" подчеркивает разницу с просто таблицами:
Плоская таблица - это классическая таблица (а-ля SQL table) в первой нормальной форме (1NF). Однострочная шапка, атомарные типы данных. В общем красота. Когда вам дают такую таблицу, вы сразу можете приступить к интересной и полезной стадии анализа.
Таблица (в общем случае) - это всё, что человек считает таблицей. Таблицы бывают матричные, двойные матричные, с многострочными шапками, неатомарными типами данных в теле и т.д. Если вам дали такую таблицу, вы потратите от 15 минут до нескольких дней на перевод её в понятный, плоский вид. Иногда этот перевод вообще невозможен, по причине архитектурных ошибок или качества исходных данных.
Для наглядности два фото:
Это НЕ плоская таблица :(
Сложно понять какие сущности присутствуют в таблице. Как оказалось, в столбце №2 есть четыре сущности: предприятие, месторождение, объект и показатель :)
А вот это плоская таблица :)
Для перевода вышепоказанной таблицы в плоский вид потребовалась не одна строчка кода. Код перевода можно посмотреть в статье тут.
Архитектура плоской таблицы
Введем основные термины.
Потенциальные ключи таблицы (candidate key) - это наборы столбцов, которые обеспечивают уникальность каждой строчки таблицы. Больше техническое понятие. Полный набор потенциальных ключей интереса не представляет.
Первичный ключ таблицы (primary key) - это то, про что рассказывает плоская таблица. Каждая строчка содержит один объект. Каждая строчка рассказывает что-то про этот объект. Первичный ключ обеспечивает уникальность каждой строчки. Т.е. первичный ключ - это один из потенциальных ключей. К сожалению, далеко не все, кто проектируют таблицы, задумываются о первичном ключе. А без этого никуда.
Функциональные зависимости (functional dependencies). Приведу пример. Есть справочник должности, а есть справочник сотрудники. Допустим, что каждый сотрудник имеет строго одну должность. Таким образом, сотрудник функционально определяет должность. Обратное, обычно, неверно. Т.е. одна должность может быть у многих сотрудников. В бизнесе принято говорить, что должность - это атрибут/реквизит/группировка для справочника сотрудников. Но суть дела не меняется.
В функциональной зависимости есть детерминант (определитель) и цель. Детерминант -> Цель. Сотрудники - детерминант, должности - цель.
А теперь представим, что нам для анализа дали хорошую плоскую таблицу. Как нам можно составить общее представление о ней (понять архитектуру таблицы). Ниже четыре вопроса, которые я задаю чаще всего:
1) Что является первичным ключом таблицы?
Плоская таблица - это рассказ про первичный ключ.
Это всегда мой первый вопрос, когда я вижу плоскую таблицу. В ряде случаев ответ очевиден, но иногда требуется проверка или даже поиск первичного ключа. Иногда ключа вообще нет. Иногда заявления о первичном ключе не подтверждаются фактическим состоянием таблицы.
2) Какие функциональные зависимости есть в таблице?
В каком-то смысле, функциональная зависимость - основа информации. Т.е. именно отношение между двумя множествами - это и есть информация. Например, фраза "каждый сотрудник имеет ровно одну должность" означает два множества и функциональную зависимость между ними. Сотрудник -> Должность.
Понимание функциональных зависимостей позволяет лучше понять данные и предметную область на основе точных метрик (есть или нет функциональная зависимость). Это простой и эффективный способ архитектурного анализа данных.
3) Как соотносятся два столбца в таблице?
Этот вопрос задается реже, обычно он нужен, чтобы детально изучить отношение между двумя столбцами (либо двумя наборами столбцов).
Столбцы могут соотноситься как: 1-1, 1-N, N-1, N-N (логика как в ER-диаграммах). Сотрудники и должности соотносятся как N-1.
Кроме того, существует ряд более детальных метрик связи между двумя столбцами. Например, типы и количество компонент связности двудольного графа (соотношение двух столбцов, по сути, образуют двудольный граф). Но это уже глубокий архитектурный анализ, который нужен не часто.
4) Чем функционально определяется данный столбец?
Обычно мы не можем выявить все функциональные зависимости в таблицы. Т.к. это долго. Обычно выявляют только те, где детерминант и цель содержат по одному столбцу. Это можно посчитать сразу полностью. Ниже в кейсе мы так и сделаем и представим результат в виде графа.
Но часто интерес представляет конкретный столбец. Можно задаться вопросом, какие наборы столбцов функционально определяют наш целевой столбец. Это, опять-таки, позволяет лучше понять архитектуру данных, проверить гипотезы, выявить противоречия в данных.
Несколько слов про нормальные формы "по-простому"
Теперь мы готовы к пониманию первых трех нормальных форм (NF) в которых может находиться таблица.
1NF - это однострочная шапка, атомарные значения, без дубликатов строк, без пустых строк и столбцов. Ну т.е. минимальные требования к формату. Это и есть плоская таблица.
2NF и 3NF требуют соблюдения более тонкого принципа: "Таблица - рассказ про первичный ключ". Если какая-то колонка рассказывает про часть первичного ключа - нарушается нормальная форма. Если одна неключевая колонка рассказывает про другую неключевую колонку - опять же принцип нарушается.
В общем, первые три нормальные формы логичны. Помнить про них надо всегда. А вот соблюдаются они не всегда, реальные данные часто требуют денормализацию. Нормальные формы выше третьей интересны только с теоретической точки зрения.
Инструменты помогающие понять архитектуру плоской таблицы
Технически можно применять следующие инструменты:
MS Excel
Если нет ничего другого, используется чистый Excel, либо его аналог. Его преимущество в том, что он простой и позволяет быстро проверять простые гипотезы. Например, является ли этот набор столбцов уникальным. Но, в целом, это неэффективный инструмент для такого класса задач.
Pivot table (MS Excel)
Сводные таблицы Excel - это уже значительно более сильный инструмент. С ними удобно проверять интересные гипотезы средней сложности. Например, существует ли функциональная зависимость между двумя наборами столбцов. Но это все делается руками, так что много гипотез не проверишь.
Python
В сложных случаях требуется python. Интересно, но я смог найти только одну библиотеку, которая выполняет подобный архитектурный анализ из коробки (git, article). FDtool написан на python2. Видно, что библиотека не поддерживается её создателями. Что расстраивает(
Я решил написать велосипед свою версию, которая бы сделала архитектурный анализ удобнее. Код выложен на github. Ниже в кейсе я покажу применение инструмента на реальной таблице.
# базовое применение такое
!git clone https://github.com/Grigory-T/FlatTableAnalysis.git
from FlatTableAnalysis.FlatTableAnalysis import FlatTableAnalysis
fta = FlatTableAnalysis(df) # предполагается, что уже есть плоская таблица в виде df
fta.show_header_info()
fta.get_candidate_keys()
fta.show_fd_graph()
fta.show_set_relation('колонка 1', 'колонка 2')
fta... # ряд других методов, которые применяются режеКейс анализа таблицы из huggingface
В качестве таблицы я взял датасет музыкальных треков из huggingface (ссылка). Он уже в хорошем плоском виде, поэтому мы сразу можем приступить к анализу (минуя скучную стадию ETL). Колаб с кодом тут. Он содержит анализ таблицы с помощью моего питоновского класса FlatTableAnalysis. Ниже я опишу основные выводы.
В качестве эксперимента можете посмотреть на шапку таблицы самостоятельно и подумать, как вы бы делали обзорный анализ.
Шапка таблицы maharshipandya/spotify-tracks-dataset
Первичный ключ
что выдает нам инструмент?
Первый вопрос - что является первичным ключом. Никакая одна колонка не обеспечивает уникальность всех строк (113 550 строк). А сочетание двух колонок track_id, track_genre обеспечивает уникальность. Вообще это странно, можно было бы подумать, что track_id будет первичным ключом. Но около 17 тыс. треков имеют 2 и более жанра.
Функциональные зависимости
что выдает нам инструмент?
С этим вообще интересно. Из графов видно, что track_id функционально определяет почти все колонки! (кроме track_genre, конечно). Единственное исключение - колонка populatiry. 720 треков имеют две популярности, остальные 89 тыс. имеют строго одну. Это надо исследовать глубже. Похоже на ошибку в данных. Логично было бы предположить, что трек всегда имеет одну популярность...
Еще одна интересная ситуация с колонкой explicit. Это бинарный флаг, означающий, что трек содержит "info offensive or inappropriate for children", по крайней мере так говорит perplexity.ai. Если мы понизим порог отображения функциональных зависимостей до 0.95, то на графе будет видно, что album_name, artists достаточно сильно определяют explicit. Это, в общем-то, логично. Альбом содержит близки по сути треки, да и исполнители обычно придерживаются одного стиля. Хотя зависимость не точная.
Также можно заметить, что track_name и track_id имеют связь один-ко-многим. Есть случаи, когда одно название трека связано с разными id. Но это достаточно частая ситуация, поэтому больших вопросов не вызывает.
Выводы по архитектуре таблицы
Если забыть на минутку про колонку populatiry, то архитектуру таблицы надо менять! Таблица должна иметь первичный ключ - track_id. Все остальные колонки чётко зависят только от неё. Это позволит сократить количество строк с 133к до 89к.
Видимо один и тот же трек может относится к разным жанрам. Если это так, то можно создать отдельную таблицу, которая будет содержать отношение многое-ко-многим между track_id и track_genre. Две колонки. Эти же две колонки будут первичным ключом этой второй таблицы. В ней будет 133к строк.
С колонкой populatiry сложно. Моя гипотеза - тут ошибка в данных. Populatiry должна функционально зависеть от track_id. Если это так, то она пойдет в первую таблицу, где первичный ключ - это track_id. Мы же помним, что плоская таблица - это рассказ про первичный ключ :) Но если мы убедимся, что populatiry каким-то интересным образом зависит именно от сочетания двух колонок track_id и genre, то можно добавить её во вторую таблицу.
Надо заметить, что указанные выводы не являются очевидными. Именно твердая теоретическая основа и целенаправленные алгоритмы позволили быстро составить представление об архитектуре таблицы, выявить вопросы и возможные противоречия. Что и было нашей целью.
Итоги
Я постарался сделать удобный инструмент, который бы помогал в реальных бизнес-кейсах. Буду рад обратной связи. Возможно, кто-то захочет поучаствовать в развитии инструмента. Вопросы качества и архитектуры табличных данных важны и должны решаться системно.
В списке TODO сейчас: подумать над глубокой оптимизацией расчетов потенциальных ключей (возможно взять идеи из FDtool), подумать над удобством применения (какие метрики нужны, какие аргументы методов добавить) и т.д.
Еще некоторые полезные видео по теме есть в моем блоге на youtube.
UPDATE 14 апреля 2024
Для удобства добавил библиотеку python на pypi.
Добавил базовые тесты и исправил несколько багов, см. github.
!pip install flattableanalysis
from flattableanalysis.flat_table_analysis import FlatTableAnalysis
df = pd.DataFrame(YOUR_DATA)
fta = FlatTableAnalysis(df) # create analysis object
fta.get_candidate_keys(2) # check 2-cols candidates
fta.show_fd_graph()[0] # see graph of functional dependencies (all pairs of columns)