Carga de datos de forma segura mediante el uso de Synapse SQL

En este artículo se resaltan los mecanismos de autenticación segura para la instrucción COPY y se muestran ejemplos al respecto. La instrucción COPY es la forma más flexible y segura de cargar datos de forma masiva en Synapse SQL.

Mecanismos de autenticación compatibles

En la siguiente matriz se describen los métodos de autenticación compatibles tanto con cada tipo de archivo como con una cuenta de almacenamiento. Esto se aplica a la ubicación de almacenamiento de origen y a la ubicación del archivo de error.

CSV Parquet ORC
Azure Blob Storage 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: Para este método de autenticación, se requiere el punto de conexión .blob ( .blob.core.windows.net) en la ruta de acceso a la ubicación externa.

2: Para este método de autenticación, se requiere el punto de conexión .dfs ( .dfs.core.windows.net) en la ruta de acceso a la ubicación externa.

A. Clave de cuenta de almacenamiento con LF como terminador de fila (nueva línea de 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='x6RWv4It5F2msnjelv3H4DA80n0QW0daPdw43jM0nyetx4c6CpDkdj3986DX5AHFMIf/YN4y6kkCnU8lb+Wx0Pj+6MDw=='),
    ,ROWTERMINATOR='0x0A' --0x0A specifies to use the Line Feed character (Unix based systems)
)

Importante

  • Use el valor hexadecimal (0x0A) para especificar el carácter de avance de línea/nueva línea. Tenga en cuenta que la instrucción COPY interpretará la cadena "\n" como "\r\n" (nueva línea de retorno de carro).

B. Firmas de acceso compartido (SAS) con CRLF como terminador de fila (nueva línea con estilo de 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%2FKiJDSFSYsz4AkNa%2F%2BTx61FuQ%2FfKHefqoBE%3D'),
    ,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

  • No especifique ROWTERMINATOR como "\r\n", lo que se interpretará como "\r\r\n" y puede dar lugar a problemas de análisis.

C. Identidad administrada

La autenticación de Identidad administrada es necesaria cuando la cuenta de almacenamiento está conectada a una red virtual.

Prerrequisitos

  1. Instale Azure PowerShell mediante esta guía.
  2. Si tiene una cuenta de uso general v1 o de Blob Storage, primero debe actualizar a Uso general v2 mediante esta guía.
  3. Debe activar Permitir que los servicios de Microsoft de confianza accedan a esta cuenta de almacenamiento en el menú de configuración Firewalls y redes virtuales de la cuenta de Azure Storage. Consulte esta guía para obtener más información.

Pasos

  1. Si tiene un grupo de SQL dedicado independiente, registre el servidor SQL con Azure Active Directory (AAD) mediante PowerShell:

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

    Este paso no es necesario con grupos de SQL dedicados que se encuentran en un área de trabajo de Synapse.

  2. Si tiene un área de trabajo de Synapse, registre la identidad administrada por el sistema de dicha área de trabajo:

    1. Vaya al área de trabajo de Synapse en Azure Portal.
    2. Vaya a la hoja Identidades administradas.
    3. Asegúrese de que la opción "Allow Pipelines" (Permitir canalizaciones) está habilitada.

    Registro de la identidad administrada por el sistema del área de trabajo

  3. Cree una cuenta de almacenamiento de uso general v2 con esta guía.

    Nota

    • Si tiene una cuenta de uso general v1 o de Blob Storage, primero debe actualizar a Uso general v2 mediante esta guía.
    • Para saber los problemas conocidos con Azure Data Lake Storage Gen2, consulte esta guía.
  4. En la cuenta de almacenamiento, seleccione Control de acceso (IAM) .

  5. Seleccione Agregar > Agregar asignación de roles para abrir la página Agregar asignación de roles.

  6. Asigne el siguiente rol. Para asignar roles, consulte Asignación de roles de Azure mediante Azure Portal.

    Configuración Valor
    Role Colaborador de datos de blobs de almacenamiento
    Asignar acceso a SERVICEPRINCIPAL
    Miembros Servidor o área de trabajo que hospeda el grupo de SQL dedicado que ha registrado con Azure Active Directory (AAD)

    Página Agregar asignación de roles en Azure Portal.

    Nota

    Solo los miembros con el privilegio Propietario pueden realizar este paso. Para conocer los distintos roles integrados de Azure, consulte esta guía.

    Importante

    Especifique los roles de Azure Propietario, Colaborador o Lector de los datos de Storage Blob. Estos roles son diferentes de los roles integrados de Azure de Propietario, Colaborador y Lector.

    Concesión de permiso de RBAC de Azure para la carga

  7. Ya puede ejecutar la instrucción COPY especificando "Identity administrada":

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

D. Autenticación con Azure Active Directory

Pasos

  1. En la cuenta de almacenamiento, seleccione Control de acceso (IAM) .

  2. Seleccione Agregar > Agregar asignación de roles para abrir la página Agregar asignación de roles.

  3. Asigne el siguiente rol. Para asignar roles, consulte Asignación de roles de Azure mediante Azure Portal.

    Configuración Valor
    Role Propietario, Colaborador o Lector de datos de Storage Blob
    Asignar acceso a USER
    Miembros Usuario de Azure AD

    Página Agregar asignación de roles en Azure Portal.

    Importante

    Especifique los roles de Azure Propietario, Colaborador o Lector de los datos de Storage Blob. Estos roles son diferentes de los roles integrados de Azure de Propietario, Colaborador y Lector.

    Concesión de permiso de RBAC de Azure para la carga

  4. Para configurar la autenticación de Azure AD, consulte la siguiente documentación.

  5. Conéctese a su grupo de SQL mediante Active Directory, donde ahora puede ejecutar la instrucción COPY sin especificar ninguna credencial:

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

E. Autenticación de la entidad de servicio

Pasos

  1. Crear una aplicación de Azure Active Directory

  2. Obtención del identificador de la aplicación

  3. Obtención de la clave de autenticación

  4. Obtención de la versión V1 del punto de conexión de token de OAuth 2.0

  5. Asignación de permisos de lectura, escritura y ejecución a una aplicación de Azure AD en la cuenta de almacenamiento

  6. Ya puede ejecutar la instrucción 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 la versión V1 del punto de conexión de token de OAuth 2.0

Pasos siguientes