Usar o driver ODBC do Azure Cosmos DB para se conectar a ferramentas de BI e análise de dados

APLICA-SE A: NoSQL

Este artigo orienta você na instalação e no uso do driver ODBC do Azure Cosmos DB para criar tabelas e exibições normalizadas para seus dados do Azure Cosmos DB. Você pode consultar os dados normalizados com consultas SQL ou importar os dados para o Power BI ou outro software de BI e análise para criar relatórios e visualizações.

O Azure Cosmos DB é um banco de dados sem esquema, que permite o desenvolvimento rápido de aplicativos e permite iterar em modelos de dados sem ficar confinado a um esquema estrito. Um único banco de dados do Azure Cosmos DB pode conter documentos JSON de várias estruturas. Para analisar ou gerar relatórios sobre esses dados, talvez seja necessário nivelá-los para caber em um esquema.

O driver ODBC normaliza os dados do Azure Cosmos DB em tabelas e exibições que se ajustam às suas necessidades de análise de dados e relatórios. Os esquemas normalizados permitem que você use ferramentas compatíveis com ODBC para acessar os dados. Os esquemas não têm efeito sobre os dados subjacentes e não exigem que os desenvolvedores adiram a eles. O driver ODBC ajuda a tornar os bancos de dados do Azure Cosmos DB úteis para analistas de dados e equipes de desenvolvimento.

Você pode fazer operações SQL nas tabelas e exibições normalizadas, incluindo agrupar por consultas, inserções, atualizações e exclusões. O driver é compatível com ODBC 3.8 e suporta sintaxe ANSI SQL-92.

Importante

Considere usar o Azure Synapse Link para Azure Cosmos DB para criar tabelas e exibições para seus dados. Synapse Link tem benefícios de desempenho distintos para grandes conjuntos de dados sobre o driver ODBC. Você também pode conectar os dados normalizados do Azure Cosmos DB a outras soluções de software, como SQL Server Integration Services (SSIS), QlikSense, Tableau e outros softwares de análise, BI e ferramentas de integração de dados. Você pode usar essas soluções para analisar, mover, transformar e criar visualizações com seus dados do Azure Cosmos DB.

Importante

• A conexão ao Azure Cosmos DB com o driver ODBC é atualmente suportada apenas para o Azure Cosmos DB para NoSQL.
• O driver ODBC atual não suporta pushdowns agregados e tem problemas conhecidos com algumas ferramentas de análise. Até que uma nova versão seja lançada, você pode usar uma das seguintes alternativas:
  • O Azure Synapse Link é a solução de análise preferida para o Azure Cosmos DB. Com os pools sem servidor do Azure Synapse Link e do Azure Synapse SQL, você pode usar qualquer ferramenta de BI para extrair insights quase em tempo real do Azure Cosmos DB SQL ou da API para dados do MongoDB.
  • Para o Power BI, você pode usar o conector do Azure Cosmos DB para o Power BI.
  • Para o Qlik Sense, consulte Conectar o Qlik Sense ao Azure Cosmos DB.

Instale o driver ODBC e conecte-se ao seu banco de dados

1. Faça o download dos drivers para o seu ambiente:

   Instalador	Sistemas operativos suportados
   64-bit.msi ODBC do Microsoft Azure Cosmos DB para Windows de 64 bits	Versões de 64 bits do Windows 8.1 ou posterior, Windows 8, Windows 7. Versões de 64 bits do Windows Server 2012 R2, Windows Server 2012 e Windows Server 2008 R2. Windows 10. Windows Server 2012 R2. Windows Server 2016
   32x64-bit.msi ODBC do Microsoft Azure Cosmos DB para 32 bits no Windows de 64 bits	Versões de 64 bits do Windows 8.1 ou posterior, Windows 8, Windows 7, Windows XP, Windows Vista. Versões de 64 bits do Windows Server 2012 R2, Windows Server 2012, Windows Server 2008 R2 e Windows Server 2003.
   32-bit.msi ODBC do Microsoft Azure Cosmos DB para Windows de 32 bits	Versões de 32 bits do Windows 8.1 ou posterior, Windows 8, Windows 7, Windows XP e Windows Vista.

2. Execute o arquivo .msi localmente, que inicia o Assistente de Instalação do Driver ODBC do Microsoft Azure Cosmos DB.

3. Conclua o assistente de instalação usando a entrada padrão.

4. Depois que o driver for instalado, digite Fontes de dados ODBC na caixa de pesquisa do Windows e abra o Administrador de fonte de dados ODBC.

5. Verifique se o driver ODBC do Banco de Dados de Documentos do Microsoft Azure está listado na guia Drivers.

   

