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

APLICA-SE A: NoSQL

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

O Azure Cosmos DB é um banco de dados sem esquema, que possibilita o método RAD e permite que você itere em modelos de dados sem ficar confinado a um esquema rígido. Um único banco de dados do Azure Cosmos DB pode conter documentos JSON de várias estruturas. Para analisar ou relatar esses dados, talvez seja necessário nivelar os dados para ajustá-los em um esquema.

O driver ODBC normaliza os dados do Azure Cosmos DB em tabelas e exibições ajustadas às suas necessidades de relatórios e análise de dados. Os esquemas normalizados permitem que você use ferramentas compatíveis com ODBC para acessar os dados. Os esquemas não têm nenhum impacto sobre os dados subjacentes e não exigem que os desenvolvedores os sigam. 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 executar operações SQL em tabelas e exibições normalizadas, incluindo o agrupamento por consultas, inserções, atualizações e exclusões. O driver está em conformidade com o ODBC 3.8 e dá suporte à sintaxe ANSI SQL-92.

Importante

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

Importante

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

Instalar o driver ODBC e conectar-se ao seu banco de dados

1. Baixe os drivers para seu ambiente:

   Instalador	Sistemas operacionais com suporte
   Microsoft Azure Cosmos DB ODBC 64-bit.msi 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.
   Microsoft Azure Cosmos DB ODBC 32x64-bit.msi 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 e Windows Vista. Versões de 64 bits do Windows Server 2012 R2, Windows Server 2012, Windows Server 2008 R2 e Windows Server 2003.
   Microsoft Azure Cosmos DB ODBC 32-bit.msi 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, o 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. Após a instalação do driver, digite Fontes de dados ODBC na caixa de pesquisa do Windows e abra o Administrador da Fonte de Dados ODBC.

5. Verifique se o Driver ODBC do Microsoft Azure DocumentDB está listado na guia Drivers.

   

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

7. Na janela Criar Fonte de Dados, selecione Driver ODBC do Microsoft Azure DocumentDB e escolha Concluir.

8. Na janela Instalação de DSN do Driver ODBC do DocumentDB, preencha as seguintes informações:

   

   • Nome da Fonte de Dados: um nome amigável para o DSN do ODBC. Esse nome é exclusivo dessa 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. Obtenha essas informações na página Chaves na sua conta do Azure Cosmos DB no portal do Azure.
   • Chave de Acesso: a chave primária ou secundária, somente leitura ou gravação da página Chaves do Azure Cosmos DB no portal do Azure. Será melhor usar as chaves somente leitura se você usar o DSN para processamento e relatório de dados somente leitura.

   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 a Chave de Acesso para: selecione a melhor opção com base nos usuários deste computador.

9. Selecione Testar para ter certeza de que você pode se conectar à sua conta do Azure Cosmos DB.

10. Escolha 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 ter a API REST versão 2018-12-31, digite 2018-12-31 e siga as etapas no final deste procedimento.

    • Consistência de Consulta: selecione o nível de consistência para as operações. O padrão é Sessão.

    • Número de Novas Tentativas: insira o número de vezes que uma operação será repetida se a solicitação inicial não for concluída devido a uma limitação da taxa de serviço.

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

      • Se você já tiver um arquivo de esquema, selecione Procurar, procure o arquivo, escolha Salvar e selecione OK.

      • Se você ainda não tiver um arquivo de esquema, selecione OK e siga as etapas da 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 que você selecionar OK para concluir e fechar a janela Instalação de DSN do Driver ODBC do DocumentDB, o novo DSN do Usuário será exibido na guia DSN do Usuário da janela Administrador da Fonte de Dados ODBC.

Editar o Registro do Windows para dar suporte à API REST versão 2018-12-31

Se você tiver contêineres com chaves de partição grandes que precisam ter a API REST versão 2018-12-31, siga estas etapas para atualizar o Registro do Windows para dar 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 subchave com o mesmo nome do DSN, por exemplo, DSN do ODBC da Conta da Contoso.

4. Procure a nova subchave DSN do ODBC da Conta da Contoso e clique com o botão direito do mouse para adicionar um novo valor de Cadeia de caracteres:

   • 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 os dois métodos, mas cada contêiner só pode usar um dos métodos de amostragem. O método a ser usado 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 do ODBC. Esse método de amostragem é eficiente e rápido quando os dados em um contêiner são homogêneos.

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

  Por exemplo, se um documento contém uma propriedade Type, você pode definir o escopo da amostragem como os valores dessa propriedade. O resultado final da amostragem é um conjunto de tabelas para cada um dos valores Type especificados. Type = Car produz uma tabela Car, enquanto Type = Plane produz uma tabela Plane.

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

