Tutorial: Carregar dados externos usando uma identidade gerenciada

Este artigo explica como criar tabelas externas ou ingerir dados de contas do ADLS (Azure Data Lake Storage) Gen2 usando uma identidade gerenciada.

Pré-requisitos

Os seguintes recursos são necessários para concluir o tutorial:

• Uma conta do Azure Data Lake Storage (ADLS) Gen2
• Um workspace do Azure Synapse Analytics e um pool de SQL dedicado

Conceder acesso à identidade do workspace à conta de armazenamento

Cada workspace do Azure Synapse Analytics cria automaticamente uma identidade gerenciada que ajuda você a configurar o acesso seguro a dados externos do seu workspace. Para saber mais sobre identidades gerenciadas para o Azure Synapse Analytics, visite Identidade de serviço gerenciada para o Azure Synapse Analytics.

Para habilitar sua identidade gerenciada a acessar dados em contas do ADLS Gen2, você precisa fornecer à sua identidade acesso à conta de origem. Para conceder as permissões adequadas, siga estas etapas:

1. No portal do Azure, encontre a conta de armazenamento.
2. Selecione Armazenamento de dados –> Contêineres e navegue até a pasta onde estão os dados de origem aos quais a tabela externa precisa ter acesso.
3. Selecione IAM (Controle de acesso).
4. Selecione Adicionar –> Adicionar atribuição de função.
5. Na lista de funções de trabalho, selecione Colaborador de Dados do Blob de Armazenamento e selecione Avançar.
6. Na página Adicionar atribuição de função, selecione + Selecionar membros. O painel Selecionar membros é aberto.
7. Digite o nome da identidade do workspace. A identidade do workspace é a mesma que o nome do workspace. Quando exibido, escolha a identidade do seu workspace e, em seguida, Selecionar.
8. Na página Adicionar atribuição de função, verifique se a lista de membros inclui sua conta desejada do Microsoft Entra ID. Depois de verificado, selecione Examinar + atribuir.
9. Na página de confirmação, examine as alterações e selecione Examinar + atribuir.

Sua identidade do workspace agora é membro da função Colaborador de Dados do Blob de Armazenamento e tem acesso à pasta de origem.

Observação

Essas etapas também se aplicam a contas seguras do ADLS Gen2 configuradas para restringir o acesso público. Para saber mais sobre como proteger sua conta do ADLS Gen2, consulte Configurar firewalls do Armazenamento do Azure e redes virtuais.

Ingerir dados usando o COPY INTO

A instrução T-SQL COPY INTO fornece uma ingestão de dados flexível e de alta taxa de transferência para suas tabelas, sendo a estratégia principal para ingerir dados nas tabelas do seu pool de SQL dedicado. COPY INTO permite que os usuários ingiram dados de locais externos sem precisar criar os objetos adicionais do banco de dados necessários para tabelas externas.

Para executar a instrução COPY INTO usando uma identidade gerenciada de workspace para autenticação, use o seguinte comando T-SQL:

COPY INTO <TableName>
FROM 'https://<AccountName>.dfs.core.windows.net/<Container>/<Folder>/ '
WITH
(
    CREDENTIAL = (IDENTITY = 'Managed Identity'),
    [<CopyIntoOptions>]
);

Onde:

• <TableName> é o nome da tabela na qual os dados serão ingeridos
• <AccountName> é o nome da sua conta do ADLS Gen2
• <Container> é o nome do contêiner em sua conta de armazenamento em que os dados de origem são armazenados
• <Folder> é a pasta (ou caminho com subpastas) em que os dados de origem são armazenados em seu contêiner. Você também pode fornecer um nome de arquivo se estiver apontando diretamente para um único arquivo.
• <CopyIntoOptions> é a lista de outras opções que você deseja fornecer à instrução COPY INTO.

Para saber mais e explorar a sintaxe completa do COPY INTO, consulte COPY INTO (Transact-SQL).

Consultar dados no ADLS Gen2 usando tabelas externas

As tabelas externas permitem que os usuários consultem dados de contas do ADLS (Azure Data Lake Storage) Gen2 sem a necessidade de ingerir dados primeiro. Os usuários podem criar uma tabela externa que aponta para arquivos em um contêiner do ADLS Gen2 e consultá-la como uma tabela de usuário regular.

As etapas a seguir descrevem o processo para criar uma nova tabela externa apontando para dados no ADLS Gen2, usando uma identidade gerenciada para autenticação.

Criar os objetos de banco de dados necessários

As tabelas externas exigem que os seguintes objetos sejam criados:

1. Uma chave mestra de banco de dados que criptografa o segredo da credencial com escopo de banco de dados
2. Uma credencial com escopo de banco de dados que usa sua identidade de workspace
3. Uma fonte de dados externa que aponta para a pasta de origem
4. Um formato de arquivo externo que define o formato dos arquivos de origem
5. Uma definição de tabela externa usada para consultas

Para seguir estas etapas, use o editor de SQL no Workspace do Azure Synapse ou seu cliente SQL preferido conectado ao pool de SQL dedicado. Vamos examinar essas etapas em detalhes.

Criar chave mestra do banco de dados

