Relatórios entre locatários usando consultas distribuídas

  • Artigo

Aplica-se a:Banco de Dados SQL do Azure

Neste tutorial, você executa consultas distribuídas em todo o conjunto de bancos de dados de locatários para relatórios. Essas consultas podem extrair informações enterradas nos dados operacionais diários dos locatários SaaS do Wingtip Tickets. Para fazer isso, implante um banco de dados de relatórios adicional no servidor de catálogo e use o Elastic Query para habilitar consultas distribuídas.

Neste tutorial, ficará a saber:

  • Como implantar um banco de dados de relatórios
  • Como executar consultas distribuídas em todos os bancos de dados de locatários
  • Como as exibições globais em cada banco de dados podem permitir consultas eficientes entre locatários

Para concluir este tutorial, devem ser cumpridos os seguintes pré-requisitos:

Padrão de relatório entre locatários

cross-tenant distributed query pattern

Uma oportunidade com aplicativos SaaS é usar a grande quantidade de dados do locatário armazenados na nuvem para obter informações sobre a operação e o uso do seu aplicativo. Essas informações podem orientar o desenvolvimento de recursos, melhorias de usabilidade e outros investimentos em seus aplicativos e serviços.

Aceder a estes dados numa única base de dados multi-inquilinos é fácil, mas não será tão fácil se houver uma distribuição à escala através de potencialmente milhares de bases de dados. Uma abordagem é usar o Elastic Query, que permite a consulta em um conjunto distribuído de bancos de dados com esquema comum. Esses bancos de dados podem ser distribuídos em diferentes grupos de recursos e assinaturas, mas precisam compartilhar um login comum. O Elastic Query usa um banco de dados de cabeçalho único no qual são definidas tabelas externas que espelham tabelas ou exibições nos bancos de dados distribuídos (locatário). As consultas submetidas para esta base de dados “head” são compiladas para produzir um plano de consultas distribuídas, com partes das consultas enviadas para as bases de dados inquilinas, conforme necessário. O Elastic Query usa o mapa de estilhaços no banco de dados de catálogo para determinar o local de todos os bancos de dados de locatário. A configuração e a consulta do banco de dados head são simples usando o Transact-SQL padrão e suportam consultas de ferramentas como Power BI e Excel.

Ao distribuir consultas entre os bancos de dados do locatário, o Elastic Query fornece informações imediatas sobre os dados de produção em tempo real. Como o Elastic Query extrai dados de bancos de dados potencialmente diversos, a latência da consulta pode ser maior do que consultas equivalentes enviadas a um único banco de dados multilocatário. Projetar consultas para minimizar os dados que são retornados ao banco de dados principal. O Elastic Query geralmente é mais adequado para consultar pequenas quantidades de dados em tempo real, em vez de criar consultas ou relatórios analíticos complexos ou usados com freqüência. Se as consultas não tiverem um bom desempenho, examine o plano de execução para ver qual parte da consulta é enviada para o banco de dados remoto e quantos dados estão sendo retornados. As consultas que exigem agregação complexa ou processamento analítico podem ser melhor tratadas extraindo dados do locatário para um banco de dados ou data warehouse otimizado para consultas de análise. Esse padrão é explicado no tutorial de análise de locatário.

Obter os scripts de aplicativo SaaS Database Wingtip Tickets por locatário

Os scripts Wingtip Tickets SaaS Multi-tenant Database e o código-fonte do aplicativo estão disponíveis no repositório GitHub WingtipTicketsSaaS-DbPerTenant . Confira as orientações gerais para conhecer as etapas para baixar e desbloquear os scripts SaaS do Wingtip Tickets.

Criar dados de vendas de bilhetes

Para executar consultas em um conjunto de dados mais interessante, crie dados de vendas de tíquetes executando o gerador de tíquetes.

  1. No ISE do PowerShell, abra o script ...\Learning Modules\Operational Analytics\Adhoc Reporting\Demo-AdhocReporting.ps1 e defina o seguinte valor:
    • $DemoScenario = 1, Compre ingressos para eventos em todos os locais.
  2. Pressione F5 para executar o script e gerar vendas de ingressos. Enquanto o script está em execução, continue as etapas neste tutorial. Os dados do ticket são consultados na seção Executar consultas distribuídas ad hoc, portanto, aguarde a conclusão do gerador de tíquetes .