1. Na guia DSN do Usuário da janela Administrador da Fonte de Dados ODBC, selecione o nome DSN do usuário do Azure Cosmos DB e escolha Configurar.

2. Na janela Configuração do DSN do Driver ODBC do DocumentDB, selecione Editor de Esquema.

   

3. Na janela Editor de Esquema, selecione Criar.

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 dos quais deseja fazer a amostragem.

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

   Ou, então, para usar o mapeamento de delimitador de tabela, use as etapas a seguir para definir atributos e valores para definir o escopo da amostra.

   1. Selecione Editar na coluna Definição de Mapeamento do DSN.

   2. Em seguida, 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 de delimitador no documento para a qual deseja definir o escopo da amostragem, por exemplo, Cidade. Pressione Enter.

   4. Caso deseje definir o escopo de amostragem para determinados valores do atributo inserido, selecione o atributo, insira um valor na caixa Valor, como Seattle e pressione ENTER. Você pode adicionar vários valores para os atributos. Apenas verifique se o atributo correto é selecionado quando estiver inserindo valores.

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

   6. Na janela Gerar Esquema, escolha Amostra.

6. Na guia Modo de Exibição de Design, refine o esquema. O Modo de Exibição de Design representa o banco de dados, o esquema e a tabela. A exibição de tabela mostra o conjunto de propriedades associado aos nomes de colunas, como Nome SQL e Nome da Fonte.

   Para cada coluna, você pode modificar o Nome SQL, o Tipo SQL, o Comprimento SQL, a Escala, a Precisão e Permite Valor Nulo, conforme aplicável.

   Você pode definir Ocultar Coluna como true se deseja excluir a coluna dos resultados da consulta. As colunas marcadas com 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 obrigatórias do sistema do Azure Cosmos DB que começam com _. A coluna id é o único campo que você não pode ocultar, pois é a chave primária no esquema normalizado.

7. Depois de terminar de definir o esquema, selecione Arquivo>Salvar, procure o diretório no qual ele será salvo e selecione Salvar.

8. Para usar esse esquema com um DSN, na janela Configuração do DSN do Driver ODBC do DocumentDB, selecione Opções Avançadas. Escolha a caixa Arquivo de Esquema, procure o esquema salvo, selecione OK e OK novamente. Se você salvar o arquivo de esquema, a conexão DSN será modificada para incluir no escopo a estrutura e os dados definidos pelo esquema.

Criar exibições

Opcionalmente, você pode definir e criar exibições no Editor de Esquema como parte do processo de amostragem. Essas exibições são equivalentes às exibições do SQL. Elas são somente leitura e têm o escopo definido para as seleções e as projeções da consulta SQL do Azure Cosmos DB.

Siga estas etapas para criar uma exibição para seus dados:

1. Na guia Exibição da Amostra da janela Editor de Esquema, selecione os contêineres dos quais deseja fazer a amostragem e escolha Adicionar na coluna Definição de Exibição.

   

2. Na janela Definições de Exibição, selecione Nova. Insira um nome para a exibição, por exemplo, EmployeesfromSeattleView e selecione OK.

3. Na janela Editar 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ê poderá criar quantas exibições desejar. Depois de terminar de definir as exibições, selecione Amostrar para realizar a amostragem 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 na versão preliminar do modo de exibição.

Verificar com o SSMS (SQL Server Management Studio)

Depois de configurar um DSN de Usuário do Driver ODBC do Azure Cosmos DB, você poderá consultar o Azure Cosmos DB no SSMS (SQL Server Management Studio) configurando uma conexão de servidor vinculado.

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

2. No editor de consulta do SSMS, crie um objeto de servidor vinculado para a fonte de dados executando os comandos a seguir. 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 de servidor vinculado, atualize a lista de servidores vinculados.

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

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

Executar a consulta. Os resultados serão parecidos com a seguinte saída:

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 seus dados no Power BI Desktop

Use 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 Outros>ODBC e escolha Conectar.

   

3. Na janela Do ODBC, selecione o DSN criado e escolha OK.

   

4. Na janela Acessar uma fonte de dados usando um driver ODBC, selecione Padrão ou Personalizada e escolha 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 se os dados foram importados.

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

Soluçã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 repita a operação.

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

  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 vinculado do Azure Cosmos DB não dá suporte à nomenclatura de quatro partes.

Próximas etapas

• Para saber mais sobre o Azure Cosmos DB, confira 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.