Compartir vía

Tutorial: Cargar datos externos mediante una identidad administrada

  • Artículo

En este artículo se explica cómo crear tablas externas o ingerir datos de cuentas de Azure Data Lake Storage (ADLS) Gen2 mediante una identidad administrada.

Requisitos previos

Los recursos siguientes son necesarios para completar este tutorial:

  • Una cuenta de Azure Data Lake Storage (ADLS) Gen2
  • Un grupo de SQL dedicado y un área de trabajo de Azure Synapse Analytics

Conceder a la identidad del área de trabajo acceso a la cuenta de almacenamiento

Cada área de trabajo de Azure Synapse Analytics crea automáticamente una identidad administrada que le ayuda a configurar el acceso seguro a los datos externos del área de trabajo. Para obtener más información sobre las identidades administradas para Azure Synapse Analytics, visite Identidad de servicio administrada para Azure Synapse Analytics.

Para permitir que la identidad administrada acceda a los datos de las cuentas de ADLS Gen2, debe proporcionar a su identidad acceso a la cuenta de origen. Para conceder los permisos adecuados, siga estos pasos:

  1. En Azure Portal, busque la cuenta de almacenamiento.
  2. Seleccione Almacenamiento de datos -> Contenedores y vaya a la carpeta donde se encuentran los datos de origen a los que necesita acceder la tabla externa.
  3. Seleccione Control de acceso (IAM).
  4. Seleccione Agregar -> Agregar asignación de roles.
  5. En la lista de roles de función de trabajo, seleccione Colaborador de datos de blobs de almacenamiento y seleccione Siguiente.
  6. En la página Agregar asignación de roles, seleccione + Seleccionar miembros. Se abre el panel Seleccionar miembros.
  7. Escriba el nombre de la identidad del área de trabajo. La identidad del área de trabajo es la misma que el nombre del área de trabajo. Cuando se muestre, elija la identidad del área de trabajo y, a continuación, Seleccionar.
  8. En la página Agregar asignación de roles, asegúrese de que la lista de miembros incluya la cuenta de Microsoft Entra ID deseada. Una vez comprobado, seleccione Revisar y asignar.
  9. En la página de confirmación, revise los cambios y seleccione Revisar y asignar.

La identidad del área de trabajo es ahora miembro del rol Colaborador de datos de blobs de almacenamiento y tiene acceso a la carpeta de origen.

Nota:

Estos pasos también se aplican a cuentas de ADLS Gen2 seguras configuradas para restringir el acceso público. Para obtener más información sobre cómo proteger la cuenta de ADLS Gen2, consulte Configuración de firewalls y redes virtuales de Azure Storage.

Ingesta de datos mediante COPY INTO

La instrucción T-SQL COPY INTO proporciona ingesta de datos flexible y de alto rendimiento en las tablas y es la estrategia principal para ingerir datos en las tablas de grupo de SQL dedicadas. COPY INTO permite a los usuarios ingerir datos de ubicaciones externas sin tener que crear ninguno de los objetos de base de datos adicionales necesarios para las tablas externas.

Para ejecutar la instrucción COPY INTO mediante una identidad administrada del área de trabajo para la autenticación, use el siguiente comando de T-SQL:

COPY INTO <TableName>
FROM 'https://<AccountName>.dfs.core.windows.net/<Container>/<Folder>/ '
WITH
(
    CREDENTIAL = (IDENTITY = 'Managed Identity'),
    [<CopyIntoOptions>]
);

Donde:

  • <TableName> es el nombre de la tabla en la que ingerir datos
  • <AccountName> es el nombre de la cuenta de ADLS Gen2
  • <Container> es el nombre del contenedor dentro de la cuenta de almacenamiento donde se almacenan los datos de origen.
  • <Folder> es la carpeta (o ruta de acceso con subcarpetas) donde se almacenan los datos de origen dentro del contenedor. También puede proporcionar un nombre de archivo si apunta directamente a un único archivo.
  • <CopyIntoOptions> es la lista de cualquier otra opción que desee proporcionar a la instrucción COPY INTO.

Para obtener más información y explorar la sintaxis completa de COPY INTO, consulte COPY INTO (Transact-SQL).

Consulta de datos en ADLS Gen2 mediante tablas externas

Las tablas externas permiten a los usuarios consultar datos de cuentas de Azure Data Lake Storage (ADLS) Gen2 sin necesidad de ingerir datos primero. Los usuarios pueden crear una tabla externa que apunte a los archivos de un contenedor de ADLS Gen2 y consultarla igual que una tabla de usuario normal.

En los pasos siguientes se describe el proceso para crear una nueva tabla externa que apunte a los datos de ADLS Gen2 mediante una identidad administrada para la autenticación.

Creación de los objetos de base de datos necesarios

Las tablas externas requieren que se creen los siguientes objetos:

  1. Clave maestra de base de datos que cifra el secreto de la credencial con ámbito de base de datos
  2. Credencial con ámbito de base de datos que usa la identidad del área de trabajo
  3. Origen de datos externo que apunte a la carpeta de origen
  4. Formato de archivo externo que defina el formato de los archivos de origen
  5. Definición de tabla externa que se usa para las consultas

