Отчеты по всем клиентам с использованием распределенных запросов

Применимо к:База данных SQL Azure

В этом руководстве выполняются распределенные запросы ко всему набору баз данных клиента, чтобы обеспечить формирование отчетов. С помощью таких запросов можно найти интересные сведения в огромных объемах текущих операционных данных клиентов SaaS Wingtip Tickets. Для этого разверните дополнительную базу данных отчетов на сервере каталога и примените эластичные распределенные запросы.

Из этого руководства вы узнаете следующее:

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

Для работы с этим руководством выполните следующие предварительные требования:

• Развернуть SaaS-приложение Wingtip Tickets c однотенантной базой данных. См. инструкции из руководства по быстрому развертыванию SaaS-приложение Wingtip Tickets c однотенантной БД.
• Установите Azure PowerShell. Дополнительные сведения см. в статье Начало работы с Azure PowerShell.
• Установите SQL Server Management Studio (SSMS). Сведения о том, как скачать и установить SSMS, см. в статье Скачивание SQL Server Management Studio (SSMS).

Шаблон отчетов по всем клиентам

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

Доступ к этим данным получить очень просто, если они содержатся в отдельной мультитенантной базе данных, но не так просто, если они распределены по тысячам масштабируемых баз данных. Одним из подходов является использование эластичного запроса, который позволяет выполнять запросы в распределенном наборе баз данных с общей схемой. Этот набор может включать базы данных из нескольких групп ресурсов и разных подписок, но в них нужно создать общую учетную запись. Эластичный запрос использует отдельную головную базу данных, в которой внешние таблицы определены как зеркальные таблицы или представления в распределенных (клиентских) базах данных. Запросы, отправленные к этой головной базе данных, компилируются для создания распределенного плана запроса с частями запроса, которые при необходимости можно принудительно установить в клиентских базах данных. Эластичный запрос использует карту сегментов в базе данных каталога для определения расположения всех клиентских баз данных. При настройке и выполнении запроса к головной базе данных используется стандартный язык Transact-SQL и поддерживаются настраиваемые запросы из специализированных инструментов, таких как Power BI и Excel.

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

Получение скриптов для SaaS-приложения Wingtip Tickets c однотенантной БД

Сценарии для приложения SaaS Wingtip Tickets c мультитенантной базой данных и исходный код этого приложения вы найдете в репозитории GitHub WingtipTicketsSaaS-DbPerTenant. Инструкции по скачиванию и разблокированию сценариев приложения SaaS Wingtip Tickets см. в статье Общие рекомендации по работе с примерами приложений SaaS Wingtip Tickets.

Создание данных о продажах билетов

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

1. В среде сценариев PowerShell откройте скрипт ...\Обучение Modules\Operations Analytics\Adhoc Reporting\Demo-AdhocReporting.ps1 и задайте следующее значение:
   • $DemoScenario = 1, чтобы приобрести билеты на мероприятия во всех местах проведения.
2. Нажмите клавишу F5, чтобы запустить сценарий и создать данные о продаже билетов. Пока сценарий выполняется, продолжите процедуру, описанную в этом руководстве. В разделе Run ad hoc distributed queries (Выполнение запросов автоматизированной системы отчетности) идет обращение к данным о билетах, поэтому дождитесь, пока генератор данных билетов завершит работу.

Обзор глобальных представлений

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

Чтобы смоделировать такой подход, в базу данных клиента добавлен набор глобальных представлений, которые проецируют идентификатор клиента на каждую из таблиц, для которых выполняются глобальные запросы. Например, представление VenueEvents добавляет вычисляемый столбец VenueId к столбцам, спроецированным из таблицы Events. Точно так же представления VenueTicketPurchases и VenueTickets добавляют вычисляемый столбец VenueId, к данным из соответствующих таблиц. Эластичный запрос использует представления со столбцами VenueId, чтобы разделить запросы на несколько параллельных запросов и передать их соответствующим удаленным клиентским базам данных. Это значительно сокращает объем возвращаемых данных и приводит к заметному увеличению производительности многих запросов. Эти глобальные представления создаются заранее во всех клиентских базах данных.

1. Откройте среду SSMS и подключитесь к серверу tenants1-<пользователь>.

2. Разверните узел Базы данных, щелкните contosoconcerthall правой кнопкой мыши и выберите Создать запрос.

3. Выполните следующие запросы, чтобы увидеть разницу между таблицами одного клиента и глобальными представлениями:

   -- The base Venue table, that has no VenueId associated.
   SELECT * FROM Venue
   
   -- Notice the plural name 'Venues'. This view projects a VenueId column.
   SELECT * FROM Venues
   
   -- The base Events table, which has no VenueId column.
   SELECT * FROM Events
   
   -- This view projects the VenueId retrieved from the Venues table.
   SELECT * FROM VenueEvents
   

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

Вот как можно просмотреть определение представления Venues.

1. В обозревателе объектов разверните узел contosoconcerthall>Представления:

   

2. Щелкните dbo.Venues правой кнопкой мыши.

3. Выберите Создать скрипт для представления>Используя CREATE>В новом окне редактора запросов.

Создайте сценарий, используя любые другие представления Venue, чтобы увидеть, как они добавляют VenueId.

Развертывание базы данных, используемой для распределенных запросов

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

1. В интегрированной среде сценариев PowerShell откройте файл ...\Learning Modules\Operational Analytics\Adhoc Reporting\Demo-AdhocReporting.ps1.

2. Задайте для $DemoScenario значение 2, чтобы развернуть базу данных автоматизированной системы отчетности.

3. Нажмите клавишу F5, чтобы выполнить скрипт и создать базу данных adhocreporting.

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

Настройка головной базы данных для выполнения распределенных запросов

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

1. Откройте SQL Server Management Studio и подключитесь к базе данных автоматизированной системы отчетности, которую вы создали на предыдущем шаге. Это база данных с именем adhocreporting.

2. В SSMS откройте файл ...\Learning Modules\Operational Analytics\Adhoc Reporting\ Initialize-AdhocReportingDB.sql.

3. Просмотрите этот сценарий SQL и обратите внимание на следующее.

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

   

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

   

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

   

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

   

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

4. Нажмите клавишу F5, чтобы выполнить скрипт и инициализировать базу данных adhocreporting.

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

Выполнение распределенных запросов

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

При проверке плана выполнения наведите указатель мыши на значки плана, чтобы получить дополнительные сведения.

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

1. В SSMS откройте файл ...\Learning Modules\Operational Analytics\Adhoc Reporting\Demo-AdhocReportingQueries.sql.

2. Убедитесь в наличии подключения к базе данных adhocreporting.

3. В меню Запрос выберите Включить действительный план выполнения.

4. Выделите запрос Which venues are currently registered? и нажмите клавишу F5.

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

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

   

5. Выберите следующий запрос и нажмите клавишу F5.

   Этот запрос объединяет данные из клиентских баз данных и локальной таблицы VenueTypes (она считается локальной, поскольку размещена в базе данных adhocreporting).

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

   

6. Теперь выберите запрос On which day were the most tickets sold? и нажмите клавишу F5.

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

   

Следующие шаги

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

• как выполнить распределенные запросы по всем клиентским базам данных.
• как развернуть базу данных системы отчетности и определить схему для выполнения распределенных запросов.

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

Дополнительные ресурсы

• Дополнительные руководства по SaaS-приложению Wingtip Tickets c однотенантной БД.
• Обзор эластичных запросов к базе данных SQL Azure (предварительная версия)