Acceso al almacenamiento externo mediante el grupo de SQL sin servidor en Azure Synapse Analytics

Tip

Microsoft Fabric Data Warehouse es un almacenamiento relacional de escala empresarial en una base de lago de datos, con una arquitectura lista para el futuro, inteligencia artificial integrada y nuevas características. Si no está familiarizado con el almacenamiento de datos, comience con Fabric Data Warehouse. Las cargas de trabajo del grupo de SQL dedicadas pueden actualizarse a Fabric para acceder a nuevas funcionalidades en ciencia de datos, análisis en tiempo real e informes.

En este artículo se describe cómo los usuarios pueden leer datos de los archivos almacenados en Azure Storage en el grupo de SQL sin servidor. Los usuarios tienen las siguientes opciones para acceder al almacenamiento:

  • OPENROWSET función que permite consultas ad hoc sobre los archivos de Azure Storage.
  • Tabla externa que es una estructura de datos predefinida basada en el conjunto de archivos externos.

El usuario puede usar diferentes métodos de autenticación como la autenticación de paso a través de Microsoft Entra (valor predeterminado para las entidades de seguridad de Microsoft Entra) y la autenticación SAS (valor predeterminado para las entidades de seguridad de SQL).

Consulta de archivos mediante OPENROWSET

OPENROWSET permite a los usuarios consultar archivos externos en Azure almacenamiento si tienen acceso al almacenamiento. Un usuario que esté conectado al grupo de SQL sin servidor debe usar la siguiente consulta para leer el contenido de los archivos en Azure almacenamiento:

SELECT * FROM
 OPENROWSET(BULK 'https://<storage_account>.dfs.core.windows.net/<container>/<path>/*.parquet', format= 'parquet') as rows

El usuario puede acceder al almacenamiento mediante las siguientes reglas de acceso:

  • Microsoft Entra usuario: OPENROWSET usará Microsoft Entra identidad del autor de la llamada para acceder a Azure Storage o acceder al almacenamiento con acceso anónimo.
  • Usuario de SQL: OPENROWSET accederá al almacenamiento con acceso anónimo o se puede suplantar mediante el token de SAS o la identidad administrada del área de trabajo.

Las entidades de seguridad de SQL también pueden usar OPENROWSET para consultar directamente los archivos protegidos con tokens de SAS o la identidad administrada del área de trabajo. Si un usuario de SQL ejecuta esta función, un usuario avanzado con permiso ALTER ANY CREDENTIAL debe crear una credencial con ámbito de servidor que coincida con la URL indicada en la función (usando el nombre del almacenamiento y el contenedor) y conceder permiso de REFERENCES para esta credencial al solicitante de la función OPENROWSET.

EXECUTE AS somepoweruser