6. Selecione a guia DSN do usuário e, em seguida, selecione Adicionar para criar um novo nome de fonte de dados (DSN). Você também pode criar um DSN do sistema.

7. Na janela Criar Nova Fonte de Dados, selecione Driver ODBC do Banco de Dados de Documentos do Microsoft Azure e selecione Concluir.

8. Na janela de configuração do DSN do driver ODBC do Banco de Dados de Documentos, preencha as seguintes informações:

   

   • Nome da fonte de dados: um nome amigável para o DSN ODBC. Esse nome é exclusivo para esta conta do Azure Cosmos DB.
   • Descrição: uma breve descrição da fonte de dados.
   • Host: O URI da sua conta do Azure Cosmos DB. Você pode obter essas informações na página Chaves em sua conta do Azure Cosmos DB no portal do Azure.
   • Chave de Acesso: A chave primária ou secundária, de leitura-escrita ou só de leitura da página Chaves do Azure Cosmos DB no portal do Azure. É melhor usar as chaves somente leitura, se você usar o DSN para processamento de dados somente leitura e relatórios.

   Para evitar um erro de autenticação, use os botões de cópia para copiar o URI e a chave do portal do Azure.

   

   • Criptografar chave de acesso para: Selecione a melhor escolha, com base em quem usa a máquina.

9. Selecione Testar para garantir que você possa se conectar à sua conta do Azure Cosmos DB.

10. Selecione Opções avançadas e defina os seguintes valores:

    • Versão da API REST: Selecione a versão da API REST para suas operações. O padrão é 2015-12-16.

      Se você tiver contêineres com chaves de partição grandes que precisam da versão da API REST , digite 2018-12-31e 2018-12-31.

    • Consistência da consulta: selecione o nível de consistência para suas operações. O padrão é Session.

    • Número de tentativas: insira o número de vezes para repetir uma operação se a solicitação inicial não for concluída devido ao limite da taxa de serviço.

    • Arquivo de esquema: Se você não selecionar um arquivo de esquema, o driver verificará a primeira página de dados de cada contêiner para determinar seu esquema, chamado mapeamento de contêiner, para cada sessão. Esse processo pode causar um longo tempo de inicialização para aplicativos que usam o DSN. É melhor associar um arquivo de esquema ao DSN.

      • Se você já tiver um arquivo de esquema, selecione Procurar, navegue até o arquivo, selecione Salvar e selecione OK.

      • Se você ainda não tiver um arquivo de esquema, selecione OK e siga as etapas na próxima seção para criar uma definição de esquema. Depois de criar o esquema, volte a esta janela Opções avançadas para adicionar o arquivo de esquema.

Depois de selecionar OK para concluir e fechar a janela de Configuração do DSN do Driver ODBC do DocumentDB, o novo DSN do Utilizador aparecerá na aba DSN do Utilizador da janela do Administrador de Fonte de Dados ODBC.

Edite o registro do Windows para oferecer suporte à API REST versão 2018-12-31

Se você tiver contêineres com chaves de partição grandes que precisam da API REST versão 2018-12-31, siga estas etapas para atualizar o registro do Windows para oferecer suporte a essa versão.

1. No menu Iniciar do Windows, digite regedit para localizar e abrir o Editor do Registro.

2. No Editor do Registro, navegue até o caminho Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC. INI.

3. Crie uma nova subchave com o mesmo nome do seu DSN, como DSN ODBC da Conta Contoso.

4. Navegue até a nova subchave Contoso Account ODBC DSN e clique com o botão direito do mouse para adicionar um novo valor de cadeia de caracteres String.

   • Nome do valor: IgnoreSessionToken

   • Dados do valor: 1

   

Criar uma definição de esquema

Há dois tipos de métodos de amostragem que você pode usar para criar um esquema: mapeamento de contêiner ou mapeamento de delimitador de tabela. Uma sessão de amostragem pode usar ambos os métodos de amostragem, mas cada recipiente pode usar apenas um dos métodos de amostragem. O método a utilizar depende das características dos seus dados.

• O mapeamento de contêiner recupera os dados em uma página de contêiner para determinar a estrutura de dados e transpõe o contêiner para uma tabela no lado ODBC. Este método de amostragem é eficiente e rápido quando os dados num recipiente são homogéneos.

• O mapeamento do delimitador de tabela fornece uma amostragem mais robusta para dados heterogêneos. Este método define o escopo da amostragem para um conjunto de atributos e valores correspondentes.

  Por exemplo, se um documento contiver uma propriedade Type , você poderá definir o escopo da amostragem para os valores dessa propriedade. O resultado final da amostragem é um conjunto de tabelas para cada um dos valores Type especificados. Tipo = Carro produz uma tabela Carro , enquanto Tipo = Plano produz uma tabela Plano .

