Carregar dados com segurança usando o SQL do Synapse

  • Artigo

Este artigo realça e fornece exemplos sobre os mecanismos de autenticação segura para a Instrução COPY. A instrução COPY é a maneira mais flexível e segura de carregar dados em massa no SQL do Synapse.

Mecanismos de autenticação compatíveis

A matriz a seguir descreve os métodos de autenticação compatíveis com cada tipo de arquivo e conta de armazenamento. Isso se aplica ao local de armazenamento de origem e ao local do arquivo de erros.

CSV Parquet ORC
Armazenamento de Blobs do Azure SAS/MSI/SERVICE PRINCIPAL/KEY/AAD SAS/KEY SAS/KEY
Azure Data Lake Gen2 SAS/MSI/SERVICE PRINCIPAL/KEY/AAD SAS (blob1)/MSI (dfs2)/SERVICE PRINCIPAL/KEY/AAD SAS (blob1)/MSI (dfs2)/SERVICE PRINCIPAL/KEY/AAD

1. O ponto de extremidade .blob (.blob.core.windows.net) no caminho de localização externa é obrigatório nesse método de autenticação.

2. O ponto de extremidade .dfs (.dfs.core.windows.net) no caminho de localização externa é obrigatório nesse método de autenticação.

a. Chave de conta de armazenamento com LF como terminador de linha (nova linha em estilo UNIX)

--Note when specifying the column list, input field numbers start from 1
COPY INTO target_table (Col_one default 'myStringDefault' 1, Col_two default 1 3)
FROM 'https://adlsgen2account.dfs.core.windows.net/myblobcontainer/folder1/'
WITH (
    FILE_TYPE = 'CSV'
    ,CREDENTIAL=(IDENTITY= 'Storage Account Key', SECRET='<Your_Account_Key>')
    --CREDENTIAL should look something like this:
    --CREDENTIAL=(IDENTITY= 'Storage Account Key', SECRET='<secret>'),
    ,ROWTERMINATOR='0x0A' --0x0A specifies to use the Line Feed character (Unix based systems)
)

Importante

  • Use o valor hexadecimal (0x0A) para especificar o caractere de alimentação de linha/nova linha. Observe que a instrução COPY interpretará a cadeia de caracteres \n como \r\n (nova linha de retorno de carro).

B. SAS (Assinaturas de Acesso Compartilhado) com o CRLF como término de linha (nova linha no estilo do Windows)

COPY INTO target_table
FROM 'https://adlsgen2account.dfs.core.windows.net/myblobcontainer/folder1/'
WITH (
    FILE_TYPE = 'CSV'
    ,CREDENTIAL=(IDENTITY= 'Shared Access Signature', SECRET='<Your_SAS_Token>')
    --CREDENTIAL should look something like this:
    --CREDENTIAL=(IDENTITY= 'Shared Access Signature', SECRET='?sv=2018-03-28&ss=bfqt&srt=sco&sp=rl&st=2016-10-17T20%3A14%3A55Z&se=2021-10-18T20%3A19%3A00Z&sig=IEoOdmeYnE9%2FKiJDSFSYsz4AkN'),
    ,ROWTERMINATOR='\n'-- COPY command automatically prefixes the \r character when \n (newline) is specified. This results in carriage return newline (\r\n) for Windows based systems.
)

Importante

Não especifique o ROWTERMINATOR como '\r\n', pois isso será interpretado como '\r\r\n' e poderá resultar em problema de análise. O comando COPY prefixa automaticamente o caractere \r quando \n (nova linha) é especificado. Isso resulta na nova linha de retorno de carro (\r\n) para sistemas baseados no Windows.

C. Identidade Gerenciada

A autenticação de identidade gerenciada é obrigatória quando sua conta de armazenamento é anexada a uma VNet.

Pré-requisitos

  1. Instale o PowerShell do Azure. Veja Instalar o PowerShell.
  2. Se você tiver uma conta de armazenamento de blobs ou de uso geral v1, primeiro, atualize-a para uso geral v2. Veja Atualizar para uma conta de armazenamento de uso geral v2.
  3. É necessário ativar Permitir que os serviços confiáveis da Microsoft acessem essa conta de armazenamento no menu de configurações Firewalls e redes virtuais da conta do Armazenamento do Azure. Veja Configurar redes virtuais e firewalls do Armazenamento do Azure.

Etapas

  1. Se você tiver um pool de SQL dedicado autônomo, registre seu SQL Server com a ID do Microsoft Entra usando o PowerShell:

    Connect-AzAccount