CREATE CREDENTIAL [https://<storage_account>.dfs.core.windows.net/<container>]
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'sas token';

GRANT REFERENCES ON CREDENTIAL::[https://<storage_account>.dfs.core.windows.net/<container>] TO sqluser

Si no hay ninguna CREDENCIAL de nivel de servidor que coincida con la dirección URL o el usuario de SQL no tiene permiso de referencias para esta credencial, se devolverá el error. Los principales de SQL no pueden suplantar empleando una identidad de Microsoft Entra.

Note

Esta versión de OPENROWSET está diseñada para la exploración rápida y sencilla de datos mediante la autenticación predeterminada. Para aprovechar la impersonación o la identidad administrada, use OPENROWSET con DATA_SOURCE descrito en la sección siguiente.

Consulta de orígenes de datos mediante OPENROWSET

OPENROWSET permite al usuario consultar los archivos colocados en algún origen de datos externo:

SELECT * FROM
 OPENROWSET(BULK 'file/path/*.parquet',
 DATA_SOURCE = MyAzureInvoices,
 FORMAT= 'parquet') as rows

El usuario que ejecuta esta consulta debe poder acceder a los archivos. Los usuarios deben ser autenticados o impersonados mediante token SAS o Identidad Administrada del área de trabajo si no pueden acceder directamente a los archivos mediante su identidad de Microsoft Entra o acceso anónimo.

DATABASE SCOPED CREDENTIAL especifica cómo acceder a los archivos en el origen de datos al que se hace referencia (actualmente SAS e identidad administrada). El usuario avanzado con CONTROL DATABASE permiso tendría que crear DATABASE SCOPED CREDENTIAL que se usará para acceder al almacenamiento y EXTERNAL DATA SOURCE que especifique la dirección URL del origen de datos y las credenciales que se deben usar:

EXECUTE AS somepoweruser;

-- Create MASTER KEY if it doesn't exists in database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'some very strong password';

CREATE DATABASE SCOPED CREDENTIAL AccessAzureInvoices
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = '******srt=sco&amp;sp=rwac&amp;se=2017-02-01T00:55:34Z&amp;st=201********' ;

CREATE EXTERNAL DATA SOURCE MyAzureInvoices
 WITH ( LOCATION = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>/' ,
 CREDENTIAL = AccessAzureInvoices) ;

El autor de la llamada debe tener uno de los permisos siguientes para ejecutar la función OPENROWSET:

  • Uno de los permisos para ejecutar OPENROWSET:
    • ADMINISTER BULK OPERATIONS permite iniciar sesión para ejecutar la función OPENROWSET.
    • ADMINISTER DATABASE BULK OPERATIONS permite a los usuarios cuyo ámbito sea la base de datos ejecutar la función OPENROWSET.
  • REFERENCES DATABASE SCOPED CREDENTIAL a la credencial a la que se hace referencia en EXTERNAL DATA SOURCE.

TABLA EXTERNA

El usuario con los permisos para leer la tabla puede tener acceso a archivos externos mediante una TABLA EXTERNA creada sobre el conjunto de carpetas y archivos de Azure Storage.

Usuario que tiene permisos para crear una tabla externa (por ejemplo, CREATE TABLE y ALTER ANY CREDENTIAL o REFERENCES DATABASE SCOPED CREDENTIAL) puede usar el siguiente script para crear una tabla en el origen de datos de Azure Storage:

CREATE EXTERNAL TABLE [dbo].[DimProductexternal]
( ProductKey int, ProductLabel nvarchar, ProductName nvarchar )
WITH
(
LOCATION='/DimProduct/year=*/month=*' ,
DATA_SOURCE = AzureDataLakeStore ,
FILE_FORMAT = TextFileFormat
) ;

El usuario que lee los datos de esta tabla debe poder acceder a los archivos. Los usuarios deben autenticarse mediante SAS token o la Identidad Administrada del espacio de trabajo si no pueden acceder directamente a los archivos mediante su identidad de Microsoft Entra o acceso anónimo.

DATABASE SCOPED CREDENTIAL especifica cómo acceder a los archivos en el origen de datos al que se hace referencia. El usuario con el permiso CONTROL DATABASE necesitaría crear un DATABASE SCOPED CREDENTIAL que se utilizará para acceder al almacenamiento y al EXTERNAL DATA SOURCE que especifica la URL del origen de datos y las credenciales que se deben usar.

EXECUTE AS somepoweruser;

-- Create MASTER KEY if it doesn't exists in database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'some very strong password';

CREATE DATABASE SCOPED CREDENTIAL cred
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=201********' ;

CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
 WITH ( LOCATION = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>' ,
 CREDENTIAL = cred
 ) ;

Leer archivos externos con EXTERNAL TABLE

EXTERNAL TABLE permite leer datos de los archivos a los que se hace referencia a través del origen de datos mediante la instrucción SELECT de SQL estándar:

SELECT *
FROM dbo.DimProductsExternal

El autor de la llamada debe tener los permisos siguientes para leer datos:

  • SELECT permiso EN tabla externa
  • REFERENCES DATABASE SCOPED CREDENTIAL permiso si DATA SOURCE tiene CREDENTIAL

Permissions

En la tabla siguiente se enumeran los permisos necesarios para las operaciones enumeradas anteriormente.

Query Permisos necesarios
OPENROWSET(BULK) sin origen de datos ADMINISTER BULK OPERATIONS, ADMINISTER DATABASE BULK OPERATIONS o el inicio de sesión de SQL deben tener REFERENCES CREDENTIAL::<URL> para el almacenamiento protegido por SAS.
OPENROWSET(BULK) con origen de datos sin credenciales ADMINISTER BULK OPERATIONS o ADMINISTER DATABASE BULK OPERATIONS,
OPENROWSET(BULK) con origen de datos con credenciales REFERENCES DATABASE SCOPED CREDENTIAL y uno de ADMINISTER BULK OPERATIONS o ADMINISTER DATABASE BULK OPERATIONS
CREAR FUENTE DE DATOS EXTERNA ALTER ANY EXTERNAL DATA SOURCE y REFERENCES DATABASE SCOPED CREDENTIAL
CREAR TABLA EXTERNA CREATE TABLE, ALTER ANY SCHEMA, ALTER ANY EXTERNAL FILE FORMAT y ALTER ANY EXTERNAL DATA SOURCE.
SELECCIONAR DE TABLA EXTERNA SELECT TABLE y REFERENCES DATABASE SCOPED CREDENTIAL
CETAS Para crear una tabla: CREATE TABLE, ALTER ANY SCHEMA, ALTER ANY DATA SOURCEy ALTER ANY EXTERNAL FILE FORMAT. Para leer datos: ADMINISTER BULK OPERATIONS o REFERENCES CREDENTIALSELECT TABLE por cada tabla, vista o función en la consulta + permiso de R/W en el almacenamiento

Pasos siguientes

Ahora usted está listo para continuar con los siguientes artículos de instrucciones:

  • Last updated on