Geração de relatórios em bancos de dados em nuvem expandidos (visualização)

  • Artigo

Aplica-se a:Banco de Dados SQL do Azure

Query across shards

Os bancos de dados fragmentados distribuem linhas em uma camada de dados dimensionada. O esquema é idêntico em todos os bancos de dados participantes, também conhecido como particionamento horizontal. Usando uma consulta elástica, você pode criar relatórios que abrangem todos os bancos de dados em um banco de dados fragmentado.

Para obter um início rápido, consulte Relatórios em bancos de dados em nuvem expandidos.

Para bancos de dados não fragmentados, consulte Consulta em bancos de dados na nuvem com esquemas diferentes.

Pré-requisitos

  • Crie um mapa de estilhaços usando a biblioteca cliente de banco de dados elástico. consulte Gestão de mapas de estilhaços. Ou use o aplicativo de exemplo em Introdução às ferramentas de banco de dados elástico.
  • Como alternativa, consulte Migrar bancos de dados existentes para bancos de dados expandidos.
  • O usuário deve possuir a permissão ALTER ANY EXTERNAL DATA SOURCE. Esta permissão está incluída com a permissão ALTER DATABASE.
  • As permissões ALTER ANY EXTERNAL DATA SOURCE são necessárias para fazer referência à fonte de dados subjacente.

Descrição geral

Essas instruções criam a representação de metadados da camada de dados fragmentada no banco de dados de consulta elástica.

  1. CRIAR CHAVE MESTRA
  2. CREDENCIAL COM ÂMBITO DE BASE DE DADOS
  3. CRIAR UMA ORIGEM DE DADOS EXTERNA
  4. CRIAR TABELA EXTERNA

1.1 Criar chaves mestras e credenciais com escopo de banco de dados

A credencial é usada pela consulta elástica para se conectar aos bancos de dados remotos.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
CREATE DATABASE SCOPED CREDENTIAL [<credential_name>]  WITH IDENTITY = '<username>',  
SECRET = '<password>';

Nota

Certifique-se de que o "nome> de usuário" não inclui nenhum sufixo "<@servername".

1.2 Criar fontes de dados externas

Sintaxe:

<External_Data_Source> ::=
    CREATE EXTERNAL DATA SOURCE <data_source_name> WITH
        (TYPE = SHARD_MAP_MANAGER,
                   LOCATION = '<fully_qualified_server_name>',
        DATABASE_NAME = '<shardmap_database_name>',
        CREDENTIAL = <credential_name>,
        SHARD_MAP_NAME = '<shardmapname>'
               ) [;]

Exemplo

CREATE EXTERNAL DATA SOURCE MyExtSrc
WITH
(
    TYPE=SHARD_MAP_MANAGER,
    LOCATION='myserver.database.windows.net',
    DATABASE_NAME='ShardMapDatabase',
    CREDENTIAL= SMMUser,
    SHARD_MAP_NAME='ShardMap'
);

Recupere a lista de fontes de dados externas atuais:

select * from sys.external_data_sources;

A fonte de dados externa faz referência ao seu mapa de estilhaços. Em seguida, uma consulta elástica usa a fonte de dados externa e o mapa de fragmentos subjacente para enumerar os bancos de dados que participam da camada de dados. As mesmas credenciais são usadas para ler o mapa de estilhaços e para acessar os dados nos fragmentos durante o processamento de uma consulta elástica.

1.3 Criar tabelas externas

Sintaxe:

CREATE EXTERNAL TABLE [ database_name . [ schema_name ] . | schema_name. ] table_name  
    ( { <column_definition> } [ ,...n ])
    { WITH ( <sharded_external_table_options> ) }
) [;]  

<sharded_external_table_options> ::=
  DATA_SOURCE = <External_Data_Source>,
  [ SCHEMA_NAME = N'nonescaped_schema_name',]
  [ OBJECT_NAME = N'nonescaped_object_name',]
  DISTRIBUTION = SHARDED(<sharding_column_name>) | REPLICATED |ROUND_ROBIN

Exemplo

CREATE EXTERNAL TABLE [dbo].[order_line](
     [ol_o_id] int NOT NULL,
     [ol_d_id] tinyint NOT NULL,
     [ol_w_id] int NOT NULL,
     [ol_number] tinyint NOT NULL,
     [ol_i_id] int NOT NULL,
     [ol_delivery_d] datetime NOT NULL,
     [ol_amount] smallmoney NOT NULL,
     [ol_supply_w_id] int NOT NULL,
     [ol_quantity] smallint NOT NULL,
      [ol_dist_info] char(24) NOT NULL
)

WITH
(
    DATA_SOURCE = MyExtSrc,
     SCHEMA_NAME = 'orders',
     OBJECT_NAME = 'order_details',
    DISTRIBUTION=SHARDED(ol_w_id)
);

Recupere a lista de tabelas externas do banco de dados atual:

SELECT * from sys.external_tables;

Para descartar tabelas externas:

DROP EXTERNAL TABLE [ database_name . [ schema_name ] . | schema_name. ] table_name[;]

Observações

A cláusula DATA_SOURCE define a fonte de dados externa (um mapa de estilhaços) que é usada para a tabela externa.

As cláusulas SCHEMA_NAME e OBJECT_NAME mapeiam a definição de tabela externa para uma tabela em um esquema diferente. Se omitido, o esquema do objeto remoto é assumido como sendo e seu nome é assumido como idêntico ao nome da tabela externa que está sendo dbo definida. Isso é útil se o nome da tabela remota já estiver no banco de dados onde você deseja criar a tabela externa. Por exemplo, você deseja definir uma tabela externa para obter uma exibição agregada de exibições de catálogo ou DMVs em sua camada de dados dimensionada. Como as exibições de catálogo e DMVs já existem localmente, não é possível usar seus nomes para a definição de tabela externa. Em vez disso, use um nome diferente e use a visualização do catálogo ou o nome do Detran nas cláusulas SCHEMA_NAME e/ou OBJECT_NAME. (Veja o exemplo abaixo.)