A chave mestra do banco de dados é uma chave simétrica usada para proteger as chaves privadas dos certificados e as chaves assimétricas presentes no banco de dados e segredos nas credenciais com escopo de banco de dados. Se já houver uma chave mestra no banco de dados, você não precisará criar uma nova. Substitua <Secure Password> por uma senha segura. Essa senha é usada para criptografar a chave mestra no banco de dados.

Para criar uma chave mestra, use o seguinte comando T-SQL:

-- Replace <Secure Password> with a secure password
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Secure Password>';

Para saber mais sobre a chave mestra do banco de dados, consulte CREATE MASTER KEY (Transact-SQL).

Criar credencial no escopo do banco de dados

Uma credencial com escopo de banco de dados usa sua identidade de workspace e é necessária para acessar o local externo sempre que a tabela externa exigir acesso aos dados de origem.

Para criar a credencial no escopo do banco de dados, use o comando a seguir. Substitua <CredentialName> pelo nome que você gostaria de usar para sua credencial com escopo de banco de dados.

CREATE DATABASE SCOPED CREDENTIAL <CredentialName> WITH IDENTITY = 'Managed Service Identity';

Para saber mais sobre as credenciais com escopo de banco de dados, consulte CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL).

Criar a fonte de dados externa

A próxima etapa é criar uma fonte de dados externa que especifique onde residem os dados de origem usados pela tabela externa.

Para criar a fonte de dados externa, use o seguinte comando T-SQL:

CREATE EXTERNAL DATA SOURCE <ExternalDataSourceName>
WITH (
    TYPE = HADOOP,
    LOCATION = 'abfss://<Container>@<AccountName>.dfs.core.windows.net/<Folder>/,
    CREDENTIAL = <CredentialName>
);

Onde:

• <ExternalDataSourceName> é o nome que você deseja usar para sua fonte de dados externa.
• <AccountName> é o nome da sua conta do ADLS Gen2.
• <Container> é o nome do contêiner em sua conta de armazenamento em que os dados de origem são armazenados.
• <Folder> é a pasta (ou caminho com subpastas) em que os dados de origem são armazenados em seu contêiner. Você também pode fornecer um nome de arquivo se estiver apontando diretamente para um único arquivo.
• <Credential> é o nome da credencial com escopo de banco de dados que você criou anteriormente.

Para saber mais sobre fontes de dados externas, consulte CREATE EXTERNAL DATA SOURCE (Transact-SQL).

Criar o formato de arquivo externo

A próxima etapa é criar o formato de arquivo externo. Ele especifica o layout real dos dados referenciados pela tabela externa.

Para criar o formato de arquivo externo, use o comando T-SQL a seguir. Substitua <FileFormatName> pelo nome que você deseja usar para o formato de arquivo externo.

CREATE EXTERNAL FILE FORMAT <FileFormatName>
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS (
        FIELD_TERMINATOR = ',',
        STRING_DELIMITER = '"',
        FIRST_ROW = 2,
        USE_TYPE_DEFAULT = True
    )
);

Neste exemplo, ajuste parâmetros como FIELD_TERMINATOR, STRING_DELIMITER, FIRST_ROW e outros, conforme necessário, de acordo com seus dados de origem. Para obter mais opções de formato e saber mais sobre EXTERNAL FILE FORMAT, consulte CREATE EXTERNAL FILE FORMAT.

Criar a tabela externa

Agora que todos os objetos necessários que contêm os metadados para acessar os dados externos com segurança foram criados, é hora de criar a tabela externa. Para criar a tabela externa, use o seguinte comando T-SQL:

-- Adjust the table name and columns to your desired name and external table schema
CREATE EXTERNAL TABLE <ExternalTableName> (
    Col1 INT,
    Col2 NVARCHAR(100),
    Col4 INT
)
WITH
(
    LOCATION = '<Path>',
    DATA_SOURCE = <ExternalDataSourceName>,
    FILE_FORMAT = <FileFormatName>
);

Onde:

• <ExternalTableName> é o nome que você deseja usar para sua tabela externa.
• <Path> é o caminho dos dados de origem, em relação ao local especificado na fonte de dados externa.
• <ExternalDataSourceName> é o nome da fonte de dados externa que você criou anteriormente.
• <FileFormatName> é o nome do formato de arquivo externo criado anteriormente.

Ajuste o nome da tabela e o esquema para o nome desejado e o esquema dos dados em seus arquivos de origem.

Neste ponto, todos os metadados necessários para acessar a tabela externa estão criados. Para testar sua tabela externa, use uma consulta como o seguinte exemplo T-SQL para validar seu trabalho:

SELECT TOP 10 Col1, Col2 FROM <ExternalTableName>;

Se tudo estiver configurado corretamente, você deverá ver os dados de seus dados de origem como resultado dessa consulta.

Para saber mais e explorar a sintaxe completa de CREATE EXTERNAL TABLE, consulte CREATE EXTERNAL TABLE (Transact-SQL).

Conteúdo relacionado

• Tutorial: Carregar dados externos usando uma identidade gerenciada
• Carregar dados de varejo da Contoso em pools de SQL dedicados no Azure Synapse Analytics
• Identidades gerenciadas para o Azure Synapse Analytics