Consistency — Согласованность

Каждая успешная транзакция фиксирует только допустимые результаты. 

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

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

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

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

Другой пример, в БД интернет-магазина нельзя добавить заказ с пустым client_id, так как это противоречит бизнес-логике.

#sql #acid

CHAR и VARCHAR — что будем использовать? Или всё-таки TEXT?

У каждой СУБД есть особенности, связанные с используемыми типами данных, но что касается хранения текстовой информации чаще стандартом являются 'char', 'varchar' и 'text'. В чём же разница и как понять, что выбрать?

1. CHAR(n) — хранит текстовые данные фиксированной длины. Что это значит? Под данные всегда бронируется указанное число символов и если добавить в столбец name типа char(5) текст 'Olga', то физически запишется 'Olga '. Недостающие символы дополнятся пробелами. Нужно понимать, что несмотря на то, что вы внесли в такой столбец текст длиной 4 символа, вывод LENGTH(name) по этому значению выведет длину строки 5.

Поэтому хранить данные переменной длины в типе char крайне неэффективно. Столбец типа CHAR всегда будет занимать одинаковое, прогнозируемое пространство диска.

Кроме этого возможны проблемы:
— при конкатенации строк, т.к. данные дополняются пробелами.
— с поиском пробелов в строках.

Если всё-таки используете тип char, в запросах рекомендуется использовать функцию RTRIM, которая усекает пробелы в конце строки.

2. VARCHAR(n) — для хранения строк переменной длины. n — указывает на максимальное хранимое количество символов, но не ограничивает в хранении строк меньшей длины.

Производительность типа varchar незначительно ниже, чем у char, так как для вычислений необходимо использовать информацию о длине строки. А, например, тот же Oracle в своём типе VARCHAR2 хранит не только строку, но и информацию о её длине. Нужно помнить, что зато данные в столбцах типа varchar, если они переменной длины, занимают меньше дискового пространства.

3. TEXT — хранит данные любой длины без ограничений. Это не значит, что нужно складывать в этот тип весь текст всех страниц Wikipedia, для очень больших текстовых данных существуют специально оптимизированные типы.

Отдельно стоит отметить особенности использования varchar без указания максимальной длины и text. Они будут отличаться для различных СУБД и стоит изучить их отдельно.

Дополнительно можно почитать хорошую статью по строковым типам на sql-ex.

#sql

Основные отличия ClickHouse от других MPP-систем

CH изначально создавался как БД, максимально приспособленная для всевозможной агрегации очень широких таблиц (в частности для хранения и использования Яндекс.Метрики), при этом в него изначально не закладывалась необходимость быстрых JOIN.

В CH используется свой диалект SQL, а не общепринятый ANSI SQL. Это нужно учитывать при написании запросов.

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

Ещё раз. ClickHouse создан для быстрой агреграции и не создан для JOIN.

И снова напомню, в ClickHouse нет транзакций. Именно поэтому он такой быстрый. Но отсутствие транзакций также накладывает и свои особенности использования.

#clickhouse

OLTP vs OLAP

OLTP — обработка транзакций в режиме реального времени.
— Хранение детализированных данных
— Максимальная нормализация при хранении
— Возможность в любое время добавлять, удалять, изменять данные даже по 1 строчке
— Измеряемое количество транзакций в секунду
— Доступ к данным по простым, заранее составленным запросам, для решения конкретных задач
— Время отклика — миллисекунды
Пример использования: обработка заказов, платежей, управление данными клиентов.

OLAP — онлайн аналитическая обработка.
— Исторические / архивные данные, часто обобщенные
— Не допускаются ошибки в данных
— Малый объем транзакций
— Согласованный формат хранения данных
— Добавление данных большими пачками
— Сложные запросы по большим объемам данных
— Время отклика — минуты и больше
Пример использования: прогнозирование поведения клиентов, анализ рекламных компаний.

#dwh

Как запросом убрать дублирующиеся данные?

Можно использовать ключевое слово DISTINCT, которое отфильтрует дублирующиеся значения из набора результатов, то есть вернёт только уникальные строки.

Пример выборки только уникальных id из таблицы:

SELECT DISTINCT id 
  FROM table_name;

Другой вариант — это использование GROUP BY, чтобы сгруппировать дублирующиеся значения.

SELECT id 
  FROM table_name
 GROUP BY id;

Что использовать и есть ли разница?

Если посмотреть план запросов выше, то можно увидеть, что в обоих случаях под капотом происходит группировка данных и скорость выполнения запросов одинаковая. Однако, для лучшей читаемости кода, DISTINCT стоит использовать там, где вы хотите убрать дублирование строк в результатах, а GROUP BY — для группировки в явном виде и дальнейшего использования агрегатных функций.