Para seguir estos pasos, use el editor de SQL en el área de trabajo de Azure Synapse o el cliente SQL preferido conectado al grupo de SQL dedicado. Echemos un vistazo a estos pasos en detalle.

Creación de la clave maestra de la base de datos

La clave maestra de base de datos es una clave simétrica que se usa para proteger las claves privadas de certificados y las claves asimétricas presentes en la base de datos y los secretos en credenciales con ámbito de base de datos. Si ya hay una clave maestra en la base de datos, no es necesario crear una nueva. Reemplace <Secure Password> por una contraseña segura. Esta contraseña se usa para cifrar la clave maestra de la base de datos.

Para crear una clave maestra, use el siguiente comando de T-SQL:

-- Replace <Secure Password> with a secure password
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Secure Password>';

Para obtener más información sobre la clave maestra de base de datos, consulte Crear clave maestra (Transact-SQL).

Creación de la credencial con ámbito de base de datos

Una credencial con ámbito de base de datos usa la identidad del área de trabajo y es necesaria para acceder a la ubicación externa siempre que la tabla externa requiera acceso a los datos de origen.

Para crear la credencial con ámbito de base de datos, use el siguiente comando. Reemplace <CredentialName> por el nombre que desea usar para la credencial con ámbito de base de datos.

CREATE DATABASE SCOPED CREDENTIAL <CredentialName> WITH IDENTITY = 'Managed Service Identity';

Para obtener más información sobre las credenciales con ámbito de base de datos, consulte Crear credencial con ámbito de base de datos (Transact-SQL).

Creación del origen de datos externo

El siguiente paso consiste en crear un origen de datos externo que especifique dónde residen los datos de origen utilizados por la tabla externa.

Para crear el origen de datos externo, use el siguiente comando T-SQL:

CREATE EXTERNAL DATA SOURCE <ExternalDataSourceName>
WITH (
    TYPE = HADOOP,
    LOCATION = 'abfss://<Container>@<AccountName>.dfs.core.windows.net/<Folder>/,
    CREDENTIAL = <CredentialName>
);

Donde:

  • <ExternalDataSourceName> es el nombre que desea usar para el origen de datos externo.
  • <AccountName> es el nombre de la cuenta de ADLS Gen2.
  • <Container> es el nombre del contenedor dentro de la cuenta de almacenamiento donde se almacenan los datos de origen.
  • <Folder> es la carpeta (o ruta de acceso con subcarpetas) donde se almacenan los datos de origen dentro del contenedor. También puede proporcionar un nombre de archivo si apunta directamente a un único archivo.
  • <Credential> es el nombre de la credencial con ámbito de base de datos que creó anteriormente.

Para obtener más información sobre los orígenes de datos externos, consulte Crear origen de datos externos (Transact-SQL).

Creación del formato de archivo externo

El siguiente paso es crear el formato de archivo externo. Especifica el diseño real de los datos a los que hace referencia la tabla externa.

Para crear el formato de archivo externo, use el siguiente comando T-SQL. Reemplace <FileFormatName> por el nombre que desea usar para el formato de archivo externo.

CREATE EXTERNAL FILE FORMAT <FileFormatName>
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS (
        FIELD_TERMINATOR = ',',
        STRING_DELIMITER = '"',
        FIRST_ROW = 2,
        USE_TYPE_DEFAULT = True
    )
);

En este ejemplo, ajuste parámetros como FIELD_TERMINATOR, STRING_DELIMITER, FIRST_ROW y otros según sea necesario de acuerdo con los datos de origen. Para obtener más opciones de formato y obtener más información sobre EXTERNAL FILE FORMAT, vea Crear formato de archivo externo.

Creación de la tabla externa

Ahora que se han creado todos los objetos necesarios que contienen los metadatos para acceder de forma segura a los datos externos, es el momento de crear la tabla externa. Para crear la tabla externa, use el siguiente comando T-SQL:

-- Adjust the table name and columns to your desired name and external table schema
CREATE EXTERNAL TABLE <ExternalTableName> (
    Col1 INT,
    Col2 NVARCHAR(100),
    Col4 INT
)
WITH
(
    LOCATION = '<Path>',
    DATA_SOURCE = <ExternalDataSourceName>,
    FILE_FORMAT = <FileFormatName>
);

Donde:

Asegúrese de ajustar el nombre y el esquema de la tabla al nombre deseado y al esquema de los datos de los archivos de origen.

En este momento, se crean todos los metadatos necesarios para acceder a la tabla externa. Para probar la tabla externa, use una consulta como el ejemplo de T-SQL siguiente para validar el trabajo:

SELECT TOP 10 Col1, Col2 FROM <ExternalTableName>;

Si todo se configuró correctamente, debería ver los datos de los datos de origen como resultado de esta consulta.

Para obtener más información y explorar la sintaxis completa de CREATE EXTERNAL TABLE, vea Crear tabla externa (Transact-SQL).

Contenido relacionado