Ejercicio: CREATE EXTERNAL TABLE AS SELECT

10 minutos

En este ejercicio, usará CREATE EXTERNAL TABLE AS SELECT (CETAS) para:

Exportar una tabla como Parquet.
Mover los datos fríos de una base de datos al almacenamiento.
Cree una tabla externa para acceder a los datos externos exportados.
Usar vistas o búsqueda con caracteres comodín como estrategias de consulta.
Limite las consultas mediante la eliminación de carpetas y la información de metadatos para mejorar el rendimiento.

Prerrequisitos

Una instancia de SQL Server 2025 con conectividad a Internet y la característica Servicio de consultas de PolyBase para datos externos instalada y habilitada como para ejercicios anteriores.
La base de datos de ejemplo AdventureWorks2022 restaurada en el servidor que se usará para los datos de ejemplo.
Una cuenta de Azure Storage con un contenedor de Blob Storage denominado data creado. Para crear el almacenamiento, consulte Inicio rápido: Carga, descarga y enumeración de blobs con Azure Portal.
Rol de control de acceso basado en roles (RBAC) de Azure colaborador de datos de Blob Storage asignado en Azure. Para más información, vea Asignación de un rol de Azure para el acceso a datos de blob.
Un token de SAS de contenedor de blobs con permisos READ, WRITE, LIST y CREATE que se usarán para CETAS. Para crear el token de SAS, consulte Creación de tokens de firma de acceso compartido (SAS) para los contenedores de almacenamiento.

Uso de CETAS para exportar una tabla como Parquet

Imagine que trabaja con un equipo de análisis empresarial que quiere exportar datos anteriores a 2012 desde una tabla de SQL Server a un contenedor de Azure Blob Storage. Quieren ejecutar sus consultas de informe en estos datos exportados en lugar de consultar directamente SQL Server.

Habilite CETAS en la instancia de SQL Server.

EXEC SP_CONFIGURE @CONFIGNAME = 'ALLOW POLYBASE EXPORT', @CONFIGVALUE = 1;

Captura de pantalla de la configuración de la opción para permitir la exportación de PolyBase.

Ejecute la siguiente consulta de exploración de datos para comprender qué datos desea exportar. En este caso, busca datos de 2012 o anteriores. Quiere exportar todos los datos de 2011 y 2012.
```
-- RECORDS BY YEARS
SELECT COUNT(*) AS QTY, DATEPART(YYYY, [DUEDATE]) AS [YEAR]
FROM  [PURCHASING].[PURCHASEORDERDETAIL] 
GROUP BY DATEPART(YYYY, [DUEDATE])
ORDER BY [YEAR]
```

Cree una clave maestra de base de datos para la base de datos, como en los ejercicios anteriores.

Use AdventureWorks2022

DECLARE @randomWord VARCHAR(64) = NEWID();
DECLARE @createMasterKey NVARCHAR(500) = N'
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = ''##MS_DatabaseMasterKey##'')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '  + QUOTENAME(@randomWord, '''')
EXEC sp_executesql @createMasterKey;

SELECT * FROM sys.symmetric_keys;

Cree las credenciales con ámbito de base de datos y el origen de datos externo. Reemplaza los marcadores de posición <sas_token> y <storageccount> por la cuenta de almacenamiento y el token de SAS que creaste en Azure.

-- DATABASE SCOPED CREDENTIAL
CREATE DATABASE SCOPED CREDENTIAL blob_storage
      WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
      SECRET = '<sas_token>';

-- AZURE BLOB STORAGE DATA SOURCE
CREATE EXTERNAL DATA SOURCE ABS_Data
WITH
(
 LOCATION = 'abs://<storageaccount>.blob.core.windows.net/data/chapter3'
,CREDENTIAL = blob_storage
);

Cree el formato de archivo externo para Parquet.

-- PARQUET FILE FORMAT
CREATE EXTERNAL FILE FORMAT ffParquet
WITH (FORMAT_TYPE = PARQUET);

Cree la tabla externa mediante CETAS. La consulta siguiente crea una tabla externa denominada ext_data_2011_2012 y exporta todos los datos de 2011 y 2012 a la ubicación especificada por el origen de datos ABS_Data.

CREATE EXTERNAL TABLE ex_data_2011_2012
WITH(
        LOCATION = 'data_2011_20122',
        DATA_SOURCE = ABS_Data,
        FILE_FORMAT = ffParquet
)AS
SELECT
	[PurchaseOrderID]
      ,[PurchaseOrderDetailID]
      ,[DueDate]
      ,[OrderQty]
      ,[ProductID]
      ,[UnitPrice]
      ,[LineTotal]
      ,[ReceivedQty]
      ,[RejectedQty]
      ,[StockedQty]
      ,[ModifiedDate]
FROM  [PURCHASING].[PURCHASEORDERDETAIL] 
WHERE YEAR([DUEDATE]) < 2013
GO