Explore as visões globais

No aplicativo Wingtip Tickets SaaS Database Per Tenant, cada locatário recebe um banco de dados. Assim, os dados contidos nas tabelas do banco de dados têm como escopo a perspetiva de um único locatário. No entanto, ao consultar todos os bancos de dados, é importante que o Elastic Query possa tratar os dados como se fizessem parte de um único banco de dados lógico fragmentado pelo locatário.

Para simular esse padrão, um conjunto de exibições 'globais' é adicionado ao banco de dados do locatário que projeta uma ID de locatário em cada uma das tabelas consultadas globalmente. Por exemplo, o modo de exibição VenueEvents adiciona um VenueId computado às colunas projetadas da tabela Eventos. Da mesma forma, as visualizações VenueTicketPurchases e VenueTickets adicionam uma coluna VenueId computada projetada a partir de suas respetivas tabelas. Essas exibições são usadas pelo Elastic Query para paralelizar consultas e enviá-las para o banco de dados de locatário remoto apropriado quando uma coluna VenueId está presente. Isso reduz drasticamente a quantidade de dados retornados e resulta em um aumento substancial no desempenho de muitas consultas. Essas exibições globais foram pré-criadas em todos os bancos de dados de locatário.

  1. Abra o SSMS e conecte-se ao servidor tenants1-USER<>.

  2. Expanda Bancos de Dados, clique com o botão direito do mouse em contosoconcerthall e selecione Nova Consulta.

  3. Execute as seguintes consultas para explorar a diferença entre as tabelas de locatário único e as exibições globais:

    -- 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

Nestas vistas, o VenueId é calculado como um hash do Nome do local, mas pode ser utilizada qualquer abordagem para introduzir um valor exclusivo. Essa abordagem é semelhante à maneira como a chave do locatário é calculada para uso no catálogo.

Para examinar a definição da visualização Locais :

  1. No Pesquisador de Objetos, expanda Exibições contosoconcerthall>:

    Screenshot shows the contents of the Views node, including four types of Venue d b o.

  2. Clique com o botão direito do rato em dbo. Locais.

  3. Selecione Visualização de Script como>CREATE para>nova janela do Editor de Consultas

Crie scripts para qualquer um dos outros modos de exibição do Local para ver como eles adicionam o VenueId.

Implantar o banco de dados usado para consultas distribuídas

Este exercício implanta o banco de dados adhocreporting . Este é o banco de dados principal que contém o esquema usado para consulta em todos os bancos de dados de locatário. O banco de dados é implantado no servidor de catálogo existente, que é o servidor usado para todos os bancos de dados relacionados ao gerenciamento no aplicativo de exemplo.

  1. no PowerShell ISE, abra ...\Learning Modules\Operational Analytics\Adhoc Reporting\Demo-AdhocReporting.ps1.

  2. Defina $DemoScenario = 2, Implantar banco de dados de relatórios ad hoc.

  3. Pressione F5 para executar o script e criar o banco de dados adhocreporting .

Na próxima seção, você adiciona esquema ao banco de dados para que ele possa ser usado para executar consultas distribuídas.

Configurar o banco de dados 'head' para executar consultas distribuídas

