Anterior

Siguientes

Creación de objetos de base de datos externos

Completado

Puede usar la función OPENROWSET en las consultas SQL que se ejecutan en la base de datos maestra predeterminada del grupo de SQL sin servidor integrado para explorar datos en el lago de datos. Sin embargo, es posible que en ocasiones desee crear una base de datos personalizada que contenga algunos objetos que faciliten el trabajo con datos externos en el lago de datos que necesita consultar con frecuencia.

Creación de una base de datos

Puede crear una base de datos en un grupo de SQL sin servidor como lo haría en una instancia de SQL Server. Puede usar la interfaz gráfica en Synapse Studio o una instrucción CREATE DATABASE. Una consideración que puede tener en cuenta es establecer la intercalación de la base de datos para que admita la conversión de datos de texto en archivos a los tipos de datos Transact-SQL correspondientes.

En el código de ejemplo siguiente se crea una base de datos denominada salesDB con una intercalación que facilita la importación de datos de texto codificado en UTF-8 en columnas VARCHAR.

CREATE DATABASE SalesDB
    COLLATE Latin1_General_100_BIN2_UTF8

Creación de un origen de datos externo

Puede usar la función OPENROWSET con una ruta de acceso BULK para consultar datos de archivo de su propia base de datos, como en la base de datos maestra. Pero si planea consultar datos en la misma ubicación con frecuencia, es más eficaz definir un origen de datos externo que haga referencia a esa ubicación. Por ejemplo, el código siguiente crea un origen de datos denominado archivos para la carpeta hipotética https://mydatalake.blob.core.windows.net/data/files/:

CREATE EXTERNAL DATA SOURCE files
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/files/'
)

Una ventaja de un origen de datos externo es que puede simplificar una consulta OPENROWSET para usar la combinación del origen de datos y la ruta de acceso relativa a las carpetas o los archivos que desea consultar:

SELECT *
FROM
    OPENROWSET(
        BULK 'orders/*.csv',
        DATA_SOURCE = 'files',
        FORMAT = 'csv',
        PARSER_VERSION = '2.0'
    ) AS orders

En este ejemplo, se usa el parámetro BULK para especificar la ruta de acceso relativa para todos los archivos .csv de la carpeta pedidos, que es una subcarpeta de la carpeta archivos a la que hace referencia el origen de datos.

Otra ventaja de usar un origen de datos es que puede asignar una credencial para el origen de datos al obtener acceso al almacenamiento subyacente, lo que le permite proporcionar acceso a los datos a través de SQL sin permitir a los usuarios tener acceso directo a los datos en la cuenta de almacenamiento. Por ejemplo, el código siguiente crea una credencial que usa una firma de acceso compartido (SAS) para autenticarse en la cuenta de almacenamiento de Azure subyacente que hospeda el lago de datos.

CREATE DATABASE SCOPED CREDENTIAL sqlcred
WITH
    IDENTITY='SHARED ACCESS SIGNATURE',  
    SECRET = 'sv=xxx...';
GO

CREATE EXTERNAL DATA SOURCE secureFiles
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/secureFiles/'
    CREDENTIAL = sqlcred
);
GO

Sugerencia

Además de la autenticación SAS, puede definir credenciales que usen la identidad administrada (la identidad de Microsoft Entra que usa su área de trabajo de Azure Synapse), una entidad de seguridad de Microsoft Entra específica o una autenticación de paso según la identidad del usuario que ejecuta la consulta (que es el tipo de autenticación predeterminado). Para obtener más información sobre el uso de credenciales en un grupo de SQL sin servidor, consulte el artículo Control del acceso a la cuenta de almacenamiento del grupo de SQL sin servidor en Azure Synapse Analytics en la documentación de Azure Synapse Analytics.

Creación de un formato de archivo externo

Aunque un origen de datos externo simplifica el código necesario para tener acceso a los archivos con la función OPENROWSET, todavía necesita proporcionar detalles de formato para el archivo al que está teniendo acceso. Entre estos detalles se pueden incluir varias opciones de configuración para archivos de texto delimitados. Puede encapsular esta configuración en un formato de archivo externo como este:

CREATE EXTERNAL FILE FORMAT CsvFormat
    WITH (
        FORMAT_TYPE = DELIMITEDTEXT,
        FORMAT_OPTIONS(
            FIELD_TERMINATOR = ',',
            STRING_DELIMITER = '"'
        )
    );
GO

Después de crear formatos de archivo para los archivos de datos específicos con los que necesita trabajar, puede usar el formato de archivo para crear tablas externas, como se explica a continuación.

Creación de una tabla externa

Si necesita realizar una gran cantidad de análisis o informes de archivos en el lago de datos, el uso de la función OPENROWSET puede dar como resultado código complejo que incluye orígenes de datos y rutas de archivo. Para simplificar el acceso a los datos, puede encapsular los archivos en una tabla externa que los usuarios y las aplicaciones de informes pueden consultar con una instrucción SQL de tipo SELECT estándar, igual que con cualquier otra tabla de base de datos. Para crear una tabla externa, use la instrucción CREATE EXTERNAL TABLE, especifique el esquema de columna como para una tabla estándar e incluya una cláusula WITH que especifique el origen de datos externo, la ruta de acceso relativa y el formato de archivo externo para los datos.

CREATE EXTERNAL TABLE dbo.products
(
    product_id INT,
    product_name VARCHAR(20),
    list_price DECIMAL(5,2)
)
WITH
(
    DATA_SOURCE = files,
    LOCATION = 'products/*.csv',
    FILE_FORMAT = CsvFormat
);
GO

-- query the table
SELECT * FROM dbo.products;

Al crear una base de datos que contenga los objetos externos descritos en esta unidad, puede proporcionar una capa de base de datos relacional sobre los archivos de un lago de datos, lo que facilita el acceso a los datos a muchos analistas de datos y herramientas de creación de informes mediante el uso de la semántica estándar de consulta SQL.

Continuar