Consultar arquivos Delta Lake usando o pool de SQL sem servidor no Azure Synapse Analytics

O workspace do Azure Synapse Analytics permite criar dois tipos de bancos de dados sobre um data lake do Spark:

• Bancos de dados do Lake em que você pode definir tabelas sobre dados do Lake usando notebooks Apache Spark, modelos de banco de dados ou Microsoft Dataverse (anteriormente Common Data Service). Essas tabelas estarão disponíveis para consulta usando a linguagem T-SQL (Transact-SQL) usando o pool de SQL sem servidor.
• Bancos de dados SQL nos quais você pode definir seus bancos de dados e tabelas diretamente usando os pools de SQL sem servidor. Você pode usar T-SQL CREATE DATABASE, CREATE EXTERNAL TABLE para definir os objetos e adicionar exibições adicionais de SQL, procedimentos e funções de valor de tabela embutida nas tabelas.

Este artigo se concentra em bancos de dados do Lake em um pool de SQL sem servidor no Azure Synapse Analytics.

Azure Synapse Analytics permite criar bancos de dados e tabelas do Lake usando o Spark ou o designer de banco de dados e, em seguida, analisar dados nos bancos de dados do Lake usando o pool de SQL sem servidor. Os bancos de dados do Lake e as tabelas (com backup em Parquet ou CSV) criadas nos Pools do Apache Spark, modelos de banco de dados ou Dataverse são disponibilizados automaticamente para consulta com o mecanismo de pool de SQL sem servidor. Os bancos de dados e tabelas do Lake modificados estarão disponíveis no pool de SQL sem servidor após algum tempo. Haverá um atraso até que as alterações feitas no Spark ou no Banco de Dados projetado apareçam no sem servidor.

Gerenciar banco de dados do Lake

Para gerenciar bancos de dados do Lake criados do Spark, você pode usar os Pools do Apache Spark ou o Designer de banco de dados. Por exemplo, crie ou exclua um banco de dados do Lake por meio de um trabalho de pool do Spark. Você não pode criar um banco de dados do Lake ou os objetos nos bancos de dados do Lake usando o pool de SQL sem servidor.

O banco de dados Spark default está disponível no contexto do pool de SQL sem servidor como um banco de dados Lake chamado default.

Observação

Não é possível criar um Lake e um banco de dados SQL no pool de SQL sem servidor com o mesmo nome.

Tabelas nos bancos de dados do Lake não podem ser modificados do pool de SQL sem servidor. Use o Designer de banco de dados ou Pools do Apache Spark para modificar um banco de dados do Lake. O pool de SQL sem servidor permite que você faça as seguintes alterações em um banco de dados do Lake usando comandos Transact-SQL:

• Adicionando, alterando e descartando exibições, procedimentos, funções de valor de tabela embutidas em um banco de dados do Lake.
• Adicionando e removendo usuários de Microsoft Entra com escopo de banco de dados.
• Adicione ou remova usuários do banco de dados do Microsoft Entra da função db_datareader. Usuários de banco de dados do Microsoft Entra na função db_datareader têm permissão para ler todas as tabelas no banco de dados do lake, mas não podem ler dados de outros bancos de dados.

Modelo de segurança

Os bancos de dados e tabelas do Lake são protegidos em dois níveis:

• A camada de armazenamento subjacente atribuindo aos usuários Microsoft Entra um dos seguintes:
  • RBAC do Azure (controle de acesso baseado em função do Azure)
  • ABAC do Azure (controle de acesso baseado em atributo do Azure)
  • Permissões de ACL
• A camada SQL em que você pode definir um usuário do Microsoft Entra e conceder permissões SQL para SELECIONAR dados de tabelas que fazem referência aos dados do lake.

Modelo de segurança do Lake

O acesso aos arquivos do banco de dados do Lake é controlado usando as permissões do Lake na camada de armazenamento. Somente usuários do Microsoft Entra podem usar tabelas nos bancos de dados do Lake e podem acessar os dados no Lake usando as próprias identidades.

Você pode conceder acesso aos dados subjacentes usados para tabelas externas a uma entidade de segurança, como: um usuário, um aplicativo Microsoft Entra com uma entidade de serviço atribuída ou um grupo de segurança. Para acesso a dados, conceda ambas as seguintes permissões:

• Conceda a permissão read (R) em arquivos (como os arquivos de dados subjacentes da tabela).
• Conceda a permissão execute (X) na pasta em que os arquivos são armazenados e em cada pasta pai até a raiz. Você pode ler mais sobre essas permissões na página ACLs (listas de controle de acesso).

Por exemplo, em https://<storage-name>.dfs.core.windows.net/<fs>/synapse/workspaces/<synapse_ws>/warehouse/mytestdb.db/myparquettable/, as entidades de segurança precisam:

• Permissões execute (X) em todas as pastas que começam em <fs> até myparquettable.
• Permissões read (R) em myparquettable e nos arquivos dentro dessa pasta, para que possam ler uma tabela em um banco de dados (sincronizado ou original).

Se uma entidade de segurança exigir a capacidade de criar ou remover objetos de um banco de dados, serão necessárias permissões write (W) adicionais nas pastas e nos arquivos da pasta warehouse. A modificação de objetos em um banco de dados não é possível do pool de SQL sem servidor, somente a partir de Pools do Spark e designer de banco de dados.

Modelo de segurança do SQL

O workspace do Azure Synapse fornece um ponto de extremidade T-SQL que permite consultar o banco de dados do Lake usando o pool de SQL sem servidor. Além do acesso a dados, a interface SQL permite controlar quem pode acessar as tabelas. Você precisa habilitar um usuário para acessar os bancos de dados do Lake compartilhados usando o pool de SQL sem servidor. Há dois tipos de usuários que podem acessar os bancos de dados do lake:

• Administradores: atribua a função de workspace do Administrador do SQL do Synapse ou a função de nível de servidor sysadmin dentro do pool de SQL sem servidor. Essa função tem controle total sobre todos os bancos de dados. As funções de Administrador do Synapse e Administrador do SQL do Synapse também têm todas as permissões em todos os objetos sincronizados no pool de SQL sem servidor por padrão.
• Leitores de workspace: conceda as permissões de nível de servidor GRANT CONNECT ANY DATABASE e GRANT SELECT ALL USER SECURABLES no pool de SQL sem servidor a um logon que permitirá que o logon acesse e leia qualquer banco de dados. Essa pode ser uma boa opção para atribuir acesso de leitor/não administrador a um usuário.
• Leitores de banco de dados: crie usuários de banco de dados do Microsoft Entra ID no banco de dados do Lake e adicione-os à função db_datareader, o que permitirá que eles leiam dados no banco de dados do Lake.

Saiba mais sobre como definir o controle de acesso em bancos de dados compartilhados aqui.

Objetos SQL personalizados em bancos de dados do Lake

Os bancos de dados do Lake permitem a criação de objetos T-SQL personalizados, como esquemas, procedimentos, exibições e iTVFs (funções de valor-tabela embutidas). Para criar objetos SQL personalizados, você precisa criar um esquema no qual colocará os objetos. Objetos SQL personalizados não podem ser colocados no esquema dbo porque são reservados para as tabelas do Lake definidas no Spark, no designer de banco de dados ou no Dataverse.

Importante

Você precisa criar um esquema SQL personalizado no qual colocará seus objetos SQL. Os objetos SQL personalizados não podem ser colocados no esquema dbo. O esquema dbo é reservado para as tabelas do Lake que são criadas originalmente no Spark ou no designer de banco de dados.

Exemplos

Criar leitor de banco de dados SQL no banco de dados do Lake

Neste exemplo, estamos adicionando um usuário do Microsoft Entra no banco de dados do Lake que pode ler dados por meio de tabelas compartilhadas. Os usuários são adicionados no banco de dados do Lake por meio do pool de SQL sem servidor. Em seguida, atribua o usuário à função db_datareader para que ele possa ler dados.

CREATE USER [customuser@contoso.com] FROM EXTERNAL PROVIDER;
GO
ALTER ROLE db_datareader
ADD MEMBER [customuser@contoso.com];

Criar leitor de dados no nível do workspace

Um logon com as permissões GRANT CONNECT ANY DATABASE e GRANT SELECT ALL USER SECURABLES pode ler todas as tabelas usando o pool de SQL sem servidor, mas não é capaz de criar bancos de dados SQL nem modificar os objetos neles.

CREATE LOGIN [wsdatareader@contoso.com] FROM EXTERNAL PROVIDER
GRANT CONNECT ANY DATABASE TO [wsdatareader@contoso.com]
GRANT SELECT ALL USER SECURABLES TO [wsdatareader@contoso.com]

Esse script permite que você crie usuários sem privilégios de administrador que possam ler qualquer tabela nos bancos de dados do Lake.

Criar um banco de dados do Spark e se conectar a ele com o pool de SQL sem servidor

Primeiro, crie um banco de dados do Spark chamado mytestdb usando um cluster Spark que você já criou no workspace. Você pode fazer isso, por exemplo, usando um notebook C# do Spark com a seguinte instrução .NET para Spark:

spark.sql("CREATE DATABASE mytestlakedb")

Após um pequeno atraso, você poderá ver o banco de dados do Lake no pool de SQL sem servidor. Por exemplo, execute a instrução a seguir no pool de SQL sem servidor.

SELECT * FROM sys.databases;

Verifique se mytestlakedb está incluída nos resultados.

Criar objetos SQL personalizados no banco de dados do Lake

O seguinte exemplo mostra como criar uma exibição, procedimento e iTVF (função de tabela-valor embutida) no esquema reports:

CREATE SCHEMA reports
GO

CREATE OR ALTER VIEW reports.GreenReport
AS SELECT puYear, puMonth,
            fareAmount = SUM(fareAmount),
            tipAmount = SUM(tipAmount),
            mtaTax = SUM(mtaTax)
FROM dbo.green
GROUP BY puYear, puMonth
GO

CREATE OR ALTER PROCEDURE reports.GreenReportSummary
AS BEGIN
SELECT puYear, puMonth,
            fareAmount = SUM(fareAmount),
            tipAmount = SUM(tipAmount),
            mtaTax = SUM(mtaTax)
FROM dbo.green
GROUP BY puYear, puMonth
END
GO

CREATE OR ALTER FUNCTION reports.GreenDataReportMonthly(@year int)
RETURNS TABLE
RETURN ( SELECT puYear = @year, puMonth,
                fareAmount = SUM(fareAmount),
                tipAmount = SUM(tipAmount),
                mtaTax = SUM(mtaTax)
        FROM dbo.green
        WHERE puYear = @year
        GROUP BY puMonth )
GO

Próximas etapas

• Saiba mais sobre os metadados compartilhados do Azure Synapse Analytics
• Saiba mais sobre as tabelas de metadados compartilhados do Azure Synapse Analytics
• Início Rápido: criar um novo banco de dados Lake aproveitando modelos de banco de dados
• Tutorial: usar o pool de SQL sem servidor com o Power BI Desktop e criar um relatório
• Sincronizar o Apache Spark para definições de tabela externa do Azure Synapse no pool de SQL sem servidor
• Tutorial: Explorar e analisar data lakes com o pool de SQL sem servidor