Este exercício adiciona esquema (a fonte de dados externa e definições de tabela externa) ao banco de dados adhocreporting para permitir a consulta em todos os bancos de dados de locatário.

  1. Abra o SQL Server Management Studio e conecte-se ao banco de dados de Relatórios Adhoc criado na etapa anterior. O nome do banco de dados é adhocreporting.

  2. Abra ...\Learning Modules\Operational Analytics\Adhoc Reporting\ Initialize-AdhocReportingDB.sql no SSMS.

  3. Analise o script SQL e observe:

    O Elastic Query usa uma credencial de escopo de banco de dados para acessar cada um dos bancos de dados de locatário. Essa credencial precisa estar disponível em todos os bancos de dados e, normalmente, deve receber os direitos mínimos necessários para permitir essas consultas.

    create credential

    Com o banco de dados de catálogo como fonte de dados externa, as consultas são distribuídas para todos os bancos de dados registrados no catálogo no momento em que a consulta é executada. Como os nomes de servidor são diferentes para cada implantação, esse script obtém o local do banco de dados de catálogo do servidor atual (@@servername) onde o script é executado.

    create external data source

    As tabelas externas que fazem referência às exibições globais descritas na seção anterior e definidas com DISTRIBUTION = SHARDED(VenueId). Como cada VenueId é mapeado para um banco de dados individual, isso melhora o desempenho de muitos cenários, conforme mostrado na próxima seção.

    create external tables

    A tabela local VenueTypes que é criada e preenchida. Essa tabela de dados de referência é comum em todos os bancos de dados de locatário, portanto, pode ser representada aqui como uma tabela local e preenchida com os dados comuns. Para algumas consultas, ter essa tabela definida no banco de dados head pode reduzir a quantidade de dados que precisam ser movidos para o banco de dados head.

    create table

    Se você incluir tabelas de referência dessa maneira, atualize o esquema e os dados da tabela sempre que atualizar os bancos de dados do locatário.

  4. Pressione F5 para executar o script e inicializar o banco de dados adhocreporting .

Agora você pode executar consultas distribuídas e coletar informações em todos os locatários!

Executar consultas distribuídas

Agora que o banco de dados adhocreporting está configurado, execute algumas consultas distribuídas. Inclua o plano de execução para uma melhor compreensão de onde o processamento da consulta está acontecendo.

Ao inspecionar o plano de execução, passe o mouse sobre os ícones do plano para obter detalhes.

É importante observar que definir DISTRIBUTION = SHARDED(VenueId) quando a fonte de dados externa é definida melhora o desempenho para muitos cenários. Como cada VenueId mapeia para um banco de dados individual, a filtragem é facilmente feita remotamente, retornando apenas os dados necessários.

  1. Abra ...\Learning Modules\Operational Analytics\Adhoc Reporting\Demo-AdhocReportingQueries.sql no SSMS.

  2. Verifique se você está conectado ao banco de dados adhocreporting .

  3. Selecione o menu Consulta e clique em Incluir Plano de Execução Real

  4. Realce a consulta Que locais estão atualmente registados? e prima F5.

    A consulta retorna toda a lista de locais, ilustrando como é rápido e fácil consultar todos os locatários e retornar dados de cada locatário.

    Inspecione o plano e veja se todo o custo está na consulta remota. Cada banco de dados de locatário executa a consulta remotamente e retorna suas informações de local para o banco de dados principal.

    SELECT * FROM dbo.Venues

  5. Selecione a próxima consulta e pressione F5.

    Essa consulta une dados dos bancos de dados do locatário e da tabela VenueTypes local (local, pois é uma tabela no banco de dados adhocreporting).

    Inspecione o plano e veja que a maior parte do custo é a consulta remota. Cada banco de dados de locatário retorna suas informações de local e executa uma associação local com a tabela VenueTypes local para exibir o nome amigável.

    Join on remote and local data

  6. Agora selecione a consulta Em que dia foram mais vendidos os ingressos? e pressione F5.

    Esta consulta faz uma junção e agregação um pouco mais complexa. A maior parte do processamento ocorre remotamente. Apenas linhas individuais, contendo a contagem diária de venda de ingressos de cada local por dia, são retornadas ao banco de dados principal.

    query

Próximos passos

Neste tutorial, ficou a saber como:

  • Executar consultas distribuídas em todas as bases de dados do inquilino
  • Implante um banco de dados de relatórios e defina o esquema necessário para executar consultas distribuídas.

Agora, tente o tutorial do Tenant Analytics para explorar a extração de dados para um banco de dados de análise separado para um processamento de análise mais complexo.

Recursos adicionais