Выполнение запросов автоматизированной системы отчетности к нескольким базам данных (База данных SQL Azure)
Применимо к:
База данных SQL Azure
В этом руководстве вы выполните распределенные запросы ко всем клиентским базам данных, чтобы создать автоматизированную систему отчетности. С помощью таких запросов можно найти интересные сведения в огромных объемах текущих операционных данных приложения SaaS Wingtip Tickets. Чтобы выполнять такие запросы, вам нужно развернуть дополнительную базу данных на сервере каталога и применить эластичные распределенные запросы.
Из этого руководства вы узнаете следующее:
- как развертывать базу данных автоматизированной системы отчетности;
- как выполнять распределенные запросы по всем клиентским базам данных.
Для работы с этим руководством выполните следующие предварительные требования:
- Разверните приложение SaaS-приложение Wingtip Tickets c мультитенантной БД. Вы можете развернуть его менее чем за пять минут, используя инструкцию из статьи Deploy and explore a sharded multi-tenant application that uses Azure SQL Database (Развертывание и изучение сегментированного мультитенантного приложения, использующего Базу данных SQL Azure).
- Установите Azure PowerShell. Дополнительные сведения см. в статье Начало работы с Azure PowerShell.
- Установите SQL Server Management Studio (SSMS). Сведения о том, как скачать и установить SSMS, см. в статье Скачивание SQL Server Management Studio (SSMS).
Схема работы автоматизированной системы отчетности
Приложения SaaS позволяют анализировать огромное количество данных клиентов, которые централизованно хранятся в облаке. Такой анализ дает много полезных сведений о том, как работает приложение и как клиенты используют его. Эти сведения будут полезны при разработке функций, помогут повысить удобство использования, а также обеспечить вложение дополнительных средств в приложения и службы.
Доступ к этим данным получить очень просто, если они содержатся в отдельной мультитенантной базе данных, но не так просто, если они распределены по тысячам масштабируемых баз данных. Одним из подходов является использование эластичного запроса, который позволяет выполнять запросы в распределенном наборе баз данных с общей схемой. Этот набор может включать базы данных из нескольких групп ресурсов и разных подписок. Вам нужно лишь создать единую учетную запись и предоставить ей права на извлечение данных из всех баз данных. Эластичный запрос использует отдельную головную базу данных, в которой внешние таблицы определены как зеркальные таблицы или представления в распределенных (клиентских) базах данных. Запросы, отправленные к этой головной базе данных, компилируются для создания распределенного плана запроса с частями запроса, которые при необходимости можно принудительно установить в клиентских базах данных. Эластичный запрос использует карту сегментов в базе данных каталога для определения расположения всех клиентских баз данных. При настройке и выполнении запроса используется стандартный язык Transact-SQL и поддерживаются настраиваемые запросы из специализированных инструментов, таких как Power BI и Excel.
Благодаря распределению запросов между базами данных клиента эластичный запрос позволяет мгновенно получить представление о текущих производственных данных. Тем не менее, так как потенциально эластичный запрос извлекает данные из множества баз данных, задержка при выполнении запроса иногда может быть выше, чем для аналогичных запросов, отправляемых к отдельной мультитенантной базе данных. Чтобы сократить объем возвращаемых данных следует применить проектирование запросов. Как правило, эластичный запрос лучше всего подходит для получения небольших объемов данных в реальном времени, в отличие от часто используемых или сложных аналитических запросов или отчетов. Если запросы работают плохо, изучите план выполнения и узнайте, какая часть запроса передается на удаленную базу данных. Также вы можете оценить, сколько данных получено от базы данных. Чтобы хорошо выполнялись запросы, требующие сложной аналитической обработки, иногда будет полезным извлечь часть клиентских данных в выделенную базу данных и оптимизировать ее для аналитических запросов. Эта базу данных аналитики можно разместить в Базе данных SQL или Azure Synapse Analytics.
Эта аналитическая схема описана в руководстве по аналитическим запросам с использованием клиентов.
Получение скриптов и исходного кода для SaaS-приложения Wingtip Tickets c мультитенантной БД
Сценарии для приложения SaaS Wingtip Tickets c мультитенантной базой данных и исходный код этого приложения вы найдете в репозитории GitHub WingtipTicketsSaaS-MultitenantDB. Инструкции по скачиванию и разблокированию сценариев приложения SaaS Wingtip Tickets см. в статье Общие рекомендации по работе с примерами приложений SaaS Wingtip Tickets.
Создание данных о продажах билетов
Чтобы выполнять запросы к более интересному набору данных, создайте данные о продаже билетов, запустив генератор данных билетов.
- В среде сценариев PowerShell откройте скрипт ...\Learning Modules\Operations Analytics\Adhoc Reporting\Demo-AdhocReporting.ps1 и задайте следующие значения:
- $DemoScenario = 1, чтобы приобрести билеты на мероприятия во всех местах проведения.
- Нажмите клавишу F5, чтобы запустить сценарий и создать данные о продаже билетов. Пока сценарий выполняется, продолжите процедуру, описанную в этом руководстве. В разделе Run ad hoc distributed queries (Выполнение запросов автоматизированной системы отчетности) идет обращение к данным о билетах, поэтому дождитесь, пока генератор данных билетов завершит работу.
Изучение клиентских таблиц
В приложении SaaS мультитенантной базы данных Wingtip Tickets используется гибридная модель управления клиентами. Клиентские данные хранятся в нескольких мультитенантных базах данных и базах данных одного клиента, а также могут перемещаться между ними. При этом важно, чтобы в эластичном запросе к нескольким клиентским базам данных все эти данные можно было рассматривать как часть логически единой базы данных, сегментированной по клиентам.
Для реализации такой схемы работы во всех клиентских таблицах есть столбец VenueId, указывающий на клиента, которому принадлежат соответствующие данные. Здесь значение VenueId вычисляется как хэш от названия объекта (Venue), но вы можете применить любой другой метод получения уникальных значений. Аналогичным способом вычисляется в ключ клиента для использования в каталоге. Эластичный запрос использует таблицы со столбцами VenueId, чтобы разделить запросы на несколько параллельных запросов и передать их соответствующим удаленным клиентским базам данных. Это значительно сокращает объем возвращаемых данных и повышает производительность, особенно при большом числе клиентов, данные которых хранятся в базах данных одного клиента.
Развертывание базы данных, используемой для распределенных запросов автоматизированной системы отчетности
Этот пример развертывает базу данных adhocreporting. Это головная база данных, которая будет содержать схему, используемую для выполнения запросов по всем базам данных клиента. Эта база данных развертывается на имеющемся сервере каталога, используемом для всех баз данных для управления в данном примере приложения.
В интегрированной среде сценариев PowerShell откройте файл ...\Learning Modules\Operational Analytics\Adhoc Reporting\Demo-AdhocReporting.ps1 и задайте в нем следующие значения.
- $DemoScenario = 2, Развертывание базы данных автоматизированной системы отчетности.
Нажмите клавишу F5, чтобы выполнить скрипт и создать базу данных adhocreporting.
В следующем разделе вы добавите схему в базу данных, чтобы ее можно было использовать для выполнения распределенных запросов.
Настройка головной базы данных для выполнения распределенных запросов
Этот пример добавляет в базу данных автоматизированной системы отчетности схему (внешний источник данных и определения внешних таблиц), которая позволяет выполнять запросы ко всем клиентским базам данных.
Откройте SQL Server Management Studio и подключитесь к базе данных автоматизированной системы отчетности, которую вы создали на предыдущем шаге. Это база данных с именем adhocreporting.
В SSMS откройте файл ...\Learning Modules\Operational Analytics\Adhoc Reporting\ Initialize-AdhocReportingDB.sql.
Просмотрите этот сценарий SQL и обратите внимание на следующее.
Эластичный запрос использует учетные данные уровня базы данных для доступа к каждой базе данных клиента. Эти учетные данные должны присутствовать во всех базах данных. Обычно им предоставляются минимальные права, необходимые для поддержки запросов автоматизированной системы отчетности.
При использовании каталога базы данных в качестве внешнего источника данных запросы распределяются по всем базам данных, зарегистрированным в каталоге, при выполнении. Так как имена серверов для каждого развертывания отличаются, этот сценарий инициализации получает расположение каталога базы данных, извлекая имя текущего сервера (@@servername), на котором выполняется сценарий.
В определении внешних таблиц, которые ссылаются на клиентские таблицы, указан параметр DISTRIBUTION = SHARDED(VenueId). Эта инструкция перенаправляет запрос для определенного значения VenueId в соответствующую базу данных, что во многих сценариях повышает производительность запроса, как показано в следующем разделе.
Локальная таблица VenueTypes, которая создается и заполняется. Эта таблица эталонных данных является общей во всех базах данных клиента, поэтому здесь ее можно представить в качестве локальной таблицы и заполнить общими данными. Для некоторых запросов это может уменьшить объем данных, перемещаемых между базами данных клиента и базой данных adhocreporting.
В случае добавления ссылочных таблиц таким способом необходимо обновлять схему и данные таблицы при каждом обновлении базы данных клиента.
Нажмите клавишу F5, чтобы выполнить скрипт и инициализировать базу данных adhocreporting.
Теперь можно выполнять распределенные запросы и собирать данные аналитики по всем клиентам.
Выполнение распределенных запросов автоматизированной системы отчетности
Теперь, когда база данных adhocreporting настроена, выполните некоторые распределенные запросы. Добавьте план выполнения, чтобы лучше понимать, где происходит обработка запросов.
При проверке плана выполнения наведите указатель мыши на значки плана, чтобы получить дополнительные сведения.
В SSMS откройте файл ...\Learning Modules\Operational Analytics\Adhoc Reporting\Demo-AdhocReportingQueries.sql.
Убедитесь в наличии подключения к базе данных adhocreporting.
В меню Запрос выберите Включить действительный план выполнения.
Выделите запрос Which venues are currently registered? и нажмите клавишу F5.
Запрос вернет полный список мест проведения, демонстрируя, как быстро и легко можно выполнить запрос по всем клиентам и вернуть данные из каждого из них.
Просмотрите план, и вы увидите, что все затраты связаны исключительно с удаленным запросом, так как мы просто перебираем базы данных клиента и выбираем информацию о местах проведения.
Выберите следующий запрос и нажмите клавишу F5.
Этот запрос объединяет данные из клиентских баз данных и локальной таблицы VenueTypes (она считается локальной, поскольку размещена в базе данных adhocreporting).
Просмотрите план, и вы увидите, что большая часть затрат связана с удаленным запросом, так как мы запрашиваем информацию о месте проведения каждого клиента (dbo.Venues), а затем быстро выполняем локальное соединение с локальной таблицей VenueTypes, чтобы отобразить понятные имена.
Теперь выберите запрос On which day were the most tickets sold? и нажмите клавишу F5.
Этот запрос выполняет более сложное соединение и агрегирование. Важно отметить, что большая часть обработки выполняется удаленно, и опять же, мы возвращаем только те строки, которые необходимы, получая только одну строку для общего числа проданных билетов за день для каждого места проведения.
Следующие шаги
Из этого руководства вы узнали, как выполнять такие задачи:
- как выполнить распределенные запросы по всем клиентским базам данных.
- как развернуть базу данных автоматизированной системы отчетности и добавить в нее схему для выполнения распределенных запросов.
Теперь ознакомьтесь с руководством Межклиентская аналитика с помощью извлеченных данных, чтобы изучить извлечение данных в отдельную базу данных аналитики для более сложной аналитической обработки.