A cláusula DISTRIBUTION especifica a distribuição de dados usada para esta tabela. O processador de consultas utiliza as informações fornecidas na cláusula DISTRIBUTION para criar os planos de consulta mais eficientes.

  1. SHARDED significa que os dados são particionados horizontalmente entre os bancos de dados. A chave de particionamento para a distribuição de dados é o parâmetro sharding_column_name>.<
  2. REPLICADO significa que cópias idênticas da tabela estão presentes em cada banco de dados. É sua responsabilidade garantir que as réplicas sejam idênticas nos bancos de dados.
  3. ROUND_ROBIN significa que a tabela é particionada horizontalmente usando um método de distribuição dependente de aplicativo.

Referência da camada de dados: A tabela externa DDL refere-se a uma fonte de dados externa. A fonte de dados externa especifica um mapa de estilhaços que fornece à tabela externa as informações necessárias para localizar todos os bancos de dados em sua camada de dados.

Considerações de segurança

Os usuários com acesso à tabela externa obtêm automaticamente acesso às tabelas remotas subjacentes sob a credencial fornecida na definição de fonte de dados externa. Evite a elevação indesejada de privilégios por meio da credencial da fonte de dados externa. Use GRANT ou REVOKE para uma tabela externa como se fosse uma tabela regular.

Depois de definir sua fonte de dados externa e suas tabelas externas, agora você pode usar o T-SQL completo sobre suas tabelas externas.

Exemplo: consultar bancos de dados particionados horizontais

A consulta a seguir executa uma junção de três vias entre depósitos, pedidos e linhas de ordem e usa várias agregações e um filtro seletivo. Ele pressupõe (1) particionamento horizontal (fragmentação) e (2) que armazéns, pedidos e linhas de pedidos são fragmentados pela coluna ID do depósito, e que a consulta elástica pode colocalizar as junções nos fragmentos e processar a parte cara da consulta nos fragmentos em paralelo.

    select  
         w_id as warehouse,
         o_c_id as customer,
         count(*) as cnt_orderline,
         max(ol_quantity) as max_quantity,
         avg(ol_amount) as avg_amount,
         min(ol_delivery_d) as min_deliv_date
    from warehouse
    join orders
    on w_id = o_w_id
    join order_line
    on o_id = ol_o_id and o_w_id = ol_w_id
    where w_id > 100 and w_id < 200
    group by w_id, o_c_id

Procedimento armazenado para execução remota de T-SQL: sp_execute_remote

A consulta elástica também introduz um procedimento armazenado que fornece acesso direto aos fragmentos. O procedimento armazenado é chamado sp_execute _remote e pode ser usado para executar procedimentos armazenados remotos ou código T-SQL nos bancos de dados remotos. Ele leva os seguintes parâmetros:

  • Nome da fonte de dados (nvarchar): O nome da fonte de dados externa do tipo RDBMS.
  • Consulta (nvarchar): A consulta T-SQL a ser executada em cada fragmento.
  • Declaração de parâmetro (nvarchar) - opcional: String com definições de tipo de dados para os parâmetros usados no parâmetro Query (como sp_executesql).
  • Lista de valores de parâmetros - opcional: lista de valores de parâmetros separados por vírgulas (como sp_executesql).

O sp_execute_remote usa a fonte de dados externa fornecida nos parâmetros de invocação para executar a instrução T-SQL fornecida nos bancos de dados remotos. Ele usa a credencial da fonte de dados externa para se conectar ao banco de dados do gerenciador de shardmap e aos bancos de dados remotos.

Exemplo:

    EXEC sp_execute_remote
        N'MyExtSrc',
        N'select count(w_id) as foo from warehouse'

Conectividade para ferramentas

Use cadeias de conexão regulares do SQL Server para conectar seu aplicativo, seu BI e ferramentas de integração de dados ao banco de dados com suas definições de tabela externa. Verifique se o SQL Server tem suporte como uma fonte de dados para sua ferramenta. Em seguida, faça referência ao banco de dados de consulta elástica como qualquer outro banco de dados do SQL Server conectado à ferramenta e use tabelas externas de sua ferramenta ou aplicativo como se fossem tabelas locais.

Melhores práticas

  • Verifique se o banco de dados de ponto de extremidade de consulta elástica recebeu acesso ao banco de dados shardmap e a todos os fragmentos por meio dos firewalls do Banco de dados SQL.
  • Valide ou imponha a distribuição de dados definida pela tabela externa. Se a distribuição de dados real for diferente da distribuição especificada na definição da tabela, as consultas poderão gerar resultados inesperados.
  • Atualmente, a consulta elástica não executa a eliminação de estilhaços quando predicados sobre a chave de fragmentação permitem excluir com segurança determinados fragmentos do processamento.
  • A consulta elástica funciona melhor para consultas em que a maior parte da computação pode ser feita nos fragmentos. Normalmente, você obtém o melhor desempenho de consulta com predicados de filtro seletivo que podem ser avaliados nos fragmentos ou junções sobre as chaves de particionamento que podem ser executadas de forma alinhada à partição em todos os fragmentos. Outros padrões de consulta podem precisar carregar grandes quantidades de dados dos fragmentos para o nó principal e podem ter um desempenho insatisfatório

Próximos passos