Consulta em bancos de dados na nuvem com esquemas diferentes (visualização)

  • Artigo

Aplica-se a:Banco de Dados SQL do Azure

Query across tables in different databases

Bancos de dados particionados verticalmente usam diferentes conjuntos de tabelas em bancos de dados diferentes. Isso significa que o esquema é diferente em bancos de dados diferentes. Por exemplo, todas as tabelas para inventário estão em um banco de dados, enquanto todas as tabelas relacionadas à contabilidade estão em um segundo banco de dados.

Pré-requisitos

  • 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

Nota

Ao contrário do particionamento horizontal, essas instruções DDL não dependem da definição de uma camada de dados com um mapa de estilhaços através da biblioteca de cliente de banco de dados elástico.

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

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 = 'master_key_password';
CREATE DATABASE SCOPED CREDENTIAL [<credential_name>]  WITH IDENTITY = '<username>',  
SECRET = '<password>';

Nota

Certifique-se de que o não inclui nenhum sufixo <username> "@servername".

Criar fontes de dados externas

Sintaxe:

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

Importante

O parâmetro TYPE deve ser definido como RDBMS.

Exemplo

O exemplo a seguir ilustra o uso da instrução CREATE para fontes de dados externas.

CREATE EXTERNAL DATA SOURCE RemoteReferenceData
   WITH
      (
         TYPE=RDBMS,
         LOCATION='myserver.database.windows.net',
         DATABASE_NAME='ReferenceData',
         CREDENTIAL= SqlUser
      );

Para recuperar a lista de fontes de dados externas atuais:

select * from sys.external_data_sources;

External Tables

Sintaxe:

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

<rdbms_external_table_options> ::=
    DATA_SOURCE = <External_Data_Source>,
    [ SCHEMA_NAME = N'nonescaped_schema_name',]
    [ OBJECT_NAME = N'nonescaped_object_name',]

Exemplo

CREATE EXTERNAL TABLE [dbo].[customer]
   (
      [c_id] int NOT NULL,
      [c_firstname] nvarchar(256) NULL,
      [c_lastname] nvarchar(256) NOT NULL,
      [street] nvarchar(256) NOT NULL,
      [city] nvarchar(256) NOT NULL,
      [state] nvarchar(20) NULL
   )
   WITH
   (
      DATA_SOURCE = RemoteReferenceData
   );

O exemplo a seguir mostra como recuperar a lista de tabelas externas do banco de dados atual:

select * from sys.external_tables;

Observações

A consulta elástica estende a sintaxe da tabela externa existente para definir tabelas externas que usam fontes de dados externas do tipo RDBMS. Uma definição de tabela externa para particionamento vertical abrange os seguintes aspetos:

  • Esquema: A tabela externa DDL define um esquema que suas consultas podem usar. O esquema fornecido em sua definição de tabela externa precisa corresponder ao esquema das tabelas no banco de dados remoto onde os dados reais são armazenados.
  • Referência de banco de dados remoto: A tabela externa DDL refere-se a uma fonte de dados externa. A fonte de dados externa especifica o nome do servidor e o nome do banco de dados remoto onde os dados reais da tabela são armazenados.

Usando uma fonte de dados externa, conforme descrito na seção anterior, a sintaxe para criar tabelas externas é a seguinte:

A cláusula DATA_SOURCE define a fonte de dados externa (ou seja, o banco de dados remoto em particionamento vertical) que é usada para a tabela externa.

As cláusulas SCHEMA_NAME e OBJECT_NAME permitem mapear a definição de tabela externa para uma tabela em um esquema diferente no banco de dados remoto ou para uma tabela com um nome diferente, respectivamente. Esse mapeamento é útil se você quiser definir uma tabela externa para uma exibição de catálogo ou DMV em seu banco de dados remoto - ou qualquer outra situação em que o nome da tabela remota já seja usado localmente.

A instrução DDL a seguir descarta uma definição de tabela externa existente do catálogo local. Isso não afeta o banco de dados remoto.

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

Permissões para CREATE/DROP EXTERNAL TABLE: AS PERMISSÕES ALTER ANY EXTERNAL DATA SOURCE são necessárias para DDL de tabela externa, que também é necessária para se referir à fonte de dados subjacente.

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. Gerencie cuidadosamente o acesso à tabela externa, a fim de evitar a elevação indesejada de privilégios por meio da credencial da fonte de dados externa. Permissões SQL regulares podem ser usadas para CONCEDER ou REVOGAR acesso a uma tabela externa como se fosse uma tabela regular.

Exemplo: consultar bancos de dados particionados verticalmente

A consulta a seguir executa uma junção de três vias entre as duas tabelas locais para pedidos e linhas de ordem e a tabela remota para clientes. Este é um exemplo do caso de uso de dados de referência para consulta elástica:

    SELECT
     c_id as customer,
     c_lastname as customer_name,
     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 customer
    JOIN orders
    ON c_id = o_c_id
    JOIN  order_line
    ON o_id = ol_o_id and o_c_id = ol_c_id
    WHERE c_id = 100

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 ao banco de dados remoto. O procedimento armazenado é chamado sp_execute _remote e pode ser usado para executar procedimentos armazenados remotos ou código T-SQL no banco de dados remoto. 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 no banco de dados remoto.
  • 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 no banco de dados remoto. Ele usa a credencial da fonte de dados externa para se conectar ao banco de dados remoto.

Exemplo:

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

Conectividade para ferramentas

Você pode usar cadeias de conexão regulares do SQL Server para conectar suas ferramentas de BI e integração de dados a bancos de dados no servidor que tenha a consulta elástica habilitada e tabelas externas definidas. Verifique se o SQL Server tem suporte como uma fonte de dados para sua ferramenta. Em seguida, consulte o banco de dados de consulta elástica e suas tabelas externas como qualquer outro banco de dados do SQL Server ao qual você se conectaria com sua ferramenta.

Melhores práticas

  • Verifique se o banco de dados de ponto de extremidade de consulta elástica recebeu acesso ao banco de dados remoto habilitando o acesso aos Serviços do Azure em sua configuração de firewall do Banco de Dados SQL do Azure. Certifique-se também de que a credencial fornecida na definição da fonte de dados externa possa efetuar login com êxito no banco de dados remoto e tenha as permissões para acessar a tabela remota.
  • A consulta elástica funciona melhor para consultas em que a maior parte da computação pode ser feita nos bancos de dados remotos. Normalmente, você obtém o melhor desempenho de consulta com predicados de filtro seletivo que podem ser avaliados nos bancos de dados remotos ou junções que podem ser executadas completamente no banco de dados remoto. Outros padrões de consulta podem precisar de carregar grandes quantidades de dados a partir de uma base de dados remota e podem ter um desempenho fraco.

Próximos passos