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='<secret>'),
    ,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%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

No especifique ROWTERMINATOR como "\r\n", ya que se interpretará como "\r\r\n" y puede dar lugar a problemas de análisis. El comando COPY agrega automáticamente el prefijo al carácter \r si se especifica \n (nueva línea). Esto da como resultado una nueva línea de retorno de carro (\r\n) para sistemas basados en Windows.

C. Identidad administrada

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

Requisitos previos

1. Instale Azure PowerShell. Consulte Instalación de PowerShell.
2. Si tiene una cuenta de uso general v1 o de Blob Storage, primero debe actualizar a Uso general v2. Consulte Actualización a una cuenta de almacenamiento de uso general v2.
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 Configuración de redes virtuales y firewalls de Azure Storage.

Pasos

1. Si tiene un grupo de SQL dedicado independiente, registre el servidor SQL Server con Microsoft Entra ID 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. El identidad administrada asignada por el sistema (SA-MI) del área de trabajo es miembro del rol de Administrador de Synapse y, por consiguiente, tiene privilegios elevados en los grupos de SQL dedicados del área de trabajo.

2. Cree una cuenta de almacenamiento de uso general v2. Para obtener más información, consulte Creación de una cuenta de almacenamiento.

   Nota

   • Si tiene una cuenta de uso general v1 o de Blob Storage, primero debe actualizar a v2. Para más información, consulte Actualización a una cuenta de almacenamiento de uso general v2.
   • Para más información sobre los problemas conocidos con Azure Data Lake Storage Gen2, consulte Problemas conocidos con Azure Data Lake Storage Gen2.

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

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

5. 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 Microsoft Entra ID

   

   Nota:

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

   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.

   

6. 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 de Microsoft Entra

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 Microsoft Entra

   

   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.

   

4. Configure la autenticación de Microsoft Entra. Consulte Configuración y administración de la autenticación de Microsoft Entra con Azure SQL.

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 Microsoft Entra.

2. Obtenga el identificador de la aplicación.

3. Obtenga la clave de autenticación.

4. Obtenga 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 Microsoft Entra 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

• Consulte el artículo sobre la instrucción COPY para ver la sintaxis detallada
• Consulte el artículo con información general sobre la carga de datos para ver los procedimientos recomendados de la carga.