Оптимизатор - Разработка программного средства, позволяющего оптимизировать SQL-скрипты

Задача оптимизатора в рамках данной дипломной работы - исправлять части SQL-кода, которые могут приводить к дополнительным тратам памяти и ресурсов.

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

    - Наличие промежуточных шагов, которые не используются при создании финальных витрин - Протянутые в промежуточные таблицы поля, не нужные для расчета финальных показателей - Соединения на таблицы, поля из которых не используются в текущем шаге - Выбор индекса, использование которого приводит к значительному перекосу в хранении данных или к тратам дополнительных ресурсов CPU при перекладывании таблицы для ее соединения с другими объектами далее в скрипте - Повторяющийся код

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

1. Забытые промежуточные шаги

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

В качестве примера можно привести простой отчет по балансам новых клиентов (Приложение).

Исходные данные:

    - AGG_DB. CLIENTS - информация по клиентам - DET_DB. CLIENT_ACCOUNT - связка клиент-счет - DET_DB. ACCOUNT_BALANCE - баланс на счету (детальная информация) - AGG_DB. CLIENT_BAL_MONTHLY - баланс на счетах клиента на конец месяца (агрегированная информация)

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

Разработанное программное средство определяет такие случаи. После разбора скрипта те таблицы, которые нигде не используются, помечаются как финальные. Далее у пользователя запрашивается (рис. 6.), какие из таблиц являются итоговыми на самом деле.

запрос на выделение финальных витрин

Рис. 6 Запрос на выделение финальных витрин

Если найденная таблица не является целевой и пользователь снимет отметку об этом, она будет убрана из скрипта.

2. Лишние поля в промежуточных таблицах

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

Пример - слегка модифицированная версия отчета о балансе новых клиентов на конец месяца (Приложение).

Исходные данные:

    - AGG_DB. CLIENTS - информация по клиентам - AGG_DB. CLIENT_BAL_MONTHLY - баланс на счетах клиента на конец месяца (агрегированная информация)

В первом шаге также выбирается информация по новым клиентам, во втором - подтягивается баланс. Допустим, в какой-то момент считалось, что в отчете должно быть поле ИНН - IND_TAX_PAYER_NUM, потом же от этой идеи отказались, из финальной таблицы поле убрали, а из промежуточной - нет.

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

3. Соединение на неиспользуемые таблицы

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

Другой вид соединения - inner join - может в таком случае играть роль фильтра - отсекать записи одной из таблиц, которым нет соответствия в другой, а left join, при котором данные присоединяемой таблицы не используются ни в select, ни в последующих соединениях, ни в фильтрации, на самом деле оказывается просто лишней тратой ресурсов. Такой join может привести к слабо контролируемому дублированию данных, источник которого сложно найти, что будет влиять на результаты и сроки тестирования.

В качестве примера возьмем выборку по клиентам (Приложение).

Исходные данные:

    - AGG_DB. CLIENTS - информация по клиентам - AGG_DB. CLIENTS_DETAILED - дополнительная информация по клиентам, доступ к которой производится нечасто, из-за чего ее вынесли отдельно.

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

Разработанный оптимизатор определяет тип соединения, используется ли присоединяемая таблица в текущем шаге, и, если она не используется при соединении типа left join, убирает ее.

4. Неоптимальный выбор индекса

Выбор первичного индекса - один из ключевых моментов при разработке sql-скриптов для Teradata. В зависимости от него будет определено, какой метод соединения будет выбран, и где будут соединяться таблицы - будет ли дополнительное перераспределение или дублирование данных по всем узлам.

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

    - По каким полям будет в дальнейшем происходить обращение к таблице - По каким полям происходит соединение внутри шага - Какие поля используются при группировке - Какие поля используются в части partition by конструкции qualify (если эта часть есть, то она будет выполняться последней, и эти поля будут наиболее вероятными кандидатами для PI, особенно если таблица в дальнейшем используется по одному из них).

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

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

Так, оптимизатору подается файл с описанием шаблонов названий исходных таблиц и того, сколько они могут занимать места.

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

Можно выделить несколько шаблонов названий и оценить по шкале (для простоты - от 1 до 3), каким будет размер этих таблиц относительно друг друга.

На работу оптимизатора также будет влиять, в каком из трех режимов работает программа. Рассмотрим, как именно.

Три режима работы (применительно к оптимизации)

- Первый - самый простой, просто анализ синтаксиса запросов.

В этом режиме работы программе можно указать только приблизительные размеры таблиц относительно друг друга. Эта настройка разовая - не требуется при каждом запуске вводить нужные значения.

- Второй - плюс чтение данных из файла

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

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

- Поля, имеющиеся в исходных таблицах