#sql

Я есть CRUD

Команды CRUD (Create, Read, Update, Delete) используются для работы с данными.

— CREATE - создание (insert - вставка данных)
— READ - чтение (select - выборка данных)
— UPDATE - изменение (update - обновление)
— DELETE - удаление (delete)

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

#sql

Доки должны быть качественными

Ранее я писала о важности документации, теперь пришло время поговорить о её качестве. Очевидно, что не вся документация хорошая и полезная. Чем более запутано и неструктурированно написан документ, тем больше проблем он принесёт при реализации.

При написании доков можно следовать чек-листу из требований к качеству.

Свойства хорошей документации:
☑ Осуществимость
☑ Полнота
☑ Краткость
☑ Непротиворечивость
☑ Атомарность
☑ Однозначность
☑ Понятность
☑ Приоритизированность
☑ Тестируемость

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

#документация

Сжатие данных в Greenplum

Опции хранения данных определяются на этапе создания таблиц.

Уровень сжатия данных:
— на уровне таблицы (table-level) — применяется ко всей таблице. Доступно для AOT-таблиц как со строковой (row-oriented), так и с колоночной (column-oriented) ориентацией данных.
— на уровне столбца (column-level) — применяется к отдельному столбцу. Позволяет использовать различные алгоритмы сжатия для разных столбцов одной таблицы. Этот тип сжатия доступен только для AOT-таблиц с колоночной ориентацией данных.

Независимо от уровня, на котором применяется сжатие данных, для его настройки можно использовать следующие параметры:
— compresstype – тип сжатия данных. Возможные значения: ZLIB, ZSTD и RLE_TYPE. По умолчанию используется значение none, при котором сжатие не применяется.
— compresslevel  – уровень сжатия данных. Уровни с наименьшими номерами соответствуют самой быстрой, но при этом наименьшей компрессии данных.

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

Пример создания AOT-таблицы с колоночной ориентацией и zstd-хранением:

create table [schema_name].<table_name>
             (<columns_list>)
with (appendoptimized = true,
      orientation = column,
      compresstype = zstd,
      compresslevel = 3
);

#greenplum

Быть или не быть. Важность формулирования вопросов.

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

Со мной это случается постоянно 😅

Если у вас бывают такие ситуации, то рекомендую взять в оборот метод Утёнка — ставите на свой рабочий стол понравившуюся фигурку (в оригинале метода — резинового утёнка) и ей задаёте вопросы, ведёте мысленную дискуссию, постепенно приходя к нужным ответам и выводам. В момент формулирования вопроса все ваши знания невольным образом упорядочиваются.

Помните, что правильная формулировка уже содержит в себе половину ответа и часто истина открывается тогда, когда вы пытаетесь что-то кому-нибудь объяснить.

#soft_skills

Целочисленные типы в Clickhouse

Кроме привычного Integer, Clickhouse также поддерживает неотрицательные целые числа, которые представлены префиксом U.

Целое число:

Int8 - [-128 : 127] - 8 bit
Int16 - [-32768 : 32767] - 16 bit
Int32 - [-2147483648 : 2147483647] - 32 bit
Int64 - [-9223372036854775808 : 9223372036854775807] - 64 bit
...

Целое неотрицательное число:

UInt8 - [0 : 255] - 8 bit
UInt16 - [0 : 65535] - 16 bit
UInt32 - [0 : 4294967295] - 32 bit
UInt64 - [0 : 18446744073709551615] - 64 bit
...

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

Что важно понимать? Прямое преобразование из UInt в Int чаще всего невозможно, так как Int не вместит в себя весь диапозон UInt. Например, в строке типа UInt8 могут содержаться числа от 0 до 255, в то время как в строке типа Int8 от -128 до 127. Аналогично и с обратным преобразованием, поэтому нужно быть внимательным при переносе данных между разными источниками или внутри БД. Ну и, конечно, всегда изучать не только типы столбцов, но и сами данные и их бизнес-смысл.

Подробнее об ёмкости типов в доках CH.

#clickhouse

Качества документации: ТЗ должно быть полным, но кратким

Всем известна фраза "краткость — сестра таланта" и она отлично ложится на написание документации. Сплошную стену текста одним абзацем с пространными рассуждениями никто читать не будет или будет, но по диагонали и крайне невнимательно. Что обязательно скажется на скорости и качестве разработки, либо её поддержке.

Всегда цените своё время и время коллег.

ТЗ должно содержать минимум воды, только технические факты. Но шутка в том, что писать кратко =/= писать мало. В ТЗ не должно быть моментов "а вот это итак очевидно, указывать не буду". То, что очевидно сегодня, будет никому неизвестно завтра, когда придёт другой сотрудник или что-то просто забудется.

