Executar consultas federadas no Google BigQuery

Esta página descreve como configurar a Lakehouse Federation para executar consultas federadas em dados do BigQuery que não são gerenciados pelo Azure Databricks. Para saber mais sobre a Lakehouse Federation, consulte O que é a Lakehouse Federation?

Para conectar-se à sua base de dados BigQuery usando a Lakehouse Federation, deve criar o seguinte no seu metastore do Azure Databricks Unity Catalog (os espaços de trabalho criados após 9 de novembro de 2023 já têm um metastore Unity Catalog configurado automaticamente).

• Uma conexão com seu banco de dados do BigQuery.
• Um catálogo externo que espelha o seu banco de dados do BigQuery no Catálogo Unity, permitindo que utilize a sintaxe de consulta do Catálogo Unity e as ferramentas de governança de dados para gerir o acesso dos utilizadores do Azure Databricks ao banco de dados.

Antes de começar

Requisitos do espaço de trabalho:

• Espaço de trabalho habilitado para o Catálogo Unity.

Requisitos de computação:

• Conectividade de rede do seu cluster Databricks Runtime ou SQL warehouse para os sistemas de banco de dados de destino. Consulte Recomendações de rede para a Lakehouse Federation.
• Os clusters do Azure Databricks devem usar o Databricks Runtime 16.1 ou superior e o modo de acesso padrão ou dedicado (anteriormente compartilhado e usuário único).
• Os armazéns SQL devem ser Pro ou Serverless.

Permissões necessárias:

• Para criar uma ligação, deve ter o privilégio CREATE CONNECTION na metastore do Unity Catalog associado ao espaço de trabalho.
• Para criar um catálogo estrangeiro, você deve ter a permissão CREATE CATALOG no metastore e ser o proprietário da conexão ou ter o privilégio de CREATE FOREIGN CATALOG na conexão.

Os requisitos de permissão adicionais são especificados em cada seção baseada em tarefas a seguir.

Criar uma conexão

Uma conexão especifica um caminho e credenciais para acessar um sistema de banco de dados externo. Para criar uma conexão, você pode usar o Gerenciador de Catálogos ou o comando CREATE CONNECTION SQL em um bloco de anotações do Azure Databricks ou no editor de consultas Databricks SQL.

Permissões necessárias: administrador do Metastore ou usuário com o CREATE CONNECTION privilégio.

Explorador de Catálogos

1. No seu espaço de trabalho do Azure Databricks, clique no Catálogo.

2. Na parte superior do painel Catálogo, clique no ícone Adicionar e selecione Criar uma conexão no menu.

3. Na página Noções básicas de conexão do assistente Configurar conexão, insira um Nome da conexão amigável.

4. Selecione um Tipo de conexão de Google BigQuerye, em seguida, clique em Avançar.

5. Na página Autenticação , insira a chave de conta de serviço do Google json para sua instância do BigQuery.

   Este é um objeto JSON bruto que é usado para especificar o projeto BigQuery e fornecer autenticação. Você pode gerar esse objeto JSON e baixá-lo na página de detalhes da conta de serviço no Google Cloud em "CHAVES". A conta de serviço deve ter permissões adequadas concedidas no BigQuery, incluindo Usuário do BigQuery e Visualizador de Dados do BigQuery. A seguir encontra-se um exemplo.

   {
     "type": "service_account",
     "project_id": "PROJECT_ID",
     "private_key_id": "KEY_ID",
     "private_key": "PRIVATE_KEY",
     "client_email": "SERVICE_ACCOUNT_EMAIL",
     "client_id": "CLIENT_ID",
     "auth_uri": "https://accounts.google.com/o/oauth2/auth",
     "token_uri": "https://accounts.google.com/o/oauth2/token",
     "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
     "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/SERVICE_ACCOUNT_EMAIL",
     "universe_domain": "googleapis.com"
   }
   

6. (Opcional) Insira o ID do projeto para a sua instância do BigQuery:

   Este é um nome para o projeto BigQuery usado para faturamento de todas as consultas executadas sob essa conexão. Define-se por padrão como a ID do projeto da sua conta de serviço. A conta de serviço deve ter permissões adequadas concedidas para este projeto no BigQuery, incluindo BigQuery User. Conjunto de dados adicional usado para armazenar tabelas temporárias pelo BigQuery pode ser criado neste projeto.

7. (Opcional) Adicione um comentário.

8. Clique em Criar conexão.

9. Na página Noções básicas do catálogo, insira um nome para o catálogo estrangeiro. Um catálogo estrangeiro espelha um banco de dados em um sistema de dados externo para que você possa consultar e gerenciar o acesso aos dados nesse banco de dados usando o Azure Databricks e o Unity Catalog.

10. (Opcional) Clique em Teste a ligação para confirmar que funciona.

11. Clique Criar catálogo.

