Partilhar via

Carregue dados com segurança usando Synapse SQL

  • Artigo

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.

CSV Parquet ORC
Armazenamento de blobs do Azure SAS/MSI/ENTIDADE DE SERVIÇO/CHAVE/AAD SAS/CHAVE SAS/CHAVE
Azure Data Lake Gen2 SAS/MSI/ENTIDADE DE SERVIÇO/CHAVE/AAD SAS (blob1)/MSI (dfs2)/ENTIDADE DE SERVIÇO/CHAVE/AAD SAS (blob1)/MSI (dfs2)/ENTIDADE DE SERVIÇO/CHAVE/AAD

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)
)

Importante

  • Use o valor hexadecimal (0x0A) para especificar o caractere Alimentação de linha/Nova linha. Observe que a instrução COPY interpretará a \n cadeia de caracteres como \r\n (carriage return newline).

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.
)

Importante

Não especifique o ROWTERMINATOR como '\r\n' que será interpretado como '\r\r\n' e pode resultar em problemas de análise. O comando COPY prefixa automaticamente o caractere \r quando \n (newline) é especificado. Isso resulta em newline de retorno de carro (\r\n) para sistemas baseados no Windows.

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

    Adicionar página de atribuição de função no portal do Azure.

    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.

    Concedendo permissão do RBAC do Azure para carregar

  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

    Adicionar página de atribuição de função no portal 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.

    Concedendo permissão do RBAC do Azure para carregar

  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')
)

Importante

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

Próximos passos