Select-AzSubscription -SubscriptionId <subscriptionId>
Set-AzSqlServer -ResourceGroupName your-database-server-resourceGroup -ServerName your-SQL-servername -AssignIdentity

    Esta etapa não é necessária para pools de SQL dedicados em um workspace do Azure Synapse. A identidade gerenciada atribuída pelo sistema (SA-MI) do workspace é membro da função de Administrador do Synapse e, portanto, tem privilégios elevados nos pools de SQL dedicados do workspace.

  2. Crie uma conta de armazenamento de uso geral v2. Para obter mais informações, consulte Criar uma conta de armazenamento.

    Observação

  3. Em sua conta de armazenamento, selecione Controle de acesso (IAM) .

  4. Selecione Adicionar>Adicionar atribuição de função para abrir a página Adicionar atribuição de função.

  5. Atribua a função a seguir. Para ver as etapas detalhadas, confira Atribuir funções do Azure usando o portal do Azure.

    Configuração Valor
    Função Colaborador de dados de blob de armazenamento
    Atribuir acesso a SERVICEPRINCIPAL
    Membros servidor ou workspace que hospeda seu pool de SQL dedicado que você registrou com a ID do Microsoft Entra

    Add role assignment page in Azure portal.

    Observação

    Somente membros com o privilégio Proprietário podem executar essa etapa. Para conhecer as várias funções internas do Azure, consulte Funções internas do Azure.

    Importante

    Especifique a função do Azure de Proprietário, Colaborador ou Leitor do ArmazenamentoDados de Blob. Essas funções são diferentes das funções internas do Azure de Proprietário, Colaborador e Leitor.

    Granting Azure RBAC permission to load

  6. Agora você pode executar a instrução COPY especificando "Identidade Gerenciada":

    COPY INTO dbo.target_table
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/*.txt'
WITH (
    FILE_TYPE = 'CSV',
    CREDENTIAL = (IDENTITY = 'Managed Identity'),
)

D. autenticação do Microsoft Entra

Etapas

  1. Em sua conta de armazenamento, selecione Controle de acesso (IAM) .

  2. Selecione Adicionar>Adicionar atribuição de função para abrir a página Adicionar atribuição de função.

  3. Atribua a função a seguir. Para ver as etapas detalhadas, confira Atribuir funções do Azure usando o portal do Azure.

    Configuração Valor
    Função Proprietário, colaborador ou leitor dos Dados de Blob de Armazenamento
    Atribuir acesso a USER
    Membros Usuário do Microsoft Entra

    Add role assignment page in Azure portal.

    Importante

    Especifique a função do Azure de Proprietário, Colaborador ou Leitor do ArmazenamentoDados de Blob. Essas funções são diferentes das funções internas do Azure de Proprietário, Colaborador e Leitor.

    Granting Azure RBAC permission to load

  4. Configurar a autenticação do Microsoft Entra. Consulte Configurar e gerenciar a autenticação do Microsoft Entra com o SQL do Azure.

  5. Conecte-se ao seu pool de SQL usando o Active Directory, em que agora você pode executar a instrução COPY sem especificar nenhuma credencial:

    COPY INTO dbo.target_table
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/*.txt'
WITH (
    FILE_TYPE = 'CSV'
)

E. Autenticação de entidade de serviço

Etapas

  1. Criar um aplicativo do Microsoft Entra.

  2. Obter a ID do aplicativo.

  3. Obter a chave de autenticação.

  4. Obter o ponto de extremidade do token OAuth 2.0 V1.

  5. Atribuir permissões de leitura, gravação e execução ao aplicativo Microsoft Entra na sua conta de armazenamento.

  6. Agora você pode executar a instrução COPY:

    COPY INTO dbo.target_table
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder0/*.txt'
WITH (
    FILE_TYPE = 'CSV'
    ,CREDENTIAL=(IDENTITY= '<application_ID>@<OAuth_2.0_Token_EndPoint>' , SECRET= '<authentication_key>')
    --CREDENTIAL should look something like this:
    --,CREDENTIAL=(IDENTITY= '92761aac-12a9-4ec3-89b8-7149aef4c35b@https://login.microsoftonline.com/72f714bf-86f1-41af-91ab-2d7cd011db47/oauth2/token', SECRET='juXi12sZ6gse]woKQNgqwSywYv]7A.M')
)

Importante

Use a versão V1 do ponto de extremidade do token OAuth 2.0

Próximas etapas