Compruebe azure Blob Storage en Azure Portal. Debería ver la siguiente estructura creada. SQL Server 2025 crea automáticamente el nombre de archivo en función de la cantidad de datos que exporta y el formato de archivo.
Ahora puede acceder a la tabla externa como una tabla normal.
```
SELECT * FROM ex_data_2011_2012
```

Los datos ahora se exportan a Parquet y son fácilmente accesibles a través de la tabla externa. El equipo de análisis empresarial puede consultar la tabla externa o dirigir su herramienta de generación de informes al archivo Parquet.

Uso de CETAS para mover datos inactivos fuera de la base de datos

Para mantener los datos administrables, la empresa decide mover datos anteriores a 2014 desde la base de datos de SQL Server. Sin embargo, todos los datos deben seguir siendo accesibles.

En este ejemplo, exporta los datos a través de CETAS y genera varias tablas externas que puede consultar más adelante. Puede usar una vista con instrucciones UNION para consultar los datos o crear una sola tabla externa y usar un carácter comodín para buscar en las subcarpetas de los datos exportados.

En primer lugar, clone la tabla original, ya que quiere simular la exportación y eliminación de los datos, pero no quiere eliminar necesariamente el origen de datos actual. Ejecute la instrucción siguiente:

-- CLONE TABLE
SELECT * INTO [PURCHASING].[PURCHASEORDERDETAIL_2] FROM [PURCHASING].[PURCHASEORDERDETAIL]

Desde la primera consulta de exploración de datos, sabe que hay 5551 registros de 2014. Todo lo anterior a 2014 debe exportarse a una carpeta identificada por año. Los datos de 2011 entran en una carpeta denominada 2011, etc.

Para crear las tablas externas, ejecute los siguientes comandos:

CREATE EXTERNAL TABLE ex_2011
WITH(
        LOCATION = '2011',
        DATA_SOURCE = ABS_Data,
        FILE_FORMAT = ffParquet
)AS
SELECT
    [PurchaseOrderID]
      ,[PurchaseOrderDetailID]
      ,[DueDate]
      ,[OrderQty]
      ,[ProductID]
      ,[UnitPrice]
      ,[LineTotal]
      ,[ReceivedQty]
      ,[RejectedQty]
      ,[StockedQty]
      ,[ModifiedDate]
FROM  [PURCHASING].[PURCHASEORDERDETAIL_2] 
WHERE YEAR([DUEDATE]) = 2011;

CREATE EXTERNAL TABLE ex_2012
WITH(
        LOCATION = '2012',
        DATA_SOURCE = ABS_Data,
        FILE_FORMAT = ffParquet
)AS
SELECT
    [PurchaseOrderID]
      ,[PurchaseOrderDetailID]
      ,[DueDate]
      ,[OrderQty]
      ,[ProductID]
      ,[UnitPrice]
      ,[LineTotal]
      ,[ReceivedQty]
      ,[RejectedQty]
      ,[StockedQty]
      ,[ModifiedDate]
FROM  [PURCHASING].[PURCHASEORDERDETAIL_2] 
WHERE YEAR([DUEDATE]) = 2012;

CREATE EXTERNAL TABLE ex_2013
WITH(
        LOCATION = '2013',
        DATA_SOURCE = ABS_Data,
        FILE_FORMAT = ffParquet
)AS
SELECT
    [PurchaseOrderID]
      ,[PurchaseOrderDetailID]
      ,[DueDate]
      ,[OrderQty]
      ,[ProductID]
      ,[UnitPrice]
      ,[LineTotal]
      ,[ReceivedQty]
      ,[RejectedQty]
      ,[StockedQty]
      ,[ModifiedDate]
FROM  [PURCHASING].[PURCHASEORDERDETAIL_2] 
WHERE YEAR([DUEDATE]) = 2013;

Después de ejecutar estos comandos, actualice el Explorador de objetos de SSMS. A continuación, abra Bases de datos>AdventureWorks2022>Tablas>externas para ver las tablas externas.
Confirme que las siguientes carpetas aparecen en el contenedor de Azure Storage:
Una vez exportados los datos en frío, puede eliminarlos de la ubicación de la tabla original.
```
DELETE FROM [PURCHASING].[PURCHASEORDERDETAIL_2] 
WHERE YEAR([DUEDATE]) < 2014
```

Consulta los datos que incluyen la tabla externa

Puede usar una vista de datos o una búsqueda con caracteres comodín para consultar los datos exportados externamente. Cada método tiene ventajas y desventajas. El método de vista se recomienda para solicitudes repetitivas porque normalmente funciona mejor y también se puede combinar con tablas físicas. El método de búsqueda con caracteres comodín es más flexible y fácil de usar con fines de exploración.

Uso de una vista para consultar los datos

Ahora que los datos antiguos se exportan y eliminan de la base de datos, puede usar T-SQL para crear una vista que consulte todas las tablas externas y los datos actuales de la base de datos.

