Подключение схем баз данных к библиотекам в метаданных SAS — очень важный этап в настройке функциональной и удобной среды для бизнес-пользователей. Эта задача может быть довольно сложной для неподготовленного администратора. В этой статье рассматриваются ключевые параметры, которые часто остаются незамеченными, но могут иметь большое значение. Использование неправильных параметров может привести к снижению производительности базы данных или даже к полной блокировке базы данных, в зависимости от количества подключений.
Введение
Когда я начал управлять своей первой средой SAS с помощью SAS management console, я быстро понял, что подключение внешних баз данных может быть сложной задачей. Настройка сервера довольно проста, но настройка библиотек — совершенно другое дело. Мастер создания библиотеки не сложен, но на вкладке с расширенными параметрами отображаются несколько параметров, которые изменяются от одной СУБД к другой. Игнорирование этих параметров и использование параметров по умолчанию приведет к проблемам с производительностью.
В течение многих лет я старался найти лучший способ подключения внешних баз данных, особенно в том, что касается определения библиотеки. Через некоторое время я понял, что лучше создать набор рекомендаций, а затем улучшить его совместно с другими экспертами.
То, что вы найдете в этой статье — результат многолетней работы с подключениями к внешним базам данных. Это мой скромный вклад в создание рекомендаций для подключения СУБД в SAS management console. Мы не будем рассматривать настройку параметров для серверной части в SAS management console, так как она не представляет сложностей. Начнем с того, что, когда это возможно, следует использовать собственные коннекторы, а не коннекторы ODBC. Использование собственных коннекторов, как правило, позволяет SAS выполнять больший набор действий с СУБД.
Эта статья поможет понять, как SAS обрабатывает запросы к внешним базам данных, и оптимизировать свой код.
Настройка базы данных
После того как соединение с сервером определено в разделе server manager section в SAS management console, можно указать базовое определение библиотеки. Чтобы запустить мастер определения библиотеки, щелкните правой кнопкой мыши на "Libraries" в разделе "Data Library Manager".
Рисунок 1. Запуск мастера создания новой библиотеки
Настройка первой библиотеки не представляет сложностей за исключением выбора сервера, который может получить доступ к вашей библиотеке. Сервер необходимо выбрать сразу после указания типа библиотеки (Base, Oracle ...). В стандартной конфигурации или конфигурации по умолчанию можно выбрать один из двух следующих серверов:
- SASMeta
- SASApp
Если вы новичок в администрировании SAS и во всем мире метаданных SAS, скорее всего, вы не знаете, в чем состоит разница между этими двумя серверами. Если это так, выберите SASApp. Этот сервер подходит для обычного использования большинством пользователей (SAS/Enterprise Guide, SAS/Enterprise Miner ...).
Открытие ящика пандоры
При настройке новой библиотеки в мастере указан набор основных параметров, которые представляют основу для работы библиотеки. В большинстве случаев основных параметров достаточно для работы библиотеки. Однако для обеспечения надлежащей производительности и функциональности библиотеки решающее значение имеет настройка расширенных параметров.
Чтобы получить доступ к дополнительным параметрам, откройте свойства новой библиотеки. Доступ к этим свойствам также можно получить во время начальной настройки библиотеки. Изменять свойства рекомендуется поэтапно. Также следует отметить, что при настройке дополнительных параметров во время начальной настройки возможны проблемы в более ранних версиях SAS management console.
Предварительно назначенные (PRE-ASSIGNED) библиотеки
Первый из дополнительных параметров определяет, является ли библиотека предварительно назначенной. Если библиотека не была предварительно назначена, пользователь должен настроить соединение перед использованием библиотеки. В этой статье мы сосредоточимся исключительно на предварительно назначенных библиотеках.
Рисунок 2. Дополнительные параметры: Вкладка "Предварительное назначение"
Второй пункт настройки — выбор типа "Предварительное назначение" (Pre-Assignment). Очень важно понимать различие между возможными значениями и понимать поведение SAS в каждом случае.
- С помощью собственных механизмов библиотеки (By native library engines)
Этот вариант выбран по умолчанию. Для библиотек СУБД этот параметр позволяет обеспечивать безопасность на стороне базы данных. Для соединений с внешними базами данных следует выбирать именно этот параметр.
- С помощью механизма библиотеки метаданных (By metadata library engine)
Этот вариант используется для управления доступом на уровне метаданных. Для базовых библиотек SAS следует использовать этот вариант, но когда дело доходит до внешних библиотек, необходимо соблюдать осторожность. Управление безопасностью в таких соединениях рекомендуется выполнять на источнике (в реальной базе данных).
- С помощью внешней конфигурации (By external configuration)
Этот вариант следует учитывать, если вы хотите вынести конфигурацию библиотек из метаданных (например: файл autoexec). Пример использования — среда с несколькими серверами, связанными с помощью SAS/Connect.
На первый взгляд, может показаться, что metadata library engine не имеет очевидных недостатков. Однако, важно понимать как SAS работает с этими соединениями. Для этого типа взаимодействия информация о безопасности представлена в метаданных. Кроме того, в некоторых случаях SAS может обрабатывать запросы самостоятельно, а не передавать их в базу данных. В этих случаях нельзя использовать индексы DBMS, которые обычно используются для объединения данных. Это приводит к увеличению объема данных, отправляемых по сети, увеличению количества ресурсов, требуемых для работы SAS, и к увеличению времени, требуемого для обработки запроса СУБД и процессов SAS.
Рисунок 3. Представление передачи запроса СУБД
Обратите внимание, что при использовании metadata library engine обработка запросов может происходить по-разному (рис.3). В SQL Pass through обработка запроса передается на сторону СУБД, а в SAS driven join SQL они обрабатываются на стороне SAS
Чтобы гарантировать правильное поведение при подключении SAS к СУБД, необходимо выполнить тестирование. Очень удобный параметр для тестирования — “OPTIONS SASTRACE=',,,d' SASTRACELOC=SASLOG NOSTSUFFIX;”. При указании этого параметра в журнал SAS будут записаны подробные сведения об обработке запроса. Ниже приведен пример выходных данных при использовании этого параметра:
Выходные данные 1. Пример журнала, созданного SASTRACE
Уменьшение числа соединений
Следующие три вкладки на панели дополнительных параметров: "Входные данные", "Выходные данные" и "Входные/выходные данные". Они содержат параметры, связанные с взаимодействием с данными из внешней базы данных. Например, вкладка "Входные данные" позволяет переименовать столбцы с именами, которые были бы недопустимыми в таблице SAS. Вкладка "Выходные данные" позволяет изменить данные в СУБД (например, вставить, изменить или удалить данные). Эти три вкладки можно было бы рассмотреть более подробно, но с точки зрения общей производительности гораздо важнее следующая вкладка.
На вкладке "Соединение" содержатся параметры, с помощью которых вы сможете задать количество активных подключений к вашей внешней базе данных. С параметрами по умолчанию SAS будет устанавливать по одному соединению с внешней базой данных для каждой библиотеки. В некоторых случаях соединения так же возникают при обработке sql запросов. Количество этих соединений быстро растет. Количество сессий важно ограничить, так как большинство администраторов баз данных ограничивают количество одновременных подключений. По достижении этого ограничения создать новые соединения будет невозможно. Поскольку некоторые пользователи запускают сеансы только для взаимодействия с таблицами SAS, для этих пользователей устанавливать подключения не нужно.
Один из эффективных способов контроля количества сессий — использование параметра "отсрочки". При использовании этого параметра библиотеки подключаются только при использовании в первый раз. Таким образом, если библиотека не используется во время сессии SAS, для нее не будет выполнено подключение к базе данных.
Подключения утилит — это специальные дополнительные подключения, которые используются некоторыми СУБД, такими как DB2 и Netezza. Каждая такая библиотека может создавать собственные служебные подключения. После открытия эти подключения не освобождаются, пока не будет закрыт сеанс SAS. К счастью, с помощью расширенных параметров можно закрывать эти сеансы, когда подключение больше не требуется.
Тип подключения по умолчанию — SHAREREAD. Этот параметр можно использовать, чтобы ограничить количество активных подключений к СУБД. С ним используется одно и то же единственное подключение для всех операций чтения базы данных. Этот результат также можно получить с помощью групп подключений. Параметр "Имя группы подключений" позволяет администратору сгруппировать разные библиотеки вместе. Для этого необходимо указать одно и то же имя группы в этом поле. Этот параметр не требуется использовать в библиотеках с метаданными, однако с помощью такой группы легко идентифицировать библиотеки, подключающиеся к общей базе данных.
Обратите внимание, что для записи и изменения данных для задачи будет создано отдельное подключение.
Рисунок 4. Параметры подключения
Избежание блокировок в СУБД
Еще один важный аспект — тип доступа к базе данных. Хотя тип доступа не влияет на производительность, это важный параметр, который необходимо учитывать. По моему опыту, доступ по умолчанию хорошо подходит для подключений Oracle. Однако, когда дело доходит до DB2, необходимо установить уровень изоляции для чтения незафиксированных данных (UR), чтобы избежать блокировки таблиц. Для других СУБД необходимо проконсультироваться с администратором базы данных и получить рекомендации по настройке параметров.
Рисунок 5. Параметры блокировки
Оптимизация подключений
Последняя важная вкладка — "Оптимизация". Эти параметры определяют не количество подключений к базе данных, а то, как используются эти подключения. Среди прочего, они позволяют настраивать буферы чтения и записи.
При необходимости записи во внешнюю базу данных рекомендуется вручную настроить библиотеку с соответствующими параметрами. Оптимизация позволяет выполнять пользовательские настройки производительности, что невозможно при использовании статической общей библиотеки. Эта вкладка особенно полезна.
Если вы планируете изменить эти параметры, обязательно обратитесь к администратору базы данных. Изменение некоторых параметров может иметь опасные последствия, поэтому перед изменением убедитесь в том, что вы знаете, что делаете.
Рисунок 6. Вкладка "Оптимизация"
Сводим все вместе
Мы рассказали о различных параметрах, которые подходят для того, чтобы ограничить количество активных подключений к внешним базам данных. Для конфигурации с одной библиотекой эти приемы будут эффективными. Однако, когда дело доходит до подключения нескольких схем из одной удаленной СУБД к библиотекам, возникают различные проблемы. Необходимо гарантировать, что запросы будут отправляться в СУБД даже при использовании разных библиотек.
Чтобы обеспечить стабильность, все библиотеки, подключенные к базе данных, должны иметь одинаковые параметры и должны быть подключены к одному серверу базы данных. Небольшое различие между библиотеками (например, если вы забыли указать чтение незафиксированных данных) может заставить SAS обрабатывать объединение самостоятельно вместо того, чтобы передать его на обработку СУБД.
Чтобы гарантировать, что определения библиотек будут одинаковыми для заданной базы данных, разработайте рекомендации по определению библиотек и придерживайтесь их. Каждая база данных должна иметь свой собственный набор параметров. Документацию по настройке также можно найти в руководствах.
Другой способ — открыть инструкции для этих библиотек в SAS/Enterprise Guide и сравнить их. Инструкции доступны в свойствах библиотеки (контекстное меню). Как вы можете видеть в приведенном ниже примере, в разделе "Параметры" показаны подробные параметры подключения.
Рисунок 7. Окно свойств библиотеки в SAS / Enterprise Guide.
Заключение
Подключение внешних баз данных — нетривиальная задача. Если делать это без надлежащей осторожности, это может привести к серьезному изменению производительности. С глубоким знанием параметров библиотеки первый логичный шаг — выбор рекомендаций. Как и с базовым программированием для SAS, важно не вдаваться в детали низкого уровня, так как точки зрения различных экспертов могут быть различными. Необходимо определить рекомендации высокого уровня и переходить к деталям только тогда, когда это необходимо.
Первое, с чего нужно начать — постоянство. Используйте один и тот же рецепт при создании библиотек. Для удобства используйте один и тот же префикс для библиотек, которые указывают на схемы из одной базы данных. Так конечные пользователи смогут понять, с чем они работают. Используйте собственные механизмы библиотек для предварительно назначенных библиотек и делайте это для всех библиотек, подключенных к одной и той же СУБД. Убедитесь, что это постоянство относится к индивидуальным параметрам библиотек, подключенных к одной и той же внешней базе данных.
Во-вторых, настройте все возможные параметры (например, параметр отсрочки), чтобы ограничить количество подключений и общее время активности.
Информационные витрины данных часто используются совместно с другим программным обеспечением для бизнес-аналитики или для создания отчетов. Влияние на других пользователей SAS из-за блокировки (вследствие ограничения количества подключений к базе данных) уже является проблемой. Ситуация может ухудшиться, если затрагиваются пользователи, которые даже не используют SAS.
Наконец, используйте конкретные параметры для вашей СУБД. При необходимости уделите некоторое время тому, чтобы изучить и изменить эти параметры. Это поможет снизить влияние на используемые вами базы данных.