Поэтому важно соблюдать баланс между многабукаф и "ничего не понятно".

Лайфхаки:
— Проработайте шаблоны под типичные для компании типы ТЗ.
— Логику процессов прописывайте пошаговым списком.
— Вместо текста, там где это возможно, используйте таблицы, графики, майнд-карты.
— При написании ТЗ ставьте себя на место разработчика. Всё ли вам понятно?
— Используйте заголовки и оглавление.
— Выделяйте важный текст и не бойтесь подчеркиваний. Но без фанатизма. Система выделений и цветов не должна пестрить и должна быть понятной.
— Дописав, сделайте паузу на чай и/или другую задачу. Вернитесь и перечитайте.

#документация

Партиционирование (partitioning) в Greenplum

Партиционирование (или секционирование) помогает повысить производительность запросов за счет разбиения больших таблиц на небольшие части, называемые партициями (partitions). Это позволяет оптимизаторам запросов сканировать ограниченное число строк в таблице (на основе условий) вместо чтения всего содержимого таблицы.

Партицировать маленькие таблицы не имеет смысла!

Партиционирование может быть указано только при создании таблицы, однако удалять/добавлять/изменять партиции в дальнейшем можно. Чтобы сделать добавить партиционирование в таблицу, нужно сделать новую таблицу с партициями и перенести данные из непартиционированной.

Пример создания партиционированной таблицы:

create table [schema_name].<table_name>
             (<columns_list>)
[with (<storage_options>)]
distributed <distribution_policy>
partition by <partition_spec>;

Важно! Загрузка данных в партиционированные таблицы крайне неэффективна. Поэтому рекомендуется загружать данные в промежуточную (staging) таблицу и затем применять к партиционированной таблице команду EXCHANGE PARTITION.

#greenplum

Isolation — Изолированность

Изолированность отвечает за то, что транзакции не должны оказывать влияния на другие параллельные транзакции.

Большинство БД поддерживает 4 уровня изоляции:
1. Read Uncommitted
2. Read Committed
3. Repeatable Read
4. Serializable

Чем ниже уровень, тем слабее изоляция, но тем меньше тратится ресурсов.

Подробнее о возникающих проблемах при работе параллельных транзакция и об уровнях изоляции поговорим позже.

#sql #acid

Шпаргалка: NULL и логические операции

Я уже рассказывала про сравнение с Null, но это не все особенности работы с ним, которые важно знать при анализе данных.

Null — третье логическое значение (кроме True и False), оно обозначает неизвестность. Исходя из этого определения легко вывести следующее:

Null AND True = Null
Null AND False = False
Null AND Null = Null

Null OR True = True
Null OR False = Null
Null OR Null = Null

NOT (Null) = Null
NOT (NOT(Null)) = Null

Во всех случаях стоит помнить, что Null в результатах не равен Null в условии. Это несравнимая неизвестность.

#sql #null

Data Governance или забота о данных

Data Governance — это процесс управления данными, включающий определение структуры и правил, обеспечивающий их качество, целостность, доступность, безопасность и эффективное использование.

Data Governance — это как следить за порядком в своей комнате, только в мире данных.

Почему это важно?
— Это помогает сохранить данные в порядке. Хаос в данных может привести к неверным выводам. Data Governance говорит нам, как следить за чистотой данных.

— Это помогает нам соблюдать законы. Мы должны защищать данные и соблюдать законодательство той страны, в которой работаем. Data Governance учит нас, как это делать.

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

Data Governance — это организационный фундамент, на котором строится надёжная, безопасная и эффективная работа с данными.

#data_governance

Не бойтесь "глупых" вопросов

IT, как и сфера любых технологий, стремительно развивается. И абсолютно невозможно знать всё обо всём, и это нормально. Но как часто кто-то из нас считает себя крутым специалистом и стыдится приходить с "глупыми" вопросами к коллегам?

Задавать любые вопросы — нормально. И это тоже очень важный навык. Во-первых, во время формулирования вопроса вы уже можете прийти к нужному ответу (писала про это в отдельном посте). Во-вторых, часто получить ответ от коллеги будет быстрее и эффективнее, чем идти самому через тернии к звёздам.

Конечно, это не значит, что нужно бегать за каждым забытым хоткеем по чатам. Сначала всё-таки сходите в старый добрый гугл и приложите хотя бы минимальные усилия на поиск ответа.

Сначала думаем, потом формулируем, гуглим, затем спрашиваем.

#soft_skills

Группировка данных в SQL: суть и применение

Группировка данных — это инструмент анализа в SQL, который позволяет агрегировать данные для получения ценных инсайтов. Давайте разберемся, как это работает.