12. Na página Acesso, selecione os espaços de trabalho nos quais os utilizadores podem aceder ao catálogo que você criou. Você pode selecionar Todos os espaços de trabalho têm acessoou clicar em Atribuir a espaços de trabalho, selecionar os espaços de trabalho e depois clicar em Atribuir.

13. Altere o Proprietário para que poderá gerir o acesso a todos os objetos do catálogo. Comece a digitar um principal na caixa de texto e, em seguida, clique no principal nos resultados apresentados.

14. Conceder os privilégios no catálogo. Clique Conceder:

    1. Especifique os Principals que terão acesso aos objetos no catálogo. Comece a digitar um principal na caixa de texto e, em seguida, clique no principal nos resultados apresentados.
    2. Selecione as predefinições de privilégio para conceder a cada principal. Todos os usuários da conta recebem BROWSE por padrão.
       • Selecione do Leitor de Dados no menu suspenso para conceder privilégios de read em objetos no catálogo.
       • Selecione Editor de Dados no menu suspenso para conceder os privilégios read e modify em objetos no catálogo.
       • Selecione manualmente os privilégios a conceder.
    3. Clique Conceder.

15. Clique Avançar.

16. Na página Metadados, especifique os pares chave-valor de tags. Para obter mais informações, consulte Aplicar tags a objetos seguráveis do Unity Catalog.

17. (Opcional) Adicione um comentário.

18. Clique Salvar.

SQL

Execute o seguinte comando em um bloco de anotações ou no editor de consultas Databricks SQL. Substitua <GoogleServiceAccountKeyJson> por um objeto JSON bruto que especifica o projeto BigQuery e fornece autenticação. Você pode gerar esse objeto JSON e baixá-lo na página de detalhes da conta de serviço no Google Cloud em "CHAVES". A conta de serviço precisa ter permissões adequadas concedidas no BigQuery, incluindo o Usuário do BigQuery e o Visualizador de Dados do BigQuery. Para obter um exemplo de objeto JSON, exiba a guia Catalog Explorer nesta página.

CREATE CONNECTION <connection-name> TYPE bigquery
OPTIONS (
  GoogleServiceAccountKeyJson '<GoogleServiceAccountKeyJson>'
);

Recomendamos que use o Azure Databricks segredos em vez de cadeias de texto simples para informações sensíveis, como credenciais. Por exemplo:

CREATE CONNECTION <connection-name> TYPE bigquery
OPTIONS (
  GoogleServiceAccountKeyJson secret ('<secret-scope>','<secret-key-user>')
)

Para obter informações sobre como configurar segredos, consulte Gerenciamento de segredos.

Criar um catálogo estrangeiro

Um catálogo estrangeiro espelha um banco de dados em um sistema de dados externo para que você possa consultar e gerenciar o acesso aos dados nesse banco de dados usando o Azure Databricks e o Unity Catalog. Para criar um catálogo estrangeiro, use uma conexão com a fonte de dados que já foi definida.

Para criar um catálogo estrangeiro, você pode usar o Catalog Explorer ou CREATE FOREIGN CATALOG em um bloco de anotações do Azure Databricks ou no editor de consultas Databricks SQL. Você também pode usar a API REST do Databricks ou a CLI do Databricks para criar um catálogo. Consulte POST /api/2.1/unity-catalog/catalogs ou os comandos do Unity Catalog.

Permissões necessárias:CREATE CATALOG permissão no metastore e propriedade da conexão ou o CREATE FOREIGN CATALOG privilégio na conexão.

Explorador de Catálogos

1. No seu espaço de trabalho do Azure Databricks, clique no Catálogo para abrir o Catalog Explorer.

2. Na parte superior do painel Catálogo , clique no ícone Adicionar ou mais ícone Adicionar e selecione Adicionar um catálogo no menu.

   Como alternativa, na página Acesso rápido, clique no botão Catálogos e depois clique no botão Criar catálogo.

3. (Opcional) Insira a seguinte propriedade de catálogo:

   Data Project Id: Um nome para o projeto do BigQuery contendo dados que serão mapeados para este catálogo. O padrão é o ID do projeto de faturamento definido no nível da conexão.

4. Siga as instruções para criar catálogos estrangeiros em Criar catálogos.

5. (Opcional) Especifique as seguintes opções de catálogo:

   • Materialization Dataset: Um nome opcional de conjunto de dados BigQuery para usar na materialização dos resultados da consulta. Se não for especificado, um conjunto de dados de materialização é auto-provisionado quando necessário. Consulte Materialização para mais informações.
   • BIGNUMERIC Default Scale: Um valor de escala opcional para mapear BigQuery BIGNUMERIC para Spark DecimalType. Consulte Mapeamentos de Tipos de Dados para mais informações.

SQL