Этот файл представляет собой список полей в каждой из исходных таблиц. Его наличие позволяет разобрать конструкции вида "select * from" и оптимизировать их, потому как часто не все поля, которые выбираются таким образом, особенно на начальных этапах, используются далее в скрипте.

Данные по полям таблиц и представлений содержатся в системной информации Teradata - представлении DBC. ColumnsV.

- Индексы таблиц, их размеры и skew

В данном файле должна содержаться информация по каждой таблице (и исходной, и создаваемой скриптом) - ее название, индекс, размер в Мб и фактор перекоса.

Вся эта информация также содержится в системных таблицах Teradata. Индексы - DBC. Indices, размеры таблиц - DBC. TableSize. Показатель skew вычисляется на основе размеров таблиц.

- Третий - с подсоединением к базе

Данный режим позволяет учесть наибольшее количество факторов по сравнению с другими, плюс он не требует от пользователя ввода информации по индексам, размерам и полям - все это он может программа получает сама. В дополнение к разбору этой информации появляется возможность проверять кандидатов на PI с помощью функций hashamp(hashbucket(hashrow())) и group by - смотреть, на какой из AMP'ов будут попадать данные при выборе PI, и как строки будут распределены между AMP'ами.

Подходящий для таблицы PI определяется по взвешенной сумме факторов. Поля анализируются по двум критериям: входные данные для витрины, какие поля используются в самом шаге, и витрина как исходная для следующих шагов - как будет использоваться в дальнейшем.

Анализ самого шага, для которого ищем индекс. Надо проверить, какие поля из центральной таблицы (обычно - первая таблица в части from) используются в соединении. Хорошие кандидаты для PI - условия равенства на поле (поля), присоединяемые по таким условиям таблицы должны быть либо больше, либо примерно того же размера, что и основная таблица. Если присоединяемая таблица маленького размера и есть возможность установить этот факт по каким-либо из исходных файлов или с помощью запроса напрямую к базе, полю отдается меньший приоритет.

Чем больше идет соединений на поле в текущем шаге, тем большим будет его вес. Также надо учитывать, откуда берется поле-кандидат на индекс. Если оно является PI в исходной витрине, то к весу поля добавляем дополнительные баллы.

Анализ зависимых от текущего шагов.

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

Если оптимизатор находит несколько кандидатов для PI, сообщение об этом выводится в комментарии к шагу.

Пример (Приложение).

Скрипт содержит формирование двух таблиц: счета клиентов (TEST_DB. CLIENT_ACC) и балансы на счете (TEST_DB. CLIENT_BAL). Первая таблица формируется путем соединения двух исходных таблиц по полю CLIENT_ID. Во второй же используется соединение по ACCOUNT_ID. PI первой таблицы в данном примере - (ACCOUNT_ID, CLIENT_ID). Такой индекс может давать более равномерное распределение данных по AMP - у каких-то клиентов счетов может быть много, если они попадут на один AMP, может быть перекос в хранении. Однако, такой PI в данном случае - не лучший выбор, потому что в первой таблице используется соединение по CLIENT_ID, и если таблицы большие, то они обе будут переложены под этот PI, соответственно, первый шаг будет удобно сразу оставить с таким же индексом, перекос в данном случае не должен быть сильно выражен. Также первой таблице можно поставить индекс ACCOUNT_ID, т. к. далее она будет использоваться по этому полю.

5. Повторяющийся код

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

Рассмотрим пример (Приложение - Исходный код).

Данный SQL-скрипт формирует отчет по типам кредитных договоров. Рассматриваются активные договоры - есть задолженность в текущем месяце, закрытые - в прошлом месяце была задолженность, а в этом погасили, и реструктурированные - в прошлом месяце была задолженность, в этом погасили, плюс появилась запись со связкой нового договора (после реструктуризации) и старого (до нее). Для каждого типа договора выбирается информация из исходной витрины - (AGG_DB. AGREEMENT_BALANCE_MONTH), активные - те, у которых есть задолженность, закрытые и реструктурированные - нет, для определения, закрыли ли договор или нет, идет соединение на таблицу реструктуризаций (AGG_DB. AGREEMENTS_RESTRUCT). Если нашли в ней соответствие, то договор был реструктурирован, иначе - просто закрыт. Такие таблицы могут быть достаточно большими. Чтобы не читать информацию много раз, можно сначала выбрать информацию о балансах, после чего сделать одно соединение на таблицу реструктуризаций вместо двух, а условия на балансы и наличие совпадений в витринах вынести далее в скрипт. Пример того, как данный скрипт можно улучшить по количеству просмотров исходных таблиц и количеству соединений также можно посмотреть в приложении (Исправленный код).

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

Все приведенные выше примеры являются упрощением реальных случаев, возникавших при разработке ETL-процессов.

Похожие статьи




Оптимизатор - Разработка программного средства, позволяющего оптимизировать SQL-скрипты

Предыдущая | Следующая