Partilhar via

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

Aplica-se a:Banco de Dados SQL do Azure

Os bancos de dados partilhados distribuem linhas em uma camada de dados expandida. 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.

Diagrama de como as consultas funcionam em fragmentos.

Para um início rápido, consulte Gerar relatório em bases de dados na nuvem escalonadas (pré-visualização).

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

Pré-requisitos

Visã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. CRIAR CREDENCIAL COM ÂMBITO DE BASE DE DADOS
  3. CRIAR FONTE DE DADOS EXTERNA
  4. CRIAR TABELA EXTERNA

1.1 Criar chave mestra com escopo definido e credenciais 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>';

Observação

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 fragmentos. 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 fragmentos e para aceder aos 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 DATA_SOURCE cláusula define a fonte de dados externa (um mapa de estilhaços) que é usada para a tabela externa.

SCHEMA_NAME e OBJECT_NAME cláusulas mapeiam a definição de tabela externa para uma tabela num esquema diferente. Se omitido, o esquema do objeto remoto é assumido como sendo dbo e seu nome é assumido como idêntico ao nome da tabela externa que está sendo 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, utilize um nome diferente e use o nome da visualização do catálogo ou do DMV nas cláusulas SCHEMA_NAME e/ou OBJECT_NAME. (Veja o exemplo mais adiante.)

A DISTRIBUTION cláusula especifica a distribuição de dados usada para esta tabela. O processador de consultas utiliza as informações fornecidas na DISTRIBUTION cláusula 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. REPLICATED 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 horizontalmente

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, encomendas e linhas de encomendas são fragmentados pela coluna ID do armazém, e que a consulta elástica pode realizar a junção nos fragmentos e processar a parte mais onerosa 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 utilizado para executar procedimentos armazenados de forma remota ou código T-SQL em bancos de dados remotos. Aceita 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 separada por vírgulas de valores de parâmetros (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

  • Certifique-se de que o banco de dados de endpoint de consulta elástica recebeu acesso ao banco de dados shardmap e a todos os fragmentos através dos firewalls do SQL Database.
  • 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 elastic query não executa a eliminação de shards quando predicados sobre a chave de sharding permitem excluir com segurança determinados shards 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 de dados ou com junções das 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.

Conteúdo relacionado

  • Last updated on