GROUP BY — основной метод группировки данных. Он позволяет сгруппировать строки в результатах запроса по значениям в одном или нескольких столбцах. Например, вы можете группировать продажи по датам и/или по категориям товаров. Это особенно полезно для создания агрегированных отчетов.

COUNT() — используется для подсчета количества строк в каждой группе. Например, вы можете узнать, сколько заказов было сделано каждым клиентом.

SUM() — для суммирования числовых значений в группе, таких как общая сумма продаж по категории товаров.

AVG() — для вычисления среднего значения числовых данных в группе. Например, средний размер заказа.

MAX() и MIN() — определяют максимальное и минимальное значение в группе. Это может быть полезно, например, для определения самой дорогой или дешевой покупки в каждой категории товаров.

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

Пример группировки, который выводит среднюю цену товара в каждой категории с фильтрацией по среднему:

SELECT category, AVG(price) as average_price
FROM products
GROUP BY category
HAVING AVG(price) > 100;

Нужно понимать, что группируя данные вы делаете именно агрегацию и не можете вывести тут же информацию и из отдельной строки.

Группировка данных полезна для создания сводных таблиц, отчетов и анализа больших объемов информации. Она помогает выделить общие закономерности и тренды, что делает ее неотъемлемой частью работы с данными в DWH.

#sql

Виды партиций в Greenplum

— partition by range – осуществляет разделение данных на основе числовых или временных (date/timestamp) диапазонов. Интервалы для партиций указываются используя ключевые слова START и END. Выражения INCLUSIVE и EXCLUSIVE используются в связке с START и END для указания того, должны ли попадать в соответствующий диапазон граничные значения. По умолчанию значения, указанные с помощью START, включаются в диапазон; значения, определенные с помощью END — нет. Партиции можно указывать как автоматически, так и вручную.

Автоматически:

create table [schema_name].<table_name>
             (<columns_list>)
[with (<storage_options>)]
distributed <distribution_policy>
partition by range(<column name>)
     (partition monthly start (date 'ХХХХ-ХХ-ХХ') inclusive end (date 'ХХХХ-ХХ-ХХ') exclusive every (interval '1 month'));

Вручную:

create table [schema_name].<table_name>
             (<columns_list>)
[with (<storage_options>)]
distributed <distribution_policy>
partition by range(<column name>)
     (partition Nov23 start(date '2023-11-01') inclusive,
      partition Dec23 start(date '2023-12-01') inclusive end(date '2024-01-01') exclusive);

— partition by list – на основе списков значений.

create table [schema_name].<table_name>
             (<columns_list>)
[with (<storage_options>)]
distributed <distribution_policy>
partition by list (pet)
    (partition cats values ('Cat'),
     partition dogs values ('Dog'),
     default partition other);

#greenplum

DWH и анализ данных

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

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

Некоторые составляющие анализа:

1. Понимание данных и их структуры (здесь поможет data-каталог или иная качественная документация к хранилищу).

2. Формулирование вопросов к бизнесу, процессам и данным. Например, "Какие продукты наиболее популярны у клиентов и есть ли зависимости от дня недели и времени суток?" или "Какие маркетинговые кампании приводят к наибольшим продажам и что между ними общего?".

3. SQL. Без этого инструмента (пока что) никуда.

4. Визуализация данных. Графики и диаграммы помогают наглядно представить результаты анализа и лучше понять данные.

5. Обнаружение паттернов, тенденции и аномалии может помочь в принятии решений, определении стратегии и оптимизации процессов.

6. Прогнозирование на основе данных из хранилища и разработка прогностических моделей.

Анализ данных добавляет существованию DWH смысл. В то же время грамотно (зависит от целей) спроектированное хранилище поможет облегчить, ускорить и качественно улучшить анализ.

#dwh

Дайджест статей за сентябрь

Лучше поздно, чем никогда :)

Общее о профессии и DWH:
Системный аналитик — кто он?
Насколько большая эта ваша Big data? 
Коротко о DWH
Data Lakehouse
Зачем нам нужен ETL?

БД:
ACID: atomicity, consistency, isolation and durability
Atomicity — Атомарность
Consistency — Согласованность
CHAR и VARCHAR — что будем использовать? Или всё-таки TEXT?
Почему UUID лучше, чем автоинкрементные идентификаторы. И лучше ли?

Немного о доках:
Без внятного ТЗ и результат ХЗ

ClickHouse:
Коротко о ClickHouse
Основные отличия ClickHouse от других MPP-систем
Шардирование данных в ClickHouse

Greenplum:
Коротко о Greenplum
Хранение данных в таблицах в Greenplum
Распределение (distribution) в Greenplum

Разное из SQL:
NULL != NULL — это True?
ALTER и UPDATE в SQL. В чём разница?

#дайджест