Para definir um esquema, siga estas etapas. Para o método de mapeamento de delimitador de tabela, você executa etapas adicionais para definir atributos e valores para o esquema.

1. Na guia DSN do Usuário da janela Administrador da Fonte de Dados ODBC, selecione seu Nome DSN de Usuário do Azure Cosmos DB e selecione Configurar.

2. Na janela Configuração DSN do driver ODBC do Banco de Dados de Documentos, selecione Editor de esquema.

   

3. Na janela Editor de esquema, selecione Criar novo.

4. A janela Gerar esquema exibe todas as coleções na conta do Azure Cosmos DB. Marque as caixas de seleção ao lado dos contêineres que você deseja amostrar.

5. Para usar o método de mapeamento de contêiner, selecione Amostra.

   Ou, para usar o mapeamento do delimitador de tabela, execute as etapas a seguir para definir atributos e valores para definir o escopo do exemplo.

   1. Selecione Editar na coluna Definição de mapeamento para seu DSN.

   2. Na janela Definição de Mapeamento, em Método de Mapeamento, selecione Delimitadores de Tabela.

   3. Na caixa Atributos, digite o nome de uma propriedade delimitadora no documento para a qual você deseja definir o escopo da amostragem, por exemplo, Cidade. Prima Enter.

   4. Se desejar definir o escopo da amostragem para determinados valores para o atributo inserido, selecione o atributo e, em seguida, insira um valor na caixa Valor , como Seattle, e pressione Enter. Você pode adicionar vários valores para atributos. Apenas certifique-se de que o atributo correto está selecionado quando você insere valores.

   5. Quando terminar de inserir atributos e valores, selecione OK.

   6. Na janela Gerar esquema, selecione Exemplo.

6. Na guia Modo Design, refine seu esquema. A Vista de Design representa banco de dados, esquema e tabela. O modo de exibição de tabela exibe o conjunto de propriedades associadas aos nomes das colunas, como Nome SQL e Nome da Fonte.

   Para cada coluna, pode modificar o nome SQL, o tipo SQL, o comprimento SQL, a escala, a precisão e a nulabilidade conforme aplicável.

   Você pode definir Ocultar coluna como true se quiser excluir essa coluna dos resultados da consulta. As colunas marcadas como Ocultar coluna = true não são retornadas para seleção e projeção, embora ainda façam parte do esquema. Por exemplo, você pode ocultar todas as propriedades necessárias do sistema Azure Cosmos DB que começam com _. A coluna id é o único campo que você não pode ocultar, porque é a chave primária no esquema normalizado.

7. Quando terminar de definir o esquema, selecione >, navegue até o diretório para salvar e selecione Salvar.

8. Para usar esse esquema com um DSN, na janela Configuração do DSN do Driver ODBC do Banco de Dados de Documentos, selecione Opções Avançadas. Selecione a caixa do Arquivo de Esquema, navegue até ao esquema guardado, selecione OK e, em seguida, selecione OK novamente. Salvar o ficheiro de esquema modifica a conexão DSN para que abrangesse os dados e a estrutura definidos pelo esquema.

Criar vistas

Opcionalmente, você pode definir e criar exibições no Editor de Esquema como parte do processo de amostragem. Esses modos de exibição são equivalentes aos modos de exibição SQL. As exibições são somente leitura e estão limitadas às seleções e projeções da consulta SQL definida do Azure Cosmos DB.

Siga estes passos para criar uma vista para os seus dados:

1. Na guia Modo de Exibição de Exemplo da janela Editor de Esquema, selecione os contêineres que deseja amostrar e selecione Adicionar na coluna Definição de Exibição.

   

2. Na janela Exibir definições, selecione Novo. Introduza um nome para a vista, por exemplo EmployeesfromSeattleView, e selecione OK.

3. Na janela Editar modo de exibição, insira uma consulta do Azure Cosmos DB, por exemplo:

   SELECT c.City, c.EmployeeName, c.Level, c.Age, c.Manager FROM c WHERE c.City = "Seattle"

4. Selecione OK.

   

Você pode criar quantas visualizações quiser. Quando terminar de definir as exibições, selecione Amostra para obter uma amostra dos dados.

Importante

O texto da consulta na definição de exibição não deve conter quebras de linha. Caso contrário, obteremos um erro genérico ao pré-visualizar a vista.

Consulta no SQL Server Management Studio

Depois de configurar um DSN de Utilizador do Driver ODBC do Azure Cosmos DB, pode consultar o Azure Cosmos DB a partir do SQL Server Management Studio (SSMS) configurando uma conexão de servidor vinculado.

1. Instale o SQL Server Management Studio e conecte-se ao servidor.

