Carregue dados com segurança usando Synapse SQL

Este artigo destaca 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 Synapse SQL.

Mecanismos de autenticação suportados

A matriz a seguir descreve os métodos de autenticação suportados para cada tipo de arquivo e conta de armazenamento. Isso se aplica ao local de armazenamento de origem e ao local do arquivo de erro.

1: O ponto de extremidade .blob (.blob.core.windows.net) no caminho do local externo é necessário para esse método de autenticação.

2: O ponto de extremidade .dfs (.dfs.core.windows.net) no caminho do local externo é necessário para esse método de autenticação.

A. Chave de conta de armazenamento com LF como terminador de linha (nova linha no 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)
)

B. Assinaturas de acesso compartilhado (SAS) com CRLF como terminador de linha (nova linha estilo 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.
)

C. Identidade Gerida

A autenticação de identidade gerenciada é necessária quando sua conta de armazenamento está conectada a uma rede virtual.

Pré-requisitos

1. Instale o Azure PowerShell. Consulte Instalar o PowerShell.
2. Se você tiver uma conta de armazenamento v1 ou blob de uso geral, primeiro deverá atualizar para v2 de uso geral. Consulte Atualizar para uma conta de armazenamento v2 de uso geral.
3. Você deve ter a opção Permitir que serviços confiáveis da Microsoft acessem essa conta de armazenamento ativada no menu Configurações de firewalls e redes virtuais da conta de armazenamento do Azure. Consulte Configurar firewalls de armazenamento do Azure e redes virtuais.

Passos

1. Se você tiver um pool SQL dedicado autônomo, registre seu servidor SQL com o Microsoft Entra ID 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 SQL dedicados em um espaço de trabalho Synapse. A identidade gerenciada atribuída ao sistema (SA-MI) do espaço de trabalho é um membro da função Synapse Administrator e, portanto, tem privilégios elevados nos pools SQL dedicados do espaço de trabalho.

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

   Nota

   • Se você tiver uma conta de armazenamento v1 ou blob de uso geral, você deve primeiro atualizar para v2. Para obter mais informações, veja Upgrade to a general-purpose v2 storage account (Atualizar para uma conta de armazenamento para fins gerais v2).
   • Para problemas conhecidos com o Azure Data Lake Storage Gen2, consulte 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ções para abrir o painel Adicionar atribuição de funções.

5. Atribua a seguinte função. Para obter os passos detalhados, veja o artigo Atribuir funções do Azure com o portal do Azure.

   Definição	Value
   Role	Contribuidor de Dados de Blobs de Armazenamento
   Atribuir acesso a	SERVICEPRINCIPAL
   Membros	servidor ou espaço de trabalho que hospeda seu pool SQL dedicado que você registrou com a ID do Microsoft Entra

   

   Nota

   Somente membros com privilégio de proprietário podem executar esta etapa. Para várias funções internas do Azure, consulte Funções internas do Azure.

   Importante

   Especifique a função Proprietário de Dados de Blob de Armazenamento, Colaborador ou Leitor do Azure. 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 "Managed Identity":

   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

Passos

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

2. Selecione Adicionar>Adicionar atribuição de funções para abrir o painel Adicionar atribuição de funções.

3. Atribua a seguinte função. Para obter os passos detalhados, veja o artigo Atribuir funções do Azure com o portal do Azure.

   Definição	Value
   Role	Proprietário, Colaborador ou Leitor de Dados de Blob de Armazenamento
   Atribuir acesso a	UTILIZADOR
   Membros	Usuário do Microsoft Entra

   

   Importante

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

   

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

5. Conecte-se ao pool SQL usando o Ative Directory, onde 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 do Principal de Serviço

Passos

1. Crie um aplicativo Microsoft Entra.

2. Obtenha o ID do aplicativo.

3. Obtenha a chave de autenticação.

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

5. Atribua permissões de leitura, gravação e execução ao seu aplicativo Microsoft Entra em 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')
   )
   

Próximos passos

• Verifique o artigo do artigo da instrução COPY para obter a sintaxe detalhada
• Consulte o artigo de visão geral do carregamento de dados para obter as práticas recomendadas de carregamento