Execute o seguinte comando SQL em um bloco de anotações ou no editor SQL do Databricks. Os itens entre parênteses são opcionais. Substitua os valores substitutos.

• <catalog-name>: Nome do catálogo no Azure Databricks.
• <connection-name>: O objeto de conexão que especifica a fonte de dados, o caminho e as credenciais de acesso.
• <data-project-id>: Um ID de projeto opcional do projeto BigQuery contendo dados a mapear para este catálogo. Se não especificado, é utilizado o ID do projeto definido na ligação, seguido do ID do projeto da conta do serviço.
• <dataset-name>: Um nome opcional de conjunto de dados BigQuery para usar na materialização dos resultados da consulta. Se não for especificado, um conjunto de dados de materialização é auto-provisionado quando necessário. Consulte Materialização para mais informações.
• <scale>: Um valor de escala opcional [0, 38] para mapear BigQuery BIGNUMERIC para Spark DecimalType(38, scale). A predefinição é 38. Consulte Mapeamentos de Tipos de Dados para mais informações.

CREATE FOREIGN CATALOG [IF NOT EXISTS] <catalog-name> USING CONNECTION <connection-name>
[OPTIONS (dataProjectId '<data-project-id>', materializationDataset '<dataset-name>', bigNumericDefaultScale '<scale>')];

Materialização

Ao contrário de outros conectores de federação, o conector BigQuery utiliza APIs de Armazenamento BigQuery em vez de JDBC para melhorar o desempenho. O Azure Databricks pode ler diretamente do BigQuery a partir do armazenamento ou usando um conjunto de dados materializado. As leituras diretas oferecem um melhor desempenho para grandes varreduras e suportam filtros e projeções. A materialização envia operações adicionais (limite, agregados, junções, ordenação) para o cálculo do BigQuery antes de transmitir os resultados para o Azure Databricks.

Visões e tabelas externas são sempre materializadas. Todas as outras leituras usam armazenamento direto sem materialização como padrão.

Considere ativar a materialização se precisar de otimizações avançadas, estiver a ler pequenos resultados de grandes conjuntos de dados, ou se estiver a ler dados entre regiões. A materialização implica cargas adicionais de cálculo no BigQuery. Para permitir a materialização, defina a seguinte configuração do Spark:

SET spark.databricks.bigquery.enableMaterialization = true;

Por defeito, um conjunto de dados de materialização é automaticamente provisionado quando necessário. Pode especificar um conjunto de dados personalizado usando a materializationDataset opção de catálogo ao criar ou alterar o catálogo estrangeiro. Isto é útil se a conta de serviço não tiver permissões para criar conjuntos de dados ou se quiser controlar onde estão armazenadas tabelas de materialização temporárias. Por exemplo:

CREATE FOREIGN CATALOG my_catalog USING CONNECTION my_bq_connection
OPTIONS (materializationDataset 'my_materialization_dataset');

Para atualizar um catálogo existente, execute:

ALTER CATALOG my_catalog OPTIONS (materializationDataset 'my_materialization_dataset');

Pushdowns suportados

Os seguintes pushdowns são suportados sem materialização:

• Filtros
• Projeções

Os seguintes pushdowns adicionais são suportados com materialização ativada:

• Limite
• Funções (suporte parcial, apenas expressões de filtro: funções de string, funções matemáticas, funções de data, hora e carimbo temporal, e outras funções diversas como Alias, Cast, SortOrder)
• Agregados
• Classificação, quando usado com limite
• Junções (Databricks Runtime 16.1 ou superior)

As seguintes flexões não são suportadas:

• Funções do Windows

Mapeamentos de tipo de dados

A tabela a seguir mostra o mapeamento de tipo de dados do BigQuery para o Spark.

* O BigQuery BIGNUMERIC tem uma precisão de até 76 dígitos, o que ultrapassa a precisão máxima DecimalType de 38 do Spark. Por defeito, BIGNUMERIC mapeia para DecimalType(38, 38). Para configurar a escala, use a opção de catálogo bigNumericDefaultScale. Os valores permitidos são [0, 38]. Por exemplo, bigNumericDefaultScale = '10' mapeia BIGNUMERIC para DecimalType(38, 10). O BigQuery NUMERIC corresponde à precisão e escala declaradas.

Quando você lê a partir do BigQuery, o BigQuery Timestamp é mapeado para o Spark TimestampType if preferTimestampNTZ = false (padrão). O BigQuery Timestamp é mapeado para TimestampNTZType if preferTimestampNTZ = true.

Solução de problemas

Error creating destination table using the following query [<query>]

Causa comum: a conta de serviço usada pela conexão não tem a função Usuário do BigQuery .

Resolução:

1. Conceda a função Usuário do BigQuery à conta de serviço usada pela conexão. Essa função é necessária para criar o conjunto de dados de materialização que armazena temporariamente os resultados da consulta.
2. Execute novamente a consulta.