2. No editor de consultas do SSMS, crie um objeto de servidor vinculado para a fonte de dados executando os seguintes comandos. Substitua DEMOCOSMOS pelo nome do servidor vinculado e SDS Name pelo nome da fonte de dados.

   USE [master]
   GO
   
   EXEC master.dbo.sp_addlinkedserver @server = N'DEMOCOSMOS', @srvproduct=N'', @provider=N'MSDASQL', @datasrc=N'SDS Name'
   
   EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DEMOCOSMOS', @useself=N'False', @locallogin=NULL, @rmtuser=NULL, @rmtpassword=NULL
   
   GO
   

Para ver o novo nome do servidor vinculado, atualize a lista de servidores vinculados.

Para consultar o banco de dados vinculado, insira uma consulta SSMS. Neste exemplo, a consulta seleciona a partir da tabela no contêiner chamado customers:

SELECT * FROM OPENQUERY(DEMOCOSMOS, 'SELECT *  FROM [customers].[customers]')

Execute a consulta. Os resultados devem ser semelhantes aos seguintes resultados:

attachments/  1507476156    521 Bassett Avenue, Wikieup, Missouri, 5422   "2602bc56-0000-0000-0000-59da42bc0000"   2015-02-06T05:32:32 +05:00 f1ca3044f17149f3bc61f7b9c78a26df
attachments/  1507476156    167 Nassau Street, Tuskahoma, Illinois, 5998   "2602bd56-0000-0000-0000-59da42bc0000"   2015-06-16T08:54:17 +04:00 f75f949ea8de466a9ef2bdb7ce065ac8
attachments/  1507476156    885 Strong Place, Cassel, Montana, 2069       "2602be56-0000-0000-0000-59da42bc0000"   2015-03-20T07:21:47 +04:00 ef0365fb40c04bb6a3ffc4bc77c905fd
attachments/  1507476156    515 Barwell Terrace, Defiance, Tennessee, 6439     "2602c056-0000-0000-0000-59da42bc0000"   2014-10-16T06:49:04 +04:00      e913fe543490432f871bc42019663518
attachments/  1507476156    570 Ruby Street, Spokane, Idaho, 9025       "2602c156-0000-0000-0000-59da42bc0000"   2014-10-30T05:49:33 +04:00 e53072057d314bc9b36c89a8350048f3

Ver os seus dados no Power BI Desktop

Você pode usar seu DSN para se conectar ao Azure Cosmos DB com qualquer ferramenta compatível com ODBC. Este procedimento mostra como se conectar ao Power BI Desktop para criar uma visualização do Power BI.

1. No Power BI Desktop, selecione Obter Dados.

   

2. Na janela Obter dados, selecione Outro>ODBC e, em seguida, selecione Conectar.

   

3. Na janela Do ODBC, selecione o DSN que você criou e, em seguida, selecione OK.

   

4. Na janela Acessar uma fonte de dados usando um driver ODBC, selecione Padrão ou Personalizado e, em seguida, selecione Conectar.

5. Na janela Navegador, no painel esquerdo, expanda o banco de dados e o esquema e selecione a tabela. O painel de resultados inclui os dados que usam o esquema que você criou.

   

6. Para visualizar os dados no Power BI desktop, marque a caixa de seleção ao lado do nome da tabela e selecione Carregar.

7. No Power BI Desktop, selecione a guia Dados à esquerda da tela para confirmar que seus dados foram importados.

8. Selecione a guia Relatório à esquerda da tela, selecione Novo visual na faixa de opções e personalize o visual.

Resolução de Problemas

• Problema: Você recebe o seguinte erro ao tentar se conectar:

  [HY000]: [Microsoft][Azure Cosmos DB] (401) HTTP 401 Authentication Error: {"code":"Unauthorized","message":"The input authorization token can't serve the request. Please check that the expected payload is built as per the protocol, and check the key being used. Server used the following payload to sign: 'get\ndbs\n\nfri, 20 jan 2017 03:43:55 gmt\n\n'\r\nActivityId: 9acb3c0d-cb31-4b78-ac0a-413c8d33e373"}
  

  Solução: verifique se os valores de Host e Chave de Acesso copiados do portal do Azure estão corretos e tente novamente.

• Problema: Você recebe o seguinte erro no SSMS ao tentar criar um servidor Azure Cosmos DB vinculado:

  Msg 7312, Level 16, State 1, Line 44
  
  Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "DEMOCOSMOS". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
  

  Solução: um servidor Azure Cosmos DB vinculado não oferece suporte à nomenclatura de quatro partes.

Próximos passos

• Para saber mais sobre o Azure Cosmos DB, veja Bem-vindo ao Azure Cosmos DB.
• Para obter mais informações sobre como criar visualizações no Power BI Desktop, consulte Tipos de visualização no Power BI.