CREATE VIEW vw_purchaseorderdetail 
AS
SELECT * FROM ex_2011
UNION ALL
SELECT * FROM ex_2012
UNION ALL
SELECT * FROM ex_2013
UNION ALL
SELECT * FROM  [PURCHASING].[PURCHASEORDERDETAIL_2]

Puede ejecutar la consulta de exploración de datos original, esta vez con la vista recién creada, para ver los mismos resultados.

SELECT  COUNT(*) AS QTY, DATEPART(YYYY, [DUEDATE]) AS [YEAR]
FROM vw_purchaseorderdetail 
GROUP BY DATEPART(YYYY, [DUEDATE])
ORDER BY [YEAR]

Uso de la búsqueda con caracteres comodín para consultar los datos

En el ejemplo anterior, usó una vista con instrucciones UNION para combinar las tres tablas externas. Otra manera de lograr los resultados deseados es usar una búsqueda con caracteres comodín para examinar la estructura de carpetas, incluidas las subcarpetas, para cualquier dato de un tipo determinado.

En el siguiente ejemplo de T-SQL se usa OPENROWSET para buscar en el origen de datos ABS_Data, incluidas sus subcarpetas, en busca de archivos Parquet.

SELECT COUNT(*) AS QTY, DATEPART(YYYY, [DUEDATE]) AS [YEAR]
FROM OPENROWSET 
    (BULK '**'
    , FORMAT = 'PARQUET'
    , DATA_SOURCE = 'ABS_Data')
    AS [cc]
GROUP BY DATEPART(YYYY, [DUEDATE])
ORDER BY [YEAR]

Eliminación de carpetas e información de metadatos

Tanto las tablas externas como OPENROWSET pueden usar la filepath función para recopilar y filtrar información basada en metadatos de archivo. La filepath función devuelve rutas de acceso completas, nombres de carpeta y nombres de archivo. Puede usar esa información para mejorar las funcionalidades de búsqueda de la tabla externa y los comandos OPENROWSET.

SELECT
    r.filepath(1) 'folder_name'
    ,r.filepath() 'full_path'
    ,r.filepath(2) 'file_name'
FROM OPENROWSET(
        BULK '*/*.parquet',
        DATA_SOURCE = 'ABS_Data',
        FORMAT = 'parquet'
    ) as [r]
GROUP BY
    r.filepath(2),r.filepath(1), r.filepath()
ORDER BY 
    r.filepath(2)

Captura de pantalla de SSMS que muestra la función filepath.

Si desea recuperar datos de una carpeta determinada y seguir usando la funcionalidad del método de búsqueda con caracteres comodín, puede usar la consulta siguiente:

SELECT  *
FROM OPENROWSET(
 BULK '*/*.parquet',
DATA_SOURCE = 'ABS_Data',
FORMAT = 'parquet'
 ) AS r
WHERE
 r.filepath(1) IN ('2011')

Los resultados finales son los mismos, pero mediante el uso de los metadatos de eliminación de carpetas, la consulta accede solo a las carpetas necesarias en lugar de examinar todo el origen de datos, lo que produce un mejor rendimiento de las consultas. Tenga en cuenta esta información al diseñar arquitecturas de almacenamiento para usar mejor las funcionalidades de PolyBase.

Por ejemplo, dada la siguiente arquitectura de carpeta:

Captura de pantalla que muestra un ejemplo de arquitectura de carpeta en un contenedor de almacenamiento.

Puede usar la consulta siguiente:

SELECT  *
FROM OPENROWSET(
 BULK 'year=*/month=*/*.parquet',
DATA_SOURCE = 'ABS_Data',
FORMAT = 'parquet'
 ) AS r
WHERE
 r.filepath(1) IN ('<year>')
 r.filepath(2) IN ('<month>')

Para los fines de esta consulta, no importa cuánto crezca el origen de datos. SQL Server carga, lee y consulta solo los datos de la carpeta seleccionada y omite todos los demás.

Dado que no hay datos almacenados en la base de datos, el administrador de bases de datos no necesita diseñar una estrategia específica para administrar estos datos. La empresa debe seguir tomando todas las precauciones necesarias para mantener los datos de forma segura, incluidos, entre otros, las copias de seguridad, la disponibilidad y los permisos.

Resumen

En este ejercicio, ha utilizado CETAS para trasladar datos inactivos de una base de datos a Azure Storage y exportar una tabla en formato de archivo Parquet. Ha aprendido a consultar los datos externos para la exploración y optimizar el rendimiento.

Puede usar CETAS para combinar OPENROWSET, tablas externas, vistas, búsqueda con caracteres comodín y funciones filepath. Puede acceder y exportar datos desde otras bases de datos como SQL Server, Oracle, Teradata y MongoDB, o desde Azure Blob Storage, Azure Data Lake Storage o cualquier almacenamiento de objetos compatible con S3. CETAS puede ayudarle a diseñar soluciones eficaces, duraderas y escalables en todos los orígenes de datos compatibles con PolyBase.

Comentarios

¿Le ha resultado útil esta página?