Carregar dados com segurança usando o SQL do Synapse

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

   • Se você tiver uma conta de armazenamento de blobs ou de uso geral v1, primeiro, atualize-a para a v2. Para obter mais informações, confira Atualizar para uma conta de armazenamento de uso geral v2.
   • Em caso de problemas conhecidos com o Azure Data Lake Storage Gen2, veja Problemas conhecidos com o Azure Data Lake Storage Gen2.

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

   

   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 de Dados de Blob de Armazenamento. Essas funções são diferentes das funções internas do Azure de Proprietário, Colaborador e Leitor.

   

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

   

   Importante

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

   

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

• Conferir o artigo Instrução COPY para ver a sintaxe detalhada
• Conferir o artigo Visão geral de carregamento de dados para ver as melhores práticas de carregamento