Размышления на тему Query Rewrite

в 9:35, , рубрики: olap, метки:

В этой статье не будет готового кода. Возможно, готового кода не будет вообще никогда. По большей части, я пишу эту статью, чтобы прочитать комментарии. Если эти комментарии подскажут мне возможности в Postgress, MySQL или любой другой СУБД, которые позволят мне решить задачу, не прибегая к разработке очередного велосипеда, я буду только рад.

Если говорить коротко, у меня появилась задача, для решения которой такие возможности как детальный контроль доступа (FGAC), материализованные представления (Materialized View) и Partitioning были бы совсем не лишними. Для реализации этих возможностей, Oracle Enterprise Edition использует механизм переписывания SQL-запросов (Query Rewrite). Все это прекрасно работает, и говорить было бы совсем не о чем, если-бы одним из условий нового проекта не было использование бесплатного ПО. Поскольку мы не можем приобрести необходимый функционал, естественным образом, возникает идея реализовать его самостоятельно.

Суть механизма Query Rewrite сводится к тому, что, перед выполнением, SQL-запрос автоматически переписывается. Например, при использовании детального контроля доступа, в фразу where добавляются дополнительные условия, а при применении материализованных представлений, обращение к некоторой большой таблице подменяется на обращение к заранее построенной таблице гораздо меньшего размера, содержащей агрегированные данные. Это можно рассматривать как некую форму рефакторинга SQL-запроса, оставляющую неизменным его вывод, но существенно оптимизирующую производительность его выполнения.

Для выполнения Query Rewrite, Oracle использует метаданные, которые нам, очевидно, также необходимо где-то хранить. Поскольку мы еще не определились с тем, какая из бесплатных СУБД будет использоваться, логично реализовать СУБД-независимое хранилище метаданных. Следующая схема иллюстрирует, как это можно сделать:

image

Таблица entity, как понятно из названия, будет содержать описание сущностей, которыми мы сможем манипулировать. К каждой сущности, посредством таблицы entity_attribute привязан список ее атрибутов. Один и тот-же атрибут может быть привязан к нескольким сущностям. Свойства атрибута сохраняются в таблице attribute. Свойство is_mandatory, например, управляет тем, может ли атрибут отсутствовать (или содержать NULL-значение).

Строки entity_attribute могут быть связаны с некоторым реально существующим столбцом, описанным в таблице columns, или вычисляться на основании значений других атрибутов, в соответствии с заданным арифметическим выражением expression. Столбцы, разумеется, связаны с описанием соответствующих таблиц tables.

Здесь следует заметить, что атрибут сущности может отображаться на какой-то столбец той таблицы на которую ссылается entity, но может размещаться и в другой, связанной с ней таблице. Условия соединения подчиненной и главной таблиц описываются в таблице condition и должны быть составлены таким образом, чтобы не более одной строки (0 или 1) подчиненной таблицы было связано с каждой строкой главной таблицы. Каждая строка condition содержит одно условие равенства значения столбца описываемого column_id некоторому значению value или значению столбца в другой таблице, описываемому foreign_column_id.

Помимо этого, мы можем описывать соединение внешние ключи, соединяющие сущности entity. Если в описании какого-то из атрибутов заполнено значение foreign_entity_id, этот атрибут считается внешним ключом, ссылающимся на первичный ключ упомянутой сущности.

Для примера, рассмотрим простую схему данных:

image

Метаданные будут содержать следующее:

tables:

Id name description
1 transaction_data Данные продажи
2 transaction_detail Детализация продажи

columns:

Id table_id name description
1 1 id Первичный ключ
2 1 transaction_date Дата продажи
3 1 total_price Суммарная стоимость
4 2 id Первичный ключ
5 2 transaction_id Ссылка на продажу
6 2 code Код товара
7 2 price Цена товара
8 2 quantity Количество товара

condition:

Id column_id foreign_column_id value
1 5 1

entity_type:

Id name description
1 operational
data
Оперативные данные
2 dictionary
data
Справочные данные

attribute_type:

Id name description
1 key Ключ
2 string Строка
3 number Число
4 date Дата

entity:

Id entity_type_id table_id name description
1 1 1 transaction Данные продажи
2 1 2 detail Детализация продажи

attribute:

Id attr_type_id name is_mandatory foreign_entity_id
1 1 id 1
2 4 transaction_date 1
3 3 total_price 1
4 1 transaction_id 1 1
5 2 code 1
6 3 price 1
7 3 quantity 1

entity_attribute:

Id entity_id attr_id column_id description
1 1 1 1 Первичный
ключ
2 1 2 2 Дата продажи
3 1 3 3 Суммарная
стоимость
4 2 1 4 Первичный
ключ
5 2 4 5 Ссылка на продажу
6 2 5 6 Код
товара
7 2 6 7 Цена
товара
8 2 7 8 Количество
товара

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

Для хранения описания мат. представлений можно использовать следующую схему:

image

Поскольку мат. представление также является сущностью, таблица mview ссылается на entity. Атрибуты mview_attribute аналогичным образом ссылаются на entity_attribute, для наследования описания атрибутов сущности мат. представления. Но, помимо этого, mview_attribute имеет еще одну ссылку (src_attr_id) на entity_attribute, определяющую, на базе какого исходного атрибута строится атрибут мат. представления.

То, как именно атрибут мат. представления участвует в агрегации, описывает таблица mview_attr_type. Таблица interval_type содержит справочник интервалов агрегации. Для примера, опишем простейшее мат. представления, построенное на базе запроса, показывающего, какое количество каждого товара было продано в день:

select trunc(a.transaction_date, 'DAY') transaction_date, 
       b.code code, sum(b.quantity) quantity
from   transaction_data a, transaction_detail b
where  b.transaction_id = a.id
group  by trunc(a.transaction_date, 'DAY'), b.code

Здесь мы считаем, что функция trunc(a.transaction_date, 'DAY') отсекает от даты продажи время, округляя дату до суток. Аналогичным образом 'WEEK' и 'MONTH' проводят округление до недели или месяца.

tables:

Id name description
3 mat_view Материализованное представление

columns:

Id table_id name description
9 3 transaction_date Дата продажи
10 3 code Код товара
11 3 quantity Количество товара

entity:

Id entity_type_id table_id name description
3 1 3 mat_view Материализованное представление

entity_attribute:

Id entity_id attr_id column_id description
9 3 4 9 Дата продажи
10 3 5 10 Код товара
11 3 7 11 Количество товара

interval_type:

Id name description
1 DAY Сутки
2 WEEK Неделя
3 MONTH Месяц
4 YEAR Год

mview_attr_type:

Id interval_type_id name description
1 KEY Ключ агрегирования
2 1 DAY Округление даты до суток
3 2 WEEK Округление даты до недели
4 3 MONTH Округление даты до месяца
5 4 YEAR Округление даты до года
6 SUM Сумма

mview:

Id entity_id description
1 3 Материализованное представление

mview_attribute:

Id type_id mview_id attribute_id src_attr_id
1 2 1 9 2
2 1 1 10 6
3 6 1 11 8

Партиционирование может быть описано следующей схемой:

image

С каждой таблицей может быть связано несколько партиций, каждая из которых уникально идентифицируется набором part_key. Физически, будут храниться не таблицы описанные в tables, а партиции (в виде таблиц БД), описанные parts. В качестве примера, партиционируем построенное нами мат.представление по дате продажи, с интервалом 1 месяц.

parts:

Id table_id name
1 3 mat_view_000001
2 3 mat_view_000002
3 3 mat_view_000004

part_key:

Id part_id attr_id min_value max_value
1 1 9 20120101 20120201
2 2 9 20120201 20120301
3 3 9 20120301 20120401

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

Например, если нас заинтересует количество проданного товара за два месяца, мы оперируя именами сущностей и атрибутов так, как если бы это были реальные таблицы и столбцы, напишем следующий запрос:

select code, sum(quantity)
from   thransaction_detail
where  transaction_date >= '20120201' and transaction_date < '20120401'
group  by code

После автоматического переписывания, запрос должен обрести следующий вид:

select code, sum(quantity)
from  ( select code code, quantity quantity
        from   mat_view_000001
        where  transaction_date >= '20120201' and transaction_date < '20120401'
        union  all
        select code, quantity
        from   mat_view_000002 
        where  transaction_date >= '20120201' and transaction_date < '20120401')
group   by code

Возможно, это не самая удачная идея, но мне, почему-то, очень захотелось ей заняться.

Автор: GlukKazan

Источник

Поделиться

* - обязательные к заполнению поля